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

[Charlie S. <Sm...@ld...>]

Yes, the use of PEARDB should make the long if's largely unnecessary.

=46or field definitions, I'd be inclined to go with NULLs as well.  We'd us=
e=
 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 * =
=66rom table where field   IS  null=20
would be the code for going against the Oracle table rather than   select *=
 =
=66rom 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 an=
d=
 NOT NULL, which would you want to go with?  =20

=46or 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 ''.

=46or 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:
>=20
>>> 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=20
>> planning
>> to make severeal schema with the hope to have minimum code rewrite on the
>> phpESP side ? (I guess it makes sense).
>>
>=20
> I think it's best to work out a schema that:
>=20
>   1. Is as generic as possible so we can avoid or minimize code like this:
>=20
>       if ($DB =3D=3D "mysql") {
>         do something...
>       }
>       elseif ($DB =3D=3D "postgresql") {
>         do something else...
>       }
>       elseif ($DB =3D=3D "oracle") {
>         etc, etc
>       }
>=20
>   2. Is still functionally the same as the current one (I don't think=20
> anyone wants to get into a database redesign).  Let me clarify=20
> that...it's not just functionally the same, it is the same except we may=
=20
> change some data types to something more generic and rename some fields=20
> to avoid keyword collisions.
>=20
> So it would be the same generic schema for each database, but different=20
> 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=20
not in the past.)  I think that PEAR::DB will help avoid this in most=20
cases.  By making phpESP database engine independant, use of neat DB=20
=66eatures will likely have to be sacrificed.  When it comes down to it,=20
if a suitible ``enum'' type does not exist accross the board (in a way=20
that will be acessible with the same SQL query), then just use a numeric=20
type, and map it to an enumeration only inside the PHP code.  If NULL is=20
not available as a default, don't use it, use -1 and remap all internal=20
representation that needs it.  I really hope that NULL will be an=20
option, because it is the best way to represent the lack of a value.=20
NULL is especially important in the response data to indicate that the=20
question was left blank.  Perhaps, we will have to avoid counting on=20
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=20
is needed, then try to avoid it with OO/polymorphism.  This may be a=20
better general approach anyway, with objects representing the database=20
tables.

I have consitently found that some of the phpesp data is poorly suited=20
=66or storage in a relational db.  This is compounded by the fact that=20
mysql (which was the initial customer reqirement) was much happier with=20
a few tables with lots of records than lots of tables with few records.=20
  I believe XML w/ a well desinged schema/dtd (using some unknown sort=20
of storage) would be far better for the representation of the survey=20
data (and could provide multi-lingual support much more redily than the=20
current organization).  User/group tables are probably best done with=20
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