示例#1
0
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)
示例#3
0
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
示例#4
0
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
示例#5
0
文件: reader.py 项目: Guillon88/stdm
    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
示例#6
0
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
示例#7
0
    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())
示例#8
0
    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
示例#9
0
 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)
示例#10
0
 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)
示例#11
0
    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
示例#12
0
    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)
示例#13
0
 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)
示例#14
0
    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)
示例#15
0
    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
示例#16
0
    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)
示例#17
0
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) 
示例#18
0
 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)
示例#19
0
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
示例#20
0
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
示例#21
0
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
示例#22
0
 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)
示例#23
0
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
示例#24
0
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
示例#25
0
    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)