Re: [sqlite] Is it possible to concatenate an arbitrary number of columns into one?

[Michael Falconer]

As Simon points out there is no SQL solution to your issue. Some sort of
external utility processing with things like awk, sed or even cut may
assist or for a quick and dirty method you could set the sqlite3 command
line utility .separator value to a blank string which may (or may not)
provide a temporary method. Not in raw SQL though.

On Wed, 21 Nov 2018 at 11:32, Simon Slavin  wrote:

> On 20 Nov 2018, at 11:54pm, Shane Dev  wrote:
>
> > Is there an SQL statement to concatenate all columns into a single
> column without explicitly naming them?
>
> No.  And I can't think of any short-cut way to do what you want.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Regards,
 Michael.j.Falconer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to concatenate an arbitrary number of columns into one?

[Simon Slavin]

On 20 Nov 2018, at 11:54pm, Shane Dev  wrote:

> Is there an SQL statement to concatenate all columns into a single column 
> without explicitly naming them?

No.  And I can't think of any short-cut way to do what you want.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible to concatenate an arbitrary number of columns into one?

[Shane Dev]

Hello,

An asterisk in the result-column represents all columns from the FROM
clause without explicitly naming them,
https://www.sqlite.org/syntax/result-column.html Is there an SQL statement
to concatenate all columns into a single column without explicitly naming
them? If it existed, I could imagine something like - select concat(*) from
table/view/query/etc
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Guy Harris]

On Nov 20, 2018, at 12:41 PM, Jens Alfke  wrote:

> On Nov 20, 2018, at 11:44 AM, Bill Hashman  
> wrote:
> 
>> The timestamp from iOS systems is not compliant with ISO 8601/Unix or other 
>> common timestamps.  It appears apple has their start date offset 31 years.
> 
> Yes, the ‘epoch’ in Apple’s own APIs (CoreFoundation, Foundation) is 
> 1/1/2001, expressed as a double. But of course the POSIX APIs on Apple 
> platforms use the regular Unix epoch of 1/1/1970 as integer.

And this applies to all Apple Darwin-based OSes, including macOS, watchOS, and 
tvOS, not just iOS.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Petite Abeille]

> On Nov 20, 2018, at 21:49, Thomas Kurz  wrote:
> 
>> (Does SQL itself have a numeric timestamp type, or explicitly endorse the 
>> POSIX epoch for numeric timestamps?)
> 
> SQL has an explicit TIMESTAMP type since SQL-92, one thing that I'm heavily 
> missing in SQlite ;-)

DATE '1998-12-25’ & TIMESTAMP '1997-01-31 09:26:50.124’  literals :))

But do *not* try this:

sqlite> select DATE '1998-12-25’;
   ...>
   ...>
   …>


sqlite3 -version
3.25.3 2018-11-05 20:37:38 
89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[David Raymond]

Well dang. I don't think I've used .executescript() before, but that is 
definitely broken, yes.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
Sent: Tuesday, November 20, 2018 3:30 PM
To: SQLite mailing list
Subject: Re: [sqlite] Creating and dropping tables with multiple connections


Change the executed SQL to be executed using .executescript(...) and make sure 
that the executed SQL is a multi-statement batch consisting of

BEGIN TRANSACTION;  COMMIT; that is 

cur1.execute(createSQL) -> cur1.executescript('BEGIN TRANSACTION; ' + createSQL 
+ ' COMMIT;')

and see what happens ...

