Re: Modification in LiveUser SQL for Perm and Auth

<smith@dybnet.de> wrote : > Actually MDB has its own native datatypes and maps these datatypes to > native datatypes. Any differences are automatically converted by MDB. > > Furthermore MDB allows you to write your DB schemas in an RDBMS > independent xml format. This is exactly the reason why MDB was written, > because in DB you will run into soooo many problems. PEAR DB is just a > common API (which is does really nicely too), but it has next to nothing > in terms of db abstraction. OK, I get it. If I read you well, MDB should know upon schema creation that an INT field type is called NUMBER in Oracle, that a TEXT is a CLOB and a BLOB a BLOB. (daunting task !) If all this works as you say, it should be enough to make one XML schema for MDB and we will need many different SQL queries for each different DB if we use PEAR DB. Maybe we should start writing the SQL for MySQL as much optimized as possible and then write other SQL for other DB. Keeping in mind some conventions we will have to decide upon, especially for : - Table names - Constraint names - Primary keys - Foreign keys - Column names My suggestions: - Table names: <3 chars package abbr>_<tablename> (always singular) Example: lvu_areaname - Column names: <3 chars table abbr>_<fieldname> (always singular) Example: arn_comment - Constraint names - Primary keys: pk<tablename> Example: pkareaname - Foreign keys: <3 chars table abbr>_fk<fieldname> Example: arn_fklanguage This will give us something like: CREATE TABLE lvu_areaname ( pkareaname int(11) UNSIGNED NOT NULL default '0', arn_fklanguage tinyint(4) unsigned DEFAULT '0' NOT NULL, arn_name varchar(20) NOT NULL default '', arn_comment varchar(255) default NULL ) TYPE=MyISAM; CREATE TABLE lvu_language ( pklanguage tinyint(4) UNSIGNED NOT NULL DEFAULT '0', lng_code char(2) default NULL, lng_name varchar(50) default NULL ) TYPE=MyISAM; Again, this is only a suggestion... Bertrand Mansion Mamasam