SQLite support

Doug

I would like to see support for SQLite added to ZenPhoto. While the initial modification would take some work, I think it would be easy enough to maintain after that.

I'm already working on this myself and will share the code here once I get it stable enough and test on a couple platforms. I'd really appreciate some input on this project and testers once I get some code ready! Let me know your thoughts.

Doug

My favorite question to ask when starting a new project is "What is the problem for which this is a solution?" So in that spirit, here are some of the benefits I see for adding SQLite support and some of the problems it might solve:

• Options
  Yes, MySQL is just about everywhere, but a) it's not that easy to use or set up, b) some hosts put limitations on, for example, the number of databases one can create, and c) some people might prefer other databases and it's good to have options. Plus, once the first step is taken, it would be easier to add support for additional databases, e.g., PostgreSQL.
• Portability
  Moving your site to a new webhost? Which scenario sounds preferable?
  
  MySQL:
  1. Copy ZenPhoto along with all your photos to the new webhost server.
  2. Log in to old webhost admin and find phpMyAdmin.
  3. Select the database, select Export, Select All tables, Save as file, set compression?, do I need to change any of the SQL options?, cross fingers and click Go.
  4. Find SQL file.
  5. Log in to new webhost and open phpMyAdmin.
  6. Create new database, user, password.
  7. Assign user to database with appropriate privileges.
  8. Select database, select SQL... Browse for file, cross fingers, and click Go.
  9. Edit `zp_config.php` on the new server and enter new database info.
  or SQLite:
  1. Copy ZenPhoto along with all your photos to the new webhost server.
• Braggability?
  All the cool kids are using SQLite (or they will be after they see ZenPhoto using it!).
• Performance?
  No idea which is faster in this situation, but I plan to test this. Of course, it may depend on your setup.
• Ease of installation
  This is my favorite. Assuming SQLite works on your server, ZenPhoto can be installed with virtually no database setup. No MySQL passwords, no need to edit `zp_config.php`, No `setup.php`. A ready-to-go database can be included with the ZenPhoto download. Simply unzip the program to your webhost server and start adding photos!

Of course there are disadvantages too, the main one being availability. I really don't know what percentages of webhosts have SQLite installed. But if it's not, MySQL support would still be available (a single pref in `zp_config.php`).

Doug

Wow, four months can really speed by if you're not paying attention!

As usual, this project was both much easier and much harder than I expected. I had a working version of ZenPhoto using SQLite back in July, but one or two bugs prevented the program from working in both SQLite and MySQL at the same time. I had other projects to work on for a while, but finally got back to it and fixed that bug, so now I have a version that works in either MySQL or SQLite based on a preference in the `zp-config.php` file. The new pref is `$conf['sqlite_used'] = true;`. When set to false, the program uses MySQL. (So upgraders could continue to use their existing MySQL databases simply by not setting this pref.) Because SQLite doesn't require usernames or passwords, no other prefs are needed.

Here are the three main steps I took to add SQLite compatibility to the code:

Generic db functions

The database functions required a simple fork based on the above pref. The file `functions-db.php` now looks like this:

`require_once("functions.php");

$db_connection = null;

if (zp_conf('sqlite_used'))

require_once("functions-db-sqlite.php");

else

require_once("functions-db-mysql.php");

db_connect();

`

The fork calls one of two new files depending on whether MySQL or SQLite is preferred. `functions-db-mysql.php` looks pretty much like functions-db.php used to look with a few additional functions. `functions-db-sqlite.php` contains the same functions translated to SQLite. For example, this function in `functions-db-mysql.php`:

`function query_single_row($sql) {

$result = query($sql);

return mysql_fetch_assoc($result);

}

`

became this in `functions-db-sqlite.php`:

`function query_single_row($sql) {

$result = query($sql);

return sqlite_fetch_array($result,SQLITE_ASSOC);

}

`

There were a few scattered places in other files that had mysql-specific functions, especially `mysql_real_escape_string()`. These had to be converted to generic functions, too, and added to the `functions-db-*` files.

SQL syntax

Being a newbie to SQL, this took a little more effort. SQLite seems to have a more limited syntax than MySQL. Because of this, just about every SQL statement in ZenPhoto had to be modified at least a little. The good news is, the modified SQL statements work equally well in either MySQL or SQLite, with one exception:

The big bug

ZenPhoto uses backticks in most of its SQL statements. Backticks are non-standard in SQL and therefore not supported in SQLite (or many other database programs). Removing them allows the SQL statements to work in both databases in all cases except two. In those two, keeping the backticks would generate an error in SQLite, but removing them would generate a different error in MySQL. This really drove me nuts for a while!

Here's what happened: The "albums" database has two fields called "desc" and "show". These are reserved keywords in MySQL: they can still be used in SQL statements if they are set off in backticks, but leaving them naked causes an error. The field names work fine as is in SQLite (as long as no backticks are used). The moral of this story is don't use reserved keywords as field names in your database.

The best solution would be to rename the fields, but seeing as there is already an installed base, I had to include a fork in the code for each of the SQL statements that refer to one of these fields. Definitely much less elegant this way, but what can you do?

Anyway, it works now. I would submit the code if the bug tracker were still working, but I can upload what I have somewhere as a zip file if anyone wants to play with it. And please let me know if you know of a good place I can store a zip file online for free.

trisweb

"Wow, four months can really speed by if you're not paying attention!" --- Absolutely! That happened to me in the worst way... if you didn't notice, 1.0.3 was released over 6 months ago or something like that. Not good.

In any case, you will be very interested to know that the latest version has a new persistence model, doing away with all the individual queries and reducing it to a generalized get-set system. That could make more work for you, but it should be easier in general!

Very very sorry about the reserved words... that was a very bad thing I know. There is no reason we can't change those fields in upcoming versions using the upgrade script, with a query like
`ALTER TABLE `images` CHANGE `desc` `description` TEXT NULL DEFAULT NULL;`
or something similar. We really should make that change.

Please check out the latest in the subalbums branch (where all current development is taking place) from here:
`http://dh.trisweb.com/websvn/dl.php?repname=zenphoto&path=/branches/subalbums/&rev=0&isdir=1`
I've got lots of extra time now so I've been working actively on it, and changes are made every day. If you want, keep in touch with me via email at trisweb at my domain (trisweb.com) and we can work on getting your changes integrated into the new persistence model, and maybe open it up to other databases as well.

Thanks for your work!