Re: [phpesp-dev] Port to PEAR DB

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

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