[Phpwebsite-developers] DB Issues for 1.x

[Greg M. <drk...@co...>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I was answering this forum post tonight
https://sourceforge.net/forum/message.php?msg_id=3165794, when I came
across some of my old notes that should be taken care of in 1.x.  If the
database issue is not resolvable, then mysql should be the only database
offered at setup time.  The users have an expectation of multi database
support when it isn't there, yet.

Users would like to use PostgreSQL, Oracle, and I suppose MS SQL, etc.
There are two problems that affect database portability.  One, tools
like phpmyadmin make for helpful development environments.  However,
when mysql exports the table, incompatible create table syntax
statements are generated by the mysqldump command.  The link below and
another private email message summarize the problem.

Two, databases like PostgreSQL and Oracle limit the table name to 32
chars as I recall.  phpWebSite code and third party module code would
have to be rewritten to use smaller table names.  Table prefixes added
during installation and branch sites along with mod_ prefix convention
further complicate the problem.  The concatenated prefixes make for some
very small names when all you have to work with is 32 chars.

Greg

https://sourceforge.net/forum/message.php?msg_id=2342542
"
The quick answer it that mysql takes liberties with the sql standard.
The create table statement

create table table_name (
  item_one      varchar(10),
  item_two      varchar(10),
  index (item_one )
);

Should change to these two statements.

create table table_name (
  item_one      varchar(10),
  item_two      varchar(10)
);

Create index on table_name_index (item_one);

Please check the syntax of the create index.  I haven't look it up and I
am going to bed so it may be wrong.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFCkq21xyxe5L6mr7IRAj1jAKCTRjJ2YnQ0RxV81wbZAXXMnAdqwACdG/QT
JtfV0ruckHOB0Rt69tmpW48=
=vxK5
-----END PGP SIGNATURE-----