commit 6715913059768931af109d70d71a7cba874c9927 parent a7ee678959ef83a8ec96891d2f18d0a4514d11cc Author: Jan Dankert <develop@jandankert.de> Date: Fri, 25 Sep 2020 01:00:58 +0200 Refactoring: More OO in the database updater :) Diffstat:
25 files changed, 823 insertions(+), 737 deletions(-)
diff --git a/modules/cms/update/version/DBVersion000001.class.php b/modules/cms/update/version/DBVersion000001.class.php @@ -4,6 +4,7 @@ namespace cms\update\version; use database\Database; use database\DbVersion; +use database\Column; /** @@ -16,296 +17,293 @@ class DBVersion000001 extends DbVersion { public function update() { - $not_nullable = false; - $nullable = true; - - $this->addTable('project'); + $table = $this->table('project')->add(); - $this->addColumn('project','name' ,OR_DB_COLUMN_TYPE_VARCHAR, 128,null,$not_nullable); - $this->addColumn('project','target_dir' ,OR_DB_COLUMN_TYPE_VARCHAR, 255,null,$not_nullable); - $this->addColumn('project','ftp_url' ,OR_DB_COLUMN_TYPE_VARCHAR, 255,null,$not_nullable); - $this->addColumn('project','ftp_passive' ,OR_DB_COLUMN_TYPE_INT ,null,null,$not_nullable); - $this->addColumn('project','cmd_after_publish' ,OR_DB_COLUMN_TYPE_VARCHAR, 255,null,$not_nullable); - $this->addColumn('project','content_negotiation',OR_DB_COLUMN_TYPE_INT , 1, 0,$not_nullable); - $this->addColumn('project','cut_index' ,OR_DB_COLUMN_TYPE_INT , 1, 0,$not_nullable); + $table->column('name' )->type(Column::TYPE_VARCHAR)->size( 128)->add(); + $table->column('target_dir' )->type(Column::TYPE_VARCHAR)->size( 255)->add(); + $table->column('ftp_url' )->type(Column::TYPE_VARCHAR)->size( 255)->add(); + $table->column('ftp_passive' )->type(Column::TYPE_INT )->add(); + $table->column('cmd_after_publish' )->type(Column::TYPE_VARCHAR)->size( 255)->add(); + $table->column('content_negotiation')->type(Column::TYPE_INT )->size( 1)->defaultValue( 0)->add(); + $table->column('cut_index' )->type(Column::TYPE_INT )->size( 1)->defaultValue( 0)->add(); - $this->addPrimaryKey('project','id'); - $this->addIndex('project','name'); + $table->addPrimaryKey('id'); + $table->addIndex('name'); - $this->addTable('user'); - $this->addColumn('user','name',OR_DB_COLUMN_TYPE_VARCHAR,128,null,$not_nullable); - $this->addColumn('user','password',OR_DB_COLUMN_TYPE_VARCHAR,50,null,$not_nullable); - $this->addColumn('user','ldap_dn',OR_DB_COLUMN_TYPE_VARCHAR,255,null,$not_nullable); - $this->addColumn('user','fullname',OR_DB_COLUMN_TYPE_VARCHAR,128,null,$not_nullable); - $this->addColumn('user','tel',OR_DB_COLUMN_TYPE_VARCHAR,128,null,$not_nullable); - $this->addColumn('user','mail',OR_DB_COLUMN_TYPE_VARCHAR,255,null,$not_nullable); - $this->addColumn('user','descr',OR_DB_COLUMN_TYPE_VARCHAR,255,null,$not_nullable); - $this->addColumn('user','style',OR_DB_COLUMN_TYPE_VARCHAR,64,null,$not_nullable); - $this->addColumn('user','is_admin',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addPrimaryKey('user','id'); - $this->addUniqueIndex('user','name'); - - $this->addTable('group'); - $this->addColumn('group','name',OR_DB_COLUMN_TYPE_VARCHAR,100,null,$not_nullable); - $this->addPrimaryKey('group','id'); - $this->addUniqueIndex('group','name'); - - $this->addTable('object'); - $this->addColumn('object','parentid',OR_DB_COLUMN_TYPE_INT,null,null,$nullable); - $this->addColumn('object','projectid',OR_DB_COLUMN_TYPE_INT,0,0,$not_nullable); - $this->addColumn('object','filename',OR_DB_COLUMN_TYPE_VARCHAR,150,null,$not_nullable); - $this->addColumn('object','orderid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('object','create_date',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('object','create_userid',OR_DB_COLUMN_TYPE_INT,0,null,$nullable); - $this->addColumn('object','lastchange_date',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('object','lastchange_userid',OR_DB_COLUMN_TYPE_INT,0,null,$nullable); - $this->addColumn('object','is_folder',OR_DB_COLUMN_TYPE_INT,1,null,$not_nullable); - $this->addColumn('object','is_file',OR_DB_COLUMN_TYPE_INT,1,null,$not_nullable); - $this->addColumn('object','is_page',OR_DB_COLUMN_TYPE_INT,1,null,$not_nullable); - $this->addColumn('object','is_link',OR_DB_COLUMN_TYPE_INT,1,null,$not_nullable); - $this->addPrimaryKey('object','id'); - $this->addConstraint('object','projectid','project','id'); - $this->addConstraint('object','lastchange_userid','user','id'); - $this->addConstraint('object','create_userid','user','id'); - - $this->addIndex('object','parentid'); - $this->addIndex('object','projectid'); - $this->addIndex('object','is_folder'); - $this->addIndex('object','is_file'); - $this->addIndex('object','is_page'); - $this->addIndex('object','is_link'); - $this->addIndex('object','orderid'); - $this->addIndex('object','create_userid'); - $this->addIndex('object','lastchange_userid'); - $this->addUniqueIndex('object','parentid,filename'); - - - - - $this->addTable('template'); - $this->addColumn('template','projectid',OR_DB_COLUMN_TYPE_INT,null,null,$not_nullable); - $this->addColumn('template','name',OR_DB_COLUMN_TYPE_VARCHAR,50,null,$not_nullable); - $this->addPrimaryKey('template','id'); - $this->addConstraint('template','projectid','project','id'); - - $this->addIndex('template','projectid'); - $this->addIndex('template','name'); - $this->addUniqueIndex('template','projectid,name'); - - - - $this->addTable('language'); - $this->addColumn('language','projectid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('language','isocode',OR_DB_COLUMN_TYPE_VARCHAR,10,null,$not_nullable); - $this->addColumn('language','name',OR_DB_COLUMN_TYPE_VARCHAR,50,null,$not_nullable); - $this->addColumn('language','is_default',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addPrimaryKey('language','id'); - $this->addConstraint('language','projectid','project','id'); - $this->addUniqueIndex('language','projectid,isocode'); - - - - - $this->addTable('page'); - $this->addColumn('page','objectid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('page','templateid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addPrimaryKey('page','id'); - $this->addConstraint('page','templateid','template','id'); - $this->addConstraint('page','objectid','object','id'); + $table = $this->table('user')->add(); + $table->column('name')->type(Column::TYPE_VARCHAR)->size(128)->add(); + $table->column('password')->type(Column::TYPE_VARCHAR)->size(50)->add(); + $table->column('ldap_dn')->type(Column::TYPE_VARCHAR)->size(255)->add(); + $table->column('fullname')->type(Column::TYPE_VARCHAR)->size(128)->add(); + $table->column('tel')->type(Column::TYPE_VARCHAR)->size(128)->add(); + $table->column('mail')->type(Column::TYPE_VARCHAR)->size(255)->add(); + $table->column('descr')->type(Column::TYPE_VARCHAR)->size(255)->add(); + $table->column('style')->type(Column::TYPE_VARCHAR)->size(64)->add(); + $table->column('is_admin')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->addPrimaryKey('id'); + $table->addUniqueIndex('name'); + + $table = $this->table('group')->add(); + $table->column('name')->type(Column::TYPE_VARCHAR)->size(100)->add(); + $table->addPrimaryKey('id'); + $table->addUniqueIndex('name'); + + $table = $this->table('object')->add(); + $table->column('parentid')->type(Column::TYPE_INT)->nullable()->add(); + $table->column('projectid')->type(Column::TYPE_INT)->size(0)->defaultValue(0)->add(); + $table->column('filename')->type(Column::TYPE_VARCHAR)->size(150)->add(); + $table->column('orderid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('create_date')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('create_userid')->type(Column::TYPE_INT)->size(0)->nullable()->add(); + $table->column('lastchange_date')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('lastchange_userid')->type(Column::TYPE_INT)->size(0)->nullable()->add(); + $table->column('is_folder')->type(Column::TYPE_INT)->size(1)->add(); + $table->column('is_file')->type(Column::TYPE_INT)->size(1)->add(); + $table->column('is_page')->type(Column::TYPE_INT)->size(1)->add(); + $table->column('is_link')->type(Column::TYPE_INT)->size(1)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('projectid', 'project', 'id'); + $table->addConstraint('lastchange_userid', 'user', 'id'); + $table->addConstraint('create_userid', 'user', 'id'); + + $table->addIndex('parentid'); + $table->addIndex('projectid'); + $table->addIndex('is_folder'); + $table->addIndex('is_file'); + $table->addIndex('is_page'); + $table->addIndex('is_link'); + $table->addIndex('orderid'); + $table->addIndex('create_userid'); + $table->addIndex('lastchange_userid'); + $table->addUniqueIndex( ['parentid','filename'] ); + + + + + $table = $this->table('template')->add(); + $table->column('projectid')->type(Column::TYPE_INT)->add(); + $table->column('name')->type(Column::TYPE_VARCHAR)->size(50)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('projectid', 'project', 'id'); + + $table->addIndex('projectid'); + $table->addIndex('name'); + $table->addUniqueIndex(['projectid','name']); + + + + $table = $this->table('language')->add(); + $table->column('projectid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('isocode')->type(Column::TYPE_VARCHAR)->size(10)->add(); + $table->column('name')->type(Column::TYPE_VARCHAR)->size(50)->add(); + $table->column('is_default')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('projectid', 'project', 'id'); + $table->addUniqueIndex(['projectid','isocode']); + + + + + $table = $this->table('page')->add(); + $table->column('objectid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('templateid')->type(Column::TYPE_INT)->size(0)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('templateid', 'template', 'id'); + $table->addConstraint('objectid', 'object', 'id'); - $this->addUniqueIndex('page','objectid'); - $this->addIndex('page','templateid'); + $table->addUniqueIndex('objectid'); + $table->addIndex('templateid'); - $this->addTable('projectmodel'); - $this->addColumn('projectmodel','projectid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('projectmodel','name',OR_DB_COLUMN_TYPE_VARCHAR,50,null,$not_nullable); - $this->addColumn('projectmodel','extension',OR_DB_COLUMN_TYPE_VARCHAR,10,null,$nullable); - $this->addColumn('projectmodel','is_default',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addPrimaryKey('projectmodel','id'); - $this->addConstraint('projectmodel','projectid','project','id'); + $table = $this->table('projectmodel')->add(); + $table->column('projectid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('name')->type(Column::TYPE_VARCHAR)->size(50)->add(); + $table->column('extension')->type(Column::TYPE_VARCHAR)->size(10)->nullable()->add(); + $table->column('is_default')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('projectid', 'project', 'id'); - $this->addIndex('projectmodel','projectid'); - $this->addUniqueIndex('projectmodel','projectid,name'); + $table->addIndex('projectid'); + $table->addUniqueIndex(['projectid','name'] ); - $this->addTable('element'); - $this->addColumn('element','templateid',OR_DB_COLUMN_TYPE_INT,0,0,$not_nullable); - $this->addColumn('element','name',OR_DB_COLUMN_TYPE_VARCHAR,50,null,$not_nullable); - $this->addColumn('element','descr',OR_DB_COLUMN_TYPE_VARCHAR,255,null,$not_nullable); - $this->addColumn('element','type',OR_DB_COLUMN_TYPE_VARCHAR,20,null,$not_nullable); - $this->addColumn('element','subtype',OR_DB_COLUMN_TYPE_VARCHAR,20,null,$nullable); - $this->addColumn('element','with_icon',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('element','dateformat',OR_DB_COLUMN_TYPE_VARCHAR,100,null,$nullable); - $this->addColumn('element','wiki',OR_DB_COLUMN_TYPE_INT,1,0,$nullable); - $this->addColumn('element','html',OR_DB_COLUMN_TYPE_INT,1,0,$nullable); - $this->addColumn('element','all_languages',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('element','writable',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('element','decimals',OR_DB_COLUMN_TYPE_INT,0,null,$nullable); - $this->addColumn('element','dec_point',OR_DB_COLUMN_TYPE_VARCHAR,5,null,$nullable); - $this->addColumn('element','thousand_sep',OR_DB_COLUMN_TYPE_VARCHAR,1,null,$nullable); - $this->addColumn('element','code',OR_DB_COLUMN_TYPE_TEXT,null,null,$nullable); - $this->addColumn('element','default_text',OR_DB_COLUMN_TYPE_TEXT,null,null,$nullable); - $this->addColumn('element','folderobjectid',OR_DB_COLUMN_TYPE_INT,null,null,$nullable); - $this->addColumn('element','default_objectid',OR_DB_COLUMN_TYPE_INT,null,null,$nullable); - $this->addPrimaryKey('element','id'); - $this->addConstraint('element','default_objectid','object','id'); - $this->addConstraint('element','folderobjectid','object','id'); - $this->addConstraint('element','templateid','template','id'); + $table = $this->table('element')->add(); + $table->column('templateid')->type(Column::TYPE_INT)->size(0)->defaultValue(0)->add(); + $table->column('name')->type(Column::TYPE_VARCHAR)->size(50)->add(); + $table->column('descr')->type(Column::TYPE_VARCHAR)->size(255)->add(); + $table->column('type')->type(Column::TYPE_VARCHAR)->size(20)->add(); + $table->column('subtype')->type(Column::TYPE_VARCHAR)->size(20)->nullable()->add(); + $table->column('with_icon')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('dateformat')->type(Column::TYPE_VARCHAR)->size(100)->nullable()->add(); + $table->column('wiki')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->nullable()->add(); + $table->column('html')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->nullable()->add(); + $table->column('all_languages')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('writable')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('decimals')->type(Column::TYPE_INT)->size(0)->nullable()->add(); + $table->column('dec_point')->type(Column::TYPE_VARCHAR)->size(5)->nullable()->add(); + $table->column('thousand_sep')->type(Column::TYPE_VARCHAR)->size(1)->nullable()->add(); + $table->column('code')->type(Column::TYPE_TEXT)->nullable()->add(); + $table->column('default_text')->type(Column::TYPE_TEXT)->nullable()->add(); + $table->column('folderobjectid')->type(Column::TYPE_INT)->nullable()->add(); + $table->column('default_objectid')->type(Column::TYPE_INT)->nullable()->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('default_objectid', 'object', 'id'); + $table->addConstraint('folderobjectid', 'object', 'id'); + $table->addConstraint('templateid', 'template', 'id'); - $this->addIndex('element','templateid'); - $this->addIndex('element','name'); - $this->addUniqueIndex('element','templateid,name'); + $table->addIndex('templateid'); + $table->addIndex('name'); + $table->addUniqueIndex(['templateid','name']); - $this->addTable('file'); - $this->addColumn('file','objectid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('file','extension',OR_DB_COLUMN_TYPE_VARCHAR,10,null,$not_nullable); - $this->addColumn('file','size',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('file','value',OR_DB_COLUMN_TYPE_BLOB,null,null,$not_nullable); - $this->addPrimaryKey('file','id'); - $this->addConstraint('file','objectid','object','id'); + $table = $this->table('file')->add(); + $table->column('objectid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('extension')->type(Column::TYPE_VARCHAR)->size(10)->add(); + $table->column('size')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('value')->type(Column::TYPE_BLOB)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('objectid', 'object', 'id'); - $this->addUniqueIndex('file','objectid'); + $table->addUniqueIndex('objectid'); - $this->addTable('folder'); - $this->addColumn('folder','objectid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addPrimaryKey('folder','id'); - $this->addConstraint('folder','objectid','object','id'); + $table = $this->table('folder')->add(); + $table->column('objectid')->type(Column::TYPE_INT)->size(0)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('objectid', 'object', 'id'); - $this->addUniqueIndex('folder','objectid'); + $table->addUniqueIndex('objectid'); - $this->addTable('link'); - $this->addColumn('link','objectid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('link','link_objectid',OR_DB_COLUMN_TYPE_INT,null,null,$nullable); - $this->addColumn('link','url',OR_DB_COLUMN_TYPE_VARCHAR,255,null,$nullable); - $this->addPrimaryKey('link','id'); - $this->addConstraint('link','objectid','object','id'); - $this->addConstraint('link','link_objectid','object','id'); + $table = $this->table('link')->add(); + $table->column('objectid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('link_objectid')->type(Column::TYPE_INT)->nullable()->add(); + $table->column('url')->type(Column::TYPE_VARCHAR)->size(255)->nullable()->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('objectid', 'object', 'id'); + $table->addConstraint('link_objectid', 'object', 'id'); - $this->addUniqueIndex('link','objectid'); - $this->addIndex('link','link_objectid'); + $table->addUniqueIndex('objectid'); + $table->addIndex('link_objectid'); - $this->addTable('name'); - $this->addColumn('name','objectid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('name','name',OR_DB_COLUMN_TYPE_VARCHAR,255,null,$not_nullable); - $this->addColumn('name','descr',OR_DB_COLUMN_TYPE_VARCHAR,255,null,$not_nullable); - $this->addColumn('name','languageid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addPrimaryKey('name','id'); - $this->addConstraint('name','objectid','object','id'); - $this->addConstraint('name','languageid','language','id'); + $table = $this->table('name')->add(); + $table->column('objectid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('name')->type(Column::TYPE_VARCHAR)->size(255)->add(); + $table->column('descr')->type(Column::TYPE_VARCHAR)->size(255)->add(); + $table->column('languageid')->type(Column::TYPE_INT)->size(0)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('objectid', 'object', 'id'); + $table->addConstraint('languageid', 'language', 'id'); - $this->addIndex('name','objectid'); - $this->addIndex('name','languageid'); - $this->addUniqueIndex('name','objectid,languageid'); + $table->addIndex('objectid'); + $table->addIndex('languageid'); + $table->addUniqueIndex(['objectid','languageid']); - $this->addTable('templatemodel'); - $this->addColumn('templatemodel','templateid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('templatemodel','projectmodelid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('templatemodel','extension',OR_DB_COLUMN_TYPE_VARCHAR,10,null,$nullable); - $this->addColumn('templatemodel','text',OR_DB_COLUMN_TYPE_TEXT,null,null,$not_nullable); - $this->addPrimaryKey('templatemodel','id'); - $this->addConstraint('templatemodel','templateid','template','id'); - $this->addConstraint('templatemodel','projectmodelid','projectmodel','id'); + $table = $this->table('templatemodel')->add(); + $table->column('templateid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('projectmodelid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('extension')->type(Column::TYPE_VARCHAR)->size(10)->nullable()->add(); + $table->column('text')->type(Column::TYPE_TEXT)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('templateid', 'template', 'id'); + $table->addConstraint('projectmodelid', 'projectmodel', 'id'); - $this->addIndex('templatemodel','templateid'); - $this->addUniqueIndex('templatemodel','templateid,extension'); - $this->addUniqueIndex('templatemodel','templateid,projectmodelid'); + $table->addIndex('templateid'); + $table->addUniqueIndex(['templateid','extension' ]); + $table->addUniqueIndex(['templateid','projectmodelid']); - $this->addTable('usergroup'); - $this->addColumn('usergroup','userid',OR_DB_COLUMN_TYPE_INT,null,null,$not_nullable); - $this->addColumn('usergroup','groupid',OR_DB_COLUMN_TYPE_INT,null,null,$not_nullable); - $this->addPrimaryKey('usergroup','id'); - $this->addConstraint('usergroup','groupid','group','id'); - $this->addConstraint('usergroup','userid','user','id'); + $table = $this->table('usergroup')->add(); + $table->column('userid')->type(Column::TYPE_INT)->add(); + $table->column('groupid')->type(Column::TYPE_INT)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('groupid', 'group', 'id'); + $table->addConstraint('userid', 'user', 'id'); - $this->addIndex('usergroup','groupid'); - $this->addIndex('usergroup','userid'); - $this->addUniqueIndex('usergroup','userid,groupid'); + $table->addIndex('groupid'); + $table->addIndex('userid'); + $table->addUniqueIndex(['userid','groupid']); - $this->addTable('value'); - $this->addColumn('value','pageid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('value','languageid',OR_DB_COLUMN_TYPE_INT,null,null,$not_nullable); - $this->addColumn('value','elementid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('value','linkobjectid',OR_DB_COLUMN_TYPE_INT,null,null,$nullable); - $this->addColumn('value','text',OR_DB_COLUMN_TYPE_TEXT,null,null,$nullable); - $this->addColumn('value','number',OR_DB_COLUMN_TYPE_INT,null,null,$nullable); - $this->addColumn('value','date',OR_DB_COLUMN_TYPE_INT,null,null,$nullable); - $this->addColumn('value','active',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('value','lastchange_date',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('value','lastchange_userid',OR_DB_COLUMN_TYPE_INT,null,null,$nullable); - $this->addColumn('value','publish',OR_DB_COLUMN_TYPE_INT,null,null,$not_nullable); - $this->addPrimaryKey('value','id'); - $this->addConstraint('value','pageid','page','id'); - $this->addConstraint('value','elementid','element','id'); - $this->addConstraint('value','languageid','language','id'); - $this->addConstraint('value','lastchange_userid','user','id'); - $this->addConstraint('value','linkobjectid','object','id'); + $table = $this->table('value')->add(); + $table->column('pageid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('languageid')->type(Column::TYPE_INT)->add(); + $table->column('elementid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('linkobjectid')->type(Column::TYPE_INT)->nullable()->add(); + $table->column('text')->type(Column::TYPE_TEXT)->nullable()->add(); + $table->column('number')->type(Column::TYPE_INT)->nullable()->add(); + $table->column('date')->type(Column::TYPE_INT)->nullable()->add(); + $table->column('active')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('lastchange_date')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('lastchange_userid')->type(Column::TYPE_INT)->nullable()->add(); + $table->column('publish')->type(Column::TYPE_INT)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('pageid', 'page', 'id'); + $table->addConstraint('elementid', 'element', 'id'); + $table->addConstraint('languageid', 'language', 'id'); + $table->addConstraint('lastchange_userid', 'user', 'id'); + $table->addConstraint('linkobjectid', 'object', 'id'); - $this->addIndex('value','pageid'); - $this->addIndex('value','languageid'); - $this->addIndex('value','elementid'); - $this->addIndex('value','active'); - $this->addIndex('value','lastchange_date'); - $this->addIndex('value','publish'); + $table->addIndex('pageid'); + $table->addIndex('languageid'); + $table->addIndex('elementid'); + $table->addIndex('active'); + $table->addIndex('lastchange_date'); + $table->addIndex('publish'); - $this->addTable('acl'); - $this->addColumn('acl','userid',OR_DB_COLUMN_TYPE_INT,null,null,$nullable); - $this->addColumn('acl','groupid',OR_DB_COLUMN_TYPE_INT,null,null,$nullable); - $this->addColumn('acl','objectid',OR_DB_COLUMN_TYPE_INT,null,null,$not_nullable); - $this->addColumn('acl','languageid',OR_DB_COLUMN_TYPE_INT,0,null,$nullable); - $this->addColumn('acl','is_write',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('acl','is_prop',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('acl','is_create_folder',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('acl','is_create_file',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('acl','is_create_link',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('acl','is_create_page',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('acl','is_delete',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('acl','is_release',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('acl','is_publish',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('acl','is_grant',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addColumn('acl','is_transmit',OR_DB_COLUMN_TYPE_INT,1,0,$not_nullable); - $this->addPrimaryKey('acl','id'); - $this->addConstraint('acl','groupid','group','id'); - $this->addConstraint('acl','userid','user','id'); - $this->addConstraint('acl','objectid','object','id'); - $this->addConstraint('acl','languageid','language','id'); - - $this->addIndex('acl','userid'); - $this->addIndex('acl','groupid'); - $this->addIndex('acl','languageid'); - $this->addIndex('acl','objectid'); - $this->addIndex('acl','is_transmit'); + $table = $this->table('acl')->add(); + $table->column('userid')->type(Column::TYPE_INT)->nullable()->add(); + $table->column('groupid')->type(Column::TYPE_INT)->nullable()->add(); + $table->column('objectid')->type(Column::TYPE_INT)->add(); + $table->column('languageid')->type(Column::TYPE_INT)->size(0)->nullable()->add(); + $table->column('is_write')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('is_prop')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('is_create_folder')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('is_create_file')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('is_create_link')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('is_create_page')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('is_delete')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('is_release')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('is_publish')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('is_grant')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->column('is_transmit')->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); + $table->addPrimaryKey('id'); + $table->addConstraint('groupid', 'group', 'id'); + $table->addConstraint('userid', 'user', 'id'); + $table->addConstraint('objectid', 'object', 'id'); + $table->addConstraint('languageid', 'language', 'id'); + + $table->addIndex('userid'); + $table->addIndex('groupid'); + $table->addIndex('languageid'); + $table->addIndex('objectid'); + $table->addIndex('is_transmit'); $this->afterUpdate( $this->getDb() ); } @@ -335,5 +333,3 @@ class DBVersion000001 extends DbVersion } - -?>- \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000002.class.php b/modules/cms/update/version/DBVersion000002.class.php @@ -3,6 +3,8 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; + /** * new table: version. * @@ -13,16 +15,14 @@ class DBVersion000002 extends DbVersion { public function update() { - $this->addTable('version'); + $table = $this->table('version')->add(); - $this->addColumn('version','version' ,OR_DB_COLUMN_TYPE_INT,null,null,false); - $this->addColumn('version','status' ,OR_DB_COLUMN_TYPE_INT,null,null,false); - $this->addColumn('version','installed',OR_DB_COLUMN_TYPE_INT,null,null,false); + $table->column('version' )->type(Column::TYPE_INT)->add(); + $table->column('status' )->type(Column::TYPE_INT)->add(); + $table->column('installed')->type(Column::TYPE_INT)->add(); - $this->addPrimaryKey ('version','id' ); - $this->addIndex ('version','status' ); - $this->addUniqueIndex('version','version' ); + $table->addPrimaryKey ('id' ); + $table->addIndex ('status'); + $table->addUniqueIndex('version'); } } - -?>- \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000003.class.php b/modules/cms/update/version/DBVersion000003.class.php @@ -15,5 +15,3 @@ class DBVersion000003 extends DbVersion // Dummy version. } } - -?>- \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000004.class.php b/modules/cms/update/version/DBVersion000004.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; /** * Add Columns for user language and user timezone. @@ -14,16 +15,13 @@ class DBVersion000004 extends DbVersion { public function update() { - $not_nullable = false; - $nullable = true; - // Add user language - $this->addColumn('user','language',OR_DB_COLUMN_TYPE_VARCHAR, 2,null,$nullable); + $table = $this->table('user'); + + $table->column('language')->type(Column::TYPE_VARCHAR)->size( 2)->nullable()->add(); // Add user timezone - $this->addColumn('user','timezone',OR_DB_COLUMN_TYPE_VARCHAR,64,null,$nullable); + $table->column('timezone')->type(Column::TYPE_VARCHAR)->size(64)->nullable()->add(); } } - -?>- \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000005.class.php b/modules/cms/update/version/DBVersion000005.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; /** * Security enhancements. @@ -14,23 +15,19 @@ class DBVersion000005 extends DbVersion { public function update() { - $not_nullable = false; - $nullable = true; - + $table = $this->table('user'); + // longer Passwords! 50 is not enough. - $this->addColumn('user','password_hash',OR_DB_COLUMN_TYPE_VARCHAR,255,null,$not_nullable); + $table->column('password_hash')->type(Column::TYPE_VARCHAR)->size(255)->add(); $db = $this->getDb(); - $table = $this->getTableName('user'); - $updateStmt = $db->sql('UPDATE '.$table. + $updateStmt = $db->sql('UPDATE '.$table->getSqlName(). ' SET password_hash=password' ); $updateStmt->query(); - $this->dropColumn('user','password'); + $table->column('password')->drop(); - $this->addColumn('user','password_salt',OR_DB_COLUMN_TYPE_VARCHAR,255,null,$not_nullable); + $table->column('password_salt')->type(Column::TYPE_VARCHAR)->size(255)->add(); } -} - -?>- \ No newline at end of file +}+ \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000006.class.php b/modules/cms/update/version/DBVersion000006.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; /** * Security enhancements. @@ -14,27 +15,23 @@ class DBVersion000006 extends DbVersion { public function update() { - $not_nullable = false; - $nullable = true; - - $this->addColumn('user','password_expires',OR_DB_COLUMN_TYPE_INT,0,null,$nullable); + $table = $this->table('user'); + + $table->column('password_expires')->type(Column::TYPE_INT)->size(0)->nullable()->add(); - $this->addColumn('user','last_login' ,OR_DB_COLUMN_TYPE_INT,0,null,$nullable); + $table->column('last_login' )->type(Column::TYPE_INT)->size(0)->nullable()->add(); - $this->addColumn('user','password_algo' ,OR_DB_COLUMN_TYPE_INT,0,2,$not_nullable); + $table->column('password_algo' )->type(Column::TYPE_INT)->size(0)->defaultValue(2)->add(); // Setting Password algo. Passwords beginning with '$' are (old) MD5-hashes. // SUBSTR(s,pos,length) is supported by MySql,Postgres,SQLite // SUBSTRING(s FROM pos FOR length) is NOT supported by SQLite - $table = $this->getTableName('user'); $db = $this->getDb(); - $updateAlgoStmt = $db->sql('UPDATE '.$table. + $updateAlgoStmt = $db->sql('UPDATE '.$table->getSqlName(). ' SET password_algo=1 WHERE SUBSTR(password_hash,1,1) = '."'$'".';' ); $updateAlgoStmt->query(); } -} - -?>- \ No newline at end of file +}+ \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000007.class.php b/modules/cms/update/version/DBVersion000007.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; use security\Password; /** @@ -15,28 +16,24 @@ class DBVersion000007 extends DbVersion { public function update() { - $not_nullable = false; - $nullable = true; - - $this->addColumn('user','otp_secret' ,OR_DB_COLUMN_TYPE_VARCHAR,255,null,$nullable ); + $table = $this->table('user'); + $table->column('otp_secret' )->type(Column::TYPE_VARCHAR)->size(255)->nullable()->add(); - $table = $this->getTableName('user'); + $tableName = $table->getSqlName(); $db = $this->getDb(); - $stmt = $db->sql('SELECT id FROM '.$table); + $stmt = $db->sql('SELECT id FROM '.$tableName); foreach($stmt->getCol() as $userid ) { $secret = Password::randomHexString(64); - $stmt = $db->sql('UPDATE '.$table.' SET otp_secret={secret} WHERE id={id}'); + $stmt = $db->sql('UPDATE '.$tableName.' SET otp_secret={secret} WHERE id={id}'); $stmt->setString('secret',$secret); $stmt->setInt('id',$userid); $stmt->query(); } - $this->addColumn('user','totp' ,OR_DB_COLUMN_TYPE_INT , 1, 0,$not_nullable); - $this->addColumn('user','hotp_counter',OR_DB_COLUMN_TYPE_INT , 0, 0,$not_nullable); - $this->addColumn('user','hotp' ,OR_DB_COLUMN_TYPE_INT , 1, 0,$not_nullable); + $table->column('totp' )->type(Column::TYPE_INT )->size( 1)->defaultValue( 0)->add(); + $table->column('hotp_counter')->type(Column::TYPE_INT )->size( 0)->defaultValue( 0)->add(); + $table->column('hotp' )->type(Column::TYPE_INT )->size( 1)->defaultValue( 0)->add(); } -} - -?>- \ No newline at end of file +}+ \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000008.class.php b/modules/cms/update/version/DBVersion000008.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; use security\Password; /** @@ -18,41 +19,38 @@ class DBVersion000008 extends DbVersion */ public function update() { - $not_nullable = false; - $nullable = true; + $table = $this->table('object'); - $this->addColumn('object','typeid',OR_DB_COLUMN_TYPE_INT,2,0,$not_nullable); - $this->addIndex('object','typeid'); + $table->column('typeid')->type(Column::TYPE_INT)->size(2)->defaultValue(0)->add(); + $table->addIndex('typeid'); // Converting old values... $db = $this->getDb(); - $table = $this->getTableName('object'); + $tableName = $table->getSqlName(); - $updateStmt = $db->sql('UPDATE '.$table. + $updateStmt = $db->sql('UPDATE '.$tableName. ' SET typeid=1 WHERE is_folder=1' ); $updateStmt->query(); - $updateStmt = $db->sql('UPDATE '.$table. + $updateStmt = $db->sql('UPDATE '.$tableName. ' SET typeid=2 WHERE is_file=1' ); $updateStmt->query(); - $updateStmt = $db->sql('UPDATE '.$table. + $updateStmt = $db->sql('UPDATE '.$tableName. ' SET typeid=3 WHERE is_page=1' ); $updateStmt->query(); - $updateStmt = $db->sql('UPDATE '.$table. + $updateStmt = $db->sql('UPDATE '.$tableName. ' SET typeid=4 WHERE is_link=1' ); $updateStmt->query(); - $this->dropColumn('object','is_folder'); - $this->dropColumn('object','is_file'); - $this->dropColumn('object','is_page'); - $this->dropColumn('object','is_link'); + $table->column('is_folder')->drop(); + $table->column('is_file' )->drop(); + $table->column('is_page' )->drop(); + $table->column('is_link' )->drop(); } -} - -?>- \ No newline at end of file +}+ \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000009.class.php b/modules/cms/update/version/DBVersion000009.class.php @@ -5,6 +5,7 @@ namespace cms\update\version; use cms\model\BaseObject; use database\DbVersion; +use database\Column; use security\Password; /** @@ -23,41 +24,37 @@ class DBVersion000009 extends DbVersion */ public function update() { - $not_nullable = false; - $nullable = true; - // Creating new table 'url' - $this->addTable('url'); - $this->addColumn('url','objectid',OR_DB_COLUMN_TYPE_INT,0,null,$not_nullable); - $this->addColumn('url','url',OR_DB_COLUMN_TYPE_VARCHAR,255,null,$not_nullable); + $table = $this->table('url')->add(); + $table->column('objectid')->type(Column::TYPE_INT)->size(0)->add(); + $table->column('url')->type(Column::TYPE_VARCHAR)->size(255)->add(); - $this->addPrimaryKey('url','id'); - $this->addConstraint('url','objectid','object','id'); + $table->addPrimaryKey('id'); + $table->addConstraint('objectid', 'object', 'id'); - $this->addUniqueIndex('url','objectid'); + $table->addUniqueIndex('objectid'); // Copying values from table 'link' to new table 'url' $db = $this->getDb(); - $insertStmt = $db->sql('INSERT INTO '.$this->getTableName('url'). - ' (id,objectid,url) SELECT id,objectid,url FROM '.$this->getTableName('link').' WHERE url is not null' + $insertStmt = $db->sql('INSERT INTO '.$table->getSqlName(). + ' (id,objectid,url) SELECT id,objectid,url FROM '.$this->table('link')->getSqlName().' WHERE url is not null' ); $insertStmt->query(); // Updating the typeid for URL entrys in table 'object' - $updateStmt = $db->sql('UPDATE '.$this->getTableName('object'). - ' SET typeid='.BaseObject::TYPEID_URL.' WHERE id IN (SELECT objectid FROM '.$this->getTableName('url').')' + $updateStmt = $db->sql('UPDATE '.$this->table('object')->getSqlName(). + ' SET typeid='.BaseObject::TYPEID_URL.' WHERE id IN (SELECT objectid FROM '.$this->table('url')->getSqlName().')' ); $updateStmt->query(); + $tableLink = $this->table('link'); // Remove old entrys in table 'link' - $updateStmt = $db->sql('DELETE FROM '.$this->getTableName('link').' WHERE url is not null' + $updateStmt = $db->sql('DELETE FROM '.$tableLink->getSqlName().' WHERE url is not null' ); $updateStmt->query(); // Cleanup: Drop unused column. - $this->dropColumn('link','url'); + $tableLink->column('url')->drop(); } -} - -?>- \ No newline at end of file +}+ \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000010.class.php b/modules/cms/update/version/DBVersion000010.class.php @@ -6,7 +6,7 @@ use database\DbVersion; use security\Password; /** - * Filetype 'file' is now devided into 'file' (unchanged), 'image' (new) and OR_DB_COLUMN_TYPE_TEXT (new). + * Filetype 'file' is now devided into 'file' (unchanged), 'image' (new) and Column::TYPE_TEXT (new). * * @author dankert * @@ -18,9 +18,9 @@ class DBVersion000010 extends DbVersion */ public function update() { - $db = $this->getDb(); - $tableObject = $this->getTableName('object'); - $tableFile = $this->getTableName('file'); + $db = $this->getDb(); + $tableObject = $this->table('object')->getSqlName(); + $tableFile = $this->table('file' )->getSqlName(); $updateStmt = $db->sql('UPDATE '.$tableObject. ' SET typeid=6 WHERE id IN (SELECT objectid FROM '.$tableFile. @@ -34,8 +34,5 @@ class DBVersion000010 extends DbVersion ); $updateStmt->query(); - } -} - -?>- \ No newline at end of file +}+ \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000011.class.php b/modules/cms/update/version/DBVersion000011.class.php @@ -4,7 +4,7 @@ namespace cms\update\version; use database\DbVersion; -use security\Password; +use database\Column; /** * Project gets new columns. @@ -19,15 +19,13 @@ class DBVersion000011 extends DbVersion */ public function update() { - $not_nullable = false; - $nullable = true; + $table = $this->table('project'); - - $this->addColumn('project', 'url', OR_DB_COLUMN_TYPE_VARCHAR, 255, '', $not_nullable); - $this->addColumn('project', 'flags', OR_DB_COLUMN_TYPE_INT, 11, 0, $not_nullable); + $table->column( 'url')->type( Column::TYPE_VARCHAR)->size( 255)->defaultValue( '')->add(); + $table->column( 'flags')->type( Column::TYPE_INT)->size( 11)->defaultValue( 0)->add(); $db = $this->getDb(); - $tableProject = $this->getTableName('project'); + $tableProject = $table->getSqlName(); // Update the url $updateStmt = $db->sql(<<<SQL @@ -53,8 +51,8 @@ SQL $updateStmt->query(); // now the information is hold in column 'flags', so we can delete the old columns. - $this->dropColumn('project', 'cut_index'); - $this->dropColumn('project', 'content_negotiation'); + $table->column( 'cut_index')->drop(); + $table->column('content_negotiation')->drop(); } } diff --git a/modules/cms/update/version/DBVersion000012.class.php b/modules/cms/update/version/DBVersion000012.class.php @@ -3,7 +3,7 @@ namespace cms\update\version; use database\DbVersion; -use security\Password; +use database\Column; /** * Objects gets new columns for storing the publish date. @@ -18,10 +18,9 @@ class DBVersion000012 extends DbVersion */ public function update() { - $this->addColumn('object','published_date' ,OR_DB_COLUMN_TYPE_INT,0,null,OR_DB_COLUMN_NULLABLE); - $this->addColumn('object','published_userid',OR_DB_COLUMN_TYPE_INT,0,null,OR_DB_COLUMN_NULLABLE); - $this->addConstraint('object','published_userid','user','id'); + $table = $this->table('object'); + $table->column('published_date' )->type(Column::TYPE_INT)->size(0)->nullable()->add(); + $table->column('published_userid')->type(Column::TYPE_INT)->size(0)->nullable()->add(); + $table->addConstraint('published_userid', 'user', 'id'); } -} - -?>- \ No newline at end of file +}+ \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000013.class.php b/modules/cms/update/version/DBVersion000013.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; use security\Password; /** @@ -18,8 +19,7 @@ class DBVersion000013 extends DbVersion */ public function update() { - $this->addColumn('file','filterid' ,OR_DB_COLUMN_TYPE_INT,0,null,OR_DB_COLUMN_NULLABLE); + $table = $this->table('file'); + $table->column('filterid' )->type(Column::TYPE_INT)->size(0)->nullable()->add(); } -} - -?>- \ No newline at end of file +}+ \ No newline at end of file diff --git a/modules/cms/update/version/DBVersion000014.class.php b/modules/cms/update/version/DBVersion000014.class.php @@ -3,10 +3,11 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; use security\Password; /** - * Objects gets new columns for storing the publish date. + * Objects gets new columns for storing the sort order. * * @author dankert * @@ -18,8 +19,9 @@ class DBVersion000014 extends DbVersion */ public function update() { - $this->addColumn('folder','order_by' ,OR_DB_COLUMN_TYPE_INT,0,null,OR_DB_COLUMN_NULLABLE); - $this->addColumn('folder','order_direction',OR_DB_COLUMN_TYPE_INT,0,null,OR_DB_COLUMN_NULLABLE); + $table = $this->table('folder'); + $table->column('order_by' )->type(Column::TYPE_INT)->size(0)->nullable()->add(); + $table->column('order_direction')->type(Column::TYPE_INT)->size(0)->nullable()->add(); } } diff --git a/modules/cms/update/version/DBVersion000015.class.php b/modules/cms/update/version/DBVersion000015.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; use security\Password; /** @@ -18,7 +19,7 @@ class DBVersion000015 extends DbVersion */ public function update() { - $this->addColumn('object','settings',OR_DB_COLUMN_TYPE_TEXT,0,null,OR_DB_COLUMN_NOT_NULLABLE); + $this->table('object')->column('settings')->type(Column::TYPE_TEXT)->size(0)->add(); } } diff --git a/modules/cms/update/version/DBVersion000016.class.php b/modules/cms/update/version/DBVersion000016.class.php @@ -3,7 +3,7 @@ namespace cms\update\version; use database\DbVersion; -use security\Password; +use database\Column; /** * Converting element types from string to number. @@ -18,11 +18,13 @@ class DBVersion000016 extends DbVersion */ public function update() { + $table = $this->table('element'); + // Type 3 = Text is the default. - $this->addColumn('element','typeid',OR_DB_COLUMN_TYPE_INT,0,3,OR_DB_COLUMN_NOT_NULLABLE); + $table->column('typeid')->type(Column::TYPE_INT)->size(0)->defaultValue(3)->add(); $db = $this->getDb(); - $tableProject = $this->getTableName('element'); + $tableProject = $table->getSqlName(); // Update the types $conversionTable = array( @@ -52,11 +54,11 @@ SQL $updateStmt->query(); } - $this->dropColumn('element','type'); + $table->column('type')->drop(); - $this->addColumn('element','flags',OR_DB_COLUMN_TYPE_INT,0,0,OR_DB_COLUMN_NOT_NULLABLE); + $table->column('flags')->type(Column::TYPE_INT)->size(0)->defaultValue(0)->add(); $updateStmt = $db->sql(<<<SQL UPDATE $tableProject @@ -88,7 +90,7 @@ SQL - $this->addColumn('element','format',OR_DB_COLUMN_TYPE_INT,0,0,OR_DB_COLUMN_NOT_NULLABLE); + $table->column('format')->type(Column::TYPE_INT)->size(0)->defaultValue(0)->add(); // Format = HTML $updateStmt = $db->sql(<<<SQL @@ -109,11 +111,11 @@ SQL // Other formats were not supported up to this version. // Cleanup - $this->dropColumn('element','wiki' ); - $this->dropColumn('element','html' ); - $this->dropColumn('element','all_languages'); - $this->dropColumn('element','writable' ); - $this->dropColumn('element','with_icon' ); + $table->column('wiki' )->drop(); + $table->column('html' )->drop(); + $table->column('all_languages')->drop(); + $table->column('writable' )->drop(); + $table->column('with_icon' )->drop(); } diff --git a/modules/cms/update/version/DBVersion000017.class.php b/modules/cms/update/version/DBVersion000017.class.php @@ -2,8 +2,8 @@ namespace cms\update\version; +use database\Column; use database\DbVersion; -use security\Password; /** * Objects gets new columns for storing valid-dates. @@ -18,8 +18,10 @@ class DBVersion000017 extends DbVersion */ public function update() { - $this->addColumn('object','valid_from',OR_DB_COLUMN_TYPE_INT,0,null,OR_DB_COLUMN_NULLABLE); - $this->addColumn('object','valid_to' ,OR_DB_COLUMN_TYPE_INT,0,null,OR_DB_COLUMN_NULLABLE); + + $table = $this->table('object'); + $table->column('valid_from')->type(Column::TYPE_INT)->size(0)->nullable()->add(); + $table->column('valid_to' )->type(Column::TYPE_INT)->size(0)->nullable()->add(); } } diff --git a/modules/cms/update/version/DBVersion000018.class.php b/modules/cms/update/version/DBVersion000018.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; use security\Password; /** @@ -18,10 +19,11 @@ class DBVersion000018 extends DbVersion */ public function update() { - $this->addColumn('element','label' ,OR_DB_COLUMN_TYPE_VARCHAR,100,'',OR_DB_COLUMN_NOT_NULLABLE); + $table = $this->table('element'); + $table->column('label' )->type(Column::TYPE_VARCHAR)->size(100)->defaultValue('')->add(); // Initial Value for Labels is the element name. - $tableElement = $this->getTableName('element'); + $tableElement = $table->getSqlName(); $updateStmt = $this->getDb()->sql(<<<SQL UPDATE $tableElement @@ -30,7 +32,7 @@ SQL ); $updateStmt->query(); - $this->addUniqueIndex('element','templateid,label'); + $table->addUniqueIndex(['templateid','label']); } } diff --git a/modules/cms/update/version/DBVersion000019.class.php b/modules/cms/update/version/DBVersion000019.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; use security\Password; /** @@ -18,11 +19,12 @@ class DBVersion000019 extends DbVersion */ public function update() { - $this->addColumn('value','format' ,OR_DB_COLUMN_TYPE_INT,1,0,OR_DB_COLUMN_NOT_NULLABLE); + $table = $this->table('value'); + $table->column('format' )->type(Column::TYPE_INT)->size(1)->defaultValue(0)->add(); // Initial Value: Copy from element. - $tableValue = $this->getTableName('value'); - $tableElement = $this->getTableName('element'); + $tableValue = $this->table('value')->getSqlName(); + $tableElement = $this->table('element')->getSqlName(); $updateStmt = $this->getDb()->sql(<<<SQL UPDATE $tableValue diff --git a/modules/cms/update/version/DBVersion000020.class.php b/modules/cms/update/version/DBVersion000020.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; /** * Aliases for node objects. @@ -17,23 +18,22 @@ class DBVersion000020 extends DbVersion */ public function update() { - $this->addTable('alias'); + $table = $this->table('alias')->add(); - $this->addColumn('alias','objectid' ,OR_DB_COLUMN_TYPE_INT,null,null,OR_DB_COLUMN_NOT_NULLABLE); - $this->addColumn('alias','languageid' ,OR_DB_COLUMN_TYPE_INT,null,null,OR_DB_COLUMN_NULLABLE); - $this->addColumn('alias','link_objectid',OR_DB_COLUMN_TYPE_INT,null,null,OR_DB_COLUMN_NOT_NULLABLE); + $table->column('objectid' )->type(Column::TYPE_INT)->add(); + $table->column('languageid' )->type(Column::TYPE_INT)->nullable()->add(); + $table->column('link_objectid')->type(Column::TYPE_INT)->add(); - $this->addPrimaryKey ('alias','id'); + $table->addPrimaryKey ('id'); - $this->addConstraint ('alias','objectid' ,'object' ,'id'); - $this->addConstraint ('alias','languageid' ,'language','id'); - $this->addConstraint ('alias','link_objectid','object' ,'id'); - - $this->addUniqueIndex('alias','objectid' ); - $this->addUniqueIndex('alias','link_objectid,languageid'); - $this->addIndex ('alias','link_objectid' ); + $table->addConstraint ('objectid' ,'object' ,'id'); + $table->addConstraint ('languageid' ,'language','id'); + $table->addConstraint ('link_objectid','object' ,'id'); + $table->addUniqueIndex('objectid'); + $table->addUniqueIndex(['link_objectid','languageid']); + $table->addIndex ('link_objectid'); } } diff --git a/modules/cms/update/version/DBVersion000021.class.php b/modules/cms/update/version/DBVersion000021.class.php @@ -3,6 +3,7 @@ namespace cms\update\version; use database\DbVersion; +use database\Column; /** * Authentication tokens. @@ -17,23 +18,23 @@ class DBVersion000021 extends DbVersion */ public function update() { - $this->addTable('auth'); + $table = $this->table('auth')->add(); - $this->addColumn('auth','selector' ,OR_DB_COLUMN_TYPE_VARCHAR ,150 ,null,OR_DB_COLUMN_NOT_NULLABLE); - $this->addColumn('auth','userid' ,OR_DB_COLUMN_TYPE_INT ,0 ,null,OR_DB_COLUMN_NOT_NULLABLE); - $this->addColumn('auth','token' ,OR_DB_COLUMN_TYPE_VARCHAR ,150 ,null,OR_DB_COLUMN_NOT_NULLABLE); - $this->addColumn('auth','token_algo' ,OR_DB_COLUMN_TYPE_INT ,0 ,0 ,OR_DB_COLUMN_NOT_NULLABLE); - $this->addColumn('auth','expires' ,OR_DB_COLUMN_TYPE_INT ,0 ,null,OR_DB_COLUMN_NOT_NULLABLE); - $this->addColumn('auth','create_date' ,OR_DB_COLUMN_TYPE_INT ,0 ,null,OR_DB_COLUMN_NOT_NULLABLE); - $this->addColumn('auth','platform' ,OR_DB_COLUMN_TYPE_VARCHAR,255 ,null,OR_DB_COLUMN_NOT_NULLABLE); - $this->addColumn('auth','name' ,OR_DB_COLUMN_TYPE_VARCHAR,255 ,null,OR_DB_COLUMN_NOT_NULLABLE); + $table->column('selector' )->type(Column::TYPE_VARCHAR )->size(150 )->add(); + $table->column('userid' )->type(Column::TYPE_INT )->size(0 )->add(); + $table->column('token' )->type(Column::TYPE_VARCHAR )->size(150 )->add(); + $table->column('token_algo' )->type(Column::TYPE_INT )->size(0 )->defaultValue(0 )->add(); + $table->column('expires' )->type(Column::TYPE_INT )->size(0 )->add(); + $table->column('create_date' )->type(Column::TYPE_INT )->size(0 )->add(); + $table->column('platform' )->type(Column::TYPE_VARCHAR)->size(255 )->add(); + $table->column('name' )->type(Column::TYPE_VARCHAR)->size(255 )->add(); - $this->addPrimaryKey ('auth','id'); + $table->addPrimaryKey ('id'); - $this->addConstraint ('auth','userid' ,'user' ,'id'); + $table->addConstraint ('userid' ,'user' ,'id'); - $this->addUniqueIndex('auth','selector' ); + $table->addUniqueIndex('selector'); } } diff --git a/modules/database/Column.class.php b/modules/database/Column.class.php @@ -0,0 +1,166 @@ +<?php + + +namespace database; + + +class Column +{ + const TYPE_INT = 1; + const TYPE_VARCHAR = 2; + const TYPE_TEXT = 3; + const TYPE_BLOB = 4; + + private $db; + private $dbmsType; + private $name; + private $table; + + private $type = self::TYPE_INT; + private $size = null; + private $default = null; + private $nullable = false; + + public function type( $type ) { + $this->type = $type; + return $this; + } + + + public function size( $size ) { + $this->size = $size; + return $this; + } + + public function defaultValue( $default ) { + $this->default = $default; + return $this; + } + + + public function nullable() { + $this->nullable = true;; + return $this; + } + + /** + * Column constructor. + * + * @param $db Database + * @param $type + * @param $table Table + * @param $name + */ + public function __construct($db, $type, $table, $name) + { + $this->db = $db; + $this->dbmsType = $type; + $this->table = $table; + $this->name = $name; + } + + + /** + * Creating a new column. + */ + function add() + { + $table = $this->table->getSqlName(); + + switch ($this->type) { + case self::TYPE_INT: + switch ($this->dbmsType) { + case OR_DB_TYPE_MYSQL: + if ($this->size == 1) + $dbmsInternalType = 'TINYINT'; + else + $dbmsInternalType = 'INT'; + break; + + case OR_DB_TYPE_ORACLE: + $dbmsInternalType = 'NUMBER'; + break; + + default: + $dbmsInternalType = 'INTEGER'; + + } + break; + + case self::TYPE_VARCHAR: + switch ($this->dbmsType) { + default: + $dbmsInternalType = 'VARCHAR'; + + } + break; + + case self::TYPE_TEXT: + switch ($this->dbmsType) { + case OR_DB_TYPE_MYSQL: + $dbmsInternalType = 'MEDIUMTEXT'; + break; + + case OR_DB_TYPE_ORACLE: + $dbmsInternalType = 'CLOB'; + break; + + default: + $dbmsInternalType = 'TEXT'; + + } + break; + + case self::TYPE_BLOB: + switch ($this->dbmsType) { + case OR_DB_TYPE_MYSQL: + $dbmsInternalType = 'MEDIUMBLOB'; + break; + + case OR_DB_TYPE_ORACLE: + $dbmsInternalType = 'CLOB'; + break; + + case OR_DB_TYPE_POSTGRES: + $dbmsInternalType = 'TEXT'; + break; + + case OR_DB_TYPE_SQLITE: + $dbmsInternalType = 'TEXT'; + break; + + default: + $dbmsInternalType = 'BLOB'; + + } + break; + default: + throw new \LogicException( 'Unknown Column type: ' . $this->type); + } + + if ($this->dbmsType == OR_DB_TYPE_ORACLE) { + // TEXT-columns must be nullable in Oracle, because empty strings are treated as NULL. BAD BAD BAD, Oracle! + if ($this->type == self::TYPE_VARCHAR || $this->type == self::TYPE_TEXT) + $nullable = true; + + } + + $ddl = $this->db->sql('ALTER TABLE ' . $table . + ' ADD COLUMN ' . $this->name . ' ' . $dbmsInternalType . ($this->size != null ? '(' . $this->size . ')' : '') . + ($this->default !== null ? ' DEFAULT ' . (is_string($this->default) ? "'" : '') . $this->default . (is_string($this->default) ? "'" : '') : '') . + ' ' . ($this->nullable ? 'NULL' : 'NOT NULL') . ';' + ); + $ddl->query(); + + return $this; + } + + + function drop() + { + $table = $this->table->getSqlName(); + + $ddl = $this->db->sql('ALTER TABLE ' . $table . ' DROP COLUMN ' . $this->name . ';'); + $ddl->query(); + } +}+ \ No newline at end of file diff --git a/modules/database/DbVersion.class.php b/modules/database/DbVersion.class.php @@ -1,326 +1,84 @@ <?php -namespace database { - - define('OR_DB_INDEX_PREFIX', 'IX'); - define('OR_DB_CONSTRAINT_PREFIX', 'FK'); - - define('OR_DB_TYPE_MYSQL',1); - define('OR_DB_TYPE_POSTGRES',2); - define('OR_DB_TYPE_SQLITE',3); - define('OR_DB_TYPE_ORACLE',4); - - define('OR_DB_COLUMN_TYPE_INT',1); - define('OR_DB_COLUMN_TYPE_VARCHAR',2); - define('OR_DB_COLUMN_TYPE_TEXT',3); - define('OR_DB_COLUMN_TYPE_BLOB',4); - define('OR_DB_COLUMN_NULLABLE',true); - define('OR_DB_COLUMN_NOT_NULLABLE',false); - - - abstract class DbVersion - { - private $db; - private $tablePrefix; - private $tableSuffix; - - /** - * Datenbank-RDBMS-Typ - * @var int - */ - private $dbmsType; - - public function __construct(Database $db) - { - $this->db = $db; - - switch ($db->conf['type']) { - case 'mysql': - case 'mysqli': - $this->dbmsType = OR_DB_TYPE_MYSQL; - break; - case 'postgresql': - $this->dbmsType = OR_DB_TYPE_POSTGRES; - break; - case 'sqlite': - case 'sqlite3': - $this->dbmsType = OR_DB_TYPE_SQLITE; - break; - case 'pdo': - $dsnParts = explode(':', $db->conf['dsn']); - switch ($dsnParts[0]) { - case 'mysql': - $this->dbmsType = OR_DB_TYPE_MYSQL; - break; - case 'pgsql': - $this->dbmsType = OR_DB_TYPE_POSTGRES; - break; - case 'sqlite': - $this->dbmsType = OR_DB_TYPE_SQLITE; - break; - default: - throw new \LogicException('Unknown DBMS in PDO-DSN: ' . $dsnParts[0]); - } - break; - default: - throw new \LogicException('Unknown DBMS type: ' . $db->conf['type']); - } - - $this->tablePrefix = $db->conf['prefix']; - $this->tableSuffix = $db->conf['suffix']; - } - - // Muss überschrieben werden! - abstract function update(); - - - protected function getTableName($name) - { - return $this->tablePrefix . $name . $this->tableSuffix; - } - - - /** - * Erzeugt eine neue Tabelle. - * Die neue Tabelle enthält bereits eine Spalte "id" (da eine leere Tabelle i.d.R. nicht zulässig ist). - * @param $tableName string - */ - function addTable($tableName) - { - $tableName = $this->getTableName($tableName); - - $table_opts = $this->dbmsType == OR_DB_TYPE_MYSQL ? ' ENGINE=InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci' : ''; - - $ddl = $this->db->sql('CREATE TABLE ' . $tableName . '(id INTEGER)' . $table_opts . ';'); - // The syntax 'TYPE = InnoDB' was deprecated in MySQL 5.0 and was removed in MySQL 5.1 and later versions. - - $ddl->query(); - } - - - /** - * Creating a new column. - * @param $tableName string Table name - * @param $columnName string Column name - * @param $type int one of the constance OR_DB_COLUMN_TYPE_* - * @param $size int Size - * @param $default mixed Default value - * @param $nullable boolean - */ - function addColumn($tableName, $columnName, $type, $size, $default, $nullable) - { - $table = $this->getTableName($tableName); - - $type = strtoupper($type); - switch ($type) { - case OR_DB_COLUMN_TYPE_INT: - switch ($this->dbmsType) { - case OR_DB_TYPE_MYSQL: - if ($size == 1) - $dbmsInternalType = 'TINYINT'; - else - $dbmsInternalType = 'INT'; - break; - - case OR_DB_TYPE_ORACLE: - $dbmsInternalType = 'NUMBER'; - break; - - default: - $dbmsInternalType = 'INTEGER'; - - } - break; - - case OR_DB_COLUMN_TYPE_VARCHAR: - switch ($this->dbmsType) { - default: - $dbmsInternalType = 'VARCHAR'; - - } - break; - - case OR_DB_COLUMN_TYPE_TEXT: - switch ($this->dbmsType) { - case OR_DB_TYPE_MYSQL: - $dbmsInternalType = 'MEDIUMTEXT'; - break; - - case OR_DB_TYPE_ORACLE: - $dbmsInternalType = 'CLOB'; - break; - - default: - $dbmsInternalType = 'TEXT'; - - } - break; - - case OR_DB_COLUMN_TYPE_BLOB: - switch ($this->dbmsType) { - case OR_DB_TYPE_MYSQL: - $dbmsInternalType = 'MEDIUMBLOB'; - break; - - case OR_DB_TYPE_ORACLE: - $dbmsInternalType = 'CLOB'; - break; - - case OR_DB_TYPE_POSTGRES: - $dbmsInternalType = 'TEXT'; - break; - - case OR_DB_TYPE_SQLITE: - $dbmsInternalType = 'TEXT'; - break; - - default: - $dbmsInternalType = 'BLOB'; - - } - break; - default: - throw new \LogicException( 'Unknown Column type: ' . $type); - } - - if ($this->dbmsType == OR_DB_TYPE_ORACLE) { - // TEXT-columns must be nullable in Oracle, because empty strings are treated as NULL. BAD BAD BAD, Oracle! - if ($type == OR_DB_COLUMN_TYPE_VARCHAR || $type == OR_DB_COLUMN_TYPE_TEXT) - $nullable = true; - - } - - $ddl = $this->db->sql('ALTER TABLE ' . $table . - ' ADD COLUMN ' . $columnName . ' ' . $dbmsInternalType . ($size != null ? '(' . $size . ')' : '') . - ($default !== null ? ' DEFAULT ' . (is_string($default) ? "'" : '') . $default . (is_string($default) ? "'" : '') : '') . - ' ' . ($nullable ? 'NULL' : 'NOT NULL') . ';' - ); - $ddl->query(); - - } - - - function addPrimaryKey($tableName, $columnNames) - { - $table = $this->getTableName($tableName); - - if (!is_array($columnNames)) - $columnNames = explode(',', $columnNames); - - $ddl = $this->db->sql('ALTER TABLE ' . $table . ' ADD PRIMARY KEY (' . implode(',', $columnNames) . ');'); - $ddl->query(); - - } - - - - # Creating a unique key - # param 1: name of index column. Seperate multiple columns with ',' - function addIndex($tableName, $columnNames, $unique = false) - { - $table = $this->getTableName($tableName); - - if (!is_array($columnNames)) - $columnNames = explode(',', $columnNames); - - $indexName = $this->tablePrefix . OR_DB_INDEX_PREFIX . '_' . $tableName . '_' . implode('_', $columnNames) . $this->tableSuffix; - -// if [ "$type" == "oracle" ]; then -// cnt=$(($cnt+1)) -// echo "CREATE UNIQUE INDEX ${prefix}uidx_${cnt}" >> $outfile -// else - - $ddl = $this->db->sql('CREATE ' . ($unique ? 'UNIQUE ' : '') . 'INDEX ' . $indexName . ' ON ' . $table . ' (' . implode(',', $columnNames) . ');'); - $ddl->query(); - - } - - - /** - * Creating a unique key. - * param 1: name of index column. Seperate multiple columns with ',' - * - */ - function addUniqueIndex($tableName, $columnNames) - { - $this->addIndex($tableName, $columnNames, true); - } - - - # Creating a foreign key - # param 1: column name - # param 2: target table name - # param 3: target column name - function addConstraint($tableName, $columnName, $targetTableName, $targetColumnName) - { - $table = $this->getTableName($tableName); - $targetTable = $this->getTableName($targetTableName); - - $constraintName = $this->tablePrefix . OR_DB_CONSTRAINT_PREFIX . '_' . $tableName . $this->tableSuffix . '_' . $columnName; - - // Oracle doesn't support "ON DELETE RESTRICT"-Statements, but its the default. - - $ddl = $this->db->sql('ALTER TABLE ' . $table . ' ADD CONSTRAINT ' . $constraintName . ' FOREIGN KEY (' . $columnName . ') REFERENCES ' . $targetTable . ' (' . $targetColumnName . ') ON DELETE RESTRICT ON UPDATE RESTRICT;'); - $ddl->query(); - } - - - function dropTable($tableName) - { - $table = $this->getTableName($tableName); - - $ddl = $this->db->sql('DROP TABLE ' . $table . ';'); - $ddl->query(); - } - - function dropColumn($tableName, $columnName) - { - $table = $this->getTableName($tableName); - - $ddl = $this->db->sql('ALTER TABLE ' . $table . ' DROP COLUMN ' . $columnName . ';'); - $ddl->query(); - - - } - - function dropIndex($indexName, $unique = false) - { - $ddl = $this->db->sql('DROP' . ($unique ? ' UNIQUE' : '') . ' INDEX ' . $indexName . ';'); - $ddl->query(); - } - - function dropUniqueIndex($indexName) - { - $this->dropIndex($indexName, true); - } - - function dropPrimaryKey($tableName, $columnNames) - { - $table = $this->getTableName($tableName); - - if (!is_array($columnNames)) - $columnNames = explode(',', $columnNames); - - $ddl = $this->db->sql('ALTER TABLE ' . $table . ' DROP PRIMARY KEY(' . implode(',', $columnNames) . ')'); - $ddl->query(); - } - - - function dropConstraint($constraintName) - { - $ddl = $this->db->sql('DROP CONSTRAINT ' . $constraintName . ';'); - $ddl->query(); - } - - - /** - * @return Database - */ - function getDb() - { - return $this->db; - } - - } -}- \ No newline at end of file +namespace database; + +define('OR_DB_TYPE_MYSQL',1); +define('OR_DB_TYPE_POSTGRES',2); +define('OR_DB_TYPE_SQLITE',3); +define('OR_DB_TYPE_ORACLE',4); + + +abstract class DbVersion +{ + private $db; + private $tablePrefix; + private $tableSuffix; + + /** + * Datenbank-RDBMS-Typ + */ + private $dbmsType; + + /** + * DbVersion constructor. + * @param Database $db + */ + public function __construct(Database $db) + { + $this->db = $db; + + switch ($db->conf['type']) { + case 'mysql': + case 'mysqli': + $this->dbmsType = OR_DB_TYPE_MYSQL; + break; + case 'postgresql': + $this->dbmsType = OR_DB_TYPE_POSTGRES; + break; + case 'sqlite': + case 'sqlite3': + $this->dbmsType = OR_DB_TYPE_SQLITE; + break; + case 'pdo': + $dsnParts = explode(':', $db->conf['dsn']); + switch ($dsnParts[0]) { + case 'mysql': + $this->dbmsType = OR_DB_TYPE_MYSQL; + break; + case 'pgsql': + $this->dbmsType = OR_DB_TYPE_POSTGRES; + break; + case 'sqlite': + $this->dbmsType = OR_DB_TYPE_SQLITE; + break; + default: + throw new \LogicException('Unknown DBMS in PDO-DSN: ' . $dsnParts[0]); + } + break; + default: + throw new \LogicException('Unknown DBMS type: ' . $db->conf['type']); + } + + $this->tablePrefix = $db->conf['prefix']; + $this->tableSuffix = $db->conf['suffix']; + } + + // Muss überschrieben werden! + abstract function update(); + + + public function table( $tableName ) { + return new Table( $this->getDb(),$this->dbmsType, $tableName ); + } + + + /** + * @return Database + */ + function getDb() + { + return $this->db; + } + +} diff --git a/modules/database/Table.class.php b/modules/database/Table.class.php @@ -0,0 +1,183 @@ +<?php + + +namespace database; + + +class Table +{ + const INDEX_PREFIX = 'IX'; + const CONSTRAINT_PREFIX = 'FK'; + + + /** + * @var string + */ + private $tablePrefix; + + /** + * @var string + */ + private $tableSuffix; + + public function getSqlName() + { + return $this->tablePrefix . $this->name . $this->tableSuffix; + } + + + /** + * @var Database + */ + private $db; + private $dbmsType; + + /** + * Table name + * @var string + */ + private $name; + + /** + * Table constructor. + * + * @param $db Database + * @param $type + * @param $name + */ + public function __construct($db, $type, $name) + { + $this->db = $db; + $this->dbmsType = $type; + $this->name = $name; + + $this->tablePrefix = $db->conf['prefix']; + $this->tableSuffix = $db->conf['suffix']; + + } + + public function column( $columnName ) { + return new Column( $this->db,$this->dbmsType, $this, $columnName ); + } + + + /** + * Erzeugt eine neue Tabelle. + * Die neue Tabelle enthält bereits eine Spalte "id" (da eine leere Tabelle i.d.R. nicht zulässig ist). + */ + public function add() + { + $tableName = $this->getSqlName(); + + $table_opts = $this->dbmsType == OR_DB_TYPE_MYSQL ? ' ENGINE=InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci' : ''; + + $ddl = $this->db->sql('CREATE TABLE ' . $tableName . '(id INTEGER)' . $table_opts . ';'); + // The syntax 'TYPE = InnoDB' was deprecated in MySQL 5.0 and was removed in MySQL 5.1 and later versions. + + $ddl->query(); + + return $this; + } + + + public function addPrimaryKey($columnNames) + { + $table = $this->getSqlName(); + + if (!is_array($columnNames)) + $columnNames = explode(',', $columnNames); + + $ddl = $this->db->sql('ALTER TABLE ' . $table . ' ADD PRIMARY KEY (' . implode(',', $columnNames) . ');'); + $ddl->query(); + + } + + + + # Creating a unique key + # param 1: name of index column. Seperate multiple columns with ',' + public function addIndex( $columnNames, $unique = false) + { + if (!is_array($columnNames)) + $columnNames = [$columnNames]; + + $indexName = $this->tablePrefix . self::INDEX_PREFIX . '_' . $this->name . '_' . implode('_', $columnNames) . $this->tableSuffix; + +// if [ "$type" == "oracle" ]; then +// cnt=$(($cnt+1)) +// echo "CREATE UNIQUE INDEX ${prefix}uidx_${cnt}" >> $outfile +// else + + $ddl = $this->db->sql('CREATE ' . ($unique ? 'UNIQUE ' : '') . 'INDEX ' . $indexName . ' ON ' . $this->getSqlName() . ' (' . implode(',', $columnNames) . ');'); + $ddl->query(); + + } + + + /** + * Creating a unique key. + * param 1: name of index column. Seperate multiple columns with ',' + * + */ + public function addUniqueIndex( $columnNames) + { + $this->addIndex( $columnNames, true); + } + + + # Creating a foreign key + # param 1: column name + # param 2: target table name + # param 3: target column name + public function addConstraint($columnName, $targetTableName, $targetColumnName) + { + $targetTable = new Table($this->db,$this->dbmsType,$targetTableName); + $targetTablename = $targetTable->getSqlName(); + + $constraintName = $this->tablePrefix . self::CONSTRAINT_PREFIX . '_' . $this->name . $this->tableSuffix . '_' . $columnName; + + // Oracle doesn't support "ON DELETE RESTRICT"-Statements, but its the default. + + $ddl = $this->db->sql('ALTER TABLE ' . $this->getSqlName() . ' ADD CONSTRAINT ' . $constraintName . ' FOREIGN KEY (' . $columnName . ') REFERENCES ' . $targetTablename . ' (' . $targetColumnName . ') ON DELETE RESTRICT ON UPDATE RESTRICT;'); + $ddl->query(); + } + + + public function drop() + { + $table = $this->getSqlName(); + + $ddl = $this->db->sql('DROP TABLE ' . $table . ';'); + $ddl->query(); + } + + function dropIndex($indexName, $unique = false) + { + $ddl = $this->db->sql('DROP' . ($unique ? ' UNIQUE' : '') . ' INDEX ' . $indexName . ';'); + $ddl->query(); + } + + public function dropUniqueIndex($indexName) + { + $this->dropIndex($indexName, true); + } + + public function dropPrimaryKey( $columnNames) + { + $table = $this->getSqlName(); + + if (!is_array($columnNames)) + $columnNames = explode(',', $columnNames); + + $ddl = $this->db->sql('ALTER TABLE ' . $table . ' DROP PRIMARY KEY(' . implode(',', $columnNames) . ')'); + $ddl->query(); + } + + + public function dropConstraint($constraintName) + { + $ddl = $this->db->sql('DROP CONSTRAINT ' . $constraintName . ';'); + $ddl->query(); + } + +}+ \ No newline at end of file diff --git a/modules/database/require.php b/modules/database/require.php @@ -1,2 +0,0 @@ -<?php -