Example #1
0
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;
 }
Example #5
0
 /**
  * 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'));
 }
Example #7
0
 /**
  * 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;
 }
Example #8
0
 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);
 }
Example #9
0
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'];