Search

Has anyone ever asked the question as to whether Symphony could be used with other database systems?

I work for a firm that primarily use Oracle, but could be persuaded to have a MySQL database for a Symphony project.

How easy/hard would it be to convert? Has the core team considered making it possible to choose database providers, like some other CMSs?

It would be very cool to see…

Well, now that Oracle owns MySQL (when they acquired Sun Microsystems, who owned MySQL), they might make it easier to convert. But you never know. You might check on Oracle’s site.

I’ve asked a few times. Personally, I’m a bit partial to PostgreSQL as a database server. The response from the team is usually “we’ll have a look in the future - no promises” (but in a friendly, affable tone).

We’ll have a look in the future - no promises.

;)

Craig, you forgot to be friendly and affable. Possibly a:

<voice tone="friendly" type="affable">$STATEMENT</voice>

Might get you through there :P

What is the value of using other database providers? Do they provide performance gains?

It’s not the performance gains or not, it’s just that my employer is a very very big multinational travel company who have many Oracle databases set up as that’s the standard for the company. Asking for a MySQL one just to run Symphony would probably cause an arguement with IT. There’d be no one to support it etc…

It’s just the way it works here.

If you really wanted Oracle support, you probably could by modifying the MySQL driver class. Replace all the MySQL specific calls with PDO based ones and hope for the best. AFAIK, you’d need the Oracle PDO PECL driver compiled with PHP too. Here’s a guide for PDO. edit: After a bit of reading, you could have a read over the OCI8 documentation and attempt a straight conversion from mysql_connect to oci_connect etc.

It’s little things that’ll crop up from extensions using specific MySQL statements that’ll produce funky results, but I imagine most of the Symphony queries are fairly standard across all DBMS’s anyway.

If not, you could always attempt hack the files to accommodate the differences.

With that said, if you manage to modify the driver to work with PDO, it could potentially open up Symphony to use any of the PDO Drivers

A bit much for me I’m afraid, but I’m going to read into it anyhoo!

Has Symphony got a database abstraction layer?

I’ve read that this makes it easier to swap databases about. That way, no SQL would need to be written in the core or extensions, just calling a PHP Class to build a statement then the abstraction layer translates that statement into the relevant SQL and calls the relevant database, using whichever provider the user wants.

Any thoughts core team?

Alistair’s Advanced Symphony Database Connector (ASDC) is integrated into the next version’s core. That’s a start.

Symphony abstracts the database connection insofar as class.mysql.php makes the connection and executes queries. However the MySQL class receives SQL statements as text strings since these are concatenated and built throughout the entire system, including third party extensions.

So while class.mysql.php could be abstracted further to support connections to other databases, it’ll only be compatible if the SQL statements it receives are compatible. 95% of queries are basic commands, but some might use MySQL-specific syntax (boolean or regex search for instance).

Yeah, that’s what I thought about the MySQL statements. I wouldn’t have thought that there are too many extensions using non standard SQL.

So, my thinking says that if I have to install on a system that doesn’t support MySQL in favour of Oracle, I could probably edit the class.mysql.php file to connect to Oracle instead?

If that’s the case, then it would probably be a lot easier than I thought, I’d just have to Police which extensions we used to ensure compatiable SQL statements, sounds like fun! lol.

I think the problem would quickly become customised SQL for each database. In order to see advantages (speed, code wise, etc) in different database engines, you’re likely to end up writing different code for each DB. There are SQL standards, but good luck running complex untouched MySQL queries on a Postgres or Oracle backend.

Abstraction is great, but when you’re talking about situations where you need really performant queries, I know a lot of devs that will just jump right back into raw SQL (and rightly so, a lot of abstraction layers do perform queries in a set manner that can result in slower execution). Unless you’re also maintaining the abstraction layer, you’re eventually likely to write around it.

That all being said, I’d love to see the team move toward this at some point in the future.

So, my thinking says that if I have to install on a system that doesn’t support MySQL in favour of Oracle, I could probably edit the class.mysql.php file to connect to Oracle instead?

That’s what my long winded post was getting at :)

@tonyarnold I actually find that Symphony’s ‘abstraction’ layer really just accepts raw SQL anyway so you could tweak queries as needed regardless.

I’m actually pretty curious as to how much of Symphony’s (and extensions) SQL is standard. If there are certain statements that are specific to the vendor I wonder if some sort of __() type function could be created that’d translate the query for each of the DBMS’s.

I suppose this is one of those things we will never really know about until we start to tinker with it!

@brendo, sorry… I should read more, or try and understand more lol.

As I don’t understand this sort of stuff very well, I will leave it out there. Although I may have to get one of our DBA’s here to take a look (which could take years lol)

Thanks for everyone’s input!

Create an account or sign in to comment.

Symphony • Open Source XSLT CMS

Server Requirements

  • PHP 5.3-5.6 or 7.0-7.3
  • PHP's LibXML module, with the XSLT extension enabled (--with-xsl)
  • MySQL 5.5 or above
  • An Apache or Litespeed webserver
  • Apache's mod_rewrite module or equivalent

Compatible Hosts

Sign in

Login details