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