[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 3:17 AM, Martin Kutschker wrote:

> ries van Twisk schrieb:
>>
>> On Sep 29, 2007, at 3:56 PM, ries van Twisk wrote:
>>
>>> Alright,
>>>
>>> wrong decision about the upper/lower case issue..
>>>
>>> I just wanted to prevent some additional DB overhead to
>>> make that DB returns fields in the right case.
>>
>> Alright.. we are back on track.. I made a mapper that map's between
>> upper/lowercase
>> field names based on what the system would expect.
>>
>> It's a bit ugly and takes up a couple of additional resources, but  
>> it's
>> much faster then trying to parse SQL queries.
>
> You mean, modify the keys of the result rows to the casing of the TCA?
>
> Masi
>
> PS: I tried to quicken up DBAL in a one DB setup with PostgreSQL  
> and ran
> into the same casing problems. PostgreSQL/DABl could not be talked  
> into
> being either case sensitive or simply using the correct case. Or is it
> the TYPO3 definitions (SQL) and the queries differ?


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

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)


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!


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'];

Oopps, doesn't work.. What happens is that $row['HTML']; doesn't  
content any data ($row['html'] does!)
So what I am doing is that after each SELECT statement I map 'html'  
back to 'HTML',
I made a quick function that generates a file that looks like this:

$pg_fieldmapper['be_groups']['groupmods'] = 'groupMods';
$pg_fieldmapper['be_groups']['locktodomain'] = 'lockToDomain';
$pg_fieldmapper['be_groups']['tsconfig'] = 'TSconfig';
$pg_fieldmapper['be_users']['realname'] = 'realName';
$pg_fieldmapper['be_users']['usermods'] = 'userMods';
$pg_fieldmapper['be_users']['locktodomain'] = 'lockToDomain';
$pg_fieldmapper['be_users']['disableiplock'] = 'disableIPlock';
$pg_fieldmapper['be_users']['tsconfig'] = 'TSconfig';
$pg_fieldmapper['be_users']['createdbyaction'] = 'createdByAction';
$pg_fieldmapper['cache_pages']['html'] = 'HTML';
.......

So I loop over each result record and I check if I need to remap  
field names to
there upper case versions.

Currently done in a function like this:

class pgFieldRemapper {
	function __construct() {
		$pg_fieldmapper = array();
		require_once(PATH_site.'typo3conf/pg_fieldmapper.php');
		$this -> pg_fieldmapper = $pg_fieldmapper;		
	}
	
	function remapFields($tables, $row) {

		if (!is_array($row))
			return $row;
		//t3lib_div::print_array($row);
		
		$tables = explode(',', $tables);
		foreach ($tables AS $table) {
			$table = explode(' ', trim($table));
			$table = $table[0];
			foreach ($row AS $fieldName => $value) {
				$fieldName = strtolower($fieldName);
				if (isset($this -> pg_fieldmapper[$table][$fieldName])) {
					$row[$this -> pg_fieldmapper[$table][$fieldName]] = $value;
					unset($row[$fieldName]);
				}
			}		
		}
		//t3lib_div::print_array($row);
		return $row;
	}
}

it's quite crude but works very well.
So now I do have TYPO3 running on PostgreSQL.

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;

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


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.

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


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)

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?

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

That value can then tell core what class to load for the DB layer.

We could make different drivers for each databases so that core  
always be native on some DB,
and that DBAL can handle additional connections like we do now.

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

let me know your thoughts, private or on the list...

thanks for your time,
Ries van Twisk



--
Ries van Twisk
Freelance TYPO3 Developer
email: ries at vantwisk.nl
web:   http://www.rvantwisk.nl/
skype: callto://r.vantwisk
Phone: + 1 810-476-4193