def _init_spatialite(self, instance_id, target):
        # initialize the spatialite database file
        if target is None:
            dst_data_dir = os.path.join(instance_id, instance_id, "data")
        else:
            dst_data_dir = os.path.join(os.path.abspath(target),
                                        instance_id, instance_id, "data")

        os.chdir(dst_data_dir)
        db_name = "config.sqlite"
        print("Setting up initial database.")
        try:
            from pyspatialite import dbapi2 as db
            conn = db.connect(db_name)
            print("Using pyspatialite.")
        except ImportError:
            try:
                from pysqlite2 import dbapi2 as db
                print("Using pysqlite.")
            except ImportError:
                from sqlite3 import dbapi2 as db
                print("Using sqlite3.")

            conn = db.connect(db_name)
            spatialite_lib = find_library('spatialite')
            try:
                conn.execute("SELECT load_extension(%s)", (spatialite_lib,))
            except Exception, msg:
                raise Exception(
                    'Unable to load the SpatiaLite library extension '
                    '"%s" because: %s' % (spatialite_lib, msg)
                )

    def setUp(self):
        try:
            os.remove(get_db_path())
        except OSError:
            pass

        self.con1 = sqlite.connect(get_db_path(), timeout=0.1)
        self.cur1 = self.con1.cursor()

        self.con2 = sqlite.connect(get_db_path(), timeout=0.1)
        self.cur2 = self.con2.cursor()

    def importusers(self):
        print "importuser"
        delta_days = self.days
        indb = db.connect(self.indb)
        dbout = db.connect(self.outdb)
        incur = indb.cursor()
        ago = ""
        if (delta_days == 0):
            ago = datetime.today() - timedelta(delta_days)
        else:
            sql = '''CREATE VIEW users_lastdays as SELECT user,
            MAX(timestamp) as tempo FROM osm_nodes GROUP BY user;'''
            incur.execute(sql)
        s = 0
        for i in self.tables:
            
            if (delta_days > 0):
                sql = '''select distinct(user) from 
                        users_lastdays where tempo > "%s"''' % str(ago)
            else:
                sql = "SELECT distinct(user) from osm_nodes";
                
            rs = incur.execute(sql)
            r = rs.fetchall()
            if s == 0:
                outcur = dbout.cursor()
                for u in r:
                    user = u[0]
                    sql = "INSERT INTO users (user) VALUES (?)"
            if user is not None:
                outcur.execute(sql,[user])
                s = s+1
                outcur.close()
                dbout.commit()
            if (delta_days >0):
                sql = "DROP VIEW users_lastdays;"
                incur.execute(sql)

            else:
                outcur = dbout.cursor()
                for u in r:
                    user = u[0]
                    sql = "Select user from users where user = ?" # user
                    rsu = list(outcur.execute(sql,user))
                    if len(rsu) == 0:
                        sql = "INSERT INTO users (user) VALUES (?)"
                        outcur.execute(sql,[user])
                outcur.close()
                dbout.commit()
        incur.close()
        indb.close()
        dbout.close()
        print "Users imported"

    def importusers(self,dbname,delta_days=180):
        indb = db.connect(dbname)
        dbout = db.connect(self.outdb)
        incur = indb.cursor()
        sixmonthago = ""
        if (delta_days == 0):
            sixmonthago = datetime.today() - timedelta(delta_days)
        else:
            sql = "CREATE VIEW users_lastdays as SELECT user,MAX(timestamp) as tempo FROM osm_nodes GROUP BY user;"
            incur.execute(sql)
        
        s = 0
        for i in self.tables:
            
            if (delta_days > 0):
                sql = 'select distinct(user) from users_lastdays where tempo > "%s"' % str(sixmonthago)
            else:
                sql = "SELECT distinct(user) from osm_nodes";
                
            rs = incur.execute(sql)
            r = rs.fetchall()
            if s == 0:
                outcur = dbout.cursor()
                for u in r:
                    user = u[0]
                    sql = "INSERT INTO users (user) VALUES ('%s')" % (user)
                    outcur.execute(sql)
                s = s+1
                outcur.close()
                dbout.commit()
            if (delta_days >0):
                sql = "DROP VIEW users_lastdays;"
                incur.execute(sql)

            else:
                outcur = dbout.cursor()
                for u in r:
                    user = u[0]
                    sql = "Select user from users where user = '%s';" % user
                    rsu = list(outcur.execute(sql))
                    if len(rsu) == 0:
                        sql = "INSERT INTO users (user) VALUES ('%s')" % (user)
                        outcur.execute(sql)
                outcur.close()
                dbout.commit()
        incur.close()
        indb.close()
        dbout.close()
        print "Users imported"

    def create(self, force = True):
        if os.path.exists(self.file) and not force:
            return
        elif os.path.exists(self.file):
            os.remove(self.file)
        try:
            connection = db.connect(self.file, check_same_thread = False)
            cursor = connection.cursor()
    
            cursor.execute('SELECT InitSpatialMetadata()')
    
            cursor.execute(self.sql_point)
            cursor.execute('''SELECT AddGeometryColumn('fs_point', 'geometry', %i, '%s', 2);''' % (int(self.srs), "POINT"))
    
            cursor.execute(self.sql_line)
            cursor.execute('''SELECT AddGeometryColumn('fs_line', 'geometry', %i, '%s', 2);''' % (int(self.srs), "LINESTRING"))
    
            cursor.execute(self.sql_polygon)
            cursor.execute('''SELECT AddGeometryColumn('fs_polygon', 'geometry', %i, '%s', 2);''' % (int(self.srs), "POLYGON"))
    
    
            sql_clean = "CREATE TABLE fs_clean (id INTEGER PRIMARY KEY AUTOINCREMENT, clean_date TEXT);"
            cursor.execute(sql_clean)
    
            now = datetime.now().strftime(self.fmt)
    
            cursor.execute("INSERT INTO fs_clean(\"clean_date\") VALUES(date('"+now+"'));")

            connection.commit()
            connection.close()
        except Exception as e:
            raise

    def populateDB(self, selectedComuni):
        if self.stopThread:
            return

        # connect spatialite db
        conn = db.connect(self.DATABASE_OUTNAME)
        try:
            # copy tables
            tables = ["istat_regioni", "istat_province", "codici_belfiore", "istat_loc_tipi", "istat_comuni"]
            for table in tables:
                self.copyTable(conn, table)
             
            # copy table with geom
            tables = ["istat_loc"]
            for table in tables:
                self.copyGeomTable(conn, table)
             
            # get fab_catasto poligons only related to selectedComuni
            for comune in selectedComuni:
                self.copyCatastoPolygons(conn, comune)
            
            # get fab_10k poligons only related to selectedComuni
            for comune in selectedComuni:
                self.copyFab10kPolygons(conn, comune)
            
            #commit population
            conn.commit()
        except db.Error as e:
            self.procMessage.emit(e.message, QgsMessageLog.CRITICAL)
            raise e

    def add_or_update_nodes(self, nodeset, source_file="unknown"):
        ignored_bars = 0
        updated_bars = 0
        new_bars = 0
        conn = sqlite3.connect(config['dbfile'])
        c = conn.cursor()
        update_tstamp = time.time()
        for barn in nodeset.values():
            if 'name' not in barn.tags or barn.tags["name"] == "":
                ignored_bars += 1
            else:
                # Always updated, but use a key to make sure that we never make a duplicate.
                bar = Bar(barn.tags["name"], float(barn.lat), float(barn.lon), type=barn.tags["amenity"], osmid=barn.id)
                cnt = c.execute("select count(1) from bars where osmid = ?", (bar.osmid,)).fetchone()[0]
                if cnt >= 1:
                    c.execute("update bars set name = ?, type =?, updated = ?, geometry = geomFromText('POINT(%f %f)', 4326) where osmid = ?" % (bar.lon, bar.lat),
                        (bar.name, bar.type, update_tstamp, bar.osmid))
                    updated_bars += 1
                else:
                    # oh fuck you very much spatialite
                    c.execute("insert into bars (name, type, osmid, created, geometry) values (?, ?, ?, ?, geomFromText('POINT(%f %f)', 4326))" % (bar.lon, bar.lat),
                        (bar.name, bar.type, bar.osmid, update_tstamp))
                    new_bars += 1

        username = "unknown" # FIXME
        # FIXME - make this log a failure too please!
        c.execute("""insert into data_updates (date, username, bars_created, bars_modified, source_file, status)
                    values (?, ?, ?, ?, ?, ?)""",
                    (update_tstamp, username, new_bars, updated_bars, source_file, "OK"))
        conn.commit()
        conn.close()
        log.info("loaded %d bars, ignored %d nameless, created %d, updated %d", len(nodeset), ignored_bars, new_bars, updated_bars)

 def avg_prices_for_bar(self, bar_pkuid):
     prices = []
     conn = sqlite3.connect(config['dbfile'])
     rows = conn.execute("select drink_type, avg(price), count(price) from pricings where barid = ? group by drink_type", (bar_pkuid,)).fetchall()
     for row in rows:
         prices.append({'drink_type': row[0], 'average': row[1], 'samples': row[2]})
     return prices

    def init_spatialite(self):
        # Get spatialite version
        c = self.con.cursor()
        try:
            self._exec_sql(c, u'SELECT spatialite_version()')
            rep = c.fetchall()
            v = [int(x) if x.isdigit() else x for x in re.findall("\d+|[a-zA-Z]+", rep[0][0])]

            # Add spatialite support
            if v >= [4, 1, 0]:
                # 4.1 and above
                sql = "SELECT initspatialmetadata(1)"
            else:
                # Under 4.1
                sql = "SELECT initspatialmetadata()"
            self._exec_sql_and_commit(sql)
        except:
            return False
        finally:
            self.con.close()

        try:
            self.con = sqlite.connect(self.con_info())

        except (sqlite.InterfaceError, sqlite.OperationalError) as e:
            raise DbError(unicode(e))

        return self.check_spatialite()

 def _updateDatabase(self):
     '''
     Updates the project database
     '''
     
     xsd_schema = main.xsd_schema
     createdb = not os.path.isfile(self.database)
     
     conn = db.connect(self.database)
     
     # Create database if not exist
     if createdb:
         cursor=conn.cursor() 
         cursor.execute("SELECT InitSpatialMetadata()")
         del cursor
     
     # Check and update tables
     for type in xsd_schema.types:
         uri = self.getTypeUri(type)
         layer = QgsVectorLayer(uri, type.friendlyName(), 'spatialite')
         
         # Create layer if not valid
         if not layer.isValid():
             self._createTable(conn, type)
             layer = QgsVectorLayer(uri, type.friendlyName(), 'spatialite')
         
         self._updateTable(type, layer, True)
     
     # Check and update the import log table
     self._updateImportLogTable(conn)
     
     conn.close()
     del conn

