def save(association_types, db_url=None, connection_func=None):
"""
persists a list of one (or more) association_type, to the warehouse database
Keyword Parameters:
association_types -- list of types for association
db_url -- String, representing a SQLAlchemy connection (Required, if
parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url parameter
is specified.
ValidateException -- raised when a problem is encountered validating a dto
>>> import datetime
>>> empty_list = []
>>> save( [])
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
insert_statement = ('INSERT INTO {schema}.{table} ('
' type_name'
' ,description'
') VALUES ('
' %(name)s'
' ,%(description)s'
')').format(schema=dto_util.SCHEMA, table=TABLE)
dto_util.exec_base(association_types,
validate,
insert_statement,
db_url=db_url,
connection_func=connection_func)
def save(types, db_url=None, connection_func=None):
"""
persists a list of one (or more) table types, to the warehouse database
Keyword Parameters:
types -- list of table types
db_url -- String, representing a SQLAlchemy connection (Required, if
parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url parameter
is specified.
ValidateTypeError -- raised when table_type is not a String
ValidateUnexpectedValue -- raised when table_type is malformed
>>> empty_list = []
>>> save( [])
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
# write out the types -- first, prepare a statement
template = 'INSERT into {schema}.{table} (type_name) values (%s);'
insert_statement = template.format(schema=dto_util.SCHEMA, table=TABLE)
# next compose a one list of bind parameters, for each row to be inserted
insert_binds = [[t] for t in types]
# insert!
dto_util.exec_base(insert_binds,
validate,
insert_statement,
db_url=db_url,
connection_func=connection_func)
def save(variables, db_url=None, connection_func=None):
"""
persists a list of one (or more) variables, to the warehouse database
Keyword Parameters:
variables -- list of variables
db_url -- String, representing a SQLAlchemy connection (Required, if
parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url parameter
is specified.
ValidateException -- raised when a problem is encountered validating a dto
>>> import datetime
>>> empty_list = []
>>> save( [])
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
# prepare statement, to write out the variable metadata
insert_statement = (
'INSERT into {schema}.{variable_table} ( \n'
' column_name \n'
' ,title \n'
' ,table_id \n'
' ,variable_python_type_id \n'
' ,column_type \n'
' ,units \n'
' ,max_length \n'
' ,precision \n'
' ,allowed_values \n'
') values ( \n'
' %(column)s --use the same key name, as the variable dictionary \n'
' ,%(title)s --again, use name of the relevant variable key \n'
' ,(SELECT table_id \n'
' FROM {schema}.{table_table} \n'
' WHERE name=%(table)s ) \n'
' ,(SELECT variable_python_type_id \n'
' FROM {schema}.{python_type_table} \n'
' WHERE constructor_name=%(python_type)s ) \n'
' ,%(physical_type)s \n'
' ,%(units)s \n'
' ,%(max_length)s \n'
' ,%(precision)s \n'
' ,%(allowed_values)s \n'
') \n').format(schema=dto_util.SCHEMA,
variable_table=TABLE,
table_table=table.TABLE,
python_type_table=variable_python_type.TABLE)
# insert! -- directly use tables dictionaries for binds
dto_util.exec_base(variables,
validate,
insert_statement,
db_url=db_url,
connection_func=connection_func)
def save(associations, db_url=None, connection_func=None):
"""
persists a list of one (or more) table associations, to the warehouse db
Keyword Parameters:
associations -- list of associations
db_url -- String, representing a SQLAlchemy connection (Required, if
parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url parameter
is specified.
ValidateException -- raised when a problem is encountered validating a dto
>>> import datetime
>>> empty_list = []
>>> save( [])
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
# prepare statement, to write out the table metadata
insert_statement = (
'INSERT into {schema}.{association_table} ( \n'
' table_id \n'
' ,table_col \n'
' ,parent_table_id \n'
' ,parent_table_col \n'
' ,table_relation_type_id \n'
') values ( \n'
' (SELECT table_id \n'
' FROM {schema}.{table_table} \n'
' WHERE name=%(table)s ) --use association object key names,as the value bind targets \n'
' ,%(column)s --again, use name of relevant key \n'
' ,(SELECT table_id \n'
' FROM {schema}.{table_table} \n'
' WHERE name=%(parent)s ) \n'
' ,%(parent_column)s \n'
' ,(SELECT table_relation_type_id \n'
' FROM {schema}.{association_type_table} \n'
' WHERE type_name=%(type)s ) \n'
')').format(schema=dto_util.SCHEMA,
association_table=TABLE,
table_table=table.TABLE,
association_type_table=association_type.TABLE)
# insert! -- directly use associations dictionaries for binds
dto_util.exec_base(associations,
validate,
insert_statement,
db_url=db_url,
connection_func=connection_func)
def save(authorizations, db_url=None, connection_func=None):
"""
persists a list of one (or more) authorizations, to the warehouse db
Keyword Parameters:
authorizations -- list of authorizations
db_url -- String, representing a SQLAlchemy connection (Required, if
parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url parameter
is specified.
ValidateException -- raised when a problem is encountered validating a dto
>>> import datetime
>>> empty_list = []
>>> save([])
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
# prepare statement, to write out the authorizations
template = """INSERT into {auth_schema}.{authorization_table} (
user_id
,table_id
,nda_signed_date
) values (
%(user_id)s --use the same key name, as the type dictionary
,(SELECT project_id
FROM {support_schema}.{project_table}
WHERE name=%(project)s )
,(SELECT contact_id
FROM {support_schema}.{table_table}
WHERE name=%(table)s
AND project_id=(SELECT project_id
FROM {support_schema}.{project_table}
WHERE name=%(project)s ))
,'1970-01-01'
)"""
insert_statement = template.format(auth_schema=SCHEMA, authorization_table=TABLE
,support_schema=dto_util.SCHEMA
,project_table=project.TABLE
,table_table=table.TABLE
)
# insert! -- directly use authorization dictionaries for binds
dto_util.exec_base(authorizations, validate, insert_statement, db_url=db_url
,connection_func=connection_func)
def delete(associations, db_url=None, connection_func=None):
"""
removes a list of one (or more) table associations, from the warehouse db
Keyword Parameters:
associations -- list of associations
db_url -- String, representing a SQLAlchemy connection (Required, if
parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url parameter
is specified.
ValidateException -- raised when a problem is encountered validating a dto
>>> import datetime
>>> empty_list = []
>>> delete( [])
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
# prepare statement, to remove table relation metadata
delete_statement = (
'DELETE FROM {schema}.{association_table} \n'
'WHERE \n'
' table_id = (SELECT table_id \n'
' FROM {schema}.{table_table} \n'
' WHERE name=%(table)s ) \n'
' AND table_col = %(column)s \n'
' AND parent_table_id = (SELECT table_id \n'
' FROM {schema}.{table_table} \n'
' WHERE name=%(parent)s ) \n'
' AND parent_table_col = %(parent_column)s \n'
' AND table_relation_type_id = (SELECT table_relation_type_id \n'
' FROM {schema}.{association_type_table} \n'
' WHERE type_name=%(type)s ) \n'
).format(schema=dto_util.SCHEMA,
association_table=TABLE,
table_table=table.TABLE,
association_type_table=association_type.TABLE)
# delete! -- directly use associations dictionaries for binds
dto_util.exec_base(associations,
validate,
delete_statement,
db_url=db_url,
connection_func=connection_func)
def save(projects, db_url=None, connection_func=None):
"""
persists a list of one (or more) projects, to the warehouse database
Keyword Parameters:
projects -- list of projects
db_url -- String, representing a SQLAlchemy connection (Required,
if parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url
parameter is specified.
ValidateException -- raised when a problem is encountered validating
a dto
>>> import datetime
>>> empty_list = []
>>> save( [])
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
# prepare statement, to write out the project metadata
template = ('INSERT into {schema}.{table} ( \n'
' name \n'
' ,title \n'
' ,inport_data_set_id \n'
' ,csw_uuid \n'
' ) values ( \n'
' %(name)s --use the same key name, as the type dictionary \n'
' ,%(title)s \n'
' ,%(inport_id)s \n'
' ,%(uuid)s \n'
' )')
insert_statement = template.format(schema=dto_util.SCHEMA, table=TABLE)
# insert! -- directly use projects dictionaries for binds
dto_util.exec_base(projects,
validate,
insert_statement,
db_url=db_url,
connection_func=connection_func)
def update_by_table_column(table_name,
column,
variable,
db_url=None,
connection_func=None):
"""
updates a variable (identified by name) in the warehouse database
Keyword Parameters:
table_name -- String, table which the variable to update belongs to
column -- String, representing the column of the variable to update
variable -- Dict, representing the new variable values (May change
the table and/or column name!)
db_url -- String, representing a SQLAlchemy connection (Required,
if parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url
parameter is specified.
ValidateException -- raised when a problem is encountered validating
a dto
>>> var1 = { 'column':'frob_hz', 'title':'Frobniz Resonance (Hz)'
... ,'python_type': 'float', 'physical_type': 'numeric'
... ,'table': 'some_table', 'max_length': 147456
... ,'units': 'hertz', 'precision': 16383
... ,'description': 'A useful value!'
... ,'allowed_values': '1+e-16383 - 9e147456'}
>>> update_by_table_column('some_table', 'frob_hz', var1)
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
# prepare statement, to update table metadata
template = """UPDATE {schema}.{table} SET
column_name = %(column)s
,title = %(title)s
,description = %(desecription)s
,table_id = (SELECT table_id
FROM {schema}.{table_table}
WHERE name=%(table)s )
,variable_python_type_id = (
SELECT variable_python_type_id
FROM {schema}.{python_type_table}
WHERE constructor_name=%(python_type)s )
,column_type = %(physical_type)s
,units = %(units)s
,max_length = %(max_length)s
,precision = %(precision)s
,allowed_values = %(allowed_values)s
WHERE column_name = %(arg_column)s
AND table_id = (SELECT table_id
FROM {schema}.{table_table}
WHERE name=%(arg_table_name)s )"""
update_statement = template.format(
schema=dto_util.SCHEMA,
table=TABLE,
table_table=table.TABLE,
python_type_table=variable_python_type.TABLE)
# update! -- directly use tables dictionary for binds
dto_util.exec_base([variable],
validate,
update_statement,
db_url=db_url,
connection_func=connection_func,
additional_binds={
'arg_table_name': table_name,
'arg_column': column
})
def update_by_table_column(table_name,
column_name,
association,
db_url=None,
connection_func=None):
"""
updates one association,referenced by the table & its lookup field
Keyword Parameters:
table_name -- String, representing the name of the association
Table
column_name -- String, representing name of table_name's
association Column
association -- DWSupport association Data Transfer Object defining
the new association values (may *change* table_name+column_name)
db_url -- String, representing a SQLAlchemy connection (Required, if
parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url parameter
is specified.
ValidateException -- raised when a problem is encountered validating a dto
>>> import datetime
>>> assoc1 = {'parent': 'depth_dim', 'parent_column': 'depth_whid', \
'table': 'catch_fact', 'column': 'depth_whid', 'type':'fact dimension'}
>>> update_by_table_column('any_table', 'any_column', assoc1)
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
# prepare statement, to update the table metadata
update_statement = (
'UPDATE {schema}.{association_table} SET \n'
' table_id = (SELECT table_id --use name of relevant key \n'
' FROM {schema}.{table_table} \n'
' WHERE name=%(table)s ) \n'
' ,table_col = %(column)s \n'
' ,parent_table_id = (SELECT table_id \n'
' FROM {schema}.{table_table} \n'
' WHERE name=%(parent)s ) \n'
' ,parent_table_col = %(parent_column)s\n'
' ,table_relation_type_id = (SELECT table_relation_type_id \n'
' FROM {schema}.{association_type_table} \n'
' WHERE type_name=%(type)s ) \n'
' ,aud_beg_dtm = current_timestamp \n'
'WHERE \n'
' table_id = (SELECT table_id --use name of relevant key \n'
' FROM {schema}.{table_table} \n'
' WHERE name=%(arg_table_name)s ) \n'
' AND table_col = %(arg_column_name)s \n').format(
schema=dto_util.SCHEMA,
association_table=TABLE,
table_table=table.TABLE,
association_type_table=association_type.TABLE)
# update! -- directly use association dictionarie for binds
dto_util.exec_base([association],
validate,
update_statement,
db_url=db_url,
connection_func=connection_func,
additional_binds={
'arg_table_name': table_name,
'arg_column_name': column_name
})
def update_by_name(name, table, db_url=None, connection_func=None):
"""
updates a table (identified by name) in the warehouse database
Keyword Parameters:
name -- String, name of the table to update
table -- Dict, representing the new table values (May change the name!)
db_url -- String, representing a SQLAlchemy connection (Required, if
parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url parameter
is specified.
ValidateException -- raised when a problem is encountered validating a dto
>>> import datetime
>>> tab1 = { 'name':'catch_fact', 'type':'fact'
... ,'updated': datetime.datetime(2016, 1, 1)
... ,'rows': 999, 'project': 'FRAM Trawl Survey'
... ,'contact': 'Name: Awesome Pat <*****@*****.**>'
... ,'years': '1971-1972, 1989, 2016', 'selectable': True
... ,'inport_id': None, 'description': None, 'uuid': None
... ,'inport_replacement_project_id': None
... ,'title': 'Survey Catch', 'update_frequency': 'continually'
... ,'restriction': 'otherRestrictions', 'usage_notice': None
... ,'keywords': None, 'bounds': '80.0, -110.5, 60.06, -134'
... ,'confidential': False}
>>> update_by_name('any_table', tab1)
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
# prepare statement, to update table metadata
template = """UPDATE {schema}.{table} SET
name = %(name)s
,data_years = %(years)s
,table_type_id = (SELECT table_type_id
FROM {schema}.{type_table}
WHERE type_name=%(type)s )
,num_rows = %(rows)s
,project_id = (SELECT project_id
FROM {schema}.{project_table}
WHERE name=%(project)s )
,contact_id = (SELECT contact_id
FROM {schema}.{contact_table}
WHERE info=%(contact)s )
,aud_beg_dtm = CURRENT_TIMESTAMP
,is_selectable = %(selectable)s
,inport_entity_id = %(inport_id)s
,inport_replacement_project_id = %(inport_replacement_project_id)s
,description = %(description)s
,title = %(title)s
,csw_uuid = %(uuid)s
,table_update_frequency_id = (
SELECT table_update_frequency_id
FROM {schema}.table_update_frequency
WHERE iso_maintenance_update_code=%(update_frequency)s )
,table_use_constraint_id = (SELECT table_use_constraint_id
FROM {schema}.table_use_constraint
WHERE gmd_code=%(restriction)s )
,usage_notice = %(usage_notice)s
,keywords = %(keywords)s
,north_bound = %(north_bound)s
,east_bound = %(east_bound)s
,south_bound = %(south_bound)s
,west_bound = %(west_bound)s
WHERE name = %(arg_table_name)s """
update_statement = template.format(schema=dto_util.SCHEMA, table=TABLE
,type_table=table_type.TABLE
,project_table=project.TABLE
,contact_table=contact.TABLE
)
# update! -- directly use tables dictionary for binds
dto_util.exec_base( [table], _validate_and_split_bounds, update_statement, db_url=db_url
,connection_func=connection_func
,additional_binds={'arg_table_name': name})
def save(tables, db_url=None, connection_func=None):
"""
persists a list of one (or more) tables, to the warehouse database
Keyword Parameters:
tables -- list of tables
db_url -- String, representing a SQLAlchemy connection (Required, if
parameter 'connection' is not provided.
connection_func -- function returning SQLAlchemy connections
(Optional, if provided, will override db_url)
Exceptions:
ExecMissingArguments -- raised if neither connection or db_url parameter
is specified.
ValidateException -- raised when a problem is encountered validating a dto
>>> import datetime
>>> empty_list = []
>>> save( [])
Traceback (most recent call last):
...
api.resources.source.warehouse.support.dto_util.ExecMissingArgument
"""
# prepare statement, to write out the table metadata
template = """INSERT into {schema}.{table} (
name
,project_id
,contact_id
,data_years
,table_type_id
,aud_beg_dtm
,is_selectable
,inport_entity_id
,inport_replacement_project_id
,description
,num_rows
,title
,csw_uuid
,table_update_frequency_id
,table_use_constraint_id
,usage_notice
,keywords
,north_bound
,east_bound
,south_bound
,west_bound
,is_sensitive
) values (
%(name)s --use the same key name, as the type dictionary
,(SELECT project_id
FROM {schema}.{project_table}
WHERE name=%(project)s )
,(SELECT contact_id
FROM {schema}.{contact_table}
WHERE info=%(contact)s )
,%(years)s
,(SELECT table_type_id
FROM {schema}.{type_table}
WHERE type_name=%(type)s ) --again, use name of relevant key
,%(updated)s
,%(selectable)s
,%(inport_id)s
,%(inport_replacement_project_id)s
,%(description)s
,%(rows)s
,%(title)s
,%(uuid)s
,(SELECT table_update_frequency_id
FROM {schema}.table_update_frequency
WHERE iso_maintenance_update_code=%(update_frequency)s )
,(SELECT table_use_constraint_id
FROM {schema}.table_use_constraint
WHERE gmd_code=%(restriction)s )
,%(usage_notice)s
,%(keywords)s
,%(north_bound)s
,%(east_bound)s
,%(south_bound)s
,%(west_bound)s
,%(confidential)s
)"""
insert_statement = template.format(schema=dto_util.SCHEMA, table=TABLE
,type_table=table_type.TABLE
,project_table=project.TABLE
,contact_table=contact.TABLE
)
# postgresql can't insert NULL into non-null fields (even with defaults)
for t in tables:
if t['rows'] is None:
t['rows'] = -1 # rows: Table might not even exist, yet
if t['updated'] is None:
t['updated'] = datetime.now() #multiple insert statements is too hard
if t['project'] is None:
t['project'] = 'warehouse' # DB default, is to use the warehouse
if t['contact'] is None:
t['contact'] = 'Name: FRAM Data Team <*****@*****.**>'
if t['update_frequency'] is None:
t['update_frequency'] = 'continual'
# insert! -- directly use tables dictionaries for binds
dto_util.exec_base( tables, _validate_and_split_bounds, insert_statement, db_url=db_url
,connection_func=connection_func)