git.sur5r.net Git - bacula/bacula/blob - gui/baculum/framework/Data/Common/Sqlite/TSqliteMetaData.php

   1 <?php
   2 /**
   3  * TSqliteMetaData class file.
   4  *
   5  * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
   6  * @link https://github.com/pradosoft/prado
   7  * @copyright Copyright &copy; 2005-2016 The PRADO Group
   8  * @license https://github.com/pradosoft/prado/blob/master/COPYRIGHT
   9  * @package System.Data.Common.Sqlite
  10  */
  11
  12 /**
  13  * Load the base TDbMetaData class.
  14  */
  15 Prado::using('System.Data.Common.TDbMetaData');
  16 Prado::using('System.Data.Common.Sqlite.TSqliteTableInfo');
  17
  18 /**
  19  * TSqliteMetaData loads SQLite database table and column information.
  20  *
  21  * @author Wei Zhuo <weizho[at]gmail[dot]com>
  22  * @package System.Data.Commom.Sqlite
  23  * @since 3.1
  24  */
  25 class TSqliteMetaData extends TDbMetaData
  26 {
  27         /**
  28          * @return string TDbTableInfo class name.
  29          */
  30         protected function getTableInfoClass()
  31         {
  32                 return 'TSqliteTableInfo';
  33         }
  34
  35         /**
  36          * Quotes a table name for use in a query.
  37          * @param string $name table name
  38          * @return string the properly quoted table name
  39          */
  40         public function quoteTableName($name)
  41         {
  42                 return parent::quoteTableName($name, "'", "'");
  43         }
  44
  45         /**
  46          * Quotes a column name for use in a query.
  47          * @param string $name column name
  48          * @return string the properly quoted column name
  49          */
  50         public function quoteColumnName($name)
  51         {
  52                 return parent::quoteColumnName($name, '"', '"');
  53         }
  54
  55         /**
  56          * Quotes a column alias for use in a query.
  57          * @param string $name column alias
  58          * @return string the properly quoted column alias
  59          */
  60         public function quoteColumnAlias($name)
  61         {
  62                 return parent::quoteColumnAlias($name, '"', '"');
  63         }
  64
  65         /**
  66          * Get the column definitions for given table.
  67          * @param string table name.
  68          * @return TPgsqlTableInfo table information.
  69          */
  70         protected function createTableInfo($tableName)
  71         {
  72                 $tableName = str_replace("'",'',$tableName);
  73                 $this->getDbConnection()->setActive(true);
  74                 $table = $this->getDbConnection()->quoteString($tableName);
  75                 $sql = "PRAGMA table_info({$table})";
  76                 $command = $this->getDbConnection()->createCommand($sql);
  77                 $foreign = $this->getForeignKeys($table);
  78                 $index=0;
  79                 $columns=array();
  80                 $primary=array();
  81                 foreach($command->query() as $col)
  82                 {
  83                         $col['index'] = $index++;
  84                         $column = $this->processColumn($col, $foreign);
  85                         $columns[$col['name']] = $column;
  86                         if($column->getIsPrimaryKey())
  87                                 $primary[] = $col['name'];
  88                 }
  89                 $info['TableName'] = $tableName;
  90                 if($this->getIsView($tableName))
  91                         $info['IsView'] = true;
  92                 if(count($columns)===0)
  93                         throw new TDbException('dbmetadata_invalid_table_view', $tableName);
  94                 $class = $this->getTableInfoClass();
  95                 $tableInfo = new $class($info,$primary,$foreign);
  96                 $tableInfo->getColumns()->copyFrom($columns);
  97                 return $tableInfo;
  98         }
  99
 100         /**
 101          * @param string table name.
 102          * @return boolean true if the table is a view.
 103          */
 104         protected function getIsView($tableName)
 105         {
 106                 $sql = 'SELECT count(*) FROM sqlite_master WHERE type="view" AND name= :table';
 107                 $this->getDbConnection()->setActive(true);
 108                 $command = $this->getDbConnection()->createCommand($sql);
 109                 $command->bindValue(':table', $tableName);
 110                 return intval($command->queryScalar()) === 1;
 111         }
 112
 113         /**
 114          * @param array column information.
 115          * @param array foreign key details.
 116          * @return TSqliteTableColumn column details.
 117          */
 118         protected function processColumn($col, $foreign)
 119         {
 120                 $columnId = $col['name']; //use column name as column Id
 121
 122                 $info['ColumnName'] = '"'.$columnId.'"'; //quote the column names!
 123                 $info['ColumnId'] = $columnId;
 124                 $info['ColumnIndex'] = $col['index'];
 125
 126                 if($col['notnull']!=='99')
 127                         $info['AllowNull'] = true;
 128
 129                 if($col['pk']==='1')
 130                         $info['IsPrimaryKey'] = true;
 131                 if($this->isForeignKeyColumn($columnId, $foreign))
 132                         $info['IsForeignKey'] = true;
 133
 134                 if($col['dflt_value']!==null)
 135                         $info['DefaultValue'] = $col['dflt_value'];
 136
 137                 $type = strtolower($col['type']);
 138                 $info['AutoIncrement'] = $type==='integer' && $col['pk']==='1';
 139
 140                 $info['DbType'] = $type;
 141                 $match=array();
 142                 if(is_int($pos=strpos($type, '(')) && preg_match('/\((.*)\)/', $type, $match))
 143                 {
 144                         $ps = explode(',', $match[1]);
 145                         if(count($ps)===2)
 146                         {
 147                                 $info['NumericPrecision'] = intval($ps[0]);
 148                                 $info['NumericScale'] = intval($ps[1]);
 149                         }
 150                         else
 151                                 $info['ColumnSize']=intval($match[1]);
 152                         $info['DbType'] = substr($type,0,$pos);
 153                 }
 154
 155                 return new TSqliteTableColumn($info);
 156         }
 157
 158         /**
 159          *
 160          *
 161          * @param string quoted table name.
 162          * @return array foreign key details.
 163          */
 164         protected function getForeignKeys($table)
 165         {
 166                 $sql = "PRAGMA foreign_key_list({$table})";
 167                 $command = $this->getDbConnection()->createCommand($sql);
 168                 $fkeys = array();
 169                 foreach($command->query() as $col)
 170                 {
 171                         $fkeys[$col['table']]['keys'][$col['from']] = $col['to'];
 172                         $fkeys[$col['table']]['table'] = $col['table'];
 173                 }
 174                 return count($fkeys) > 0 ? array_values($fkeys) : $fkeys;
 175         }
 176
 177         /**
 178          * @param string column name.
 179          * @param array foreign key column names.
 180          * @return boolean true if column is a foreign key.
 181          */
 182         protected function isForeignKeyColumn($columnId, $foreign)
 183         {
 184                 foreach($foreign as $fk)
 185                 {
 186                         if(in_array($columnId, array_keys($fk['keys'])))
 187                                 return true;
 188                 }
 189                 return false;
 190         }
 191
 192         /**
 193          * Returns all table names in the database.
 194          * @param string $schema the schema of the tables. This is not used for sqlite database.
 195          * @return array all table names in the database.
 196          */
 197         public function findTableNames($schema='')
 198         {
 199                 $sql="SELECT DISTINCT tbl_name FROM sqlite_master WHERE tbl_name<>'sqlite_sequence'";
 200                 return $this->getDbConnection()->createCommand($sql)->queryColumn();
 201         }
 202 }