Welcome to the MacNN Forums.

darcybaston · Mar 15, 2006, 10:14 PM

I wanted to learn more about SQLite and decided to use my Yojimbo database as a test subject. I've got data coming out of it and displaying to a web browser now. Just some early experiments:

Code:
<?php 
	################################################################
	# Just having fun studying SQLite using a Yojimbo Database
	# March 15, 2006 - Do what you want with this
	#
	# Darcy Baston
	#
	# What you need:
	#    PHP 5.1.2 (Entropy-PHP-5.1.2-1.dmg works great)
	#    OS X 10.4.x (has sqlite3 already - http://www.sqlite.org)
	#    Source database in SQLite 2.x or 3 format
	#    Personal Web Sharing needs to be turned *on*
	#
	# PHP has native sqlite_function() stuff for SQLite2 databases,
	# but you need to use the PDO interface to get to version 3
	# DBs, which Yojimbo uses. Otherwise you'll get "encrypted
	# database" errors.
	#
	# Any DB you try to access should have read/write permissions
	# and I think for PDO stuff, the containing folder does too. I'm
	# not yet able to connect to the Yojimbo database where it sits
	# in Application Support, nor do I really want to as its in
	# production. I suggest you copy or create a new dbase and put it
	# in your Sites folder or whatever.
	#################################################################
	# This doesn't do much except display a handful of information
	# related to items in a Yojimbo database library. I may learn
	# more if I have time. (I do MySQL/PHP professionally.)
	#################################################################
	

	/*
		############################
		# Z_ITEM data you can pull #
		############################
		Z_ENT				INTEGER
		Z_PK				INTEGER				PRIMARY				KEY
		Z_OPT				INTEGER
		ZDATETRASHED		TIMESTAMP
		ZENCRYPTED			INTEGER
		ZDATECREATED		TIMESTAMP
		ZDATEMODIFIED		TIMESTAMP
		ZFLAGGED			INTEGER
		ZKIND				INTEGER
		ZCOMMENTS			VARCHAR
		ZNAME				VARCHAR
		ZUUID				VARCHAR
		ZINTRASH			INTEGER
		ZLABEL				INTEGER
		ZBLOB				INTEGER
		ZACCOUNT			VARCHAR
		ZLOCATION			VARCHAR
		ZCIPHER				VARCHAR
		ZENCRYPTEDPASSWORD	BLOB
		ZENCRYPTIONKEY		INTEGER
		ZBLOB1				INTEGER
		ZOWNERNAME			VARCHAR
		ZORGANIZATION		VARCHAR
		ZACTIVATIONKEY		VARCHAR
		ZSERIALNUMBER		VARCHAR
		ZOWNEREMAIL			VARCHAR
		ZSOURCEURLSTRING	VARCHAR
		ZBLOB2				INTEGER
		ZTRASHAFTEROPENING	INTEGER
		ZOPENEDCOUNT		INTEGER
		ZLASTOPENEDDATE		TIMESTAMP
		ZURLSTRING			VARCHAR
	
	*/
	// ##############
	// # HTML intro #
	// ##############
	echo "<html><head><title>SQLite Fun</title></head><body>";
	echo "<table border=0 cellpadding=5 cellspacing=0 width=100%>";
	
	// *cough* try something *cough*
	try {
	 // connect to DB and get a "handle" called $dbh
	 $dbh = new PDO("sqlite:/Users/darcy/Sites/Database.sqlite"); 
	 
	 // Build query (implicit left join between ZITEM and Z_PRIMARYKEY)
	 $query = "	SELECT 	ZITEM.ZNAME, 
	 					ZITEM.Z_ENT, 
	 					ZITEM.ZDATECREATED,
	 					ZITEM.ZDATEMODIFIED,
	 					ZITEM.ZCOMMENTS,
	 					ZITEM.ZSERIALNUMBER,
	 					Z_PRIMARYKEY.Z_NAME 
	 					FROM ZITEM, Z_PRIMARYKEY 
	 					WHERE ZITEM.Z_ENT = Z_PRIMARYKEY.Z_ENT
	 					limit 10 /* take this 'limit 10' out to see all items /*";
	 
	 // Cycle through query results, the query being triggered by $dbh->query($query)
	 foreach ($dbh->query($query) as $row) {
		$ZNAME 			= $row['ZNAME'];
		$Z_NAME 		= $row['Z_NAME'];
		$ZKIND 			= $row['Z_PK'];
		$ZDATECREATED 	= $row['ZDATECREATED'];
		$ZCOMMENTS 		= $row['ZCOMMENTS'];
		$ZDATEMODIFIED 	= $row['ZDATEMODIFIED'];
		$ZSERIALNUMBER 	= $row['ZSERIALNUMBER'];
	  
		// Display record fields
		echo "	<tr>
				<td align=right><b>Date</b></td>
				<td align=left>$ZDATECREATED ($ZDATEMODIFIED)</td>
				</tr>
				<tr>
				<td align=right><b>Kind</b></td>
				<td align=left>$Z_NAME</td>
				</tr>
				<tr>
				<td align=right><b>Name</b></td>
				<td align=left>$ZNAME</td>
				</tr>";
		
		// One way of checking, "don't display a field unless it has something to display."
		if(isset($ZSERIALNUMBER))
			echo "	<tr>
					<td align=right><b>Serial #</b></td>
					<td align=left>$ZSERIALNUMBER</td>
					</tr>";
		if(isset($ZCOMMENTS))
			echo "	<tr>
					<td align=right><b>Comments</b></td>
					<td align=left>$ZCOMMENTS</td>
					</tr>";
		echo "<tr style='background: #000000'><td>&nbsp;</td><td>&nbsp;</td></tr>";
	 }
	 $dbh = null;
	
	// check for a failed connection and display an error if so
	} catch (PDOException $e) {
	 print "Error!: " . $e->getMessage() . "<br/>";
	 die();
	}

	echo "</table>";

	echo "</body></html>";
?>

Yuck, I lost all my indentations.

Love Calm Quiet · Mar 31, 2006, 10:32 AM

I've been loathe to invest time in another DB now that I'm getting comfortable w/ MySQL.

I'm wondering what you experience as benefits of SQLite?
How "lite" is it? Does everything MySQL does?

darcybaston · Mar 31, 2006, 09:46 PM

SQLite and MySQL are totally different.

SQLite is a library/api of functions an application can use to manage data in database form, without the user requiring a database "server".

MySQL is a server/client approach where a server must be running and a client/api interface used to tap into it.