Port DB layer to Postgres?

[Matthew]

LMCE runs MySQL as its database server. I have an existing Postgres installation, and I'd rather not DBA two different databases. The PHP scripts seem to use ADO to connect to the DB, which might mean only the datasource has to be changed, but that's usually just wishful thinking. And there's lots more glue than in the PHP.

I see that there is a documented technique for distributing the MySQL server across the network, rather than running on the core localhost. But it seems untested, and just a hint at using a different RDBMS entirely.

Has anyone investigated or documented what it would take to port LMCE from MySQL to Postgres? Any notes on any dependencies of LMCE on MySQL specifically, either SQL syntax, or features, or some other constraint? OTOH, is anyone running LMCE on Postgres already?

[darrenmason]

Hi Matthew,

There was some discussion on this a while back and basically my recollection of the outcome was that the benefit of migrating to a different RDBMS was not really worth the effort.

The database is primarily used for configuration storage, there is not really much of a transactional need.

In terms of DBA'ing multiple types of databases, there is little to no maintaintence and administration performed on the databases, since they are not used transactionally and do not see large growth figures or cause any performance bottlenecks.

My feeling is that there is enough work still to do in getting the software to work faultlessly and adding new technologies and devices that adding a new variable such as multiple DB support creates unneccessary work.

But that said, if you are keen to experiment - it would likely give you quite a good exposure to the database dependencies and that information would be useful to the rest of the community documented in the wiki, even if the port proved fruitless.

At a guess I would expect most of the SQL dispersed throughout the system to be quite portable and if memory serves me correctly I think that the first version of Pluto was actually written to use MS SQL Server - but I could  be wrong.

Your primary DB dependencies will be found in the Object-DB wrapper that is written in C++, the many shell scripts for setup and bootup checks and as you mentioned the web admin server code (which is mainly php).
I would suspect that there is an element of automatic timestamping of rows utilised, especially by the sqlCVS stuff - not sure how well this would port.

Good luck
Darren

[Matthew]

According to the wiki entry "Building From Source, you can download an archive of the sql dumps of the databases needed for the build. But that's v1.0 of the DBs. Is there a more recent version? That would be the code from which to generate a schema view, including interfaces, and isolate the code in the runtime that connects to that DB API.

[danielk]

Matthew, you can just dump your existing tables using mysqldump. If you're trying to build from source updated versions of the tables are available from svn.charonmedia.org in the db_dumps directory. But these are used to generate a the pluto_main database, they do not by themselves give you a working DB.

In anycase, you would still need a mysql database even if you ported db_wrapper to use postgres, because MythTV requires mysql. Until MythTV works with postgres there is no reason to entertain the idea of porting the rest of LinuxMCE.