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:
Constructor, Functions and Variables:
-
SQLiteDB
SQLiteDB(string filename [, int mode [, string &error_message]])
Creates a new SQLiteDB object. Parameters are the same as for sqlite_open.
example:
$mydb = new SQLiteDB('../data/employees.sqlite');
-
query
query(string query)
Executes any valid SQL query, including ALTER TABLE. Returns $result.
example:
$result = $mydb->query("SELECT * FROM employees");
-
fetch_array
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
example:
$row = $mydb->fetch_array($result, DB_ASSOC);
-
fetch_assoc
fetch_assoc(resource result)
Fetches the next row from the given result handle. The row will be indexed by the fieldname.
example:
$row = $mydb->fetch_assoc($result);
-
fetch_row
fetch_row(resource result)
Fetches the next row from the given result handle. The row will be indexed by number.
example:
$row = $mydb->fetch_row($result);
-
seek
seek(resource result, int rownum)
Seeks to the row given by the parameter rownum
example:
$mydb->seek($result, 24);
-
$error
Contains the error message generated from the last query, or empty if none
example:
echo $mydb->error;
-
$handle
Reference to the connection resource
example:
$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
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:
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.
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.