Posted by Double Compile. Tuesday, May 6. 2008, 06:14 PM
in Databases
Last night I tried to install Oracle 10g XE on my Debian-based database server for testing purposes. Tried is the key word here.
Interestingly enough, Oracle offers a deb repository for this purpose. Add the following line to your /etc/apt/sources.list:
deb http://oss.oracle.com/debian unstable main non-free
Then execute the following commands as root (or with sudo) to add Oracle's public signing key and then install Oracle XE:
# wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add -
# apt-get update
# apt-get install oracle-xe
Here's the catch: my database server is an OpenVZ VPS. The Oracle installer checks for a sufficiently large swap partition and will bomb out if one of a sufficient size is not found. OpenVZ VPSs do not have a swap partition.
OpenVZ does support swap space, and that swap space can be set with barriers and limits, but the swap space is as a whole meaning that there is one swap space for the entire system, not a user-configurable swap space per-VPS.
So now comes the task of tricking the installer to think I the VPS has a swap partition, or trying to get it on there some other way. Stay tuned for the nuttiness.
Oracle is stupid.
Posted by Double Compile. Monday, April 21. 2008, 08:10 PM
in Databases
If you want foreign key support in MySQL, you need to use the InnoDB storage engine. If you want fulltext index support, you need to use the MyISAM storage engine. Ain't that a bitch?
Just for the record: PostgreSQL supports them both well.
To be fair, the syntax for fulltext searching in MySQL is more simple than that of fulltext searching in PostgreSQL. Here's an example:
MySQL:
SELECT title
FROM example
WHERE MATCH (title, body) AGAINST ('foobar')
PostgreSQL:
SELECT title
FROM example
WHERE to_tsvector(title || body) @@ to_tsquery('foobar');
So while the syntax is easier, you can't use fulltext and foreign keys simultaneously in MySQL. PostgreSQL wins this battle.
Posted by Double Compile. Saturday, October 13. 2007, 12:53 PM
in PHP
I applaud the efforts of the GoPHP5 campaign. They're getting commitments from lots of projects to adhere to minimum requirements of PHP 5.2; PHP 4 is end-of-life very soon. In addition to offering projects the chance to refactor and improve their application design, PHP 5 offers many things PHP 4 just doesn't. This series of posts will deal with things projects can get their fingers into that will benefit everyone.
The first: you have no excuse to support only one database.
PHP and MySQL for many years have gone together like bread-and-butter. Some applications still only solidly support MySQL. Nothing is inherently wrong with MySQL (shush, trolls), but not everyone can or will run it. I can't imagine many of these PHP applications are using super-proprietary MySQL features that can't be done with other systems.
Use an abstraction layer for your data access. PDO is a fantastic addition to PHP; it's been stable and in the core distribution for a good two years. If you have the extensions for each system, PDO can out-of-the-box support MySQL, Sqlite, PostgreSQL, MS SQL Server, Oracle, and recently DB2. In the Zend Framework, Zend_Db is a great tool as well. It's fast, well-thought-out, has many convenient features, and provides some more abstraction than PDO does. For instance, listing all the tables in a database, describing a table, and performing limit/offset queries.
All that's left to you the application developer is writing the SQL for creating your tables in each database system. As far as I'm concerned (Yes, and my opinion matters), you should support the "Big 4" (MySQL, PostgreSQL, MS SQL, Oracle) out of the gate; these have the widest install base.
Lastly, since you're going to the trouble of using such a data access layer, make sure you take advantage of value binding. Binding your values to placeholders in the SQL statement greatly reduces the risk of SQL injections. It also lets the DB worry about how to escape the value.
So stop being database system elitists. You'll have a wider and happier user install base if your system supports a few databases.
