ctsit / redcapcustodian

Simplified, automated data management on REDCap systems
Other
12 stars 6 forks source link

Port convert_schema_to_sqlite from rcc.billing #94

Closed ChemiKyle closed 1 year ago

ChemiKyle commented 1 year ago

Altered function to accept path to sql file rather than require file be present in package.

The function could also accept a raw sql string, I resisted my temptation to follow this rabbit and left a note for future improvement instead.
If we're being really nice to the community, we could check for presence of a perl binary at the start of the function as well.

This is a precursor to work related to redcap_summary_metrics.


Note that sqlite has some peculiarities, the most pertinent being that while it does support auto-incrementing primary keys, lines like:

  `id` bigint NOT NULL AUTO_INCREMENT,

need to be expressed as:

  `id` INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,

for our perl script to translate them. See paragraphs 1 and 2 of this SO answer.

ChemiKyle commented 1 year ago

I also ported mutate_columns_to_posixct from rcc.billing in commit 6fe8296. This is to aid writing automated tests involving in-memory dbs.

pbchase commented 1 year ago

I like what you wrote. I added a test of convert_schema_to_sqlite.

If you like the test I wrote, please merge it. The merging ball is in your court

ChemiKyle commented 1 year ago

Probably not needed, but I threw away the in-memory SQLite table at the end of the convert_schema_to_sqlite test since it feels proper.