[sqlite] Bug in checkin e2c6fed8f8 vtab.c sqlite3_drop_modules with SQLITE_ENABLE_API_ARMOR: zName is undeclared

[Keith Medcalf]

SQLITE_API int sqlite3_drop_modules(sqlite3 *db, const char** azNames){
  HashElem *pThis, *pNext;
#ifdef SQLITE_ENABLE_API_ARMOR
  if( !sqlite3SafetyCheckOk(db) || zName==0 ) return SQLITE_MISUSE_BKPT;
#endif
  for(pThis=sqliteHashFirst(>aModule); pThis; pThis=pNext){
Module *pMod = (Module*)sqliteHashData(pThis);
pNext = sqliteHashNext(pThis);
if( azNames ){
  int ii;
  for(ii=0; azNames[ii]!=0 && strcmp(azNames[ii],pMod->zName)!=0; ii++){}
  if( azNames[ii]!=0 ) continue;
}
createModule(db, pMod->zName, 0, 0, 0);
  }
  return SQLITE_OK;
}


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




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

Re: [sqlite] System.Data.SQLite.EF6 feature request: Timespan support

[Joe Mistachkin]

I’ll look into adding this type mapping to System.Data.SQLite.

Sent from my iPhone

> On Aug 19, 2019, at 7:19 PM, Barry  wrote:
> 
> A project I work on runs mainly on SQL Server, but there are times we want
> to be able to create the same data model in SQLite for portability.
> 
> We mostly use EF6 to access the database; we have a code-first approach and
> write custom scripts to generate the database schema.
> 
> We have a custom ConnectionFactory to determine whether a new database
> context should use a SQLiteConnection or SQLServerConnection. (Yes it's
> deprecated but it works...)
> 
> This was working well until we added a TimeSpan field to one of our
> entities. Now we get an exception on model creation: There is no store type
> corresponding to the EDM type 'Edm.Time' of primitive type 'Time'.
> 
> I know it would be possible for us to store the Ticks or TotalSeconds of
> the TimeSpan in a SQLite INTEGER or REAL field, but we've already got
> deployments using the TimeSpan field on SQLServer and would rather avoid
> having to change that schema.
> 
> Would it be possible for a future version of SQLite to support TimeSpan
> mapping in the entity framework?
> 
> Thanks in advance,
> 
> - Barry Smith
> ___
> 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] System.Data.SQLite.EF6 feature request: Timespan support

[Barry]

Hi Simon,

The System.Data.SQLite (basically a dotNet wrapper for SQLite, and official
part of SQLite) team maintain a library called System.Data.SQLite.EF6,
which I understand to be the glue between the Entity Framework and SQLite.
If my understanding is correct, this is the library that should be doing
the TimeSpan <-> (Number or string or however you want to store it in the
database) conversion.

Cheers,

 - Barry

On Mon, 19 Aug 2019 at 16:40, Simon Slavin  wrote:

> On 20 Aug 2019, at 12:19am, Barry  wrote:
>
> > Would it be possible for a future version of SQLite to support TimeSpan
> mapping in the entity framework?
>
> This is not an aspect of SQLite itself, but of how Entity Framework 6
> talks to SQLite.  The development team who could affect this change are
> those who maintain Entity Framework.  Perhaps you could contact them.
>
> I'm not an expert on it, but I see no reason why Entity Framework could
> not see a '.Time' and know that SQLite should store it as text or a number,
> making appropriate conversions when needed.
> ___
> 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] System.Data.SQLite.EF6 feature request: Timespan support

[Simon Slavin]

On 20 Aug 2019, at 12:19am, Barry  wrote:

> Would it be possible for a future version of SQLite to support TimeSpan 
> mapping in the entity framework?

This is not an aspect of SQLite itself, but of how Entity Framework 6 talks to 
SQLite.  The development team who could affect this change are those who 
maintain Entity Framework.  Perhaps you could contact them.

I'm not an expert on it, but I see no reason why Entity Framework could not see 
a '.Time' and know that SQLite should store it as text or a number, making 
appropriate conversions when needed.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared Statement Consistency

[Simon Slavin]

On 19 Aug 2019, at 11:22pm, Mitchell Keith Bloch  wrote:

> I have observed that preparing statements that depend on a table will fail if 
> the table does not currently exist. It appears that those prepared statements 
> will, however, continue to function if the table is dropped and subsequently 
> recreated.

