public function gc($maxlifetime) { // Memcached deals with it's own GC, but we have to do it for the DB. $query = new Query($this->dbconnection); $query->sql("\n Delete from `" . static::$tablename . "` where expiry < '" . time() . "'\n "); $query->execute(); return true; }
public function resolveState() { $key = 'join_' . $this->joinCount; $select = 'Select distinct ' . $key . '.id as id from `' . $this->sourceSchema['table_name'] . '` as join_0 '; $where = 'Where join_0.id = ' . $this->source->id; array_unshift($this->route, $select); array_push($this->route, $where); $query = new Query($this->database); $query->sql(implode(' ', $this->route)); list($ids) = $query->execute(); foreach ($ids as $id) { $return[] = $id['id']; } return $return; }
public static final function factoryDataCount($where, $table, $dbconnection, array $options = null) { // Select * from $table where $where $build = QueryBuilder::count($table)->where($where); if (is_array($options)) { // Limit if (key_exists('limit', $options) && key_exists('offset', $options)) { $build->limit($options['limit'], $options['offset']); } elseif (key_exists('limit', $options)) { $build->limit($options['limit']); } } $query = new Query($dbconnection); list($data) = $query->sql($build)->execute(); return $data; }
public function gc($maxlifetime) { if (!$this->session_broken) { try { $query = new Query($this->dbconnection); $query->sql("\n Delete from `" . static::$tablename . "` where expiry < '" . time() . "'\n "); $query->execute(); return true; } catch (\Automatorm\Exception\Database $e) { $this->session_broken = true; throw new Exception\Session('GC', $e); } } return false; }
public function getChildren() { // Find all direct child/parent relationships $query = new Query(static::getConnection()); $query->sql(QueryBuilder::select($this->closureTable, ['child_id'])->where(['parent_id' => $this->id, 'depth' => 1])); list($results) = $query->execute(); $children = []; foreach ($results as $row) { $children[] = $row['child_id']; } return static::findAll(['id' => $children]); }
public static function generateSchema($dbconnection) { $model = []; // Get a list of all foreign keys in this database $query = new Query($dbconnection); $query->sql("\n SELECT b.table_name, b.column_name, b.referenced_table_name, b.referenced_column_name\n FROM information_schema.table_constraints a \n JOIN information_schema.key_column_usage b\n ON a.table_schema = b.table_schema AND a.constraint_name = b.constraint_name\n WHERE a.table_schema = database() AND a.constraint_type = 'FOREIGN KEY'\n ORDER BY b.table_name, b.constraint_name;"); $query->sql("\n SELECT table_name, column_name, data_type FROM information_schema.columns where table_schema = database();\n "); list($keys, $schema) = $query->execute(); // Assemble list of table columns by table foreach ($schema as $row) { $table_name = self::normaliseCase($row['table_name']); $model[$table_name]['table_name'] = $row['table_name']; // All tables default to type 'table' - can also be 'pivot' or 'foreign' as detected later $model[$table_name]['type'] = 'table'; // List all columns for this table $model[$table_name]['columns'][$row['column_name']] = $row['data_type']; } // Loop over every foreign key definition foreach ($keys as $row) { $table_name = self::normaliseCase($row['table_name']); $ref_table_name = self::normaliseCase($row['referenced_table_name']); if ($row['referenced_column_name'] == 'id' and $row['column_name'] == 'id') { // If both columns in the key are 'id' then this is a 1 to 1 relationship. // Create a link in both objects to each other $model[$ref_table_name]['one-to-one'][self::underscoreCase($table_name)] = $table_name; $model[$table_name]['one-to-one'][self::underscoreCase($ref_table_name)] = $ref_table_name; $model[$table_name]['type'] = 'foreign'; } elseif ($row['referenced_column_name'] == 'id') { // if this foreign key points at one 'id' column then this is a usable foreign 'key' if (substr($row['column_name'], -3) == '_id') { $column_root = substr($row['column_name'], 0, -3); $model[$table_name]['many-to-one'][$column_root] = $ref_table_name; // Add the key constraint in reverse, trying to make a sensible name. // If the column name was derived from the table name, just use the table name. // (e.g "my_account" table and "my_account_id" -> my_account) // Otherwise, append the column name to the table name to make sure it is unique. // (e.g "your_account" table and "my_account_id" -> your_account_my_account) if ($column_root == $row['referenced_table_name']) { $property_name = self::underscoreCase($table_name); } else { $property_name = self::underscoreCase($table_name) . '_' . $column_root; } $model[$ref_table_name]['one-to-many'][$property_name] = array('table' => $table_name, 'column_name' => $row['column_name']); } } } // Now look for pivot tables foreach ($model as $pivottablename => $pivot) { // If we have found a table with only foreign keys then this must be a pivot table if (count($pivot['many-to-one']) > 1 and count($pivot['columns']) == count($pivot['many-to-one'])) { // Grab all foreign keys and rearrange them into arrays. $tableinfo = array(); foreach ($pivot['many-to-one'] as $column => $tablename) { $tableinfo[] = array('column' => $column . '_id', 'column_raw' => $column, 'table' => $tablename); } // For each foreign key, store details in the table it point to on how to get to the OTHER table in the "Many to Many" relationship foreach ($tableinfo as $i => $table) { // If the column name is named based on the foreign table name, then use the pivot table name as the property name // This is the normal/usual case if ($table['column'] == self::underscoreCase($table['table']) . '_id') { $property_name = self::underscoreCase($pivottablename); } else { // Else append the column name to the pivot table name. // This is mostly for when a pivot table references the same table twice, and so // needs to have a unique name for at least one of the columns (which is not based on the table name) $property_name = self::underscoreCase($pivottablename) . '_' . $table['column_raw']; } // Outersect of tables to create an array of all OTHER foreign keys in this table, for this foreign key. $othertables = array_values(array_diff_assoc($tableinfo, array($i => $table))); $model[$table['table']]['many-to-many'][$property_name] = array('pivot' => $pivottablename, 'connections' => $othertables, 'id' => $table['column']); } $model[$pivottablename]['type'] = 'pivot'; // Remove the M-1 keys for these tables to fully encapsulate the M-M scheme. foreach ($tableinfo as $table) { foreach ((array) $model[$table['table']]['one-to-many'] as $key => $val) { if ($val['table'] == $pivottablename) { unset($model[$table['table']]['one-to-many'][$key]); } } } } } return $model; }
public function joinCount($var, $where = []) { if (!is_null($this->__external[$var]) && !$this->__external[$var] instanceof Collection) { return 1; } if ($this->__external[$var]) { return $this->__external[$var]->filter($where)->count(); } // If this Model_Data isn't linked to the db yet, then linked values cannot exist if (!($id = $this->__data['id'])) { return 0; } /* FOREIGN KEYS */ // 1-1, just grab the object - not worth optimising if (key_exists($var, (array) $this->__model['one-to-one'])) { /* Call Tablename::factory(foreign key id) to get the object we want */ $table = $this->__model['one-to-one'][$var]; $this->__external[$var] = Model::factoryObjectCache($id, $table, $this->__database); return $this->__external[$var] ? 1 : 0; } // M-1, just grab the object - not worth optimising if (key_exists($var, (array) $this->__model['many-to-one'])) { /* Call Tablename::factory(foreign key id) to get the object we want */ $table = $this->__model['many-to-one'][$var]; $this->__external[$var] = Model::factoryObjectCache($this->__data[$var . '_id'], $table, $this->__database); return $this->__external[$var] ? 1 : 0; } /* Look for lists of objects in other tables referencing this one */ if (key_exists($var, (array) $this->__model['one-to-many'])) { $table = $this->__model['one-to-many'][$var]['table']; $column = $this->__model['one-to-many'][$var]['column_name']; // Use the model factory to find the relevant items list($data) = Model::factoryDataCount($where + [$column => $id], $table, $this->__database); return $data['count']; } if (key_exists($var, (array) $this->__model['many-to-many'])) { // Get pivot schema $pivot = $this->__model['many-to-many'][$var]; // We can only support simple connection access for 2 key pivots. if (count($pivot['connections']) != 1) { throw new Exception\Model('MODEL_DATA:CANNOT_CALL_MULTIPIVOT_AS_PROPERTY', array($var)); } // Get a list of ids linked to this object (i.e. the tablename_id stored in the pivot table) $pivot_schema = $this->__schema->getTable($pivot['pivot']); $pivot_tablename = $pivot_schema['table_name']; $clauses = []; if ($where) { foreach ($where as $clause_column => $clause_value) { // Rewrite $where clauses to insert `pivotjoin` table in column name preg_match('/^([!=<>%]*)(.+?)([!=<>%]*)$/', $clause_column, $parts); $prefix = $parts[1] ?: $parts[3]; $clause_column = $parts[2]; $clauses['`pivotjoin`.`' . $clause_column . '`' . $prefix] = $clause_value; } } // Build Query $q = QueryBuilder::select([$pivot_tablename => 'pivot'], ['pivot.*'])->where(['`pivot`.' . $pivot['id'] => $this->__data['id']])->join([Schema::underscoreCase($pivot['connections'][0]['table']) => 'pivotjoin'])->joinOn(['pivotjoin.id' => "`pivot`.`{$pivot['connections'][0]['column']}`"])->where($clauses); $query = new Query($this->__database); list($raw) = $query->sql($q)->execute(); // Rearrange the list of ids into a flat array $id = array(); foreach ($raw as $raw_id) { $id[] = $raw_id[$pivot['connections'][0]['column']]; } $dedup = array_unique($id); return count($dedup); } throw new Exception\Model("MODEL_DATA:UNKNOWN_FOREIGN_PROPERTY", ['property' => $var, 'data' => $this]); }