Create JavaScript SQL Table Creation Template Function

[TimAConner]

Description

This pull request introduces a function that will automate the creation, possible dropping, and insertion of tables into our database.

Number of Fixes

Type of number of fixes included in this PR.

Related Ticket(s)

This is where Issue #50 arrived after discussion with the group.

Proposed Changes

Adds a function that will automate sql database generation.

Expected Behavior

When you run the code below, it should create a table, dropping an old one if it there, and insert the information referenced in the dataToIterateOver variable.

generateSqlTable(
  {
    tableName: `computers`,
    columns: 
      `computer_id INTEGER PRIMARY KEY,
      purchase_date TEXT,
      decommission_date TEXT,
      a_number INTEGER,
      thirty_seven INTEGER`,
    dataToIterateOver: computers,
    valuesToInsert: [
      null,
      `purchaseDate`, 
      `decommissionDate`, 
      `aNumber`, 
      37]
  }
);

Steps to Test Solution

1. Require in the file sqlRunTemplate.js
2. Follow the documentation in running it, or use the below example code, which should create a table called computers. You will have to use Jordan's Computer JSON data to test. This data would be put into the variable computers.

   generateSqlTable(
   {
   tableName: `computers`,
   columns: 
     `computer_id INTEGER PRIMARY KEY,
     purchase_date TEXT,
     decommission_date TEXT,
     a_number INTEGER,
     thirty_seven INTEGER`,
   dataToIterateOver: computers,
   valuesToInsert: [
     null,
     `purchaseDate`, 
     `decommissionDate`, 
     `aNumber`, 
     37]
   }
   );

Documentation

The generateSqlTable function takes an object with the below properties.

1. The tableName is the name of the table. What will be created, dropped, or inserted into.
2. The columns property contains sql code that would normally be in the parenthesis after the CREATE TABLE statment.

   `computer_id INTEGER PRIMARY KEY,
     purchase_date TEXT,
     decommission_date TEXT,
     a_number INTEGER,
     thirty_seven INTEGER`

3. The dataToIterateOver property contains an array of objects.
4. The valuesToInsert is an array containing integers, null, or strings. These are the values that will be inserted into the columns specified in the columns property. They are inserted in the same order that the columns are defined. If the value is a string, it will be interpreted as a property of an individual object in the dataToiterateOver property. If the value is null or a number, it will be inserted as that exact value.

   generateSqlTable(
   {
   tableName: `<table name>`,
   columns: `<sql column code>`,
   dataToIterateOver: <Array of Objects>,
   valuesToInsert: [<integers, null, or strings]
   }
   );

[dealWIPdev]

🖖