ikkez / f3-schema-builder Goto Github PK

In Zeile 1123 steht: if ($this->type != 'TIMESTAMP' && // TODO: check that condition

Würden Sie bitte Ihr Werk vollenden? Ich verwende Ihren Quellcode für ein ernsthaftes Projekt.

hi, tnx for your awesome package.
is there any way to create foreign key constraint by schema builder?

Regarding to the docs for getCols(true) ;

Returns an array of existing table columns. If $types is set to TRUE, it will return an associative array with column name as key and the schema array as value.

This does not work for columns defined as 'default' => Schema::DF_CURRENT_TIMESTAMP.

What you get for that column:

array(5) {
  ["type"]=> "timestamp"
  ["pdo_type"]=>2
  ["default"]=> "current_timestamp()" <-- bad!  :(
  ["nullable"]=>true
  ["pkey"]=> false
}

What you expect:

array(5) {
  ["type"]=> "timestamp"
  ["pdo_type"]=>2
  ["default"]=> "CUR_STAMP" <-- good!
  ["nullable"]=>true
  ["pkey"]=> false
}

Hint: Schema::DF_CURRENT_TIMESTAMP == "CUR_STAMP" (https://github.com/ikkez/f3-schema-builder/blob/master/lib/db/sql/schema.php#L102)

Where does this happen?

The problem is the condition in line 768 (https://github.com/ikkez/f3-schema-builder/blob/master/lib/db/sql/schema.php#L768):

if (!is_null($default) && (
        (is_int(strpos($curdef=$this->findQuery($this->schema->defaultTypes['CUR_STAMP']),
                $default)) || is_int(strpos($default,$curdef)))
        || $default == "('now'::text)::timestamp(0) without time zone"))
{
    $default = 'CUR_STAMP';
 }

To be more precise, is_int(strpos($default,$curdef)) is supposed to be evaluated as true, then the complete if becomes true and $default becomes "CUR_STAMP".

Why does this happen:

1. $default is "current_timestamp()" This is correct! (because it read from "information_schema.columns" database table)
2. $curdef is "CURRENT_TIMESTAMP" This is correct! (is set one line above)

now we have ``is_int(strpos( "current_timestamp()" ,"CURRENT_TIMESTAMP"))=>false`.

How to fix:

Wrap both variables in strtolower() functions inside of strpos(). Then we get the expected true :)

Es wäre sehr angenehm, wenn man auch Kommentare für Tabellen/ Spalten bei der Erstellung von Tabellen/ Spalten angeben könnte.

How do I get an error saying that the table already exists?
Or is that the SQL script is not applied?

<?php

use DB\SQL\Schema;

class migration_2017_02_22_10_12
{
    public function up($f3)
    {
        $table = $f3->get('SCHEMA')->createTable('users');
        $table->primary('user_id');
        $table->addColumn('name')->type(schema::DT_VARCHAR128);
        $table->addColumn('password')->type(schema::DT_VARCHAR256);
        $table->build();

        // again for error test
        $table = $f3->get('SCHEMA')->createTable('users');
        $table->primary('user_id');
        $table->addColumn('name')->type(schema::DT_VARCHAR128);
        $table->addColumn('password')->type(schema::DT_VARCHAR256);
        $table->build(); // How to handling error here?

    }

    public function down($f3)
    {
        $f3->get('SCHEMA')->dropTable('users');
    }
}

Is it possible to add support for UUID?

References:

• MariaDB: https://mariadb.com/kb/en/uuid/
• MySQL: https://dev.mysql.com/blog-archive/storing-uuid-values-in-mysql-tables/
  • To my knowledge, MySQL doesnt directly have uuid data type, but we can store them as binary with the help of UUID_TO_BIN and BIN_TO_UUID functions. I'll attach some links for this notes too.
  • https://www.mysqltutorial.org/mysql-uuid/
  • https://stackoverflow.com/a/54759433/4721245
  • https://stackoverflow.com/a/48903998/4721245
• PostgreSQL: https://www.postgresql.org/docs/current/datatype-uuid.html
• SQL Server: https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql?view=sql-server-ver16

To my knowledge, there are several version for UUID too, but I havent take a deeper look to what version they currently use and supports.

Thank you!

Can't add custom schema for postgress check. Currently only checks the public schema.

something like the following should work (not tested).

		$cmd=[
			'mysql'=>[
				"show tables"],
			'sqlite2?'=>[
				"SELECT name FROM sqlite_master WHERE type='table' AND name!='sqlite_sequence'"],
			'pgsql|sybase|dblib'=>[
				"select table_name from information_schema.tables where table_schema = ?",$db_schema ],
			'mssql|sqlsrv|odbc'=>[
				"select table_name from information_schema.tables"],
			'ibm'=>["select TABLE_NAME from sysibm.tables"],
		];
		$query=$this->findQuery($cmd);
		if (!$query[0]) return FALSE;
		$tables=$this->db->exec($query[0], !this->devoid($query[1])?$query[1],null);
		if ($tables && is_array($tables) && count($tables)>0)
			foreach ($tables as &$table)
				$table=array_shift($table);
		return $tables;
	}```

Hi,

Are you considering adding support for views? 😃

I´m currently working on a database update/setup feature for my app. I would like to create a small UI that shows all Cortex models with their $fieldConf configuration, like type, index, default ,... information (something similar to PHPMyAdmin ). Moreover I want to compare the model $fieldConf settings with the "Current" existing table (MySQL) columns.

Some examples:

• If a column exists in the model but not in MySQL, it is marked with a red cross (X)
• If a column dataType has changed on an existing MySQL column (e.g. Schema::DT_VARCHAR128 to Schema::DT_VARCHAR256), the current dataType is marked in orange,...

My approach:

I have a static array with all model classes, which i loop over to get the New/Updated $fieldConf` for each model:

$requiredTables = [];

foreach($dbData['models'] as $model){
    $tableConfig =  call_user_func($model . '::resolveConfiguration');
    $requiredTables[$tableConfig['table']] = [
        'fieldConf' => $tableConfig['fieldConf'],
         'exists' => false
    ];
}

... and i have an other array with the Current tables in the database ...

$schema = new SQL\Schema($db);
$currentTables = $schema->getTables();

... now i can do a nested loop over both arrays and check if a table already exists. If it does, i can compare the column definition and check for changes...

foreach($requiredTables as $requiredTableName => $data){
    if(in_array($requiredTableName, $currentTables)){
        // table exists

        // get column definition
        $tableModifier = new SQL\TableModifier($requiredTableName, $schema);
        $currentColumns = $tableModifier->getCols(true);

        foreach($data['fieldConf'] as $columnName => $fieldConf){
            if(array_key_exists($columnName, $currentColumns)){
                // column exists -> check colum type for changes...
                // -> THE PROBLEM :)

            }else{
                // column not exists OR is not required e.g. "virtual Field"
            }
        }

    }
}

The Problem

For a column type check for changes, i could compare $currentColumns[$columnName]['type'] (which is a MySQL type definition e.g. "tinyint(1)" ) with $fieldConf['type'] (which is a DB\SQL\Schema::DT_BOOL type definition e.g. "BOOLEAN").

But the column type actually hasn´t changed at all :)
What i need at this point is some kind of "reverse Schema mapping" that either converts "tinyint(1)" into "BOOLEAN" (which is probably not always a good idea) OR "BOOLEAN" into "tinyint(1)" to make a column type check possible :)

The Question

1. Is there a simpler/more elegant way to achieve what i want?
2. Can i use something (already existing) from Schema Builder?

Sorry for the wall of text, I hope it is understandable ;) Great work at all!

Here is small preview of the current state: