Re: [sqlite] FOREIGN KEY question

[Simon Slavin]

On 8 Feb 2017, at 5:03am, Igor Korot  wrote:

> Does SQLite supports the FK name?
> If yes, what is the proper syntax?

Foreign keys do not have names in SQLite.  You must define the FK as part of 
the child table definition.  You cannot add it after.



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

Re: [sqlite] FOREIGN KEY question

[Hick Gunter]

Did you look at the syntax diagrams? If you mean giving names to foreign key 
clauses, then no.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Igor Korot
Gesendet: Mittwoch, 08. Februar 2017 06:04
An: Discussion of SQLite Database ; 
General Discussion of SQLite Database 
Betreff: [sqlite] FOREIGN KEY question

Hi, ALL,
Does SQLite supports the FK name?
If yes, what is the proper syntax?

Thank you.
___
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
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] FOREIGN KEY question

[Igor Korot]

Hi, ALL,
Does SQLite supports the FK name?
If yes, what is the proper syntax?

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

Re: [sqlite] Critical issues found by Fortify scan of 3.15.2

[Simon Slavin]

On 7 Feb 2017, at 11:38pm, Boris Besky  wrote:

> Please see that attached report.

Attachments to this mailing list are dropped.  If your report is text, just 
paste it into a message.  If not, please post a URL to it.

By the way, Fortify has repeatedly given false positive reports for SQLite.  
Someone will look at what you report, but I don’t expect it to amount to 
anything.

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

[sqlite] Critical issues found by Fortify scan of 3.15.2

[Boris Besky]

Please see that attached report.

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

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Kevin Benson]

On Tue, Feb 7, 2017 at 4:11 PM, Clyde Eisenbeis  wrote:

> int iKeyID = (int)sqliteCmd.ExecuteScalar();
>

I believe the type of last_insert_rowid() is *always* INT64

--
   --
  --
 --Ö¿Ö--
K e V i N
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Joe Mistachkin]

