This class makes connecting to, and interacting with an SQLite database super easy. The best, and almost only documentation for the SQLite database is at their official website: www.sqlite.org. The following is what I based my code on:
- How SQL Database Engines Work - by Dr. Richard Hipp, creator of SQLite
- Compilation Options For SQLite
- SQLite Syntax Diagrams
- PHP Data Object (PDO) Classes
- Connecting to your database
- PRAGMA Statements
- CREATE TABLE
- Column Types: TEXT, INTEGER, REAL, BLOB - default is BLOB
- TEXT - a text string of any length (use for Date and Time values)
- INTEGER - whole numbers - positive or negative (use for Unix time values)
- REAL - floating point numbers - positive or negative (use for Julian day numbers)
- BLOB - a blob of data
- Primary Key: INTEGER PRIMARY KEY (AUTOINCREMENT - not recommended!)
- The INTEGER PRIMARY KEY becomes an alias for rowid, and speeds queries
- Omitting AUTOINCREMENT will make inserts run a little faster, and keep inserting beyond the largest possible integer - 9,223,372,036,854,775,807 (~ 9 1/4 Quintillion)
- Constraints: UNIQUE, NOT NULL, CHECK ( expression )
- UNIQUE - every value must be unique
- NOT NULL - the column may not contain a NULL value - the default is to have a NULL value
- CHECK - must match ( expression )
- DEFAULT: number, value, CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or ( expression ) - default is NULL
- CURRENT_TIME - HH:MM:SS
- CURRENT_DATE - YYYY-MM-DD
- CURRENT_TIMESTAMP - YYYY-MM-DD HH:MM:SS
- COLLATE: BINARY, NOCASE, RTRIM - default is BINARY
- Used for sorting and comparing string
- REFERENCES: table(column-name - optional) ON DELETE / ON UPDATE:
- NO ACTION - the default - nothing happens
- RESTRICT - prohibit the action from being taken on the parent key
- SET NULL - set the child key (this one) to NULL
- SET DEFAULT - set the child key (this one) to the default specified
- CASCADE - delete the record, or update the child key along with the parent being modified
- Column Types: TEXT, INTEGER, REAL, BLOB - default is BLOB
- CREATE INDEX
- ALTER and DROP TABLE(s)
- INSERT, UPDATE, and DELETE Data
- SELECT Queries
- CREATE TRIGGER's and VIEW's
- VACUUM and ANALYZE Maintenance Operations
This is the constructor for the class, and it will open an SQLite database in your BASE . 'sqlite\' folder. This method also turns 'PRAGMA foreign_keys = ON', and will alert you if foreign_keys are not supported. If no database is specified, then one will be opened in memory.
|$database||The name of the database beyond where you put it in the BASE . 'sqlite/' folder. If you leave it blank, then the database will be created in memory.|
|$version||What version the sqlite database you are opening / creating is. Anything besides a 2 is a 3.|
|Returns||True or false - whether or not the database was successfully opened.|
This will attach a database to the other(s) you currently have open.
|$database||What you named the database. Also, where you put it beyond the BASE . 'sqlite/' folder.|
|$alias||How you plan on referring to it in your queries.|
|Returns||Whether or not the database was successfully attached.|
This will create an SQLite table in your database. You can call this method every time you open the database, and for every table created. If any changes were made, then it will automatically update (or alter) the table. Don't forget to index your referenced fields! The documentation says that it is beneficial in every instance.
|$table||The name of the database table you are creating.|
|$columns||An array of columns in the form of array($name => $type).|
|$changes||If you are altering a table, then specify the changed field(s) in the form of array('old field name' => 'new field name').|
|Returns||False - if the table has already been created in it's requested state.|
True - if the table was newly created (or altered).
Creates an index on an SQLite table's field(s).
|$table||The name of the table being referenced.|
|$suffix||A unique suffix to distinguish this index from any others you may have on the same table.|
|$columns||You can index one field (a string), or many (in an array).|
|Returns||Whether or not the index was created successfully.|
This method allows you to set or retrieve the value(s) of an SQLite pragma.
|$name||The name of the pragma.|
|$value||The value you would like to set the pragma name to. If you want to retrieve the value(s), then leave this blank.|
|$all||Set to true if you are expecting more that one row of values.|
|Returns||True or false whether the query was successful or not when you specify a value, or an array of values when retrieving the value.|
Performs a query on your SQLite database.
|$query||The SQLite query.|
|$values||An array of values that match the question marks in your query. This is a better way to query your SQLite database, as it is unncessary to escape any data when doing this, and you are protected from SQL injection attacks.|
|Returns||Whether or not the query was successful.|
Performs an exec command on your SQLite database. In general, you should only use this method when you are making changes to the database that you are in control of. Leave the inserts, updates, selects, and deletes for either the query or statement methods.
|$query||The exec command you would like to perform.|
|Returns||Whether or not the exec was successful.|
Use this method when you want to perform multiple insert, update, or delete statements where the query structure remains fixed, and only the values are different.
|$query||Similar to the query method, only this time your query must have question marks where / when appropriate.|
|$values||An array of values that match up with the question marks in your query. If the array is multi-dimensional then all of your queries will be put into a transaction, and they will be completed much faster than if you were to do one query at a time.|
|Returns||Either the total number of rows affected by your query and values, or false if the query was unsuccessful.|
This allows to you to easily insert one or many rows into an SQLite table.
|$table||The table you want to insert records into.|
|$array||An array of $field => $value pairs to be inserted. If this array is multi-dimensional then multiple rows will be inserted.|
|Returns||Either the total number of rows inserted if the array is multi-dimensional, or the last inserted id if only one record was inserted. If the query failed for some reason then it will return false.|
This method allows you to update one, or many rows in your SQLite database.
|$table||The name of the table you are updating.|
|$array||An array of $field => $value pairs to be updated. If this array is multi-dimensional then multiple rows will be updated.|
|$column||The column you're using to define what exactly should be updated (usually the table's primary id column).|
|$id||The id (or value) of the column you'd like to update. If array (above) is multi-dimensional, then this should also be a matching array of values / id's.|
|$add||If you have any other conditional statements that you would like to make then you can say so here.|
|Returns||The total number of rows updated, or false if the query failed.|
This method allows you to delete one, or many rows in your SQLite database.
|$table||The name of the table whose records you are deleting.|
|$column||The column you're using to define what exactly should be deleted (usually the table's primary id column).|
|$id||The id (or value) of the column you'd like to delete. If this is a multi-dimensional array of values then multiple records will be deleted.|
|$add||Anything you might like to add to the end of the query.|
|Returns||The number of rows deleted, or false if the query failed.|
This allows you to fetch the results from your SELECT query statement.
|$return||The available values are:|
'row' or 'num': Your array will be indexed by column number.
'assoc': Your array will be indexed by column name.
'both': Your array will have both numbered and named indexes.
|$all||If set to true (or anything besides false), then all of the results will be returned at once in one big multi-dimensional array.|
|Returns||An array of results, or false if there are no results to fetch.|
This method is a quick way to get a single value you may be interested in.
|$query||The SQLite query.|
|$values||An array of values that match the question marks in your query.|
|Returns||A single value, or false if there isn't one.|
This is a quick way to find out if a record exists or not.
|$table||The SQLite table's name.|
|$unique||The column you are searching against.|
|$value||The unique value of your column.|
|Returns||Returns the primary rowid of the parameters in question, or false if there is no such record.|
If the built-in FTS Functionality is not available to you then this method is here to save the day, albeit not as well, fast, or efficiently.
|$action||The available values are:|
'create': Create a new fulltext table of $fields from $table.
'upsert': Updates or inserts as the case may be the $fields into your $table.
'search': Searches the $fields in your $table for $text.
|$table||The table with the fields you want to search against.|
|$fields||'create': An array of fieldname(s).|
'upsert': An array of $field => $value pairs.
'search': An array of fieldname(s).
|$text||'search': The text you want to search for.|
|$limit||'search': Comma-separated start, duration values. eg. LIMIT 5, 10|
|Returns||'create': (bool) Whether or not the table was created.|
'upsert': (bool) Whether or not the row was upserted.
'search': An array of search results (table rowid's) ordered by relevancy - up to 200.