Routines

Retrieves the uploads for a given user.

select *
from tdis_get_uploads_by_user('pbuschow');

Creates a new contact. Returns the new contact ID.

select *
from tdis_update_contact('penumbra@penumbra.com', 'penumbraUpdated2',
                         'veil2', 2031, '555-555-1212', '555-555-1212',
                         'Otters are pretty silly but very cute2.');

select *
from tdis_get_artifacts_by_user_paginated('pbuschow', 1, 2);

select *
from tdis_get_model('TM-932cbc4e-9688-11ec-ba2a-59ebf1ac4488');

select *
from tdis_get_models_by_user_paginated('pbuschow', 1, 2);

Retrieves all jurisdictions.

select * from tdis_get_all_jurisdictions();

select *
from tdis_get_models_by_user_with_ids('pbuschow');

Updates an existing contact.

select *
FROM tdis_update_contact('penumbra@penumbra.com', 'penumbraUpdated2',
                         'veil2', 2031, '555-555-1212', '555-555-1212',
                         'Otters are pretty silly but very cute2.');

Creates a new agency. Returns new agency ID.

select *
from tdis_add_agency('Texas Department of Volcanology', 'TDV');

select *
from tdis_get_models_by_user('pbuschow');

In cases for which an identical filename has been uploaded for the same model in the same path, a conflict will arise. This function returns a set of upload conflicts that may exist for a given user. Note that a synthetic column called conflict_number provides a grouping of conflict sets in the event that there are more than one.

select *
from tdis_get_upload_conflicts_by_user('pbuschow');

Retrieves the ID of a user given the user’s email address.

select *
from tdis_get_user_id_by_email('paul@csr.utexas.edu');

Adds and existing user to a project role

select * from tdis_add_user_to_role('bporter', 'RBFS', 'admin-basic');

Retrieves a jurisdiction for a given ID.

select * from tdis_get_jurisdiction('1');

Updates a collection in the dB.

select *
from tdis_update_collection('TL-c36ee070-9dfe-11ed-a439-afd4c5252924', 4, '01', '{Chain,of,Custody}',
                    'Region 01 Collection', 'Lorem Ipsum', 'Lorem Ipsum',
                    '{{32,1},{37,6}}', null);

Duplicates or clones an existing model creating a completely new record which is unconnected with the original. Returns the new model’s TDIS Identifier.

select *
from tdis_duplicate_model('TM-54de7176-98ea-11ec-ba2a-59ebf1ac4488');

Creates a user. Returns the new user’s ID.

select * from tdis_add_user('turban', '123456', 'turban@tacc.utexas.edu', 'Urban', 'Tomislav',
                          'The University of Texas at Austin', null, '512-909-1086',
                          null);

Retrieves the uploads associated with a given model.

select *
from tdis_get_uploads_by_model('TM-9f36cc8a-93e2-11ec-ba2a-59ebf1ac4488');

Takes a standard web search sting, which can include logic like “and”, “or” and “not” and returns artifacts that match the search string in the title, description or purpose fields.

select * from tdis_search_artifacts('urban or weather');

Retrieves verified jurisdictions.

select * from tdis_get_verified_jurisdictions();

Retrieves a list of all vertical datums.

select *
from tdis_get_vertical_datums();

select *
from tdis_get_layers_by_user_with_ids('pbuschow');

Selects which conflicting files should be chosen as current from amongst multiple possible uploads. This function accepts a TDIS Identifier and the upload batch id which corresponds to the unique upload directory name into which the upload files have been saved. It then archives the files associated with the same TDIS Identifier, but not in that upload batch. It also makes sure that any previously archived files associated with the desired batch are unarchived.

select * from tdis_resolve_batch_conflict('TM-f1f68322-b09b-11ec-b386-256c9c6fe0cd', 'e8ddf3d1-0f35-4f56-9200-ca53450c7325');

select *
from tdis_associate_contact_with_model('TM-9f36cc8a-93e2-11ec-ba2a-59ebf1ac4488', 37, 7);

Sets the status on a list of TDIS Identifiers to accepted.

select *
from tdis_accept_models('{TM-6e34e9ac-9f20-11ec-ba2a-59ebf1ac4488,TM-90bd467a-9d97-11ec-ba2a-59ebf1ac4488}', 'turban', 'Testing multiple model acceptance.');

Creates a new model. Returns the new model’s TDIS Identifier

