db_query_ex() - Future of abstract database querying?

Drupal querying mechanism is based on:

• having a query with placeholders that will be replaced by values sanitized through db_escape_string()
• tables inside {} will be processed for prefixing
• if we want to use ranges, will need to use db_query_range()
• if we want to rewrite a node query we will use db_rewrite_sql()
• the system always tries to parse the query to inject things like "limits"

Kind of queries that can be done

Dummy queries

Usually found in the form of "SELECT * FROM table", and we know what this does to the application performance.

If a guy, avoids to write fields he wants to retrieve, it is almost sure that he will *NOT* sanitize is parameters and instead of writing SELECT field1, field2 FROM table WHERE field3 = '%s' he will write SELECT * FROM table WHERE field3 = '$field3'

node.module

Writing the fields is so boring, but when we got to develop a new node type we have no way to avoid it: we really have to write the necessary SQL for SELECT, UPDATE, INSERT and DELETE; and, with sanitized fields! By the way, do you ever had to write node types with more than 10 fields?!

Before Drupal 4.7, even the node.module implemented a strange node_save() that did not obey to fields types and only used sanitized strings.
Now, yes, it uses the correct placeholders to replace with the correct data.

Did you ever take a look at the code on node_save()? Yes, we could have less code.

Probably it would be much more simple just do a:

  $fields = array(... defined fields ...);
  db_query_ex_fillfields($fields, $node);
  db_query_ex($fields, 'node', 'insert');

db_query_ex()

This function appears to help the construction of SQL for node API (load, delete, insert and update), with the potential to increase validation and communication between form API and database.

Things that this actually do:

• Define name, type, key, required for fields
• Constructs SQL for operations: SELECT, INSERT, UPDATE, DELETE
• Uses the 'key' definition for modification operations
• Uses 'value' information for retrieving operation
• Correctly uses types: string, int, float and bool
• Helper function to transform mysql table structure in Drupal fields definition
• Reduces the code (improving readability and security) in about 50%
• Non required fields can be update to NULL in the database

What can be implemented:

• Logic for multi-joins at the $tables parameter with aliases and join types
• Parameter for limits, group, order
• Logic for expressions at 'value' field definition
• Connection with FAPI, at the validation level, where FAPI can use the fields definition to better understand how controls should behave and validate
• Add 'date' type (based on 'string') but with knowledge by the logic
• Implement non-explicit array fields definition (which would reduce much more the code size)
• Add 'default' definition (at least for required fields)
• Full database abstraction

Definition

  $fields = array of $field;
  $field = array of
    'type' (string|int|float|bool)
    'key' (is primary key)
    'required' (field is required)
    'value' (value to be used for filter or data modification)

  $tables = table name or array of tables

  $op = (select|insert|update|delete)

This would do a select ($fields array should be re-used over and over again):

$fields = array(
  'nid' => array('type' => 'int', 'key' => TRUE, 'required' => TRUE),
  'vid' => array('type' => 'int', 'required' => TRUE),
  'title' => array('type' => 'string', 'required' => TRUE),
  'type' => array('type' => 'string', 'required' => TRUE),
  'uid' => array('type' => 'int', 'required' => TRUE),
  'status' => array('type' => 'int', 'required' => TRUE),
  'created' => array('type' => 'int', 'required' => TRUE),
  'changed' => array('type' => 'int', 'required' => TRUE),
  'comment' => array('type' => 'int', 'required' => TRUE),
  'promote' => array('type' => 'int', 'required' => TRUE),
  'moderate' => array('type' => 'int', 'required' => TRUE),
  'sticky' => array('type' => 'int', 'required' => TRUE)
);

$fields['nid']['value'] = 1;
db_query_ex($fields, 'node', 'select');

How about an insert:

  $fields = _get_fields_definition();
  db_query_ex_fillfields($fields, $node);
  db_query_ex($fields, 'my_table', 'insert');

Hope you like this technology preview!