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

[Michel JACQUEMES]

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.

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20060305/d6524d45/attachment-0003.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Patches-4.2.4-MySQL5.zip
Type: application/x-zip-compressed
Size: 5188 bytes
Desc: not available
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20060305/d6524d45/attachment-0003.bin