<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:595.3pt 841.9pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=FR link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>Hi all,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>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.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt;font-family:Arial;font-weight:bold'>It also lights up
some defects in vtiger database schema</span></font></b><font size=2
face=Arial><span lang=EN-GB style='font-size:10.0pt;font-family:Arial'>, that
could help to solve some issues in current versions.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>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).<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt;font-family:Arial;font-weight:bold'>Requirements :<o:p></o:p></span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>upgrade to last version of AXMLS package (1.0.2
include MySQL 5 support) in adodb directory<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>patch to adodb-datadict.inc.php to solve MySQL 5.0
bug with default value of TIMESTAMP fields.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt;font-family:Arial;font-weight:bold'>CONFIG :<o:p></o:p></span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>PHP 5.1.2<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>MySQL 5.0.18<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'> <o:p></o:p></span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt;font-family:Arial;font-weight:bold'>ABSTRACT<o:p></o:p></span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>DatabaseSchema :<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>ON DELETE CASCADE ->
ON DELETE NO ACTION (already documented)<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>change cvcolumnlist index
(primary key not unique)<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>change selectcolumn index
(primary key not unique)<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>change relcriteria index
(primary key not unique)<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>change faqcomments add
DEFTIMESTAMP to column createdtime (missing default value)<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>change ticketcomments add
DEFTIMESTAMP to column createdtime (missing default value)<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>Utils :<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>getDBInsertDateValue
change empty date ('--') -> '0000-00-00'<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>replace contact list
query joins according to SQL standards<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>CRMEntity, LoginHistory, PopulateComboValues,
PopulateReports:<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>replace default field
value from "''" to "DEFAULT"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>Security :<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>replace default field
value from "''" to "NULL" in Tab INSERT query<o:p></o:p></span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt;font-family:Arial;font-weight:bold'><o:p> </o:p></span></font></b></p>
<p class=MsoNormal><b><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt;font-family:Arial;font-weight:bold'>COMMENTS<o:p></o:p></span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt;font-family:Arial;font-weight:bold'>Database schema,
primary key not unique<o:p></o:p></span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>CREATE of cvcolumnlist :<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <table
name="cvcolumnlist"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <field
name="cvid" type="I" size="19"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <KEY/><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </field><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <field
name="columnindex" type="I" size="11"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>
<NOTNULL/><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </field><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <field
name="columnname" type="C" size="250"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>
<DEFAULT value=""/><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </field><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <index
name="columnindex"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <col>columnindex</col><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </index><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <opt>Type=InnoDB</opt><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <data></data><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </table><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>is changed to :<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <table
name="cvcolumnlist"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <field
name="cvid" type="I" size="19"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </field><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <field
name="columnindex" type="I" size="11"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>
<NOTNULL/><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </field><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <field
name="columnname" type="C" size="250"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>
<DEFAULT value=""/><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </field><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <index
name="cvcolumnlistid"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <UNIQUE/><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <col>cvid</col><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <col>columnindex</col><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </index><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <index
name="columnindex"><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <col>columnindex</col><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </index><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <opt>Type=InnoDB</opt><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> <data></data><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'> </table><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>cvid is not unique,
several columns for same customview<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><b><font size=2 face=Arial><span lang=EN-GB
style='font-size:10.0pt;font-family:Arial;font-weight:bold'>TIMESTAMP default
values<o:p></o:p></span></font></b></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>There is probably a bug
in MySQL 5.0.<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>CREATE TABLE
<table_name><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><column_name>
DATETIME NOT NULL<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>is supposed to default to
CURRENT_TIMESTAMP but it doesn't.<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><column_name>
DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>and<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><column_name>
DATETIME DEFAULT CURRENT_TIMESTAMP<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>are not accepted<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>That is the reason of the
patch in adodb-datadict.inc.php, it generates :<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><column_name>
DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>which is accepted.<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:35.4pt'><font size=2 face=Arial><span
lang=EN-GB style='font-size:10.0pt;font-family:Arial'>Note : I have probably
forgotten some <DEFTIMESTAMP/> clauses in XML schema.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'>Hope this will help in building a more opened
software.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-GB style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
</div>
</body>
</html>