R/rdb_to_ddl.R

In mertnuhoglu/yuml_to_rdb_schema: Converts yuml Data Model into rdb Data Schema Specs

#!/usr/local/bin/Rscript

#' @importFrom magrittr "%>%"
NULL
#' @import data.table
NULL


#' @export
rdb_to_ddl = function(data_entity, data_field) {
  get_den_pk = function(den, dfl) {
    dfl_pk = dfl %>%
      dplyr::filter(pk_fk == "PK") %>%
      dplyr::select(-pk_fk) %>%
      dplyr::select(pk_data_field_id = data_field_id, fk_data_entity_id = data_entity_id, pk_data_field_name = data_field_name)
    den %>%
      dplyr::select(data_entity_id, entity_name) %>%
      dplyr::left_join( dfl_pk, by = c("data_entity_id" = "fk_data_entity_id")) %>%
      dplyr::select(data_entity_id, fk_data_entity_name = entity_name, fk_data_field_name = pk_data_field_name) 
  }
  get_dfl_fk = function(den, dfl) {
    dfl_fk = dfl %>%
      dplyr::filter(pk_fk == "FK") %>%
      dplyr::select(-pk_fk) %>%
      dplyr::left_join(get_den_pk(den, dfl), by = c("fk_data_entity_id" = "data_entity_id"))
  }
  den = data_entity 
  dfl = data_field 
  den_pk = get_den_pk(den, dfl)
  loop_create_table_sql = function(deid, dfl, den) {
    fd = dplyr::filter(dfl, data_entity_id == deid) %>%
      dplyr::left_join(den, by = "data_entity_id")
    table_name = unique(fd$entity_name)
    non_keys = dplyr::filter(fd, pk_fk == "NON_KEY")
    pks = dplyr::filter(fd, pk_fk == "PK")
    fks = dplyr::filter(fd, pk_fk == "FK") %>%
      dplyr::left_join(den_pk, by = c( "fk_data_entity_id" = "data_entity_id" ))
    tibble::tibble( 
                   data_entity_id = deid 
                   , sql_create_table = create_table_sql( table_name, non_keys, pks, fks )
                   )
  }
  ids = dplyr::distinct(dfl, data_entity_id)$data_entity_id
  lapply(ids, loop_create_table_sql, dfl, den) %>%
    dplyr::bind_rows()
}

#' @export
create_table_sql = function(table_name, non_keys, pks, fks) {
	# usage:
	# template = create_table_sql( "t_c_bonitet_bali", names(cbb) )
	# example output:
  # "CREATE TABLE Vehicle ( vehicle_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, vehicle_type TEXT)"
  template_pk = "%s BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY"
  sql_pk = sprintf(template_pk
                   , pks$data_field_name
                   )
  template_fk = "%s BIGINT %s REFERENCES %s (%s)"
  sql_fk = sprintf(template_fk
                   , fks$data_field_name
                   , ifelse(fks$not_null == TRUE, "NOT NULL", "")
                   , fks$fk_data_entity_name
                   , fks$fk_data_field_name
                   )
  template_nonkey = "%s %s %s"
  sql_nonkey = sprintf(template_nonkey
                       , non_keys$data_field_name
                       , non_keys$type
                       , ifelse(non_keys$not_null == TRUE, "NOT NULL", "")
                       )
  column_sql = c(sql_pk, sql_fk, sql_nonkey)
  column_names = column_sql %>% paste(collapse=", ")
	template = "CREATE TABLE %s (%s);"
	create_table = sprintf( template, table_name, column_names)
	return(create_table)
}

study_create_table_sql = function() {
  table_name = "Vehicle"
  columns = c("vehicle_id", "vehicle_type")
	template = "    CREATE TABLE %s (%s);"
	column_names = columns %>% paste(collapse=",")
	result = sprintf( template, table_name, column_names)

  library(magrittr)
  table_name = "Vehicle"
  columns = c("vehicle_id", "vehicle_type")
  types = c("BIGINT", "TEXT")
	template = "    CREATE TABLE %s (%s);"
  template_column = "%s %s"
  column_sql = sprintf(template_column, columns, types)
  column_sql
	column_names = column_sql %>% paste(collapse=",")
	sprintf( template, table_name, column_names)

  library(magrittr)
  table_name = "Vehicle"
  columns = c("vehicle_type")
  types = c("TEXT")
	template = "    CREATE TABLE %s (%s);"
  pks = c("vehicle_id")
  template_pk = "%s BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY"
  pk_sql = sprintf(template_pk, pks)
  pk_sql
  template_column = "%s %s"
  column_sql = sprintf(template_column, columns, types)
  column_sql = c(pk_sql, column_sql)
  column_sql
	column_names = column_sql %>% paste(collapse=",")
	sprintf( template, table_name, column_names)
}