table prefix should NOT be required

[undisclosed]

Greetings,
I am attempting to evaluate Serendipity as the possible option for
our some 50,000 clients. I chose the typical, or
standard/easy single installation using the web
based install. Having already evaluated some 300 CMS/BOARD/
BLOG/ERP related applications, and having been a hosting company
since the 1970's, I had no difficulties providing the correct entries
for all the options provided in the installation form fields. However,
when choosing complete installation I was presented
with an error message indicating that I did not provide a
table prefix. We already have a prefixing system in place
on our MySQL databases to accommodate our clients. Since this is
a single installation for the sake of evaluation. I can see no
reason that I should be required to provide a table prefix
for a database that has already been created for the sole use of this
eval install.
Is this a (mis)feature, or a bug?
I guess I can find||grep the Serendipity source for the table prefix
statement(s) and comment them out. However, given that I am
evaluating Serendipity for possible long term usage. I felt I
should inquire as to why it's currently a requirement. So as to make
an informed decision regarding it's possible future on our servers.

Thank you for all your time and consideration in this matter.

P.S. I did perform a search for the term table prefix
prior to making this post. However, no matter which/how I made the
search, I was presented with some 1,100 matches. With either table
or prefix. But apparently not both. No. I didn't attempt to
look through all 1,100 results. Nor was I able to discover how to
retrieve results on exact match.

[garvinhicking]

Hi!

A prefix is required because some table names are otherwise reserved mysql words ("references" for example needs to at least read "s9y_references" or something like that).

So requiring the prefix gets rid of that problem on all SQL systems.

Best regards,
Garvin

[undisclosed]

Hello, and thank you for your fast response.
I see. What was the reason for choosing table names that would
result in name collisions? I'm sure there must have been a good
reason for taking this direction.

Thanks again for taking the time to respond.

--Chris

[garvinhicking]

Hi!

The reason was 'References' was not a reserved word when Serendipity began development. Only starting with MySQL 4.1 was it introduced, where Serendipity was already existing.

And before you ask why we don't use "`" prefix limiters: Serendipity also supports postgresql and SQLite, where "`" is not allowed, so we cannot use that in our generic SQL statements.

May I ask why a prefix is a problem for you? Having "blog_blabla" instead of "blabla" should be no problem for you? If we chose to use "[YOURPREFIX]serendipity_references" instead, you would also have a hardcoded prefix

Best regards,
Garvin

[ads]

At least in PostgreSQL (don't know about SQLite) you can put your table name in "", data in ''. This should work fine.

[garvinhicking]

Hi!

In MySQL and SQLite you can't. There you would need to use "`" instead, which does not seem to work in PGSQL.

Since s9y does not want to use different quoting mechanisms to keep the SQL syntax easy, using a table prefix is really the most easy and convenient way to go.

Best regards,
Garvin

[ads]

Since s9y does not want to use different quoting mechanisms to keep the SQL syntax easy, using a table prefix is really the most easy and convenient way to go.

Imho the "best" way would be a general interface for database access.
I did a deep look into this problem back in the beginning of this year, but i found out, that almost every piece of code, which is accessing the database, must be changed. It's not a full rewrite ... but almost.

[garvinhicking]

Hi!

Imho the "best" way would be a general interface for database access.

Well, I don't really think that the large rewrite would really justify that, because already a table prefix fixes the problem.

What is it, that disturbs people about a table prefix? I see not a single issue with that. If my table is called 'comments' or 'blog_comments', where's a problem?

Best regards,
Garvin

[ads]

What is it, that disturbs people about a table prefix? I see not a single issue with that. If my table is called 'comments' or 'blog_comments', where's a problem?

In "my" database world, you have schemata and such things.
A table prefix always looks like a cheap mysql workaround. This stuff first appeared with multiple webapplications hosted in a single database. If you have your own database, you even can create multiple databases for every application but if you buy this service, you pay for every db ... or you just have one db.

The table prefix does fix this one single problem, but has a lot of other disatvantages: you have to add the string in every query (maybe more than once), it makes your code more complex and less readable; it's a potential source for errors. Not really KISS.

S9y is stating compatibility with more databases than MySQL but i have seen enough errors in the past in the core or in different modules. Just recently i fixed a bug in the static pages modul which prevented updating of static pages when using a PostgreSQL database. The reason was a missing column in a table ... and it was just a syntax error (like all the times before) in the SQL code for adding this column.

[garvinhicking]

Hi!

A table prefix always looks like a cheap mysql workaround.

Exactly.

S9y is stating compatibility with more databases than MySQL but i have seen enough errors in the past in the core or in different modules. Just recently i fixed a bug in the static pages modul which prevented updating of static pages when using a PostgreSQL database. The reason was a missing column in a table ... and it was just a syntax error (like all the times before) in the SQL code for adding this column.

There are no open compatibility issues known to us in the core. Different plugins might be optimized for certian database types, that much is true. This is why we need help from testers who use different DBs than the developers do.

Best regards,
Garvin