Instructions for using sqlite DDLs for non-R users

Aperta
  • Qualcosa non va?
#693 4 commenti 5 reazioni 0 assegnatari Vedi su GitHub

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
35/100
Tipo di issue
Documentazione
Chiarezza
Abbastanza chiara
Stato di attività
Ferma
Stack tecnologico
bash, sql, sqlite
Ambito
databases, documentation

Direzione di ricerca

Esamina lo script update-ddl.sh fornito e i file OMOPCDM*_ddl.sql, *_primary_keys.sql, *_constraints.sql e *_indices.sql; verifica come sqlite3 li gestisce e come sqlrender utilizza @cdmDatabaseSchema. Documenta un workflow non-R riproducibile e verifica che crei un database senza errori di foreign key.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

Bug Community Contribution

Hi there!

I've been playing around trying to get the DDLs loaded for sqlite3, version 5.4 using the sqlite3 command line and I've been having a pretty rough go of it using the provided definitions.

I didn't find any documentation here or elsewhere about the @cdmDatabaseSchema notation in the ddl files, so I went ahead and removed them. I assume they are something used by the sqlrender utility - but I'm not an R user - I'm just trying to create a working OMOPCDM database in sqlite.

I also discovered that sqlite does not allow you to apply primary or foreign keys to tables using the ALTER TABLE commands provided. So, I wrote a brittle little script to apply the PK and FK definitions to the main _ddl.sql file.

This allows me to use the patched _ddl.sql file to define tables with PKs and FKs. The _indices.sql file seems fine as-is.

I did notice that the COHORT table is missing a PRIMARY KEY, so the foreign key check fails in a clean install for just that table. It almost seems like COHORT should have cohort_id integer PRIMARY KEY and COHORT_DEFINITION should have cohort_definition_id integer PRIMARY KEY - and then the COHORT table should have a FK on COHORT_DEFINITION.cohort_definition_id (instead, it seems backwards). But as it is now, if I make COHORT.cohort_definition_id the PK of the COHORT table - it gets me past the failing foreign key checks. Anyway, I expect some of that will change in future versions - my script includes a hack for this.

For posterity, I'm providing my script here. However, are there instructions for people who don't want to use R but just want to create a working DB out there somewhere?

Click to see my script 
#!/bin/bash
#
# File: update-ddl.sh
#
# Converts the OMOP-provided DDL database definition scripts into a format
# that can be loaded by sqlite3.
#
# This has been tested against the provided sqlite 5.4 DDL files.
#
# To run this, place copies of the provided .sql files into this directory
# and run the script.  It will modify the sql files and create an empty
# database called cdm.db
#

set -e
set -o pipefail
set -u


# There should be only one DDL file in the local directory.
DDL=$( ls OMOPCDM*_ddl.sql )
[ ! -e ${DDL} ] && echo "Failed to find an OMOPCDM*_ddl.sql schema!" && exit 1


# Awk scriptlet to add a PRIMARY KEY declaration to a table.column in a table
# definition.
#
# Assumptions:
#   'table' is padded with spaces
#   'column' begins with a tab and ends with a space
#
AWK_PK='
/^CREATE TABLE / {
  if ($0 ~ table) { target=1 } else { target=0 }
}

{
  if (target && $0 ~ column && (!($0 ~ / PRIMARY KEY,/))) {
    sub(/,/, " PRIMARY KEY,");
  }
  print $0
}
'

# Awk scriptlet to add a FOREIGN KEY declaration to a table.column in a table
# definition.
#
# Assumptions:
#   'table' is padded with spaces
#   'column' begins with a tab and ends with a space
#   'ref' is multiple words long
#
AWK_FK='
/^CREATE TABLE / {
  if ($0 ~ table) { target=1 } else { target=0 }
}

{
  if (target && $0 ~ column && (!($0 ~ / REFERENCES /))) {
    if ($0 ~ /,$/) {
      sub(/,$/, " REFERENCES " ref ",");
    } else {
      sub(/ );$/, " REFERENCES " ref ");");
    }
  }
  print $0
}
'

##
# Gets the table names and PK columns from constraints.
#
function get_pks() {
  grep '^ALTER TABLE ' OMOPCDM*_primary_keys.sql \
    | sed -e 's:.* \(.*\) ADD CONSTRAINT .* PRIMARY KEY .\(.*\).;:\1 \2:'
}


