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

[Martin Kutschker]

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:

> 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.

> 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?

> 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.

> 
> 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?

> 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.

> 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.

> 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.

> 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).

> 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.

> 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.

> 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!

Masi