Nuts and Bolts - part 3

INSERT

The INSERT statement is composed by two elements, the attribute list and the values list. The lists are positional. The formatting should mark clearly the various elements of the query in order to make it simpler to spot the correspondence between the attribute and the value.

• After the words INSERT INTO there is one space followed by the table's name
• After the table's name there is one carriage return
• The attributes if present are enclosed between round brackets indented one tab from the word INSERT
• The attribute's list indents one tab from the round brackets
• The attributes indent one tab fro the round brackets and are separated by a carriage return
• The word VALUES indents at the same level of INSERT and is followed by a carriage return
• The value's list is surrounded by round brackets indented one tab from the word VALUE
• The values indent one tab fro the round brackets and are separated by a carriage return

UPDATE

The WHERE condition on the update is the same of the SELECT. Take a look to 1.1.1 for more informations. Just after the table's name there is the keyword SET and the list of the updates. The formatting should reflect this particular structure.

• After the table's name there is one carriage return
• The word SET indents one tab from the word UPDATE
• The update's list indents one tab from the word set
• Each update is separated by a carriage return 

DELETE

The delete is the simplest of the DML as lists just the target relation and the eventual WHERE condition. The formatting is quite simple as the where condition is the only part which requires to be structured as seen in the select.

Data Definition Language

The data definition language groups all the commands defining, altering or dropping the database's relations. Each command type follows a different syntax structure.

CREATE

The create statement is composed by the verb CREATE followed by the object type (e.g. TABLE) and the new relation's name followed by a carriage return. The rest of the statement indents one tab from the verb CREATE.
The rules for the CREATE TABLE are the following.

• The fields are indented one tab stop from the surrounding brackets
• Between the field's name and the type there is one or more tab stops
• The number of tabs is determined by the field with the longest name
• Between the field's type and the optional DEFAULT there is one space
• Between the the optional DEFAULT and the optional field's constraint there is one space
• If the DEFAULT is missing, between the field's type and the field's constraint there is one space
• Table constraints will require the constraint name followed by one carriage return
• The constraint type indents one tab from the word CONSTRAINT
• Any clause indents one tab from the constraint type

 

The rules for the CREATE VIEW are the following.

• After the view name there is one carriage return
• The word AS indents one tab from the verb CREATE
• The round brackets surrounding the view definition indents at the same level of the word AS
• The view's SELECT follows the same rules defined in the select.

ALTER

The alter statement's only rule is very simple, all the alter actions indent by one tab fromt he ALTER verb and are separated by a carriage return.

DROP

The drop statement's formatting is very simple. Everything is on a single line.

Nuts and Bolts - part 2

WITH statements

Because the WITH statement works like having a temporary table, the formatting will follow rules similar to the CREATE TABLE statement.

• Between the WITH and the alias there is a tab followed by the word AS and a carriage return
• The round brackets surrounding the inner query indent one tab from the alias
• The inner query indents one tab from the round brackets

Conclusions 

This coding style is at first sight complex and with some drawback as it can slow down the coding process because of more keystrokes required for the uppercase and the carriage return. However, when the writing automatism is acquired the coding speed is very fast.

This coding style creates the query using a top down structure where the different part of the query are indented in order to reflecting their level inside the query’s logic. This increases the query readability in particular when dealing with complex queries.

The vertical block structure gives great control on the query’s sub sections. With query editors supporting the partial code execution (e.g. pgadmin3’s query ) is possible to highlight the query’s single sections and execute them immediately.

I developed this style from the induction I received in my first DBA job. My employer enforced along the developers and DBA a similar set of rules which worked perfectly, improving the produc- tivity and the code’s consistency across the company.


Incidentally this matches almost perfectly the way PostgreSQL processes a select. What follow is an excerpt of the PostgreSQL’ manual describing how the select is processed.


SELECT retrieves rows from zero or more tables. 
The general processing of SELECT is as follows:

1. All queries in the WITH list are computed.
These effectively serve as temporary tables that can be referenced in the FROM list.
A WITH query that is referenced more than once in FROM is computed only once.
 
2. All elements in the FROM list are computed.
(Each element in the FROM list is a real or virtual table.)
If more than one element is specified in the FROM list, they are cross-joined together.
 
3. If the WHERE clause is specified, all rows that do not satisfy the condition
are eliminated from the output.
 
4. If the GROUP BY clause is specified, or if there are aggregate function calls,
the output is combined into groups of rows that match on one or more values,
and the results of aggregate functions are computed.
If the HAVING clause is present, it eliminates groups that do not satisfy the
given condition.
 
5. The actual output rows are computed using the SELECT output expressions
for each selected row or row group.
 
6. SELECT DISTINCT eliminates duplicate rows from the result.
SELECT DISTINCT ON eliminates rows that match on all the specified expressions.
SELECT ALL (the default) will return all candidate rows, including duplicates.

7. Using the operators UNION, INTERSECT, and EXCEPT, the output of more than one
SELECT statement can be combined to form a single result set.
The UNION operator returns all rows that are in one or both of the result sets.
The INTERSECT operator returns all rows that are strictly in both result sets.
The EXCEPT operator returns the rows that are in the first result set but not in the second.
In all three cases, duplicate rows are eliminated unless ALL is specified.
The noise word DISTINCT can be added to explicitly specify eliminating duplicate rows.
Notice that DISTINCT is the default behavior here, even though ALL is the default for SELECT itself.
 
8. If the ORDER BY clause is specified, the returned rows are sorted in the specified order.
If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.
 
9. If the LIMIT (or FETCH FIRST) or OFFSET clause is specified, the SELECT statement only returns
a subset of the result rows.
 
10. If FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE or FOR KEY SHARE is specified,
the SELECT statement locks the selected rows against concurrent updates.

 

