This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
development:ohdsi_code_style_for_sql [2018/02/19 09:01] schuemie |
development:ohdsi_code_style_for_sql [2018/02/19 09:10] schuemie |
||
---|---|---|---|
Line 1: | Line 1: | ||
====== OHDSI code style for SQL ====== | ====== OHDSI code style for SQL ====== | ||
- | The OHDSI code style for SQL is heavily inspired by the [[http://architectshack.com/PoorMansTSqlFormatter.ashx|Poor Man's T-SQL Formatter]], which is available as a [[https://notepad-plus-plus.org/|NotePad++]] plug-in. The only difference with the default settings is that in OHDSI, comma's are trailing. You can automatically format your SQL correctly by using the [[http://poorsql.com/|Poor Man's T-SQL Formatter Online Tool]] (but don't forget to set Trailing Commas). | + | The OHDSI code style for SQL is heavily inspired by the [[http://architectshack.com/PoorMansTSqlFormatter.ashx|Poor Man's T-SQL Formatter]], which is available as a [[https://notepad-plus-plus.org/|NotePad++]] plug-in. The only difference with the default settings is that in OHDSI, commas are trailing. You can automatically format your SQL correctly by using the [[http://poorsql.com/|Poor Man's T-SQL Formatter Online Tool]] (but don't forget to set Trailing Commas). |
===== Case ===== | ===== Case ===== | ||
- | Because several database platforms are case-insensitive and tend to convert table and field names to either uppercase (e.g. Oracle) or lowercase (e.g. PostgreSQL), we use snake_case. All names should be in lowercase. | + | Because several database platforms are case-insensitive and tend to convert table and field names to either uppercase (e.g. Oracle) or lowercase (e.g. PostgreSQL), we use snake_case. All names should be in lowercase. Reserved words should be in upper case. |
**Good** | **Good** | ||
Line 18: | Line 18: | ||
''SELECT COUNT(*) AS PERSON_COUNT FROM person'' | ''SELECT COUNT(*) AS PERSON_COUNT FROM person'' | ||
+ | |||
+ | ''select count(*) as person_count from person'' | ||
===== Commas ===== | ===== Commas ===== | ||
+ | Commas should be trailing. | ||
+ | **Good** | ||
+ | <code> | ||
+ | SELECT COUNT(*) AS person_count, | ||
+ | condition_concept_id, | ||
+ | condition_type_concept_id | ||
+ | FROM condition_era | ||
+ | GROUP BY condition_concept_id, | ||
+ | condition_type_concept_id | ||
+ | </code> | ||
+ | |||
+ | **Bad** | ||
+ | <code> | ||
+ | SELECT COUNT(*) AS person_count | ||
+ | ,condition_concept_id | ||
+ | ,condition_type_concept_id | ||
+ | FROM condition_era | ||
+ | GROUP BY condition_concept_id | ||
+ | ,condition_type_concept_id | ||
+ | </code> | ||
+ | |||
+ | ===== Indentation and new lines ===== | ||
+ | Indentation is done using tabs. Field definitions are followed by a new line. | ||
+ | |||
+ | **Good** | ||
+ | <code> | ||
+ | SELECT COUNT(*) AS person_count, | ||
+ | condition_type_concept_id | ||
+ | FROM ( | ||
+ | SELECT * | ||
+ | FROM condition_era | ||
+ | WHERE condition_concept_id = 123 | ||
+ | ) tmp | ||
+ | GROUP BY condition_type_concept_id; | ||
+ | </code> | ||
+ | |||
+ | **Bad** | ||
+ | <code> | ||
+ | SELECT COUNT(*) AS person_count, condition_type_concept_id | ||
+ | FROM (SELECT * FROM condition_era WHERE condition_concept_id = 123) tmp | ||
+ | GROUP BY condition_type_concept_id; | ||
+ | </code> | ||