File modules/cms/update/version/DBVersion000001.class.php

Last commit: Sun Oct 1 02:58:08 2023 +0200	Jan Dankert	Refactoring: No it is possible to use 8 byte integers in the database


     1 <?php
     2 
     3 namespace cms\update\version;
     4 
     5 use database\Database;
     6 use database\DbVersion;
     7 use database\Column;
     8 
     9 
    10 /**
    11  * Baseline database structure.
    12  * 
    13  * @author dankert
    14  *
    15  */
    16 class DBVersion000001 extends DbVersion
    17 {
    18 	public function update()
    19 	{
    20 		$table = $this->table('project')->add();
    21 
    22 		$table->column('name'               )->type(Column::TYPE_VARCHAR)->size( 128)->add();
    23 		$table->column('target_dir'         )->type(Column::TYPE_VARCHAR)->add();
    24 		$table->column('ftp_url'            )->type(Column::TYPE_VARCHAR)->add();
    25 		$table->column('ftp_passive'        )->type(Column::TYPE_INT    )->size(Column::SIZE_INT_BOOL )->add();
    26 		$table->column('cmd_after_publish'  )->type(Column::TYPE_VARCHAR)->add();
    27 		$table->column('content_negotiation')->type(Column::TYPE_INT    )->size( Column::SIZE_INT_BOOL )->defaultValue(   0)->add();
    28 		$table->column('cut_index'          )->type(Column::TYPE_INT    )->size( Column::SIZE_INT_BOOL )->defaultValue(   0)->add();
    29 		
    30 		$table->addPrimaryKey();
    31 		$table->addIndex('name');
    32 		
    33 
    34 		$table = $this->table('user')->add();
    35 		$table->column('name')->type(Column::TYPE_VARCHAR)->size(128)->add();
    36 		$table->column('password')->type(Column::TYPE_VARCHAR)->size(50)->add();
    37 		$table->column('ldap_dn')->type(Column::TYPE_VARCHAR)->add();
    38 		$table->column('fullname')->type(Column::TYPE_VARCHAR)->size(128)->add();
    39 		$table->column('tel')->type(Column::TYPE_VARCHAR)->size(128)->add();
    40 		$table->column('mail')->type(Column::TYPE_VARCHAR)->add();
    41 		$table->column('descr')->type(Column::TYPE_VARCHAR)->add();
    42 		$table->column('style')->type(Column::TYPE_VARCHAR)->size(64)->add();
    43 		$table->column('is_admin')->type(Column::TYPE_INT)->size( Column::SIZE_INT_BOOL )->defaultValue(0)->add();
    44 		$table->addPrimaryKey();
    45 		$table->addUniqueIndex('name');
    46 		
    47 		$table = $this->table('group')->add();
    48 		$table->column('name')->type(Column::TYPE_VARCHAR)->size(100)->add();
    49 		$table->addPrimaryKey();
    50 		$table->addUniqueIndex('name');
    51 		
    52 		$table = $this->table('object')->add();
    53 		$table->column('parentid')->type(Column::TYPE_INT)->nullable()->add();
    54 		$table->column('projectid')->type(Column::TYPE_INT)->defaultValue(0)->add();
    55 		$table->column('filename')->type(Column::TYPE_VARCHAR)->size(150)->add();
    56 		$table->column('orderid')->type(Column::TYPE_INT)->add();
    57 		$table->column('create_date')->type(Column::TYPE_INT)->add();
    58 		$table->column('create_userid')->type(Column::TYPE_INT)->nullable()->add();
    59 		$table->column('lastchange_date')->type(Column::TYPE_INT)->add();
    60 		$table->column('lastchange_userid')->type(Column::TYPE_INT)->nullable()->add();
    61 		$table->column('is_folder')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->add();
    62 		$table->column('is_file')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->add();
    63 		$table->column('is_page')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->add();
    64 		$table->column('is_link')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->add();
    65 		$table->addPrimaryKey();
    66 		$table->addConstraint('projectid', 'project');
    67 		$table->addConstraint('lastchange_userid', 'user');
    68 		$table->addConstraint('create_userid', 'user');
    69 		
    70 		$table->addIndex('parentid');
    71 		$table->addIndex('projectid');
    72 		$table->addIndex('is_folder');
    73 		$table->addIndex('is_file');
    74 		$table->addIndex('is_page');
    75 		$table->addIndex('is_link');
    76 		$table->addIndex('orderid');
    77 		$table->addIndex('create_userid');
    78 		$table->addIndex('lastchange_userid');
    79 		$table->addUniqueIndex( ['parentid','filename'] );
    80 		
    81 		
    82 		
    83 		
    84 		$table = $this->table('template')->add();
    85 		$table->column('projectid')->type(Column::TYPE_INT)->add();
    86 		$table->column('name')->type(Column::TYPE_VARCHAR)->size(50)->add();
    87 		$table->addPrimaryKey();
    88 		$table->addConstraint('projectid', 'project');
    89 		
    90 		$table->addIndex('projectid');
    91 		$table->addIndex('name');
    92 		$table->addUniqueIndex(['projectid','name']);
    93 		
    94 		
    95 		
    96 		$table = $this->table('language')->add();
    97 		$table->column('projectid')->type(Column::TYPE_INT)->add();
    98 		$table->column('isocode')->type(Column::TYPE_VARCHAR)->size(10)->add();
    99 		$table->column('name')->type(Column::TYPE_VARCHAR)->size(50)->add();
   100 		$table->column('is_default')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   101 		$table->addPrimaryKey();
   102 		$table->addConstraint('projectid', 'project');
   103 		$table->addUniqueIndex(['projectid','isocode']);
   104 		
   105 		
   106 		
   107 		
   108 		$table = $this->table('page')->add();
   109 		$table->column('objectid')->type(Column::TYPE_INT)->add();
   110 		$table->column('templateid')->type(Column::TYPE_INT)->add();
   111 		$table->addPrimaryKey();
   112 		$table->addConstraint('templateid', 'template');
   113 		$table->addConstraint('objectid', 'object');
   114 		 
   115 		$table->addUniqueIndex('objectid');
   116 		$table->addIndex('templateid');
   117 		
   118 		
   119 		
   120 		
   121 		$table = $this->table('projectmodel')->add();
   122 		$table->column('projectid')->type(Column::TYPE_INT)->add();
   123 		$table->column('name')->type(Column::TYPE_VARCHAR)->size(50)->add();
   124 		$table->column('extension')->type(Column::TYPE_VARCHAR)->size(10)->nullable()->add();
   125 		$table->column('is_default')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   126 		$table->addPrimaryKey();
   127 		$table->addConstraint('projectid', 'project');
   128 		
   129 		$table->addIndex('projectid');
   130 		$table->addUniqueIndex(['projectid','name'] );
   131 		
   132 		
   133 		$table = $this->table('element')->add();
   134 		$table->column('templateid')->type(Column::TYPE_INT)->defaultValue(0)->add();
   135 		$table->column('name')->type(Column::TYPE_VARCHAR)->size(50)->add();
   136 		$table->column('descr')->type(Column::TYPE_VARCHAR)->add();
   137 		$table->column('type')->type(Column::TYPE_VARCHAR)->size(20)->add();
   138 		$table->column('subtype')->type(Column::TYPE_VARCHAR)->size(20)->nullable()->add();
   139 		$table->column('with_icon')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   140 		$table->column('dateformat')->type(Column::TYPE_VARCHAR)->size(100)->nullable()->add();
   141 		$table->column('wiki')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->nullable()->add();
   142 		$table->column('html')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->nullable()->add();
   143 		$table->column('all_languages')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   144 		$table->column('writable')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   145 		$table->column('decimals')->type(Column::TYPE_INT)->nullable()->add();
   146 		$table->column('dec_point')->type(Column::TYPE_VARCHAR)->size(5)->nullable()->add();
   147 		$table->column('thousand_sep')->type(Column::TYPE_VARCHAR)->size(1)->nullable()->add();
   148 		$table->column('code')->type(Column::TYPE_TEXT)->nullable()->add();
   149 		$table->column('default_text')->type(Column::TYPE_TEXT)->nullable()->add();
   150 		$table->column('folderobjectid')->type(Column::TYPE_INT)->nullable()->add();
   151 		$table->column('default_objectid')->type(Column::TYPE_INT)->nullable()->add();
   152 		$table->addPrimaryKey();
   153 		$table->addConstraint('default_objectid', 'object');
   154 		$table->addConstraint('folderobjectid', 'object');
   155 		$table->addConstraint('templateid', 'template');
   156 		
   157 		$table->addIndex('templateid');
   158 		$table->addIndex('name');
   159 		$table->addUniqueIndex(['templateid','name']);
   160 		
   161 		
   162 		
   163 		
   164 		$table = $this->table('file')->add();
   165 		$table->column('objectid')->type(Column::TYPE_INT)->add();
   166 		$table->column('extension')->type(Column::TYPE_VARCHAR)->size(10)->add();
   167 		$table->column('size')->type(Column::TYPE_INT)->add();
   168 		$table->column('value')->type(Column::TYPE_BLOB)->add();
   169 		$table->addPrimaryKey();
   170 		$table->addConstraint('objectid', 'object');
   171 		
   172 		$table->addUniqueIndex('objectid');
   173 		
   174 		
   175 		
   176 		$table = $this->table('folder')->add();
   177 		$table->column('objectid')->type(Column::TYPE_INT)->add();
   178 		$table->addPrimaryKey();
   179 		$table->addConstraint('objectid', 'object');
   180 		
   181 		$table->addUniqueIndex('objectid');
   182 		
   183 		
   184 		
   185 		
   186 		
   187 		$table = $this->table('link')->add();
   188 		$table->column('objectid')->type(Column::TYPE_INT)->add();
   189 		$table->column('link_objectid')->type(Column::TYPE_INT)->nullable()->add();
   190 		$table->column('url')->type(Column::TYPE_VARCHAR)->nullable()->add();
   191 		$table->addPrimaryKey();
   192 		$table->addConstraint('objectid', 'object');
   193 		$table->addConstraint('link_objectid', 'object');
   194 		
   195 		$table->addUniqueIndex('objectid');
   196 		$table->addIndex('link_objectid');
   197 		
   198 		
   199 		
   200 		
   201 		
   202 		$table = $this->table('name')->add();
   203 		$table->column('objectid')->type(Column::TYPE_INT)->add();
   204 		$table->column('name')->type(Column::TYPE_VARCHAR)->add();
   205 		$table->column('descr')->type(Column::TYPE_VARCHAR)->add();
   206 		$table->column('languageid')->type(Column::TYPE_INT)->add();
   207 		$table->addPrimaryKey();
   208 		$table->addConstraint('objectid', 'object');
   209 		$table->addConstraint('languageid', 'language');
   210 		
   211 		$table->addIndex('objectid');
   212 		$table->addIndex('languageid');
   213 		$table->addUniqueIndex(['objectid','languageid']);
   214 		
   215 		
   216 		
   217 		
   218 		
   219 		$table = $this->table('templatemodel')->add();
   220 		$table->column('templateid')->type(Column::TYPE_INT)->add();
   221 		$table->column('projectmodelid')->type(Column::TYPE_INT)->add();
   222 		$table->column('extension')->type(Column::TYPE_VARCHAR)->size(10)->nullable()->add();
   223 		$table->column('text')->type(Column::TYPE_TEXT)->add();
   224 		$table->addPrimaryKey();
   225 		$table->addConstraint('templateid', 'template');
   226 		$table->addConstraint('projectmodelid', 'projectmodel');
   227 		
   228 		$table->addIndex('templateid');
   229 		$table->addUniqueIndex(['templateid','extension'     ]);
   230 		$table->addUniqueIndex(['templateid','projectmodelid']);
   231 		
   232 		
   233 		
   234 		
   235 		
   236 		$table = $this->table('usergroup')->add();
   237 		$table->column('userid')->type(Column::TYPE_INT)->add();
   238 		$table->column('groupid')->type(Column::TYPE_INT)->add();
   239 		$table->addPrimaryKey();
   240 		$table->addConstraint('groupid', 'group');
   241 		$table->addConstraint('userid', 'user');
   242 		
   243 		$table->addIndex('groupid');
   244 		$table->addIndex('userid');
   245 		$table->addUniqueIndex(['userid','groupid']);
   246 		
   247 		
   248 		
   249 		
   250 		$table = $this->table('value')->add();
   251 		$table->column('pageid')->type(Column::TYPE_INT)->add();
   252 		$table->column('languageid')->type(Column::TYPE_INT)->add();
   253 		$table->column('elementid')->type(Column::TYPE_INT)->add();
   254 		$table->column('linkobjectid')->type(Column::TYPE_INT)->nullable()->add();
   255 		$table->column('text')->type(Column::TYPE_TEXT)->nullable()->add();
   256 		$table->column('number')->type(Column::TYPE_INT)->nullable()->add();
   257 		$table->column('date')->type(Column::TYPE_INT)->nullable()->add();
   258 		$table->column('active')->type(Column::TYPE_INT)->add();
   259 		$table->column('lastchange_date')->type(Column::TYPE_INT)->add();
   260 		$table->column('lastchange_userid')->type(Column::TYPE_INT)->nullable()->add();
   261 		$table->column('publish')->type(Column::TYPE_INT)->add();
   262 		$table->addPrimaryKey();
   263 		$table->addConstraint('pageid', 'page');
   264 		$table->addConstraint('elementid', 'element');
   265 		$table->addConstraint('languageid', 'language');
   266 		$table->addConstraint('lastchange_userid', 'user');
   267 		$table->addConstraint('linkobjectid', 'object');
   268 		
   269 		$table->addIndex('pageid');
   270 		$table->addIndex('languageid');
   271 		$table->addIndex('elementid');
   272 		$table->addIndex('active');
   273 		$table->addIndex('lastchange_date');
   274 		$table->addIndex('publish');
   275 		
   276 		
   277 		
   278 		
   279 		
   280 		$table = $this->table('acl')->add();
   281 		$table->column('userid')->type(Column::TYPE_INT)->nullable()->add();
   282 		$table->column('groupid')->type(Column::TYPE_INT)->nullable()->add();
   283 		$table->column('objectid')->type(Column::TYPE_INT)->add();
   284 		$table->column('languageid')->type(Column::TYPE_INT)->nullable()->add();
   285 		$table->column('is_write')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   286 		$table->column('is_prop')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   287 		$table->column('is_create_folder')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   288 		$table->column('is_create_file')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   289 		$table->column('is_create_link')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   290 		$table->column('is_create_page')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   291 		$table->column('is_delete')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   292 		$table->column('is_release')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   293 		$table->column('is_publish')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   294 		$table->column('is_grant')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   295 		$table->column('is_transmit')->type(Column::TYPE_INT)->size(Column::SIZE_INT_BOOL)->defaultValue(0)->add();
   296 		$table->addPrimaryKey();
   297 		$table->addConstraint('groupid', 'group');
   298 		$table->addConstraint('userid', 'user');
   299 		$table->addConstraint('objectid', 'object');
   300 		$table->addConstraint('languageid', 'language');
   301 		
   302 		$table->addIndex('userid');
   303 		$table->addIndex('groupid');
   304 		$table->addIndex('languageid');
   305 		$table->addIndex('objectid');
   306 		$table->addIndex('is_transmit');
   307 
   308 		$this->afterUpdate( $this->getDb() );
   309 	}
   310 
   311 
   312 
   313     /**
   314      * Initialisieren der frisch aktualisierten Datenbank.
   315      *
   316      * @param Database $db
   317      */
   318     private function afterUpdate( Database $db )
   319     {
   320         // Benutzer zählen.
   321         $sql = $db->sql('SELECT COUNT(*) From {{user}}',$db->id);
   322         $countUsers = $sql->getOne();
   323 
   324         // Wenn noch kein Benutzer vorhanden, dann einen anlegen.
   325         if	( $countUsers == 0 )
   326         {
   327             // Hashing the admin password with MD5. In Version 6 the Algo will be set to 2 (=MD5).
   328             $sql = $db->sql("INSERT INTO {{user}} (id,name,password,ldap_dn,fullname,tel,mail,descr,style,is_admin) VALUES(1,'admin','21232f297a57a5a743894a0e4a801fc3','','Administrator','','','Account for administration tasks.','default',1)",$db->id);
   329             $sql->execute();
   330             $db->commit();
   331         }
   332     }
   333 
   334 
   335 }



Download modules/cms/update/version/DBVersion000001.class.php


History
Sun, 1 Oct 2023 02:58:08 +0200	Jan Dankert	Refactoring: No it is possible to use 8 byte integers in the database
Wed, 7 Jul 2021 22:29:06 +0200	Jan Dankert	Refactoring: Split values and content, new table "content".
Sun, 7 Mar 2021 00:10:20 +0100	Jan Dankert	Refactoring: Hopefully more performance while accessing the database resultsets.
Fri, 25 Sep 2020 01:00:58 +0200	Jan Dankert	Refactoring: More OO in the database updater :)
Sun, 23 Feb 2020 00:30:10 +0100	Jan Dankert	Refactoring: Namespacing for module 'database-update', now called 'cms\update'.