Moving to SQLite For Those Who Know MySQL

By Victory | Published: August 15, 2009

SQLite is a dialect of SQL similar to MySQL. SQLite3 comes with some huge benefits when it comes to CAAR over MySQL. PHP5.2+ comes with the PDO class by default which has an interface to SQLite3.

SQLite3 does not require any setting up of user accounts like MySQL. SQLite databases are just normal system files which and be copied, deleted, FTP’ed, scp’ed, rsync’ed and generally passed around like any other file on the system.

This is a HUGE advantage, because you can do things like have a central server which trivially downloads all stats from satellite sites. Similarly you can have one repository for a “config” file (or mappings) and then upload to all your server sites.

No more messing around with importing and exporting, creating user accounts etc, just copy the file and go.

SQLite3 is pretty darn fast and the only drawback is when doing a lot of inserts from a lot of different connections. If you can rap the inserts into a transaction that its really fast.

Learning SQLite if You Already Know MySQL by Example

First off SQLite3 has more in common with MySQL than differences. I would like to address some of the differences which come up commonly when programing CRUD.

First off you will need to create a sqlite file using the PDO class, in these example we will assume that the database is or will be created in the db/ directory and that database name will be site.sqlite.

As you look over this code it might look more involved than you are use to. I tried to post something which show many common uses.

If you don’t know what TRANSACTION’s, FOREIGN KEYS, TRIGGERS or prepared statements are, then well you are a little slow on the ball in the world of SQL, but this example will use all of them so you can add them to your tool belt.

You should read this code and the comments, its meant to be an example.

PDO/SQLite By Example

 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
    "http://www.w3.org/TR/html4/loose.dtd">
 
<html>
 
<head>
<title>Learning PDO/Sqlite 
For Those Who Know MySQL by Example</title>
<link rel="stylesheet" href="style.css" >
<meta http-equiv="content-type" 
      content="text/html; 
      charset=utf-8">
</head>
<body>
 
 
<?php
   echo "<" . "pre>"; 
 
 
// Creating the PDO object. This will attempt to open an sqlite3
// database or if it doesn't exist, create it. If you are just now
// creating the database make sure the 'db' folder is writable by the
// user Apache is wrong as.
$pdo=new PDO("sqlite:db/site.sqlite");
 
// When pdo/sqlite encounter an error we throw and exception
$pdo->setAttribute(PDO::ATTR_ERRMODE,
		   PDO::ERRMODE_EXCEPTION);
 
// Create a table of products with the sku number, name and
// price. Note there is no need to create the "id INT(11) PRIMARY KEY
// AUTO_INCREMENT" because SQLite auto matically creates one for you
// called "ROWID"
$sql="
CREATE TABLE IF NOT EXISTS products (
 
 -- Create a char variable called name, which should be 10 chars long,
 -- however sqlite will not truncate the char if you try to insert
 -- something longer. So the (1) is basically just a comment.
 `name` CHAR(10) NOT NULL,
 
 -- create a unique key called sku_number, which when a conflict
 -- happens we FAIL. FAIL just means: throw an exception, but keep the
 -- rest of the transaction in tact.
 `sku_number` INT UNIQUE NOT NULL ON CONFLICT FAIL,
 
 
 -- SQLite3 Will, let you put strings in INT columns, if it can't
 -- convert the string to an int in a resonable fashion.
 `price` INT
 
)
";
// Run the SQL statment to create the table
$pdo->query($sql);
 
 
// Create an index to speed up the retrival of products via the
// sku_number
$sql="
CREATE UNIQUE INDEX IF NOT EXISTS sku_index 
ON products(sku_number)
";
$pdo->query($sql);
 
 
// create an array to hold some to insert into are database.
$products=
  Array(
   Array(":sku_number"=>123,
	 ":name"=>"stove",
	 ":price"=>500),
 
   Array(":sku_number"=>456,
	 ":name"=>"lamp",
	 ":price"=>"30"),
 
 
   Array(":sku_number"=>100,
	 ":name"=>"sink",
	 ":price"=>300),
 
   Array(":sku_number"=>789,
	 ":name"=>"toaster",
	 ":price"=>"35 dollars after rebate"));
 
 