def addObject(name, sub_type, country, geometry, scale, eng_name, db_file):
    conn = db.connect(DB_DIR + db_file)
    cur = conn.cursor()
    sql = "SELECT MbrMinX(GeomFromGeoJSON('"+ geometry +"')) as min_lng, MbrMinY(GeomFromGeoJSON('"+ geometry +"')) as min_lat, MbrMaxX(GeomFromGeoJSON('"+ geometry +"')) as max_lng, MbrMaxY(GeomFromGeoJSON('"+ geometry +"')) as max_lat"
    print sql
    res = cur.execute(sql)
    for rec in res:
        print rec
        min_lng = rec[0]
        min_lat = rec[1]
        max_lng = rec[2]
        max_lat = rec[3]
    name = filterString(name)
    if len(name) == 0:
        return None
    cur.execute("INSERT INTO object (name, sub_type, geometry, min_lng, min_lat, max_lng, max_lat, country, scale, eng_name) VALUES(?,?,?,?,?,?,?,?,?,?)", (name, sub_type, geometry,min_lng,min_lat,max_lng,max_lat,country,scale,eng_name))
    conn.commit()
    cur.execute("SELECT id, geometry, name, sub_type, country, min_lat, min_lng, max_lat, max_lng, scale, eng_name FROM object WHERE name=?",(name,))
    id = -1
    for rec in res:
        id = rec[0]
        geometry = rec[1].strip().encode('utf-8')
        name = rec[2].encode('utf-8')
        sub_type = rec[3].encode('utf-8')
        country = rec[4].encode('utf-8')
        min_lat = rec[5]
        min_lng = rec[6]
        max_lat = rec[7]
        max_lng = rec[8]
        scale = rec[9]
        eng_name = rec[10].encode('utf-8')
    if id == -1:
        return None
    else:
        return (name, sub_type, geometry, country, id, (min_lat+max_lat)/2, (min_lng+max_lng)/2, scale, eng_name)

 def CheckConnectionExecutemany(self):
     con = sqlite.connect(":memory:")
     con.execute("create table test(foo)")
     con.executemany("insert into test(foo) values (?)", [(3,), (4,)])
     result = con.execute("select foo from test order by foo").fetchall()
     self.assertEqual(result[0][0], 3, "Basic test of Connection.executemany")
     self.assertEqual(result[1][0], 4, "Basic test of Connection.executemany")

 def CheckFailedOpen(self):
     YOU_CANNOT_OPEN_THIS = "/foo/bar/bla/23534/mydb.db"
     try:
         con = sqlite.connect(YOU_CANNOT_OPEN_THIS)
     except sqlite.OperationalError:
         return
     self.fail("should have raised an OperationalError")

