Menu
▾
▴
Re: [phpesp-dev] Port to PEAR DB
|
From: Charlie S. <Sm...@ld...> - 2003-11-20 16:46:39
|
=46or the timestamp definition, I just used varchar2(14) - See the Oracle = table definitions. As long as the time is in yyyymmddhhmiss format it ough= t= 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=20 TIMESTAMP is not supported. I'll have to delay the post of my shcema here=20 until I fix that (hopefully by this week). My dbadmin also asked me to draw him a conceptual presentation of the table= s= =20 and theire relations... Boring job. I'm actually tweaking dbmodeller (a fre= e= =20 software) to do it automatically. More on that later if someone is = interested=20 (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 = = =20 > TESTED/Platform INIT/Date > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =20 > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D = =3D=3D=3D=3D=3D=3D=3D=3D 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=3D=3D'') > mysql=3D'select * from designer where username is null'; > else > mysql=3D'select * from designer where username =3D ' + = quotefunc(mystring); > > See = http://www.tek-tips.com/viewthread.cfm?spid=3D759&newpid=3D759&sqid=3D65748= 0= =20 > 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 =3D 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 =3D and !=3D. 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 =3D ''. > > 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 =3D=3D "mysql") { > > do something... > > } > > elseif ($DB =3D=3D "postgresql") { > > do something else... > > } > > elseif ($DB =3D=3D "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=20 > _______________________________________________ > phpESP-devel mailing list > php...@li...=20 > https://lists.sourceforge.net/lists/listinfo/phpesp-devel=20 > > > = --------------------------------------------------------------------------- >--- This message may contain confidential information, and is intended only > for the use of the individual(s) to whom it is addressed. > > > = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >=3D=3D=3D > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf=20 > _______________________________________________ > phpESP-devel mailing list > php...@li...=20 > https://lists.sourceforge.net/lists/listinfo/phpesp-devel=20 > > > = --------------------------------------------------------------------------- >--- This message may contain confidential information, and is intended only > for the use of the individual(s) to whom it is addressed. > > > = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >=3D=3D=3D > > > > ------------------------------------------------------- > This sf.net email is sponsored by:ThinkGeek > Welcome to geek heaven. > http://thinkgeek.com/sf=20 > _______________________________________________ > phpESP-devel mailing list > php...@li...=20 > https://lists.sourceforge.net/lists/listinfo/phpesp-devel=20 > > > > > ------------------------------------------------------- > 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=3Dmm/g22lp.tmp= l= =20 > _______________________________________________ > phpESP-devel mailing list > php...@li...=20 > https://lists.sourceforge.net/lists/listinfo/phpesp-devel=20 ------------------------------------------------------- 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/=20 _______________________________________________ phpESP-devel mailing list php...@li...=20 https://lists.sourceforge.net/lists/listinfo/phpesp-devel=20 ---------------------------------------------------------------------------= --- This message may contain confidential information, and is intended only for= = the use of the individual(s) to whom it is addressed. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D |
