get() public method

See also: Adapter::get
public get ( $sql, $bindings = [] )
Beispiel #1
0
 /**
  * @see QueryWriter::queryRecordRelated
  */
 public function queryRecordRelated($sourceType, $destType, $linkIDs, $addSql = '', $bindings = array())
 {
     $addSql = $this->glueSQLCondition($addSql, QueryWriter::C_GLUE_WHERE);
     list($sourceTable, $destTable, $linkTable, $sourceCol, $destCol) = $this->getRelationalTablesAndColumns($sourceType, $destType);
     $key = $this->getCacheKey(array($sourceType, $destType, implode(',', $linkIDs), $addSql, $bindings));
     if ($this->flagUseCache && ($cached = $this->getCached($destType, $key))) {
         return $cached;
     }
     $inClause = $this->getParametersForInClause($linkIDs, $bindings);
     $sqlFilterStr = '';
     if (count(self::$sqlFilters)) {
         $sqlFilterStr = $this->getSQLFilterSnippet($destType);
     }
     if ($sourceType === $destType) {
         $inClause2 = $this->getParametersForInClause($linkIDs, $bindings, count($bindings));
         //for some databases
         $sql = "\n\t\t\tSELECT\n\t\t\t\t{$destTable}.* {$sqlFilterStr} ,\n\t\t\t\tCOALESCE(\n\t\t\t\tNULLIF({$linkTable}.{$sourceCol}, {$destTable}.id),\n\t\t\t\tNULLIF({$linkTable}.{$destCol}, {$destTable}.id)) AS linked_by\n\t\t\tFROM {$linkTable}\n\t\t\tINNER JOIN {$destTable} ON\n\t\t\t( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} IN ({$inClause}) ) OR\n\t\t\t( {$destTable}.id = {$linkTable}.{$sourceCol} AND {$linkTable}.{$destCol} IN ({$inClause2}) )\n\t\t\t{$addSql}\n\t\t\t-- keep-cache";
         $linkIDs = array_merge($linkIDs, $linkIDs);
     } else {
         $sql = "\n\t\t\tSELECT\n\t\t\t\t{$destTable}.* {$sqlFilterStr},\n\t\t\t\t{$linkTable}.{$sourceCol} AS linked_by\n\t\t\tFROM {$linkTable}\n\t\t\tINNER JOIN {$destTable} ON\n\t\t\t( {$destTable}.id = {$linkTable}.{$destCol} AND {$linkTable}.{$sourceCol} IN ({$inClause}) )\n\t\t\t{$addSql}\n\t\t\t-- keep-cache";
     }
     $bindings = array_merge($linkIDs, $bindings);
     $rows = $this->adapter->get($sql, $bindings);
     $this->putResultInCache($destType, $key, $rows);
     return $rows;
 }
Beispiel #2
0
 /**
  * @see QueryWriter::getColumns
  */
 public function getColumns($table)
 {
     $columnsRaw = $this->adapter->get("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" . $this->esc($table) . "'");
     $columns = array();
     foreach ($columnsRaw as $r) {
         $columns[$r['COLUMN_NAME']] = $r['DATA_TYPE'] . (is_numeric($r['CHARACTER_MAXIMUM_LENGTH']) ? '(' . $r['CHARACTER_MAXIMUM_LENGTH'] . ')' : '');
     }
     return $columns;
 }
Beispiel #3
0
 /**
  * @see QueryWriter::getColumns
  */
 public function getColumns($table)
 {
     $columnsRaw = $this->adapter->get("DESCRIBE " . $this->esc($table));
     $columns = array();
     foreach ($columnsRaw as $r) {
         $columns[$r['Field']] = $r['Type'];
     }
     return $columns;
 }
Beispiel #4
0
 /**
  * @see QueryWriter::getColumns
  */
 public function getColumns($table)
 {
     $table = $this->esc($table, TRUE);
     $columnsRaw = $this->adapter->get("SELECT column_name, data_type FROM information_schema.columns WHERE table_name='{$table}' AND table_schema = ANY( current_schemas( FALSE ) )");
     $columns = array();
     foreach ($columnsRaw as $r) {
         $columns[$r['column_name']] = $r['data_type'];
     }
     return $columns;
 }