To expand a little on the previous answer, preparing a statement uses internal 
details of the tables it refers to.  The 'prepared' version doesn't just refer 
to entity names, it exploits information about how the table is organised.  So 
deleting a table invalidates all preparations which refer to it.

So deleting the table causes the prepared statement to become invalid (you 
might call this 'fail') too.  However, as DRH wrote, if you used 
sqlite3_prepare_v2() or _v3(), the next time the statement is used SQLite 
realises that the statement is no longer prepared and prepares it again.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.SQLite.EF6 feature request: Timespan support

[Barry]

A project I work on runs mainly on SQL Server, but there are times we want
to be able to create the same data model in SQLite for portability.

We mostly use EF6 to access the database; we have a code-first approach and
write custom scripts to generate the database schema.

We have a custom ConnectionFactory to determine whether a new database
context should use a SQLiteConnection or SQLServerConnection. (Yes it's
deprecated but it works...)

This was working well until we added a TimeSpan field to one of our
entities. Now we get an exception on model creation: There is no store type
corresponding to the EDM type 'Edm.Time' of primitive type 'Time'.

I know it would be possible for us to store the Ticks or TotalSeconds of
the TimeSpan in a SQLite INTEGER or REAL field, but we've already got
deployments using the TimeSpan field on SQLServer and would rather avoid
having to change that schema.

Would it be possible for a future version of SQLite to support TimeSpan
mapping in the entity framework?

Thanks in advance,

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

Re: [sqlite] Prepared Statement Consistency

[Richard Hipp]

On 8/19/19, Mitchell Keith Bloch  wrote:
> I have observed that preparing statements that depend on a table will fail
> if the table does not currently exist. It appears that those prepared
> statements will, however, continue to function if the table is dropped and
> subsequently recreated. Will I find this behavior to be consistent if I
> later switch to another SQL implementation? What should I read to know
> what's standard SQL behavior, well defined SQLite behavior, or possibly a
> quirk of rusqlite?
>

All prepared statements stop working - in a sense - after every schema
change.  However, assuming you have used sqlite3_prepare_v2() or
_v3(), they will automatically rerun the SQL parser and code generator
and reprepare themselves when you attempt to run them.  Will the same
SQL statement continue to work after you DROP and then CREATE a table
it uses?  That depends, I suppose, one how the new CREATE differs from
the prior table that you DROP-ed.

-- 
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

[sqlite] Prepared Statement Consistency

[Mitchell Keith Bloch]

I have observed that preparing statements that depend on a table will fail
if the table does not currently exist. It appears that those prepared
statements will, however, continue to function if the table is dropped and
subsequently recreated. Will I find this behavior to be consistent if I
later switch to another SQL implementation? What should I read to know
what's standard SQL behavior, well defined SQLite behavior, or possibly a
quirk of rusqlite?

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

Re: [sqlite] primary key quinky dink

[Simon Slavin]

On 19 Aug 2019, at 7:20pm, dboland9  wrote:

> That is what I am using.

I'm sorry.  I missed that.  Yes, you have the right solution now.  I don't know 
where you got "AUTO_INCREMENT" from but it's not part of SQLite.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] primary key quinky dink

[dboland9]

That is what I am using.  Thanks for the pointer to the FAQ.

Dave,


Sent with ProtonMail Secure Email.

‐‐‐ Original Message ‐‐‐
On Monday, August 19, 2019 2:15 PM, Simon Slavin  wrote:

> On 19 Aug 2019, at 6:56pm, dboland9 dbola...@protonmail.com wrote:
>
> > I have created a number of tables, and two of then are part of a many to 
> > many relationship. Thus, I need to get the last row id. Using the browser,
>
> What browser ? No part of the SQLite distribution is called a browser.
>
> > I find that:
> > mytable_id INTEGER AUTO_INCREMENT PRIMARY KEY produces a null value
> > mytable_id INTEGER PRIMARY KEY produces an integer value.
> > So, AUTO_INCREMENT should not be used for a primary key?
>
> I think you have the right idea but a wrong syntax.
>
> https://sqlite.org/faq.html#q1
>
> My guess is that you want
>
> mytable_id INTEGER PRIMARY KEY
>
> 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] primary key quinky dink

[Simon Slavin]

On 19 Aug 2019, at 6:56pm, dboland9  wrote:

