View Ticket
Not logged in
Home Timeline Branches Tags Tickets Wiki Login
Edit New Ticket Plaintext
Ticket Hash: 604fae8d2831864e1edd49467bac0ab2290bdc7b
Title: Add logic to add missing new spatialite internal TABLES to older Databases
Status: Closed Type: Feature_Request
Severity: Cosmetic Priority: Immediate
Subsystem: Resolution: Overcome_By_Events
Last Modified: 2020-08-26 07:18:17
Version Found In: 4.5.0dev
User Comments:
mj10777 added on 2017-05-04 05:58:57:

When using older Databases that do not contain new internal TABLES, the 'INSERT OR IGNORE' statements brings errors:

    SQL error: INSERT OR IGNORE INTO data_licenses (id, name, url) VALUES (0, 'Undefined', NULL): attempt to write a readonly database
Maybe some checking must be done to 'upgrade' these Databases to work with the needed 4.5.0 logic.

mj10777 added on 2017-05-04 10:23:23:

Sorry, was doing to many things at once.
The TABLE existed, so it seems that the 'attempt to write a readonly database' is the problem I need to resolve.
So I will close this.

mj10777 added on 2017-06-06 05:33:31:

After adding the following statement into 'createAdvancedMetaData':

    int i_rc = sqlite3_db_readonly( sqlite, "main" );

I receive the following output while starting QGIS: 
-I-> createAdvancedMetaData read_only[0]
-I-> createAdvancedMetaData read_only[0]
-I-> createAdvancedMetaData read_only[1]
SQL error: INSERT OR IGNORE INTO data_licenses (id, name, url) VALUES (0, 'Undefined', NULL): attempt to write a readonly database
-I-> createAdvancedMetaData read_only[1]
SQL error: INSERT OR IGNORE INTO data_licenses (id, name, url) VALUES (0, 'Undefined', NULL): attempt to write a readonly database
-I-> createAdvancedMetaData read_only[1]
SQL error: INSERT OR IGNORE INTO data_licenses (id, name, url) VALUES (0, 'Undefined', NULL): attempt to write a readonly database
So I think that a check is needed to avoid these messages.
I assume that 'something' called from QGIS is 'sniffing' the Database and that the connection has been opened with readonly.

mj10777 added on 2017-06-06 06:58:46:

This might offer a swifter solution, that would also avoid making INSERTS, UPDATES on tables that don't need them. 

