Proposal of MySQL specific query translation

[84.le0n]

Hi all,
I'm developing PostgreSQL driver for joomla platform and, talking with
other database driver developer, we've found some MySQL specific
queries that we think it's better to rewrite using common way to do
the same query.
Two queries found inside joomla-cms code that it's not possible to
translate to other driver is "REPLACE INTO" and "INSERT IGNORE INTO":
can you try to rewrite these type of queries to somewhat more common?

Thank you!

[Mark Dexter]

Have you looked at the db17 branch? This is where the work for supporting MSSQL has been done. I would guess that they might have had this same issue and have addressed it there. Can you please check or contact Sudhi about this? Thanks. Mark

--
You received this message because you are subscribed to the Google Groups "Joomla! CMS Development" group.
To post to this group, send an email to joomla-...@googlegroups.com.
To unsubscribe from this group, send email to joomla-dev-cm...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/joomla-dev-cms?hl=en-GB.

[84.le0n]

2011/10/15 Mark Dexter <dexter...@gmail.com>:

> Have you looked at the db17 branch? This is where the work for supporting
> MSSQL has been done. I would guess that they might have had this same issue
> and have addressed it there. Can you please check or contact Sudhi about
> this? Thanks. Mark

Yes I did. I'm in contact with Sudhi since I started my job about PostgreSQL ;)

This is how that queries are handled in db17 branch :
- inside sample_data.sql there is "INSERT IGNORE INTO", sqlazure's
version and sqlsrv one have "INSERT INTO"
- "REPLACE INTO" is present in files (as joomla_update_16ga.sql) that
there isn't in sqlsrv and sqlazure folders

Inside installation/models/configuration.php a "REPLACE INTO" query is
changed with a SELECT and then, if there's a tuple do an UPDATE, else
do an INSERT ; the code is

$query = $db->getQuery(true);
$query->select('id');
$query->from('#__users');
$query->where('id = 42');
$db->setQuery($query);

if($db->loadResult())
{
$query = $db->getQuery(true);
$query->update('#__users');
....
$query->where('id = 42');
}
else
{
$query = $db->getQuery(true);
....
$query->insertInto('#__users', true);
....
}

These are the correct steps with PostgreSQL too, but I think it's
better to put a "replaceInto" command inside database class (not in
JDatabaseQuery class) so every 3pd that wish use "REPLACE INTO" query
does not think about that "SELECT- if UPDATE- else INSERT" command
block because database driver does.

Another commands that I wish to move inside database class is
JInstallationModelDatabase::createDatabase
because PostgreSQL need to create a role for an "user" (from
$option['username'] ) before database creation and set it as owner,
set the privileges, without specifying the CHARSET (PostgreSQL uses
UTF8 by default and doesn't have CHARSET option).

A question about JInstallationModelDatabase::deleteDatabase function
name misunderstanding : this function clears database's table but
doesn't drop the database itself, if I wish call "deleteDatabase" I
think it DROP completely a database, not only clear the content.
Why don't rename it as "dropDatabaseTables" and add a "dropDatabase"
that simply execute a "DROP DATABASE" (in PostgreSQL drop role too) ?

Thank you, and excuse me for this long email.

Eng. Gabriele Pongelli

[elin]

Gabriele,

I think you should post this also on the Platform list (I guess the new one, you can start it off) since hese are library changes.

I think it's a good point about the sample data, as much as is possible I would like to minimize the differences between joomla.sgl and sample_data.sql the same for the different databases.

I do have a topic I want to raise related to this. Drizzle disallows tinyint and mediumtext both of which we use (they just use integer and text) and mandates utf-8 which so DEFAULT CHARSET=utf8 is invalid for it.  We cannot use literally the same file (because of differences in null date, Drizzle uses null which is invalid for MySQL) but I wonder if we would be willing to make the files a bit closer and more generic by considering adopting this usage. Conversely, what distinct aspects of MySQL do people feel it is highly important to preserve? 

Right now I am focused just on joomla.sql.

Elin

[Nick Weavers]

I may have understood what you meant, but MySQL allows NULL for DATE
and DATETIME if you tell it to:

CREATE TABLE mydb.mytable(
mydate DATE DEFAULT NULL
)
ENGINE = MYISAM
CHARACTER SET utf8
COLLATE utf8_general_ci;

On Oct 16, 9:30 pm, elin <elin.war...@gmail.com> wrote:
> Gabriele,
>
> ...  

> We cannot
> use literally the same file (because of differences in null date, Drizzle
> uses null which is invalid for MySQL)

> ...
>
> Elin

[84.le0n]

@elin added new platform mailing list, Sudhi and Santiago.
I've converted an sql file but I don't remember how tinyint and
mediumtext are translated for PostgreSQL, I'll tell you later.
DEFAULT CHARSET is invalid for PostgreSQL too, it always uses utf8 .
Same problem for date: PostgreSQL null date is "1970-01-01 00:00:00" .
I don't know Drizzle and I think to leave db17 folders untouched so
there are a joomla.sql and a sample_data.sql per database (with
appropriate changes inside).

@all
This is the discussion starting point: integrate, or eventually
change, some cms query inside platform to run them in
database-independent mode.

Hoping we'll approach all of these issues here it is the list:

1) how many files do we have ? I think a joomla.sql and a
sample_data.sql for each driver, where each can solve data type
mismatch and values (tinyint, date value etc) as he/she prefer for
underlying database; the cons is having more file to maintain

