function updateSchema($database)
{
$schema = new fSchema($database);
// Relate caldaily -> calevent
$schema->setKeysOverride(array(array('column' => 'id', 'foreign_table' => 'calevent', 'foreign_column' => 'id', 'on_delete' => 'cascade', 'on_update' => 'cascade'), array('column' => 'exceptionid', 'foreign_table' => 'calevent', 'foreign_column' => 'id', 'on_delete' => 'cascade', 'on_update' => 'cascade')), 'caldaily', 'foreign');
return $schema;
}
/**
* Adds `WHERE` params to the SQL for the primary keys of this record set
*
* @param fDatabase $db The database the query will be executed on
* @param fSchema $schema The schema for the database
* @param array $params The parameters for the fDatabase::query() call
* @param string $route The route to this table from another table
* @return array The params with the `WHERE` clause added
*/
private function addWhereParams($db, $schema, $params, $route = NULL)
{
$table = fORM::tablize($this->class);
$table_with_route = $route ? $table . '{' . $route . '}' : $table;
$pk_columns = $schema->getKeys($table, 'primary');
// We have a multi-field primary key, making things kinda ugly
if (sizeof($pk_columns) > 1) {
$escape_pk_columns = array();
foreach ($pk_columns as $pk_column) {
$escaped_pk_columns[$pk_column] = $db->escape('%r', $table_with_route . '.' . $pk_column);
}
$column_info = $schema->getColumnInfo($table);
$conditions = array();
foreach ($this->getPrimaryKeys() as $primary_key) {
$sub_conditions = array();
foreach ($pk_columns as $pk_column) {
$value = $primary_key[$pk_column];
// This makes sure the query performs the way an insert will
if ($value === NULL && $column_info[$pk_column]['not_null'] && $column_info[$pk_column]['default'] !== NULL) {
$value = $column_info[$pk_column]['default'];
}
$sub_conditions[] = str_replace('%r', $escaped_pk_columns[$pk_column], fORMDatabase::makeCondition($schema, $table, $pk_column, '=', $value));
$params[] = $value;
}
$conditions[] = join(' AND ', $sub_conditions);
}
$params[0] .= '(' . join(') OR (', $conditions) . ')';
// We have a single primary key field, making things nice and easy
} else {
$first_pk_column = $pk_columns[0];
$params[0] .= $db->escape('%r IN ', $table_with_route . '.' . $first_pk_column);
$params[0] .= '(' . $schema->getColumnInfo($table, $first_pk_column, 'placeholder') . ')';
$params[] = $this->getPrimaryKeys();
}
return $params;
}
/**
* Checks to see if a columns has a value, but based on the schema and if the column allows NULL
*
* If the columns allows NULL values, than anything other than NULL
* will be returned as TRUE. If the column does not allow NULL and
* the value is anything other than the "empty" value for that data type,
* then TRUE will be returned.
*
* The values that are considered "empty" for each data type are as follows.
* Please note that there is no "empty" value for dates, times or
* timestamps.
*
* - Blob: ''
* - Boolean: FALSE
* - Float: 0.0
* - Integer: 0
* - String: ''
*
* @internal
*
* @param fSchema $schema The schema object for the table
* @param string $class The class the column is part of
* @param array &$values An associative array of all values for the record
* @param array $columns The column to check
* @return string An error message for the rule
*/
public static function hasValue($schema, $class, &$values, $column)
{
$value = $values[$column];
if ($value === NULL) {
return FALSE;
}
$table = fORM::tablize($class);
$data_type = $schema->getColumnInfo($table, $column, 'type');
$allows_null = !$schema->getColumnInfo($table, $column, 'not_null');
if ($allows_null) {
return TRUE;
}
switch ($data_type) {
case 'blob':
case 'char':
case 'text':
case 'varchar':
if ($value === '') {
return FALSE;
}
break;
case 'boolean':
if ($value === FALSE) {
return FALSE;
}
break;
case 'integer':
if ($value === 0 || $value === '0') {
return FALSE;
}
break;
case 'float':
if (preg_match('#^0(\\.0*)?$|^\\.0+$#D', $value)) {
return FALSE;
}
break;
}
return TRUE;
}
/**
* Adds params for a `WHERE` clause to ensure a database call is only selecting from rows that are part of the same set when an ordering field is in multi-column `UNIQUE` constraint.
*
* @param fSchema $schema The schema of the database the query will be executed on
* @param array $params The parameters for the fDatabase::query() call
* @param string $table The table the `WHERE` clause is for
* @param array $other_columns The other columns in the multi-column unique constraint
* @param array &$values The values to match with
* @return array The updated params for fDatabase::query()
*/
private static function addOtherFieldsWhereParams($schema, $params, $table, $other_columns, &$values)
{
$column_info = $schema->getColumnInfo($table);
$conditions = array();
foreach ($other_columns as $other_column) {
$value = $values[$other_column];
// This makes sure the query performs the way an insert will
if ($value === NULL && $column_info[$other_column]['not_null'] && $column_info[$other_column]['default'] !== NULL) {
$value = $column_info[$other_column]['default'];
}
$params[] = $table . '.' . $other_column;
$params[] = $value;
$conditions[] = fORMDatabase::makeCondition($schema, $table, $other_column, '=', $value);
}
$params[0] .= join(' AND ', $conditions);
return $params;
}
/**
* Returns an array of all routes from a table to one of its related tables
*
* @internal
*
* @param fSchema $schema The schema object to get the routes for
* @param string $table The main table we are searching on behalf of
* @param string $related_table The related table we are trying to find the routes for
* @param string $relationship_type The relationship type: `NULL`, `'*-to-many'`, `'*-to-one'`, `'!many-to-one'`, `'one-to-one'`, `'one-to-many'`, `'many-to-one'`, `'many-to-many'`
* @return array All of the routes from the main table to the related table
*/
public static function getRoutes($schema, $table, $related_table, $relationship_type = NULL)
{
$key = $table . '::' . $related_table . '::' . $relationship_type;
if (isset(self::$cache['getRoutes'][$key])) {
return self::$cache['getRoutes'][$key];
}
$valid_relationship_types = array(NULL, '*-to-many', '*-to-one', '!many-to-one', 'many-to-many', 'many-to-one', 'one-to-many', 'one-to-one');
if (!in_array($relationship_type, $valid_relationship_types)) {
$valid_relationship_types[0] = '{null}';
throw new fProgrammerException('The relationship type specified, %1$s, is invalid. Must be one of: %2$s.', $relationship_type, join(', ', $valid_relationship_types));
}
$all_relationships = $schema->getRelationships($table);
$routes = array();
foreach ($all_relationships as $type => $relationships) {
// Filter the relationships by the relationship type
if ($relationship_type !== NULL) {
if ($relationship_type == '!many-to-one') {
if ($type == 'many-to-one') {
continue;
}
} else {
if (strpos($type, str_replace('*', '', $relationship_type)) === FALSE) {
continue;
}
}
}
foreach ($relationships as $relationship) {
if ($relationship['related_table'] == $related_table) {
if ($type == 'many-to-many') {
$routes[$relationship['join_table']] = $relationship;
} elseif ($type == 'one-to-many') {
$routes[$relationship['related_column']] = $relationship;
} else {
$routes[$relationship['column']] = $relationship;
}
}
}
}
self::$cache['getRoutes'][$key] = $routes;
return $routes;
}
public function testAlterTypeIncreaseDecimalSize()
{
self::$db->translatedQuery('ALTER TABLE albums ALTER COLUMN "msrp" TYPE DECIMAL(12,2)');
$this->rollback_statements[] = "ALTER TABLE albums ALTER COLUMN msrp SET NOT NULL";
$this->rollback_statements[] = "ALTER TABLE albums RENAME COLUMN msrp2 TO msrp";
$this->rollback_statements[] = "ALTER TABLE albums DROP COLUMN msrp";
$this->rollback_statements[] = "UPDATE albums SET msrp2 = msrp";
$this->rollback_statements[] = "ALTER TABLE albums ADD COLUMN msrp2 DECIMAL(10,2)";
$schema = new fSchema(self::$db);
$this->assertEquals(array('type' => 'float', 'placeholder' => '%f', 'not_null' => TRUE, 'default' => NULL, 'valid_values' => NULL, 'max_length' => NULL, 'max_value' => new fNumber('9999999999.99'), 'min_value' => new fNumber('-9999999999.99'), 'decimal_places' => 2, 'auto_increment' => FALSE, 'comment' => ''), $schema->getColumnInfo('albums', 'msrp'));
}
/**
* Takes a table name, cleans off quoting and removes the schema name if unambiguous
*
* @param fSchema $schema The schema object for the database being inspected
* @param string $table The table name to be made cleaned
* @return string The cleaned table name
*/
private static function cleanTableName($schema, $table)
{
$table = str_replace('"', '', $table);
$tables = array_flip($schema->getTables());
if (!isset($tables[$table])) {
$short_table = preg_replace('#^\\w\\.#', '', $table);
if (isset($tables[$short_table])) {
$table = $short_table;
}
}
return $table;
}
public function testCreateTable()
{
$this->db->translatedQuery("CREATE TABLE translation_test (\n\t\t\t\ttranslation_test_id INTEGER AUTOINCREMENT PRIMARY KEY,\n\t\t\t\tbigint_col BIGINT NULL,\n\t\t\t\tchar_col CHAR(40) NULL,\n\t\t\t\tvarchar_col VARCHAR(100) NULL,\n\t\t\t\ttext_col TEXT NULL,\n\t\t\t\tblob_col BLOB NULL,\n\t\t\t\ttimestamp_col TIMESTAMP NULL,\n\t\t\t\ttime_col TIME NULL,\n\t\t\t\tdate_col DATE NULL,\n\t\t\t\tboolean_col BOOLEAN NULL\n\t\t\t)");
$this->db->translatedQuery("CREATE TABLE translation_test_2 (\n\t\t\t\ttranslation_test_2_id INTEGER NOT NULL PRIMARY KEY,\n\t\t\t\ttranslation_test_id INTEGER NOT NULL REFERENCES translation_test(translation_test_id) ON DELETE CASCADE,\n\t\t\t\tname VARCHAR(100) NULL\n\t\t\t)");
$schema = new fSchema($this->db);
$translation_test_schema = $schema->getColumnInfo('translation_test');
foreach ($translation_test_schema as $type => &$list) {
ksort($list);
}
ksort($translation_test_schema);
$max_blob_length = 0;
$max_text_length = 0;
switch (DB_TYPE) {
case 'sqlite':
$max_blob_length = 1000000000;
$max_text_length = 1000000000;
break;
case 'oracle':
$max_blob_length = 4294967295;
$max_text_length = 4294967295;
break;
case 'mysql':
$max_blob_length = 4294967295;
$max_text_length = 16777215;
break;
case 'postgresql':
$max_blob_length = 1073741824;
$max_text_length = 1073741824;
break;
case 'mssql':
$max_blob_length = 2147483647;
$max_text_length = 1073741823;
break;
case 'db2':
$max_blob_length = 1048576;
$max_text_length = 1048576;
break;
}
$this->assertEquals(array('bigint_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber('9223372036854775807') : null, "min_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber('-9223372036854775808') : null, "not_null" => FALSE, "placeholder" => "%i", "type" => "integer", "valid_values" => NULL), 'blob_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => $max_blob_length, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%l", "type" => "blob", "valid_values" => NULL), 'boolean_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%b", "type" => "boolean", "valid_values" => NULL), 'char_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => 40, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%s", "type" => "char", "valid_values" => NULL), 'date_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => $this->db->getType() == 'mssql' ? "%p" : "%d", "type" => $this->db->getType() == 'mssql' ? "timestamp" : "date", "valid_values" => NULL), 'text_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => $max_text_length, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%s", "type" => "text", "valid_values" => NULL), 'time_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => in_array($this->db->getType(), array('mssql', 'oracle')) ? "%p" : "%t", "type" => in_array($this->db->getType(), array('mssql', 'oracle')) ? "timestamp" : "time", "valid_values" => NULL), 'timestamp_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%p", "type" => "timestamp", "valid_values" => NULL), 'translation_test_id' => array("auto_increment" => TRUE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(2147483647) : null, "min_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(-2147483648) : null, "not_null" => TRUE, "placeholder" => "%i", "type" => "integer", "valid_values" => NULL), 'varchar_col' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => 100, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%s", "type" => "varchar", "valid_values" => NULL)), $translation_test_schema);
$translation_test_2_schema = $schema->getColumnInfo('translation_test_2');
foreach ($translation_test_2_schema as $type => &$list) {
ksort($list);
}
ksort($translation_test_2_schema);
$this->assertEquals(array('name' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => 100, "max_value" => NULL, "min_value" => NULL, "not_null" => FALSE, "placeholder" => "%s", "type" => "varchar", "valid_values" => NULL), 'translation_test_2_id' => array("auto_increment" => $this->db->getType() == 'sqlite' ? TRUE : FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(2147483647) : null, "min_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(-2147483648) : null, "not_null" => TRUE, "placeholder" => "%i", "type" => "integer", "valid_values" => NULL), 'translation_test_id' => array("auto_increment" => FALSE, "decimal_places" => NULL, "default" => NULL, "max_length" => NULL, "max_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(2147483647) : null, "min_value" => DB_TYPE != 'sqlite' && DB_TYPE != 'oracle' ? new fNumber(-2147483648) : null, "not_null" => TRUE, "placeholder" => "%i", "type" => "integer", "valid_values" => NULL)), $translation_test_2_schema);
$translation_test_keys = $schema->getKeys('translation_test');
ksort($translation_test_keys);
$this->assertEquals(array('foreign' => array(), 'primary' => array('translation_test_id'), 'unique' => array()), $translation_test_keys);
$translation_test_2_keys = $schema->getKeys('translation_test_2');
ksort($translation_test_2_keys);
$this->assertEquals(array('foreign' => array(array("column" => "translation_test_id", "foreign_table" => "translation_test", "foreign_column" => "translation_test_id", "on_delete" => "cascade", "on_update" => "no_action")), 'primary' => array('translation_test_2_id'), 'unique' => array()), $translation_test_2_keys);
}
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with Print Master. If not, see <http://www.gnu.org/licenses/>.
*/
/*
Database patch script for PrintMaster.
If this is the first time being run, the patch history table is created.
When it exists, it executes the required patch scripts in the ./db folder.
*/
// Include initialisation file
include_once 'inc/core.php';
// Get DB schema so we can find out if the table exists.
$schema = new fSchema($db);
$tables = $schema->getTables();
// If table does not exist - create it and insert default entry.
if (!array_search('patch_history', $tables)) {
echo "Initialising patch history table...<br>";
$sql = "CREATE TABLE `patch_history` ( `num` smallint(5) unsigned NOT NULL) ENGINE='MyISAM' COLLATE 'utf8_unicode_ci'";
$db->query($sql);
$sql = "ALTER TABLE `patch_history` ADD PRIMARY KEY `num` (`num`)";
$db->query($sql);
$sql = "INSERT INTO `patch_history` SET `num` = 0";
$db->query($sql);
}
// Get the last patch level
$sql = "SELECT MAX(num) AS num FROM patch_history";
$row = $db->query($sql)->fetchRow();
$num = (int) $row['num'];