печально быть антисоциальным - refactoring SQL

03:24 pm - refactoring SQL

Padalīšos ar īsu padomu sarakstiņu par SQL uzturēšanu no refactoring@yahoogroups.com. Autors - Harald M. Müller.

Hi -

I don't know anything written anywhere, but here are a few pieces of advice from my experience:

1. Formatting of longer statements is essential. Line by line formatting helps humans (although people writing naive=line-splitting parsers don't like it):

SELECT
   TAB1.COL1,
   TAB1.COL2,
   TAB2.COL1
FROM
   TAB1, TAB2, TAB3
WHERE
   TAB1 ...
   AND ...
;

or

SELECT
   TAB1.COL1,
   TAB1.COL2,
   TAB2.COL1
FROM
   TAB1
   INNER JOIN TAB2 ON ...
   OUTER JOIN TAB3 ON ...
WHERE
   TAB1 ...
   AND ...
;

2. If there are corresponding items, add comments:

INSERT INTO TAB1 (
   COL1,
   COL2,
   COL3_VAL,
   COL3_CUR
) VALUES (
   'abc',   -- COL1
   37,      -- COL2
   27.00,   -- COL3_VAL
   'EUR'    -- COL3_CUR
);

or

CREATE VIEW VVV (
   "COL1",
   "COL2"
) AS 
   SELECT
      null,     -- COL1
      MYCOL2,   -- COL2
   FROM
      TAB1
UNION
   SELECT
      MYCOL111, -- COL1
      MYCOL222  -- COL2
   FROM
      TAB2;

3. Be very careful with ALTER scripts. In my experience, it is much better to rebuild a database (or part of a database - whatever conforms to your concept of a logical module). This requires

* that you have a process which accepts that databases are changed frequently (we do a completely new database build about once a week up to biweekly in development);

* if you rebuild only parts of the database, you need also "DROP scripts" which are the inverse of a build script; a typical upgrade then consists of an "DROP old version" + "CREATE new version"; keep the DROP and CREATE scripts tightly coupled by giving them corresponding names and checking them into version control always at the same time;

* if you need changes of databases with valuable data, you even need
migration scripts to migrate the data over. We do this by renaming the old tables (instead of dropping them) in the DROP scripts. The sequence then becomes:
"RENAME old version"; "CREATE new version"; "MIGRATE data".

Because of FK constraints, you will need a strict dependency architecture of your database (which will follow the architecture of your overall system).
[When you have to work on existing, non-modular/non-architected databases, it will get very very ugly ...]

4. If possible, have a table in the database where you store the version of the script(s) which created the database. These scripts should get their version script from the revision control system, i.e. each script should start with an insert like

INSERT INTO SCRIPT_VERSION (
   SCRIPT_NAME,
   CHECK_IN_DATE,
   VERSION)
VALUES (
   'THIS SCRIPT', -- oR $Filename$, if your RCS supports filename expansion
   '$Date$',
   '$Revision$);

This is even more important if you do frequent database schema changes in development (probably in parallel with your object model), and even MORE important if you do it on databases with valuable data.

5. Don't forget the explicit COMMIT WORK; statements which your database editor might require (if it does not have an "autocommit mode"). Write them after each statement.

6. For long-running scripts (we have scripts of >= 12 hours runtime), you might even add status information statements like

UPDATE SCRIPT_STATUS SET 
   CHECK_POINT = ...,
   CHECK_DATE = CURDATE(),
   CHECK_TIME = CURTIME(),
WHERE
   SCRIPT_NAME = 'THIS SCRIPT';

with 

CREATE TABLE SCRIPT_STATUS (
   SCRIPT_NAME VARCHAR(100),
   CHECK_POINT VARCHAR(50),
   CHECK_DATE DATE,
   CHECK_TIME TIME,
   PRIMARY KEY (SCRIPT_NAME)
);

7. Have "unit-tests" for your scripts and script sequences: I.e., it must be possible that you can, "on the side", quickly build a database and run the scripts over it. For this, it is also essential that you have a documentation of

* what script order is necessary to get the schema/contents of the database needed (if there is more than one script)

* in which state exactly a database must be before a certain script sequence can run. If a script can check the state at the beginning itself, this is even better (check your local script dialect whether there are if statements and "exits" so that you can write "if (not exists ...) exit script" or the like).

We lost quite much time with people who change scripts and then claim "I could not set up a database for testing, and operations didn't have time". Even syntax errors are only found by me a week later ...


So much off my head from my "real world" ...

Regards
Harald M. Mueller