Menu
▾
▴
Re: [phpesp-dev] Port to PEAR DB
|
From: Stefan C. <sch...@ea...> - 2003-11-21 06:42:49
|
Just came to know about "http://www.azzurri.jp/en/software/clay/index.js", may be useful as well (and looks much more polished than dbModeller) stF > Oh.... > > Hmm, to tell you the truth, that's precisely the kind of stuff I don't want > to do. The reason is very simple : if I store a date in a db, then the > colum in which the data is stored must be of the date type. It's just > better. That's why I'm into all those protability troubles. (no offense > hey, I'mp sure you'll get my point). > > stF > > > For the timestamp definition, I just used varchar2(14) - See the Oracle > > table definitions. As long as the time is in yyyymmddhhmiss format it > > ought to be able to be treated like a string. > > > > >>> "Stefan Champailler" <sch...@ea...> 11/19/03 12:59PM >>> > > > > Thanks for the big update Charles ! > > > > Just when I thought I was done with the Oracle schema, I just discovered > > that TIMESTAMP is not supported. I'll have to delay the post of my shcema > > here until I fix that (hopefully by this week). > > > > My dbadmin also asked me to draw him a conceptual presentation of the > > tables and theire relations... Boring job. I'm actually tweaking > > dbmodeller (a free software) to do it automatically. More on that later > > if someone is interested (and if I finish the tweak :)) > > > > stF > > > > > Let's make all mysql declarations of 'NOT NULL default ', become NULL > > > by definition. It appears that Oracle will have to be dealt with in > > > the at the code level when dealing with the mysql empty string. We may > > > be able to abstract this to by passing the query statement through some > > > type of parser, but I'm not sure how clean this would be. > > > > > > So far, it looks like we've got Stephan, Sean and Jeremy Buchman who > > > have volunteered on this project. I'll try and get the schema for > > > Oracle in my next email to you all. This should firm up the datbase > > > design for the Oracle schema. > > > > > > I'm thinking that by using the PEAR DB library, those wanting to stay > > > with mysql could just substitute the PEAR DB calls in your unit > > > testing, as you go. So, whether using PEAR DB in some calls and direct > > > mysql calls in others, it should work. > > > > > > I've attached oracleIncBeta.php, the Oracle versions for all the mysql > > > calls I have identified as being used in the phpESP. Most were pretty > > > straigt-forward to handle. There was one that I'd like to see if we > > > can dispose of, however. See if you can guess which one it is. > > > > > > Please take a look at these routines (there mysql counterpart would be > > > mysql_connect for oracleConnect, mysql_close for oracleClose, etc.) > > > and assist in identfying the PEAR DB function that would take it's > > > place. > > > > > > I'd like to propose we use ORACLE as the test platform. Or at least, > > > I'd like to see the end product work with Oracle as one of the standard > > > databases. I'll attach the Oracle schema in my next posting. > > > > > > There are 13 functions. If you would like to sign up for one, please > > > mark your name or initials next to the routine. > > > > > > Please list the name of the identical PEAR DB fuction, if there is on. > > > If not please indicate. Mark TESTED with an 'x/ORACLE' to show test > > > complete on Oracle platform. Mark INIT with your initials and then add > > > a date when test complete. ex. cws. ex. cws-11/18/03 > > > > > > function PEAR DB > > > TESTED/Platform INIT/Date > > > ================== =================== > > > ================ ======== oracleConnect > > > oracleClose > > > oracleQuery > > > oracleFetchArray > > > oracleNumRows > > > oracleExecuteQuery > > > oracleFetchRow > > > oracleError() > > > oracleFreeResult > > > oracleInsertId > > > oracleResult > > > oracleSelectDB > > > oracleAffectedRows > > > > > > Charlie > > > 11/18/03 > > > > > > Charlie Smith wrote on 10/24/03 10:26AM: > > > <snip> > > > Calling for help - W O U L D A N Y O N E B E > > > INTERESTED IN ASSISTING WITH THE PORT OF MYSQL TO PEAR DB? If so, > > > please respond, and I'll try and divy out assignments. </snip> > > > > > > >>> "Charlie Smith" <Sm...@ld...> 10/06/03 07:28AM >>> > > > > > > So code would be modified to be: > > > if (mystring=='') > > > mysql='select * from designer where username is null'; > > > else > > > mysql='select * from designer where username = ' + > > > quotefunc(mystring); > > > > > > See > > > http://www.tek-tips.com/viewthread.cfm?spid=759&newpid=759&sqid=657480 > > > for further discussion on topic of empty string. > > > > > > >>> "Charlie Smith" <Sm...@ld...> 10/02/03 01:51PM >>> > > > > > > Yes, the use of PEARDB should make the long if's largely unnecessary. > > > > > > For field definitions, I'd be inclined to go with NULLs as well. We'd > > > use the NULL and NOT NULL qualifier in the field definitions and work > > > out the default values in the code. So we'd keep fields defined as NOT > > > NULL in Oracle. The field definition of fieldname ... NOT NULL > > > default '' is not valid in Oracle. Also, in Oracle, NULL fields are > > > detected differently that fields with data in them when using a select > > > statement. ie. select * from table where field IS null would be the > > > code for going against the Oracle table rather than select * from > > > table where field = null (This is an invalid select as you have to > > > use 'is' and 'is not' null when detecting null fields in the oracle > > > tables, not = and !=. So if there is code in phpESP which references > > > empty string in select statements, it would need to be modified. > > > > > > I had thought we may want to replace the NOT NULL default '' with just > > > default ' '. But had my reservations. Thanks for your input on this, > > > though I confess I am still a bit confused. In Oracle if there is no > > > supplied value for a nullable field (any field not declared as NOT > > > NULL), it's value is NULL by default. If you had a choice between > > > default NULL and NOT NULL, which would you want to go with? > > > > > > For following fields we could translate to Oracle as follows? > > > > > > MySQL becomes Oracle > > > int(10) unsigned NOT NULL default '0' NOT NULL > > > default NULL, NULL > > > int(10) unsigned default '0' default 0 > > > enum('Y','N') NOT NULL default 'N' NOT NULL (with check > > > constraint *) NOT NULL default '' NOT > > > NULL > > > > > > > > > * check constraint syntax: > > > alter table designer > > > add constraint designer_pdesign_ck > > > check (pdesign in ('Y','N')); > > > > > > We could also put a trigger on the table/field so that on insert or > > > update, if the new field value were null, it would get populated with > > > 'N'. We could handle all the other default values this way except for > > > the empty string ''. We'd have to modify the code for this where it > > > selects from table where field = ''. > > > > > > For ENUM types, a check constraint can be used in Oracle, to make sure > > > values are matched against those we want to allow. What would the > > > syntax look like in PostGres? > > > > > > Does this sound agreeable? > > > > > > >>> "James E. Flemer" <jf...@uv...> 10/01/03 06:43PM >>> > > > > > > Jeremy Buchmann wrote: > > > > On Wednesday, October 1, 2003, at 02:13 PM, Stefan Champailler wrote: > > > >>> With that: > > > >>> Let's discuss how to handle the schemas > > > >>> 1) ENUM type in PostGreSQL and Oracle. > > > >>> 2) the empty string in default values > > > >>> 3) default values vs NOT NULL > > > >> > > > >> My Oracle and PostgreSQL is limited, so I ask the question : are we > > > >> planning > > > >> to make severeal schema with the hope to have minimum code rewrite > > > >> on the phpESP side ? (I guess it makes sense). > > > > > > > > I think it's best to work out a schema that: > > > > > > > > 1. Is as generic as possible so we can avoid or minimize code like > > > > this: > > > > > > > > if ($DB == "mysql") { > > > > do something... > > > > } > > > > elseif ($DB == "postgresql") { > > > > do something else... > > > > } > > > > elseif ($DB == "oracle") { > > > > etc, etc > > > > } > > > > > > > > 2. Is still functionally the same as the current one (I don't think > > > > anyone wants to get into a database redesign). Let me clarify > > > > that...it's not just functionally the same, it is the same except we > > > > may change some data types to something more generic and rename some > > > > fields to avoid keyword collisions. > > > > > > > > So it would be the same generic schema for each database, but > > > > different creation scripts and things like that. At least, that's > > > > how I envision it. > > > > > > Please, please avoid huge if/else blocks if possible. (Even if I did > > > not in the past.) I think that PEAR::DB will help avoid this in most > > > cases. By making phpESP database engine independant, use of neat DB > > > features will likely have to be sacrificed. When it comes down to it, > > > if a suitible ``enum'' type does not exist accross the board (in a way > > > that will be acessible with the same SQL query), then just use a > > > numeric type, and map it to an enumeration only inside the PHP code. > > > If NULL is not available as a default, don't use it, use -1 and remap > > > all internal representation that needs it. I really hope that NULL > > > will be an option, because it is the best way to represent the lack of > > > a value. NULL is especially important in the response data to indicate > > > that the question was left blank. Perhaps, we will have to avoid > > > counting on defaults all together, and explicitly set all fields from > > > the code. > > > > > > If all else fails, and there is someplace where it looks like an > > > if/else is needed, then try to avoid it with OO/polymorphism. This may > > > be a better general approach anyway, with objects representing the > > > database tables. > > > > > > I have consitently found that some of the phpesp data is poorly suited > > > for storage in a relational db. This is compounded by the fact that > > > mysql (which was the initial customer reqirement) was much happier with > > > a few tables with lots of records than lots of tables with few records. > > > I believe XML w/ a well desinged schema/dtd (using some unknown sort > > > of storage) would be far better for the representation of the survey > > > data (and could provide multi-lingual support much more redily than the > > > current organization). User/group tables are probably best done with > > > some traditional db or directory (SQL or LDAP). > > > > > > -James > > > > > > > > > > > > ------------------------------------------------------- > > > This sf.net email is sponsored by:ThinkGeek > > > Welcome to geek heaven. > > > http://thinkgeek.com/sf > > > _______________________________________________ > > > phpESP-devel mailing list > > > php...@li... > > > https://lists.sourceforge.net/lists/listinfo/phpesp-devel > > > > > > > > > ----------------------------------------------------------------------- > > >-- -- --- This message may contain confidential information, and is > > > intended only for the use of the individual(s) to whom it is addressed. > > > > > > > > > ======================================================================= > > >== == === > > > > > > > > > > > > ------------------------------------------------------- > > > This sf.net email is sponsored by:ThinkGeek > > > Welcome to geek heaven. > > > http://thinkgeek.com/sf > > > _______________________________________________ > > > phpESP-devel mailing list > > > php...@li... > > > https://lists.sourceforge.net/lists/listinfo/phpesp-devel > > > > > > > > > ----------------------------------------------------------------------- > > >-- -- --- This message may contain confidential information, and is > > > intended only for the use of the individual(s) to whom it is addressed. > > > > > > > > > ======================================================================= > > >== == === > > > > > > > > > > > > ------------------------------------------------------- > > > This sf.net email is sponsored by:ThinkGeek > > > Welcome to geek heaven. > > > http://thinkgeek.com/sf > > > _______________________________________________ > > > phpESP-devel mailing list > > > php...@li... > > > https://lists.sourceforge.net/lists/listinfo/phpesp-devel > > > > > > > > > > > > > > > ------------------------------------------------------- > > > This SF. Net email is sponsored by: GoToMyPC > > > GoToMyPC is the fast, easy and secure way to access your computer from > > > any Web browser or wireless device. Click here to Try it Free! > > > https://www.gotomypc.com/tr/OSDN/AW/Q4_2003/t/g22lp?Target=mm/g22lp.tmp > > >l _______________________________________________ > > > phpESP-devel mailing list > > > php...@li... > > > https://lists.sourceforge.net/lists/listinfo/phpesp-devel > > > > ------------------------------------------------------- > > This SF.net email is sponsored by: SF.net Giveback Program. > > Does SourceForge.net help you be more productive? Does it > > help you create better code? SHARE THE LOVE, and help us help > > YOU! Click Here: http://sourceforge.net/donate/ > > _______________________________________________ > > phpESP-devel mailing list > > php...@li... > > https://lists.sourceforge.net/lists/listinfo/phpesp-devel > > > > > > ------------------------------------------------------------------------- > >-- --- This message may contain confidential information, and is intended > > only for the use of the individual(s) to whom it is addressed. > > > > > > ========================================================================= > >== === > > ------------------------------------------------------- > This SF.net email is sponsored by: SF.net Giveback Program. > Does SourceForge.net help you be more productive? Does it > help you create better code? SHARE THE LOVE, and help us help > YOU! Click Here: http://sourceforge.net/donate/ > _______________________________________________ > phpESP-devel mailing list > php...@li... > https://lists.sourceforge.net/lists/listinfo/phpesp-devel |
