Skip to content

Latest commit

 

History

History
95 lines (79 loc) · 4.38 KB

guide-sql.asciidoc

File metadata and controls

95 lines (79 loc) · 4.38 KB
Raw
Table of Contents

SQL

For general guides on dealing or avoiding SQL, preventing SQL-injection, etc. you should study data-access layer.

Naming Conventions

Here we define naming conventions that you should follow whenever you write SQL files:

  • All SQL-Keywords in UPPER CASE

  • Table names in upper CamelCase (e.g. RestaurantOrder)

  • Column names in CamelCase (e.g. drinkState)

  • Indentation should be 2 spaces as suggested by devonfw for every format.

DDL

For DDLs follow these additional guidelines:

  • ID column names without underscore (e.g. tableId)

  • Define columns and constraints inline in the statement to create the table

  • Indent column types so they all start in the same text column

  • Constraints should be named explicitly (to get a reasonable hint error messages) with:

    • PK_{table} for primary key (name optional here as PK constraint are fundamental)

    • FK_{table}_{property} for foreign keys ({table} and {property} are both on the source where the foreign key is defined)

    • UC_{table}_{property}[_{propertyN}]* for unique constraints

    • CK_{table}_{check} for check constraints ({check} describes the check, if it is defined on a single property it should start with the property).

  • Databases have hard limitations for names (e.g. 30 characters). If you have to shorten names try to define common abbreviations in your project for according (business) terms. Especially do not just truncate the names at the limit.

  • If possible add comments on table and columns to help DBAs understanding your schema. This is also honored by many tools (not only DBA-tools).

Here is a brief example of a DDL:

CREATE SEQUENCE HIBERNATE_SEQUENCE START WITH 1000000;

-- *** Table ***
CREATE TABLE Table (
  id BIGINT NOT NULL AUTO_INCREMENT,
  modificationCounter INTEGER NOT NULL,
  seatsNumber INTEGER NOT NULL,
  CONSTRAINT PK_Table PRIMARY KEY(id)
);

-- *** UserRole ***
CREATE TABLE UserRole (
  id BIGINT NOT NULL AUTO_INCREMENT,
  modificationCounter INTEGER NOT NULL,
  name VARCHAR (255),
  active BOOLEAN,
  CONSTRAINT PK_UserRole PRIMARY KEY(id)
-- *** User ***
CREATE TABLE User (
  id BIGINT NOT NULL AUTO_INCREMENT,
  modificationCounter INTEGER NOT NULL,
  username VARCHAR (255) NULL,
  password VARCHAR (255) NULL,
  email VARCHAR (120) NULL,
  idRole BIGINT NOT NULL,
  CONSTRAINT PK_User PRIMARY KEY(id),
  CONSTRAINT PK_User_idRole FOREIGN KEY(idRole) REFERENCES UserRole(id) NOCHECK
);
COMMENT ON TABLE User is 'The users of the restaurant site';
...

Data

For insert, update, delete, etc. of data SQL scripts should additionally follow these guidelines:

  • Inserts always with the same order of columns in blocks for each table.

  • Insert column values always starting with id, modificationCounter, [dtype, ] …​

  • List columns with fixed length values (boolean, number, enums, etc.) before columns with free text to support alignment of multiple insert statements

  • Pro Tip: Get familiar with column mode of notepad++ when editing large blocks of similar insert statements.

INSERT INTO UserRole(id, modificationCounter, name, active) VALUES (0, 1, 'Customer', true);
INSERT INTO UserRole(id, modificationCounter, name, active) VALUES (1, 1, 'Waiter', true);
INSERT INTO User(id, modificationCounter, username, password, email, idRole) VALUES (0, 1, 'user0', 'password', '[email protected]', 0);
INSERT INTO User(id, modificationCounter, username, password, email, idRole) VALUES (1, 1, 'waiter', 'waiter', '[email protected]', 1);

INSERT INTO Table(id, modificationCounter, seatsNumber) VALUES (0, 1, 4);
INSERT INTO Table(id, modificationCounter, seatsNumber) VALUES (1, 1, 4);
INSERT INTO Table(id, modificationCounter, seatsNumber) VALUES (2, 1, 4);
INSERT INTO Table(id, modificationCounter, seatsNumber) VALUES (3, 1, 4);
INSERT INTO Table(id, modificationCounter, seatsNumber) VALUES (4, 1, 6);
INSERT INTO Table(id, modificationCounter, seatsNumber) VALUES (5, 1, 6);
INSERT INTO Table(id, modificationCounter, seatsNumber) VALUES (6, 1, 6);
INSERT INTO Table(id, modificationCounter, seatsNumber) VALUES (7, 1, 8);
INSERT INTO Table(id, modificationCounter, seatsNumber) VALUES (8, 1, 8);
...