2) create inside database driver a "SELECT- if UPDATE- else INSERT"
query block (see my last mail below) where it's not supported "REPLACE
INTO" query, naming it "replaceInto" so calling it will query "REPLACE
INTO" for MySQL or that block for the others while 3pd don't see the
difference.

3) Moving JInstallationModelDatabase::createDatabase inside database
driver: I need this because PostgreSQL needs to create a role for the
username that will connect and set it as OWNER. Another step I've
thought is about authentication and privileges, that I can set in
PostgreSQL only before database creation.

4) Add a dropDatabase function that execute "DROP DATABASE"

5) Rename "deleteDatabase" to "clearDatabase" because the database is
not deleted but only cleared.

6) In addition to the mail below think that an "ALTER" query element
or driver function could be useful.

My last mail on cms mailing list is following

[84.le0n]

excuse me for my double email, but I can't send the mail to platform
mailing list.

2011/10/17 84.le0n <84....@gmail.com>:

[elin]

Nick

https://github.com/joomla/joomla-cms/blob/master/installation/sql/mysql/joomla.sql#L103

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

http://stackoverflow.com/questions/1691117/how-to-store-null-values-in-datetime-fields-in-mysql

We use NOT  NULL  and that obviously will not work with null as the default :). 

This is something worth thinking about as we go to jdatabasequery and also maybe the query performance team is looking at that, i don't know.

Elin

[Nicholas K. Dionysopoulos]

Elin,

That's partially correct. There is some magic going on when you're using JTable's save() method. This method takes one parameter which defines if null values are to be updated. The default is that null values are ignored. You can tell it to store null values, but you have to be very (very!!) careful to always do a read before an update, otherwise you'll be overwriting useful data with null values. This explains why trying to set any column to NULL results in the column not being updated. Frankly, I don't think many people are aware of that as we're used to having all of our columns set to NOT NULL to avoid any nasty surprises when we're not all that careful.

Cheers,

-- 
Nicholas K. Dionysopoulos
Lead Developer, AkeebaBackup.com

Web: http://www.AkeebaBackup.com

Blog: http://www.dionysopoulos.me/blog

--

You received this message because you are subscribed to the Google Groups "Joomla! CMS Development" group.

To view this discussion on the web, visit https://groups.google.com/d/msg/joomla-dev-cms/-/YdZSox5lvCAJ.

[elin]

Right, I just want to make it clear that we can't just do a search and replace and switch that to NULL.

Elin

[84.le0n]

Forwarding my mail to platform mailing list for discussion.

- Nascondi testo citato -

[84.le0n]

@elin
this is how I've translated that variable type in PostgreSQL ddl.sql
file (working for my tests) :

** MySQL ** <--> ** PostgreSQL **
tinyint <--> smallint
mediumtext <--> text
varchar(255) <--> character varying(255)
int(10) <--> integer (it become "serial" type if is used for an
"id" table column)
int(11) <--> bigint
tinyint(3) <--> smallint
datetime <--> timestamp
text <--> text

