PostgerSQL possible?

[engelant]

Hi there,
i tried to install LetoDMS using the AdoDB driver "postgres8", but the sql files for database initialisation won't run, as they are written for mysql. Now, is there a way to use PostgreSQL, are there some special init sql files? Also, did i get it right, that AdoDB takes a SQL statement and translates it to any of the given drivers? Or is it just a ODBC wrapper that still requires a specific syntax for each type of database?

Thank you in advance

[steinm]

Hi there,
i tried to install LetoDMS using the AdoDB driver "postgres8", but the sql files for database initialisation won't run, as they are written for mysql. Now, is there a way to use PostgreSQL, are there some special init sql files? Also, did i get it right, that AdoDB takes a SQL statement and translates it to any of the given drivers? Or is it just a ODBC wrapper that still requires a specific syntax for each type of database?

Thank you in advance

The bad news is, there is currently nor support for other database than mysql. The good news is, I'm aware of it and try to eliminate that restrictrion. All those database abstraction libraries provide a common api to different databases but they do not handle different sql dialects. That is still up to the user. I already started to support more databases and the next version will hopefully have support for sqlite3. PostgreSQL is probably not as difficult to support but not planed for the near future.

Uwe

[engelant]

Thanks for your reply, but that makes me think: Why is there an abstraction layer being used, if only one type of database is used?
As I just read, AdoDB requires one to use Portable SQL as described on ( http://phplens.com/lens/adodb/tips_portable_sql.htm ), there might be a more update version showing wich standart is implemented in the current parser. I suppose for using innodb in mysql there is a setting within the AdoDB object. So, in my opinion it would be useful to rewrite all the SQL statements to a Portable SQL eqivalent and using the library the "right way". I'm not shure, if that already is the case, but from all the other perspectives LetoDMS looks very nice to me.

I will take a look at the SQL statements now, and hopefully get an overview of how many statements are affected.

Is there any specific reason, why the SQL dialetct being used is MYSQL and not ANSI SQL(I suppose that is Portable SQL)?

Thank you in advance

[steinm]

Thanks for your reply, but that makes me think: Why is there an abstraction layer being used, if only one type of database is used?
As I just read, AdoDB requires one to use Portable SQL as described on ( http://phplens.com/lens/adodb/tips_portable_sql.htm ), there might be a more update version showing wich standart is implemented in the current parser. I suppose for using innodb in mysql there is a setting within the AdoDB object. So, in my opinion it would be useful to rewrite all the SQL statements to a Portable SQL eqivalent and using the library the "right way". I'm not shure, if that already is the case, but from all the other perspectives LetoDMS looks very nice to me.

I will take a look at the SQL statements now, and hopefully get an overview of how many statements are affected.

Is there any specific reason, why the SQL dialetct being used is MYSQL and not ANSI SQL(I suppose that is Portable SQL)?

Thank you in advance

I have to admit that adodb has more methods for portable sql than I thought. The reason they are not used in letodms are out of my scope. It's probably unawareness of the problem or adodb just didn't have it when it was first used.

Anyway, there are still limits that can't be handled by an abstraction layer. Some of the sql statements in letodms use concat_ws() which is (as far as I know) only available in mysql.

I'd be very happy to make it more portable.

Uwe

[engelant]

Ok, that of course is a problem, since functions within SQL are not ANSI specified afaik. But as always there is a way around this: apply functions post query. I know that this sometimes may cause a huge overhead, if a statement looks like "SELECT * FROM WHERE trim("field1") <> ''; ". Another solution would be to provide data "As needed", e.g. if one one php function needs all "field1" values as they are and another function needs to compare against the trim("field1"), then a second table would be the right choice. I also think, that may speed up certain operations, since the DB Server wouldn't have to apply the function on every field.

It wold propably cost more memory, but less then running a second DB server. Also memory is kind of cheap nowdays.

There are some things I'm not shure about, e.g. what ANSI-SQL (92, 98, 2003) or other SQL standart AdoDB parses, what statements are definitly working. I suppose statements like "INSERT * ON DUPLICATE INSERT" are made use of, but are on the other hand not suppurted by PostgreSQL. ANSI-SQL somenumber defines MERGE for that Operation, but I don't know, if AdoDB has implemented this statement.