With isolation_level=None there should be no difference since you have 
theoretically turned off the wrapper magic, but there is.  I do not know what 
the sqlite3 wrapper is doing that causes it to fail.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of David Raymond
>Sent: Tuesday, 20 November, 2018 12:57
>To: SQLite mailing list
>Subject: Re: [sqlite] Creating and dropping tables with multiple
>connections
>
>As an aside, it works properly written in python using apsw.  It does
>not work when using Pythons sqlite3 wrapper (but then, since that
>wrapper "buggers about" with no way to tell what it is doing, I find
>that unsurprising).
>
>
>Works fine for me anyway. What I wrote for an equivalent is below.
>
>
>import contextlib
>import os
>import sqlite3
>import sys
>
>print("Python version: {0}".format(sys.version))
>print("SQLite3 module version: {0}".format(sqlite3.version))
>print("SQLite3 DLL version: {0}\n".format(sqlite3.sqlite_version))
>
>createSQL = """CREATE TABLE MyTable(
> component TEXT not null,
> key TEXT not null,
> value INTEGER not null,
> primary key (component, key)
>);"""
>dropSQL = "DROP TABLE MyTable;"
>
>dbFi = r"D:\Temp\DeleteMe.sqlite"
>if os.path.isfile(dbFi):
>os.remove(dbFi)
>
>with contextlib.closing(sqlite3.connect(dbFi, isolation_level =
>None)) as conn1, \
> contextlib.closing(sqlite3.connect(dbFi, isolation_level =
>None)) as conn2:
>with contextlib.closing(conn1.cursor()) as cur1, \
> contextlib.closing(conn2.cursor()) as cur2:
>for roundNo in range(2):
>print(f"Round: {roundNo:,d}")
>
>print("Create on conn1")
>try:
>cur1.execute(createSQL)
>except sqlite3.Error as err:
>print("ERROR: {0!s}".format(err))
>
>print("Delete on conn2")
>try:
>cur2.execute(dropSQL)
>except sqlite3.Error as err:
>print("ERROR: {0!s}".format(err))
>print()
>print("Executing an extra drop on conn2. This should fail")
>try:
>cur2.execute(dropSQL)
>except sqlite3.Error as err:
>print("ERROR: {0!s}".format(err))
>
>if os.path.isfile(dbFi):
>os.remove(dbFi)
>
>print("\nDone.")
>
>Running that gives me:
>
>Python version: 3.7.1 (v3.7.1:260ec2c36a, Oct 20 2018, 14:57:15) [MSC
>v.1915 64 bit (AMD64)]
>SQLite3 module version: 2.6.0
>SQLite3 DLL version: 3.25.3
>
>Round: 0
>Create on conn1
>Delete on conn2
>
>Round: 1
>Create on conn1
>Delete on conn2
>
>Executing an extra drop on conn2. This should fail
>ERROR: no such table: MyTable
>
>Done.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Thomas Kurz]

> (Does SQL itself have a numeric timestamp type, or explicitly endorse the 
> POSIX epoch for numeric timestamps?)

SQL has an explicit TIMESTAMP type since SQL-92, one thing that I'm heavily 
missing in SQlite ;-)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Jens Alfke]

> On Nov 20, 2018, at 11:44 AM, Bill Hashman  
> wrote:
> 
> The timestamp from iOS systems is not compliant with ISO 8601/Unix or other 
> common timestamps.  It appears apple has their start date offset 31 years.

Yes, the ‘epoch’ in Apple’s own APIs (CoreFoundation, Foundation) is 1/1/2001, 
expressed as a double. But of course the POSIX APIs on Apple platforms use the 
regular Unix epoch of 1/1/1970 as integer. So apparently the app that generated 
this database explicitly decided to use Apple’s epoch.

>  *   Is this something that could be built into the ODBC driver source?

SQLite doesn’t have a date or timestamp type. It just has some functions that 
parse and generate ISO date-time strings. So this doesn’t seem like an issue at 
the SQLite level; it’s just a schema mismatch.

(Does SQL itself have a numeric timestamp type, or explicitly endorse the POSIX 
epoch for numeric timestamps?)

>  *   Could there be an option or built in filter that recognizes that the 
> timestamp data is not ISO 8601/Unix compliant but it's in iOS timestamp 
> format?  And then it reads the data and then correctly maps the iOS timestamp 
> data to be ISO 8601/Unix compliant?

I have no idea how you could detect that! How would a piece of code know 
whether a number in a column is supposed to represent a date in 1980 or a date 
in 2010? The fact that the value is a float vs an integer doesn’t help; I’m 
sure there are databases that store POSIX-epoch timestamps with sub-second 
precision, and databases that store Apple timestamps as integers.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[Keith Medcalf]

