[TYPO3-dev] PostgreSQL native drive for TYPO3 status update... and question about : 102: These fields are not properly

[ries van Twisk]

On Sep 30, 2007, at 11:16 AM, Martin Kutschker wrote:
ries van Twisk schrieb:

hey Masi,

there are two options


1) Import the fields in MySQL into PostgreSLQ and keep teh case.
So for example CType in mysql is CType in PostgreSQL.

The import of that is not a problem, and storing data in a database is
also easy.

For example, I could insert/update data into a PostgreSL database like
this:

UPDATE tt_content set "CType"='text' WHERE uid=1;

As you can see, I HAVE to double quote CType here. This can easily be  
done
in exec_UPDATEquery, exec_INSERTquery etc...

I know it works with quoting. But I would like to get rid of it if
possible, because of this:

Quoting the field-name is more difficult because specially
in the where clause you always need to parse the SQL
and assemble it again for a specific RDBM. If this is done in C,
I would say this can be fast and efficient in C, But I don't think we  
should
parse each and every query to map it to a appropriate RDBM in PHP.




But then I run into this problem for example with a query like this:


MySQL version: SELECT * FROM tt_content WHERE CType='text';
PostgreSQL version: SELECT * FROM tt_content WHERE "CType"='text';

Since all data in the where clause is just text, I would need to parse
teh where clause it and quote all fields,
which will take up CPU cycles (I do have a good SQL parser ready for the
job... but well..... it will be slow)

This is what DBAL does.

And that's why it's so slow, first of all because of AdoDB layer,
second because we need to parse SQL queries.


2 ) So I decided to go for this method:

I store all field names in PostgreSQL in lower case.
That means inserting always works, updating always works, also
from extensions.

and a query like this : SELECT * FROM tt_content WHERE CType='text';
will give me results aswell,
this because CType will be matched against a lower ctype in the
PostgreSQL database. good news!

Only problem is when extension authors don't quote columns they should
(eg with names like MID which is an SQL function, etc). DBAL's
autoquoting circumvents this problem.

What I did in my extension is to have a configuration which tells me
which tables must be quoted. Currently it's done manually but it could
be done by checking the TCA or ext_tables.sql for suspicious column  
names.

However, I had problems with cached content and the showed me an
additional problem, for example we have this table:

