Photo Organizer

  • Status Assigned
  • Percent Complete
  • Task Type Feature Request
  • Category Backend / Core
  • Assigned To
  • Operating System All
  • Severity Low
  • Priority Very Low
  • Reported Version Devel
  • Due in Version Undecided
  • Due Date Undecided
  • Votes
  • Private
Attached to Project: Photo Organizer
Opened by pizza - 2006-04-25
Last edited by pizza - 2007-03-27

FS#11 - Clean up database conventions

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.

pizza commented on 2006-06-15 15:27

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.

pizza commented on 2006-10-06 00:06

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..

pizza commented on 2006-11-12 16:22

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

pizza commented on 2007-03-27 03:41

It's 2.35-fodder now. Simply too invasive on top of the other changes.

pizza commented on 2008-02-19 00:03

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.

pizza commented on 2008-10-03 13:17

I've been replacing pg_fetch_row with pg_fetch_assoc in more places. It also has the added benefit of letting us move to pg_query_all() which returns the whole set as an array of assoc arrays, so we can transparently cache the results behind the scenes.


Available keyboard shortcuts


Task Details

Task Editing