guide sql - rfreier/oasp4j GitHub Wiki

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 CamlCase (e.g. RestaurantOrder)

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

  • Indentation should be 2 spaces as suggested by OASP 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)
);
...

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.

-- Common Booking with already created orders
INSERT INTO Booking(id, modificationCounter, idUser, name, bookingToken, comment, email, bookingDate, expirationDate, creationDate, canceled, bookingType, idTable, idOrder, assistants) VALUES(0, 1, 0, 'user0', 'CB_20170509_123502555Z', 'Booking Type CSR', '[email protected]', DATEADD('DAY', 5, CURRENT_TIMESTAMP), DATEADD('DAY', 5, DATEADD('HOUR', -1, CURRENT_TIMESTAMP)), CURRENT_TIMESTAMP, false, 0, 0, 0, 3);
INSERT INTO Booking(id, modificationCounter, idUser, name, bookingToken, comment, email, bookingDate, expirationDate, creationDate, canceled, bookingType, idTable, idOrder, assistants) VALUES(1, 1, 0, 'user1', 'CB_20170510_123502575Z', 'Booking Type GSR', '[email protected]', DATEADD('DAY', 5, CURRENT_TIMESTAMP), DATEADD('DAY', 5, DATEADD('HOUR', -1, CURRENT_TIMESTAMP)), CURRENT_TIMESTAMP, false, 1, 1, 1, null);
-- Common Booking without orders
INSERT INTO Booking(id, modificationCounter, idUser, name, bookingToken, comment, email, bookingDate, expirationDate, creationDate, canceled, bookingType, idTable, idOrder, assistants) VALUES(2, 1, 0, 'user2', 'CB_20170510_123502595Z', 'Booking Type GSR', '[email protected]', DATEADD('DAY', 5, CURRENT_TIMESTAMP), DATEADD('DAY', 5, DATEADD('HOUR', -1, CURRENT_TIMESTAMP)), CURRENT_TIMESTAMP, false, 0, 2, null, 5);

-- Guest Booking
INSERT INTO Booking(id, modificationCounter, idUser, name, bookingToken, comment, email, bookingDate, expirationDate, creationDate, canceled, bookingType, idTable, idOrder) VALUES(3, 1, 0, 'host1', 'CB_20170510_123502655Z', 'Booking Type GSR', '[email protected]', DATEADD('DAY', 5, CURRENT_TIMESTAMP), DATEADD('DAY', 5, DATEADD('HOUR', -1, CURRENT_TIMESTAMP)), CURRENT_TIMESTAMP, false, 1, 3, null);
-- guests for booking with id 3
INSERT INTO InvitedGuest(id, modificationCounter, idBooking, guestToken, email, accepted, modificationDate) VALUES(0, 1, 3, 'GB_20170510_02350266501Z', '[email protected]', true, DATEADD('DAY', 5, CURRENT_TIMESTAMP));
INSERT INTO InvitedGuest(id, modificationCounter, idBooking, guestToken, email, accepted, modificationDate) VALUES(1, 1, 3, 'GB_20170510_12350266501Z', '[email protected]', true, DATEADD('DAY', 5, CURRENT_TIMESTAMP));
INSERT INTO InvitedGuest(id, modificationCounter, idBooking, guestToken, email, accepted, modificationDate) VALUES(2, 1, 3, 'GB_20170510_22350266501Z', '[email protected]', false, DATEADD('DAY', 5, CURRENT_TIMESTAMP));
INSERT INTO InvitedGuest(id, modificationCounter, idBooking, guestToken, email, accepted, modificationDate) VALUES(3, 1, 3, 'GB_20170510_32350266501Z', '[email protected]', true, DATEADD('DAY', 5, CURRENT_TIMESTAMP));
INSERT INTO InvitedGuest(id, modificationCounter, idBooking, guestToken, email, accepted, modificationDate) VALUES(4, 1, 3, 'GB_20170510_42350266501Z', '[email protected]', false, DATEADD('DAY', 5, CURRENT_TIMESTAMP));

...

Creative Commons License Agreement
This documentation is licensed under the Creative Commons License (Attribution-NoDerivatives 4.0 International).

⚠️ **GitHub.com Fallback** ⚠️