Re: [phpesp-dev] Port to PEAR DB

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

FYI: If you're modeling PostgreSQL, Autdoc[1] is worth looking at.

[1] http://www.rbt.ca/autodoc/

On Wed, 2003-11-19 at 14:59, Stefan Champailler wrote:
> 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.tmpl
> > _______________________________________________
> > 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
-- 
mcg
-------------------------------------
The IT Lab (http://www.itlab.musc.edu)