In sumprain/dbMapR: Functions and classes to access Database

Overview of the structure of the database front end system

There will be three types of classes:

1. Database
2. Table
3. Column

Column class

Property

• name
• name of mother table
• name of shiny widget (tableName_name)
• whether PK
• whether FK (if so):
  • table name of reference
  • column name for valCol
  • column name for textCol
• type of data
• isRequired
• default value
• whether options (if yes):
  • values provided by user
  • if FK, then the details given above

methods

• initialise: This method is the constructor method which will initialise a new column class with values obtained from database.
• set_*: These methods will allow the users to set the properties.

Table class

Properties

• name
• namePKColumn
• names of columns
• names of tables to which it references

Methods

• insert
• alter
• delete row
• nextPK: Method which will get next PK value if the column is PK.

Creating new table with %>% way

table(name, ...) %>% cols(name = type, ...) %>% pk(name) %>% fk(name, ref_table, ref_col, update_rule, delete_rule) %>% fk(name, ref_table, ref_col, update_rule, delete_rule) %>% required(name, ...) %>% unique(name, ...) %>% defaultVal(col1 = val1, ...) %>% create_table(src)

The above statement will convert into a SQL statement which will be entered into dbSendQuery statement to get a new table.

It is assumed that new tables will be created by humans and data will entered into a database by machine.

table will create a new table object and cols will populate it with column objects. Rest of the functions can appear in any fashion and will populate the column objects.

create_table will assimilate all the inputs and run dbSendQuery and create a new table.

Validation of inputs

Validation needs to be done at column class level. Column class will have a validation_rule field which will store ae experession in form of . > 10, . meaning the value. The validation will be set by the user after the column class is made out of the database by dbDatabaseClass$new("extract_from_db").

What to do after successful insertion into database?

It will stored with cache value with pkid and timestamp as attribute and identifier. Old data will be automatically removed in FIFO format, length will be determined by cacheVal.

Updating values in database

Updating the values of variables in database is going to be complicated affair. It involves the following components:

1. Retreiving values from database table as a row of columns given pk_id value. it will be contain one row.

2. Sending the values to the corresponding dbColumnClass field update_container. The field will point to an environment with parent environment emptyenv. It will have four fields: orig_val, modified_val, pk_id, time_stamp. Environment will be locked and pk_id, time_stamp will also be locked for any modification. modified_val will be checked for accuracy (as for valToDB) before accepting the value.

3. It will select all the columns which are modified and make UPDATE SQL command. Before executing UPDATE command, it will extract the time_stamp value and check it with earlier time_stamp value. UPDATE command will be run only when both the time stamps are same. time_stamp will be updated automatically as for INSERT command.

4. After successful UPDATE, the values will be stored as list in FIFO fashion as for INSERT function.

sumprain/dbMapR documentation built on May 30, 2019, 8:37 p.m.