Change the executed SQL to be executed using .executescript(...) and make sure 
that the executed SQL is a multi-statement batch consisting of

BEGIN TRANSACTION;  COMMIT; that is 

cur1.execute(createSQL) -> cur1.executescript('BEGIN TRANSACTION; ' + createSQL 
+ ' COMMIT;')

and see what happens ...

With isolation_level=None there should be no difference since you have 
theoretically turned off the wrapper magic, but there is.  I do not know what 
the sqlite3 wrapper is doing that causes it to fail.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of David Raymond
>Sent: Tuesday, 20 November, 2018 12:57
>To: SQLite mailing list
>Subject: Re: [sqlite] Creating and dropping tables with multiple
>connections
>
>As an aside, it works properly written in python using apsw.  It does
>not work when using Pythons sqlite3 wrapper (but then, since that
>wrapper "buggers about" with no way to tell what it is doing, I find
>that unsurprising).
>
>
>Works fine for me anyway. What I wrote for an equivalent is below.
>
>
>import contextlib
>import os
>import sqlite3
>import sys
>
>print("Python version: {0}".format(sys.version))
>print("SQLite3 module version: {0}".format(sqlite3.version))
>print("SQLite3 DLL version: {0}\n".format(sqlite3.sqlite_version))
>
>createSQL = """CREATE TABLE MyTable(
> component TEXT not null,
> key TEXT not null,
> value INTEGER not null,
> primary key (component, key)
>);"""
>dropSQL = "DROP TABLE MyTable;"
>
>dbFi = r"D:\Temp\DeleteMe.sqlite"
>if os.path.isfile(dbFi):
>os.remove(dbFi)
>
>with contextlib.closing(sqlite3.connect(dbFi, isolation_level =
>None)) as conn1, \
> contextlib.closing(sqlite3.connect(dbFi, isolation_level =
>None)) as conn2:
>with contextlib.closing(conn1.cursor()) as cur1, \
> contextlib.closing(conn2.cursor()) as cur2:
>for roundNo in range(2):
>print(f"Round: {roundNo:,d}")
>
>print("Create on conn1")
>try:
>cur1.execute(createSQL)
>except sqlite3.Error as err:
>print("ERROR: {0!s}".format(err))
>
>print("Delete on conn2")
>try:
>cur2.execute(dropSQL)
>except sqlite3.Error as err:
>print("ERROR: {0!s}".format(err))
>print()
>print("Executing an extra drop on conn2. This should fail")
>try:
>cur2.execute(dropSQL)
>except sqlite3.Error as err:
>print("ERROR: {0!s}".format(err))
>
>if os.path.isfile(dbFi):
>os.remove(dbFi)
>
>print("\nDone.")
>
>Running that gives me:
>
>Python version: 3.7.1 (v3.7.1:260ec2c36a, Oct 20 2018, 14:57:15) [MSC
>v.1915 64 bit (AMD64)]
>SQLite3 module version: 2.6.0
>SQLite3 DLL version: 3.25.3
>
>Round: 0
>Create on conn1
>Delete on conn2
>
>Round: 1
>Create on conn1
>Delete on conn2
>
>Executing an extra drop on conn2. This should fail
>ERROR: no such table: MyTable
>
>Done.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[David Raymond]

Short version of my opinion for this is: An application can store whatever it 
wants in a database. The job of the database software, or the ODBC software is 
to give you "what they actually stored", not to give you "what they meant by 
it." So putting this at the ODBC driver level would be bad. But making a 
(wrapper/shim/whatever it's called) on top of the driver that does this for you 
is just fine and is where this should get handled.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Bill Hashman
Sent: Tuesday, November 20, 2018 2:45 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] SQLite iOS timestamp type mapping settings must be set to 
float to get correct data

Hello to all,  I was delving into a SQLite db3 that is a backup from a iOS 
application when I came across a timestamp translation challenge.

The timestamp from iOS systems is not compliant with ISO 8601/Unix or other 
common timestamps.  It appears apple has their start date offset 31 years.  But 
also, the way the timestamp is stored cannot be converted correctly into a 
timestamp with several open source SQLite ODBC drivers I have tried.

