Menu
▾
▴
Re: [phpesp-dev] Port to PEAR DB
|
From: Charlie S. <Sm...@ld...> - 2003-11-19 19:01:29
|
I got an email saying that this message I'm trying to send to the group wen=
t=
over the 40KB limit, so I'll break it up. This is the second of two. =
You'll need to use MS Word or something like that - maybe Codewrite - to =
open the files, unless you can get them to a UNIX (preferred) box.
Attached:
replace_fields.ksh - korn shell script to run PERL script.
process_inet_phpesp_code.pl - PERL script to replace occurrences of mysql =
calls with Oracle calls seen in my previous post.
>>> "Charlie Smith" <Sm...@ld...> 11/18/03 10:00AM >>>
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 m=
y=
next email to you all. This should firm up the datbase design for the =
Oracle schema. =20
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. =20
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=20
=66unction PEAR DB =
=
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 =
=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=
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.
=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
-------------------------------------------------------
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.tmpl=
=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
|
