DX: Take advantage of the return key, readable queries

Yes, that is a good example and something I also do.

If we can a few more solid opinions, lets start on an official standard.

So lets just do queries for now.

I find it a bit odd that we write all DBTNG queries on multiple lines and leave db_query()'s on one MASSIVE line.

For example:

$result = db_query("SELECT column_name, data_type, column_default FROM information_schema.columns WHERE table_schema = :schema AND table_name = :table AND (data_type = 'bytea' OR (numeric_precision IS NOT NULL AND column_default LIKE :default))", array(':schema' => $schema, ':table' => $table_name, ':default' => '%nextval%'));

and

$action = db_query("SELECT callback, parameters FROM {actions} WHERE aid = :aid", array(':aid' => $action_ids))->fetchObject();

Compared with:

  $result = db_select('users', 'u')
    ->fields('u', array('uid', 'data'))
    ->condition('uid', $sandbox['last_user_processed'], '>')
    ->where('data IS NOT NULL')
    ->range(0, $limit)
    ->execute();

Those are just plain unreadable...and I have found MUCH MUCH worse in contrib. I literally have to copy them into a seperate text document and format them on separate lines so I can read them.

Just as we don't put multiple statements on the same line...even though we can...lets not do it with SQL.

I propose the following rules (as the core of this, I'm sure we'll find edge cases or weird things):

1. If query is made up of two parts it can stay on one line.
2. If query is made up of more than two parts each part must be placed on a separate line.

Taking that into account the above queries become (readable):

$result = db_query("SELECT column_name, data_type, column_default
                    FROM information_schema.columns 
                    WHERE table_schema = :schema AND table_name = :table
                    AND (
                      data_type = 'bytea'
                      OR (numeric_precision IS NOT NULL AND column_default LIKE :default)
                    )", array(':schema' => $schema, ':table' => $table_name, ':default' => '%nextval%'));

and

$action = db_query("SELECT callback, parameters
                    FROM {actions}
                    WHERE aid = :aid", array(':aid' => $action_ids))->fetchObject();

The parenthesis are formatted just like array definitions and the nested (... AND ...) is left on one line like we do if conditions. We can add more rules if we like...but that is the basic premise.

I have no qualms about 80 characters...we execeede it ALL OVER.

I think where clauses on one line is fine with where clauses on one line as long as they are logical. Same reasoning behind large if conditions.

SELECT foo
FROM bar
WHERE status = 1
AND uid = 3

would also be fine and:

SELECT foo
FROM bar
WHERE status = 1
AND uid = 3
AND more = 5

Any yes, as I wrote, lets keep this to SQL.

This issue is trying to change that and states the reasons for it.

To clarify...I'm not comparing DBTNG queries to static ones...I would have to agree that static are more clear. This is comparing static one line queries very multiline. SQL is a language just like PHP and it provides a syntax of its own and can be formatted...

Seems like in early comments I have support and now later comments are against this...so seems somewhat 50/50.

I don't think there is anyway to argue that a single line query is easier to read...given science..and general experience. If we decide that we don't want to make a standard for this is one thing.

The SQL example is great...I can quickly find the WHERE clause and everything.

I gave a nice, more complex example in #3:

$result = db_query("SELECT column_name, data_type, column_default FROM information_schema.columns WHERE table_schema = :schema AND table_name = :table AND (data_type = 'bytea' OR (numeric_precision IS NOT NULL AND column_default LIKE :default))", array(':schema' => $schema, ':table' => $table_name, ':default' => '%nextval%'));

vs

$result = db_query("SELECT column_name, data_type, column_default
                    FROM information_schema.columns
                    WHERE table_schema = :schema AND table_name = :table
                    AND (
                      data_type = 'bytea'
                      OR (numeric_precision IS NOT NULL AND column_default LIKE :default)
                    )", array(':schema' => $schema, ':table' => $table_name, ':default' => '%nextval%'));

The first is nice to read like a sentence, but good luck debugging or editing it.

I can provide tons of examples...essentially and sql query that is more then SELECT foo FROM bar WHERE 1 = 2...(even that) just gets hard to read/edit/and understand.

My personal favorite:

(SELECT pd.ftid, pd.cid, n.nid AS issue_id, n.title AS issue_title, n.uid, pi.pid, pi.rid, f.filepath AS patch_url, p.title AS project, u.name AS submitter$select FROM {pift_data} pd INNER JOIN {project_issues} pi ON pd.nid = pi.nid INNER JOIN {files} f ON pd.fid = f.fid INNER JOIN {node} n ON n.nid = pi.nid INNER JOIN {node} p ON p.nid = pi.pid INNER JOIN {users} u ON u.uid = pd.uid$join WHERE ((pd.status = %d AND pd.timestamp < %d) OR (pd.status = %d AND pd.timestamp < %d)) AND pd.cid = %d$where) UNION (SELECT pd.ftid, pd.cid, n.nid AS issue_id, n.title AS issue_title, n.uid, pi.pid, pi.rid, f.filepath AS patch_url, p.title AS project, u.name AS submitter$select FROM {pift_data} pd INNER JOIN {project_issues} pi ON pd.nid = pi.nid INNER JOIN {files} f ON pd.fid = f.fid INNER JOIN {node} n ON n.nid = pi.nid INNER JOIN {node} p ON p.nid = pi.pid INNER JOIN {users} u ON u.uid = pd.uid$join WHERE ((pd.status = %d AND pd.timestamp < %d) OR (pd.status = %d AND pd.timestamp < %d)) AND pd.cid <> %d$where) ORDER BY ftid

I had to place in external editor and format it just to see what the .... was going on. The following is a more complex query that is format...not the one above as I don't have a formatted copy:

UPDATE {pift_test}
SET status = %d
WHERE type = %d
AND id IN (
  SELECT f.fid
  FROM {files} f
  LEFT JOIN {upload} u
    ON f.fid = u.fid
  LEFT JOIN {comment_upload} cu
    ON f.fid = cu.fid

  JOIN {project_issues} pi
    ON (u.nid = pi.nid OR cu.nid = pi.nid)
  JOIN {pift_project} p
    ON pi.pid = p.pid
  JOIN {project_release_nodes} r
    ON pi.rid = r.nid

  WHERE pi.sid IN (" . implode(',', array_fill(0, count($sids), '%d')) . ")
  AND r.tag REGEXP '%s'
)
AND status > %d
AND last_tested < %d

It seems that I need to demonstrate the extreme to get my point across. Same reason books are written on pages and WRAP the text. Same reason web sites are designed with a hard width instead of fluid...please read easer when in a column.

m = 2
x = 1
y = mx + b

you do math vertically you don't but that on one line.

Notice the MySQL example: http://dev.mysql.com/doc/refman/5.0/en/select.html

Although you may never see these extreme examples in core...contrib has them...and we should set a standard.

To compare with code, which we separate into "statements" just like I want to to do with SQL statements.

$i = 0;
if ($i < 2) {
echo 'foo';
}

instead of:

$i = 0; if ($i < 2) { echo 'foo'; }

which of course reads like English: i is 0 which is less then 2 so print the word foo.

Doesn't mean we write it like that.

So when I come to help contribute to a contrib module...I don't need to spend time formating a query so I can read it....

We have a difference of opinion that has no 100% answer...I've made my case...I have nothing more to say.

Instead of letting this rot in the issue queue like the other 100s of issues...lets won't fix this since it won't happen.