// prepare an insert statement, the ":sku_number" type strings will
// replaced with the appropriate value above. PDO does all the
// proper escaping.
$sql="
INSERT INTO products
 (sku_number,name,price) 
  VALUES
 (:sku_number,:name,:price)
";
 
// run the perpare statment and get the $statment object.
$statment=$pdo->prepare($sql);
 
 
// When inserting many rows, its a very good idea to the executes them
// in a beginTransaction() and commit() block, this will speed up the
// running of many inserts greatly.
$pdo->beginTransaction();
 
 
// Iterate over each of the products as returned by the $statment object 
foreach($products as $product){
  // ... executing the prepaired statment for each product.
  try{
    $statment->execute($product);
  }catch(Exception $e){
    // Note because we set `ON CONFLICT FAIL` for sku_number the
    // transaction will continue. See
    // /sqlite-3_6_16-docs/conflict.html for alternatives to FAIL,
    // such as IGNORE, REPLACE, ROLLBACK etc...
 
    if($e->getCode() == "23000"){
      // If you wanted you could simulate MySQL's "INSERT ... ON
      // DUPLICATE KEY ..." by running some other SQL commands
      // here. The lack of "ON DUPLICATE KEY" is the feature of MySQL
      // i miss the most in SQLite3.
    }
  }
}
 
// Now end the commit block, which writes the results to the disk
$pdo->commit(); 
 
// now just select all the items in the table to see what we have.
$sql="SELECT * FROM products ";
$result=$pdo->query($sql);
 
echo "<h2>All The Products</h2>";
while($row=$result->fetch(PDO::FETCH_ASSOC)){
  var_dump($row);
}
/*
This will Print:
 
array(3) {
  ["name"]=>
  string(5) "stove"
  ["sku_number"]=>
  string(3) "123"
  ["price"]=>
  string(3) "500"
}
array(3) {
  ["name"]=>
  string(4) "lamp"
  ["sku_number"]=>
  string(3) "456"
  ["price"]=>
  string(2) "30"
}
array(3) {
  ["name"]=>
  string(4) "sink"
  ["sku_number"]=>
  string(3) "100"
  ["price"]=>
  string(3) "300"
}
array(3) {
  ["name"]=>
  string(7) "toaster"
  ["sku_number"]=>
  string(3) "789"
  ["price"]=>
  string(23) "35 dollars after rebate"
}
 
*/
 
 
$sql="
SELECT * FROM products
 WHERE
  ROWID='1';
";
$result=$pdo->query($sql);
$row=$result->fetch(PDO::FETCH_ASSOC);
 
echo "<h2>Selecting row with ROWID '1'</h2>";
var_dump($row);
/* 
This will print out:
array(3) {
  ["name"]=>
  string(5) "stove"
  ["sku_number"]=>
  string(3) "123"
  ["price"]=>
  string(3) "500"
}
 */
 
 
echo "<h3>Updating price to 300 on product with sku 123</h3>";
$sql="
UPDATE products SET
 price='300'
  WHERE
 sku_number='123'
";
$pdo->query($sql);
 
 
echo "<h3>Deleting all products with price less than 200</h3>";
$sql="
DELETE FROM products
 WHERE
  sku_number > 200
";
$pdo->query($sql);
 
$sql="SELECT ROWID,* FROM products";
$result=$pdo->query($sql);
 