> I have created a number of tables, and two of then are part of a many to many 
> relationship. Thus, I need to get the last row id.  Using the browser,

What browser ?  No part of the SQLite distribution is called a browser.

> I find that:
> mytable_id INTEGER AUTO_INCREMENT PRIMARY KEY produces a null value
>mytable_id INTEGER PRIMARY KEY produces an integer value.
> 
> So, AUTO_INCREMENT should not be used for a primary key?

I think you have the right idea but a wrong syntax.



My guess is that you want

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

[sqlite] primary key quinky dink

[dboland9]

I have created a number of tables, and two of then are part of a many to many 
relationship. Thus, I need to get the last row id.  Using the browser, I find 
that:
 mytable_id INTEGER AUTO_INCREMENT PRIMARY KEY produces a null value
mytable_id INTEGER PRIMARY KEY produces an integer value.

So, AUTO_INCREMENT should not be used for a primary key?  I have seen it used 
in some examples on the Internet (yes, I know, don't believe everything I 
read).  Will the table still auto number?

Dave,

Sent with [ProtonMail](https://protonmail.com) Secure Email.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] bug: typo in sqlite3.n

[Roland Illig]

From sqlite-autoconf-329:

tea/doc/sqlite3.n says "SQLite3 is a self-contains". It should be
"contained" instead.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] build system

[Sergey Ivanov]

Hi there.

It would be nice to add full autotools support as build system of sqlite
project. For this moment files like Makefile.am are not present in project
tree.
It would be even bettrer to move to more progressive cmake build system. It
would allow to open project in other ide's like qt transparently.
-- 
Kind regards,
Sergey Ivanov
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug report: DROP TABLE while a cursor is open leads to SQLITE_LOCKED

[Jürgen Baier]

Hi,

sorry for the repost. I just noticed that I probably should have added 
"Bug report" in the subject line.


I'm using the Xerial JDBC driver for accessing SQLite (but this issue is 
not directly related to the driver).


I have the problem that it is not possible to drop a table in the same
database connection while a resultset is open:

0. Preparation: Create table t1 and add some values

1. Open connection
2. Create temporary table tmp1
3. Execute SELECT statement on t1 (SELECT * FROM t1).
4. Execute DROP TABLE tmp1 while still iterating over the result of 3.

=> [SQLITE_LOCKED]  A table in the database is locked (database table is
locked)

Note that all this happens in a single thread.

Someone of the Xerial community pointed me to

https://www2.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

It seems while a resultset is open it also was not possible in earlier
SQLite versions to CREATE a table, but this restriction has been fixed
in the meantime.

Is it planned to fix this for DROP TABLE too? Or is there some workaround?

Thanks,

Jürgen





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

Re: [sqlite] [EXTERNAL] Re: Schema updates across threads in WAL & multithread mode

[Hick Gunter]

As already stated, this looks like you have at least one transaction underways. 
Your schema change will become visible only after

1) they are committed on ther "writer" connection AND
2) a new transaction is started on the "reader" connection

If your "readers" are failing to reset or finalize any of their statements, the 
corresponding connection will have an active transaction that you may be 
unaware of and that prevents the schema change being seen.

For diagnostic purposes ONLY, try opening with shared cache enabled and execute 
pragma read_uncommitted. If the problem goes away, then you are keeping 
transactions open longer than you think.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Ben Asher
Gesendet: Freitag, 16. August 2019 21:49
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Schema updates across threads in WAL & 
multithread mode

To clarify, we add a column on our writer connection, and then "SELECT * FROM 
table" on the reader connection does not include the column that was added.

Ben

On Fri, Aug 16, 2019 at 11:32 AM Ben Asher  wrote:

> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
> schema update (adding a column to a table) on our writer connection,
> but then the schema update isn't immediately available on the
> read-only connections that we use on other threads, which causes a
> crash in our application (app expects the column to exist at that
> point). I've verified that the column does indeed get added, and
> everything works fine after restarting the application (i.e. all
> connections loaded fresh pickup the schema update).
>
> Is there something we need to do proactively to ensure that schema
> update appears immediately from other threads?
>
> Some notes about our setup:
>
> sqlite 3.27.2
> Using multithread mode (SQLITE_OPEN_NOMUTEX) Using WAL mode
>
> Thanks!
>
> Ben
>


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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users