[prev in list] [next in list] [prev in thread] [next in thread]
List: gallery-devel
Subject: [Gallery-devel] Fwd: G3 over PgSQL : it works !
From: Romain LE DISEZ <romain.g3 () ledisez ! net>
Date: 2009-05-08 6:55:28
Message-ID: 495D185A-73F7-4A86-9DDB-D2394A84E263 () ledisez ! net
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Début du message réexpédié :
> De : Andy Staudacher <andy.st@gmail.com>
> Date : 7 mai 2009 19:33:42 HAEC
> À : Romain LE DISEZ <romain.g3@ledisez.net>
> Objet : Rép : [Gallery-devel] G3 over PgSQL : it works !
>
> @backticks:
> G3 is using some reserved words as table column names. E.g. "key" \
> (http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html ).
> I think we should have avoided this, but we're using them.
> As far as I know, you can't omit the backticks for column names if
> they're reserved names.
>
> I haven't looked at the rest yet.
>
> Generally, I envision a future for G3 with a distributed source code
> management model (like GIT-hub), where we have the official G3
> source provided by the core team, and then popular G3 flavors
> managed and maintained by enthusiasts, e.g. one for G3 on a 100% MS
> stack, one for postgresql, etc.
>
> But...if patches don't make the G3 code more complex (e.g. no
> additional abstractions, additional checks for different platforms /
> DBMS, etc), then the patches should usually go back from the flavor
> X tree to the official G3 source code tree.
>
> - Andy
>
> On Thu, May 7, 2009 at 2:36 AM, Romain LE DISEZ
> <romain.g3@ledisez.net> wrote:
> (All my post is based on G3 Alpha 4, sorry for my approximative
> english)
>
> Hello developers,
>
> with just few modifications to G3, I got it to work with PgSQL. These
> modifications are totally compatible with MySQL and (for most of them)
> don't add complexity.
>
> Here are my suggestions.
>
> 1/ Stop using the back quote : `
> It's a MySQL only notation. Don't use any quoting for tables names or
> fields names, you don't need it because all names are lower case.
> Providing a patch for this isn't usefull so I provide a command :
>
> # (you must be IN the gallery3 directory)
> cd /path/to/gallery3
> find . -type f -name '*.php' \
> ! -wholename './kohana/*' \
> ! -wholename './core/helpers/movie.php' \
> -exec sed -i "s,\`,,g" {} \;
>
> I read the diff after the command (929 lines), I hadn't see anything
> bad. I hope there is no malicious bugs created by it (except that now
> the installer must check that there is no space in the table_prefix).
>
> 2/ Executing the command of the first point revealed a bug : in table
> items there is fields named left and right. left and right are
> reserved
> words in SQL, so their names must be changed. The patch
> 01-sql_reserved_words.patch name them lft and rgt (as in the
> documentation you give about MPTT [1]).
>
> patch -p1 -i /path/to/01-sql_reserved_words.patch
>
> 3/ SQL is case insensitive. There is some code that relies on the case
> sensitivity of MySQL. The patch 02-sql_case_insensitive.patch fix
> that.
>
> patch -p1 -i /path/to/02-sql_case_insensitive.patch
>
> 4/ In PgSQL there is schemas (see [2]). Kohana lists tables in the
> form
> <schema_name>.<table_name>. It don't work with the code where G3 add
> tables prefix. Patch 03-remove_pg_schemaname.patch fix it.
>
> patch -p1 -i /path/to/03-remove_pg_schemaname.patch
>
> 5/ There is a DELETE that is not SQL compliant. You must not use
> JOIN in
> DELETE query but USING. But I don't see the reason of this request
> so I
> just removed it : items.id CAN'T be null so "WHERE items.id IS NULL"
> will never return rows... Can you explain me that ? Patch
> 04-sql_useless_delete.patch delete the query :
>
> patch -p1 -i /path/to/04-sql_useless_delete.patch
>
> 6/ When using statistics functions of SQL (count, sum, ...), the query
> must contains a GROUP BY. Patch 07-sql_count_groupby.patch fix it.
>
> patch -p1 -i /path/to/07-sql_count_groupby.patch
>
> 7/ For MySQL, comparing an integer with a string is not a problem. But
> PgSQL refuse to do it. In the REST controller, when $function='index'
> and $method!='get', there is a bug. Patch 08-rest_check_id.patch fix
> it.
>
> patch -p1 -i /path/to/08-rest_check_id.patch
>
> Patches 98-ticket_1239.patch and 99-pgsql_bugfixes.patch fix bugs in
> Kohana (I will send the patches upstream soon).
>
> patch -p1 -i /path/to/98-ticket_1239.patch
> patch -p1 -i /path/to/99-pgsql_bugfixes.patch
>
> I hadn't do extensive tests but it seems to work as with MySQL.
>
> Even if it's not your priority, interoperability is the best way to
> find
> bugs. I don't ask you to had PgSQL to the list of supported plateform
> but just to help me (us ?) to do it.
>
> What is your opinion ?
>
> [1] http://www.sitepoint.com/article/hierarchical-data-database/3/
> [2] http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html
>
> ------------
>
> Second part, for those who want to try :
>
> 1/ Extract a fresh copy of g3 alpha 4
> 2/ Apply the patches
> 3/ Install it over MySQL
> 4/ Convert your MySQL DB to PgSQL. I know it's totally quick & dirty,
> but it do the job for now. In a bash :
> (replace <tables_prefix>, <my_dbname>, <pg_username> and <pg_dbname>)
>
> prefix="<tables_prefix>"
>
> mysqldump --compatible=postgresql --skip-opt <my_dbname> >
> g3_mysql.sql
> cp -f g3_mysql.sql g3_pgsql.sql
> sed -i "s, int([0-9]\+) , integer ,g" g3_pgsql.sql
> sed -i "s, unsigned , ,g" g3_pgsql.sql
> sed -i "s, tinyint([0-9]\+) , integer ,g" g3_pgsql.sql
> sed -i "s,longtext,text,g" g3_pgsql.sql
>
> sed -i "s,UNIQUE KEY \"[^\"]\+\" ,UNIQUE,g" g3_pgsql.sql
> sed -i "s,KEY \"[^\"]\+\" ([^)]\+),CHECK ( 1=1 ),g" g3_pgsql.sql
> sed -i "s,FULLTEXT ,,g" g3_pgsql.sql
>
> echo "" >> g3_pgsql.sql
> for table in access_caches access_intents comments graphics_rules
> groups incoming_translations items items_tags logs messages modules
> outgoing_translations permissions search_records tags tasks themes
> users vars; do
> echo "CREATE SEQUENCE ${prefix}${table}_id_seq;" >>
> g3_pgsql.sql
> echo "ALTER TABLE ${prefix}${table} ALTER COLUMN id TYPE
> integer;" >> g3_pgsql.sql
> echo "ALTER TABLE ${prefix}${table} ALTER COLUMN id SET
> DEFAULT nextval('${prefix}${table}_id_seq');" >> g3_pgsql.sql
> echo "SELECT pg_catalog.setval('${prefix}${table}_id_seq',
> 20, true);" >> g3_pgsql.sql
> done
>
> echo "CREATE FUNCTION get_lock(text,integer) RETURNS integer AS
> 'SELECT 1;' LANGUAGE 'SQL';" >> g3_pgsql.sql
> echo "CREATE FUNCTION release_lock(text) RETURNS integer AS
> 'SELECT 1;' LANGUAGE 'SQL';" >> g3_pgsql.sql
> echo "CREATE FUNCTION unix_timestamp() RETURNS integer AS 'SELECT
> ROUND(EXTRACT( EPOCH FROM ABSTIME(NOW()) ))::int4 AS result;'
> LANGUAGE 'SQL';" >> g3_pgsql.sql
> echo "CREATE FUNCTION unix_timestamp(timestamp with time zone)
> RETURNS integer AS 'SELECT ROUND(EXTRACT( EPOCH FROM ABSTIME(\
> $1) ))::int4 AS result;' LANGUAGE 'SQL';" >> g3_pgsql.sql
>
> 5/ Import the g3_pgsql.sql into your PgSQL database
>
> psql -U <pg_username> -f /path/to/g3_pgsql.sql <pg_dbname>
>
> 6/ Update configuration file : /path/to/gallery3/var/database.php :
> - type = pgsql
> - database = <pg_dbname>
> - user = <pg_username>
> - pass = <pg_password>
>
> 7/ Enjoy !
>
> So, what need to be done now :
> - an SQL script for PgSQL with a real implementation of
> get_lock/release_lock,
> - search do not work,
>
>
> ------------------------------------------------------------------------------
> The NEW KODAK i700 Series Scanners deliver under ANY circumstances!
> Your
> production scanning environment may not be a perfect world - but
> thanks to
> Kodak, there's a perfect scanner to get the job done! With the NEW
> KODAK i700
> Series Scanner you'll get full speed at 300 dpi even with all image
> processing features enabled. http://p.sf.net/sfu/kodak-com
> __[ g a l l e r y - d e v e l ]_________________________
>
> [ list info/archive --> http://gallery.sf.net/lists.php ]
> [ gallery info/FAQ/download --> http://gallery.sf.net ]
>
[Attachment #5 (text/html)]
<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; \
-webkit-line-break: after-white-space; "><br><div><br><div>Début du message réexpédié \
:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div><div \
style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; \
"><font face="Helvetica" size="3" color="#000000" style="font: 12.0px Helvetica; \
color: #000000"><b>De : </b></font><font face="Helvetica" size="3" style="font: \
12.0px Helvetica">Andy Staudacher <<a \
href="mailto:andy.st@gmail.com">andy.st@gmail.com</a>></font></div><div \
style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; \
"><font face="Helvetica" size="3" color="#000000" style="font: 12.0px Helvetica; \
color: #000000"><b>Date : </b></font><font face="Helvetica" size="3" style="font: \
12.0px Helvetica">7 mai 2009 19:33:42 HAEC</font></div><div style="margin-top: 0px; \
margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><font face="Helvetica" \
size="3" color="#000000" style="font: 12.0px Helvetica; color: #000000"><b>À : \
</b></font><font face="Helvetica" size="3" style="font: 12.0px Helvetica">Romain LE \
DISEZ <<a href="mailto:romain.g3@ledisez.net">romain.g3@ledisez.net</a>></font></div><div \
style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; \
"><font face="Helvetica" size="3" color="#000000" style="font: 12.0px Helvetica; \
color: #000000"><b>Objet : </b></font><font face="Helvetica" size="3" style="font: \
12.0px Helvetica"><b>Rép : [Gallery-devel] G3 over PgSQL : it works \
!</b></font></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; \
margin-left: 0px; min-height: 14px; "><br></div> </div>@backticks:<div>G3 is using \
some reserved words as table column names. E.g. "key" (<a \
href="http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html">http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html</a>).<div> \
I think we should have avoided this, but we're using them.</div><div>As far as I \
know, you can't omit the backticks for column names if they're reserved \
names.<br><br></div><div>I haven't looked at the rest yet.</div> \
<div><br></div><div>Generally, I envision a future for G3 with a distributed source \
code management model (like GIT-hub), where we have the official G3 source provided \
by the core team, and then popular G3 flavors managed and maintained by enthusiasts, \
e.g. one for G3 on a 100% MS stack, one for postgresql, etc.</div> \
<div><br></div><div>But...if patches don't make the G3 code more complex (e.g. no \
additional abstractions, additional checks for different platforms / DBMS, etc), then \
the patches should usually go back from the flavor X tree to the official G3 source \
code tree.</div> <div><br></div><div> - Andy</div><div><br><div \
class="gmail_quote">On Thu, May 7, 2009 at 2:36 AM, Romain LE DISEZ <span \
dir="ltr"><<a href="mailto:romain.g3@ledisez.net">romain.g3@ledisez.net</a>></span> \
wrote:<br> <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex;">(All my post is based on G3 Alpha 4, sorry for my \
approximative english)<br> <br> Hello developers,<br> <br> with just few \
modifications to G3, I got it to work with PgSQL. These<br> modifications are totally \
compatible with MySQL and (for most of them)<br> don't add complexity.<br> <br> Here \
are my suggestions.<br> <br> 1/ Stop using the back quote : `<br> It's a MySQL only \
notation. Don't use any quoting for tables names or<br> fields names, you don't need \
it because all names are lower case.<br> Providing a patch for this isn't usefull so \
I provide a command :<br> <br> # (you must be IN the gallery3 \
directory)<br> cd /path/to/gallery3<br> find . -type f \
-name '*.php' \<br> ! -wholename './kohana/*' \<br> \
! -wholename './core/helpers/movie.php' \<br> \
-exec sed -i "s,\`,,g" {} \;<br> <br> I read the diff after the command \
(929 lines), I hadn't see anything<br> bad. I hope there is no malicious bugs created \
by it (except that now<br> the installer must check that there is no space in the \
table_prefix).<br> <br> 2/ Executing the command of the first point revealed a bug : \
in table<br> items there is fields named left and right. left and right are \
reserved<br> words in SQL, so their names must be changed. The patch<br> \
01-sql_reserved_words.patch name them lft and rgt (as in the<br> documentation you \
give about MPTT [1]).<br> <br> patch -p1 -i \
/path/to/01-sql_reserved_words.patch<br> <br> 3/ SQL is case insensitive. There is \
some code that relies on the case<br> sensitivity of MySQL. The patch \
02-sql_case_insensitive.patch fix that.<br> <br> patch -p1 -i \
/path/to/02-sql_case_insensitive.patch<br> <br> 4/ In PgSQL there is schemas (see \
[2]). Kohana lists tables in the form<br> <schema_name>.<table_name>. It \
don't work with the code where G3 add<br> tables prefix. Patch \
03-remove_pg_schemaname.patch fix it.<br> <br> patch -p1 -i \
/path/to/03-remove_pg_schemaname.patch<br> <br> 5/ There is a DELETE that is not SQL \
compliant. You must not use JOIN in<br> DELETE query but USING. But I don't see the \
reason of this request so I<br> just removed it : <a href="http://items.id" \
target="_blank">items.id</a> CAN'T be null so "WHERE <a href="http://items.id" \
target="_blank">items.id</a> IS NULL"<br> will never return rows... Can you explain \
me that ? Patch<br> 04-sql_useless_delete.patch delete the query :<br> <br> \
patch -p1 -i /path/to/04-sql_useless_delete.patch<br> <br> 6/ When using \
statistics functions of SQL (count, sum, ...), the query<br> must contains a GROUP \
BY. Patch 07-sql_count_groupby.patch fix it.<br> <br> patch -p1 -i \
/path/to/07-sql_count_groupby.patch<br> <br> 7/ For MySQL, comparing an integer with \
a string is not a problem. But<br> PgSQL refuse to do it. In the REST controller, \
when $function='index'<br> and $method!='get', there is a bug. Patch \
08-rest_check_id.patch fix it.<br> <br> patch -p1 -i \
/path/to/08-rest_check_id.patch<br> <br> Patches 98-ticket_1239.patch and \
99-pgsql_bugfixes.patch fix bugs in<br> Kohana (I will send the patches upstream \
soon).<br> <br> patch -p1 -i /path/to/98-ticket_1239.patch<br> \
patch -p1 -i /path/to/99-pgsql_bugfixes.patch<br> <br> I hadn't do extensive \
tests but it seems to work as with MySQL.<br> <br> Even if it's not your priority, \
interoperability is the best way to find<br> bugs. I don't ask you to had PgSQL to \
the list of supported plateform<br> but just to help me (us ?) to do it.<br> <br> \
What is your opinion ?<br> <br> [1] <a \
href="http://www.sitepoint.com/article/hierarchical-data-database/3/" \
target="_blank">http://www.sitepoint.com/article/hierarchical-data-database/3/</a><br> \
[2] <a href="http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html" \
target="_blank">http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html</a><br> \
<br> ------------<br> <br> Second part, for those who want to try :<br> <br> 1/ \
Extract a fresh copy of g3 alpha 4<br> 2/ Apply the patches<br> 3/ Install it over \
MySQL<br> 4/ Convert your MySQL DB to PgSQL. I know it's totally quick & \
dirty,<br> but it do the job for now. In a bash :<br> (replace <tables_prefix>, \
<my_dbname>, <pg_username> and <pg_dbname>)<br> <br> \
prefix="<tables_prefix>"<br> <br> mysqldump \
--compatible=postgresql --skip-opt <my_dbname> > g3_mysql.sql<br> \
cp -f g3_mysql.sql g3_pgsql.sql<br> sed -i "s, int([0-9]\+) , \
integer ,g" g3_pgsql.sql<br> sed -i "s, unsigned , ,g" g3_pgsql.sql<br> \
sed -i "s, tinyint([0-9]\+) , integer ,g" g3_pgsql.sql<br> \
sed -i "s,longtext,text,g" g3_pgsql.sql<br> <br> sed -i "s,UNIQUE \
KEY \"[^\"]\+\" ,UNIQUE,g" g3_pgsql.sql<br> sed -i "s,KEY \"[^\"]\+\" \
([^)]\+),CHECK ( 1=1 ),g" g3_pgsql.sql<br> sed -i "s,FULLTEXT ,,g" \
g3_pgsql.sql<br> <br> echo "" >> g3_pgsql.sql<br> for \
table in access_caches access_intents comments graphics_rules groups \
incoming_translations items items_tags logs messages modules outgoing_translations \
permissions search_records tags tasks themes users vars; do<br> \
echo "CREATE SEQUENCE ${prefix}${table}_id_seq;" >> g3_pgsql.sql<br> \
echo "ALTER TABLE ${prefix}${table} ALTER COLUMN id TYPE \
integer;" >> g3_pgsql.sql<br> echo "ALTER TABLE \
${prefix}${table} ALTER COLUMN id SET DEFAULT nextval('${prefix}${table}_id_seq');" \
>> g3_pgsql.sql<br> echo "SELECT \
pg_catalog.setval('${prefix}${table}_id_seq', 20, true);" >> g3_pgsql.sql<br> \
done<br> <br> echo "CREATE FUNCTION get_lock(text,integer) \
RETURNS integer AS 'SELECT 1;' LANGUAGE 'SQL';" >> g3_pgsql.sql<br> \
echo "CREATE FUNCTION release_lock(text) RETURNS integer AS \
'SELECT 1;' LANGUAGE 'SQL';" >> g3_pgsql.sql<br> echo "CREATE \
FUNCTION unix_timestamp() RETURNS integer AS 'SELECT ROUND(EXTRACT( EPOCH FROM \
ABSTIME(NOW()) ))::int4 AS result;' LANGUAGE 'SQL';" >> g3_pgsql.sql<br> \
echo "CREATE FUNCTION unix_timestamp(timestamp with time zone) RETURNS integer \
AS 'SELECT ROUND(EXTRACT( EPOCH FROM ABSTIME(\$1) ))::int4 AS result;' LANGUAGE \
'SQL';" >> g3_pgsql.sql<br> <br> 5/ Import the g3_pgsql.sql into your PgSQL \
database<br> <br> psql -U <pg_username> -f /path/to/g3_pgsql.sql \
<pg_dbname><br> <br> 6/ Update configuration file : \
/path/to/gallery3/var/database.php :<br> - type = pgsql<br> - database = \
<pg_dbname><br> - user = <pg_username><br> - pass = \
<pg_password><br> <br> 7/ Enjoy !<br> <br> So, what need to be done now :<br> - \
an SQL script for PgSQL with a real implementation of<br> \
get_lock/release_lock,<br> - search do not work,<br> <br> \
<br>------------------------------------------------------------------------------<br> \
The NEW KODAK i700 Series Scanners deliver under ANY circumstances! Your<br> \
production scanning environment may not be a perfect world - but thanks to<br> Kodak, \
there's a perfect scanner to get the job done! With the NEW KODAK i700<br> Series \
Scanner you'll get full speed at 300 dpi even with all image<br> processing features \
enabled. <a href="http://p.sf.net/sfu/kodak-com" \
target="_blank">http://p.sf.net/sfu/kodak-com</a><br>__[ g a l l e r y - d e v e l \
]_________________________<br> <br> [ list info/archive --> <a \
href="http://gallery.sf.net/lists.php" \
target="_blank">http://gallery.sf.net/lists.php</a> ]<br> [ gallery info/FAQ/download \
--> <a href="http://gallery.sf.net" target="_blank">http://gallery.sf.net</a> \
]<br></blockquote></div><br></div></div></blockquote></div><br></body></html>
------------------------------------------------------------------------------
The NEW KODAK i700 Series Scanners deliver under ANY circumstances! Your
production scanning environment may not be a perfect world - but thanks to
Kodak, there's a perfect scanner to get the job done! With the NEW KODAK i700
Series Scanner you'll get full speed at 300 dpi even with all image
processing features enabled. http://p.sf.net/sfu/kodak-com
__[ g a l l e r y - d e v e l ]_________________________
[ list info/archive --> http://gallery.sf.net/lists.php ]
[ gallery info/FAQ/download --> http://gallery.sf.net ]
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic