Simple relational database shema creator with R. The resulting database (in SQLite) can easily be used using the Firefox add-in "SQLite Manager" (which supports foreign keys). Other options (not tested): http://www.sqlmaestro.com/products/sqlite/phpgenerator/ http://www.sqlitemanager.org/ https://code.google.com/p/phpliteadmin/ http://sqlitestud…

create_shema.R

library(yaml)

# loads the database shema description
db_list <- yaml.load_file("sample3.yaml")

# create the database
con <- dbConnect(SQLite(), "new_db.sqlite")

# create the tables one by one
for (i in 1:length(db_list)){
  dbSendQuery(con, get_query(i, db_list))
}

# see results
dbListTables(con)

get_query <- function(table_nbr, db_list){
    
  # prototype query:
  # CREATE TABLE child_table_name (field_1 INTEGER PRIMARY KEY, field_2 TEXT, foreign_key_field INTEGER , FOREIGN KEY(foreign_key_field) REFERENCES parent_table_name(parent_key_field));
    
  current_tbl_def <- db_list[[table_nbr]]
  current_tablename <- names(db_list)[table_nbr]
  
  query <- paste0("CREATE TABLE '", current_tablename, "' (")
  
  # start loop on fields
  for (current_field in 1:length(current_tbl_def)){
    if (current_field != 1){
      query <- paste0(query, ", ")
    }
    
    current_colname <- names(current_tbl_def)[[current_field]]
    current_type <- current_tbl_def[[current_field]]
    if (current_type == "fk"){
      current_type <- "INTEGER"
    }
    
    piece <- paste0("'",current_colname,"' ", current_type)
    query <- paste0(query, piece)
  } # end loop on fields
  
  # deal with foreign keys
  for (current_field in which(unlist(current_tbl_def) == "fk")){
    current_colname <- names(current_tbl_def)[[current_field]]
    current_type <- current_tbl_def[[current_field]]
    piece <- paste0(", FOREIGN KEY('", current_colname, "') REFERENCES '",
                    current_colname, "'('id')")
    query <- paste0(query, piece)
  }

  query <- paste0(query, ");")
  return(query)  
}

sample3.yaml

impact:
  id: INTEGER PRIMARY KEY
  desc: TEXT
  decay: fk
decay:
  id: INTEGER PRIMARY KEY
  desc: TEXT
  decay_param1: INTEGER
  decay_param2: INTEGER