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