It seems the solution I found is to change the timestamp type mapping to float 
type and then I can at least extract the iOS timestamp data from the db3 file.  
I used "SQLite Expert Personal version 5.0.0 alpha" from and set the timestamp 
type mapping to float to get the data.


  *   Is this something that could be built into the ODBC driver source?
  *   Could there be an option or built in filter that recognizes that the 
timestamp data is not ISO 8601/Unix compliant but it's in iOS timestamp format? 
 And then it reads the data and then correctly maps the iOS timestamp data to 
be ISO 8601/Unix compliant?

Ref:   SQLite Expert Personal version 5.0.0 alpha Coral Creek Software
www.sqliteexpert.com

Source file was from: Apple iOS app that uses a SQLite db3 compliant database:  
http://priddysoftware.com/home/products/mytime-2


  1.  Launch app.  Put in some time entries and then email the backup db to get 
the data in a db3 compliant db.
  2.  The table in the db3 that has a timestamp field that will not translate 
correctly with the few SQLite ODBC drivers I tried:  ZTIMEENTRY
  3.  The field in the ZTIMEENTRY table that must have the timestamp type 
setting changed to float to get the data correctly:  ZDATE

Resepectfully,

Bill Hashman
503-899-8780

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[David Raymond]

As an aside, it works properly written in python using apsw.  It does not work 
when using Pythons sqlite3 wrapper (but then, since that wrapper "buggers 
about" with no way to tell what it is doing, I find that unsurprising).


Works fine for me anyway. What I wrote for an equivalent is below.


import contextlib
import os
import sqlite3
import sys

print("Python version: {0}".format(sys.version))
print("SQLite3 module version: {0}".format(sqlite3.version))
print("SQLite3 DLL version: {0}\n".format(sqlite3.sqlite_version))

createSQL = """CREATE TABLE MyTable(
 component TEXT not null,
 key TEXT not null,
 value INTEGER not null,
 primary key (component, key)
);"""
dropSQL = "DROP TABLE MyTable;"

dbFi = r"D:\Temp\DeleteMe.sqlite"
if os.path.isfile(dbFi):
os.remove(dbFi)

with contextlib.closing(sqlite3.connect(dbFi, isolation_level = None)) as 
conn1, \
 contextlib.closing(sqlite3.connect(dbFi, isolation_level = None)) as conn2:
with contextlib.closing(conn1.cursor()) as cur1, \
 contextlib.closing(conn2.cursor()) as cur2:
for roundNo in range(2):
print(f"Round: {roundNo:,d}")

print("Create on conn1")
try:
cur1.execute(createSQL)
except sqlite3.Error as err:
print("ERROR: {0!s}".format(err))

print("Delete on conn2")
try:
cur2.execute(dropSQL)
except sqlite3.Error as err:
print("ERROR: {0!s}".format(err))
print()
print("Executing an extra drop on conn2. This should fail")
try:
cur2.execute(dropSQL)
except sqlite3.Error as err:
print("ERROR: {0!s}".format(err))

if os.path.isfile(dbFi):
os.remove(dbFi)

print("\nDone.")

Running that gives me:

Python version: 3.7.1 (v3.7.1:260ec2c36a, Oct 20 2018, 14:57:15) [MSC v.1915 64 
bit (AMD64)]
SQLite3 module version: 2.6.0
SQLite3 DLL version: 3.25.3

Round: 0
Create on conn1
Delete on conn2

Round: 1
Create on conn1
Delete on conn2

Executing an extra drop on conn2. This should fail
ERROR: no such table: MyTable

Done.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Bill Hashman]

Hello to all,  I was delving into a SQLite db3 that is a backup from a iOS 
application when I came across a timestamp translation challenge.

The timestamp from iOS systems is not compliant with ISO 8601/Unix or other 
common timestamps.  It appears apple has their start date offset 31 years.  But 
also, the way the timestamp is stored cannot be converted correctly into a 
timestamp with several open source SQLite ODBC drivers I have tried.

