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
Labels:

Comments

  1. DmitryJanuary 20, 2010 at 5:52 AM

    Helpful, thanks!

    ReplyDelete
  2. AnonymousJune 15, 2010 at 12:59 PM

    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
  3. webzenguyMay 14, 2011 at 5:01 PM

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

    ReplyDelete
  4. AnonymousJuly 3, 2011 at 4:36 AM

    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
  5. web design companyOctober 13, 2011 at 6:21 AM

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

    ReplyDelete
  6. AnonymousNovember 23, 2011 at 8:50 AM

    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
  7. AnonymousApril 30, 2012 at 7:11 PM

    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

Writing a Minimal PSR-0 Autoloader

An excellent overview of autoloading in PHP and the PSR-0 standard was written by Hari K T over at PHPMaster.com , and it's definitely worth the read. But maybe you don't like some of the bloated, heavier autoloader offerings provided by various PHP frameworks, or maybe you just like to roll your own solutions. Is it possible to roll your own minimal loader and still be compliant? First, let's look at what PSR-0 mandates, taken directly from the standards document on GitHub : A fully-qualified namespace and class must have the following structure \<Vendor Name>\(<Namespace>\)*<Class Name> Each namespace must have a top-level namespace ("Vendor Name"). Each namespace can have as many sub-namespaces as it wishes. Each namespace separator is converted to a DIRECTORY_SEPARATOR when loading from the file system. Each "_" character in the CLASS NAME is converted to a DIRECTORY_SEPARATOR . The "_" character has no special ...
6 comments
Read more

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 Shoulders Composition doesn’t happen in a vacuum. Bach wa...
7 comments
Read more

Learning Prolog

I'm not quite sure exactly I was searching for, but somehow I serendipitously stumbled upon the site learnprolognow.org a few months ago. It's the home for an introductory Prolog programming course. Logic programming offers an interesting way to think about your problems; I've been doing so much procedural and object-oriented programming in the past decade that it really took effort to think at a higher level! I found the most interesting features to be definite clause grammars (DCG), and unification. Difference lists are very powerful and Prolog's DCG syntax makes it easy to work with them. Specifying a grammar such as: s(s(NP,VP)) --> np(NP,X,Y,subject), vp(VP,X,Y). np(np(DET,NBAR,PP),X,Y,_) --> det(DET,X), nbar(NBAR,X,Y), pp(PP). np(np(DET,NBAR),X,Y,_) --> det(DET,X), nbar(NBAR,X,Y). np(np(PRO),X,Y,Z) --> pro(PRO,X,Y,Z). vp(vp(V),X,Y) --> v(V,X,Y). vp(vp(V,NP),X,Y) --> v(V,X,Y), np(NP,_,_,object). nbar(nbar(JP),X,3) --> jp(JP,X). pp(pp(PREP,N...
22 comments
Read more