Skip to main content

PHP and SQLite2 on CentOS

I'm changing shared hosting providers for my websites and the sites I manage for clients as Salt City Tech. Most of the sites are basic websites powered by PHP, share a common code base, and store data in MySQL databases. But I've also been taking advantage of the situation to transition the sites from MySQL to SQLite. There are a lot of things that annoy me about SQLite (such as allowing NULLs in primary-key columns, ignoring foreign key constrains, and incomplete ALTER TABLE support) and a few things that I think are pretty awesome (such as user-defined functions in PHP). Pragmatically speaking, SQLite will be more convenient for deployment to and backup from the shared hosting environment and the sites' simple storage requirements fall exactly in SQLite's sweet-spot.

The transition hasn't been terribly difficult... the biggest obstacle has been configuring a development environment that adequately mirrored the shared hosting deployment environment. I happened to have a cloned virtual CentOS 5.3 image at my disposal so I started with that, but the hosting provider supports SQLite v2 (ancient!), which's storage format is incompatible with the newer version 3 in CentOS. The search engines didn't offer me much help in finding a packaged SQLite2 solution on CentOS that would meet my needs, so I figured I'd post a few notes in case someone else may find them helpful.

Obtain the sqlite-2 RPM for CentOS from RPMForge repository (in case you want to work with the database files directly):
wget http://dag.wieers.com/rpm/packages/sqlite/
sqlite-2.8.17-1.el5.rf.i386.rpm
Install sqlite using the -i and --force options (do not use -U so you do not replace sqlite3):
rpm -i --force sqlite-2.8.17-1.el5.rf.i386.rpm
Install the php-devel package so phpize and phpconfig are available:
yum install php-devel
Obtain the source RPM for PHP (CentOS installs 5.1.6--yuck! Now I remember why I hate using packages for important software):
wget ftp://mirror.switch.ch/pool/3/mirror/centos/5.3/os/SRPMS/
php-5.1.6-23.el5.src.rpm
Extract PHP from the SRPM:
rpm2cpio php-5.1.6-23.el5.src.rpm | cpio -id php-5.1.6.tar.gz
Uncompress the archive:
tar zxvf php-5.1.6.tar.gz
Build the sqlite extension using the standard phpize, ./configure, make routine:
cd php-5.1.6/ext/sqlite
phpize
./configure
make
Install the extension:
cp modules/sqlite.so /usr/lib/php/modules/
echo extension=sqlite.so > /etc/php.d/sqlite2.ini
Even though it's been obsoleted by version 3 for almost 6 years now, version 2 of SQLite works fine for my needs here. And God forbid I have to migrate the sites again or the provider upgrades, upgrading my code base is as simple as this:
sqlite site.db.old .dump | sqlite3 site.db
for f in $(ls *php); do
sed -i 's/new SQLiteDatabase/new SQLite3/g' $f
sed -i 's/fetch\(SQLITE_ASSOC\)/fetchArray\(SQLITE3_ASSOC\)/g' $f
done

Comments

  1. Very helpful. Thank you.

    I'm using PHP 5.2.10 from the CentOS-5 Testing
    repositories at http://dev.centos.org/centos/5/testing/ and your procedure worked perfectly to add sqlite2 to my CenOS-5.5 box. The one hiccup was that the php distro in the 5.2.10 SRPM is a .bz2 rather than a .gz file, and I had to run cpio -t to find it when the tarball didn't extract.

    ReplyDelete
  2. There is a great tutorial on installing sqlite, sqlite3 and mcrypt on Centos here: http://www.exteon.ro/en/articles/php/compile-extensions

    ReplyDelete
  3. running rpm -qpl php-5.1.6-23.el5.src.rpm can show you all the files inside the SRPM. Once you identify what file you want you can provide it to cpio -id.

    ReplyDelete
  4. That was really interesting to know about.It was really helpful.Thank you !

    ReplyDelete
  5. Side note -> extra information to help others:

    1. This post assumes you have a compatible compiler like gcc (many production systems do not to prevent people from compiling malicious code), so you may need to install a compiler before you run your configure or make commands.


    2. If after following this blog this you get this error:

    PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/sqlite.so' - /usr/lib/php/modules/sqlite.so: undefined symbol: php_pdo_register_driver in Unknown on line 0

    when you start PHP. All that you need to do is install php-pdo (yum install php-pdo) and this will fix the problem. (After you restart apache /etc/init.d/httpd restart)

    Hope this helps others out... or even me if I need to do it again. Cheeers.

    ReplyDelete
  6. Thank you very much! I was able to loosely follow your directions on Ubuntu Server 12.04. You saved me from having to redo my old SQLite2 PHP code. Once again, thank you very much!

    ReplyDelete

Post a Comment

Popular posts from this blog

Geolocation Search

Services that allow users to identify nearby points of interest continue to grow in popularity. I'm sure we're all familiar with social websites that let you search for the profiles of people near a postal code, or mobile applications that use geolocation to identify Thai restaurants within walking distance. It's surprisingly simple to implement such functionality, and in this post I will discuss how to do so.

The first step is to obtain the latitude and longitude coordinates of any locations you want to make searchable. In the restaurant scenario, you'd want the latitude and longitude of each eatery. In the social website scenario, you'd want to obtain a list of postal codes with their centroid latitude and longitude.

In general, postal code-based geolocation is a bad idea; their boundaries rarely form simple polygons, the area they cover vary in size, and are subject to change based on the whims of the postal service. But many times we find ourselves stuck on a c…

Composing Music with PHP

I’m not an expert on probability theory, artificial intelligence, and machine learning. And even my Music 201 class from years ago has been long forgotten. But if you’ll indulge me for the next 10 minutes, I think you’ll find that even just a little knowledge can yield impressive results if creatively woven together. I’d like to share with you how to teach PHP to compose music. Here’s an example: You’re looking at a melody generated by PHP. It’s not the most memorable, but it’s not unpleasant either. And surprisingly, the code to generate such sequences is rather brief. So what’s going on? The script calculates a probability map of melodic intervals and applies a Markov process to generate a new sequence. In friendlier terms, musical data is analyzed by a script to learn which intervals make up pleasing melodies. It then creates a new composition by selecting pitches based on the possibilities it’s observed. . Standing on ShouldersComposition doesn’t happen in a vacuum. Bach was f…

Reading Unicode (UTF-8) in C

In working on scanner code for Kiwi I did a bit of reading up on Unicode. It's not really as difficult as one might think parsing UTF-8 character by character in C. In the end I opted to use ICU so I could take advantage of its character class functions instead of rolling my own, but the by-hand method I thought was still worth sharing. Functions like getc() read in a byte from an input stream. ASCII was the predominant encoding scheme and encoded characters in 7-8 bits, so reading a byte was effectively the same as reading a character. But you can only represent 255 characters using 8 bits, far too little to represent all the characters of the world's languages. The most common Unicode scheme is UTF-8, is a multi-byte encoding scheme capable of representing over 2 million characters using 4 bytes or less. The 128 characters of 7-bit ASCII encoding scheme are encoded the same, the most-significant bit is always 0. Other characters can be encoded as multiple bytes but the mo…