Uploaded images into the DBMS.

[genesis]

Has there been any thought given to putting these into a DBMS table?

Here's why - with that you can replicate and load balance VERY easily using something like Slony (for Postgres) or other tools on other platforms.

This gives you survivability as well as redundancy.

I can replicate the tables, but the images are stored in a directory. If the original server suffers a disk crash all my image uploads instantly become broken, even though I have the database intact.

This is not particularly difficult to do - I do it for my forum software, where everything including images is stored in the tables.

[garvinhicking]

Hi!

IMHO binary data does not belong into a RDBMS, that's what filesystems are for. You can also easily replicate filestorage with NFS or even automatted rsync jobs.

This is much preferably than needing to use a DB wrapper to fetch files from the DB and spool them forward to the application.

Best regards,
Garvin

[Don Chambers]

I would think that storing images in a database would also be a significant performance hit for a site as well...

[akremedy]

I would think that storing images in a database would also be a significant performance hit for a site as well...

Yeah, I built a very basic image gallery once that stored all image data in BLOBs to prove to someone that it could be done - it was a good-enough concept but with turtle-like performance. C'est la vie.

Adam

[genesis]

I totally disagree on the performance aspect.

I have a forum system that maintains all data in Postgresql tables. It thumbnails images and stores both the thumbnail and base image in the table, and uses Apache's capability to run an on-disk cache along with internal knowledge to generate EUIDs as required.

It is blistering-fast.

Nonetheless I have a larger concern, and that is load-balancing. As system load goes up I eventually get where I need to have multiple round-robin hosts providing service to the system in general. This is very difficult to do in the current environment.

Slony's "base requirement" is that all replicated tables must have a primary key. Serendipity violates this and as such I cannot use Slony to replicate. I can fix it locally at the cost of breaking upgrade capability of the software in the future, so it would be nice to see Serendipity have primary keys on all tables. That's the first order issue.

The second-order issue is the capability to issue two handles for dbms use - one for reads where no writing is contemplated (and where those reads are atomic to any in-process transactions) and those where writes are part of the transaction stream.

This allows the bifurcation of the connections - that is they can point at DIFFERENT hosts. That in turn allows replicated database structures to be used without conflict. There is then one master database but all read accesses can come from a different (replicated) machine, which allows near-infinite scalability.

I have this structure implemented on my forum code and it works extremely well. I can get around the image storage issue by having any "new post" issue an rsync call (clunky, but it will work) but the inability at present to submit a Serendipity table space into replication makes the second difficult. Localized changes to permit dual DBMS handle use, while I could make them, will again break all forward version compatability.

Is there any interest in Serendipity being able to handle this?

[kleinerChemiker]

ever tried mysql cluster?

[genesis]

If I wanted to run Mysql I'd be running Wordpress. Serendipity's ability to use Postgres as a back end is the reason I moved to it.

Perhaps that was a mistake....

[garvinhicking]

Hi!

Sadly I don'T know slony. But you could easily add primary keys. S9y has some "horizontlly" scaling tables which do not by default have primary keys, as they are not needed, like "groupconfig" or "entryproperties". On those you could add a primary key like on groupconfig a key on "id" and "property".

The other tables where you could add some:

authorgroups: on groupid+authorid
access: on groupid+artifact_id+artifact_type+artifact_mode+artifact_index
config: on name+authorid
options: on name+okey
suppress: on ip,scheme,host,port,path,query
entrycat: on entryid+categoryid
entryproperties: on entryid+property
mediaproperties:mediaid+property+property_group+property_subgroup
permalinks: permalink+entry_id+type
plugincategories: class_name+category
pluginlist: plugin_file

About separation of READ and WRITE accesses: I believe this could be done from within the database layer itself. You could check the $query in serendipity_db_query() if it's a SELECT or UPDATE/DELETE/INSERT statement and divert to the appropriate RDBMS connection. For trying it, you could hardcode the alternate connection, a long-term route would be to either add the new required RDBMS 2nd configuration array to either an event plugin, or inside the local configuration of s9y, and only display them for capable DB layers.

But, I also aggree to kleinerchemiker. Looking out for Proxy-Solutions like available for MySQL might help for pgsql as well, technically it would be easy to utilize a central Proxy-Server that diverts the SQL queries to multiple servers accordingly. I once heard of "SQLRelay", don't know if it's capable for postgresql?

Regards,
Garvin