TableSchemaList: Class '"TableSchemaList"'

In dbottomly/poplite: Tools for Simplifying the Population and Querying of SQLite Databases

Description

A list-based representation of a SQLite database which provides a simple approach to loading data into a database as well as merging with the existing data. See the vignette for more complex examples.

Objects from the Class

Objects can be created by calls of the form new("TableSchemaList", tab.list, search.cols).

Slots

tab.list:

Object of class "list" A list of lists with each list representing a table and each element containing information on the definition of columns. There should be 6 elements to the list: db.cols a character vector containing the names of the columns db.schema a character vector of the same length as db.cols which contains the columns types (e.g. TEXT, INTEGER) db.constr a character string containing the statement at the end of a query indicating constraints dta.func a function which when applied to the input (usually a list) provides a data.frame to be inserted into the database. should.ignore a boolean value indicating whether duplicates implied by the constraints should be ignored upon insertion foreign.keys a list (or NULL) containing several elements named by each table to be joined. The two elements are local.keys which are the columns that should be kept from joining of the two tables and ext.keys which are the columns used in the joining.

Methods

length

signature(obj = "TableSchemaList") Return the number of tables in the object

append

signature(obj = "TableSchemaList"), x, values, after=length(x): Return a new TableSchemaList object consisting of x, the object to be modified, values the object(s) to be added and after the element of x to place them after.

columns

signature(obj = "TableSchemaList") Returns a list of length equal to the number of tables where each element contains columns for the given table.

tables

signature(obj = "TableSchemaList") Returns a vector of the table names in the object.

createTable

signature(obj = "TableSchemaList"), table.name, mode=c("normal", "merge"): Produces a create table statement based on the table specified in table.name and whether the table should be temporary for merging purposes or normal permanent table

insertStatement

signature(obj = "TableSchemaList"), table.name, mode=c("normal", "merge"): Produces an insert statement based on the table specified in table.name and whether the table should be temporary for merging purposes or normal permanent table. This insert statement will be used in conjunction with dbGetPreparedQuery in the RSQLite package and the data.frame resulting from the dta.func function to populate the initial database table.

mergeStatement

signature(obj = "TableSchemaList"), table.name: Produces a statement joining an existing table and a temporary one and inserting into a new (non-temporary) tables

'relationship<-'

signature(obj = "TableSchemaList"), from, to, value: Provides a mechanism to specify how two tables are connected to each other in a database. The arguments from and to should refer to tables in the specified TableSchemaList. The value should be a formula describing how the column(s) correspond to each other. The special value '.' refers to the autoincremented integer column if applicable. The simplest use would be to specify that two tables should be joined on the same column (e.g. column1~column1). Another typical use would be two say that the combination of one or more columns in one table should uniquely identify a row in another table (e.g. .~column1+column2).

'constraint<-'

signature(obj = "TableSchemaList"), obj, table.name, should.ignore=T, constr.name=NULL, value: Allows the specification of uniqueness constraints for a given table (table.name) using the specified columns provided as a single sided formula (e.g. ~ column). should.ignore specifies whether a row of the input dataset should be ultimately ignored if determined to be duplicate in terms of the specified columns, by default it is set to TRUE. By default, constr.name sets the constraint name as 'table.name_idx', this can be changed by specifying constr.name. Setting this to NULL removes the constraint.

Author(s)

Daniel Bottomly

Examples

if (require(Lahman))
{
  baseball.teams <- new("TableSchemaList")
  
  franches <- makeSchemaFromData(TeamsFranchises, name="team_franch")
  
  baseball.teams <- append(baseball.teams, franches)
  
  teams <- makeSchemaFromData(Teams, name="teams")
   
  baseball.teams <- append(baseball.teams, teams)
    
  salaries <- makeSchemaFromData(Salaries, name="salaries")
  
  baseball.teams <- append(baseball.teams, salaries)
    
  relationship(baseball.teams, from="team_franch", to="teams") <- franchID ~ franchID
    
  relationship(baseball.teams, from="teams", to="salaries") <- teamID ~ teamID
  
  constraint(baseball.teams, "team_franch") <- ~franchID
  
  tables(baseball.teams)
  
  columns(baseball.teams)
  
}

dbottomly/poplite documentation built on May 15, 2019, 1:23 a.m.