wiiki - SqlLineCount

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