Sql Line Count

There are several different ways to count the number of LinesOfCode in StructuredQueryLanguage.

Here is the SQL example from the ManyToManyChallenge. [DeleteMe: Some spaces have been deleted for consistency; the number of lines has not been changed.]

 CREATE TABLE Users 
 ( user_id INTEGER NOT NULL PRIMARY KEY, 
   username VARCHAR NOT NULL,
   CONSTRAINT UNIQUE (username));

 CREATE TABLE Groups 
 ( group_id INTEGER NOT NULL PRIMARY KEY,
   group_name VARCHAR,
   CONSTRAINT UNIQUE (group_name));

 CREATE TABLE r_user_groups (
   user_id INTEGER NOT NULL,
   group_id INTEGER NOT NULL,
   CONSTRAINT PRIMARY KEY (user_id, group_id),
   CONSTRAINT FOREIGN KEY (user_id) REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT FOREIGN KEY (group_id) REFERENCES groups ON DELETE CASCADE ON UPDATE CASCADE
  )

  CREATE INDEX r_user_groups_idx1 ON r_user_groups (group_id);

If a complete SQL query is considered to be a line of code, this example has 4 lines of code.

If the code is formatted as shown above, and each non-blank line as formatted is considered to be a line of code, this example has 16 lines of code.

If each constraint or action is formatted on its own line, the code looks like this:

 CREATE TABLE Users 
 ( user_id    INTEGER 
              NOT NULL 
              PRIMARY KEY, 
   username   VARCHAR 
              NOT NULL,
   CONSTRAINT UNIQUE (username));

 CREATE TABLE Groups 
 ( group_id   INTEGER 
              NOT NULL 
              PRIMARY KEY,
   group_name VARCHAR,
   CONSTRAINT UNIQUE (group_name));

 CREATE TABLE r_user_groups (
   user_id  INTEGER 
            NOT NULL,
   group_id INTEGER 
            NOT NULL,
   CONSTRAINT PRIMARY KEY (user_id, group_id),
   CONSTRAINT FOREIGN KEY (user_id) 
                          REFERENCES users 
                          ON DELETE CASCADE 
                          ON UPDATE CASCADE,
   CONSTRAINT FOREIGN KEY (group_id) 
                          REFERENCES groups 
                          ON DELETE CASCADE 
                          ON UPDATE CASCADE
  )

  CREATE INDEX r_user_groups_idx1 
               ON r_user_groups (group_id);

If a complete SQL query is considered to be a line of code, this example still has only 4 lines of code.

If the code is formatted as shown above, and each non-blank line as formatted is considered to be a line of code, this example has 30 lines of code.

I prefer to have much of the schema setups in meta-data (See DataDictionary) and use a GUI tool and/or table browser rather than code for such. In fact, I think that is how it is usually stored. Code is just one representation. See CodeAvoidance.

CategoryMetrics