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

Naming Conventions

File structure

~/projects/itr/itr_documentation/data_model master*
❯ tree
.
├── data
│   └── verify
│       ├── assert_empty_.tsv
│       └── assert_inclusion_entity_name.tsv
├── datamodel_location.md
├── datamodel_order.md
├── datamodel_shipment.md
├── rdb
│   ├── data_entity.tsv
│   ├── data_entity.xlsx
│   ├── data_field.xlsx
└── view
    ├── conceptmodel_sdb.yuml
    ├── datamodel_sdb.yuml
    ├── yuml_data_entity.tsv
    ├── yuml_data_field.tsv
    └── yuml_data_model.csv

Types of files:

datamodel_location.md
  yuml data model
data/view/yuml_data_entity.tsv
  structured and automatically generated
rdb/data_entity.tsv
  structured and manually maintained

export_data_model_to_sample_data_headings

Input:

build_ddl.R

Input: data_field.tsv

| "data_field_aid" | "data_field_name" | "type" | "pk_fk" | "data_entity_aid" |
| 1                | "vehicle_id"      | NA     | ""      | 1                 |
| 2                | "type"            | "TEXT" | ""      | 1                 |
| 3                | "shipment_id"     | NA     | ""      | 2                 |

Input: data_entity.tsv

| "data_entity_aid" | "entity_name" |
| 1                 | "Vehicle"     |
| 2                 | "Shipment"    |

CREATE TABLE Vehicle ( vehicle_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, type TEXT)

create_table_sql_template()

1. CREATE TABLE basic

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

1. Data types of fields added

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)

1. PK columns added

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)

reserved keywords of sql

Reserved keywords of SQL are stored in:

inst/extdata/sql_keywords.csv

Source for the keywords is:

https://www.postgresql.org/docs/current/static/sql-keywords-appendix.html

mertnuhoglu/yuml_to_rdb_schema documentation built on May 15, 2019, 5:03 a.m.