def saveR(rectangles, A, cc, n):
    zona = takeZona(n)
    polis =[]
    for r in rectangles:
        polis.append(r[0])

    union = affinity.rotate(cascaded_union(polis), -A, origin=cc)
    dx = union.centroid.x-cc.x
    dy = union.centroid.y-cc.y
    print 'translate : ',dx, dy
    data2save=()
    for r in rectangles:
        rotated=affinity.rotate(r[0], -A, origin=cc)
        translated = affinity.translate(rotated, -dx, -dy)
        #verificar si interseca
        print zona.intersects(translated)
        if zona.intersects(translated):
            data = (n, A, r[1], r[2], "SRID=25831;"+str(translated))
            data2save += (data,)
        #print data
    conn = db.connect(rootData)
    c = conn.cursor()
    c.executemany('''insert into elementsdiv ( name, ang, x, y, geometry ) values ( ?, ?, ?,?, GeomFromEWKT( ? ) )''', data2save )
    conn.commit()
    conn.close()

    return

def extractData(spatiaLitePath, tableName, id, attributes):
    try:
        conn = db.connect(spatiaLitePath)
        cur = conn.cursor()
        constAttributes = getConstAttributes(cur, tableName)
        varAttributes = getVarAttributes(cur, tableName)
        constData = getConstData(cur, tableName, id)
        varData = getVarData(cur, tableName, id)
        image = getGeometryImage(cur, tableName, id)
        
        #Filtering stuff
        if attributes:
            varAttr_ = []
            constAttr_ = []
            constData_ = []
            varData_ = []
            for index, value in enumerate(constAttributes):
                if value in attributes[0]:
                    constAttr_.append(constAttributes[index])
                    constData_.append(constData[index])
        
            for index, value in enumerate(varAttributes):
                if value in attributes[1]:
                    varAttr_.append(varAttributes[index])
                    for i,v in enumerate(varData):
                        if len(varData_) <= i:
                            varData_.append([varData[i][index]])
                        else:
                            varData_[i].append(varData[i][index])
            
            return[constAttr_, constData_, varAttr_, varData_, image]
        
    except db.Error, e:
        print "Error %s:" % e.args[0]
        sys.exit()

