PRAGMA table_info could not update schema

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

PRAGMA table_info could not update schema

sanhua.zh
I find that `PRAGMA table_info(tableName)` will not check the expired schema which is modified by other sqlite connections.


1. Open conn 1 and conn 2.
2. Run a SQL to load the schema for conn 2
3. Change the schema using conn 1 by create-table-statement.
4. Get the schema using conn 2 by table_info-pragma-statement.


As a result, step 4 could not get any things since the schema is expired but table_info-pragma-statement do not update it.
How to solve this problem ? Am I using SQLite in a wrong way ?


Here is the sample code:
```
sqlite3_config(SQLITE_CONFIG_MULTITHREAD);  
sqlite3* conn1;
rc = sqlite3_open(path.UTF8String, conn1);  
sqlite3* conn2;
rc = sqlite3_open(path.UTF8String, conn2);
assert(rc==0);
{
  //load schema
  rc = sqlite3_exec(conn2, "SELECT * FROM sqlite_master", nullptr, nullptr, nullptr);
  printf("rc %d\n", rc);
}
rc = sqlite3_exec(conn1, "CREATE TABLE test1 (i INTEGER)", NULL, NULL, NULL);
rc = sqlite3_exec(conn1, "INSERT INTO test1 VALUES(1)", NULL, NULL, NULL);
assert(rc==0);
sqlite3_stmt* stmt;
rc = sqlite3_prepare_v2(conn2, "PRAGMA table_info(test1)", -1, stmt, nullptr);
assert(rc==0);
while (YES) {
  int rc = sqlite3_step(stmt);
  if (rc!=SQLITE_ROW) {
    break;
  }
  for (int i = 0; i  sqlite3_column_count(stmt); i++) {
    switch (sqlite3_column_type(stmt, i)) {
      case SQLITE_TEXT:
        printf("%d %s\n", i, sqlite3_column_text(stmt, i));
        break;
      case SQLITE_INTEGER:
        printf("%d %d\n", i, sqlite3_column_int(stmt, i));
        break;
      default:
       printf("other\n");
        break;
    }
  }
}
sqlite3_finalize(stmt);
```
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: PRAGMA table_info could not update schema

Clemens Ladisch
sanhua.zh wrote:
> I find that `PRAGMA table_info(tableName)` will not check the expired schema which is modified by other sqlite connections.
>
> Here is the sample code:

That code is incomplete and buggy.  (Heed the compiler warnings!)

Anyway, I can reproduce this with two command-line shells:

  1                             2
  ============================  ==================
  select * from sqlite_master;
                                create table t(x);
  pragma table_info(t);


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: PRAGMA table_info could not update schema

sanhua.zh
In reply to this post by sanhua.zh
It's great idea for reproducing with two command-line shells.
Of cource, it can be reproduced with shells and here is the test code.


Note that you should really care the order for running code.


```
Command-line shell 1:
$ sqlite3
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite .open testschema


Command-line Shell 2:
$ sqlite3
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite .open testschema
sqlite SELECT * FROM sqlite_master;


Command-line shell 1:
sqlite CREATE TABLE test1 (i INTEGER);
sqlite PRAGMA table_info(test1);
0|i|INTEGER|0||0


Command-line shell 2:
sqlite PRAGMA table_info(test1); // test1 is already created but can't be reached.
sqlite SELECT * FROM sqlite_master;
table|test1|test1|2|CREATE TABLE test1 (i INTEGER)
sqlite PRAGMA table_info(test1);
0|i|INTEGER|0||0
```








Original Message
Sender:Clemens [hidden email]
Recipient:[hidden email]
Date:Monday, Aug 21, 2017 17:25
Subject:Re: [sqlite] PRAGMA table_info could not update schema


sanhua.zh wrote:  I find that `PRAGMA table_info(tableName)` will not check the expired schema which is modified by other sqlite connections.   Here is the sample code: That code is incomplete and buggy. (Heed the compiler warnings!) Anyway, I can reproduce this with two command-line shells: 1 2 ============================ ================== select * from sqlite_master; create table t(x); pragma table_info(t); Regards, Clemens _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: PRAGMA table_info could not update schema

Rowan Worth-2
In reply to this post by Clemens Ladisch
On 21 August 2017 at 17:25, Clemens Ladisch <[hidden email]> wrote:

> sanhua.zh wrote:
> > I find that `PRAGMA table_info(tableName)` will not check the expired
> schema which is modified by other sqlite connections.
> >
> > Here is the sample code:
>
> That code is incomplete and buggy.  (Heed the compiler warnings!)
>
> Anyway, I can reproduce this with two command-line shells:
>
>   1                             2
>   ============================  ==================
>   select * from sqlite_master;
>                                 create table t(x);
>   pragma table_info(t);
>

Doesn't look like a recent change in behaviour either; even as far back as
3.8.1 the pragma table_info comes up empty.

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users