@sudhi and others
I wish to do the latter 1. and 2. points ("if insert ignore or
replace.." and "Modify the code...") inside driver, so it'll be the
default behaviour, no 3pd needs to implement this difference because
it's done inside driver.

I haven't find any changes on your code for "createDatabase"; there
are only in "backupDatabase" and "deleteDatabase" and they are simply
query element translation of what were present (good in a multidb
environment).

I need to move createDatabase inside driver because I can't create the
database with current commands, I need to create an OWNER before and
associate it to the database; I've to run "CREATE DATABASE yyy OWNER
xxx" and not only "CREATE DATABASE yyy", this syntax problem is the
question.

I've a PostgreSQL server running on my laptop, where I'm doing my tests.

@ to everyone: any thought about that starting point list ?
favorable or against ?!?

Eng. Gabriele Pongelli

2011/10/17 sudhendra Seshachala <su...@hooduku.com>:
> I will try to explain at a high level, what we had to do to over come some
> of mysql nuances.
> 1. We did not touch joomla.sql and sample_data.sql for mysql.
> 2. We modified joomla.sql and sample_data.sql for SQLServer.
> In the code - wherever there was mysqlspecific syntax or non ANSI SQL, we
> did either of the below two.
> 1. If insert ignore or replace - we basically check if id exists and
> basically do insert or update.
> 2. Modify the code to leverage the DB framework -- generates sql based on
> the underlying database.
>
> In cases we had to do drop database or drop tables - we used the framework
> to generate the appropriate code.
> Creating and droping databases, we did add the code in the framework to
> generate code appropriate create database and drop database/table statement.
> If I remember correctly - I will revert back on this.
>
> In MySQL - we have table level locking. In SQLServer  - it is a "no-op". In
> oracle - there is some sort of row level locking. To accomplish this - moved
> the lock/unlock method to the db framework and made the lock/unlock a
> "no-op" in SQLServer.
> Do you have a postgress SQL instance running somewhere? I can start looking
> at the code as the time permits.
>
> thanks
> Sudhi

> --
> Thanks & Regards
> Sudhi
>
> Hooduku Inc
> 1.888.262.8389
> http://www.hooduku.com
> http://www.hoodukucloud.com
>
>
>
>
>

[84.le0n]

News from my to-do list:

>> 1) how many files do we have ? I think a joomla.sql and a
>> sample_data.sql for each driver, where each can solve data type
>> mismatch and values (tinyint, date value etc) as he/she prefer for
>> underlying database; the cons is having more file to maintain

I've created "joomla.sql" and "sample_data.sql" for PostgreSQL
database, not yet tested during an installation but it's converted
following the same rules I did for "ddl.sql" file, so I hope it will
work fine.
That two files will be added in my joomla-cms github repo, inside
"installation/sql/postgresql" folder, as searched by
JInstallationModelDatabase::installSampleData() function coming from
installation/models/database.php .

>> 2) create inside database driver a "SELECT- if UPDATE- else INSERT"
>> query block (see my last mail below) where it's not supported "REPLACE
>> INTO" query, naming it "replaceInto" so calling it will query "REPLACE
>> INTO" for MySQL or that block for the others while 3pd don't see the
>> difference.

Waiting your thought.

>> 3) Moving JInstallationModelDatabase::createDatabase inside database
>> driver: I need this because PostgreSQL needs to create a role for the
>> username that will connect and set it as OWNER. Another step I've
>> thought is about authentication and privileges, that I can set in
>> PostgreSQL only before database creation.

Like MySQL does, it's better that the database's user/role is created
BEFORE the database, so I don't need to execute more instruction other
than "CREATE DATABASE" in a PostgreSQL manner.

The syntax between databases remain different, so I prefer create a
JDatabase::getCreateDbQuery( $options, $utf )
that simply does

if ($utf) {
$query = 'CREATE DATABASE '.$db->nameQuote($name).' CHARACTER SET `utf8`';
}
else {
$query = 'CREATE DATABASE '.$db->nameQuote($name);
}

