fleetside72/tps_etl

small etl application retaining source data and layering extractions

tps_etl's Issues

inserting json - constraint does not prevent duplication

change source name

if the source name changes, what happens to all the associated data

maintain a global list of keys

maintain a list of all object keys

auto build views

unless the schema is unchanged, build views to reflect at srce_set

srce set on diff

only update the source if the definition is actually different

srce_import does not properly trap errors and should be renamed to _csv

x

change in constraint

if the constraint is changing, consider rebuilding for existing rows

map set additional actions

if i call the map overwrite function after writing to the map table, I cannot rollback since transactions dont apply inside a function

-> put a an update trigger than can fail the original insert
-> if the return value of map overwrite is fail, leave it and notify the application
-> dont build sql into functions but instead leave it in files and implement transactions that way

add option to delete a source definition

map on insert

apply mappings at insert time

• in srce_insert function
  -or-
• statement level trigger

import function approach

the current import function should be rename to file_import since it only uses COPY to push files into the database

another approach might be to have the client application push records directly to tps.trans and forego even calling a database level function

test

cast double quote value to date or numeric

if a schema extracts a json key and it's value is like this {"date_field":""} it will not cast to a date.

• set paths that extract as date or numeric to null if double quote
  • would require specifying type for each constraint
• swap double quote for null at query time somehow

validate map set

if maps definitions are passed to the set function as an array of objects they are not validated.
create map set function to allow for:

• validate and skip if nothing changing
• rebuild maps if the definition is changing

regex helper

function to run regex on dataset to test for creating map_rm record

convert function to only require single json as argument

each function should only require a json object so as to simplify the SQL involved

update readme files