Goal 1 Notes: Create Table and Insert Data
The idea is to take a json object, and insert auto-generate the table and/or columns necessary to insert the data into mysql.
given a json object and target table name:
- if the table doesn't exist then:
- loop through the json object, collecting type information for each key value
- generate a table with the table name and columns
- start back at (1)
- else, i…
The idea is to take a json object, and insert auto-generate the table and/or columns necessary to insert the data into mysql.
given a json object and target table name:
- if the table doesn't exist then:
- loop through the json object, collecting type information for each key value
- generate a table with the table name and columns
- start back at (1)
- else, if the table exists, then:
- verify that the table contains columns for all of the keys in the json object
- if not, then add the appropriate columns (never delete/rename), start back at 2
- determine the unique key of the json object "id" unless otherwise specified
- determine if a row with that id already exists,
- if so, then update the row with the json object
- otherwise insert a new row
- verify that the table contains columns for all of the keys in the json object
if the JSON object contains other objects as its property values, then flatten them into columns, so:
{ "foo" : { "bar1" : "baz1", "bar2" : "baz2"} }
is equivalant to:
{"foo_bar1" : "baz1", "foo_bar2" : "baz2" }
if a property contains an array for a value, then each of those objects should be added to their own mysql table, with a foreign key to the original table's row. so:
{ "foo" : [{ "bar" : "baz1"}, {"bar" : "baz2"}] }
would auto generate a table tablename_foo
, with foreign key column: foo_id
that points to the original foo object. tablename_foo
would also contain the data column bar
, and 2 rows for each baz1
and baz2
would be inserted.