- Status Assigned
- Percent Complete
- Task Type Feature Request
- Category Backend / Core
-
Assigned To
pizza - Operating System All
- Severity Low
- Priority Very Low
- Reported Version Devel
- Due in Version Undecided
-
Due Date
Undecided
- Votes
- Private
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.
Loading...
Available keyboard shortcuts
- Alt + ⇧ Shift + l Login Dialog / Logout
- Alt + ⇧ Shift + a Add new task
- Alt + ⇧ Shift + m My searches
- Alt + ⇧ Shift + t focus taskid search
Tasklist
- o open selected task
- j move cursor down
- k move cursor up
Task Details
- n Next task
- p Previous task
- Alt + ⇧ Shift + e ↵ Enter Edit this task
- Alt + ⇧ Shift + w watch task
- Alt + ⇧ Shift + y Close Task
Task Editing
- Alt + ⇧ Shift + s save task
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.
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..
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
It's 2.35-fodder now. Simply too invasive on top of the other changes.
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.
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.