RTFM

The acronym RTFM stands for Read The Fucking Manual. It's quite obvious that reading the manual is the perfect approach for finding the correct information or at least a hint for finding the solution.

The false confidence or a lack of humbleness make people to forget how important is to read the documents. Understanding the documents is the next step, and is not simple indeed. In particular if the background is poor or the reader have preconceptions which alter the word meanings.

On the same message board mentioned in 1.4 an user asked some details about the binary data. Another "expert" explained how PostgreSQL had two ways of storing binary data. The bytea and the OID, which maximum size limit was respectively  one and two GB. I replied with a simple question. How is possible to store two GB in a four byte integer without strange voodoo rituals?

After an unpleasant argument finally the "expert" admitted he did not understood the mechanism used by PostgreSQL to workaround the TOASTed datum's size limit. If you are curious about that, well, RTFM!

The angry dba

Before anything on database administration let me introduce some best practice I acquired during my experience in the consulting company I mentioned before.
This company offer Oracle services to important enterprises in Italy and, despite the size, is well structured and organized.
The first thing I've learned was how to write good documents, not too much verbose but complete in any part.
Then I was inducted to the company's best practice in SQL coding as Oracle have some limitations in the size and characters allowed in the identifiers.
PostgreSQL does not have this limitations giving freedom of choice for any format and structure, with unicode characters (if the database supports UTF8 of course) and a 64 characters maximum length for the identifiers.
This is a good thing but, believe me, sometimes it can be a bloody nightmare.
I've written some guidelines to get things sorted out.

Rule 1 – No camel case or reserved keywords in the identifier name

Let start from the beginning.
Should you need to create a table to store debit notes for the year 2002, which name will you choose?
My old school development habits suggest me something like debit_notes_2002.
A new school developer or maybe used to work with AGILE, SCRUM,ITIL and S**** will probably use the name DebitNotes2002.
This in PostgreSQL, is a problem because all the names are converted in lower case making the camel case useless.
It's possible to keep the case preserved enclosing the identifiers between double quotes “.
That mean that ANY query involving your beautiful identifiers will contain the quotation and this affects the readability of the query.
This happens for any identifier named after reserved keyword as well.
Same story for the column names .

Rule 2 - self explaining schema  

In the rule 1 I declared I would name the table for debit notes as debit_notes_2002.
I lied.
I like to put prefix in any identifier to keep track which relation I'm are dealing with.
PostgreSQL stores any relation in one system table named pg_class and give you system views like pg_tables and pg_indexes to query.
But why I should bother in system catalogue queries or people inquiry if I can let the database describe itself?
The way to do this is quite simple.
I put a prefix on any identifier to tell what kind it is or which data carries.
Adding a t_ prefix in the table name will tell to any developer and, most important to any dba/performance tuner, that the relation carries physical data.
Adding a v_ prefix to any view will tell that we are dealing with a short cut to a query and, hopefully, this will avoid the bad habit to join views resulting in the query planner going nuts.
On the same way giving a prefix to the columns to tell the data type will reduce to the developers the risk to get a wrong behaviour for wrong typecast, and will give to the dba precious informations about the space and storage used by the table.
A good structure for building self explaining names can be this.

Object	Prefix
Table	t_
View	v_
Index	idx_
Unique index	uidx_
Primary key	pk_
Foreign key	fk_
Check	chk_
Unique key	uk_
Type	ty_
Sql function	fn_sql_
Plpgsql function	fn_plpg_
Plpython function	fn_plpy_
Plperl function	fn_plpr_
Trigger	trg_
rule	rul_

Table 1: Object prefix

The same for the data type used for the column.
An example for the most common data type is shown in table 2

Type	Prefix
Character	c_
Character varying	v_
integer	i_
text	t_
bytea	by_
numeric	n_

Rule number 4 – format the SQL queries

Sometime I receive requests to debug or optimize queries written in this way
select * from debitnoteshead a join debitnoteslines b on debnotid where a.datnot=b. datnot and b.deblin>1;
Frustrating isn't it?
Despite my legendary bad temper when I'm in service I'm absolutely professional and probably too much kind because I don't bounce the request and I fix the query.
I know I know, I'm doing wrong.
The company doesn't pay my salary to hit tab, enter and ctrl+u, but to keep the company's systems up, running and sound.
So, if you don't want to waste your time formatting when you receive something like this, I suggest to simply bounce back the request asking for a pretty format.
Some simple rules can enhance the readability.

1. SQL keywords in upper case
   
2. all the identifiers must be indented on the same level
   
3. All the same level SQL keywords must be indented at same level
   
4. Avoid the SELECT * as is a waste of memory and doesn't tell anything useful
   
5. If possible inner join the tables explicitally in the WHERE condition
   
6. In the join use the keyword ON to make clear which column is used for joining
   
7. In the table list use self explaining aliasing
   

Using those rules the previous query looks like this.
SELECT
                productcode,
                noteid,
                datnot
FROM
               debitnoteshead hrd,
               debitnoteslines lns
WHERE
                         hdr.debnotid=lns.debnotid
               AND hdr.datnot=lns.datnot
               AND lns.deblin>1
;
Now look beauty.



With this last advice ends my first blog post.
Just small afterword before ending.
Probably someone will think this guidelines are too strict to be accepted.
Obviously for the code already written nothing can be done, but can still teach the developers to write decent code.
I think this is a goal can be achieved and believe me, it worth it.
For the new projects this guidelines give you a powerful vision for the database structure without wasting your time through data dictionaries or asking people about the data.
IMHO our job is a war where the casualties are downtime and data loss.
I consider the data the most precious thing a company own and definitely our mission is to guarantee the smallest reaction time to fix any problem minimizing any possible loss of money.