1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
|
<?php
// TODO
// Improve caching to include anything constructed from the DB
// Fall back to multi key if single key didn't work (so unique key can be null)
// consider replacing the one sql_col class with a bunch of child classes for different types (will work well with defaults() when 5.3.0 comes out)
// make sure things really work with enum
// replace the functionality of hasattrs and getattr somehow - more complex custom queries than refers_to
// learn about MySQL indices and see if I can use them better (AKA multi_key but SQL-supported)
// Make the from-SQL init more polished, take account of things after the last ) like ENGINE and DEFAULT CHARSET
// Make from-SQL init and other stuff support UNIQUE KEY with a name different than the column's (currently ignored)
// Remove support for 'signed' and print notices so I can correct my classes to start using unsigned when necessary
// Consider removing the array-based init and only allowing SQL-based (with some sort of file-based cache so we don't have to init every time, and of course it auto-updated based on mtime)
// Support \-quoting of ' and maybe also " in SQL-based init (write general function given a string to return the part that is one word in SQL?)
// store cache as static info in the proper class instead of in a global class that gets copied by reference into every instance (waiting for PHP 5.3.0)
// Take account of http://dev.mysql.com/doc/refman/5.1/en/silent-column-changes.html
// Put sql_col in its own file
// Allow using different DBs and even different connections for each class/table
// Make sure functions are in the proper order, etc. and consider using standardized PHPDoc commenting, which would be good for SoC
// Clean up the variables I'm using which then can't be column names (after 5.3.0 this will still be an issue because you can't have a static and a member var with the same name)
// Make sql_row_obj::debug a real debug function which is silent by default but can be turned on or replaced by an external function
// Get rid of any dependencies on other PHP code of mine
// Experiment with PDOStatement->fetchObject() (file:///usr/share/doc/php-docs-20071125-r2/en/html/function.PDOStatement-fetchObject.html)
// Change the default fetch() type from BOTH to either assoc or num, check for places that need to manually change it
// Probably a good idea to accomplish some of this with PDO::ATTR_STATEMENT_CLASS (file:///usr/share/doc/php-docs-20071125-r2/en/html/function.PDO-setAttribute.html)
// TODO Don't use rowCount - PDO docs say it doesn't work with all backends
abstract class sql_row_obj { // If the name of this class changes, it must be updated where is_subclass_of() is found
// PDO object to use for queries
protected static $pdo;
// The static cache so each class is only initialized once per page-load (maybe this should really be once per class edit, saved in a file somewhere...)
private static $cache=array(), $table_cache=array(), $ref_cache=array();
// These are input by the source class (auto-filled by cache after init)
protected $table, $columns, $primary_key; // TODO $unique_keys
// These are loaded from the static cache
private $auto_increment, $num_key, $misc_key;
// These are run-time variables
private $db_values=array(), $values;
// Sets the PDO object to use
public static function set_pdo_obj(&$obj) {
$obj->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
self::$pdo=$obj;
}
// Checks if set_pdo_obj has been called yet and complains if it hasn't
private static function check_pdo_obj() {
if (!is_object(self::$pdo)) {
throw new Exception('sql_row_obj::set_pdo_obj() has not been called to set the PDO object to use for queries.');
}
}
// Makes an SQL query using $sql and returns the resulting object
private static function sql_query($q) {
self::check_pdo_obj();
return self::$pdo->query($q);
}
public static function sql_quote_string($s) {
self::check_pdo_obj();
$r=self::$pdo->quote($s); // We're not supposed to use this because PDO::ODBC doesn't support it
return $r===false?"'".str_replace("'", "''", $s)."'":$r; # If the PDO call failed, at least fix single quotes
}
// Handles debug data TODO something
private static function debug($type, $text) {
}
// Initializes the class by converting the columns given into sql_col objects and caching certain other info in the static cache array
// TODO should be static (5.3.0)
private function init_from_array() {
// Converts from array format to object format so classes can be written in array format
foreach ($this->columns as $name => $col) {
if (is_array($col)) {
$col=new sql_col($col);
$this->columns[$name]=$col; // This is a copy, not a reference, so we set it back to the array also
}
if ($col->auto_increment) {
$this->auto_increment=$name;
}
}
}
private function init_constructors() {
// Fills num and misc constructors with other unique columns
// TODO check where this is called and see if we really need this first check
// TODO fix this for multi-column keys - as of now, it's dangerous!
if (!isset($this->num_key) || !isset($this->misc_key)) {
foreach ($this->columns as $name => $col) {
if ($col->unique) {
if ($col->is_numeric()) {
if (!isset($this->num_key)) {
$this->num_key=$name;
if (isset($this->misc_key)) {
break;
}
}
} else {
if (!isset($this->misc_key)) {
$this->misc_key=$name;
if (isset($this->num_key)) {
break;
}
}
}
}
}
}
}
// Inserts generated data to the cache so we won't have to do this again
private function cache_me() {
self::$cache['table'][get_class($this)]=&$this->table;
self::$cache['columns'][get_class($this)]=&$this->columns;
self::$cache['primary_key'][get_class($this)]=&$this->primary_key;
self::$cache['auto_increment'][get_class($this)]=&$this->auto_increment;
self::$cache['num_key'][get_class($this)]=&$this->num_key;
self::$cache['misc_key'][get_class($this)]=&$this->misc_key;
// Inserts this class into a lookup-table if necessary so we know which tables are serviced by which classes
if (!isset(self::$table_cache[$this->table])) {
self::$table_cache[$this->table]=get_class($this);
}
}
// Initializes this class based in the sql table given by $this->table
// TODO should be static (5.3.0)
private function init_from_create() {
$c=self::sql_query('SHOW CREATE TABLE `'.$this->table.'`')->fetchColumn(1);
$c=explode("\n", $c); // If we split by commas, things could get real messed up so we rely on \n
unset($c[count($c)-1]); # Removes closing ), but also some options like engine and default charset
unset($c[0]); # Removes CREATE TABLE `x`
foreach ($c as $line) {
$line=trim($line);
$line=rtrim($line, ',');
if ($line[0]=='`') {
$col=new sql_col();
$name=substr($line, 1, strpos($line, '`', 1)-1);
$line=trim(substr($line, strlen($name)+2));
$this->columns[$name]=new sql_col($line);
} else {
if (strpos($line, 'PRIMARY KEY') === 0) {
$line=substr($line, strpos($line, '(')+1);
$line=substr($line, 0, strrpos($line, ')'));
$line=explode(',', $line);
foreach ($line as $col) {
$col=trim($col);
$col=trim($col, '`');
$this->primary_key[]=$col;
}
} elseif (strpos($line, 'UNIQUE KEY') === 0) {
// TODO process multi-column unique keys, multiple unique keys correctly (steal the text processing side from PRIMARY KEY above)
$line=explode(' ', $line, 3);
$line=trim($line[2]);
$line=substr($line, strrpos($line, '('));
$u=substr($line, 2, strlen($line)-4);
$this->columns[$u]->unique=true; // If this is boolean true, we just add UNIQUE to the col def., if a string, we add a named UNIQUE KEY at the end (TODO)
}
}
}
}
function __construct() {
if (!isset($this->table)) {
throw new Exception('Class '.get_class($this).' doesn\'t provide a table name ($table)');
}
// If the class is in the cache already, bring over the cached data by reference,
// if not, initialize the class and add its data to the cache
if (isset(self::$cache['columns'][get_class($this)])) {
//$this->table=&self::$cache['table'][get_class($this)]; // Kinda pointless
$this->columns=&self::$cache['columns'][get_class($this)];
$this->primary_key=&self::$cache['primary_key'][get_class($this)]; // Not really necessary, it's not changed by init
$this->auto_increment=&self::$cache['auto_increment'][get_class($this)];
$this->num_key=&self::$cache['num_key'][get_class($this)];
$this->misc_key=&self::$cache['misc_key'][get_class($this)];
} else {
if (isset($this->columns)) {
$this->init_from_array();
} else {
$this->init_from_create();
}
$this->init_constructors();
$this->cache_me();
}
// Load default values for all columns
foreach ($this->columns as $name => $col) {
$this->values[$name]=$col->default;
}
// No arguments to constructor - leave values as defaults
if (func_num_args() == 0) {
self::debug(get_class($this), 'null constructor');
return;
}
// One argument - this may be one of four things:
// 1. An array directly from an sql result fetch() (in associative form - TODO accept numeric index),
// 2. The primary key if the primary key has one column
// 3. An integer intended to be used to fetch the object from the DB by unique numeric column
// 4. A string intended to be used to fetch the object from the DB by unique column
// 5. Just the value to be put into the first column (handled like any arbitrary number of values)
if (func_num_args() == 1) {
if (is_array(func_get_arg(0))) {
// We're assuming that the array comes from sql_result->fetch() - assoc form, so we do from_array(from_db=true), but this may not be wise
$this->from_array(func_get_arg(0), true);
self::debug(get_class($this), 'array constructor');
return;
}
$arg=func_get_arg(0);
if (isset($this->primary_key) && count($this->primary_key) == 1) {
if (is_numeric($arg) && $this->columns[$this->primary_key[0]]->is_numeric() || !$this->columns[$this->primary_key[0]]->is_numeric()) {
self::debug(get_class($this), 'primary key constructor ('.$this->primary_key[0].')');
$r=self::sql_query('SELECT * FROM `'.$this->table.'` WHERE `'.$this->primary_key[0].'`='.$this->columns[$this->primary_key[0]]->sql_value($arg));
if ($r->rowCount() == 0) {
throw new Exception(get_class($this).' object constructed with single argument ('.$arg.') but found no rows with this in the `'.$this->primary_key[0].'` column (PRIMARY_KEY).');
} else {
$this->from_array($r->fetch(), true);
return;
}
}
}
if (is_numeric($arg) && isset($this->num_key)) {
self::debug(get_class($this), 'numeric constructor');
$r=self::sql_query('SELECT * FROM `'.$this->table.'` WHERE `'.$this->num_key.'`='.$this->columns[$this->num_key]->sql_value($arg));
if ($r->rowCount() == 0) {
throw new Exception(get_class($this).' object constructed with single numeric argument ('.$arg.') but found no rows with this in the `'.$this->num_key.'` column (UNIQUE numeric).');
} else {
$this->from_array($r->fetch(), true);
return;
}
} elseif (isset($this->misc_key)) {
self::debug(get_class($this), 'misc constructor');
$r=self::sql_query('SELECT * FROM `'.$this->table.'` WHERE `'.$this->misc_key.'`='.$this->columns[$this->misc_key]->sql_value($arg));
if ($r->rowCount() == 0) {
throw new Exception(get_class($this).' object constructed with single value ('.$arg.') but found no rows with this in the `'.$this->misc_key.'` column (UNIQUE non-numeric).');
} else {
$this->from_array($r->fetch(), true);
return;
}
}
// We have a table that requires multiple columns to identify a given row
// and we have the right number of arguments to expect that's what's happening
} elseif (isset($this->primary_key) && func_num_args() == count($this->primary_key)) {
self::debug(get_class($this), 'primary key constructor ('.implode(', ', $this->primary_key).')');
for ($i=0; $i<func_num_args(); $i++) {
$this->db_values[$this->primary_key[$i]]=func_get_arg($i);
}
$r=self::sql_query('SELECT * FROM `'.$this->table.'` WHERE '.$this->sql_id());
if ($r->rowCount() == 0) {
throw new Exception(get_class($this).' object constructed with '.func_num_args().' values '.$this->sql_id().' but no rows were found (PRIMARY KEY).');
} else {
$this->from_array($r->fetch(), true);
return;
}
}
// If we haven't loaded values from the DB or an array, use each argument to fill one field, in order
if (func_num_args() > count($this->columns)) {
throw new Exception('new '.get_class($this).'() called with too many arguments (accepts up to '.count($this->columns).' but got '.func_num_args().'.');
}
$i=0;
foreach ($this->columns as $name => $col) {
$this->values[$name]=func_get_arg($i);
if (++$i == func_num_args()) {
break;
}
}
}
// Takes an array and populates the object with values from the array
// If $from_db is set true, fills db_values column
// NOTE: if from_db is true, every column must have a value in the array, even if null
// Returns the number of columns filled, or false if not given an array
function from_array($array, $from_db=false) {
if (!is_array($array)) {
return false;
}
$cols_filled=0;
foreach ($this->columns as $name => $col) {
if (array_key_exists($name, $array)) {
$this->values[$name]=$array[$name];
if ($from_db) {
$this->db_values[$name]=$array[$name];
}
$cols_filled++;
} elseif ($from_db) {
throw new Exception('from_array called with $from_db=true, but column '.$name.' not specified.');
}
}
return $cols_filled;
}
// Writes the object's data to the database, either by UPDATE or INSERT
function write() {
$q=($this->is_in_db()?'UPDATE':'INSERT INTO').' `'.$this->table.'` SET ';
$i=0; // Number of columns we've set so far
$to_change=array();
foreach ($this->columns as $name => $col) {
if (!array_key_exists($name, $this->db_values) || $this->values[$name] !== $this->db_values[$name]) {
$sql_val=$col->sql_value($this->values[$name]);
if (is_array($sql_val)) {
//echo $name."\n";
print_r($sql_val);
throw new Exception('Failed to write '.get_class($this).' object to table `'.$this->table.'` because column `'.$name.'` ('.$sql_val['description'].') '.$sql_val['reason'].' (value: '.$sql_val['value'].')');
}
if (++$i > 1) {
$q.=', ';
}
$q.='`'.$name.'`='.$sql_val;
}
}
if ($i == 0) {
self::debug(get_class($this), '<span style="color: red">write() with no modifications</span>');
return;
}
if ($this->is_in_db()) {
$q.=' WHERE '.$this->sql_id();
}
$r=self::sql_query($q);
// Fill auto-increment column if it was null before the query
if (isset($this->auto_increment) && $this->__get($this->auto_increment) === null) {
$this->__set($this->auto_increment, self::$pdo->lastInsertId());
}
// We've just written the current values to the db, so it stands to reason they are now the values in the db
$this->values_to_db_values();
return $r;
}
// (Re-)Loads data from the database
function load() {
if ($this->is_in_db()) {
$r=self::sql_query('SELECT * FROM `'.$this->table.'` WHERE '.$this->sql_id());
$this->from_array($r->fetch(), true);
}
}
// Deletes this row from the database and clears db_values array
function delete() {
if ($this->is_in_db()) {
self::sql_query('DELETE FROM `'.$this->table.'` WHERE '.$this->sql_id());
$this->db_values=array();
} else {
throw new Exception('Tried to delete '.get_class($this).' object that was not in the database.');
}
}
// Returns an SQL clause that will single out the row represented by this object
// Order of choice is:
// 1. Primary key
// 2. Numeric key
// 3. Misc. key
// 4. Multi key
function sql_id() {
if (isset($this->primary_key)) {
$id=count($this->primary_key)>1?'(':'';
$i=0;
foreach ($this->primary_key as $name) {
if (++$i > 1) {
$id.=' AND ';
}
$id.='`'.$name.'`='.$this->columns[$name]->sql_value($this->db_values[$name]);
}
$id.=count($this->primary_key)>1?')':'';
return $id;
} elseif (isset($this->num_key) && $this->db_values[$this->num_key] !== null) {
return '`'.$this->num_key.'`='.$this->columns[$this->num_key]->sql_value($this->db_values[$this->num_key]);
} elseif (isset($this->misc_key) && $this->db_values[$this->misc_key] !== null) {
return '`'.$this->misc_key.'`='.$this->columns[$this->misc_key]->sql_value($this->db_values[$this->misc_key]);
} else {
throw new Exception('Tried to generate SQL to select unique '.get_class($this).' object, but there were no available unique indicators (primary key, numeric, misc).');
}
}
// If no argument is given, returns whether this object represents a row that is currently in the database.
// If an argument that evaluates to false is given, clears the db_values array, causing it to be known as not in the db.
// if an argument that evaluates to true is given, fills the db_values array with values fromt the values array.
function is_in_db() {
if (func_num_args() == 0) {
// db_values being populated indicates that it is in the database
return (count($this->db_values) > 0);
}
if ($set) {
$this->values_to_db_values();
} else {
$this->db_values=array();
}
}
// Sets cached database values to current values - used in is_in_db(true) and in write()
function values_to_db_values() {
foreach ($this->columns as $name => $col) {
$this->db_values[$name]=$this->values[$name];
}
}
// Returns the SQL code to drop the table from the database
// TODO this should be static (probably needs 5.3.0)
function drop_table($ifexists=true) {
return 'DROP TABLE '.($ifexists?'IF EXISTS ':'').'`'.$this->table.'`';
}
// Returns the SQL code to create the table represented by this class
// TODO this should be static (probably needs 5.3.0)
function create_table($ifnotexists=true) {
$q='CREATE TABLE '.($ifnotexists?'IF NOT EXISTS ':'').'`'.$this->table.'` ('."\n";
$i=1;
$rows=array();
foreach ($this->columns as $name => $col) {
// TODO for the , at EOL, this checks for PRIMARY KEY to indicate that there should be an extra
$rows[]="\t".'`'.$name.'` '.$col->describe();
}
if (isset($this->primary_key)) {
$rows[]="\t".'PRIMARY KEY (`'.implode('`, `', $this->primary_key).'`)';
}
foreach ($this->columns as $name => $col) {
if ($col->unique && is_string($col->unique)) {
$rows[]="\t".' UNIQUE KEY `'.$col->unique.'` (`'.$col->unique.'`)';
}
}
$q.=implode(",\n", $rows)."\n";
$q.=')';
return $q;
}
function to_php() {
$r="class ".get_class($this)." extends ".get_parent_class($this)." {\n\tprotected \$table='".$this->table."', ";
if (isset($this->primary_key)) {
$r.='$primary_key=array(\''.implode('\', \'', $this->primary_key).'\'), ';
}
$r.="\$columns=array(\n";
$i=0;
foreach($this->columns as $name => $col) {
$i++;
$r.="\t\t'$name' => array (\n";
$cols=$col->to_array();
$j=0;
foreach ($cols as $name => $val) {
$j++;
if (is_bool($val)) {
$val=$val?'true':'false';
} elseif (!is_numeric($val)) {
$val="'".str_replace("'", "\'", $val)."'";
}
$r.="\t\t\t'$name' => $val".($j<count($cols)?',':'')."\n";
}
$r.="\t\t)".($i<count($this->columns)?',':'')."\n";
}
$r.="\n\t);\n}\n";
return $r;
}
//////// IMPORTANT //////////
//
// For __set(), __get(), __isset(), __unset(), and __call():
// See PHP Language Reference -> Classes and Objects (PHP 5) -> Overloading
//
/////////////////////////////
// Magic function that sets the value of the columns
function __set($name, $value) {
if (array_key_exists($name, $this->columns)) {
$this->values[$name]=$value;
} else {
throw new Exception('Tried to set undefined property \''.$name.'\' of '.get_class($this).' object to \''.$value.'\'.');
}
}
// Magic function that gets the value of the columns
function __get($name) {
if (array_key_exists($name, $this->columns)) {
return $this->values[$name];
} else {
throw new Exception('Tried to get undefined property \''.$name.'\' of '.get_class($this).' object.');
}
}
// Magic function that checks if the value of a column is set (not null)
function __isset($name) {
if (array_key_exists($name, $this->columns)) {
return ($this->values[$name] !== null);
} else {
throw new Exception('Tried to isset() on undefined property \''.$name.'\' of '.get_class($this).' object.');
}
}
// Magic function that gets angry if you try to unset a column, because they can be null, but not deleted
function __unset($name) {
throw new Exception('Tried to unsset() property \''.$name.'\' of '.get_class($this).' object, but sql_row_obj values may not be unset. Try '.get_class($this).'->'.$name.'=null.');
}
// Magic function that's used to forward get_xxx() calls to get('xxx')
function __call($name, $args) {
if (substr($name, 0, 4) == 'get_') {
$col=substr($name, 4);
return $this->get(substr($name, 4));
} else {
throw new Exception('Undefined call to '.get_class($this).'->'.$name.'().');
}
}
// Fetches objects for 'refers to' columns (column given by $name)
function &get($name) {
// TODO in 5.3.0, this can be a static variable in each class instead of array[$class]
if (isset(self::$ref_cache[get_class($this)][$name][$this->__get($name)])) {
return self::$ref_cache[get_class($this)][$name][$this->__get($name)];
}
if (!isset($this->columns[$name])) {
throw new Exception('Tried to fetch object for `'.$name.'` column of '.get_class($this).' object, but that column does not exist.');
}
$col=$this->columns[$name];
if (!isset($col->refers_to)) {
throw new Exception('Tried to fetch object for `'.$name.'` column of '.get_class($this).' object, but that column does not refer to another table row.');
}
if (!$this->__isset($name)) {
$null=null;
return $null;
}
list($reftable, $refcol)=explode('.', $col->refers_to, 2); // Should be refclass also
$obj=self::table_to_obj($reftable);
if ($obj === null) {
throw new Exception ('Tried to fetch object for `'.$name.'` column of '.get_class($this).' object, but there is no class available to handle the `'.$reftable.'` table.');
}
$r=self::sql_query('SELECT * FROM `'.$reftable.'` WHERE `'.$refcol.'`='.$col->sql_value($this->__get($name)));
if ($r->rowCount() == 0) {
return null;
} elseif ($r->rowCount() == 1) {
$obj->from_array($r->fetch());
self::$ref_cache[get_class($this)][$name][$this->__get($name)]=&$obj;
return $obj;
} else {
throw new Exception('Fetching '.$refclass.' object for `'.$name.'` column of `'.$this->table.'` table based on `'.$refcol.'`='.$col->sql_value($this->__get($name)).' and expected one row but got '.$r->rowCount().'.');
}
}
// Returns an instance of whichever class extends sql_row_obj for the given table, or null if none found
// TODO this shouldn't have to instantiate every class, we need 5.3.0 so this can be fully static
public static function &table_to_obj($table) {
if (isset(self::$table_cache[$table])) {
$obj=new self::$table_cache[$table]();
return $obj;
}
foreach (get_declared_classes() as $class) {
if (is_subclass_of($class, 'sql_row_obj')) {
$r=new ReflectionClass($class);
if (!$r->isInstantiable()) continue;
unset($r);
$obj=new $class();
if ($obj->table == $table) {
self::$table_cache[$table]=$class;
return $obj;
}
}
}
$null=null;
return $null;
}
// Same as table_to_class, but returns the name of the class, not an instance
public static function table_to_class($table) {
$obj=self::table_to_obj($table);
return $obj===null?null:get_class($obj);
}
}
// This class represents a column in an SQL table definition
class sql_col {
public $type, $length, $unsigned=false, $charset='utf8', $collate, $not_null=false, $default=null, $auto_increment=false, $unique=false, $comment, $refers_to;
private static $defaults;
public function __construct($array=null) {
if (is_array($array)) {
// TODO this should probably be a switch inside a foreach
if (isset($array['type']))
$this->type=strtoupper($array['type']); // To allow for lower-case types
if (isset($array['length']))
$this->length=$array['length'];
if (isset($array['unsigned']))
$this->unsigned=$array['unsigned']?true:false; // To allow for non-booleans that can evaluate to boolean
if (isset($array['charset']))
$this->charset=$array['charset'];
if (isset($array['collate']))
$this->collate=$array['collate'];
if (isset($array['not_null']))
$this->not_null=$array['not_null']?true:false;
if ($this->is_numeric() && isset($array['auto_increment']))
$this->auto_increment=$array['auto_increment']?true:false;
if (isset($array['default']))
$this->default=$array['default'];
elseif ($this->not_null) { // TODO add the default non-null val for the rest of types (http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html)
if ($this->is_numeric() && !$this->auto_increment)
$this->default=0;
elseif ($this->type == 'ENUM') {
// $this->default=$this->length; // TODO finish this
} elseif ($this->is_string())
$this->default='';
}
if (isset($array['unique']))
$this->unique=$array['unique']?true:false;
if (isset($array['refers_to']))
$this->refers_to=$array['refers_to'];
} elseif (is_string($array)) {
$line=$array;
list($type, $line)=explode(' ', $line, 2);
if (strpos($type, '(') !== false) {
$length=substr($type, strpos($type, '('));
$type=substr($type, 0, strlen($type)-strlen($length));
$length=substr($length, 1, strlen($length)-2);
$this->length=$length;
}
$this->type=strtoupper($type);
$opts=explode(' ', $line);
for ($i=0; $i<count($opts); $i++) {
$word=$opts[$i];
switch (strtoupper($word)) {
case 'NOT':
if (strtoupper($opts[++$i]) == 'NULL') {
$this->not_null=true;
} else {
$i--; // We assume it's NULL and backtrack otherwise
}
break;
case 'NULL':
$this->not_null=false;
break;
case 'UNSIGNED':
$this->unsigned=true;
break;
case 'AUTO_INCREMENT':
$this->auto_increment=true;
break;
case 'CHARACTER':
if (strtoupper($opts[$i+1]) == 'SET') {
$word.=' '.$opts[++$i];
} else {
break;
}
case 'COLLATE':
case 'DEFAULT':
case 'COMMENT':
$string=$opts[++$i];
if ($string == "''") {
$string='';
} elseif (substr($string, 0, 1) == "'") {
// An odd number of ' at the end means an unquoted '
// The $i<count($opts) is just to avoid infinite loops that shouldn't happen
//echo strtoupper($word).' '.$string;
while ( ( strlen($string) - strlen(rtrim($string, '\'')) ) % 2 == 0 && $i<count($opts)) {
$string.=' '.$opts[++$i];
}
$string=str_replace("''", "'", $string);
$string=substr($string, 1, strlen($string)-2);
} elseif (strtoupper($string) == 'NULL') {
$string=null;
}
switch (strtoupper($word)) {
case 'CHARACTER SET':
$this->charset=$string;
break;
case 'COLLATE':
$this->collate=$string;
break;
case 'DEFAULT':
$this->default=$string;
break;
case 'COMMENT':
if (preg_match('/^refers to: ([a-zA-Z0-9_$]+\.[a-zA-Z0-9_$]+)$/', $string, $match)) {
$this->refers_to=$match[1];
} else {
$this->comment=$string;
}
break;
}
break;
}
}
}
}
// True for data types that accept a length parameter, false otherwise
public function has_length() {
switch($this->type) {
case 'VARCHAR':
case 'CHAR':
case 'BIT':
case 'TINYINT':
case 'SMALLINT':
case 'MEDIUMINT':
case 'BIGINT':
case 'INT':
case 'INTEGER':
case 'FLOAT':
case 'DOUBLE':
case 'DOUBLE PRECISION':
case 'DECIMAL':
case 'DEC':
case 'ENUM':
return true;
default:
return false;
}
}
// Returns the length parameter given, or a default length for this column
// TODO make all the defaults smart values, not 10 and 7,3 (find out what MySQL does if left blank)
public function get_length() {
if (isset($this->length)) {
return $this->length;
} else {
switch($this->type) {
case 'VARCHAR':
return 255;
case 'CHAR':
return 1;
case 'TINYINT':
return 3;
case 'BIT':
case 'SMALLINT':
case 'MEDIUMINT':
case 'BIGINT':
case 'INT':
case 'INTEGER':
return 10;
case 'FLOAT':
case 'DOUBLE':
case 'DOUBLE PRECISION':
case 'DECIMAL':
case 'DEC':
return '7,3';
default:
return null;
}
}
}
// True if this is a numeric column, false otherwise
public function is_numeric() {
switch($this->type) {
case 'INT':
case 'INTEGER':
case 'SMALLINT':
case 'TINYINT':
case 'MINIUMINT':
case 'BIGINT':
case 'NUMERIC':
case 'DEC':
case 'DECIMAL':
case 'FLOAT':
case 'REAL':
case 'DOUBLE PRECISION':
return true;
default:
return false;
}
}
// True if this is a text column, false otherwise
public function is_string() {
switch($this->type) {
case 'VARCHAR':
case 'CHAR':
case 'TEXT':
case 'ENUM':
case 'SET':
return true;
default:
return false;
}
}
// Returns the row used to create this column in the CREATE statement
public function describe() {
$d=$this->type.($this->has_length()?'('.$this->get_length().')':'');
if ($this->is_numeric() && $this->unsigned)
$d.=' UNSIGNED';
if ($this->is_string()) {
$d.=' CHARSET '.$this->charset;
if (isset($this->collate))
$d.=' COLLATE '.$this->collate;
}
if ($this->not_null)
$d.=' NOT NULL';
if (isset($this->default))
$d.=' DEFAULT '.$this->sql_value($this->default);
if ($this->is_numeric() && $this->auto_increment)
$d.=' AUTO_INCREMENT';
if ($this->unique === true)
$d.=' UNIQUE';
if (isset($this->comment))
$d.=' COMMENT '.sql_row_obj::sql_quote_string($this->comment);
elseif (isset($this->refers_to))
$d.=' COMMENT '.sql_row_obj::sql_quote_string('refers to: '.$this->refers_to);
return $d;
}
// Returns the array necessary to generate this column using the constructor
public function to_array() {
$me=get_object_vars($this);
$defaults=self::defaults();
$r=array();
foreach ($defaults as $name => $val) {
if ($me[$name] !== $val) {
$r[$name]=$me[$name];
}
}
return $r;
}
// Formats the given value for use in an SQL statement in this column - escapes strings and
// checks that the value is allowed by the column's definition
public function sql_value($value) {
if ($value === null) {
$value='NULL';
if ($this->not_null && !$this->auto_increment) {
$value=$this->sql_value_fail('may not be null', $value);
}
} else {
if ($this->has_length() && strlen($value) > $this->length) {
// TODO warning here about length being over the maximum
// Needs to have a different way of handling things that
// aren't actually lengths, like ENUM
}
if ($this->is_numeric()) {
if (!is_numeric($value)) {
$value=$this->sql_value_fail('must be numeric', sql_row_obj::sql_quote_string($value));
} elseif ($this->unsigned && $value < 0) {
$value=$this->sql_value_fail('is unsigned', $value);
}
} else {
$value=sql_row_obj::sql_quote_string($value);
}
}
return $value;
}
// Helper for sql_value() - returns the reason for failure, the value that failed (both
// passed in by sql_value()) and column description in a nice associative array for error handling.
private function sql_value_fail($reason, $value) { // Just puts together the array for brevity of code
return array('reason' => $reason, 'value' => $value, 'description' => $this->describe());
}
// Returns a (cached) array of the default values of a sql_row instance
private static function &defaults() {
if (!isset(self::$defaults)) {
self::$defaults=get_object_vars(new sql_col());
}
return self::$defaults;
}
}
?>
|