SPATIALITE_PRIVATE int
createAdvancedMetaData (void *p_sqlite)
{
/* creating the advanced MetaData tables */
    sqlite3 *sqlite = (sqlite3 *) p_sqlite;
    if (sqlite3_db_readonly( sqlite, "main" ))
     return 1;
    sqlite3_stmt *stmt;
    int ret=0;
    int has_geometry_columns_statistics=0;
    int has_geometry_columns_field_infos=0;
    int has_geometry_columns_auth=0;
    int has_geometry_columns_times=0;
    int has_geometry_columns_views=0;
    int has_views_geometry_columns=0;
    int has_views_geometry_columns_statistics=0;
    int has_views_geometry_columns_field_infos=0;
    int has_views_geometry_columns_auth=0;
    int has_virts_geometry_columns=0;
    int has_virts_geometry_columns_statistics=0;
    int has_virts_geometry_columns_field_infos=0;
    int has_virts_geometry_columns_auth=0;
    int has_data_licenses=0;
    int has_sql_statements_log=0;
    // 15 checks
    char *sql;
    sql =
	sqlite3_mprintf ("SELECT "
			 "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
			 "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
			 "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
			 "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
    "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
			 "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
    "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
			 "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
    "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
			 "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
    "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
			 "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
    "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
			 "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), "
			 "(SELECT Exists(SELECT rootpage FROM  sqlite_master "
			 "WHERE (type = 'table' AND Lower(tbl_name) = Lower(%Q)), ",
    "geometry_columns_statistics","geometry_columns_field_infos",
    "geometry_columns_auth","geometry_columns_times","geometry_columns_views",
			 "views_geometry_columns","views_geometry_columns_statistics",
			 "views_geometry_columns_field_infos","views_geometry_columns_auth",
    "virts_geometry_columns","virts_geometry_columns_statistics",
    "virts_geometry_columns_field_infos","'virts_geometry_columns_auth",
    "data_licenses","sql_statements_log");
    ret = sqlite3_prepare_v2 (sqlite, sql, strlen (sql), &stmt, NULL);
    sqlite3_free (sql);
    if (ret == SQLITE_OK)
    {
	 while (sqlite3_step (stmt) == SQLITE_ROW)
	 {
	  if ( (sqlite3_column_type (stmt, 0) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 1) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 2) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 3) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 4) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 5) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 6) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 7) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 8) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 9) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 10) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 11) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 12) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 13) != SQLITE_NULL) &&
          (sqlite3_column_type (stmt, 14) != SQLITE_NULL) )
	 {
          has_geometry_columns_statistics=sqlite3_column_type (stmt, 0);
          has_geometry_columns_field_infos=sqlite3_column_type (stmt, 1);
          has_geometry_columns_auth=sqlite3_column_type (stmt, 2);
          has_geometry_columns_times=sqlite3_column_type (stmt, 3);
          has_geometry_columns_views=sqlite3_column_type (stmt, 4);
          has_views_geometry_columns=sqlite3_column_type (stmt, 5);
          has_views_geometry_columns_statistics=sqlite3_column_type (stmt, 6);
          has_views_geometry_columns_field_infos=sqlite3_column_type (stmt, 7);
          has_views_geometry_columns_auth=sqlite3_column_type (stmt, 8);
          has_virts_geometry_columns=sqlite3_column_type (stmt, 9);
          has_virts_geometry_columns_statistics=sqlite3_column_type (stmt, 10);
          has_virts_geometry_columns_field_infos=sqlite3_column_type (stmt, 11);
          has_virts_geometry_columns_auth=sqlite3_column_type (stmt, 12);
          has_data_licenses=sqlite3_column_type (stmt, 13);
          has_sql_statements_log=sqlite3_column_type (stmt, 14);
	 }
     }
     sqlite3_finalize (stmt);
     if ( (has_geometry_columns_statistics) && (has_geometry_columns_field_infos) &&
           (has_geometry_columns_auth) && (has_geometry_columns_times) &&
           (has_geometry_columns_views) &&
           (has_views_geometry_columns) && (has_views_geometry_columns_statistics) &&
           (has_views_geometry_columns_field_infos) && (has_views_geometry_columns_auth) &&
           (has_virts_geometry_columns) && (has_virts_geometry_columns_statistics) &&
           (has_virts_geometry_columns_field_infos) && (has_virts_geometry_columns_auth) &&
           (has_data_licenses) && (has_sql_statements_log) )
     {
       return 1;
     }
    }
    if ((!has_geometry_columns_statistics) && (!create_geometry_columns_statistics (sqlite)))
	return 0;
    if ((!has_geometry_columns_field_infos) && (!create_geometry_columns_field_infos (sqlite)))
	return 0;
    if ((!has_geometry_columns_auth) && (!create_geometry_columns_auth (sqlite)))
	return 0;
    if ((!has_geometry_columns_times) && (!create_geometry_columns_times (sqlite)))
	return 0;
    if ((!has_geometry_columns_views) && (!create_geometry_columns_views (sqlite)))
	return 0;

    if ((!has_views_geometry_columns) && (!create_views_geometry_columns (sqlite)))
	return 0;
    if ((!has_views_geometry_columns_statistics) && (!create_views_geometry_columns_statistics (sqlite)))
	return 0;
    if ((!has_views_geometry_columns_field_infos) && (!create_views_geometry_columns_field_infos (sqlite)))
	return 0;
    if ((!has_views_geometry_columns_auth) && (!create_views_geometry_columns_auth (sqlite)))
	return 0;

    if ((!has_virts_geometry_columns) && (!create_virts_geometry_columns (sqlite)))
	return 0;
    if ((!has_virts_geometry_columns_statistics) && (!create_virts_geometry_columns_statistics (sqlite)))
	return 0;
    if ((!has_virts_geometry_columns_field_infos) && (!create_virts_geometry_columns_field_infos (sqlite)))
	return 0;
    if ((!has_virts_geometry_columns_auth) && (!create_virts_geometry_columns_auth (sqlite)))
	return 0;

    if ((!has_data_licenses) && (!create_data_licenses (sqlite)))
	return 0;
    if ((!has_sql_statements_log) && (!create_sql_statements_log (sqlite)))
	return 0;

    return 1;
}

sandro added on 2020-08-26 07:18:17: 
too old to be still considered
Keep in touch ... join the community, subscribe the SpatiaLite Users mailing list
SpatiaLite is developed and maintained by Alessandro Furieri a.furieri@lqt.it
Fossil version 2.13 [e7bba4ff36] 2020-11-01 00:13:51