getCreateSQL {xml2relational}

Exporting the relational data model and data to a database

Description

Produces ready-to-run SQL INSERT statements to import the data transformed with toRelational() into a SQL database.

Usage

getCreateSQL(
  ldf,
  sql.style = "MySQL",
  tables = NULL,
  prefix.primary = "ID_",
  prefix.foreign = "FKID_",
  line.break = "\n",
  datatype.func = NULL,
  one.statement = FALSE
)

Arguments

ldf

A list of dataframes created by toRelational() (the data tables transformed from XML to a relational schema).

sql.style

The SQL flavor that the produced CREATE statements will follow. The supported SQL styles are "MySQL", "TransactSQL" and "Oracle". You can add your own SQL flavor by providing a dataframe with the required information instead of the name of one of the predefined SQL flavors as value for sql.style. See the Details section for more information on working with different SQL flavors.

tables

A character vector with the names of the tables for whichs SQL CREATE statements will be produced. If null (default) CREATE statements will be produced for all tables in in the relational data model of ldf.

prefix.primary

The prefix that is used in the relational data model of ldf to identify primary keys. "ID_" by default.

prefix.foreign

The prefix that is used in the relational data model of ldf to identify foreign keys. "FKID_" by default.

line.break

Line break character that is added to the end of each CREATE statement (apart from the semicolon that is added automatically). Default is "\n".

datatype.func

A function that is used to determine the data type of the table fields. The function must take the field/column from the data table (basically the result of SELCT field FROM table) as its sole argument and return a character vector providing the data type. If null (default), the built-in mechanism will be used to determine the data type.

one.statement

Determines whether all CREATE statements will be returned as one piece of SQL code (one.statement = TRUE) or if each CREATE statement will be stored in a separate element of the return vector.

Details

If you want to produce SQL CREATE statements that follow a different SQL dialect than one of the built-in SQL flavors (i.e. MySQL, TransactSQL and Oracle) you can provide the necessary information to getCreateSQL() via the sql.style argument. In this case the sql.style argument needs to be a dataframe with the folling fields:

Column	Type	Description	Example
Style	character	Name of the SQL flavor.	"MySQL"
NormalField	character	Template string for a normal, nullable field.	"%FIELDNAME% %DATATYPE%"
NormalFieldNotNull	character	Template string for non-nullable field.	"%FIELDNAME% %DATATYPE% NOT NULL"
PrimaryKey	character	Template string for the definition of a primary key.	"PRIMARY KEY (%FIELDNAME%)"
ForeignKey	character	Template string for the definition of a foreign key. "FOREIGN KEY (%FIELDNAME%) REFERENCES %REFTABLE%(%REFPRIMARYKEY%)"
PrimaryKeyDefSeparate	logical	Indicates if primary key needs additional definition like a any other field.	TRUE
ForeignKeyDefSeparate	logical	Indicates if foreign key needs additional definition like a any other field.	TRUE
Int		character	Name of integer data type. "INT"
Int.MaxSize	numeric	Size limit of integer data type.	4294967295
BigInt	character	Name of data type for integers larger than the size limit of the normal integer data type.	"BIGINT"
Decimal	character	Name of data type for floating point numbers.	"DECIMAL"
VarChar	character	Name of data type for variable-size character fields.	"VARCHAR"
VarChar.MaxSize	numeric	Size limit of variable-size character data type.	65535
Text	character	Name of data type for string data larger than the size limit of the variable-size character data type.	"TEXT"
Date	character	Name of data type date data.	"DATE"
Time	character	Name of data type time data	"TIME"
Date	character	Name of data type for combined date and time data.	"TIMESTAMP"

In the template strings you can use the following placeholders, as you also see from the MySQL example in the table:

1. %FIELDNAME%: Name of the field to be defined.

2. %DATATYPE%: Datatype of the field to be defined.

3. %REFTABLE%: Table referenced by a foreign key.

4. %REFPRIMARYKEY%: Name of the primary key field of the table referenced by a foreign key.

When you use your own defintion of an SQL flavor, then sql.style must be a one-row dataframe providing the fields described in the table above.

You can use the datatype.func argument to provide your own function to determine how the data type of a field is derived from the values in that field. In this case, the values of the columns Int, Int.MaxSize, VarChar, VarChar.MaxSize, Decimal and Text in the sql.style dataframe are ignored. They are used by the built-in mechanism to determine data types. Providing your own function allows you to determine data types in a more differentiated way, if you like. The function that is provided needs to take a vectors of values as its argument and needs to provide the SQL data type of these values as a one-element character vector.

Value

A character vector with exactly one element (if argument one.statement = TRUE) or with one element per CREATE statement.

See Also

Other xml2relational: getInsertSQL(), savetofiles(), toRelational()

Examples

# Find path to custmers.xml example file in package directory
path <- system.file("", "customers.xml", package = "xml2relational")
db <- toRelational(path)

sql.code <- getCreateSQL(db, "TransactSQL", "address")