Photo Organizer

This is the bug tracker for Photo Organizer.
Tasklist

FS#11 - Clean up database conventions

Attached to Project: Photo Organizer
Opened by Solomon Peachy (pizza) - Tuesday, 25 April 2006, 12:29 GMT-4
Last edited by Solomon Peachy (pizza) - Monday, 26 March 2007, 23:41 GMT-4
Task Type Feature Request
Category Backend / Core
Status Assigned
Assigned To Solomon Peachy (pizza)
Operating System All
Severity Low
Priority Normal
Reported Version Devel
Due in Version Undecided
Due Date Undecided
Percent Complete 0%
Votes 0
Private No

Details

Most database results are being indexed by position in a fixed array, rather than by column name. This may be slightly faster, but is hell to program around, especially when there can be twenty fields being returned.

* Convert all fetch_row() calls to fetch_assoc()

Secondly, using prepared statements would make the code a lot cleaner and eliminate the need to escape strings being passed in to the database. Unfortunately, this may require PHP 5.1 to pull off, depending on the maturity of the postgres bindings for earlier versions.

* Use prepared statements and SQL variables instead of inlined data.
This task depends upon

Comment by Solomon Peachy (pizza) - Thursday, 15 June 2006, 11:27 GMT-4
If we want to use prepared statements or lazy bindings, we'll need PHP 5.1.0 or newer to do so with postgres. This is... embarassing. I was doing this with Perl's Postgres bindings seven years ago, and it wasn't new then.

Instead, it would be smarter to switch to a DB abstraction layer, such as the PEAR::MDB2 package, and use their "prepared statements" API. Theoretically, this way it'll just start working when the DB mid-layer supports it, and it also theoretically allows us to use other databases as well.

This is going to be pretty invasive, and not something that can be easily ported over.
Comment by Solomon Peachy (pizza) - Thursday, 05 October 2006, 20:06 GMT-4
Or just use PHP's PDO extension. This is available with PHP5, so it should just work.

In fact, I think I'll try this..
Comment by Solomon Peachy (pizza) - Sunday, 12 November 2006, 11:22 GMT-4
I'm thinking this would be a good candidate for v2.34. It has zero overall impact on functionality, but it obviously affects every single SQL call. Lots of drudgery is involved here!

PHP:PDO would mean PO will require PHP5. Currently we only strictly require PHP4. (Not really a problem, but our dentist

PHP:PDO automagically escapes everything as necessary; although I think it'll have to emulate the prepared statements on <PHP5.1
Comment by Solomon Peachy (pizza) - Monday, 26 March 2007, 23:41 GMT-4
  • Field changed: Due in Version (2.34 → Undecided)
It's 2.35-fodder now. Simply too invasive on top of the other changes.
Comment by Solomon Peachy (pizza) - Monday, 18 February 2008, 19:03 GMT-4
Okay, here's an update as to what's going on. It's now PO 2.36+ fodder. We'll be using PHP:PDO, and will require PHP 5.1+.

1) Rename all of the 'core' functions that use the database to from 'X' to 'X_DBOLD' (and rename all calls) too.
2) Write new versions of the absolute core functions -- pg_dbconnect(), site_prolog(), site_epilog(), site_update_session(), refresh_user_prefs(), and probably a few others. All 'old' functions use '$database' as the handle; use '$dbh' to refer to the new handle to ease the use of finding stragglers.
3) Start porting the pages one by one. When library functions need to be ported, port them (and make sure you get all callers). Don't forget the installer!

Once started, this has to be completed before we can release. *every* query has to be updated (about 700). The simplest conversion consists of API differences, but I'd like to use prepared statements too, if possible.

Loading...