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 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; }