(The below table is created from my BE module that converts a MySQL DB
to a PG database)
CREATE TABLE cache_pages
(
   id serial NOT NULL,
   hash character varying(32) NOT NULL DEFAULT ''::character varying,
   page_id integer NOT NULL DEFAULT 0,
   reg1 integer NOT NULL DEFAULT 0,
   html text NOT NULL DEFAULT ''::text,
   temp_content integer NOT NULL DEFAULT 0,
   tstamp integer NOT NULL DEFAULT 0,
   expires integer NOT NULL DEFAULT 0,
   cache_data text NOT NULL DEFAULT ''::text,
   CONSTRAINT cache_pages_pkey PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE cache_pages OWNER TO postgres;
CREATE INDEX cache_pages_page_id ON cache_pages USING btree (page_id);
CREATE INDEX cache_pages_sel ON cache_pages USING btree (hash, page_id);

As you can see all fields lowercase, now TYPO3 does something like this:

SELECT * FROM cache_pages WHERE page_id=1;

and then in PHP we do something like

$this -> content = $row['HTML'];

And remapping all rows is really faster than parsing the query?

Yes, because normally we only have a couple of these fields in
the table anyways, so the loop as shown in PHP looks expensive,
but often we would loop it just a couple of times. I also think
I can make it even faster by just looping over the rows in the  
fieldmapper,
rather then looping over the result set. I just made it like this  
because..
well.. I was trying out and see what other problems I might find.

The objective of this exercise was for me to see how compatible
TYPO3 core is with other RDBM's without using expensive SQL parsers.
I would say we can come really really close to full compatibility  
without
breaking anything.


Other problems show up with IMHO incorrect SQL generated by TYPO3,
here is a nice example:

I Click on task center and see this SQL error:
SELECT * FROM tx_impexp_presets WHERE (public>0 || user_uid=1) ORDER BY
item_uid DESC, title;
Needs to be:
SELECT * FROM tx_impexp_presets WHERE (public>0 OR user_uid=1) ORDER BY
item_uid DESC, title;

Please file such bugs. They are easily fixed in the Core.

no prob...



and here is a other nice one:

DB check:
SELECT
uid,storage_pid,fe_group,shortcut,content_from_pid,mount_pid,media FROM
pages WHERE storage_pid!="" OR fe_group!="" OR shortcut!="" OR
content_from_pid!="" OR mount_pid!="" OR media!=""

This is quite interesting, apparently core sometimes use || and in other
cases OR,
but what is more interesting is that storage_pid = of the type integer,
and core compares it now to a empty string.

But what is the purpose of it? If storage pid is an integer it may not
contain an empty string. Or is it a NULL column and the idea was to
check against this?

The above SQL was also generated by core (DB check functions)...
The core generates an empty string to compare against an integer
value which is incorrect. The constraint on storage_id is NOT NULL,
so I think the programmer assumed it works the same as in PHP
I think (0=='') this evaluates to TRUE. in any RDBM this shouldn't be
the case ofcourse. I know that Oracle handles an empty string as NULL,
PostgreSQL handles an empty string as a string with zero bytes.

Apparently MySQL handles a empty string as 0 aswell, much like what  
PHP does,
which is bogus IMHO.

Again the fix is simple and is compatible with MySQL, PG and oracle.


In PostgreSQL this is not possible. You cannot compare an empty string
to an integer field, however this will work:

SELECT
uid,storage_pid,fe_group,shortcut,content_from_pid,mount_pid,media FROM
pages WHERE storage_pid!='0' OR fe_group!='0' OR shortcut!='0'" OR
content_from_pid!='0' OR mount_pid!='0' OR media!='0';
(Double quotes changed for single quotes and added an integer 0)

This  how that query was build...

It was created in selectNonEmptyRecordsWithFkeys

with this line: $cl_fl = implode ('!="" OR ',$fieldArr). '!=""';

In the above case quoting was not done by the DB engine, but hard coded
in the PHP. I changed it to this one:

$cl_fl = implode ('!=\'0\' OR ',$fieldArr). '!=\'0\'';

and whoooops it works!

So you now rely on auto-conversion ('0' => 0). Does this work on other
DBs? You could write 0 in the query, but fe_group is a VARCHAR so the
plain implode won't work.

Actually,

what I have seen with insert queries that an integer field get's  
filled in with
an empty string. However due to the associate array given in insert  
query
I can easily check the field type for a given fieldName and table
and and intval any integer field. For example:

$myArray = array (
	'textTypeField1' => 'Masi',
	'textTypeField2' => '',
	'integerTypeField1' => '110',
	'integerTypeField2' => ''
);

get's converted to:

$myArray = array (
	'textTypeField1' => 'Masi',
	'textTypeField2' => '',
	'integerTypeField1' => 110,
	'integerTypeField2' => 0
);

Since I can ask the RDBM teh field type for a given field name within  
a table I know that a field
needs to be an integer, then I can simply intval the value and voila,
the insert query works.





My conclusion so far:
As far as I can see is that TYPO3 core need slight modifications that
are all compatible with MySQL but will make TYPO3 core much more
compatible with other databases.

Yes. With DBAL PostgreSQL works quite well, but I don't have used all
sysext modules/extensions yet.

I will see how DBAL handles some of the queries and do a quick speed  
comparison with it.



It would have been great if all fields in the databases are all lower  
case,
unfortunately this is not the case. But with the simple field mapper  
class
we can re-map these fields, and per query we talk mostly about 1-2  
fields
anyways so data retrieval will stay fast. (I think the function can be
written faster even).

Does this work:

SELECT lowercase AS mixedCase FROM ....

So maybe it's enough to parse the SELECT clause.

Almost,

it needs to get written like this :

SELECT lowercase AS "mixedCase" FROM ....

However that means I need an SQL parser, and I want to stay away
from such a parser for each and every query.

For example I also have cases like this:

SELECT * FROM table WHERE mixedCase= 'somevalue'.....

in that case I would need to re-write it to:

SELECT * FROM table WHERE "mixedCase"= 'somevalue'.....




I am currently curious how other people solve this issues when using
other databases,
I know there are some that uses oracle, and some that uses PostgreSQL
and wonder how they bypass these problems. (I am not sure how DBAL will
handle this internally)

DBAL uses mixed case and does excessive query parsing (unless you use
execSELECTquery and friends).

figured that.... I think the doc mentions the speed impact which is  
quite huge.


For myself, I always wanted to write a native PG (I love PG...) drive
and see what I get, and now wonder
if this can be usefull to the community some how.  However to make this
work really well, core needs some modifications that needs to be  
tested and
merged back into 4.2 branche?

IMHO the mixed casing really spoils the fun.

it's indeed the biggest issue next to empty string to integer  
comparisons.
I feel these are the two mayor problems.


One thing I would like to see is to get rid of the XLASS function and
tell typo3 what storage
driver to use. Something like:  $typo3_db-driver = 'MySQL'; In
localconf.php

We could, but even DBAL uses XCLASS and I think this is easy enough.
More interesting would be a way to install DBAL or a native PostgreSQL
driver in the TYPO3 installer.

Currently I need a running MySQL installation to load all data and  
tables
into PG. installing extensions is not supported as of yet... Might  
work, might not work...


One advantage using this method is just speed, much faster then using
AdoDB layer (although I love AdoDB) and additional SQL parsers.

To do this you need some more query or SQL function generating
functions. And you must teach the Mysql users to use them!

I think all serious extension are already mostly compatible... and  
only minor tweaks
are needed. Which can be easily added the extensions that are  
currently under maintenance.

I do have an idea with an SQL parser to make even these extensions  
more 'compatible'.

what I can do is first try any SQL query with my current system. Then  
when it fails
I will log it, but run the same SQL through a SQL query parser and  
assembly it again for PG.
Then try the sQL again.

This will create some additional over header, but might guarantee  
more compatibility.
At least most queries will be fast, and the administrator will get a  
overview of all incompatible
queries which can be looked up in the extensions (core is almost good  
anyways).


side note:
What I see/hear from the industry that there is really a demand for  
support for other RDBM's
and it would be great if we can support others 'out of the box' with  
the same speed as with MySQL
and with a high degree of compatibility. Specially for this company  
it's a burden to maintain yet an
other DB next to our current one and if we can put everything in one  
DB the whole system is easer to
maintain with less resources.

Q: if I make core changes and patches, shall I make then against  
4.1.2 as an unified diff?

Ries van Twisk