Qt, Sqlite: How to add Spatialite extension

[Danilo Cicerone]

Hi to all,
here is a simple tutorial to use Spatialite in your Qt project:

http://www.digitazero.org/?p=185

Enjoy!

[Brad Hards]

Rather than have to recompile all of Qt, you could probably just
link in the DLL and invoke the init function from C++. I haven't
tried this, but it should be OK.

Brad

[mj10777]

This is unfortunately not always that simple.
You must take into consideration of the conditions of QT during the runtime of the application.
- i.e. the condition of QT on the machine where the application is running.

There are 2 conditions that should be taken into consideration:
- QT has been compiled with internal support for sqlite [default]
or
- QT has been compiled with system sqlite support
-- i.e. './configure -system-sqlite -plugin-sql-sqlite '

For the second condition:
- 'load_extension' must only be called once
For the first condition:
- it must be called every-time a new connection has been made with a new instance of the qt-driver,
because the driver creates a new instance of sqlite and therefore 'load_extension' must be called for that instance .

It should also be noted that the above link supplies a system specific solution that (I believe) will not work on non-windows systems.

In our scenario we have a Dynamic Library that relies totally on spatialite for all spatial functionality,
whether connected to a sqlite,mysql etc database or not. Also there can be more than one connection.
Therefore we need to determine whether the driver has spatial capabilities or not.

- for this we call the following  static function:
int QGeomColl::driver_spatial_capable(QSqlDatabase sql_db,int i_debug)
{
 // GeomFromText('POINT(40000 10000)',3068)) as hex_point;
 QString s_Sql_Test=QString("SELECT hex(%1)) as hex_point;").arg(s_spatial_GeomFromText);
- for spatialite and mysql
s_Sql_Test=QString("SELECT upper(encode(ST_%1)),'hex')) as hex_point;").arg(s_spatial_GeomFromText);
- for postgres

  // SELECT UPPER(master.sys.fn_varbintohexstr(CAST(geometry::STGeomFromText('POINT(40000 10000)',3068) AS varbinary(max)))) AS hex_point;
for Mssql using QODBC

We then extract the driver-handle from the QT-Driver:
 QVariant variant_handle = sql_db.driver()->handle();
  QString s_Driver_Type=variant_handle.typeName();
  QString s_handler_version="";
  if (!variant_handle.isValid())
  { // error
  }
- for sqlite:
  if (variant_handle.isValid() && qstrcmp(s_Driver_Type.toStdString().c_str(),"sqlite3*")==0)
  { // variant_handle.data() returns a pointer to the handle
   sqlite3 *handle_sqlite3 = *static_cast<sqlite3 **>(variant_handle.data());
   if (handle_sqlite3 != 0)
   { // check that it is not NULL
    result_list = QGeomColl::cmd_sqlite3_db(handle_sqlite3,s_Sql_Test);
    if (result_list.count() == 0)
    {
     if (QGeomColl::i_sqlite3_enable_load_extension == 1)
      QGeomColl::i_sqlite3_enable_load_extension=0;
    }
   }
  }
 [similar code for mysql,postgres and odbc]
returning true/false if the spatial-function succeeded or not

for spatialite we check if the global static value QGeomColl::i_sqlite3_enable_load_extension is 0 or 1
- if 1: the handler of the QT-Drive has spatialite capabilities and nothing more must be done
-- i.e QT has been compiled with -system-sqlite
- if 0: QGeomColl::::init_sqlite_support() will be called
-- this function does everything needed to insure that Library/Driver has everything it needs.
mainly: QtGeom_External::spatialite_register_functions(sqlite3 *handle_sqlite3)
- will be called:
 if (QGeomColl::i_sqlite3_enable_load_extension == 0)
 {
  if(sqlite3_enable_load_extension(handle_sqlite3,1) !=0)
  {
   qDebug()<< "-E-> sqlite3_enable_load_extension failed";
   i_rc=100;
   return i_rc;
  }
  else
  {
   QGeomColl::i_sqlite3_enable_load_extension=1;
  }
 }
 // sqlite3_create_function etc.
}

calling 'sqlite3_enable_load_extension', using the sqlite3 handler extracted from the QT driver,
should insure that it will work on all systems supported by QT.

We also check and store  the version numbers of spatialite to insure that only functions are called that are supported by the runtime version.
Since the Library also relies heavily on the srid tables, on each new database connection the structure of the
'spatial_ref_sys' is read to determine the version of spatialite the  database was created with.

All of this is called with the following function, where the spatialite functionality is needed:

 bool QGeomColl::on_spatialite_loaded(bool b_load)
 { // Functions that need the spatialite-interface will call this.
  if (b_load)
  { // This will start the spatialite interface IF it is NOT active
   if (!b_spatialite_loaded)
   { // If not done allready, this will insure that everything needed is up and running.
    if (!QGeomColl::init_sqlite_support())
    {
     return false;
    }
   }
   return b_load;
  }
  // This will close the spatialite interface IF it is active
  return QGeomColl::cleanup_sqlite_support();
 }

So 'QGeomColl::on_spatialite_loaded(true)'
- where spatialite should be started (also starts gdal specific tasks or other one time things)

and when the last sqlite connection OR the application closes:
QGeomColl::on_spatialite_loaded(false);
- this will call QGeomColl::cleanup_sqlite_support();
- which calls everything needed to keep Valgrind happy and to avoid memory leaks.

Mark Johnson, Berlin Germany

[Danilo Cicerone]

Thanks Mark for yuor observations, I've not tried that solution for Mac or Linux.
It's simple to make it works on windows system.

Il giorno giovedì 14 febbraio 2013 08:52:07 UTC+2, mj10777 ha scritto:

It should also be noted that the above link supplies a system specific solution that (I believe) will not work on non-windows systems.

Mark Johnson, Berlin Germany