diff options
| -rw-r--r-- | MainController.php | 59 | ||||
| -rw-r--r-- | MainDb.php | 13 |
2 files changed, 51 insertions, 21 deletions
diff --git a/MainController.php b/MainController.php index aae6d69..a7542d4 100644 --- a/MainController.php +++ b/MainController.php @@ -33,6 +33,12 @@ class MainController private $table; // corresponding Db table private $primary_key; // name of primary key column + private $sql_select = 'SELECT %s FROM %s %s %s'; + + private $sql_insert = 'INSERT INTO %s (%s) VALUES (%s)'; + private $sql_update = 'UPDATE %s SET %s'; + private $sql_delete = 'DELETE FROM %s WHERE %s.%s = %s'; + //// // create controller object function __construct($where = NULL) @@ -42,9 +48,9 @@ class MainController $this->class = get_class($this); $this->table = static::TableName; - // table name not defined, default to class name + // table name not defined, default to plural class name if (!$this->table) { - $this->table = strtolower($this->class); + $this->table = strtolower($this->class) . 's'; } $this->mainView = new MainView(); @@ -88,23 +94,37 @@ class MainController $driver = MainApp::conf('db.driver'); switch ($driver) { + case 'pgsql': + $sql = "SELECT column_name + FROM INFORMATION_SCHEMA.COLUMNS + WHERE table_name = '{$this->table}'"; + + $schema = MainDb::fetch($sql); + + foreach( $schema as $s ) { + $this->data[$s['column_name']] = ''; + } + + break; + case 'sqlite': $sql = 'PRAGMA TABLE_INFO('.$this->table.')'; $schema = MainDb::fetch($sql); foreach ($schema as $s) { $this->data[$s['name']] = ''; } - return $this; break; - - default: + + case 'mysql': $schema = MainDb::fetch('DESCRIBE `'.$this->table.'`'); foreach ($schema as $s) { $this->data[$s['Field']] = $s['Default']; } - return $this; break; } + + return $this; + } /** @@ -153,12 +173,12 @@ class MainController $op = '='; } - $tmp = (empty($q)) ? ' WHERE ' : ' AND '; - $q .= $tmp . "`{$col}`" . ' ' . $op . ' ?'; + $tmp = (empty($q)) ? 'WHERE ' : 'AND'; + $q .= sprintf(' %s %s %s ?', $tmp, $col, $op); } } else { - $q = "WHERE `{$this->table}`.`id` = ?"; + $q = "WHERE {$this->table}.id = ?"; $values = $where; } @@ -174,7 +194,7 @@ class MainController if ($sort) { list($col, $dir) = explode(' ', $sort); - $sort = "ORDER BY `{$this->table}`.`$col` $dir"; + $sort = "ORDER BY {$this->table}.{$col} $dir"; } if (!$sort && $i_sort) { @@ -183,16 +203,16 @@ class MainController if ($i_select) { list($select_col, $args) = explode(",", $i_select); - $select .= ", $args AS `{$select_col}`"; + $select .= ", $args AS {$select_col}"; } - $sql = "SELECT $select FROM `{$this->table}` $q $sort"; + $this->sql_select = sprintf($this->sql_select, $select, $this->table, $q, $sort); if ($limit) { - $sql .= " LIMIT $limit"; + $this->sql_select .= " LIMIT $limit"; } - $result = MainDb::fetch($sql, $values); + $result = MainDb::fetch($this->sql_select, $values); if (empty($result)) { return $this->data = array(); @@ -219,7 +239,7 @@ class MainController // @return void private function find_children($row, $i) { - $id = $row['id']; // ID of the parent + $id = $row['id']; // ID of this parent $fk = explode(',', static::HasMany); if (empty($fk[0])) { @@ -234,11 +254,11 @@ class MainController $table = ($child::TableName) ? $child::TableName : $child; if ($ob = $child::OrderBy) { - $order_by = sprintf('ORDER BY `%s`.%s', $table, $ob); + $order_by = sprintf('ORDER BY %s.%s', $table, $ob); } if ($gb = $child::GroupBy) { - $group_by = sprintf('GROUP BY `%s`.%s', $table, $gb); + $group_by = sprintf('GROUP BY %s.%s', $table, $gb); } // determine nature of relationship @@ -252,9 +272,8 @@ class MainController $sql .= ', ' . $child::TableSelect; } - $sql .= " FROM `$table` WHERE `{$this->table}_id` = ? - $group_by - $order_by"; + $pk = sprintf('%s_id', strtolower(get_class($this))); + $sql .= " FROM $table WHERE {$pk} = ? $group_by $order_by"; if (in_array($this->table, $many_to_many)) // m/n { @@ -40,9 +40,18 @@ class MainDb // always use UTF-8? self::$instance->query('SET NAMES utf8'); break; + case 'pgsql': + self::$instance = new PDO( + "{$c['db.driver']}:host={$c['db.host']};dbname={$c['db.name']}", + $c['db.username'], $c['db.password'], array(PDO::ATTR_PERSISTENT => true) + ); + break; case 'sqlite': self::$instance = new PDO('sqlite:'.$c['db.path']); break; + default: + exit('Unknown db driver: ' . $c['db.driver']); + break; } } catch (PDOException $e) { @@ -166,8 +175,10 @@ class MainDb if (is_array($data)) { $args = $data; } - $stmt = self::query($sql, $args) or exit(MainApp::error('Error executing query '.$sql)); + + $stmt = self::query($sql, $args) or exit(MainApp::error('Error executing query: '.$sql)); return $stmt->fetchAll(PDO::FETCH_ASSOC); + } //// |