and returns $query, while
JDatabasePostgreSQL::getCreateDbQuery( $options, $utf )
will return
$query = 'CREATE DATABASE ' . $options['database'] . ' OWNER ' .
$options['user'] ;

There are some other configuration that I thought to add like set the
correct privileges only to $options['user'] , but to be really
effective it's necessary to modify pg_hba.conf, so I think this is the
best way to let multidb works.

>> 4) Add a dropDatabase function that execute "DROP DATABASE"

I think it's better that "DROP DATABASE" and "DROP ROLE" will be
executed manually by user not inside Joomla! platform (I don't know if
it could be useful for JCli or other applications, but for CMS it
doesn't ).

>> 5) Rename "deleteDatabase" to "clearDatabase" because the database is
>> not deleted but only cleared.

Waiting your thought.

>> 6) In addition to the mail below think that an "ALTER" query element
>> or driver function could be useful.

Waiting your thought.

I wish this is not a monologue thread, I'm waiting for your answers.

Eng. Gabriele Pongelli

2011/10/18 84.le0n <84....@gmail.com>:

[elin]

 Hi, 

I want to mention to everyone that the db17 branch on Joomlacode is looking quite good in mysqli at this point. I would be great if some people would download, install and test on  various databases.

Elin

[84.le0n]

While I'm waiting your thought I'm testing my postgresql version.
I'm solving the problem using the same solution of db17 (using to the
most recent platform and not that present in db17).
I can create the tables and populate them in a previously created
database, next test is trying to create a new one during installation
and doing a backup in same database.

Eng. Gabriele Pongelli

2011/10/21 84.le0n <84....@gmail.com>:

[sid]

Just got my version merged with latest on platform on github. db17 should have latest very soon.
I will need to delete old files on db17.
Postgress sql specific -- can you modify the db framework to add extra methods in JDatabaseQuery - make it empty method in base class and over ride in Postgress sql Query - that should work is'nt it?
Then you can send a pull request.
If you have skype chat we can walk through and see if it works for you.

sudhi

--
Thanks & Regards
Sudhi

Founder & Chief Architect
Hooduku Inc
Plexicloud
1.888.262.8389
http://www.hooduku.com
http://www.plexicloud.com

[84.le0n]

I'm merging db17 with recent platform, fixing PostgreSQL problems.

I can add JDatabaseQuery elements in platform as you've said, it works, but I prefer to talk about what new methods add before adding them ;-)

I'll send you my skype contact.

Eng. Gabriele Pongelli

AVVERTENZE AI SENSI DEL D.LGS. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e negli eventuali files allegati, sono da considerarsi strettamente riservati. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceveste per errore questo messaggio, Vi preghiamo cortesemente di darcene notizia all'indirizzo e-mail di cui sopra e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema; costituisce comportamento contrario ai principi dettati dal D.lgs. 196/2003 il trattenere il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse.

This electronic transmission is strictly confidential and intended solely for the addresses. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify us and delete the received data as soon as possible.

[84.le0n]

Today I've found some db17 changes about CASE WHEN statement inside a SELECT.

I don't know this statement very well, but I haven't found a FROM statement after the case during query building, is it maybe more correct to add a FROM statement?

 

I'll try to do some test with this statement in PostgreSQL, how is the syntax of this element for oracle and other new databases?

Eng. Gabriele Pongelli

AVVERTENZE AI SENSI DEL D.LGS. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e negli eventuali files allegati, sono da considerarsi strettamente riservati. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceveste per errore questo messaggio, Vi preghiamo cortesemente di darcene notizia all'indirizzo e-mail di cui sopra e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema; costituisce comportamento contrario ai principi dettati dal D.lgs. 196/2003 il trattenere il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse.

This electronic transmission is strictly confidential and intended solely for the addresses. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify us and delete the received data as soon as possible.

Il giorno 23/ott/2011, alle ore 20:28, sid <sid...@gmail.com> ha scritto:

[84.le0n]

I've checked CASE WHEN statement, it's ok for PostgreSQL too because
the FROM statement is added inside other function to the same $query
object.

Checking inside JInstallationModelDatabase::initialise() I've found
that #__schema update was done using always
"com_admin/sql/updates/mysql" folder (in db17 version too), now my
working copy has this changed to
'com_admin/sql/updates/' . (($type == 'mysqli') ? 'mysql' : $type)
to solve this issue.