It seems the solution I found is to change the timestamp type mapping to float 
type and then I can at least extract the iOS timestamp data from the db3 file.  
I used "SQLite Expert Personal version 5.0.0 alpha" from and set the timestamp 
type mapping to float to get the data.


  *   Is this something that could be built into the ODBC driver source?
  *   Could there be an option or built in filter that recognizes that the 
timestamp data is not ISO 8601/Unix compliant but it's in iOS timestamp format? 
 And then it reads the data and then correctly maps the iOS timestamp data to 
be ISO 8601/Unix compliant?

Ref:   SQLite Expert Personal version 5.0.0 alpha Coral Creek Software
www.sqliteexpert.com

Source file was from: Apple iOS app that uses a SQLite db3 compliant database:  
http://priddysoftware.com/home/products/mytime-2


  1.  Launch app.  Put in some time entries and then email the backup db to get 
the data in a db3 compliant db.
  2.  The table in the db3 that has a timestamp field that will not translate 
correctly with the few SQLite ODBC drivers I tried:  ZTIMEENTRY
  3.  The field in the ZTIMEENTRY table that must have the timestamp type 
setting changed to float to get the data correctly:  ZDATE

Resepectfully,

Bill Hashman
503-899-8780

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[Keith Medcalf]

after adding in the obviously missing #include's for  and  
the provided code does not compile.  It is looking for an undefined something 
called EXPECT_EQ ...

Kind of hard to reproduce if incomplete code is presented.

