[Vtigercrm-developers] Support of MySQL 5 in 4.1 version

[Fathi BOUDRA]

thanks michel,

i'll add your patch to ticket 43:
http://vtiger.fosslabs.com/cgi-bin/trac.cgi/ticket/43

cheers,

Fathi

Le Dimanche 5 Mars 2006 11:23, Michel JACQUEMES a écrit :
> Hi all,
>
>
>
> Pursuing my efforts on database compatibility, find joined somme patches
> allowing support of MySQL 5.0. Something remarqable to notice is that most
> of the changes have the same origin than those for mssql or postgres
> support.
>
>
>
> It also lights up some defects in vtiger database schema, that could help
> to solve some issues in current versions.
>
>
>
> These patches are made to keep compatibility with previous versions of
> MySQL. They have been tested with MySQL 4.1.16 (limited test according to
> the time I have).
>
>
>
> Requirements :
>
> upgrade to last version of AXMLS package (1.0.2 include MySQL 5 support) in
> adodb directory
>
> patch to adodb-datadict.inc.php to solve MySQL 5.0 bug with default value
> of TIMESTAMP fields.
>
>
>
> CONFIG :
>
> PHP 5.1.2
>
> MySQL 5.0.18
>
>
>
> ABSTRACT
>
>
>
> DatabaseSchema :
>
> ON DELETE CASCADE -> ON DELETE NO ACTION (already documented)
>
> change cvcolumnlist index (primary key not unique)
>
> change selectcolumn index (primary key not unique)
>
> change relcriteria index (primary key not unique)
>
> change faqcomments add DEFTIMESTAMP to column createdtime (missing default
> value)
>
> change ticketcomments add DEFTIMESTAMP to column createdtime (missing
> default value)
>
>
>
> Utils :
>
> getDBInsertDateValue change empty date ('--') -> '0000-00-00'
>
> replace contact list query joins according to SQL standards
>
>
>
> CRMEntity, LoginHistory, PopulateComboValues, PopulateReports:
>
> replace default field value from "''" to "DEFAULT"
>
>
>
> Security :
>
> replace default field value from "''" to "NULL" in Tab INSERT query
>
>
>
> COMMENTS
>
>
>
> Database schema, primary key not unique
>
>
>
> CREATE of cvcolumnlist :
>
>             <table name="cvcolumnlist">
>
>                         <field name="cvid" type="I" size="19">
>
>                                    <KEY/>
>
>                         </field>
>
>                         <field name="columnindex" type="I" size="11">
>
>                              <NOTNULL/>
>
>                         </field>
>
>                         <field name="columnname" type="C" size="250">
>
>                               <DEFAULT value=""/>
>
>                         </field>
>
>                         <index name="columnindex">
>
>                                    <col>columnindex</col>
>
>                         </index>
>
>                         <opt>Type=InnoDB</opt>
>
>                         <data></data>
>
>             </table>
>
>
>
> is changed to :
>
>             <table name="cvcolumnlist">
>
>                         <field name="cvid" type="I" size="19">
>
>                         </field>
>
>                         <field name="columnindex" type="I" size="11">
>
>                              <NOTNULL/>
>
>                         </field>
>
>                         <field name="columnname" type="C" size="250">
>
>                               <DEFAULT value=""/>
>
>                         </field>
>
>                         <index name="cvcolumnlistid">
>
>                                    <UNIQUE/>
>
>                                    <col>cvid</col>
>
>                                    <col>columnindex</col>
>
>                         </index>
>
>                         <index name="columnindex">
>
>                                    <col>columnindex</col>
>
>                         </index>
>
>                         <opt>Type=InnoDB</opt>
>
>                         <data></data>
>
>             </table>
>
> cvid is not unique, several columns for same customview
>
>
>
> TIMESTAMP default values
>
>
>
> There is probably a bug in MySQL 5.0.
>
>
>
> CREATE TABLE <table_name>
>
> <column_name> DATETIME NOT NULL
>
>
>
> is supposed to default to CURRENT_TIMESTAMP but it doesn't.
>
>
>
> <column_name> DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
>
> and
>
> <column_name> DATETIME DEFAULT CURRENT_TIMESTAMP
>
> are not accepted
>
>
>
> That is the reason of the patch in adodb-datadict.inc.php, it generates :
>
>
>
> <column_name> DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
>
>
>
> which is accepted.
>
> Note : I have probably forgotten some <DEFTIMESTAMP/> clauses in XML
> schema.
>
>
>
> Hope this will help in building a more opened software.