Re: [phpesp-dev] Porting to PEAR and/or PostgreSQL

[James E. F. <jf...@uv...>]

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