As an aside, it works properly written in python using apsw.  It does not work 
when using Pythons sqlite3 wrapper (but then, since that wrapper "buggers 
about" with no way to tell what it is doing, I find that unsurprising).

>I was expecting that modifying a DB via a certain connection should
>also be visible for all other opened connections. Is my assumption
>incorrect ? (both operations being transactions).

You are correct. Committed transactions on connection A should be visible to 
connection B provided that the transaction committed on A before the 
transaction commenced on connection B.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Albert Banaszkiewicz
>Sent: Tuesday, 20 November, 2018 08:35
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Creating and dropping tables with multiple
>connections
>
>Hi everybody,
>
>
>I have a question regarding multiple connections to the same DB from
>the same process.
>
>I was expecting that modifying a DB via a certain connection should
>also be visible for all other opened connections. Is my assumption
>incorrect ? (both operations being transactions).
>
>
>In my test example, I am creating a table via connection no. 1 and
>drop it via connection no. 2. Table is created correctly and then
>correctly dropped in the first iteration. However, if I try to run
>this one more time I am receiving 'table already exists' error while
>trying to create it (connection no. 1) and then 'no such table' for
>connection no. 2 while trying to drop it.
>
>Is it intended behavior ?
>
>
>Moreover, if I open connections with SQLITE_OPEN_SHAREDCACHE flag,
>everything works as expected.
>
>
>The code I use:
>
>
>static const char* KCreateTable =
>"CREATE TABLE MyTable("
>" component TEXT not null,"
>" key TEXT not null,"
>" value INTEGER not null,"
>" primary key (component, key)"
>");";
>
>static const char* KDropTable =
>"DROP TABLE MyTable;";
>
>
>void ExecuteInTransaction(sqlite3* aDb, const std::string& aQuery)
>{
>std::string finalQuery = "BEGIN TRANSACTION;" + aQuery + "COMMIT;";
>
>EXPECT_EQ(SQLITE_OK, sqlite3_exec(aDb, finalQuery.c_str(), nullptr,
>nullptr, nullptr)) << sqlite3_errmsg(aDb);
>}
>
>TEST_F(CEncryptedStreamTest, DroppingTablesTest)
>{
>sqlite3* writeDb1 = nullptr;
>sqlite3* writeDb2 = nullptr;
>
>EXPECT_EQ(SQLITE_OK, sqlite3_open_v2("db.sqlite", ,
>SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr));
>EXPECT_EQ(SQLITE_OK, sqlite3_open_v2("db.sqlite", ,
>SQLITE_OPEN_READWRITE, nullptr));
>
>for (int i = 0; i < 2; ++i)
>{
>ExecuteInTransaction(writeDb1, KCreateTable);
>
>ExecuteInTransaction(writeDb2, KDropTable);
>}
>
>sqlite3_close_v2(writeDb1);
>sqlite3_close_v2(writeDb2);
>}
>
>
>Cheers,
>
>Albert
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[Richard Hipp]

On 11/20/18, Albert Banaszkiewicz  wrote:
> I was expecting that modifying a DB via a certain connection should also be
> visible for all other opened connections.

That is correct.

>
>
> In my test example, I am creating a table via connection no. 1 and drop it
> via connection no. 2.

The sqlite3_prepare_v2() interface does not look at the database file.
It simply compiles the SQL statement into byte-code.  So on the second
interation sqlite3_prepare_v2() does not yet know that the table has
been DROP-ed by the other connection during the first iteration.  It
thinks the table still exists, and thus throws the error.

If you proceed the CREATE TABLE by some other SQL that does require
reading the database files (example: "SELECT 1 FROM sqlite_master
LIMIT 1") then sqlite3_prepare_v2() will know that the table has been
deleted and will work as you expect.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[Igor Korot]

Hi,

On Tue, Nov 20, 2018 at 10:32 AM Dominique Devienne  wrote:
>
> On Tue, Nov 20, 2018 at 5:28 PM Simon Slavin  wrote:
>
> > On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz <
> > albert.banaszkiew...@tomtom.com> wrote:
> >
> > > ExecuteInTransaction(writeDb1, KCreateTable);
> >
> > I can't answer your question but the above line shows a misunderstanding
> > of SQL.  Transactions are for commands which modify tables: INSERT, UPDATE,
> > DELETE.  Commands which modify the database schema fall outside the scope
> > of transactions and cannot be handled within the framework of COMMIT and
> > ROLLBACK.
> >
>
> Hmmm, DDL is transactional in SQLite, AFAIK... --DD

Unless you work with mySQL. ;-)

Thank you.

> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[Simon Slavin]

On 20 Nov 2018, at 4:32pm, Dominique Devienne  wrote:

> Hmmm, DDL is transactional in SQLite, AFAIK... --DD

I was wrong.  Thanks for the correction.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[Jay Kreibich]

> On Nov 20, 2018, at 10:27 AM, Simon Slavin  wrote:
> On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz 
>  wrote:
> 
>> ExecuteInTransaction(writeDb1, KCreateTable);
> 
> I can't answer your question but the above line shows a misunderstanding of 
> SQL.  Transactions are for commands which modify tables: INSERT, UPDATE, 
> DELETE.  Commands which modify the database schema fall outside the scope of 
> transactions and cannot be handled within the framework of COMMIT and 
> ROLLBACK.
> 
> So don't use BEGIN and COMMIT for anything except INSERT, UPDATE, DELETE .  
> Perhaps you could make that change to your code and see whether the problem 
> remains.

While that is true in many RDBMS products, in SQLite DDL commands are 
transactional.

  -j



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[Dominique Devienne]

On Tue, Nov 20, 2018 at 5:28 PM Simon Slavin  wrote:

> On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz <
> albert.banaszkiew...@tomtom.com> wrote:
>
> > ExecuteInTransaction(writeDb1, KCreateTable);
>
> I can't answer your question but the above line shows a misunderstanding
> of SQL.  Transactions are for commands which modify tables: INSERT, UPDATE,
> DELETE.  Commands which modify the database schema fall outside the scope
> of transactions and cannot be handled within the framework of COMMIT and
> ROLLBACK.
>

Hmmm, DDL is transactional in SQLite, AFAIK... --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Creating and dropping tables with multiple connections

[Simon Slavin]

On 20 Nov 2018, at 3:34pm, Albert Banaszkiewicz 
 wrote:

> ExecuteInTransaction(writeDb1, KCreateTable);

I can't answer your question but the above line shows a misunderstanding of 
SQL.  Transactions are for commands which modify tables: INSERT, UPDATE, 
DELETE.  Commands which modify the database schema fall outside the scope of 
transactions and cannot be handled within the framework of COMMIT and ROLLBACK.

So don't use BEGIN and COMMIT for anything except INSERT, UPDATE, DELETE .  
Perhaps you could make that change to your code and see whether the problem 
remains.

> Moreover, if I open connections with SQLITE_OPEN_SHAREDCACHE flag, everything 
> works as expected.

The result of shared cache is the same as if both connections are the same 
connection.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Creating and dropping tables with multiple connections

[Albert Banaszkiewicz]

Hi everybody,


I have a question regarding multiple connections to the same DB from the same 
process.

I was expecting that modifying a DB via a certain connection should also be 
visible for all other opened connections. Is my assumption incorrect ? (both 
operations being transactions).


In my test example, I am creating a table via connection no. 1 and drop it via 
connection no. 2. Table is created correctly and then correctly dropped in the 
first iteration. However, if I try to run this one more time I am receiving 
'table already exists' error while trying to create it (connection no. 1) and 
then 'no such table' for connection no. 2 while trying to drop it.

Is it intended behavior ?


Moreover, if I open connections with SQLITE_OPEN_SHAREDCACHE flag, everything 
works as expected.


The code I use:


static const char* KCreateTable =
"CREATE TABLE MyTable("
" component TEXT not null,"
" key TEXT not null,"
" value INTEGER not null,"
" primary key (component, key)"
");";

static const char* KDropTable =
"DROP TABLE MyTable;";


void ExecuteInTransaction(sqlite3* aDb, const std::string& aQuery)
{
std::string finalQuery = "BEGIN TRANSACTION;" + aQuery + "COMMIT;";

EXPECT_EQ(SQLITE_OK, sqlite3_exec(aDb, finalQuery.c_str(), nullptr, nullptr, 
nullptr)) << sqlite3_errmsg(aDb);
}

TEST_F(CEncryptedStreamTest, DroppingTablesTest)
{
sqlite3* writeDb1 = nullptr;
sqlite3* writeDb2 = nullptr;

EXPECT_EQ(SQLITE_OK, sqlite3_open_v2("db.sqlite", , 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr));
EXPECT_EQ(SQLITE_OK, sqlite3_open_v2("db.sqlite", , 
SQLITE_OPEN_READWRITE, nullptr));

for (int i = 0; i < 2; ++i)
{
ExecuteInTransaction(writeDb1, KCreateTable);

ExecuteInTransaction(writeDb2, KDropTable);
}

sqlite3_close_v2(writeDb1);
sqlite3_close_v2(writeDb2);
}


Cheers,

Albert
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] geopoly - testing the snapshot code gives error in geopoly_ccw