def get_connection(db_path):
    connection = spatialite.connect(db_path)
    if LOAD_AS_EXTENSION:
        # print('spatialite loaded as sqlite extension')

        connection.enable_load_extension(True)

        libspatialite_shared_libs = [
            'libspatialite.so',
            'libspatialite',
            'mod_spatialite',
            '/usr/local/opt/libspatialite/lib/mod_spatialite',
            'libspatialite.dll'
        ]

        load_successfull = False
        for lib_name in libspatialite_shared_libs:
            load_ext_query = 'SELECT load_extension("{0}")'.format(lib_name)
            try:
                connection.execute(load_ext_query)
                load_successfull = True
                break
            except:
                pass

        if not load_successfull:
            print("Unable to load spatialite sqlite3 extension")
            sys.exit(0)

    return connection

 def getNativeFields(self, type):
     '''
     Gets the native fields with type from database
     
     :param type: XSD schema type
     :type type: PAGType
     '''
     
     conn = db.connect(self.database)
     
     cursor = conn.cursor() 
     rs = cursor.execute("PRAGMA table_info('{}')".format(type.name))
     
     for i in range(len(rs.description)):
         if rs.description[i][0] == 'name':
             name_index = i
         if rs.description[i][0] == 'type':
             type_index = i
     
     fields =[]
     
     for row in rs:
         fields.append((row[name_index],row[type_index]))
     
     cursor.close()
     del cursor
     
     conn.close()
     del conn
     
     return fields

