def pg_tables(schema="public", exclude_lookups=False):
"""
Returns all the tables in the given schema minus the default PostGIS tables.
Views are also excluded. See separate function for retrieving views.
"""
t = text("SELECT table_name FROM information_schema.tables WHERE table_schema = :tschema and table_type = :tbtype " \
"ORDER BY table_name ASC")
result = _execute(t,tschema = schema,tbtype = "BASE TABLE")
pgTables = []
for r in result:
tableName = r["table_name"]
#Remove default PostGIS tables
tableIndex = getIndex(_postGISTables, tableName)
if tableIndex == -1:
if exclude_lookups:
#Validate if table is a lookup table and if it is, then omit
rx = QRegExp("check_*")
rx.setPatternSyntax(QRegExp.Wildcard)
if not rx.exactMatch(tableName):
pgTables.append(tableName)
else:
pgTables.append(tableName)
return pgTables
def __init__(self, parent, source_cols, dest_table, dest_col, src_col):
QDialog.__init__(self, parent)
self.setupUi(self)
TranslatorDialogBase.__init__(self, source_cols, dest_table, dest_col, src_col)
self._notif_bar = NotificationBar(self.vl_notification)
# Container of column names for an enumeration table
self._enum_col_names = []
self._load_separators()
# Init user selection to the corresponding UI controls
self.txt_source_col.setText(self._src_col)
# Remove selected column name from the 'other column' list
sel_col_idx = getIndex(self._source_cols, self._src_col)
if sel_col_idx != -1:
self._source_cols.remove(self._src_col)
# Load source columns
self.cbo_src_other_col.addItem("")
self.cbo_src_other_col.addItems(self._source_cols)
# Load database tables
self.cbo_enum_table.addItem("")
self.cbo_enum_table.addItems(self.db_tables())
# Connect signals
self.cbo_enum_table.currentIndexChanged.connect(self._on_enum_table_changed)
self.cbo_primary_col.currentIndexChanged.connect(self._on_primary_col_changed)
def unique_column_values(tableName,columnName,quoteDataTypes=["character varying"]):
"""
Select unique row values in the specified column.
Specify the data types of row values which need to be quoted. Default is varchar.
"""
dataType = columnType(tableName,columnName)
quoteRequired = getIndex(quoteDataTypes, dataType)
sql = "SELECT DISTINCT {0} FROM {1}".format(columnName,tableName)
t = text(sql)
result = _execute(t)
uniqueVals = []
for r in result:
if r[columnName] == None:
if quoteRequired == -1:
uniqueVals.append("NULL")
else:
uniqueVals.append("''")
else:
if quoteRequired == -1:
uniqueVals.append(str(r[columnName]))
else:
uniqueVals.append("'{0}'".format(str(r[columnName])))
return uniqueVals
def pg_table_exists(table_name, include_views=True, schema="public"):
"""
Checks whether the given table name exists in the current database
connection.
:param table_name: Name of the table or view. If include_views is False
the result will always be False since views have been excluded from the
search.
:type table_name: str
:param include_views: True if view names will be also be included in the
search.
:type include_views: bool
:param schema: Schema to search against. Default is "public" schema.
:type schema: str
:return: True if the table or view (if include_views is True) exists in
currently connected database.
:rtype: bool
"""
tables = pg_tables(schema=schema)
if include_views:
tables.extend(pg_views(schema=schema))
if getIndex(tables, table_name) == -1:
return False
else:
return True
def _map_column_values(self, feature, feature_defn, source_cols):
"""
Retrieves values for specific columns from the specified feature.
:param feature: Input feature.
:type feature: ogr.Feature
:param feature_defn: Feature definition for the layer.
:type feature_defn: ogr.FeatureDefn
:param source_cols: List of columns whose respective values will be
retrieved.
:type source_cols: list
:return: Collection containing pairs of column names and corresponding
values.
:rtype: dict
"""
col_values = {}
if len(source_cols) == 0:
return col_values
for f in range(feature_defn.GetFieldCount()):
field_defn = feature_defn.GetFieldDefn(f)
field_name = field_defn.GetNameRef()
match_idx = getIndex(source_cols, field_name)
if match_idx != -1:
field_value = feature.GetField(f)
col_values[field_name] = field_value
return col_values
def pg_tables(schema="public"):
"""
Returns all the tables in the given schema minus the default PostGIS tables.
Views are also excluded. See separate function for retrieving views.
"""
t = text("SELECT table_name FROM information_schema.tables WHERE table_schema = :tschema and table_type = :tbtype " \
"ORDER BY table_name ASC")
result = _execute(t, tschema=schema, tbtype="BASE TABLE")
pgTables = []
for r in result:
tableName = r["table_name"]
#Remove default PostGIS tables
tableIndex = getIndex(_postGISTables, tableName)
if tableIndex == -1:
#Validate if table is a lookup table and if it is, then omit
rx = QRegExp("check_*")
rx.setPatternSyntax(QRegExp.Wildcard)
if not rx.exactMatch(tableName):
pgTables.append(tableName)
return pgTables
def referencing_column_value(self, field_values):
"""
Searches a corresponding record from the linked table using one or more
pairs of field names and their corresponding values.
:param field_values: Pair of field names and corresponding values i.e.
{field1:value1, field2:value2, field3:value3...}
:type field_values: dict
:return: Value of the referenced column in the linked table.
:rtype: object
"""
link_table_columns = self._table_columns(self._referenced_table)
query_attrs = {}
for source_col, val in field_values.iteritems():
ref_table_col = self._input_referenced_columns.get(source_col, None)
if not ref_table_col is None:
col_idx = getIndex(link_table_columns, ref_table_col)
#If column is found, add it to the query fields collection
if col_idx != -1:
query_attrs[ref_table_col] = val
#Create link table object
link_table = self._table(self._referenced_table)
#Use AND operator
link_table_rec = self._db_session.query(link_table).filter_by(**query_attrs).first()
if link_table_rec is None:
return IgnoreType()
else:
return getattr(link_table_rec, self._output_referenced_column, IgnoreType())
def run_checks(self):
"""
Assert translator configuration prior to commencing the translation
process. This includes checking whether the defined tables and respective
columns exist.
:return: Whether the translator settings are correct.
:rtype: bool
"""
res = False
#Check destination table
if self._table(self._referencing_table) is None:
msg = QApplication.translate("SourceValueTranslator",
"Target table '%s' does not exist."
% (self._referencing_table))
raise TranslatorException(msg)
#Check destination column
dest_columns = self._table_columns(self._referencing_table)
referencing_col_idx = getIndex(dest_columns, self._referencing_column)
if referencing_col_idx == -1:
msg = QApplication.translate("SourceValueTranslator",
"Target column '%s' does not exist."
% (self._referencing_column))
raise TranslatorException(msg)
#Check link table
if self._table(self._referenced_table) is None:
msg = QApplication.translate("SourceValueTranslator",
"Linked table '%s' does not exist."
% (self._referenced_table))
raise TranslatorException(msg)
return res
def _addDocumentReference(self,docid):
'''
Add a document reference to the list that the document manager
contains
'''
docIndex = getIndex(self._docRefs,docid)
if docIndex == -1:
self._docRefs.append(docid)
def _addDocumentReference(self, docid):
'''
Add a document reference to the list that the document manager
contains
'''
docIndex = getIndex(self._docRefs, docid)
if docIndex == -1:
self._docRefs.append(docid)
def _onRecordSelectedEntityBrowser(self, rec, row_number=-1):
'''
Slot raised when the user has clicked the select button in the 'EntityBrowser' dialog
to add the selected record to the table's list.
Add the record to the foreign key table using the mappings.
'''
#Check if the record exists using the primary key so as to ensure only one instance is added to the table
try:
if isinstance(rec, int):
recIndex = getIndex(self._recordIds(), rec)
if recIndex != -1:
return
dbHandler = self._dbModel()
modelObj = dbHandler.queryObject().filter(self._dbModel.id == rec).first()
elif isinstance(rec, object):
modelObj = rec
else:
return
except:
pass
dbHandler = self._dbModel()
modelObj = dbHandler.queryObject().filter(self._dbModel.id == rec).first()
if not modelObj is None:
#Raise before entity added signal
self.beforeEntityAdded.emit(modelObj)
#Validate for unique value configurations
if not self._validate_unique_columns(modelObj, row_number):
return
if self._tableModel:
if not self._supportsLists and self._tableModel.rowCount() > 0:
self._removeRow(0)
insert_position = self._insertModelToView(modelObj, row_number)
if isinstance(rec, object):
self._deferred_objects[insert_position] = modelObj
else:
try:
self.global_id = self.onfk_lookup(modelObj)
except Exception as ex:
QMessageBox.information(self,
QApplication.translate("ForeignKeyMapper",
"Foreign Key Reference"),
unicode(ex.message))
return
def addUniqueColumnName(self, colName, replace=True):
'''
Set the name of the column whose values are to be unique.
If 'replace' is True then the existing row will be replaced
with one with the new value; else, the new row will not be added to the list.
'''
headers = self._dbModel.displayMapping().values()
colIndex = getIndex(headers, colName)
if colIndex != -1:
self.addUniqueColumnIndex(colIndex, replace)
def addUniqueColumnName(self,colName,replace = True):
'''
Set the name of the column whose values are to be unique.
If 'replace' is True then the existing row will be replaced
with one with the new value; else, the new row will not be added to the list.
'''
headers = self._dbModel.displayMapping().values()
colIndex = getIndex(headers,colName)
if colIndex != -1:
self.addUniqueColumnIndex(colIndex, replace)
def __init__(self, config, treeview=None, parentwidget=None):
self._config = config
headers = self._config.displayColumns.values()
idx = getIndex(headers, "Id")
if idx != -1:
id_ref = headers.pop(idx)
self._headers = headers
self._data = []
self.rootNode = BaseSTRNode(self._headers, view=treeview,
parentWidget=parentwidget)
def _is_spatial_data_source(self, ds):
"""
Searches the data source name against the list of spatial tables.
:param ds: Data source name
:type ds: str
:return: True if 'ds' is a spatial table, otherwise False.
:rtype: bool
"""
sp_idx = getIndex(self._spatial_data_sources, ds)
if sp_idx == -1:
return False
return True
def _onRecordSelectedEntityBrowser(self, recid):
'''
Slot raised when the user has clicked the select button in the 'EntityBrowser' dialog
to add the selected record to the table's list.
Add the record to the foreign key table using the mappings.
'''
#Check if the record exists using the primary key so as to ensure only one instance is added to the table
recIndex = getIndex(self._recordIds(), id)
if recIndex != -1:
return
dbHandler = self._dbModel()
modelObj = dbHandler.queryObject().filter(
self._dbModel.id == recid).first()
if modelObj != None:
#Raise before entity added signal
self.beforeEntityAdded.emit(modelObj)
#Validate for unique value configurations
for colIndex, replace in self._uniqueValueColIndices.items():
attrName = self._dbModel.displayMapping().keys()[colIndex]
attrValue = getattr(modelObj, attrName)
#Check to see if there are cell formatters so that the correct value is searched for in the model
if attrName in self._cellFormatters:
attrValue = self._cellFormatters[attrName](attrValue)
matchingIndex = self.searchModel(colIndex, attrValue)
if matchingIndex.isValid():
if replace:
existingId = matchingIndex.data()
#Delete old record from db
entityModels = self._modelInstanceFromIds([existingId])
if len(entityModels) > 0:
entityModels[0].delete()
self._removeRow(matchingIndex.row())
break
else:
#Break. Do not add item to the list.
return
if not self._supportsLists and self._tableModel.rowCount() > 0:
self._removeRow(0)
self._insertModelToView(modelObj)
def delete_table_data(tableName,cascade = True):
"""
Delete all the rows in the target table.
"""
tables = pg_tables()
tableIndex = getIndex(tables, tableName)
if tableIndex != -1:
sql = "DELETE FROM {0}".format(tableName)
if cascade:
sql += " CASCADE"
t = text(sql)
_execute(t)
def _onRecordSelectedEntityBrowser(self,recid):
'''
Slot raised when the user has clicked the select button in the 'EntityBrowser' dialog
to add the selected record to the table's list.
Add the record to the foreign key table using the mappings.
'''
#Check if the record exists using the primary key so as to ensure only one instance is added to the table
recIndex = getIndex(self._recordIds(),id)
if recIndex != -1:
return
dbHandler = self._dbModel()
modelObj = dbHandler.queryObject().filter(self._dbModel.id == recid).first()
if modelObj != None:
#Raise before entity added signal
self.beforeEntityAdded.emit(modelObj)
#Validate for unique value configurations
for colIndex,replace in self._uniqueValueColIndices.items():
attrName = self._dbModel.displayMapping().keys()[colIndex]
attrValue = getattr(modelObj,attrName)
#Check to see if there are cell formatters so that the correct value is searched for in the model
if attrName in self._cellFormatters:
attrValue = self._cellFormatters[attrName](attrValue)
matchingIndex = self.searchModel(colIndex, attrValue)
if matchingIndex.isValid():
if replace:
existingId = matchingIndex.data()
#Delete old record from db
entityModels = self._modelInstanceFromIds([existingId])
if len(entityModels) > 0:
entityModels[0].delete()
self._removeRow(matchingIndex.row())
break
else:
#Break. Do not add item to the list.
return
if not self._supportsLists and self._tableModel.rowCount() > 0:
self._removeRow(0)
self._insertModelToView(modelObj)
def pg_views(schema="public"):
"""
Returns the views in the given schema minus the default PostGIS views.
"""
t = text("SELECT table_name FROM information_schema.tables WHERE table_schema = :tschema and table_type = :tbtype " \
"ORDER BY table_name ASC")
result = _execute(t, tschema=schema, tbtype="VIEW")
pgViews = []
for r in result:
viewName = r["table_name"]
#Remove default PostGIS tables
viewIndex = getIndex(_postGISViews, viewName)
if viewIndex == -1:
pgViews.append(viewName)
return pgViews
def pg_views(schema="public"):
"""
Returns the views in the given schema minus the default PostGIS views.
"""
t = text("SELECT table_name FROM information_schema.tables WHERE table_schema = :tschema and table_type = :tbtype " \
"ORDER BY table_name ASC")
result = _execute(t,tschema = schema,tbtype = "VIEW")
pgViews = []
for r in result:
viewName = r["table_name"]
#Remove default PostGIS tables
viewIndex = getIndex(_postGISViews, viewName)
if viewIndex == -1:
pgViews.append(viewName)
return pgViews
def numeric_varchar_columns(table, exclude_fk_columns=True):
#Combines numeric and text column types mostly used for display columns
num_char_types = _pg_numeric_col_types + _text_col_types
num_char_cols = columns_by_type(table, num_char_types)
if exclude_fk_columns:
fk_refs = foreign_key_parent_tables(table)
for fk in fk_refs:
local_col = fk[0]
col_idx = getIndex(num_char_cols, local_col)
if col_idx != -1:
num_char_cols.remove(local_col)
return num_char_cols
else:
return num_char_cols
def InsertDocFromModel(self,sourcedoc,containerid):
'''
Renders the source document info from 'SourceDocument' model.
'''
#Check if the document has already been inserted in the manager.
docIndex = getIndex(self._docRefs, sourcedoc.document_id)
if docIndex != -1:
return
if len(self.containers) > 0:
if containerid in self.containers:
container = self.containers[containerid]
networkManager = NetworkFileManager(self.networkResource())
#Add document widget
docWidg = DocumentWidget(networkManager,mode = DOWNLOAD_MODE,canRemove = self._canEdit)
self._linkWidgetRemovedSignal(docWidg)
docWidg.setModel(sourcedoc)
container.addWidget(docWidg)
self._docRefs.append(sourcedoc.document_id)
def spatial_tables(exclude_views=False):
"""
Returns a list of spatial table names in the STDM database.
"""
t = text("select DISTINCT f_table_name from geometry_columns")
result = _execute(t)
spTables = []
views = pg_views()
for r in result:
spTable = r["f_table_name"]
if exclude_views:
tableIndex = getIndex(views,spTable)
if tableIndex == -1:
spTables.append(spTable)
else:
spTables.append(spTable)
return spTables
def columns_by_type(table, data_types):
"""
:param table: Name of the database table.
:type table: str
:param data_types: List containing matching datatypes that should be
retrieved from the table.
:type data_types: list
:return: Returns those columns of given types from the specified
database table.
:rtype: list
"""
cols = []
table_cols = table_column_names(table)
for tc in table_cols:
col_type = columnType(table, tc)
type_idx = getIndex(data_types, col_type)
if type_idx != -1:
cols.append(tc)
return cols
def InsertDocFromModel(self, sourcedoc, containerid):
'''
Renders the source document info from 'SourceDocument' model.
'''
#Check if the document has already been inserted in the manager.
docIndex = getIndex(self._docRefs, sourcedoc.DocumentID)
if docIndex != -1:
return
if len(self.containers) > 0:
if containerid in self.containers:
container = self.containers[containerid]
networkManager = NetworkFileManager(self.networkResource())
#Add document widget
docWidg = DocumentWidget(networkManager,
mode=DOWNLOAD_MODE,
canRemove=self._canEdit)
self._linkWidgetRemovedSignal(docWidg)
docWidg.setModel(sourcedoc)
container.addWidget(docWidg)
self._docRefs.append(sourcedoc.DocumentID)
def _on_enum_table_changed(self, index):
"""
Slot raised when an enumeration table is selected.
:param index: Index of currently selected item.
:type index: int
"""
enum_table = self.cbo_enum_table.currentText()
self.cbo_primary_col.clear()
self.cbo_other_col.clear()
self._enum_col_names = []
if enum_table:
self._enum_col_names = table_column_names(enum_table)
# Remove id column name
id_col = "id"
id_idx = getIndex(self._enum_col_names, id_col)
if id_idx != -1:
self._enum_col_names.remove(id_col)
self.cbo_primary_col.addItem("")
self.cbo_primary_col.addItems(self._enum_col_names)