select *
from tdis_add_model(6, 117, 'Flood Model 68970', '2022-12-13', '2020-07-11', 'Lorem Ipsum', 'Lorem Ipsum', 13,
                2, '{"Dallas|County","Dallas|Municipality"}', '{{6,7},{6,8}}', 'Jane D. Doe', '{TDEM,TxDOT,TNRIS}',
                5,
                1, 'https://web.site.net', 45, 'USGS', 'flood modeling, harvey, hydraulic, river reach',
                'GNU', '012333444555', '{Combined Riverine-Coastal,Hydraulic}', '{013555444}', 'REST', 'HEC-RAS',
                '1.0.2', 'Annually', '{"[2022-02-15, 2022-02-17)","[2022-02-03, 2022-02-09)"}', '{Public}');

select *
from tdis_delete_model('TL-54de7176-98ea-11ec-ba2a-59ebf1ac4488');

Retrieves all contacts.

select * from tdis_get_all_contacts();

select *
from tdis_get_artifact('TA-932cbc4e-9688-11ec-ba2a-59ebf1ac4488');

Duplicates or clones an existing artifact creating a completely new record which is unconnected with the original. Returns the new artifacts’s TDIS Identifier.

select *
from tdis_duplicate_artifact('TA-54de7176-98ea-11ec-ba2a-59ebf1ac4488');

select *
from tdis_get_contact_by_email('bill.shatner@usgs.gov');

select *
from tdis_get_layers_by_user_paginated('pbuschow', 1, 2);

select *
from tdis_get_layers_by_user('pbuschow');

select *
from tdis_get_artifacts_by_user('pbuschow');

select *
from tdis_get_contact_roles();

select * from tdis_delete_contact('arne2@snaefell.is');

Retrieves the archive uploaded with a given model.

select *
from tdis_get_archive_by_model('TM-00a04348-e861-11ec-a632-8fba6188f5ad');

select *
from tdis_get_contact_by_id(32);

select *
from tdis_update_artifact('TA-cc9fbcd2-9dfe-11ed-a439-afd4c5252924', 6, 170, 'Test artifact', '2022-12-15',
                      '2020-07-11',
                      'artifact description. Updated.', 'artifact purpose.',
                      4, '{"Dallas|Municipality","Dallas|County"}',
                      '{{6,7},{6,8}}', 'Creator Name', '{TDEM,TxDOT,TNRIS}',
                      3, 2, 'https://url.org/1234', 120,
                      'GeoTIFF', 'USGS',
                      'Rainfall, Precipitation', 'GNU',
                      '2023-01-05',
                      'Process step description.', 2087,
                      'ArcGIS Web Service', 'HEC-RAS', '3.4.1',
                      'Daily', '{"[2022-02-15, 2022-02-17)","[2022-02-03, 2022-02-09)"}',
                      '{Public}');

Retrieves all models.

select * from tdis_get_all_models();

select *
from tdis_get_contact_id_by_email('bill.shatner@usgs.gov');

select *
from tdis_get_notifications_for_element('TL-9f36cc8a-93e2-11ec-ba2a-59ebf1ac4488');

In cases for which an identical filename has been uploaded for the same model in the same path, a conflict will arise. This function returns a set of upload conflicts that may exist for a given model. Note that a synthetic column called conflict_number provides a grouping of conflict sets in the event that there are more than one.

select *
from tdis_get_upload_conflicts_by_model('TM-9f36cc8a-93e2-11ec-ba2a-59ebf1ac4488');

Takes a standard web search sting, which can include logic like “and”, “or” and “not” and returns layers that match the search string in the title, description or purpose fields.

select * from tdis_search_layers('urban or weather');

Retrieves a list of all horizontal projections or coordinate systems.

select *
from tdis_get_horizontal_projections();

select *
from tdis_get_layer('TL-932cbc4e-9688-11ec-ba2a-59ebf1ac4488');

Duplicates or clones an existing layer creating a completely new record which is unconnected with the original. Returns the new layer’s TDIS Identifier.

select *
from tdis_duplicate_layer('TL-54de7176-98ea-11ec-ba2a-59ebf1ac4488');

Archives any uploads with the same filename, associated object (e.g. Model, Layer or Artifact) and parent object (e.g. directory) as the upload indicated by the provided ID.

select * from tdis_resolve_conflict(856);

Retrieves all contacts associated with a model indicated by TDIS Identifier.

select *
from tdis_get_contacts_by_model('TM-932cbc4e-9688-11ec-ba2a-59ebf1ac4488');

select *
from tdis_get_collections();

Removes an existing user from a project role

select * from tdis_remove_user_from_project_role('bporter', 'RBFS', 'admin-basic');

select *
from tdis_get_history('TM-932cbc4e-9688-11ec-ba2a-59ebf1ac4488');

Sends a message regarding a model given by TDIS Identifier from the provided user.

select *
from tdis_add_message('TM-9f36cc8a-93e2-11ec-ba2a-59ebf1ac4488', 'turban', 'This is a test message.');

Retrieves all jurisdictions for a given project name.

select * from tdis_get_jurisdictions_by_project('MS2');