def processCsv(infile, schemadict, outdb):
    """function that takes a file, schemadict and outdb
    loops through row by row, matches with the schema
    and throws each row out to the outdb"""
    # let the user know that the script is running
    print infile
    # take the name of the file
    tablename = infile.split('.')[0]
    # grab pertinent part of schemadict
    schema = schemadict[tablename]
    f = open(infile)
    # start of the dictreader, which is a great little option for csvs
    reader = csv.DictReader(f)
    # open a connection and create cursor to access the database
    conn = sqlite3.connect(outdb)
    cur = conn.cursor()
    # find the intersection of csv fieldnames and the schema
    headers = [x for x in reader.fieldnames if x in schema.keys()]
    # i really have no experience with how to do this, i know it is wrong
    # but i am just making a string with the right amount of question marks
    questionmarks = '?,' * len(headers)
    # create a base string that has everything but the values
    string = "insert or replace into {0} {2} values({1})".format(tablename, questionmarks[:-1], tuple(headers))
    # loop through each row of the infile
    for r in reader:
        # process each element of the row through the schema
        # so strs stay as strings, and ints get converted to integers
        vals = [schema[k](r[k]) for k in reader.fieldnames if k in schema]
        # execute
        cur.execute(string, vals)
    # commit and close
    conn.commit()
    conn.close()    

    def init_spatialite(self):
        # Get spatialite version
        c = self.con.cursor()
        try:
            self._exec_sql(c, u'SELECT spatialite_version()')
            rep = c.fetchall()
            v = [int(a) for a in rep[0][0].split('.')]
            vv = v[0] * 100000 + v[1] * 1000 + v[2] * 10

            # Add spatialite support
            if vv >= 401000:
                # 4.1 and above
                sql = "SELECT initspatialmetadata(1)"
            else:
                # Under 4.1
                sql = "SELECT initspatialmetadata()"
            self._exec_sql_and_commit(sql)
        except:
            return False
        finally:
            self.con.close()

        try:
            self.con = sqlite.connect(self.con_info())

        except (sqlite.InterfaceError, sqlite.OperationalError) as e:
            raise DbError(unicode(e))

        return self.check_spatialite()

    def createDB(self):
        self.setWindowTitle(self.tr("Crea il DB %s" % gw.GEOSISMA_DBNAME) )
        self.setRange( 0, 3 )

        if self.stopThread:
            return
        
        if os.path.exists(self.DATABASE_OUTNAME):
            os.unlink(self.DATABASE_OUTNAME)
        # read 
        geosismadb_schema = ""
        with open(self.DATABASE_SCHEMAFILE, 'r') as fs:
            geosismadb_schema += fs.read()
        self.onProgress()
        # connect spatialite db
        conn = db.connect(self.DATABASE_OUTNAME)
        # create DB
        try:
            self.resetMessage.emit(self.tr("Inizializza il DB Spatialite; %s" % self.DATABASE_OUTNAME), QgsMessageLog.INFO)
            conn.cursor().executescript(geosismadb_schema)
            self.onProgress()
        except db.Error as e:
            self.resetMessage.emit(e.message, QgsMessageLog.CRITICAL)
            raise e
        self.onProgress(-1)