02-populate.R

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

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

# this script is meant for a first load run

# Populate ---------------------------------------------------------------------

q_ins_form_d <-
  "INSERT OR REPLACE INTO form_d
   SELECT
   ACCESSIONNUMBER,
   FILING_DATE,
   SIC_CODE,
   SUBMISSIONTYPE,
   OVER100PERSONSFLAG,
   OVER100ISSUERFLAG
   FROM 'formd-unzipped/*/FORMDSUBMISSION.tsv';
  "

dbExecute(duckdb_con, q_ins_form_d)

# or use dplyr::rows_insert() which is supported on DuckDB despite the docs not mentioning it

q_ins_issuers <-
  "INSERT OR REPLACE INTO issuers
   SELECT
   ACCESSIONNUMBER,
   IS_PRIMARYISSUER_FLAG,
   ISSUER_SEQ_KEY,
   ENTITYNAME,
   CITY,
   STATEORCOUNTRY,
   STATEORCOUNTRYDESCRIPTION,
   JURISDICTIONOFINC,
   ENTITYTYPE,
   ENTITYTYPEOTHERDESC,
   YEAROFINC_TIMESPAN_CHOICE,
   YEAROFINC_VALUE_ENTERED
   FROM 'formd-unzipped/*/ISSUERS.tsv';
  "

dbExecute(duckdb_con, q_ins_issuers)

q_ins_offering <-
  "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/*/OFFERING.tsv';
  "

dbExecute(duckdb_con, q_ins_offering)

q_ins_recipients <-
  "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/*/RECIPIENTS.tsv';
  "
dbExecute(duckdb_con, q_ins_recipients)

dbGetQuery(duckdb_con, "PRAGMA show_tables_expanded;") |>
  as_tibble() |>
  filter(!temporary) |>
  tidyr::unnest_longer(c(column_names, column_types)) |>
  print(n = Inf)

dbDisconnect(duckdb_con)