1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13:
14:
15: require_once('AWLUtilities.php');
16: require_once('AwlQuery.php');
17:
18:
19: 20: 21: 22: 23: 24: 25: 26: 27:
28: function sql_from_object( $obj, $type, $tablename, $where, $fprefix = "" ) {
29: $fields = awl_get_fields($tablename);
30: $update = strtolower($type) == "update";
31: if ( $update )
32: $sql = "UPDATE $tablename SET ";
33: else
34: $sql = "INSERT INTO $tablename (";
35:
36: $flst = "";
37: $vlst = "";
38: foreach( $fields as $fn => $typ ) {
39:
40: dbg_error_log( "DataUpdate", ":sql_from_object: %s => %s (%s)", $fn, $typ, (isset($obj->{$fn})?$obj->{$fn}:"[undefined value]"));
41: if ( !isset($obj->{$fn}) && isset($obj->{"xxxx$fn"}) ) {
42:
43:
44: $obj->{$fn} = $obj->{"xxxx$fn"};
45: }
46: if ( !isset($obj->{$fn}) ) continue;
47: $value = $obj->{$fn};
48: if ( $fn == "password" ) {
49: if ( $value == "******" || $value == "" ) continue;
50: if ( !preg_match('/^\*[0-9a-z+\/=]+\*({SSHA})?[0-9a-z+\/=]+$/i', $value ) ) {
51: $value = (function_exists("session_salted_sha1")
52: ? session_salted_sha1($value)
53: : (function_exists('session_salted_md5')
54: ? session_salted_md5($value)
55: : md5($value)
56: )
57: );
58: }
59: }
60: $value = str_replace( "'", "''", str_replace("\\", "\\\\", $value));
61: if ( preg_match('{^(time|date|interval)}i', $typ ) && $value == "" ) {
62: $value = "NULL";
63: }
64: else if ( preg_match('{^bool}i', $typ) ) {
65: $value = ( $value == false || $value == "f" || $value == "off" || $value == "no" ? "FALSE"
66: : ( $value == true || $value == "t" || $value == "on" || $value == "yes" ? "TRUE"
67: : "NULL" ));
68: }
69: else if ( preg_match('{^interval}i', $typ) ) {
70: $value = "'$value'::$typ";
71: }
72: else if ( preg_match('{^int}i', $typ) ) {
73: $value = ($value == '' || $value === null ? 'NULL' : intval( $value ));
74: }
75: else if ( preg_match('{^bit}i', $typ) ) {
76: $value = ($value == '' || $value === null ? 'NULL' : "'$value'");
77: }
78: else if ( preg_match('{^(text|varchar)}i', $typ) ) {
79: $value = "'$value'";
80: }
81: else
82: $value = "'$value'::$typ";
83:
84: if ( $update )
85: $flst .= ", $fn = $value";
86: else {
87: $flst .= ", $fn";
88: $vlst .= ", $value";
89: }
90: }
91: $flst = substr($flst,2);
92: $vlst = substr($vlst,2);
93: $sql .= $flst;
94: if ( $update ) {
95: $sql .= " $where; ";
96: }
97: else {
98: $sql .= ") VALUES( $vlst ); ";
99: }
100: return $sql;
101: }
102:
103:
104: 105: 106: 107: 108: 109: 110: 111:
112: function sql_from_post( $type, $tablename, $where, $fprefix = "" ) {
113: $fakeobject = (object) $_POST;
114: return sql_from_object( $fakeobject, $type, $tablename, $where, $fprefix );
115: }
116:
117:
118: 119: 120: 121:
122: class DBRecord
123: {
124: 125: 126:
127: 128: 129: 130:
131: var $Table;
132:
133: 134: 135: 136: 137:
138: var $Fields;
139:
140: 141: 142: 143:
144: var $Keys;
145:
146: 147: 148: 149:
150: var $Values;
151:
152: 153: 154: 155:
156: var $WriteType;
157:
158: 159: 160: 161:
162: var $OtherTable;
163:
164: 165: 166: 167: 168: 169:
170: var $OtherTargets;
171:
172: 173: 174: 175: 176:
177: var $OtherJoin;
178:
179: 180: 181: 182: 183:
184: var $OtherWhere;
185:
186:
187:
188: 189: 190:
191: 192: 193: 194:
195: var $EditMode;
196:
197:
198:
199: 200: 201:
202: function DBRecord( ) {
203: dbg_error_log( "DBRecord", ":Constructor: called" );
204: $this->WriteType = "insert";
205: $this->EditMode = false;
206: $this->prefix = "";
207: $values = (object) array();
208: $this->Values = &$values;
209: }
210:
211: 212: 213: 214: 215: 216:
217: function Initialise( $table, $keys = array() ) {
218: dbg_error_log( "DBRecord", ":Initialise: called" );
219: $this->Table = $table;
220: $this->Fields = awl_get_fields($this->Table);
221: $this->Keys = $keys;
222: $this->WriteType = "insert";
223: }
224:
225: 226: 227: 228: 229: 230: 231: 232:
233: function AddTable( $table, $target_list, $join_clause, $and_where ) {
234: dbg_error_log( "DBRecord", ":AddTable: $table called" );
235: $this->OtherTable[] = $table;
236: $this->OtherTargets[$table] = $target_list;
237: $this->OtherJoin[$table] = $join_clause;
238: $this->OtherWhere[$table] = $and_where;
239: }
240:
241: 242: 243: 244:
245: function PostToValues( $prefix = "" ) {
246: foreach ( $this->Fields AS $fname => $ftype ) {
247: @dbg_error_log( "DBRecord", ":PostToValues: %s => %s", $fname, $_POST["$prefix$fname"] );
248: if ( isset($_POST["$prefix$fname"]) ) {
249: $this->Set($fname, $_POST["$prefix$fname"]);
250: @dbg_error_log( "DBRecord", ":PostToValues: %s => %s", $fname, $_POST["$prefix$fname"] );
251: }
252: }
253: }
254:
255: 256: 257: 258:
259: function _BuildJoinClause() {
260: $clause = "";
261: foreach( $this->OtherJoins AS $t => $join ) {
262: if ( ! preg_match( '/^\s*$/', $join ) ) {
263: $clause .= ( $clause == "" ? "" : " " ) . $join;
264: }
265: }
266:
267: return $clause;
268: }
269:
270: 271: 272: 273:
274: function _BuildFieldList() {
275: $list = "";
276: foreach( $this->Fields AS $fname => $ftype ) {
277: $list .= ( $list == "" ? "" : ", " );
278: $list .= "$fname" . ( $this->prefix == "" ? "" : " AS \"$this->prefix$fname\"" );
279: }
280:
281: foreach( $this->OtherTargets AS $t => $targets ) {
282: if ( ! preg_match( '/^\s*$/', $targets ) ) {
283: $list .= ( $list == "" ? "" : ", " ) . $targets;
284: }
285: }
286:
287: return $list;
288: }
289:
290: 291: 292: 293: 294:
295: function _BuildWhereClause($overwrite_values=false) {
296: $where = "";
297: foreach( $this->Keys AS $k => $v ) {
298:
299: if ( $overwrite_values ) $this->Values->{$k} = $v;
300:
301: $where .= ( $where == '' ? 'WHERE ' : ' AND ' );
302: $where .= $k . '=' . AwlQuery::quote($v);
303: }
304:
305: if ( isset($this->OtherWhere) && is_array($this->OtherWhere) ) {
306: foreach( $this->OtherWhere AS $t => $and_where ) {
307: if ( ! preg_match( '/^\s*$/', $and_where ) ) {
308: $where .= ($where == '' ? 'WHERE ' : ' AND (' ) . $and_where . ')';
309: }
310: }
311: }
312:
313: return $where;
314: }
315:
316: 317: 318: 319: 320: 321:
322: function Set($fname, $fval) {
323: dbg_error_log( "DBRecord", ":Set: %s => %s", $fname, $fval );
324: $this->Values->{$fname} = $fval;
325: return $fval;
326: }
327:
328: 329: 330: 331: 332:
333: function Get($fname) {
334: @dbg_error_log( "DBRecord", ":Get: %s => %s", $fname, $this->Values->{$fname} );
335: return (isset($this->Values->{$fname}) ? $this->Values->{$fname} : null);
336: }
337:
338: 339: 340: 341: 342:
343: function Undefine($fname) {
344: if ( !isset($this->Values->{$fname}) ) return null;
345: $current = $this->Values->{$fname};
346: dbg_error_log( 'DBRecord', ': Unset: %s =was> %s', $fname, $current );
347: unset($this->Values->{$fname});
348: return $current;
349: }
350:
351: 352: 353: 354:
355: function Write() {
356: dbg_error_log( "DBRecord", ":Write: %s record as %s.", $this->Table, $this->WriteType );
357: $sql = sql_from_object( $this->Values, $this->WriteType, $this->Table, $this->_BuildWhereClause(), $this->prefix );
358: $qry = new AwlQuery($sql);
359: return $qry->Exec( "DBRecord", __LINE__, __FILE__ );
360: }
361:
362: 363: 364: 365: 366:
367: function Read() {
368: $i_read_the_record = false;
369: $values = (object) array();
370: $this->EditMode = true;
371: $where = $this->_BuildWhereClause(true);
372: if ( "" != $where ) {
373:
374: $fieldlist = "*";
375:
376: $sql = "SELECT $fieldlist FROM $this->Table $where";
377: $qry = new AwlQuery($sql);
378: if ( $qry->Exec( "DBRecord", __LINE__, __FILE__ ) && $qry->rows() > 0 ) {
379: $i_read_the_record = true;
380: $values = $qry->Fetch();
381: $this->EditMode = false;
382: dbg_error_log( "DBRecord", ":Read: Read %s record from table.", $this->Table, $this->WriteType );
383: }
384: }
385: $this->Values = &$values;
386: $this->WriteType = ( $i_read_the_record ? "update" : "insert" );
387: dbg_error_log( "DBRecord", ":Read: Record %s write type is %s.", $this->Table, $this->WriteType );
388: return $i_read_the_record;
389: }
390: }
391:
392: