User Tools

Site Tools


development:ohdsi_code_style_for_sql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
development:ohdsi_code_style_for_sql [2018/02/19 09:10]
schuemie
development:ohdsi_code_style_for_sql [2020/04/06 13:40] (current)
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-inThe 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). +This has moved here: https://ohdsi.github.io/MethodsLibrary/codeStyle.html#​ohdsi_code_style_for_sql
- +
- +
-===== 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. Reserved words should be in upper case. +
- +
-**Good** +
- +
-''​SELECT COUNT(*) AS person_count FROM person''​ +
- +
-**Bad** +
- +
-''​SELECT COUNT(*) AS personCount FROM person''​ +
- +
-''​SELECT COUNT(*) AS Person_Count FROM person''​ +
- +
-''​SELECT COUNT(*) AS PERSON_COUNT FROM person''​ +
- +
-''​select count(*) as person_count from person''​ +
- +
-===== 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>​ +
development/ohdsi_code_style_for_sql.1519031454.txt.gz · Last modified: 2018/02/19 09:10 by schuemie