MySQL to Oracle help

Question

I have a few questions about using mysql and oracle in a PHP app.

1)
Is it possible to code my PHP app to easily switch between these 2 databases? (Use MySQL for a year and then easily switch a "DB" file and it will run on Oracle?) I believe some large PHP projects have support for multiple database types.

2)
Does Oracle have something similar to phpMyAdmin?

3)
In general would one expect to see a performance gain in switching from mysql to oracle in a high traffic environment?

Answer 1

1. My guess on this one is "yes", because every other language allows it (e.g., Java, C#, etc.) The important points would be to externalize connection parameters in some way and to steer clear of database-specific functions and idioms in your SQL. Those will lock you into a particular vendor. Java allows implementation to change by writing in terms of interfaces and substituting vendor-specific implementations underneath. I don't know if PHP allows anything similar to that.
2. Oracle has many client tools, including (primitive) SQL*Plus and others.
3. Performance depends on factors that have as much to do with your schema and how you construct it as the database vendor. Oracle runs a lot of very high performance sites, but there's no guarantee that swapping one for the other will mean a significant performance difference.

Answer 2

For #2 - Oracle 10g XE has web user interface through which you can do many operations (From normal activities to maintaining a DB)(Documentation here). The standard editions have Enterprise Manager(documentation here).

Answer 3

Yes, but it's incredibly difficult to do properly.

Swapping out database drivers is a piece of cake. If you use PDO, you can connect to different database drivers by passing a different DSN. PDO has drivers for SQLite, MySQL, PostgreSQL, Firebird, DB2, Microsoft's SQL Server, Oracle, ODBC devices, and probably some more that I'm forgetting about. If you use it sensibly, using prepared statements and the like, you don't even have to worry about the different databases having different escaping rules.

This isn't enough.

MySQL and Oracle don't speak the same dialect of SQL. They don't have the same features and capabilities. The features they have in common work differently. They have different performance characteristics. Indexing is different. Collations are different. Even basic things, like how to reference tables, or what kind of field types you can use, can differ drastically between databases.

Even if you managed to abstract all that away, and come up with some common subset of functionality you could use, chances are your application will be slow on both databases. In order to get decent performance out of any of these things, you need to be familiar with how they work, and you need to use the special features they offer.

Taking full advantage of each database typically requires changing the data model a bit. Particularly with MySQL and Oracle, which are almost as different as two databases can get.

The further apart the data models get, the more difficult it becomes to verify that your application actually works properly on both databases. It also becomes more difficult to migrate the data between the databases.

It's certainly not impossible, but it's a lot of work.

Answer 4

1. There's the PDO extension that unifies the access of different databases on the API level. It is not a database abstraction, i.e. you have to write queries specific for the database system you're using. Switching from one dbms to another would most likely need another/adapted set of queries unless you're only doing very basic stuff (the parts of sql where all dbms vendors agree on). E.g. to select only the first three records of a query you could use (all queries untested):
   mysql: SELECT x,y,z FROM foo ORDER BY x DESC LIMIT 3
   oracle: SELECT * FROM ( SELECT x, y, z, row_number() over (ORDER BY x DESC) rn FROM foo ) WHERE rn <= 3 ORDER BY x desc
   t-sql: SELECT TOP 3 * FROM ( SELECT x,y,z FROM foo ORDER BY x )
   And PDO doesn't abstract that away.
   There are also a couple of database abstraction layers and object relational mappers for php, e.g. doctrine. They have their own syntax/object model that is then mapped (on the fly) to the query syntax of the actual dbms. You can switch between database systems without touching the code again (if all goes well). On the other hand you have to rely on the quality of the mapping. And you can't expect them to squeeze every bit of performance from each database system by using every tweak and special keyword in existence. You can usually influence or even manually change the "real" query. But then again you're back to optimizing the queries by hand for each dbms.

2. ...

3. As duffymo said, maybe, maybe not. Don't expect a performance gain without some afford. Don't even plan to have at least the same performance when simply switching from one dbms to another. If your mysql schemes and queries are somewhat optimized it might take some doing to reach an equal performance in oracle (and vice versa).