Clyde Eisenbeis wrote:
>
> using (System.Data.SQLite.SQLiteConnection sqliteConnection = new
> System.Data.SQLite.SQLiteConnection("Data Source=" + stPathFilename +
> ";")) {
> using (System.Data.SQLite.SQLiteCommand sqliteCmd =
> sqliteConnection.CreateCommand()) {
> ...
> sqliteCmd.CommandText = "INSERT INTO ..."
> ...
> sqliteCmd.ExecuteNonQuery();
> sqliteCmd.CommandText = "SELECT last_insert_rowid()";
> int iKeyID = (int)sqliteCmd.ExecuteScalar();
>
> End up with an exception: "Specified cast is not valid."
>

Changing the type of iKeyID to "long" should make it work, e.g.:

long iKeyID = (long)sqliteCmd.ExecuteScalar();

Alternatively, you should also be able to use the LastInsertRowId
property of the SQLiteConnection object, e.g.:

long iKeyID = sqliteConnection.LastInsertRowId;

--
Joe Mistachkin @ https://urn.to/r/mistachkin

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

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

Chris,

I have tried:

using (System.Data.SQLite.SQLiteConnection sqliteConnection = new
System.Data.SQLite.SQLiteConnection("Data Source=" + stPathFilename +
";")) {
using (System.Data.SQLite.SQLiteCommand sqliteCmd =
sqliteConnection.CreateCommand()) {
...
sqliteCmd.CommandText = "INSERT INTO ..."
...
sqliteCmd.ExecuteNonQuery();
sqliteCmd.CommandText = "SELECT last_insert_rowid()";
int iKeyID = (int)sqliteCmd.ExecuteScalar();

End up with an exception: "Specified cast is not valid."

Could you provide a specific example for SQLite that does work?  Thanks!



On Mon, Feb 6, 2017 at 9:55 AM, Chris Locke  wrote:
> Why do you say 'there is no equivalence' ?
> Have you read the link I posted in the reply to your question nearly 3 days
> ago?
>
> Last_insert_rowid()
>
> https://www.sqlite.org/c3ref/last_insert_rowid.html
>
> select @@identity and 'select last_insert_rowid()' perform the same action
> - retrieving the last unique row reference.  It was the answer to your
> query.  The link provides further research.
>
>
> On Mon, Feb 6, 2017 at 1:19 PM, Clyde Eisenbeis  wrote:
>
>> In this case, there is only one record added ... no one else has
>> access to this database.
>>
>> In the past, I have locked a record, so no one else can access that
>> record while it is being modified.  Is locking an option in SQLite?
>>
>> Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
>> "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?
>>
>> On Mon, Feb 6, 2017 at 1:55 AM, Hick Gunter  wrote:
>> > But only if you can guarantee that your statement inserts exactly one
>> record and that nothing is executed on your connection between the insert
>> and the call.
>> >
>> > -Ursprüngliche Nachricht-
>> > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> Im Auftrag von Chris Locke
>> > Gesendet: Freitag, 03. Februar 2017 15:41
>> > An: SQLite mailing list 
>> > Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY
>> >
>> > Last_insert_rowid()
>> >
>> > https://www.sqlite.org/c3ref/last_insert_rowid.html
>> >
>> > On Fri, Feb 3, 2017 at 1:51 PM, Clyde Eisenbeis 
>> wrote:
>> >
>> >> For OLE DB SQL, I have retrieved the primary key:
>> >>
>> >> -
>> >>   using (System.Data.OleDb.OleDbConnection oledbConnect = new
>> >> System.Data.OleDb.OleDbConnection(stConnectString))
>> >>   {
>> >> using (System.Data.OleDb.OleDbCommand oledbCmd =
>> >> oledbConnect.CreateCommand())
>> >> {
>> >>   ...
>> >>   oledbCmd.ExecuteNonQuery();
>> >>   //Retrieve the ID
>> >>   oledbCmd.CommandText = "Select @@Identity";
>> >>   int iKeyID = (int)oledbCmd.ExecuteScalar();
>> >>   stKeyID = iKeyID.ToString();
>> >> -
>> >>
>> >> What is the correct nomenclature for SQLite?
>> >> ___
>> >> 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
>> >
>> >
>> > ___
>> >  Gunter Hick
>> > Software Engineer
>> > Scientific Games International GmbH
>> > FN 157284 a, HG Wien
>> > Klitschgasse 2-4, A-1130 Vienna, Austria
>> > Tel: +43 1 80100 0
>> > E-Mail: h...@scigames.at
>> >
>> > This communication (including any attachments) is intended for the use
>> of the intended recipient(s) only and may contain information that is
>> confidential, privileged or legally protected. Any unauthorized use or
>> dissemination of this communication is strictly prohibited. If you have
>> received this communication in error, please immediately notify the sender
>> by return e-mail message and delete all copies of the original
>> communication. Thank you for your cooperation.
>> >
>> >
>> > ___
>> > 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Virtual table vs real table query performance

[Bob Friesenhahn]

We are trying to improve the query performance of our virtual table 
implementation (which is implemented in C).  Due to requirements of 
external code, a specified column of a specified row (by rowid) is 
queried at a time (the least efficient means of access).  Our virtual 
table is accessing entries in a memory-based array.


I have implemented a benchmark script written in Python using the APSW 
wrapper.  The benchmark script reveals that access to a native 
database table is 5 times faster than access to our virtual table.


Intuitively, I would think that access to a memory-based virtual table 
could be faster than native tables.  Our developer has implemented 
xBestIndex and xFilter support which is intended to result in direct 
access to the requested row rather than scanning the whole table.


What is the expected performance of a properly implemented virtual 
table (assuming little additional overhead) vs a native table?


Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Beginning of release testing for version 3.17.0

[Roger Binns]

On 07/02/17 08:56, James K. Lowden wrote:
> I must be having a bad day.  Both Google and cscope fail to turn up any
> reference to NULL_TRIM.  What are you referring to, and what does it
> do?  

I started at the changelog posted at the beginning of this thread which
makes everything clear/linkable:

  https://www.sqlite.org/draft/releaselog/3_17_0.html

Then from the changelog my concerns are if have to wrap any new
functions, and how to test relevant changed/updated parts.  (I'm the
author of a Python wrapper for SQLite.)

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Beginning of release testing for version 3.17.0

[Richard Hipp]

On 2/7/17, James K. Lowden  wrote:
>
> I must be having a bad day.  Both Google and cscope fail to turn up any
> reference to NULL_TRIM.  What are you referring to, and what does it
> do?

Roger was referring to
https://www.sqlite.org/draft/compile.html#enable_null_trim

-- 
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] Bulk Insert in Sqlite3

[James K. Lowden]

On Tue, 7 Feb 2017 14:42:13 +0800
Rowan Worth  wrote:

> Note that golang's sql transaction abstraction doesn't map perfectly
> to sqlite. Golang does not allow any further operations on the Tx
> following a call to Tx.Commit() or Tx.Rollback(). But in sqlite a
> transaction remains open if COMMIT fails because the database is
> locked. 

That suggests a failure of imagination in the Go SQLite driver.  

Most DBMSs return only fatal errors for COMMIT (or success, of
course).  There is no such thing as "try again".  

SQLite does not behave that way by default, but can be made to do so.
If a busy handler is installed that never returns zero, the only time
SQLite returns SQLITE_BUSY is when it determines the transaction cannot
be completed i.e., that there's a deadlock.  

ISTM the Go driver should supply a default busy handler that reduces
COMMIT errors to fatal ones.  It might expose to the application knobs
to change the retry interval.  Of course, if the application can
replace the busy handler, it also has the option of removing it, and
restoring the default behavior.  

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

Re: [sqlite] Beginning of release testing for version 3.17.0

[nomad]

On Tue Feb 07, 2017 at 09:32:18AM -0500, Richard Hipp wrote:
> 
> A draft change log can be seen at
> https://www.sqlite.org/draft/releaselog/3_17_0.html

Nice to see a sha1 extension included with SQLite now. I don't see a
matching SQLITE_ENABLE_SHA1 to add it statically (if that is what the
SQLITE_ENABLE_* compile options do).

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

Re: [sqlite] New pre-release snapshot with performance enhancements

[Dan Kennedy]

On 02/07/2017 09:38 PM, Dan Kennedy wrote:

On 02/07/2017 05:14 PM, Jake Thaw wrote:

Hello Richard,

Is the following enhancement included as part of this pre-release 
snapshot?


- Enhance the session extension to support WITHOUT ROWID tables.

The draft documentation still says that this support does not yet exist,
and my test below also demonstrates this. Please let me know if I am
missing something.


Hi Jake,

Maybe you accidentally linked against the wrong version of SQLite or 
something. It's working here.


  sql = "CREATE TABLE a(x PRIMARY KEY) --WITHOUT ROWID"; 


As the email was being sent I noticed the -- in the above. But it works 
here with or without that.


Dan.


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

Re: [sqlite] New pre-release snapshot with performance enhancements

[Dan Kennedy]

On 02/07/2017 05:14 PM, Jake Thaw wrote:

Hello Richard,

Is the following enhancement included as part of this pre-release snapshot?

- Enhance the session extension to support WITHOUT ROWID tables.

The draft documentation still says that this support does not yet exist,
and my test below also demonstrates this. Please let me know if I am
missing something.


Hi Jake,

Maybe you accidentally linked against the wrong version of SQLite or 
something. It's working here. The code I used is below. It is the same 
as yours except that:


  * prints the SQLite version to stdout, and
  * includes a dummy conflict handler. Technically the conflict handler 
argument to sqlite3changeset_apply may not be NULL, although it wouldn't 
cause your test to fail.


Dan.

//

#include 
#include 

static int xConflict(
  void *pCtx,
  int eConf,
  sqlite3_changeset_iter *pIter
){
  return SQLITE_CHANGESET_ABORT;
}

/*
** The following function will print an error as expected: "UNIQUE
** constraint failed: a.x".
**
** If table a is WITHOUT ROWID, then there will be no error.
*/
void fn(void){
  sqlite3 *db;
  sqlite3_session *pSession = 0;
  int nChangeset;
  void *pChangeset;
  char *sql;
  char *zMsg;

  printf("Using SQLite %s\n", sqlite3_libversion());

  sqlite3_open(":memory:", );

  sql = "CREATE TABLE a(x PRIMARY KEY) --WITHOUT ROWID";
  sqlite3_exec(db, sql, NULL, NULL, NULL);

  sqlite3session_create(db, "main", );
  sqlite3session_attach(pSession, "a");

  sql = "INSERT INTO a VALUES (1)";
  sqlite3_exec(db, sql, NULL, NULL, NULL);

  sqlite3session_changeset(pSession, , );

  sql = "DELETE FROM a WHERE x=1";
  sqlite3_exec(db, sql, NULL, NULL, NULL);

  sqlite3changeset_apply(db, nChangeset, pChangeset, 0, xConflict, 0);

  sql = "INSERT INTO a VALUES (1)";
  sqlite3_exec(db, sql, NULL, NULL, );
  if( zMsg ) printf("%s\n", zMsg);
}

int main(int argc, char **argv){
  fn();
  return 0;
}

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

[sqlite] Beginning of release testing for version 3.17.0

[Richard Hipp]

In a few days, we will begin the official release testing for SQLite
version 3.17.0.

A recent snapshot of the code can be found on the
https://www.sqlite.org/download.html page or directly from Fossil at
https://www.sqlite.org/src/timeline

A draft change log can be seen at
https://www.sqlite.org/draft/releaselog/3_17_0.html

Please try the new code, as you are able, and report any issues to
this mailing list or directly to me at the email shown below.

Thanks.

-- 
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] New pre-release snapshot with performance enhancements

[Jake Thaw]

Hello Richard,

Is the following enhancement included as part of this pre-release snapshot?

- Enhance the session extension to support WITHOUT ROWID tables.

The draft documentation still says that this support does not yet exist,
and my test below also demonstrates this. Please let me know if I am
missing something.

/*
** The following function will print an error as expected: "UNIQUE
constraint failed: a.x".
**
** If table a is WITHOUT ROWID, then there will be no error.
*/
void fn(void){
  sqlite3 *db;
  sqlite3_session *pSession = 0;
  int nChangeset;
  void *pChangeset;
  char *sql;
  char *zMsg;

  sqlite3_open(":memory:", );

  sql = "CREATE TABLE a(x PRIMARY KEY) --WITHOUT ROWID";
  sqlite3_exec(db, sql, NULL, NULL, NULL);

  sqlite3session_create(db, "main", );
  sqlite3session_attach(pSession, "a");

  sql = "INSERT INTO a VALUES (1)";
  sqlite3_exec(db, sql, NULL, NULL, NULL);

  sqlite3session_changeset(pSession, , );

  sql = "DELETE FROM a WHERE x=1";
  sqlite3_exec(db, sql, NULL, NULL, NULL);

  sqlite3changeset_apply(db, nChangeset, pChangeset, 0, 0, 0);

  sql = "INSERT INTO a VALUES (1)";
  sqlite3_exec(db, sql, NULL, NULL, );
  if( zMsg ) printf("%s\n", zMsg);
}

/*/

Kind Regards

Jake

On Sun, Feb 5, 2017 at 2:23 AM, Richard Hipp  wrote:

> There is a new pre-release snapshot of SQLite up at
> https://www.sqlite.org/download.html
>
> Change notes can be seen at https://www.sqlite.org/draft/
> releaselog/3_17_0.html
>
> The performance enhancements in the R-Tree extension are of particular
> significance.  If you are able to test out this pre-release snapshot
> in your application, please do so and report results to this mailing
> list, or directly to me.  Thanks.
>
> --
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug using aggregate functions

[Radovan Antloga]

I use group by 99,99% with aggregate functions.
Just this one special case I have when I must
get min, max values and also I have group_concat
where I must filter records. Result is then
inserted into table where I have not null constraint
and I get error because min, max returned null.
I solved this with two sql-s. First is insert
and second is update.

I didn't know I could use dummy value for
grouping. I see this is not just sqlite
specific. I tried also with Firebird DB
and it works. So I will modify my program.

Thank you!

Clemens Ladisch je 07.02.2017 ob 8:40 napisal:

Radovan Antloga wrote:

select min(A)
from TEST
where B is null
   and A > 3;

if you replace min(A) with * you get empty result set as expected
but with min or max or avg you get one record

This is just how aggregate functions in SQL work.

When you're using GROUP BY, you get exactly one result row per group.
And if there are no rows that are grouped, there are no groups, and
therefore the result is empty.

When you're not using GROUP BY, you always get exactly one result row
out of the aggregate function(s), even if they aggregate an empty set.

If you do want an empty result if the (filtered) source table is empty,
you have to add grouping (by some dummy value):

 SELECT min(a)
 FROM Test
 WHERE b IS NULL
   AND a > 3
 GROUP BY NULL;


Regards,
Clemens
___
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