##
# Decorate the PK columns with PRIMARY KEY if not already decorated.
#
function mark_pk() {

  # Apply the change to the ddl file content, saving into a temp file.
  cat ${DDL} | awk -v table=" ${1} " -v column="        ${2} " "${AWK_PK}" \
    > ${DDL}.tmp

  # diff the files to assert diff count is 1 changed line only.
  (( $( diff -by --suppress-common-lines ${DDL} ${DDL}.tmp | wc -l ) == 1 )) || exit 1

  # Apply the temp file to the DDL file.
  mv ${DDL}.tmp ${DDL}
}


function get_fks() {
  grep '^ALTER TABLE ' OMOPCDM*_constraints.sql \
    | sed -e 's:ALTER TABLE \(.*\) ADD CONSTRAINT .* FOREIGN KEY .\([^ ]*\). REFERENCES:\1 \2:' \
    | sed -e 's:;::'
}


##
# Decorate the PK columns with PRIMARY KEY if not already decorated.
#
function mark_fk() {

  # Apply the change to the ddl file content, saving into a temp file.
  cat ${DDL} \
    | awk -v table=" ${1} " -v column=" ${2} " -v ref="${3}" "${AWK_FK}" \
    > ${DDL}.tmp

  # diff the files to assert diff count is 1 changed line only.
  (( $( diff -by --suppress-common-lines ${DDL} ${DDL}.tmp | wc -l ) == 1 )) || exit 1

  # Apply the temp file to the DDL file.
  mv ${DDL}.tmp ${DDL}
}


# Remove the template schema reference, which isn't supported in sqlite.
sed -i -e 's:@cdmDatabaseSchema.::g' OMOPCDM*.sql && rm -f OMOPCDM*.sql-e

# Update the table definitions to insert the PRIMARY KEY declarations.
echo "Adding PRIMARY KEYS to DDL"
get_pks | while read table column; do
  mark_pk ${table} ${column}
done

# Insert the FK constraints into the table.
echo "Adding FOREIGN KEYS to DDL"
get_fks | while read table column reference; do
  mark_fk ${table} ${column} "${reference}"
done

# HACK: no PK in the cohort table, so this is needed to satisfy FK checks.
mark_pk cohort cohort_definition_id

# Populate the DB tables and indices.
echo "Creating empty cdm.db database"
rm -f cdm.db
touch cdm.db
sqlite3 cdm.db < ${DDL}
sqlite3 cdm.db < OMOPCDM*_indices.sql
sqlite3 cdm.db "pragma foreign_key_check"

# Success!
echo "DONE!"

This updates the _ddl.sql file to look like this:

CREATE TABLE person (
                        person_id integer NOT NULL PRIMARY KEY,
                        gender_concept_id integer NOT NULL REFERENCES CONCEPT (CONCEPT_ID),
                        year_of_birth integer NOT NULL,
                        month_of_birth integer NULL,
                        day_of_birth integer NULL,
                        birth_datetime REAL NULL,
                        race_concept_id integer NOT NULL REFERENCES CONCEPT (CONCEPT_ID),
                        ethnicity_concept_id integer NOT NULL REFERENCES CONCEPT (CONCEPT_ID),
                        location_id integer NULL REFERENCES LOCATION (LOCATION_ID),
                        provider_id integer NULL REFERENCES PROVIDER (PROVIDER_ID),
                        care_site_id integer NULL REFERENCES CARE_SITE (CARE_SITE_ID),
                        person_source_value TEXT NULL,
                        gender_source_value TEXT NULL,
                        gender_source_concept_id integer NULL REFERENCES CONCEPT (CONCEPT_ID),
                        race_source_value TEXT NULL,
                        race_source_concept_id integer NULL REFERENCES CONCEPT (CONCEPT_ID),
                        ethnicity_source_value TEXT NULL,
                        ethnicity_source_concept_id integer NULL REFERENCES CONCEPT (CONCEPT_ID));

When the script runs correctly, you see output like this:

(base) anderson.carl3@home test % ./update-ddl.sh
Adding PRIMARY KEYS to DDL
Adding FOREIGN KEYS to DDL
Creating empty cdm.db database
DONE!
Lingua principale
HTML
Stelle
1.1k
Fork
513
Merge medio
8g 3h
PR unite (30g)
4

Preparare l'ambiente

Questo progetto non fornisce container di sviluppo, Dockerfile né guida per i contributori, quindi l'ambiente è a tuo carico: parti dal suo README e consulta la nostra guida al primo contributo per i passaggi generali.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di OHDSI/CommonDataModel

Tutte le issue di OHDSI/CommonDataModel

Issue simili

Altre issue su Databases

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.