I've found a query problem with some file inside com_admin/sql/updates :

* there's a REPLACE INTO in a file of them and it's not possible to
make the trick with SELECT-if-else and I don't know how to replace it
(with an INSERT or an UPDATE or both ?)

* ALTER TABLE with AFTER syntax to add the column after indicated
column is not supported in PostgreSQL and it is not useful for
relational database, is it possible to drop this syntax from Mysql ?

Eng. Gabriele Pongelli

2011/10/25 84.le0n <84....@gmail.com>:

[Mark Dexter]

I believe that the files in com_admin/sql/updates/mysql are only for mysql. You would add a folder called com_admin/sql/updates/postgres (or something) for the postgres queries and com_admin/sql/updates/mssql (or something) for the mssql queries. Mark

[84.le0n]

It's what I've done in my local working copy ;) 

Now I'm trying to change REPLACE INTO present in .sql files with something accepted by PostgreSQL that get the same result.

It could be translated with two queries, an UPDATE and an INSERT, I've tested them with phppgadmin and they worked, but doing the same with two pg_query I don't get the same result.

Cheers,

Eng. Gabriele Pongelli

AVVERTENZE AI SENSI DEL D.LGS. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e negli eventuali files allegati, sono da considerarsi strettamente riservati. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceveste per errore questo messaggio, Vi preghiamo cortesemente di darcene notizia all'indirizzo e-mail di cui sopra e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema; costituisce comportamento contrario ai principi dettati dal D.lgs. 196/2003 il trattenere il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse.

This electronic transmission is strictly confidential and intended solely for the addresses. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify us and delete the received data as soon as possible.

[84.le0n]

I've found a couple of SQL query that give the same result of mysql REPLACE INTO, a big issue solved!

Eng. Gabriele Pongelli

AVVERTENZE AI SENSI DEL D.LGS. 196/2003

Le informazioni contenute in questo messaggio di posta elettronica e negli eventuali files allegati, sono da considerarsi strettamente riservati. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceveste per errore questo messaggio, Vi preghiamo cortesemente di darcene notizia all'indirizzo e-mail di cui sopra e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema; costituisce comportamento contrario ai principi dettati dal D.lgs. 196/2003 il trattenere il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse.

This electronic transmission is strictly confidential and intended solely for the addresses. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify us and delete the received data as soon as possible.

Il giorno 26/ott/2011, alle ore 17:04, Mark Dexter <dexter...@gmail.com> ha scritto:

[Elin Waring]

Great  news! 

Elin

[84.le0n]

I've fixed a bug in fetch object list for my driver, now works as expected.

I've a problem during installation: after creating tables and populating them there is a process commented as "Attempt to refresh manifest caches" that doesn't convert #__ correctly if it is present inside a WHERE statement.
An example of what I'm talking about.

If the query is "SELECT * FROM #__dbtest WHERE ..." this goes well and #__ will correctly translated.

If the query is "SELECT * FROM information_schema.columns WHERE table_name='#__dbtest' " this goes bad and #__ will NOT correctly translated, it remains #__dbtest so I'll give an empty result.

The problem is inside replacePrefix of JDatabase.

Can anyone test and check about this issue ?

A workaround that works for me is a table name substitution done before query composition, but it's not a good solution for this issue.

Trying to install joomla with this workaround gives me other errors inside load() calls inside refreshManifestCache.

Thank you for any help!

Eng. Gabriele Pongelli

AVVERTENZE AI SENSI DEL D.LGS. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e negli eventuali files allegati, sono da considerarsi strettamente riservati. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceveste per errore questo messaggio, Vi preghiamo cortesemente di darcene notizia all'indirizzo e-mail di cui sopra e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema; costituisce comportamento contrario ai principi dettati dal D.lgs. 196/2003 il trattenere il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse.
This electronic transmission is strictly confidential and intended solely for the addresses. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify us and delete the received data as soon as possible.

Il giorno 27/ott/2011, alle ore 20:29, Elin Waring <elin....@gmail.com> ha scritto:

> Great news!
>
> Elin