03-update.R

# Upsert -----------------------------------------------------------------------

# dplyr::rows_upsert() is not supported with DuckDB (yet), we need to
# resort to SQL.

library(dplyr)
library(DBI)
library(duckdb)

duckdb_con <- dbConnect(
  duckdb(),
  dbdir = "formd.duckdb",
  read_only = FALSE
)

new_data_dir <-
  paste0(
    lubridate::year(lubridate::today()),
    "Q",
    lubridate::quarter(lubridate::today()) - 2,
    "_d"
  )

# `INSERT OR REPLACE INTO` is syntactic sugar for
# `INSERT INTO ... DO UPDATE SET c1 = EXCLUDED.c1, c2 = EXCLUDED.c2, ...`
q_app_form_d <-
  sprintf(
  "INSERT OR REPLACE INTO form_d (
   ACCESSIONNUMBER,
   FILING_DATE,
   SIC_CODE,
   SUBMISSIONTYPE,
   OVER100PERSONSFLAG,
   OVER100ISSUERFLAG
   )
   SELECT
   ACCESSIONNUMBER,
   FILING_DATE,
   SIC_CODE,
   SUBMISSIONTYPE,
   OVER100PERSONSFLAG,
   OVER100ISSUERFLAG
   FROM 'formd-unzipped/%s/FORMDSUBMISSION.tsv'
  ",
    new_data_dir
  )

dbExecute(duckdb_con, q_app_form_d)

q_app_issuers <-
  sprintf(
  "INSERT OR REPLACE INTO issuers (
   ACCESSIONNUMBER,
   IS_PRIMARYISSUER_FLAG,
   ISSUER_SEQ_KEY,
   ENTITYNAME,
   CITY,
   STATEORCOUNTRY,
   STATEORCOUNTRYDESCRIPTION,
   JURISDICTIONOFINC,
   ENTITYTYPE,
   ENTITYTYPEOTHERDESC,
   YEAROFINC_TIMESPAN_CHOICE,
   YEAROFINC_VALUE_ENTERED
   )
   SELECT
   ACCESSIONNUMBER,
   IS_PRIMARYISSUER_FLAG,
   ISSUER_SEQ_KEY,
   ENTITYNAME,
   CITY,
   STATEORCOUNTRY,
   STATEORCOUNTRYDESCRIPTION,
   JURISDICTIONOFINC,
   ENTITYTYPE,
   ENTITYTYPEOTHERDESC,
   YEAROFINC_TIMESPAN_CHOICE,
   YEAROFINC_VALUE_ENTERED
   FROM 'formd-unzipped/%s/ISSUERS.tsv';
  ",
    new_data_dir
  )

dbExecute(duckdb_con, q_app_issuers)

q_app_offering <-
  sprintf(
  "INSERT OR REPLACE INTO offering
   SELECT
   ACCESSIONNUMBER,
   INDUSTRYGROUPTYPE,
   INVESTMENTFUNDTYPE,
   IS40ACT,
   REVENUERANGE,
   AGGREGATENETASSETVALUERANGE,
   FEDERALEXEMPTIONS_ITEMS_LIST,
   ISAMENDMENT,
   PREVIOUSACCESSIONNUMBER,
   SALE_DATE,
   YETTOOCCUR,
   MORETHANONEYEAR,
   ISEQUITYTYPE,
   ISDEBTTYPE,
   ISOPTIONTOACQUIRETYPE,
   ISSECURITYTOBEACQUIREDTYPE,
   ISPOOLEDINVESTMENTFUNDTYPE,
   ISTENANTINCOMMONTYPE,
   ISMINERALPROPERTYTYPE,
   ISOTHERTYPE,
   DESCRIPTIONOFOTHERTYPE,
   ISBUSINESSCOMBINATIONTRANS,
   BUSCOMBCLARIFICATIONOFRESP,
   MINIMUMINVESTMENTACCEPTED,
   OVER100RECIPIENTFLAG,
   TOTALOFFERINGAMOUNT,
   TOTALAMOUNTSOLD,
   TOTALREMAINING,
   SALESAMTCLARIFICATIONOFRESP,
   HASNONACCREDITEDINVESTORS,
   NUMBERNONACCREDITEDINVESTORS,
   TOTALNUMBERALREADYINVESTED,
   SALESCOMM_DOLLARAMOUNT,
   SALESCOMM_ISESTIMATE,
   FINDERSFEE_DOLLARAMOUNT,
   FINDERSFEE_ISESTIMATE,
   FINDERFEECLARIFICATIONOFRESP,
   GROSSPROCEEDSUSED_DOLLARAMOUNT,
   GROSSPROCEEDSUSED_ISESTIMATE,
   GROSSPROCEEDSUSED_CLAROFRESP,
   AUTHORIZEDREPRESENTATIVE
   FROM 'formd-unzipped/%s/OFFERING.tsv';
  ",
    new_data_dir
  )

dbExecute(duckdb_con, q_app_offering)

q_app_recipients <-
  sprintf(
  "INSERT OR REPLACE INTO recipients
   SELECT
   ACCESSIONNUMBER,
   RECIPIENT_SEQ_KEY,
   RECIPIENTNAME,
   RECIPIENTCRDNUMBER,
   ASSOCIATEDBDNAME,
   ASSOCIATEDBDCRDNUMBER,
   STREET1,
   STREET2,
   CITY,
   STATEORCOUNTRY,
   STATEORCOUNTRYDESCRIPTION,
   ZIPCODE,
   STATES_OR_VALUE_LIST,
   DESCRIPTIONS_LIST,
   FOREIGNSOLICITATION
   FROM 'formd-unzipped/%s/RECIPIENTS.tsv';
  ",
    new_data_dir
  )

dbExecute(duckdb_con, q_app_recipients)

dbDisconnect(duckdb_con)