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.
Constructor, Functions and Variables:
SQLiteDB(string filename [, int mode [, string &error_message]])
Creates a new SQLiteDB object. Parameters are the same as for sqlite_open.
$mydb = new SQLiteDB('../data/employees.sqlite');
Executes any valid SQL query, including ALTER TABLE. Returns $result.
$result = $mydb->query("SELECT * FROM employees");
fetch_array(resource result [,int result_type])
Fetches the next row from the given result handle. The row will be indexed by the result_type or DB_BOTH if none is specified. Possible values for result_type are DB_ASSOC, DB_BOTH and DB_NUM
$row = $mydb->fetch_array($result, DB_ASSOC);
Fetches the next row from the given result handle. The row will be indexed by the fieldname.
$row = $mydb->fetch_assoc($result);
Fetches the next row from the given result handle. The row will be indexed by number.
$row = $mydb->fetch_row($result);
seek(resource result, int rownum)
Seeks to the row given by the parameter rownum
Contains the error message generated from the last query, or empty if none
Reference to the connection resource
$myvar = $mydb->handle;
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
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
Multiple SQL statements in one call to the query function are not supported if one is an ALTER TABLE statement.
ALTER TABLE statements can be pretty slow on large tables, since the data has to be copied twice. Now that SQLite supports RENAME TABLE statements, I plan on rewriting this code so as to double the efficiency of the ALTER TABLE statements.
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.