R/odcr_updatedb.R

In openjusticeok/ojo: Access and analyze data from the Open Justice Oklahoma database

odcr_updatedb <- function() {
  
  connect_ojo()
  
  if (exists("odcr_caseinfo")) { dbWriteTable(ojo_db, "odcr_caseinfo", odcr_caseinfo, row.names = FALSE, append = TRUE) }
  if (exists("odcr_events")) {dbWriteTable(ojo_db, "odcr_events", odcr_events, row.names = FALSE, append = TRUE)}
  if (exists("odcr_parties")) { dbWriteTable(ojo_db, "odcr_party", odcr_parties, row.names = FALSE, append = TRUE)}
  if (exists("odcr_party_profile")) { dbWriteTable(ojo_db, "odcr_party_profile", odcr_party_profile, row.names = FALSE, append = TRUE)}
  if (exists("odcr_updates")) { dbWriteTable(ojo_db, "odcr_updates", odcr_updates, row.names = FALSE, append = TRUE)}
  if (exists("odcr_lastupdate")) { dbxUpsert(ojo_db, "odcr_lastupdate", as.data.frame(odcr_lastupdate), where_cols = c("odcr_lastupdate_id"))}
  if (exists("odcr_disps")) { dbWriteTable(ojo_db, "odcr_disps", odcr_disps, row.names = FALSE, append = TRUE)}
  
  if (exists("odcr_mins")) {
    years <- unique(odcr_mins$file_year)
    casetypes <- unique(odcr_mins$casetype)
    
    for (i in years) {
      for (j in casetypes) {
        if (!paste0("odcr_mins_", i, str_to_upper(j)) %in% dbListTables(ojo_db)) {
          dbGetQuery(ojo_db,
                     paste0(
                       "CREATE TABLE `", paste0("odcr_mins_", i, str_to_upper(j)),
                       "` (`odcr_min_id` varchar(50) NOT NULL,
          `court` varchar(50) DEFAULT NULL,
          `casenum` varchar(50) DEFAULT NULL,
          `casetype` varchar(6) DEFAULT NULL,
          `file_year` int(6) DEFAULT NULL,
          `min_date` date DEFAULT NULL,
          `min_desc` text,
          `fee_amt` double DEFAULT NULL,
          PRIMARY KEY (`odcr_min_id`),
          KEY `odcr_min_id` (`odcr_min_id`),
          KEY `court` (`court`),
          KEY `casenum` (`casenum`),
          KEY `casetype` (`casetype`),
          KEY `file_year` (`file_year`),
          FULLTEXT KEY `min_desc` (`min_desc`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
                     )
          )
        }
        
        dbWriteTable(ojo_db,
                     paste0("odcr_mins_", i, str_to_upper(j)),
                     odcr_mins[odcr_mins$file_year == i & odcr_mins$casetype == j, ],
                     row.names = FALSE, append = TRUE)
        
        if (!paste0("odcr_pays_", i) %in% dbListTables(ojo_db)) {
          dbGetQuery(ojo_db, paste0(
            "CREATE TABLE `odcr_pays_", i, "` (
  `odcr_pay_id` varchar(50) NOT NULL,
`court` varchar(50) DEFAULT NULL,
`casenum` varchar(50) DEFAULT NULL,
`casetype` varchar(10) DEFAULT NULL,
`file_year` int(6) DEFAULT NULL,
`pay_date` date DEFAULT NULL,
`pay_desc` varchar(50) DEFAULT NULL,
`pay_amt` double DEFAULT NULL,
PRIMARY KEY (`odcr_pay_id`),
KEY `court` (`court`),
KEY `casenum` (`casenum`),
KEY `casetype` (`casetype`),
KEY `file_year` (`file_year`),
KEY `pay_date` (`pay_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"
          ))
        }
        
        if (exists("odcr_pays")) {
          dbWriteTable(ojo_db,
                       paste0("odcr_pays_", i),
                       odcr_pays[odcr_pays$file_year == i,],
                       row.names = FALSE, append = TRUE)
        }
        
      }
    }
  }
  if (exists("odcr_updates")) { dbWriteTable(ojo_db, "odcr_updates", odcr_updates,
                                             append = TRUE, row.names = FALSE) }
  
  ojodb::disconnect_ojo()
  
  print("Database updated.")
}