def executeSql(self):
if self.editSql.toPlainText().isEmpty():
return
txt = unicode(self.editSql.toPlainText())
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
try:
c = self.db.con.cursor()
t = QTime()
t.start()
self.db._exec_sql(c, txt)
secs = t.elapsed() / 1000.0
self.viewResult.setModel( SqlTableModel(c, self.viewResult) )
# commit before closing the cursor to make sure that the changes are stored
self.db.con.commit()
c.close()
self.lblResult.setText("%d rows, %.1f seconds" % (c.rowcount, secs))
QApplication.restoreOverrideCursor()
except postgis_utils.DbError, e:
QApplication.restoreOverrideCursor()
DlgDbError.showError(e, self)
def onOK(self):
if self.radLength.isChecked():
fct = "length"
elif self.radArea.isChecked():
fct = "area"
else:
QMessageBox.information(self, "error", "No function selected!")
return
addTrigger = self.chkUpdateTrigger.isChecked()
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
try:
schema, table = unicode(self.cboSchema.currentText()), unicode(self.cboTable.currentText())
resColumn, geomColumn = unicode(self.cboResColumn.currentText()), unicode(self.cboGeomColumn.currentText())
self.db.table_apply_function(schema, table, resColumn, fct, geomColumn)
if addTrigger:
self.db.table_add_function_trigger(schema, table, resColumn, fct, geomColumn)
QApplication.restoreOverrideCursor()
except postgis_utils.DbError, e:
QApplication.restoreOverrideCursor()
DlgDbError.showError(e, self)
return
def addColumn(self):
""" open dialog to set column info and add column to table """
dlg = DlgFieldProperties(self)
if not dlg.exec_():
return
name = unicode(dlg.editName.text())
data_type = unicode(dlg.cboType.currentText())
is_null = dlg.chkNull.isChecked()
default = unicode(dlg.editDefault.text())
if dlg.editLength.text().count() > 0:
x = dlg.editLength.text().toInt()
modifier = x[0] if x[1] else None
else:
modifier = None
new_field = postgis_utils.TableField(name, data_type, is_null, default, modifier)
self.emit(SIGNAL("aboutToChangeTable()"))
try:
# add column to table
self.db.table_add_column(self.table, new_field, self.schema)
self.populateFields()
except postgis_utils.DbError, e:
DlgDbError.showError(e, self)
def deleteColumn(self):
""" delete currently selected column """
num = self.currentColumn()
if num == -1:
return
m = self.viewFields.model()
column = m.item(num, 1).text()
data_type = m.item(num, 2).text()
res = QMessageBox.question(self, "are you sure", "really delete column '%s' ?" % column, QMessageBox.Yes | QMessageBox.No)
if res != QMessageBox.Yes:
return
self.emit(SIGNAL("aboutToChangeTable()"))
try:
if data_type == "geometry":
self.db.table_delete_geometry_column(self.table, column, self.schema)
else:
self.db.table_delete_column(self.table, column, self.schema)
self.populateFields()
except postgis_utils.DbError, e:
DlgDbError.showError(e, self)
def createTable(self):
""" create table with chosen fields, optionally add a geometry column """
schema = unicode(self.cboSchema.currentText())
if len(schema) == 0:
QMessageBox.information(self, "sorry", "select schema!")
return
table = unicode(self.editName.text())
if len(table) == 0:
QMessageBox.information(self, "sorry", "enter table name!")
return
m = self.fields.model()
if m.rowCount() == 0:
QMessageBox.information(self, "sorry", "add some fields!")
return
useGeomColumn = self.chkGeomColumn.isChecked()
if useGeomColumn:
geomColumn = unicode(self.editGeomColumn.text())
geomType = str(self.cboGeomType.currentText())
geomDim = self.spinGeomDim.value()
try:
geomSrid = int(self.editGeomSrid.text())
except ValueError:
geomSrid = -1
useSpatialIndex = self.chkSpatialIndex.isChecked()
if len(geomColumn) == 0:
QMessageBox.information(self, "sorry", "set geometry column name")
return
flds = []
for row in xrange(m.rowCount()):
fldName = unicode(m.data(m.index(row,0,QModelIndex())).toString())
fldType = unicode(m.data(m.index(row,1,QModelIndex())).toString())
fldNull = m.data(m.index(row,2,QModelIndex())).toBool()
flds.append( postgis_utils.TableField(fldName, fldType, fldNull) )
pkey = unicode(self.cboPrimaryKey.currentText())
if self.db:
# commit to DB
try:
self.db.create_table(table, flds, pkey, schema)
if useGeomColumn:
self.db.add_geometry_column(table, geomType, schema, geomColumn, geomSrid, geomDim)
# commit data definition changes, otherwise index can't be built
self.db.con.commit()
if useSpatialIndex:
self.db.create_spatial_index(table, schema, geomColumn)
self.emit(SIGNAL("databaseChanged()"))
except postgis_utils.DbError, e:
DlgDbError.showError(e, self)
return
def onOK(self): # execute and commit the code try: sql = "\n".join(self.updateSql()) self.db._exec_sql_and_commit( sql ) QMessageBox.information(self, "good!", "everything went fine!") self.accept() except postgis_utils.DbError, e: DlgDbError.showError(e, self)
def constructTreeFromDb(self, db): """ creates a tree of schemas and tables from current DB connection """ self.tableCount = 0 if not db: return try: list_schemas = db.list_schemas() except postgis_utils.DbError, e: DlgDbError.showError(e, None) return
def editColumn(self):
""" open dialog to change column info and alter table appropriately """
num = self.currentColumn()
if num == -1:
return
m = self.viewFields.model()
# get column in table
# (there can be missing number if someone deleted a column)
column = unicode(m.item(num, 1).text())
for col in self.fields:
if col.name == column:
break
print col.num, col.name
dlg = DlgFieldProperties(self, col)
if not dlg.exec_():
return
new_name = unicode(dlg.editName.text())
new_data_type = unicode(dlg.cboType.currentText())
new_is_null = dlg.chkNull.isChecked()
new_default = unicode(dlg.editDefault.text())
if dlg.editLength.text().count() > 0:
x = dlg.editLength.text().toInt()
new_modifier = x[0] if x[1] else -1
else:
new_modifier = -1
self.emit(SIGNAL("aboutToChangeTable()"))
try:
if new_name != col.name:
self.db.table_column_rename(self.table, col.name, new_name, self.schema)
if new_data_type != col.data_type or new_modifier != col.modifier:
if new_modifier >= 0:
new_data_type += "(%d)" % new_modifier
self.db.table_column_set_type(self.table, new_name, new_data_type, self.schema)
if new_is_null == col.notnull:
self.db.table_column_set_null(self.table, new_name, new_is_null, self.schema)
if len(new_default) > 0 and new_default != col.default:
self.db.table_column_set_default(self.table, new_name, new_default, self.schema)
if len(new_default) == 0 and col.hasdefault:
self.db.table_column_set_default(self.table, new_name, None, self.schema)
self.populateFields()
except postgis_utils.DbError, e:
DlgDbError.showError(e, self)
def createIndex(self): column = unicode(self.cboColumn.currentText()) name = unicode(self.editName.text()) if not name: QMessageBox.critical(self, "error", "Please enter some name for the index") return # now create the index try: self.db.create_index(self.table, name, column, self.schema) except postgis_utils.DbError, e: DlgDbError.showError(e, self) return
def createSpatialIndex(self):
""" asks for every geometry column whether it should create an index for it """
# TODO: first check whether the index doesn't exist already
self.emit(SIGNAL("aboutToChangeTable()"))
try:
for fld in self.fields:
if fld.data_type == 'geometry':
res = QMessageBox.question(self, "create?", "create spatial index for field "+fld.name+"?", QMessageBox.Yes | QMessageBox.No)
if res == QMessageBox.Yes:
self.db.create_spatial_index(self.table, self.schema, fld.name)
except postgis_utils.DbError, e:
DlgDbError.showError(e, self)
return
def do_dbf_import(self):
""" last step: create table and import data """
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
tablename = unicode(self.field("tablename").toString())
pkey = unicode(self.field("pkey").toString())
if pkey == "(none)": pkey = None
try:
# create the table
self.db.create_table(tablename, self.dbf_fields, pkey)
cursor = self.db.con.cursor()
# now let's get the features and import them to database
pr = self.vlayer.dataProvider()
flds = pr.fields()
pr.enableGeometrylessFeatures(True)
pr.select(pr.attributeIndexes(), QgsRectangle(), False) # all attrs, no geometry
f = QgsFeature()
while pr.nextFeature(f):
attrs = f.attributeMap()
values = []
for (i,val) in attrs.iteritems():
vartype = flds[i].type()
if val.isNull():
values.append("NULL")
elif vartype == QVariant.Int:
values.append(str(val.toInt()[0]))
elif vartype == QVariant.Double:
values.append(str(val.toDouble()[0]))
else: # string or something else
values.append("'%s'" % str(val.toString().toUtf8()).replace("'","''").replace("\\", "\\\\"))
self.db.insert_table_row(tablename, values, None, cursor)
# commit changes to DB
self.db.con.commit()
except postgis_utils.DbError, e:
QApplication.restoreOverrideCursor()
DlgDbError.showError(e, self)
return False
def setData(self, index, value, role):
if role != Qt.EditRole or index.column() != 0:
return False
item = index.internalPointer()
new_name = unicode(value.toString())
if new_name == item.name:
return False
if isinstance(item, TableItem):
# rename table or view
try:
schema = item.parentItem.name
self.db.rename_table(item.name, new_name, schema)
self.emit(SIGNAL('dataChanged(const QModelIndex &, const QModelIndex &)'), index, index)
return True
except postgis_utils.DbError, e:
DlgDbError.showError(e, None)
return False
def addConstraint(self):
""" add primary key or unique constraint """
dlg = DlgCreateConstraint(self, self.db, self.table, self.schema)
if not dlg.exec_():
return
column = unicode(dlg.cboColumn.currentText())
self.emit(SIGNAL("aboutToChangeTable()"))
try:
if dlg.radPrimaryKey.isChecked():
self.db.table_add_primary_key(self.table, column, self.schema)
else:
self.db.table_add_unique_constraint(self.table, column, self.schema)
except postgis_utils.DbError, e:
DlgDbError.showError(e, self)
return
def deleteIndex(self):
""" delete currently selected index """
num = self.currentIndex()
if num == -1:
return
m = self.viewIndexes.model()
idx_name = m.item(num, 0).text()
res = QMessageBox.question(self, "are you sure", "really delete index '%s' ?" % idx_name, QMessageBox.Yes | QMessageBox.No)
if res != QMessageBox.Yes:
return
self.emit(SIGNAL("aboutToChangeTable()"))
try:
self.db.delete_index(idx_name, self.schema)
except postgis_utils.DbError, e:
DlgDbError.showError(e, self)
return
def deleteConstraint(self):
""" delete a constraint """
num = self.currentConstraint()
if num == -1:
return
m = self.viewConstraints.model()
con_name = m.item(num, 0).text()
res = QMessageBox.question(self, "are you sure", "really delete constraint '%s' ?" % con_name, QMessageBox.Yes | QMessageBox.No)
if res != QMessageBox.Yes:
return
self.emit(SIGNAL("aboutToChangeTable()"))
try:
self.db.table_delete_constraint(self.table, con_name, self.schema)
except postgis_utils.DbError, e:
DlgDbError.showError(e, self)
return
def addGeometryColumn(self):
""" open dialog to add geometry column """
dlg = DlgAddGeometryColumn(self)
if not dlg.exec_():
return
name = unicode(dlg.editName.text())
geom_type = str(dlg.cboType.currentText())
dim = dlg.spinDim.value()
try:
srid = int(dlg.editSrid.text())
except ValueError:
srid = -1
self.emit(SIGNAL("aboutToChangeTable()"))
try:
self.db.add_geometry_column(self.table, geom_type, self.schema, name, srid, dim)
self.populateFields()
except postgis_utils.DbError, e:
DlgDbError.showError(e, self)