Beispiel #5
0
 /**
  * @see QueryWriter::getColumns
  */
 public function getColumns($table)
 {
     $table = $this->esc($table);
     $columnsRaw = $this->adapter->get("SHOW COLUMNS FROM {$table}");
     $columns = array();
     foreach ($columnsRaw as $r) {
         $columns[$r['Field']] = $r['Type'];
     }
     return $columns;
 }
Beispiel #6
0
 /**
  * @see QueryWriter::getColumns
  */
 public function getColumns($table)
 {
     $table = $this->esc($table, TRUE);
     $columnsRaw = $this->adapter->get("PRAGMA table_info('{$table}')");
     $columns = array();
     foreach ($columnsRaw as $r) {
         $columns[$r['name']] = $r['type'];
     }
     return $columns;
 }
Beispiel #7
0
 /**
  * @see QueryWriter::queryTagged
  */
 public function queryTagged($type, $tagList, $all = FALSE, $addSql = '', $bindings = array())
 {
     $assocType = $this->getAssocTable(array($type, 'tag'));
     $assocTable = $this->esc($assocType);
     $assocField = $type . '_id';
     $table = $this->esc($type);
     $slots = implode(',', array_fill(0, count($tagList), '?'));
     $score = $all ? count($tagList) : 1;
     $sql = "\n\t\t\tSELECT {$table}.*, count({$table}.id) FROM {$table}\n\t\t\tINNER JOIN {$assocTable} ON {$assocField} = {$table}.id\n\t\t\tINNER JOIN tag ON {$assocTable}.tag_id = tag.id\n\t\t\tWHERE tag.title IN ({$slots})\n\t\t\tGROUP BY {$table}.id\n\t\t\tHAVING count({$table}.id) >= ?\n\t\t\t{$addSql}\n\t\t";
     $bindings = array_merge($tagList, array($score), $bindings);
     $rows = $this->adapter->get($sql, $bindings);
     return $rows;
 }
Beispiel #8
0
 /**
  * @see QueryWriter::addIndex
  */
 public function addIndex($type, $name, $column)
 {
     $table = $type;
     $table = $this->esc($table);
     $name = preg_replace('/\\W/', '', $name);
     $column = $this->esc($column);
     try {
         foreach ($this->adapter->get("SHOW INDEX FROM {$table} ") as $ind) {
             if ($ind['Key_name'] === $name) {
                 return;
             }
         }
         $this->adapter->exec("CREATE INDEX {$name} ON {$table} ({$column}) ");
     } catch (\Exception $e) {
     }
 }
Beispiel #9
0
 /**
  * @see QueryWriter::addIndex
  */
 public function addIndex($type, $name, $column)
 {
     $table = $type;
     $table = $this->esc($table);
     $name = preg_replace('/\\W/', '', $name);
     $column = $this->esc($column, TRUE);
     try {
         foreach ($this->adapter->get("PRAGMA INDEX_LIST({$table}) ") as $ind) {
             if ($ind['name'] === $name) {
                 return;
             }
         }
         $t = $this->getTable($type);
         $t['indexes'][$name] = array('name' => $column);
         $this->putTable($t);
     } catch (\Exception $exception) {
         //do nothing
     }
 }
Beispiel #10
0
 /**
  * @see QueryWriter::addUniqueIndex
  */
 public function addUniqueIndex($table, $columns)
 {
     $table = $this->esc($table, TRUE);
     sort($columns);
     //else we get multiple indexes due to order-effects
     foreach ($columns as $k => $v) {
         $columns[$k] = $this->esc($v);
     }
     $r = $this->adapter->get("SELECT i.relname AS index_name\n\t\t\tFROM pg_class t,pg_class i,pg_index ix,pg_attribute a\n\t\t\tWHERE t.oid = ix.indrelid\n\t\t\t\tAND i.oid = ix.indexrelid\n\t\t\t\tAND a.attrelid = t.oid\n\t\t\t\tAND a.attnum = ANY(ix.indkey)\n\t\t\t\tAND t.relkind = 'r'\n\t\t\t\tAND t.relname = '{$table}'\n\t\t\tORDER BY t.relname, i.relname;");
     $name = "UQ_" . sha1($table . implode(',', $columns));
     if ($r) {
         foreach ($r as $i) {
             if (strtolower($i['index_name']) == strtolower($name)) {
                 return;
             }
         }
     }
     $sql = "ALTER TABLE \"{$table}\"\n                ADD CONSTRAINT {$name} UNIQUE (" . implode(',', $columns) . ")";
     $this->adapter->exec($sql);
 }