echo "<h2>All The Products Now, Showing ROWID.</h2>";
while($row=$result->fetch(PDO::FETCH_ASSOC)){
  var_dump($row);
}
/* 
This will print out:
 
array(3) {
  ["name"]=>
  string(5) "stove"
  ["sku_number"]=>
  string(3) "123"
  ["price"]=>
  string(3) "300"
}
array(3) {
  ["name"]=>
  string(4) "sink"
  ["sku_number"]=>
  string(3) "100"
  ["price"]=>
  string(3) "300"
}
 
*/
 
 
// SQLite doesn't have foreign key constrants by default but its easy
// enought to simulate with SQL Triggers.
$sql="
CREATE TABLE IF NOT EXISTS sales (
 
-- create a product id column, and check to see that we only insert
-- integers. You can put other types of checks as well.
 
product_id INT
 CHECK(typeof(product_id) = 'integer'),
 
 
sell_date DATETIME
)
";
$pdo->query($sql);
 
// Now we try to insert a product id which is not an int
$sql="
INSERT INTO sales
 (product_id,sell_date)
VALUES
 ('problem',DATETIME('NOW'))
";
// This throw an exception because, because 50000 is not a valid
// product id.
try{
  $pdo->query($sql);
}catch(Exception $e){
  // looks like we have a key constraint problem
  echo "product id not a int " .$e->getMessage() . "\n";
}
 
 
// If you are a bit new to creating TRIGGERS than this might look a
// little strange, but the pattern is always the same for a doing a
// foreign key constraint, so you can just wrap this in a function or
// copy/paste/modify.
$sql="
CREATE TRIGGER IF NOT EXISTS 
 sales_insert 
BEFORE INSERT ON
 sales
BEGIN
 SELECT CASE
  WHEN 
   (SELECT ROWID FROM products WHERE ROWID=NEW.product_id) IS NULL
  THEN 
   RAISE(ABORT,
         'Foreign Key Violation: product_id is not in products.ROWID')
 END;
END;
";
$pdo->query($sql);
 
 
// This is just to check to see if its safe to delete the key.
$sql="
CREATE TRIGGER IF NOT EXISTS 
 sales_delete
BEFORE DELETE ON
  products
BEGIN
 SELECT CASE
  WHEN
   (SELECT COUNT(ROWID) FROM sales WHERE product_id=OLD.ROWID) > 0
  THEN
   RAISE(ABORT,
         'Foreign Key Violation: product refrences sales')
 END;
END;
";
$pdo->query($sql);
 
 
// *** NOTE *** there is a bug in PDO which quotes integers, in SQLite
// *** quoted integers are NOT integers, they are strings, so using
// *** prepared statments will not do what we
// *** want. php.net/bug.php?id=49268
$sql="
INSERT INTO sales
 (product_id,
  sell_date)
  VALUES
 (1,
  DATE('NOW'))
";
// no error is thrown
$pdo->query($sql);
 
// this will throw an exception, because 5484 isn't a valid ROWID in
// products.
$sql="
INSERT INTO sales
 (product_id,
  sell_date)
  VALUES
 (5484,
  DATE('NOW'))
";
try{
  $pdo->query($sql);
}catch(PDOException $e){
  // i will get run, because 5484 isn't valid
  echo "bad product id " . $e->getMessage() . "\n";
}
 
$sql="
DELETE FROM products 
 WHERE
  sku_number='123'
";
// this will throw an exception, because i inserted a sale for a
// product with sku_number=123 above. Specifically product_id=1 has
// sku_number '123'
try{
  $pdo->query($sql); 
}catch(Exception $e){
  echo "cant delete sku_number 123 because we have sales " . 
    $e->getMessage() . "\n";
}
 
echo "<" . "/pre>"; 
 
// just delete the database because we are done with it.
unlink('db/site.sqlite');
?>
 
 
</body>
</html>

You can read most (all?) of the The definitive guide to SQLite By Mike Owens, Michael Owens on google books. The chapter entitled SQL is the most useful for our purposes.

  • Share/Bookmark
This entry was posted in intermediate.programming and tagged , , , , , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading ... Loading ...

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="">

If you are going to post code please use:
<pre lang="php" escaped="true"> YOUR_CODE_HERE </pre>

Change the lang to mysql, python, lisp, whatever. This will escape your code.