How to append to SQLite table in R with autogenerated fields

Question

This is a similar problem to this question, but I do not want the missing columns filled in with NA, because the missing columns have meaningful default values including the primary key.

I am trying to append to a SQLite table from R where the table has some auto-generated fields, specifically the primary key, and two timestamp values. The first timestamp is the created date, and the second timestamp is a modified date.

Here is the table structure:

CREATE TABLE "level1" (
  "l1id" bigint(20)  NOT NULL ,
  "l0id" bigint(20)  DEFAULT NULL,
  "acid" bigint(20)  DEFAULT NULL,
  "cndx" int(11) DEFAULT NULL,
  "repi" int(11) DEFAULT NULL,
  "created_date" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "modified_date" timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  "modified_by" varchar(100) DEFAULT NULL,
  PRIMARY KEY ("l1id")
)

When I have tried doing the exact same thing using MySQL, dbWriteTable automatically handles the default values for missing columns, and populates the primary key and created_date properly (AND it matches the order of the columns automatically).

How can I achieve the same behavior with the RSQLite package? I am not sure if I have the database configured incorrectly, or if I need some addtional steps within R?

I have tried pre-populating the missing fields with NA & 'null', but in both cases I get an error saying:

Warning message:
In value[[3L]](cond) :
  RS-DBI driver: (RS_SQLite_exec: could not execute: column l1id is not unique)

And the data does not get written.

Answer 1

The Solution

I figured out a solution, based largely on the dbWriteFactor function Ari Friedman wrote as an answer to his question. Below I show the portion of code I used, modified to work specifically with the data.table package.

It is also very important to note that I had to change the sqlite table structure. To get this to work I had to remove the "NOT NULL" designation from all auto-generated fields.

New Table Structure

CREATE TABLE "level1" (
  "l1id" INTEGER PRIMARY KEY,
  "l0id" bigint(20)  DEFAULT NULL,
  "acid" bigint(20)  DEFAULT NULL,
  "cndx" int(11) DEFAULT NULL,
  "repi" int(11) DEFAULT NULL,
  "created_date" timestamp DEFAULT CURRENT_TIMESTAMP,
  "modified_date" timestamp DEFAULT '0000-00-00 00:00:00',
  "modified_by" varchar(100) DEFAULT NULL
);

Adapted Code Sample

dbcon <- do.call(dbConnect, db_pars)

tempTbl <- "temp_table"
if (dbExistsTable(dbcon, tempTbl)) dbRemoveTable(dbcon, tempTbl)
dbWriteTable(conn = dbcon, 
             name = tempTbl, 
             value = dat, 
             row.names = FALSE, 
             append = FALSE)
tbl_flds <- loadColNames(tbl, db)
tmp_flds <- names(dat)
status <- dbSendQuery(dbcon, 
                      paste("INSERT INTO", tbl, 
                            "(", paste(tmp_flds, collapse = ","), ")",
                            "SELECT",
                            paste(tmp_flds, collapse = ","),
                            "FROM",
                            tempTbl))
# Remove temporary table
dbRemoveTable(dbcon, tempTbl)

dbDisconnect(dbcon)

where db_pars is a list of database parameters to establish the connection.