Databases: the Next Generation

(notes from a session at DrupalCon 2010)

Larry Garfield: Databases: the Next Generation!

  • limitations of current system due to PHP 4, database-specific functions
  • PHP5 has PDO database abstraction layer built in.
  • D7 is fully based on PDO, object-oriented, with db-specific optimizations handle automatically.
  • new $databases array specifies all database characteristics, [database][target]
    allows master/slave replication out of the box, just specify another target
  • types of queries: static, dynamic, insert, update, delete, merge, truncate
  • static queries (majority) don't change based on environment, take named placeholders. instead of %s, %d, use :type, :uid -- keyed array, so order of placeholders in array doesn't matter. 
    • SQL injection becomes impossible because full query string never actually exists as text -- PDO compiles the structure and passes in the parameters.
    • auto-expanding placeholders: if a placeholder is an array, it gets automatically exploded with commas.
    • now use foreach instead of while to iterate through result set
    • to request data from a slave database, just specify the target in the db_query call.  Slave could be 2-3 minutes old.  If there is no slave, the default will be used instead, automatically.
    • fetch options: object by default; associative array, numeric, or both, custom class.
    • alternatively, use $result->fetchObject(), fetchAssoc(), fetchAll(), fetchAllAssoc('nid'), or even fetchCol() to get just a single column!  Can even call these on the db_query call itself, in one step!
  • dynamic queries, using db_select() and db_execute() -- only use if you need dynamic behavior (i.e. allowing other modules to change query structure)
    • hook_query_alter() allows modules to change query structure without bothering with text
    • introduces additional overhead compared to static queries.
    • add joins to a query by calling $query->join()
    • can add parameters to the query in the execution stage to keep the same base prepared query.
    • can extend a prepared query with a pager, etc. before execution
  • there is no way to get the actual database query out for debugging purposes, unless you consult the logs.
  • insert, update, and delete always call a query builder due to database differences.
    • simply pass the fields as an array.  Multiple rows at once!
    • can insert from $query
    • update same as insert, just specify a condition
  • merge queries are weird! but fun! insert if record doesn't exist, update if it does.
    • just set the key and fields, execute, and it just works.
    • can also auto-increment when the record exists, instead of updating.
  • transactions - D7 adds its own wrapper around PDO's wrappers
    • use try & catch, with $transaction->rollback in the catch block.
  • slides will be available on session page.
Taxonomy upgrade extras: