2

Attempting to use a plugin in QGIS (Midvatten) that utilizes splite. I am attempting to create a database in a different Coordinate reference system than the plugin defaults to, I'm getting "foreign key constraint failed" as my error. In the code it mentions something about the keys being turned off at default and having to enable the keys for each database connection separately.

I downloaded the SpatiaLite GUI in hopes to figure something out there but it seems like the installation of the that is involved on its own.

Can anyone steer me in the direction that I need to go in order to get this database created? Inserted is the error and the bit of code that I'm guessing I have to adjust, I've tried a few different things but so far nothing has worked.

Traceback (most recent call last):
  File "C:/Users/spencerjr/.qgis2/python/plugins\midvatten\midvatten.py", line 654, in new_db
    newdbinstance = newdb(verno)
  File "C:\Users\spencerjr\.qgis2\python\plugins\midvatten\tools\create_db.py", line 35, in __init__
    self.create_new_db(verno,user_select_CRS,EPSG_code)#CreateNewDB(verno)
  File "C:\Users\spencerjr\.qgis2\python\plugins\midvatten\tools\create_db.py", line 78, in create_new_db
    self.rs = self.cur.execute(line.replace('CHANGETORELEVANTEPSGID',str(EPSGID)).replace('CHANGETOPLUGINVERSION',str(verno)).replace('CHANGETOQGISVERSION',str(qgisverno)).replace('CHANGETOSPLITEVERSION',str(versionstext[0][0]))) # use tags to find and replace SRID and versioning info
IntegrityError: foreign key constraint failed

Python version: 2.7.5 (default, May 15 2013, 22:44:16) [MSC v.1500 64 bit (AMD64)]

QGIS version: 2.8.1-Wien Wien, exported

enter image description here

import PyQt4.QtCore
import PyQt4.QtGui
from qgis.core import QGis    
import os
import locale
from pyspatialite import dbapi2 as sqlite# pyspatialite is absolutely necessary (sqlite3 not enough) due to InitSpatialMetaData()
import datetime
#plugin modules
import midvatten_utils as utils


class newdb():

    def __init__(self, verno, user_select_CRS='y', EPSG_code='4326'):
        self.dbpath = ''
        self.create_new_db(verno,user_select_CRS,EPSG_code)#CreateNewDB(verno)

    def create_new_db(self, verno, user_select_CRS='y', EPSG_code='4326'):#CreateNewDB(self, verno):
        """Open a new DataBase (create an empty one if file doesn't exists) and set as default DB"""
        if user_select_CRS=='y':
            EPSGID=str(self.ask_for_CRS()[0])
        else:
            EPSGID=EPSG_code
        PyQt4.QtGui.QApplication.setOverrideCursor(PyQt4.QtCore.Qt.WaitCursor)
        if EPSGID=='0' or not EPSGID:
            utils.pop_up_info("Cancelling...")
        else: # If a CRS is selectd, go on and create the database
            #path and name of new db
            self.dbpath = PyQt4.QtGui.QFileDialog.getSaveFileName(None, "New DB","midv_obsdb.sqlite","Spatialite (*.sqlite)")
            if not self.dbpath:
                PyQt4.QtGui.QApplication.restoreOverrideCursor()
                return ''
            #create Spatialite database
            else:
                try:
                    # creating/connecting the test_db
                    self.conn = sqlite.connect(self.dbpath) 
                    # creating a Cursor
                    self.cur = self.conn.cursor()
                    self.cur.execute("PRAGMA foreign_keys = ON")    #Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately.
                except:
                    utils.pop_up_info("Impossible to connect to selected DataBase")
                    return ''
                    PyQt4.QtGui.QApplication.restoreOverrideCursor()
                #First, find spatialite version
                versionstext = self.cur.execute('select spatialite_version()').fetchall()
                #print versionstext#debug
                # load sql syntax to initialise spatial metadata, automatically create GEOMETRY_COLUMNS and SPATIAL_REF_SYS
                # then the syntax defines a Midvatten project db according to the loaded .sql-file
                if int(versionstext[0][0][0]) > 3: # which file to use depends on spatialite version installed
                    SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions","create_db_splite4.sql")
                else:
                    SQLFile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions","create_db.sql") 
                qgisverno = QGis.QGIS_VERSION#We want to store info about which qgis-version that created the db
                f = open(SQLFile, 'r')
                linecounter = 1
                for line in f:
                    if linecounter > 1:    # first line is encoding info....
                        self.rs = self.cur.execute(line.replace('CHANGETORELEVANTEPSGID',str(EPSGID)).replace('CHANGETOPLUGINVERSION',str(verno)).replace('CHANGETOQGISVERSION',str(qgisverno)).replace('CHANGETOSPLITEVERSION',str(versionstext[0][0]))) # use tags to find and replace SRID and versioning info
                    linecounter += 1

                self.cur.execute("PRAGMA foreign_keys = ON")
                #FINISHED WORKING WITH THE DATABASE, CLOSE CONNECTIONS
                self.rs.close()
                self.conn.close()
                #create SpatiaLite Connection in QGIS QSettings
                settings=PyQt4.QtCore.QSettings()
                settings.beginGroup('/SpatiaLite/connections')
                #settings.setValue(u'%s/sqlitepath'%os.path.basename(str(self.dbpath)),'%s'%self.dbpath)
                settings.setValue(u'%s/sqlitepath'%os.path.basename(self.dbpath),'%s'%self.dbpath)
                settings.endGroup()

                """
                #The intention is to keep layer styles in the database by using the class AddLayerStyles but due to limitations in how layer styles are stored in the database, I will put this class on hold for a while. 

                #Finally add the layer styles info into the data base
                AddLayerStyles(self.dbpath)
                """
  • I figured out how to change the EPSG in the layers window (create a db in default EPSG 1st) I would still like to see if there is a permanent fix so that this error does not occur and the db will create successfully when I input the desired EPSG when prompted – Spencer Feb 4 '16 at 20:20
1

After talking with the Developer of the Midvatten plug in, I learned that the problem is rooted in that my EPSG is not a "common" one, and therefore is not included in the Spatialite install. Because I have no background in coding and am wary of messing things up I found a work around utilizing the GUI in QGIS that seems to be working out so far. I can post some further insight into how to actually get at the root of the problem if requested

Current Workaround

  1. create a default DB with default EPSG
  2. in the Layers Panel, right click on your DB (icon looks like paperclipped pages), in my case "Midvatten_OBS_DB"
  3. select set group CRS
  4. from the prompt that pops up you should be able to select and apply any of the EPSGs included in a standard install of QGIS

I have had success using this method in order to utilize the Midvatten plugin in a State Plane CRS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.