[Graham Hardman]

Hi, 

thanks for giving access to the snapshot. I paste below output from the
CLI. The pragma compile_options now works, but there is an error in the
coordinates returned from applying geopoly_ccw those produced by a
simple mirror about x axis. 

SQLite version 3.26.0 2018-11-17 14:26:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database. 

sqlite> pragma compile_options;
COMPILER=gcc-8.1.0
ENABLE_GEOPOLY
ENABLE_JSON1
ENABLE_RTREE
THREADSAFE=0
sqlite> create virtual table newtab using geopoly(a,b,c);

sqlite> insert into newtab (_shape) values
('[[0,0],[88,0],[88,80],[60,80],[30,40],[0,40],[0,0]]');

sqlite> select geopoly_json(_shape) from newtab;
[[0.0,0.0],[88.0,0.0],[88.0,80.0],[60.0,80.0],[30.0,40.0],[0.0,40.0],[0.0,0.0]]

sqlite> select geopoly_json(geopoly_xform(_shape, 1,0,0,-1,0,0)) from
newtab;
[[0.0,0.0],[88.0,0.0],[88.0,-80.0],[60.0,-80.0],[30.0,-40.0],[0.0,-40.0],[0.0,0.0]]

sqlite> select geopoly_json(geopoly_ccw(geopoly_xform(_shape,
1,0,0,-1,0,0))) from newtab;
[[0.0,0.0],[30.0,-40.0],[60.0,-80.0],[88.0,-80.0],[88.0,0.0],[0.0,-40.0],[0.0,0.0]]


I think my process is Ok. 

regards, 

Graham
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users