Two blogs partially sharing database - can it work?

[Mekk]

I am to manage two personal blogs (English and Polish). I installed separate copies of serendipity (using some symlinks though to avoid copying bundled liraries plugins and such) sideways, but configured both blogs to use the same Postgres database on the same account but with different prefix.

So far so good, haven't tested much but it seems everything is all right. I have tables like en_access, en_authorgroups, en_authors, ... and tables like pl_access, pl_authorgroups, pl_authors, ...

Now the crazy idea: maybe I could sync some of those tables somehow? For example to have authors, groups, plugins, images in sync?

Possibilities:

a) create some triggers to pass updates between tables

(seems uneasy)

b) drop some of those tables replacing them with views (for example drop en_authors and create view en_authors as select * from pl_authors).

(it would mean that some operations would fail on the 'en' blog as views can't be updated)

c) create fully-blown table aliases using postgres rule system (with some effort it should be possible using postgres specific rule system)

(this looks most promising)

d) hack around postgresql schemas and search path (to use the same prefix in both bases but partially different schemas, like common tables in public, blog specific in dedicated schemas)

(also promising but require hacking SET SEARCH_PATH somewhere into serendipity)

Leaving apart the method: if we consider such table sharing possible, which tables would it make sense to share and which should stay blog specific? The purpose is to have separate blogs with separate content, but share authorization/passwords/access rules/active plugins and such.

What do you think about the idea as such?

PS I am not sure whether I am to try sth like that, just wanted to hear comments....

[kleinerChemiker]

e) write a script that sync both and execute it periodically.

[garvinhicking]

Hi!

On MySQL, views can also be inserted/updated, does that really not work in PGSQL? Because that method would be my personal favorite.

The tables you would probably want to keep unique are:

serendipity_config
serendipity_entries
serendipity_comments
serendipity_entryproperties
serendipity_plugins [if you plan to have differing plugins on each blog]
serendipity_pluginlist
serendipity_plugincategories
serendipity_categories
serendipity_references
serendipity_options
serendipity_access [because permissions to categories may vary from blog to blog]

so that leaves those sharable tables:

_authors
_groups
_groupconfig
_authorgroups
_mediaproperties

However, I'm thinking if two separate blog installs in your case are really the best way. Couldn't you maybe achieve what you want using different main categories on your blog? You could even create custom templates for each main category branch, and using the sidebarhider plugin you should also be able to hide certain sidebar plugins on certain languages.

Maybe even the multilingual plugin could already help you. The only thing that this solution might not properly solve is if identical sidebar plugins should have different language titles in each variant...

HTH,
Garvin

[Mekk]

Hi!
On MySQL, views can also be inserted/updated, does that really not work in PGSQL? Because that method would be my personal favorite.

Not (yet). But this is mostly just different syntax. See
http://www.postgresql.org/docs/8.3/stat ... views.html
and
http://www.postgresql.org/docs/8.3/stat ... pdate.html

The tables you would probably want to keep unique are:
(...)
so that leaves those sharable tables:
_authors
_groups
_groupconfig
_authorgroups
_mediaproperties

OK. Thanks. If I ever decided to handle registrations, I'd dig this discussion

However, I'm thinking if two separate blog installs in your case are really the best way. Couldn't you maybe achieve what you want using different main categories on your blog? You could even create custom templates for each main category branch, and using the sidebarhider plugin you should also be able to hide certain sidebar plugins on certain languages.

Thought about it for some time. But I really dislike to mix content written in different languages.

PS My blogs are up and running, http://notatnik.mekk.waw.pl/ and http://blog.mekk.waw.pl/ I am still learning serendipity, but I must say I am impressed, very nice application with unexpected useful features. Thanks!