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

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

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

What's Wrong with OOP

Proponents of Object Oriented Programming feel the paradigm yields code that is better organized, easier to understand and maintain, and reusable. They view procedural programming code as unwieldy spaghetti and embrace OO-centric design patterns as the "right way" to do things. They argue objects are easier to grasp because they model how we view the world. If the popularity of languages like Java and C# is any indication, they may be right. But after almost 20 years of OOP in the mainstream, there's still a large portion of programmers who resist it. If objects truly model the way people think of things in the real world, then why do people have a hard time understanding and working in OOP? I suspect the problem might be the focus on objects instead of actions. If I may quote from Steve Yegge's Execution in the Kingdom of Nouns : Verbs in Javaland are responsible for all the work, but as they are held in contempt by all, no Verb is ever permitted to wander about