/** * Returns metadata for all indexes in a table. * @param string * @return array */ public function getIndexes($table) { $res = $this->driver->query("SHOW INDEX FROM {$this->driver->escapeIdentifier($table)}"); $indexes = []; while ($row = $res->fetch(TRUE)) { $indexes[$row['Key_name']]['name'] = $row['Key_name']; $indexes[$row['Key_name']]['unique'] = !$row['Non_unique']; $indexes[$row['Key_name']]['primary'] = $row['Key_name'] === 'PRIMARY'; $indexes[$row['Key_name']]['columns'][$row['Seq_in_index'] - 1] = $row['Column_name']; } return array_values($indexes); }
/** * Returns count of rows in a table * @param string * @return int */ public function getTableCount($table, $fallback = TRUE) { if (empty($table)) { return FALSE; } $result = $this->driver->query("\n\t\t\tSELECT MAX(rowcnt)\n\t\t\tFROM sys.sysindexes\n\t\t\tWHERE id=OBJECT_ID({$this->driver->escapeIdentifier($table)})\n\t\t"); $row = $result->fetch(FALSE); if (!is_array($row) || count($row) < 1) { if ($fallback) { $row = $this->driver->query("SELECT COUNT(*) FROM {$this->driver->escapeIdentifier($table)}")->fetch(FALSE); $count = intval($row[0]); } else { $count = FALSE; } } else { $count = intval($row[0]); } return $count; }
/** * Returns metadata for all foreign keys in a table. * @param string * @return array */ public function getForeignKeys($table) { $res = $this->driver->query("PRAGMA foreign_key_list({$this->driver->escapeIdentifier($table)})"); $keys = []; while ($row = $res->fetch(TRUE)) { $keys[$row['id']]['name'] = $row['id']; // foreign key name $keys[$row['id']]['local'][$row['seq']] = $row['from']; // local columns $keys[$row['id']]['table'] = $row['table']; // referenced table $keys[$row['id']]['foreign'][$row['seq']] = $row['to']; // referenced columns $keys[$row['id']]['onDelete'] = $row['on_delete']; $keys[$row['id']]['onUpdate'] = $row['on_update']; if ($keys[$row['id']]['foreign'][0] == NULL) { $keys[$row['id']]['foreign'] = NULL; } } return array_values($keys); }