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

[Steffen Kamper]

Hi ries,

wow, you solved a lot of issues. These "lazy" comparisons are only detected 
while not working with mysql, that helps a lot of fixing that. Hope that 
this wlll  go to core.
You made a great job, thanks for that.

Your suggesting about not xclass but configure the native driver is ++1 IMHO

vg  Steffen

"ries van Twisk" <typo3 at rvt.dds.nl> schrieb im Newsbeitrag 
news:mailman.1045.1191175945.1851.typo3-dev at lists.netfielders.de...
>
> 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
>
>
>
>
>
>
>
>
>
>
>
>