SQL Formatting

The following SQL formatting rules apply to code written as views/SP/upgrade scripts under the dbscripts folder:

Please note that each example demonstrates ONLY the specific rule and not the entire formatting for simplicity.

Indentation

All indentations should use 4 spaces no TABs are allowed

Capitalization

Keyword: UPPER SELECT Data type: UPPER VARCHAR(32) Table name : lower users Column name : lower user_name Function name : InitCap for SP, lower for general functions that also must start with fn_db_ InsertAuditLog

   fn_db_add_column

Column alias: lower dept AS department Variable : lower, starts with v_ v_user Constraint: lower

   fk_user_user_sessions

Comma option

Always after item

  SELECT
  a,
  b,
  c
  FROM t;

AND/OR

Always in the beginning of line

  a > 1
  AND b<10

Operators

Always surround operators with one space

  a + b = c

Select Query

Column list style : stacked INSERT INTO T (name, size) VALUES(‘a’, 1);

FROM clause : in a new line SELECT * FROM T FROM clause table list style : stacked SELECT * FROM a, b FROM clause join : join table in a new line SELECT a.* FROM a INNER JOIN b ON a.id = b.id WHERE clause : condition in a new line SELECT * FROM t WHERE a = 1; WHERE clause : AND/OR at beginning of line SELECT * FROM t WHERE a = 1 AND b=’x’; GROUP BY clause : Column list in a new line SELECT * FROM t WHERE a = 1 GROUP BY b; GROUP BY column list style : stacked SELECT * FROM t WHERE a = 1 GROUP BY b, c; ORDER BY clause : Column list in a new line SELECT * FROM t WHERE a = 1 ORDER BY b; ORDER BY column list style : stacked SELECT * FROM t WHERE a = 1 ORDER BY b, c;

Nested Conditions

Example 1

  SELECT DISTINCT vds.*
         FROM vds
         WHERE (
                 NOT v_is_filtered
                 OR EXISTS (
                     SELECT 1
                     FROM user_vds_permissions_view
                     WHERE user_id = v_user_id
                         AND entity_id = vds_id
                     )
                 );

Example 2

    WHILE FOUND LOOP v_id := CAST(v_tempId AS UUID);
             SELECT count(*)
             INTO v_result
             FROM users
             WHERE user_id IN (
                     SELECT ad_element_id AS user_id
                     FROM permissions,
                         roles
                     WHERE permissions.role_id = roles.id
                         AND ad_element_id IN (
                             (
                                 SELECT ad_groups.id
                                 FROM ad_groups,
                                     engine_sessions
                                 WHERE engine_sessions.user_id = v_id
                                     AND ad_groups.id IN (
                                         SELECT *
                                         FROM fnsplitteruuid(engine_sessions.group_ids)
                                         )
                                 UNION
                                 SELECT v_id
                                 )
                             )
                         AND (
                             roles.role_type = 1
                             OR permissions.role_id = '00000000-0000-0000-0000-000000000001'
                             )
                     );
         UPDATE users
         SET last_admin_check_status = CASE
                 WHEN v_result = 0
                     THEN FALSE
                 ELSE TRUE
                 END
         WHERE user_id = v_id;
         FETCH myCursor
         INTO v_tempId;
     END LOOP;

Example 3

  IF EXISTS (
             SELECT 1
             FROM vm_device
             WHERE vm_id = v_vm_id
                 AND type = 'balloon'
                 AND device = 'memballoon'
             ) THEN result := true;
  END IF ;