SQLiteDB - SQLite and PHP

last modified 2005-04-18 by jon

Overview

This page details SQLiteDB, a simple PHP class I've written for working with SQLite. (Though they share the same name, this is a different project than the recently released SQLiteDb ActiveX DLL.)

The source for SQLiteDB is in the public domain, so you may use it for any purpose. It was originally written back in 2003 to help me better manage my SQLite databases by providing support for ALTER TABLE statements. I am assuming a working knowledge of PHP, SQLite, and the current SQLite PHP functions.

A major drawback to SQLite is that only the latest release (3.2.0) supports the ALTER TABLE syntax, and the implementation is incomplete (you can only rename tables and add columns). When you consider that even the latest PHP releases are still bundled with a pre-3.0 version of SQLite, altering tables can be a bit of a pain for most users. If you want to alter a table, you have to create a temporary table, copy the contents, delete the original, recreate the original with new column definitions, copy the contents back, and delete the temporary table.

With the SQLiteDB class, whenever you call the query function, it parses the SQL string and if it's a valid ALTER TABLE statement, it will then perform the above operations. As for error messages, I've included a class variable that either includes actual SQLite error messages if they are generated by an invalid query, or custom messages generated by the ALTER TABLE function. So whenever you run a query, you can check the $error var to see if there are any error messages.

Source code:

Get it here

Constructor, Functions and Variables:

ALTER TABLE syntax

ALTER TABLE tbl_name alter_specification [, alter_specification] ...

alter_specification:
    ADD column_definition
  | DROP column_definition
  | CHANGE old_col_name column_definition

column_definition:
    same as for create table statements
examples:
ALTER TABLE employees ADD first_name VARCHAR(50), ADD last_name VARCHAR(50)
ALTER TABLE invoices ADD note text, CHANGE idate invoice_date DATETIME
ALTER TABLE foo DROP bar, ADD bar2 INTEGER

Known Limitations:

Acknowledgements:

Thanks to Mark Meves for the drop columm bug fix.

Questions? Corrections? Suggestions? Email me. Feel free to use this code in any way with or without modification or acknowledgement, although a link to me is always appreciated. Email me if you would like permission to reproduce this documentation.

Table of Contents

Compatibility

Related Pages