select *
from tdis_get_messages_for_element('TM-9f36cc8a-93e2-11ec-ba2a-59ebf1ac4488');

Retrieves all layers.

select * from tdis_get_all_layers();

select *
from tdis_get_artifact_with_ids(''TA-932cbc4e-9688-11ec-ba2a-59ebf1ac4488'');'

Returns project roles for user if token is valid and current.

select *
from tdis_authorize_user('spierce', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6NiwiaWF0IjoxNjc1NzE3MTMyLCJleHAiOjE2NzU3NDU5MzJ9.Z3PKugfBx_OOWmAz9WTu2csrVsyAbEa_LXgyKzFVWuU');

Adds a new project to the dB.

select *
from tdis_add_project('My New Project');

select *
from tdis_get_layer_with_ids(''TL-932cbc4e-9688-11ec-ba2a-59ebf1ac4488'');

Sends a notification regarding a model given by TDIS Identifier from the provided user. May include a status change as well.

select *
from tdis_add_notification('TM-9f36cc8a-93e2-11ec-ba2a-59ebf1ac4488', 'turban', 'pending',
                           'This is a test notification.');

Retrieves unverified jurisdictions.

select * from tdis_get_unverified_jurisdictions();

Use this function to change the status on an existing model.

select *
from tdis_set_status('TM-9f36cc8a-93e2-11ec-ba2a-59ebf1ac4488', 'Accepted', 'turban', null);

select *
from tdis_delete_model('TA-54de7176-98ea-11ec-ba2a-59ebf1ac4488');

Resets an existing user’s password.

select *
FROM tdis_reset_password('bporter', '098urt8934ut0893vun0t9uc40589tmu03');

Retrieves a list of all vertical projections or coordinate systems.

select *
from tdis_get_vertical_projections();

Deletes any expired sessions that have not been elegantly deleted by logout.

select * from tdis_clean_sessions();

select *
from tdis_get_collection('TC-c86e508c-8ec3-11ec-ba2a-59ebf1ac4488');

This function retrieves all of the contacts associated with any of the models that the requested user is the creator of. This is intended to function as a sort of get_vendor_contacts until such time as we have a vendor entity.

select * from tdis_get_vendor_contacts('pbuschow');

select * from tdis_get_jurisdiction_types();

select *
from tdis_add_artifact(6, 170, 'Test artifact', '2022-12-15',
                      '2020-07-11',
                      'artifact description', 'artifact purpose.',
                      4, '{"Dallas|Municipality","Dallas|County"}',
                      '{{6,7},{6,8}}', 'Creator Name', '{TDEM,TxDOT,TNRIS}',
                      3, 2, 'https://url.org/1234', 120,
                      'GeoTIFF', 'USGS',
                      'Rainfall, Precipitation', 'GNU',
                      '2023-01-05',
                      'Process step description.', 2087,
                      'ArcGIS Web Service', 'HEC-RAS', '3.4.1',
                      'Daily', '{"[2022-02-15, 2022-02-17)","[2022-02-03, 2022-02-09)"}',
                      '{Public}');

select * from tdis_invalidate_session('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6NiwiaWF0IjoxNjc1NzE3MTMyLCJleHAiOjE2NzU3NDU5MzJ9.Z3PKugfBx_OOWmAz9WTu2csrVsyAbEa_LXgyKzFVWuU');

select *
from tdis_delete_model('TM-54de7176-98ea-11ec-ba2a-59ebf1ac4488');

Retrieves all users from the dB. Note that users are not equivalent to contacts. All users are contacts, but not all contacts are users. Users have a login and can access the system.

select *
from tdis_get_users();

select *
from tdis_get_projects();

Retrieves a list of all software.

select *
from tdis_get_software_list();

select *
from tdis_get_model_with_ids('TM-932cbc4e-9688-11ec-ba2a-59ebf1ac4488');

Retrieves a list of all horizontal datums.

select *
from tdis_get_horizontal_datums();

Creates a new session for an authenticated user. Returns the new session ID.

select *
from tdis_initialize_session('spierce', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6NiwiaWF0IjoxNjc1NzE3MTMyLCJleHAiOjE2NzU3NDU5MzJ9.Z3PKugfBx_OOWmAz9WTu2csrVsyAbEa_LXgyKzFVWuU',
                         '2023-01-08 16:05:06');

Adds a new collection to the dB.

select *
from tdis_add_collection(4, '01', '{Chain,of,Custody}',
                    'Region 01 Collection', 'Lorem Ipsum', 'Lorem Ipsum',
                    '{{32,1},{37,6}}', null);

Takes a standard web search sting, which can include logic like “and”, “or” and “not” and returns models that match the search string in the title, description or purpose fields.

select * from tdis_search_models('urban or weather');

select * from tdis_get_agencies();