Create the results tables to store PatientLevelPrediction models and results into a database
Source:R/uploadToDatabase.R
createPlpResultTables.RdThis function executes a large set of SQL statements to create tables that can store models and results
Usage
createPlpResultTables(
connectionDetails,
targetDialect = "postgresql",
resultSchema,
deleteTables = TRUE,
createTables = TRUE,
tablePrefix = "",
tempEmulationSchema = getOption("sqlRenderTempEmulationSchema"),
testFile = NULL
)Arguments
- connectionDetails
The database connection details
- targetDialect
The database management system being used
- resultSchema
The name of the database schema that the result tables will be created.
- deleteTables
If true any existing tables matching the PatientLevelPrediction result tables names will be deleted
- createTables
If true the PatientLevelPrediction result tables will be created
- tablePrefix
A string that appends to the PatientLevelPrediction result tables
- tempEmulationSchema
The temp schema used when the database management system is oracle
- testFile
(used for testing) The location of an sql file with the table creation code
Examples
# create a sqlite database with the PatientLevelPrediction result tables
connectionDetails <- DatabaseConnector::createConnectionDetails(
dbms = "sqlite",
server = file.path(tempdir(), "test.sqlite"))
createPlpResultTables(connectionDetails = connectionDetails,
targetDialect = "sqlite",
resultSchema = "main",
tablePrefix = "plp_")
#> Connecting using SQLite driver
#> All or some PLP result tables do not exist, tables being recreated
#> Deleting existing tables
#> Creating PLP results tables
#> | | | 0% | |== | 3% | |===== | 7% | |======= | 10% | |========= | 13% | |============ | 17% | |============== | 20% | |================ | 23% | |=================== | 27% | |===================== | 30% | |======================= | 33% | |========================== | 37% | |============================ | 40% | |============================== | 43% | |================================= | 47% | |=================================== | 50% | |===================================== | 53% | |======================================== | 57% | |========================================== | 60% | |============================================ | 63% | |=============================================== | 67% | |================================================= | 70% | |=================================================== | 73% | |====================================================== | 77% | |======================================================== | 80% | |========================================================== | 83% | |============================================================= | 87% | |=============================================================== | 90% | |================================================================= | 93% | |==================================================================== | 97% | |======================================================================| 100%
#> Executing SQL took 0.0268 secs
#> PLP result migration being applied
#> Migrating data set
#> Migrator using SQL files in PatientLevelPrediction
#> Connecting using SQLite driver
#> Creating migrations table
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00213 secs
#> Migrations table created
#> Executing migration: Migration_1-store_version.sql
#> | | | 0% | |=================================== | 50% | |======================================================================| 100%
#> Executing SQL took 0.00199 secs
#> Saving migration: Migration_1-store_version.sql
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00288 secs
#> Migration complete Migration_1-store_version.sql
#> Executing migration: Migration_2-add_hyperparameter_settings.sql
#> | | | 0% | |================== | 25% | |=================================== | 50% | |==================================================== | 75% | |======================================================================| 100%
#> Executing SQL took 0.00336 secs
#> Saving migration: Migration_2-add_hyperparameter_settings.sql
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00281 secs
#> Migration complete Migration_2-add_hyperparameter_settings.sql
#> Executing migration: Migration_3-add_model_name.sql
#> | | | 0% | |=================================== | 50% | |======================================================================| 100%
#> Executing SQL took 0.00235 secs
#> Saving migration: Migration_3-add_model_name.sql
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00162 secs
#> Migration complete Migration_3-add_model_name.sql
#> Closing database connection
#> Updating version number
#> Connecting using SQLite driver
#> | | | 0% | |=================================== | 50% | |======================================================================| 100%
#> Executing SQL took 0.00196 secs
# delete the tables
createPlpResultTables(connectionDetails = connectionDetails,
targetDialect = "sqlite",
resultSchema = "main",
deleteTables = TRUE,
createTables = FALSE,
tablePrefix = "plp_")
#> Connecting using SQLite driver
#> All or some PLP result tables do not exist, tables being recreated
#> Deleting existing tables
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.261 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00182 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00161 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00177 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00158 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00235 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00165 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00175 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00185 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00189 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00195 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00204 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00186 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00196 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00242 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.005 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00197 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00203 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00205 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00206 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00197 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00218 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00205 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.0021 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00195 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00201 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00194 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00201 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00194 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00218 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00164 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00179 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.0019 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00187 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00173 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.0018 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00167 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00167 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00168 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00257 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00158 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00167 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00152 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00165 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00159 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00162 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00148 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00153 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.0015 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00151 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00148 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00174 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00157 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00165 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00171 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00169 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00156 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00164 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00154 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00143 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00142 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00145 secs
#> Deleting PLP migration tables
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00177 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00156 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.00145 secs
#> | | | 0% | |======================================================================| 100%
#> Executing SQL took 0.0015 secs
# clean up the database file
unlink(file.path(tempdir(), "test.sqlite"))