Re: [sqlite] Patch Etiquette

[Clemens Ladisch]

Ziemowit Laski wrote:
> [...]
> I DO care about being acknowledged as the author of the patch, however.

But you are not the author.  You reported the problem, but the actual
patches that got applied (http://www.sqlite.org/cgi/src/info/50e60cb44fd3687d,
http://www.sqlite.org/cgi/src/info/d9752c8f7c55426f) were not derived
from the patch you proposed.


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

[Clemens Ladisch]

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

Re: [sqlite] Bulk Insert in Sqlite3

[Rowan Worth]

On 7 February 2017 at 15:11, Simon Slavin  wrote:

>
> On 7 Feb 2017, at 6:56am, Niti Agarwal  wrote:
>
> > Thanks for your reply. The length matters as I am appending 100 rows at a
> > time in a sql statement. It is making very fast as compared to single sql
> > insert in For loop.
> > Copied the code below for reference. Here the list size is 100
> > Any better way to do this? Like I read about *bind*...not sure how I can
> do
> > it in Golang.
>
> Okay.  By using an INSERT command with lots of value sets you are doing
> things more efficiently than I thought.  Each INSERT is its own transaction
> so you are doing 100 INSERTs per transaction.
>

It would a lot simpler though to move the db.Begin() outside the for loop
and execute multiple INSERT statements within the loop.


> I am not familiar with GoLang.  Can someone say if it’s appropriate to use
> the two functions
>
> PathInfoStmt, err := db.Prepare(sql_PathInfo)
> err = tx.Stmt(PathInfoStmt).Exec(valsPath…)
>
> like that ?  I would expect Prepare to go with Step instead but I could
> understand if the library being used makes it okay.
>

Yes, that's ok. These are not sqlite specific bindings, go takes a ODBC
like approach where a standard interface[1] is used to connect to various
database engines.

[1] https://golang.org/pkg/database/sql/

Exec() is designed for INSERT/UPDATEs where you're not asking the DB for
information. The read equivalent is Query() which returns a sql.Rows
structure that you iterate over using Rows.Next().

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

[Simon Slavin]

On 7 Feb 2017, at 6:56am, Niti Agarwal  wrote:

> Thanks for your reply. The length matters as I am appending 100 rows at a
> time in a sql statement. It is making very fast as compared to single sql
> insert in For loop.
> Copied the code below for reference. Here the list size is 100
> Any better way to do this? Like I read about *bind*...not sure how I can do
> it in Golang.

Okay.  By using an INSERT command with lots of value sets you are doing things 
more efficiently than I thought.  Each INSERT is its own transaction so you are 
doing 100 INSERTs per transaction.

I am not familiar with GoLang.  Can someone say if it’s appropriate to use the 
two functions

PathInfoStmt, err := db.Prepare(sql_PathInfo)
err = tx.Stmt(PathInfoStmt).Exec(valsPath…)

like that ?  I would expect Prepare to go with Step instead but I could 
understand if the library being used makes it okay.

Simon.
___
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

[Niti Agarwal]

Thanks for your reply. The length matters as I am appending 100 rows at a
time in a sql statement. It is making very fast as compared to single sql
insert in For loop.
Copied the code below for reference. Here the list size is 100
Any better way to do this? Like I read about *bind*...not sure how I can do
it in Golang.



As shown below:

func StoreFileList(db *sql.DB, fileList []File) {
sql_PathInfo := `
INSERT OR IGNORE INTO path_info(Id,FilePath) VALUES`
sql_FileInfo := `
INSERT OR REPLACE INTO file_info(
PathId,
FileName,
FileSize,
IsDir,
IsExported,
Level,
ModTime
) VALUES `

valsPath := []interface{}{}
valsFile := []interface{}{}
for _, file := range fileList {
sql_PathInfo += "(?,?),"
sql_FileInfo += "((SELECT Id FROM path_info WHERE FilePath = ?),?, ?, ?, ?,
?, ?),"
valsPath = append(valsPath, nil, file.FilePath)
valsFile = append(valsFile, file.FilePath, file.FileName, file.FileSize,
file.IsDir, file.IsExported, file.Level, file.ModTime)
}

sql_PathInfo = sql_PathInfo[0 : len(sql_PathInfo)-1]
sql_FileInfo = sql_FileInfo[0 : len(sql_FileInfo)-1]

PathInfoStmt, err := db.Prepare(sql_PathInfo)
if err != nil {
panic(err)
}
fileInfoStmt, err := db.Prepare(sql_FileInfo)
if err != nil {
panic(err)
}
defer PathInfoStmt.Close()
defer fileInfoStmt.Close()

tx, err := db.Begin()
if err != nil {
panic(err)
}
_, err = tx.Stmt(PathInfoStmt).Exec(valsPath...)
_, err1 := tx.Stmt(fileInfoStmt).Exec(valsFile...)
if err != nil || err1 != nil {
if err != nil {
panic(err)
}
if err1 != nil {
panic(err1)
}
fmt.Println("doing rollback")
tx.Rollback()
} else {
tx.Commit()
}
}

On Tue, Feb 7, 2017 at 11:56 AM, Simon Slavin  wrote:

>
> On 7 Feb 2017, at 5:36am, Niti Agarwal  wrote:
>
> > Need to insert close to 10 Million records to sqlite3 in around 30 mins.
>
> This number of records requires so much space the temporary data will not
> fit inside a cache.  Consider using a counter so that the transaction is
> ended and a new one begun every 1000 records.  Or perhaps every 1
> records.  Try both ways and see which is faster.
>
> > Currently I am saving 100 Records under one transaction with below
> settings:
> >
> >   PRAGMA synchronous = NORMAL;
> >   PRAGMA journal_mode = WAL;
> >   PRAGMA auto_vacuum = FULL;
> >
> > I read about SQLITE_MAX_SQL_LENGTH, Is it better to tune this value?
>
> The value SQLITE_MAX_SQL_LENGTH is about the length in text of one SQL
> command.  I doubt it has any impact on your problem.  You should probably
> leave it as it is.
>
> Simon.
> ___
> 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] Why does a query run 50x slower across a network?

[GB]

There are several reasons why networks are much slower than local disks 
(think of SATA vs. Ethernet, SATA Bus vs. Network latency, no client 
side caching etc.). This is especially true for random access patterns 
like those SQLite uses.


So to minimize file access, (like already suggested by others) carefully 
inspect and adjust your indexes using EXPLAIN and do a VACUUM and ANALYZE.


But in general I'd advise against using file-based databases over 
network filesystems. They tend to have problems with random access 
patterns. I've seen systems where you could happily throw gigabyte-sized 
files back and forth but failing miserably on random access.


If you need server-side storage, consider using a full-fledged database 
server. Since you are coming from Access, SQL Server Express comes to my 
mind but PostgreSQL or Firebird may also be an option.


___
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

[Rowan Worth]

Hi Niti,

There's on need to build a giant SQL string; a transaction can span
multiple statements. To bind in golang place a ? within your SQL query and
provide the values as additional arguments to the Exec/Query function. eg,
after using db.Begin() to create a transaction

tx, err := db.Begin()
if err != nil {
return err
}
_, err = tx.Exec("INSERT INTO table1 VALUES (?, ?, ?)", column1,
column2, column3)
if err != nil {
 tx.Rollback()
 return err
}
err = tx.Commit()
if err != nil {
 return err
}

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. If you want to be able
to retry the COMMIT in this situation you must manually manage transactions
via db.Exec("BEGIN")/db.Exec("COMMIT") instead of db.Begin()/tx.Commit().

-Rowan


On 7 February 2017 at 13:36, Niti Agarwal  wrote:

> Hi,
> We are using Sqlite3 with Golang to do bulk insert.
> Need to insert close to 10 Million records to sqlite3 in around 30 mins.
>
> Currently I am saving 100 Records under one transaction with below
> settings:
>
>PRAGMA synchronous = NORMAL;
>PRAGMA journal_mode = WAL;
>PRAGMA auto_vacuum = FULL;
>
> I read about SQLITE_MAX_SQL_LENGTH, Is it better to tune this value?
>
> Also, read about sql bind feature, but not very sure how to do in Golang?
> Is there any better way to do bulk insert?
>
> Thanks,
> Niti
> ___
> 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] Bulk Insert in Sqlite3

[Simon Slavin]

On 7 Feb 2017, at 5:36am, Niti Agarwal  wrote:

> Need to insert close to 10 Million records to sqlite3 in around 30 mins.

This number of records requires so much space the temporary data will not fit 
inside a cache.  Consider using a counter so that the transaction is ended and 
a new one begun every 1000 records.  Or perhaps every 1 records.  Try both 
ways and see which is faster.

> Currently I am saving 100 Records under one transaction with below settings:
> 
>   PRAGMA synchronous = NORMAL;
>   PRAGMA journal_mode = WAL;
>   PRAGMA auto_vacuum = FULL;
> 
> I read about SQLITE_MAX_SQL_LENGTH, Is it better to tune this value?

The value SQLITE_MAX_SQL_LENGTH is about the length in text of one SQL command. 
 I doubt it has any impact on your problem.  You should probably leave it as it 
is.

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

[sqlite] Bulk Insert in Sqlite3

[Niti Agarwal]

Hi,
We are using Sqlite3 with Golang to do bulk insert.
Need to insert close to 10 Million records to sqlite3 in around 30 mins.

Currently I am saving 100 Records under one transaction with below settings:

   PRAGMA synchronous = NORMAL;
   PRAGMA journal_mode = WAL;
   PRAGMA auto_vacuum = FULL;

I read about SQLITE_MAX_SQL_LENGTH, Is it better to tune this value?

Also, read about sql bind feature, but not very sure how to do in Golang?
Is there any better way to do bulk insert?

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

Re: [sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT (Was: Patch Etiquette)

[Ziemowit Laski]

Hello Jan + Maintainers,

Here is my approach to the SQLITE_ENABLE_UPDATE_DELETE_LIMIT problem.  [If the 
attachment does not arrive intact, please let me know.]

It is different from Jan's in that it operates strictly on the Makefile level.  
The idea is simple:  When compiling parse.y with lemon, we do it twice - once 
with -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT and once without, obtaining two 
temporary C parser files.  Then, we diff the two files with the 
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT option, producing a parse.c with 
SQLITE_ENABLE_UPDATE_DELETE_LIMIT if-defs.  This parse.c is then sucked into 
the amalgamation, and now one can use SQLITE_ENABLE_UPDATE_DELETE_LIMIT 
directly with the amalgamation, and without needing a full source compile.

Should you wish to integrate this patch, please provide the proper attribution 
:)

--Zem

From: Ziemowit Laski
Sent: Monday, 6 February 2017 12:38
To: 'jan.nijtm...@gmail.com'; 'sqlite-users@mailinglists.sqlite.org'
Subject: SQLITE_ENABLE_UPDATE_DELETE_LIMIT (Was: [sqlite] Patch Etiquette)

Hi Jan,

Thank you for the link.  I'll take a look.

--Zem

[P.S. It appears I cannot reply to the whole list directly from the 
https://www.mail-archive.com/sqlite-users webpage.  Is this because I'm not 
(yet) a members of the list?]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Patch Etiquette

[Ziemowit Laski]

Hi Jens,

Yes, I did notice the commit that mentions my name, and am glad for the 
attribution.  What I was getting at, though, is that such attributions should 
be part of SQLite policy and should happen automatically and every time.

To your question, I don't particularly care if I have write privileges to the 
repo.  In fact, I don't think it would be a good idea because (1) I'm a 
relative newbie to SQLite and could easily break things and (2) I'm not and 
will not be a regular contributor.  I DO care about being acknowledged as the 
author of the patch, however.

On the e-mail address question, I tend to think that they should be required as 
well.  Any patch that is committed may cause integration problems down the 
line.  SQLite has numerous extensions which can be baked into it, and it is 
impossible for any single contributor to perform regression testing on all 
these permutations.  Ergo, it may be necessary to contact the author of the 
patch for them to try to rework it and/or explain how the patch's functionality 
is supposed to interoperate with the new feature/change which caused the 
breakage.

SQLite is a relatively small project, so we can probably get away with not 
publishing e-mail addresses, since the 3 maintainers probably know who 
submitted what and can contact them if needed.  For larger projects (e.g., 
GCC), having e-mail addresses of contributors known to everyone is absolutely 
essential.

Thanks,

--Zem


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

[sqlite] SQLITE_ENABLE_UPDATE_DELETE_LIMIT (Was: Patch Etiquette)

[Ziemowit Laski]

Hi Jan,

Thank you for the link.  I'll take a look.

--Zem

[P.S. It appears I cannot reply to the whole list directly from the 
https://www.mail-archive.com/sqlite-users webpage.  Is this because I'm not 
(yet) a members of the list?]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug using aggregate functions

[Radovan Antloga]

Hi,

I discovered a bug using sqlite 3.15.2. It is simple
to reproduce. Try this SQL-s:

CREATE TABLE TEST(
A integer primary key,
B integer);

-- insert some test data
insert into TEST (A,B) values (1, 1);
insert into TEST (A,B) values (2, null);

-- check count(*)
select count(*)
from TEST
where B is null
  and A > 3;

-- bug sql using agg function (also with other functions: max, avg)
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

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

Re: [sqlite] Exposing compile-time limits?

[Richard Newman]

>
> We do strive to contain unnecessary growth in the number of
> interfaces.  Remember that we are promising to support every interface
> until 2050.  That argues for keeping the number of APIs to a minimum.
>

Sounds good to me. Thanks for clarifying!
___
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

[Chris Locke]

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

Re: [sqlite] Why does a query run 50x slower across a network?

[ajm]

Besides my own experience, although it is a bit older and I couldn't find it in 
this email list, It is still in my archive, so there you have (copy-paste) of 
related email of 2014-09-08 in this list, send by jose isaias cabrera 
<...@cinops.xerox.com> in response of a query:

Re: [sqlite] Does the Connection string support UNC paths?

a...@zator.com wrote...
>
>>
>>  Mensaje original 
>> De: "Chris" 
>> Para: 
>> Fecha: Sat, 6 Sep 2014 23:46:19 -0500
>> Asunto: [sqlite] Does the Connection string support UNC paths?
>>
>>
>>I am old database programmer that just came across SQLite and am working 
>>on
>>a small project for a PVR that uses SQLite as it's db provider. I try
>>specifying a UNC path to the database for the datasource in the connection
>>string and I get the following error, "unable to open database file". .
>>When I look at the exception generated, I see an errorcode = 14. However,
>>if I map a network drive, I can open the file and work with it. I am
>>running Windows 7 x64 Pro and system.data.sqlite version 1.0.93.0 with dot
>>net framework 4.0 and Visual Studio 2010.
>>
>>
>>Obviously SQLite supports UNC paths because I am using SQLite database
>>browser to open the same database using a UNC path.
>>
>
> Also, a full pathname, can start with a double backslash (\\), indicating 
> the global root, followed by a server name and a share name to indicate 
> the path to a network file server.

Just a little suggestion: UNC paths are slower than connecting that same 
path to a drive. If you are going to use it a lot, I suggest for you to 
connect that path to a drive and it will be much faster. We have a system 
using SQLite with a SharedDB and connecting that path to a drive is much 
faster. Ihth.

josé 

--
Adolfo.
>
>  Mensaje original 
> De: James K. Lowden
> Para:  SQLite mailing list 
> Fecha:  Mon, 06 Feb 2017
> Asunto:  Re: [sqlite] Why does a query run 50x slower across a network?
>
>> In respect to the Windows environment I've appreciated that the use of UNC 
>> convention over a network (LAN) behaves much slower that "mapping" the 
>> logical unit as a drive letter D, E, .. Z in the local host. Altought 
>> unfortunately this doesn't seem very handy in all situations.

>That's bizarre.  By mapping a network file service to a drive letter,
the user gains some convenience, and saves the OS very little: only the
work of resolving the name, and maybe some other setup.  Command
conveyance and data transfer should be identical.  In my experience, it
always was.  

>If you're seeing noticeable difference, I'd expect you'll find they're
either in name resolution or somewhere in the GUI.  I can't think of
any reason the underlying transport would be affected.  


___
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

[Jean-Luc Hainaut]

This is the way SQL (not only SQLite) interprets these queries.
Basically you ask information about an empty set:
- count(*) = 0, as expected
- min(A) is undefined, which is translated in SQL by 'null' value; since 
'null' is not 'nothing', you get a 1-line result comprising 'null' !


Regards

J-L Hainaut



Hi,

I discovered a bug using sqlite 3.15.2. It is simple
to reproduce. Try this SQL-s:

CREATE TABLE TEST(
A integer primary key,
B integer);

-- insert some test data
insert into TEST (A,B) values (1, 1);
insert into TEST (A,B) values (2, null);

-- check count(*)
select count(*)
from TEST
where B is null
  and A > 3;

-- bug sql using agg function (also with other functions: max, avg)
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

Best Regards
Radovan Antloga
___
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] Transactions

[Jens Alfke]

> On Feb 6, 2017, at 11:08 AM, James K. Lowden  wrote:
> 
> It's fascinating, and emblematic of our times, that
> something like iTunes had (or has) DBMS interaction amidst low-level
> operations like capture and playback.  

Oh, it didn’t use a database! It was just streaming audio data from the 
filesystem. (And Final Cut was just streaming video frames to the filesystem.)

My point is that the disk-controller flush invoked by SQLite’s commit caused 
the entire filesystem to become unavailable for tens-to-hundreds of 
milliseconds at a time, and when I started doing that multiple times a second, 
it would sometimes starve iTunes’ audio threads of data, causing dropouts.

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

Re: [sqlite] Transactions

[James K. Lowden]

On Mon, 6 Feb 2017 09:38:20 -0800
Jens Alfke  wrote:

> In some cases there?d be multiple events in a second that triggered a
> database write in a transaction; when this happened down in my humble
> process, it could cause iTunes playback to stutter and video capture
> to lose frames. 

You should turn that into a war story for "Coders at Work" or
something.  It's fascinating, and emblematic of our times, that
something like iTunes had (or has) DBMS interaction amidst low-level
operations like capture and playback.  

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

Re: [sqlite] Why does a query run 50x slower across a network?

[James K. Lowden]

On Mon, 06 Feb 2017 13:12:22 +0100
a...@zator.com wrote:

> In respect to the Windows environment I've appreciated that the use
> of UNC convention over a network (LAN) behaves much slower that
> "mapping" the logical unit as a drive letter D, E, .. Z in the local
> host. 

That's bizarre.  By mapping a network file service to a drive letter,
the user gains some convenience, and saves the OS very little: only the
work of resolving the name, and maybe some other setup.  Command
conveyance and data transfer should be identical.  In my experience, it
always was.  

If you're seeing noticeable difference, I'd expect you'll find they're
either in name resolution or somewhere in the GUI.  I can't think of
any reason the underlying transport would be affected.  

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

Re: [sqlite] Transactions

[Nathan Bossett]

On Sun, Feb 05, 2017 at 09:41:48AM +0100, Michele Pradella wrote:
> Do you think transactions are useful only when you have to do a sequence
> of statements that depends on each other and you need a way to rollback
> all statements if something goes wrong? or you can use transactions even
> with not interdependent statements for performance reason? and if yes do
> you think there's a trade-off about the number of query number in each
> transaction? 
> 
> I'm think about 1000 INSERT query to execute, is transaction works
> better because you do not have to change index at any insert but just
> one time on commit?

Anecdotally, I can say that I've experimented and see huge speedups in 
building up a database on both HDD's with a few tens of megabytes of cache 
and on SSD's by batching up individual INSERTs and UPDATEs into groups 
through a transaction.

I've experimented with batching various numbers of INSERTS when 
building up a new database from another local data source and wound 
up with 100 or 1000, which may not be optimal but was fast enough.

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

Re: [sqlite] Patch Etiquette

[Jens Alfke]

> On Feb 5, 2017, at 12:14 PM, Ziemowit Laski  wrote:
> 
> HOWEVER, one thing bothers me:  You did not acknowledge my authorship of it.  
> AFAICT, you NEVER seem to acknowledge third party contributions.  Clearly, 
> I'm not user 'drh’. Like with other open-source projects, I would expect my 
> name and e-mail to appear in the commit message (and the changelog, if you 
> had one), and definitely in the release notes. 

The commit description does say "This fixes a problem identified on the SQLite 
mailing list by Ziemowit Laski.” 

Are you complaining that your email address was left out [some people strongly 
dislike having their email address posted online]; or that you weren’t 
identified as the author of the patch; or that you don’t have an account on the 
version-control system?

(FYI I am not associated with SQLite in any way, just curious.)

—Jens
___
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]

Sorry to post this to quick. I just checked this
with Firebird and I get same result. I did not
expect that.

Sorry once again !!


Radovan Antloga je 06.02.2017 ob 18:34 napisal:

Hi,

I discovered a bug using sqlite 3.15.2. It is simple
to reproduce. Try this SQL-s:

CREATE TABLE TEST(
A integer primary key,
B integer);

-- insert some test data
insert into TEST (A,B) values (1, 1);
insert into TEST (A,B) values (2, null);

-- check count(*)
select count(*)
from TEST
where B is null
  and A > 3;

-- bug sql using agg function (also with other functions: max, avg)
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

Best Regards
Radovan Antloga
___
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] Transactions

[Jens Alfke]

> On Feb 6, 2017, at 12:07 AM, Hick Gunter  wrote:
> 
> The optimal number of inserts/transaction depends on your hardware setup and 
> who else needs access to CPU and I/O resources.

Too many transactions can definitely be a problem! It depends on the OS, but 
the filesystem flush at the end of the commit can cause the hardware disk 
controller to block for “a long time” (tens or hundreds of ms) while it writes 
all the blocks from its internal cache to the physical storage medium. This can 
be bad for real-time threads that are dependent on disk I/O.

Ten years ago, back when I worked at Apple and was first using SQLite, I was 
too eager about running transactions. In some cases there’d be multiple events 
in a second that triggered a database write in a transaction; when this 
happened down in my humble process, it could cause iTunes playback to stutter 
and video capture to lose frames. Fortunately this was caught early on by 
internal testers, and I tweaked my insertion code to batch up changes into 
larger more widely-spaced transactions before release.

[Disclaimer: Some of this may be not be true anymore. SSDs have different 
performance characteristics than hard disks, and OS kernels have advanced. But 
it’s still true that achieving durability is expensive and has trade-offs.]

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

[sqlite] Bug using aggregate functions

[Radovan Antloga]

Hi,

I discovered a bug using sqlite 3.15.2. It is simple
to reproduce. Try this SQL-s:

CREATE TABLE TEST(
A integer primary key,
B integer);

-- insert some test data
insert into TEST (A,B) values (1, 1);
insert into TEST (A,B) values (2, null);

-- check count(*)
select count(*)
from TEST
where B is null
  and A > 3;

-- bug sql using agg function (also with other functions: max, avg)
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

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

Re: [sqlite] Too many emails.

[Richard Hipp]

On 2/6/17, Taylor Matson  wrote:
>
> Hello could you please take me off the mailing list? Thanks!
>

You are not on the mailing list, at least not under the email address
shown above.  Perhaps you have subscribed using a different email
address which is then forwarded to the address you are using?

-- 
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] Too many emails.

[Taylor Matson]

Hello could you please take me off the mailing list? Thanks!


Sent from my Verizon, Samsung Galaxy smartphone
___
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]

I'm writing WPF / C# code.  This is part of a function that is called
whenever the user enters data that is saved.  The primary key is used
whenever the user changes that record ... to ensure the correct data
is changed.

On Mon, Feb 6, 2017 at 10:11 AM, Simon Slavin  wrote:
>
> On 6 Feb 2017, at 1:30pm, Clyde Eisenbeis  wrote:
>
>> What is the correct nomenclature for using last_insert_rowid() for
>> SQLite?  For OLE DB I've used ... oledbCmd.CommandText = "Select
>> @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar().
>
> last_insert_row() is an SQL variable.  In whatever API you’re using, do 
> whatever you’d do to execute a SELECT command but instead of selecting data 
> from a table do
>
> SELECT last_insert_row()
>
> instead.  Please note that the standard SQLite API is C code.  If you’re not 
> programming in C you have to tell us what you are programming in, or we have 
> no clue how your programming language accesses SQLite databases.
>
> Simon.
> ___
> 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] Retrieve INTEGER PRIMARY KEY

[Simon Slavin]

On 6 Feb 2017, at 1:30pm, Clyde Eisenbeis  wrote:

> What is the correct nomenclature for using last_insert_rowid() for
> SQLite?  For OLE DB I've used ... oledbCmd.CommandText = "Select
> @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar().

last_insert_row() is an SQL variable.  In whatever API you’re using, do 
whatever you’d do to execute a SELECT command but instead of selecting data 
from a table do

SELECT last_insert_row()

instead.  Please note that the standard SQLite API is C code.  If you’re not 
programming in C you have to tell us what you are programming in, or we have no 
clue how your programming language accesses SQLite databases.

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

Re: [sqlite] Why does a query run 50x slower across a network?

[Dominique Devienne]

On Mon, Feb 6, 2017 at 4:27 PM, Bart Smissaert 
wrote:

> Would a "server app" be an option, so run SQLite on the remote location and
> return the dataset?
>

Didn't sound like it was, from David's description, but in case I'm
guessing wrong,
then https://github.com/rqlite/rqlite might be of interest or an
inspiration. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why does a query run 50x slower across a network?

[Bart Smissaert]

Would a "server app" be an option, so run SQLite on the remote location and
return the dataset?

RBS



On Mon, Feb 6, 2017 at 10:28 AM, dandl  wrote:

> We have an application we converted from Access to Sqlite. Mostly it's
> been a great success, but we have two queries that runs 50x slower across a
> gigabit LAN than on a local file system and we don't know why. Performance
> on Access was perfectly acceptable, and on Sqlite is not and we can't
> figure out why. Customers are complaining, and with good reason.
>
> We're using System.Data.Sqlite and the file is being opened as a shared
> UNC pathname. The network can transfer at upwards of 250 Mbps on file
> copies, but the SQL query runs at around 10 Mbps (Windows Perfmon). The
> database is about 90MB. The queries takes 100ms on local file system and 5s
> on network share. [With customer data it can run into minutes.]
>
> I'm hoping we've done something really dumb and obvious, but we can't see
> it. Details follow. Anyone who can shed light very much appreciated.
>
> The query looks like this:
> Query1:
>
> SELECT  Max([date]) AS LastOfdate FROM order_header WHERE
> (((transaction_type)=1) AND ((status_code)=-1) AND ((sale_type_id)=1 Or
> (sale_type_id)=2 Or (sale_type_id)=14)) GROUP BY billToCardGuid,
> date([date]) HAVING billToCardGuid=X'A426D7165BBF0ECA3276555D32B6E385'
> ORDER BY date([date]) DESC limit 3
>
> Query2:
>
> SELECT  order_header.order_id AS maxID FROM order_header WHERE
> (((order_header.transaction_type)=1) AND ((order_header.status_code)=-1)
> AND ((order_header.sale_type_id)=1 Or (order_header.sale_type_id)=2 Or
> (order_header.sale_type_id)=14)) AND (order_header.billToCardGuid=X'
> A426D7165BBF0ECA3276555D32B6E385') ORDER BY [date] DESC, order_id desc
> limit 1
>
> The schema looks like this:
> CREATE TABLE IF NOT EXISTS "order_header" (
> "order_id" INTEGER DEFAULT 0,
> "user_name" VARCHAR(31) COLLATE NOCASE ,
> "number" INTEGER DEFAULT 0,
> "confirmation_number" VARCHAR(9) COLLATE NOCASE ,
> "creation_date" DATETIME,
> "modification_date" DATETIME,
> "transaction_type" SMALLINT DEFAULT 0,
> "customer_billto_last_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_first_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_company" VARCHAR(50) COLLATE NOCASE ,
> "customer_billto_address" VARCHAR(63) COLLATE NOCASE ,
> "customer_billto_city" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_state" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_zip" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_last_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_first_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_company" VARCHAR(50) COLLATE NOCASE ,
> "customer_shipto_address" VARCHAR(63) COLLATE NOCASE ,
> "customer_shipto_city" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_state" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_zip" VARCHAR(31) COLLATE NOCASE ,
> "customer_fax" VARCHAR(31) COLLATE NOCASE ,
> "customer_ar_balance" REAL DEFAULT 0,
> "customer_bill_rate" REAL DEFAULT 0,
> "customer_tel" VARCHAR(31) COLLATE NOCASE ,
> "date" DATETIME,
> "status_description" VARCHAR(31) COLLATE NOCASE ,
> "status_code" SMALLINT DEFAULT 0,
> "order_comment" TEXT,
> "payment_comment" VARCHAR(63) COLLATE NOCASE ,
> "terms_description" VARCHAR(31) COLLATE NOCASE ,
> "shipmethod_description" VARCHAR(31) COLLATE NOCASE ,
> "shipmethod_amount" REAL DEFAULT 0,
> "shipmethod_tax_rate" REAL DEFAULT 0,
> "shipmethod_tax_code" VARCHAR(3) COLLATE NOCASE ,
> "tax_total" REAL DEFAULT 0,
> "ex_tax_total" REAL DEFAULT 0,
> "grand_total" REAL DEFAULT 0,
> "pay_amount" REAL DEFAULT 0,
> "balance" REAL DEFAULT 0,
> "card" VARCHAR(19) COLLATE NOCASE ,
> "exp" VARCHAR(4) COLLATE NOCASE ,
> "po" VARCHAR(15) COLLATE NOCASE ,
> "payment_date" DATETIME,
> "printed_name" VARCHAR(31) COLLATE NOCASE ,
> "signature" BLOB,
> "line_item_count" SMALLINT DEFAULT 0,
> "flags" SMALLINT DEFAULT 0,
> "employeeGuid" GUID,
> "employee_bill_rate" REAL DEFAULT 0,
> "employee_name" VARCHAR(31) COLLATE NOCASE ,
> "date_hotsynced" DATETIME,
> "date_exported_to_myob" DATETIME,
> "export_status" SMALLINT DEFAULT 0,
> "export_error_no" INTEGER DEFAULT 0,
> "attempt_export" BOOL NOT NULL DEFAULT 1,
> "invoice_status" CHAR(1) DEFAULT 'I',
> "sale_type_id" INTEGER DEFAULT 1,
> "export_Error_Guid" GUID,
> "validated" BOOL NOT NULL DEFAULT 0,
> "reconciled" BOOL NOT NULL DEFAULT 0,
> "txnGuid" GUID,
> "cardGuid" GUID,
> "billToCardGuid" GUID,
> "shipToCardGuid" GUID,
> "locationFromCardGuid" GUID,
> "locationToCardGuid" GUID,
> "unidentified_chunks" BLOB,
> "toDoGuid" GUID,
> "uom_pick_mode" BOOL NOT NULL DEFAULT 0,
> "validationGuid" GUID,
> "territoryGuid" GUID,
> "territoryGroupGuid" GUID,
> "hasTerritory" BOOL NOT NULL DEFAULT 0,
> "parentTranGuid" GUID,
> "cartonQuantity" REAL,
> "pickInstructions" VARCHAR(64) COLLATE NOCASE ,
> "creator" INTEGER,
> "POSMode" BOOL NOT NULL DEFAULT 0,
> "Locked" BOOL NOT NULL DEFAULT 0,
> "relatedTransactionGuid" GUID,
> 

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Clyde Eisenbeis]

What is the correct nomenclature for using last_insert_rowid() for
SQLite?  For OLE DB I've used ... oledbCmd.CommandText = "Select
@@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar().

On Mon, Feb 6, 2017 at 7:24 AM, Clemens Ladisch  wrote:
> Clyde Eisenbeis wrote:
>> Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
>> "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?
>
> This has *nothing* to do with OLE DB; @@Identity is an SQL Server
> specific thing.  SQLite's is last_insert_rowid(), and both have
> exactly the same restrictions.
>
>
> 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

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Hick Gunter]

There is no record locking in SQLite

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Clyde Eisenbeis
Gesendet: Montag, 06. Februar 2017 14:19
An: SQLite mailing list 
Betreff: Re: [sqlite] Retrieve INTEGER PRIMARY KEY

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


___
 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

Re: [sqlite] Retrieve INTEGER PRIMARY KEY

[Clemens Ladisch]

Clyde Eisenbeis wrote:
> Perhaps there is no equivalence to OLE DB ... oledbCmd.CommandText =
> "Select @@Identity" ... int iKeyID = (int)oledbCmd.ExecuteScalar()?

This has *nothing* to do with OLE DB; @@Identity is an SQL Server
specific thing.  SQLite's is last_insert_rowid(), and both have
exactly the same restrictions.


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

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

Re: [sqlite] Exposing compile-time limits?

[Richard Hipp]

On 2/5/17, Richard Newman  wrote:
> Hello folks,
>
> `sqlite3_limit` allows callers to discover the run-time value of limits
> such as `SQLITE_LIMIT_VARIABLE_NUMBER`.
>
> Callers can also *set* each limit, so long as the value is smaller than a
> compile-time define, in this case `SQLITE_MAX_VARIABLE_NUMBER`.
>
> But callers have no good way of determining the largest acceptable value:
> they must do one of three things.
>
> 1. Be compiled at the same time as sqlite3.c and sniff the environment.
> Obviously this isn't possible for systems that don't control their own
> SQLite library.
> 2. Make sure to call `sqlite3_limit` when opening the first database
> connection, and save the returned value.
> 3. Discover the acceptable maximum for an existing connection by changing
> the limit: calling `sqlite3_limit` with a huge value, then calling it again
> with -1 to fetch the truncated value. (Or calling *three* times to fetch,
> change, restore.)
>
> Is this a deliberate omission?

We do strive to contain unnecessary growth in the number of
interfaces.  Remember that we are promising to support every interface
until 2050.  That argues for keeping the number of APIs to a minimum.

I think solution (3) above works well in this case, does it not?  The
function to discover the compile-time upper bound on a limit while
keeping the current limit setting the same looks like this:

   int compileTimeMaxLimit(sqlite3 *db, int eCode){
  int iOriginalLimit = sqlite3_limit(db, eCode, 0x7fff);
  return sqlite3_limit(db, eCode, iOriginalLimit);
   }



-- 
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] Why does a query run 50x slower across a network?

[ajm]

In respect to the Windows environment I've appreciated that the use of UNC 
convention over a network (LAN) behaves much slower that "mapping" the logical 
unit as a drive letter D, E, .. Z in the local host. Altought unfortunately 
this doesn't seem very handy in all situations.

--
Adolfo.

>
>  Mensaje original 
> De: dandl 
> Para:  SQLite mailing list 
> Fecha:  Mon, 6 Feb 2017 11:02:39 +
> Asunto:  Re: [sqlite] Why does a query run 50x slower across a network?
>
> 
On 6 Feb 2017, at 10:28am, dandl  wrote:

>We have an application we converted from Access to Sqlite. Mostly it's been a 
> great success, but we have two queries that runs 50x slower across a gigabit
> LAN...


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

Re: [sqlite] Exposing compile-time limits?

[Clemens Ladisch]

Richard Newman wrote:
> `sqlite3_limit` allows callers to discover the run-time value of limits
> such as `SQLITE_LIMIT_VARIABLE_NUMBER`.
>
> Callers can also *set* each limit, so long as the value is smaller than a
> compile-time define, in this case `SQLITE_MAX_VARIABLE_NUMBER`.
>
> But callers have no good way of determining the largest acceptable value:
> [...]
> Is this a deliberate omission?

Originally, SQLite was intended to be embedded into the application itself,
so it was assumed that you'd know what compilation option you used.

SQLite was not intended to be a part of a vaguely-defined platform.
(Of course, you could remove the "vaguely" from the previous sentence
and assume that Android etc. always use the same values.)


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

[Olivier Mascia]

> Le 5 févr. 2017 à 18:26, Clyde Eisenbeis  a écrit :
> 
> To clarify further, the SQLite function is attached. 

Attachments to this mailing list are stripped.
Better inline in the email itself if short or send privately to the recipient.
:)

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


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

Re: [sqlite] Patch Etiquette

[Jan Nijtmans]

2017-02-05 21:14 GMT+01:00 Ziemowit Laski:
> I have another patch in the works which will make 
> SQLITE_ENABLE_UPDATE_DELETE_LIMIT directly accessible from the amalgamation, 
> but am reluctant to submit it.

Well, I submitted such a patch already, some half year ago. You can
find the full thread here:



Feel free to try this patch (see my message Jul 12, 2016; 11:14am in
this thread)

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

Re: [sqlite] Why does a query run 50x slower across a network?

[Simon Slavin]

On 6 Feb 2017, at 10:28am, dandl  wrote:

> I'm hoping we've done something really dumb and obvious, but we can't see it. 

> CREATE INDEX [order_header_type_idx] ON [order_header] ([transaction_type], 
> [sale_type_id]);

Nothing really dumb, but this might help.

Create another two indexes with these three fields in this order:

transaction_type,status_code,sale_type_id
status_code,transaction_type,sale_type_id

Once you’ve done that, run the SQL command ANALYZE on that database.

You can delete the index it doesn’t end up using.

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

[sqlite] Exposing compile-time limits?

[Richard Newman]

Hello folks,

`sqlite3_limit` allows callers to discover the run-time value of limits
such as `SQLITE_LIMIT_VARIABLE_NUMBER`.

Callers can also *set* each limit, so long as the value is smaller than a
compile-time define, in this case `SQLITE_MAX_VARIABLE_NUMBER`.

But callers have no good way of determining the largest acceptable value:
they must do one of three things.

1. Be compiled at the same time as sqlite3.c and sniff the environment.
Obviously this isn't possible for systems that don't control their own
SQLite library.
2. Make sure to call `sqlite3_limit` when opening the first database
connection, and save the returned value.
3. Discover the acceptable maximum for an existing connection by changing
the limit: calling `sqlite3_limit` with a huge value, then calling it again
with -1 to fetch the truncated value. (Or calling *three* times to fetch,
change, restore.)

Is this a deliberate omission?

Thanks,

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

[sqlite] Patch Etiquette

[Ziemowit Laski]

Hello SQLite maintainers,

I'm glad that my patch below has been incorporated (with some changes) into 
future releases:

[50e60cb4] Modify the ICU 
extension to use a static initializer, as VC++ complains about a dynamic 
initialization. Maybe the dynamic structure initialization is a GCC extension. 
(user: 
drh,
 tags: 
trunk)

I think that SQLite is a great product, and I'm happy to be able to contribute 
to it.

HOWEVER, one thing bothers me:  You did not acknowledge my authorship of it.  
AFAICT, you NEVER seem to acknowledge third party contributions.  Clearly, I'm 
not user 'drh'.  Like with other open-source projects, I would expect my name 
and e-mail to appear in the commit message (and the changelog, if you had one), 
and definitely in the release notes.  This is the polite and professional thing 
to do, and may even encourage others to contribute to the project.

I have another patch in the works which will make 
SQLITE_ENABLE_UPDATE_DELETE_LIMIT directly accessible from the amalgamation, 
but am reluctant to submit it.

Thank you,

--Zem


From: Ziemowit Laski
Sent: Wednesday, 25 January 2017 12:36
To: sqlite-users@mailinglists.sqlite.org
Subject: BUG: Illegal initialization in icu.c : sqlite3IcuInit

Visual C++ correctly catches this.  The fragment

  struct IcuScalar {
const char *zName;/* Function name */
int nArg; /* Number of arguments */
int enc;  /* Optimal text encoding */
void *pContext;   /* sqlite3_user_data() context */
void (*xFunc)(sqlite3_context*,int,sqlite3_value**);
  } scalars[] = {
{"regexp", 2, SQLITE_ANY|SQLITE_DETERMINISTIC,  0, icuRegexpFunc},

{"lower",  1, SQLITE_UTF16|SQLITE_DETERMINISTIC,0, icuCaseFunc16},
{"lower",  2, SQLITE_UTF16|SQLITE_DETERMINISTIC,0, icuCaseFunc16},
{"upper",  1, SQLITE_UTF16|SQLITE_DETERMINISTIC, (void*)1, icuCaseFunc16},
{"upper",  2, SQLITE_UTF16|SQLITE_DETERMINISTIC, (void*)1, icuCaseFunc16},

{"lower",  1, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuCaseFunc16},
{"lower",  2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuCaseFunc16},
{"upper",  1, SQLITE_UTF8|SQLITE_DETERMINISTIC,  (void*)1, icuCaseFunc16},
{"upper",  2, SQLITE_UTF8|SQLITE_DETERMINISTIC,  (void*)1, icuCaseFunc16},

{"like",   2, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuLikeFunc},
{"like",   3, SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, icuLikeFunc},

{"icu_load_collation",  2, SQLITE_UTF8, (void*)db, icuLoadCollation},
  };

  int rc = SQLITE_OK;
  int i;

should read

   struct IcuScalar {
  const char *zName;/* Function name */
  int nArg; /* Number of arguments 
*/
  int enc;  /* Optimal text 
encoding */
  void *pContext;   /* sqlite3_user_data() 
context */
  void(*xFunc)(sqlite3_context*, int, sqlite3_value**);
   } scalars[] = {
  { "regexp", 2, SQLITE_ANY | SQLITE_DETERMINISTIC,  0, 
icuRegexpFunc },

  { "lower",  1, SQLITE_UTF16 | SQLITE_DETERMINISTIC,0, 
icuCaseFunc16 },
  { "lower",  2, SQLITE_UTF16 | SQLITE_DETERMINISTIC,0, 
icuCaseFunc16 },
  { "upper",  1, SQLITE_UTF16 | SQLITE_DETERMINISTIC, (void*)1, 
icuCaseFunc16 },
  { "upper",  2, SQLITE_UTF16 | SQLITE_DETERMINISTIC, (void*)1, 
icuCaseFunc16 },

  { "lower",  1, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
icuCaseFunc16 },
  { "lower",  2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
icuCaseFunc16 },
  { "upper",  1, SQLITE_UTF8 | SQLITE_DETERMINISTIC,  (void*)1, 
icuCaseFunc16 },
  { "upper",  2, SQLITE_UTF8 | SQLITE_DETERMINISTIC,  (void*)1, 
icuCaseFunc16 },

  { "like",   2, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
icuLikeFunc },
  { "like",   3, SQLITE_UTF8 | SQLITE_DETERMINISTIC, 0, 
icuLikeFunc },

  { "icu_load_collation",  2, SQLITE_UTF8, 0, icuLoadCollation }
   };

   int rc = SQLITE_OK;
   int i;

   scalars[11].pContext = (void*)db;

Thank you,

--Zem
___
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]

To clarify further, the SQLite function is attached.  Retrieving the
primary key is commented (used by System.Data.OleDb function).

On Sun, Feb 5, 2017 at 10:40 AM, J Decker  wrote:
> http://data.sqlite.org/c3ref/last_insert_rowid.html
>
> there is an api call to get it; or you can use select and get it
>
> the .net library has it as a connection property  LastInsertRowId
>
> https://www.crestron.com/reference/simpl_sharp/html/P_Crestron_SimplSharp_SQLite_SQLiteConnection_LastInsertRowId.htm
>
> On Sun, Feb 5, 2017 at 5:54 AM, Simon Slavin  wrote:
>
>>
>> On 5 Feb 2017, at 1:26pm, Clyde Eisenbeis  wrote:
>>
>> > The compiler complains about "SELECT last_insert_rowid()" ... which
>> > appears to be limited to SQLite3.
>>
>> The compiler should never have got that string.  The string is executed
>> when the program is already compiled, just like any other SELECT command.
>>
>> Find some context where you can execute any other SELECT command, and use
>> "last_insert_rowid()" as a column name.
>>
>> Simon.
>> ___
>> 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] Why does a query run 50x slower across a network?

[Rowan Worth]

I'm in a different environment (linux with database on nfs share) but found
the same behaviour. I came to the conclusion that the latency of network
file system operations combined with database fragmentation was largely
responsible for the reduced performance. SQLite is very seek heavy, unlike
a file copy.

SQLite's internal structure naturally results in lots of fragmentation
unless each table/index is fully populated in turn -- you might try a
VACUUM as a quick test and see if that speeds things up.

I've been experimenting with storing each table in a separate database file
and ATTACHing them all together with some success, but that doesn't help
you much since you only have one table!

I've not done the math but said table looks to have very large rows.
Depending on the page size you're using you may only see one or two rows
per read() op, which will kill performance if you ever need to query on an
unindexed column (requiring a table-scan).

I haven't looked at your queries/indices in any detail so no idea if
there's something particular to your design making the problem worse, just
sharing my experience.

-Rowan


On 6 February 2017 at 18:28, dandl  wrote:

> We have an application we converted from Access to Sqlite. Mostly it's
> been a great success, but we have two queries that runs 50x slower across a
> gigabit LAN than on a local file system and we don't know why. Performance
> on Access was perfectly acceptable, and on Sqlite is not and we can't
> figure out why. Customers are complaining, and with good reason.
>
> We're using System.Data.Sqlite and the file is being opened as a shared
> UNC pathname. The network can transfer at upwards of 250 Mbps on file
> copies, but the SQL query runs at around 10 Mbps (Windows Perfmon). The
> database is about 90MB. The queries takes 100ms on local file system and 5s
> on network share. [With customer data it can run into minutes.]
>
> I'm hoping we've done something really dumb and obvious, but we can't see
> it. Details follow. Anyone who can shed light very much appreciated.
>
> The query looks like this:
> Query1:
>
> SELECT  Max([date]) AS LastOfdate FROM order_header WHERE
> (((transaction_type)=1) AND ((status_code)=-1) AND ((sale_type_id)=1 Or
> (sale_type_id)=2 Or (sale_type_id)=14)) GROUP BY billToCardGuid,
> date([date]) HAVING billToCardGuid=X'A426D7165BBF0ECA3276555D32B6E385'
> ORDER BY date([date]) DESC limit 3
>
> Query2:
>
> SELECT  order_header.order_id AS maxID FROM order_header WHERE
> (((order_header.transaction_type)=1) AND ((order_header.status_code)=-1)
> AND ((order_header.sale_type_id)=1 Or (order_header.sale_type_id)=2 Or
> (order_header.sale_type_id)=14)) AND (order_header.billToCardGuid=X'
> A426D7165BBF0ECA3276555D32B6E385') ORDER BY [date] DESC, order_id desc
> limit 1
>
> The schema looks like this:
> CREATE TABLE IF NOT EXISTS "order_header" (
> "order_id" INTEGER DEFAULT 0,
> "user_name" VARCHAR(31) COLLATE NOCASE ,
> "number" INTEGER DEFAULT 0,
> "confirmation_number" VARCHAR(9) COLLATE NOCASE ,
> "creation_date" DATETIME,
> "modification_date" DATETIME,
> "transaction_type" SMALLINT DEFAULT 0,
> "customer_billto_last_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_first_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_company" VARCHAR(50) COLLATE NOCASE ,
> "customer_billto_address" VARCHAR(63) COLLATE NOCASE ,
> "customer_billto_city" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_state" VARCHAR(31) COLLATE NOCASE ,
> "customer_billto_zip" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_last_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_first_name" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_company" VARCHAR(50) COLLATE NOCASE ,
> "customer_shipto_address" VARCHAR(63) COLLATE NOCASE ,
> "customer_shipto_city" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_state" VARCHAR(31) COLLATE NOCASE ,
> "customer_shipto_zip" VARCHAR(31) COLLATE NOCASE ,
> "customer_fax" VARCHAR(31) COLLATE NOCASE ,
> "customer_ar_balance" REAL DEFAULT 0,
> "customer_bill_rate" REAL DEFAULT 0,
> "customer_tel" VARCHAR(31) COLLATE NOCASE ,
> "date" DATETIME,
> "status_description" VARCHAR(31) COLLATE NOCASE ,
> "status_code" SMALLINT DEFAULT 0,
> "order_comment" TEXT,
> "payment_comment" VARCHAR(63) COLLATE NOCASE ,
> "terms_description" VARCHAR(31) COLLATE NOCASE ,
> "shipmethod_description" VARCHAR(31) COLLATE NOCASE ,
> "shipmethod_amount" REAL DEFAULT 0,
> "shipmethod_tax_rate" REAL DEFAULT 0,
> "shipmethod_tax_code" VARCHAR(3) COLLATE NOCASE ,
> "tax_total" REAL DEFAULT 0,
> "ex_tax_total" REAL DEFAULT 0,
> "grand_total" REAL DEFAULT 0,
> "pay_amount" REAL DEFAULT 0,
> "balance" REAL DEFAULT 0,
> "card" VARCHAR(19) COLLATE NOCASE ,
> "exp" VARCHAR(4) COLLATE NOCASE ,
> "po" VARCHAR(15) COLLATE NOCASE ,
> "payment_date" DATETIME,
> "printed_name" VARCHAR(31) COLLATE NOCASE ,
> "signature" BLOB,
> "line_item_count" SMALLINT DEFAULT 0,
> "flags" SMALLINT DEFAULT 0,
> "employeeGuid" GUID,
> 

[sqlite] Why does a query run 50x slower across a network?

[dandl]

We have an application we converted from Access to Sqlite. Mostly it's been a 
great success, but we have two queries that runs 50x slower across a gigabit 
LAN than on a local file system and we don't know why. Performance on Access 
was perfectly acceptable, and on Sqlite is not and we can't figure out why. 
Customers are complaining, and with good reason.

We're using System.Data.Sqlite and the file is being opened as a shared UNC 
pathname. The network can transfer at upwards of 250 Mbps on file copies, but 
the SQL query runs at around 10 Mbps (Windows Perfmon). The database is about 
90MB. The queries takes 100ms on local file system and 5s on network share. 
[With customer data it can run into minutes.]

I'm hoping we've done something really dumb and obvious, but we can't see it. 
Details follow. Anyone who can shed light very much appreciated.

The query looks like this:
Query1:

SELECT  Max([date]) AS LastOfdate FROM order_header WHERE 
(((transaction_type)=1) AND ((status_code)=-1) AND ((sale_type_id)=1 Or 
(sale_type_id)=2 Or (sale_type_id)=14)) GROUP BY billToCardGuid, date([date]) 
HAVING billToCardGuid=X'A426D7165BBF0ECA3276555D32B6E385' ORDER BY date([date]) 
DESC limit 3

Query2:

SELECT  order_header.order_id AS maxID FROM order_header WHERE 
(((order_header.transaction_type)=1) AND ((order_header.status_code)=-1) AND 
((order_header.sale_type_id)=1 Or (order_header.sale_type_id)=2 Or 
(order_header.sale_type_id)=14)) AND 
(order_header.billToCardGuid=X'A426D7165BBF0ECA3276555D32B6E385') ORDER BY 
[date] DESC, order_id desc limit 1

The schema looks like this:
CREATE TABLE IF NOT EXISTS "order_header" (
"order_id" INTEGER DEFAULT 0,
"user_name" VARCHAR(31) COLLATE NOCASE ,
"number" INTEGER DEFAULT 0,
"confirmation_number" VARCHAR(9) COLLATE NOCASE ,
"creation_date" DATETIME,
"modification_date" DATETIME,
"transaction_type" SMALLINT DEFAULT 0,
"customer_billto_last_name" VARCHAR(31) COLLATE NOCASE ,
"customer_billto_first_name" VARCHAR(31) COLLATE NOCASE ,
"customer_billto_company" VARCHAR(50) COLLATE NOCASE ,
"customer_billto_address" VARCHAR(63) COLLATE NOCASE ,
"customer_billto_city" VARCHAR(31) COLLATE NOCASE ,
"customer_billto_state" VARCHAR(31) COLLATE NOCASE ,
"customer_billto_zip" VARCHAR(31) COLLATE NOCASE ,
"customer_shipto_last_name" VARCHAR(31) COLLATE NOCASE ,
"customer_shipto_first_name" VARCHAR(31) COLLATE NOCASE ,
"customer_shipto_company" VARCHAR(50) COLLATE NOCASE ,
"customer_shipto_address" VARCHAR(63) COLLATE NOCASE ,
"customer_shipto_city" VARCHAR(31) COLLATE NOCASE ,
"customer_shipto_state" VARCHAR(31) COLLATE NOCASE ,
"customer_shipto_zip" VARCHAR(31) COLLATE NOCASE ,
"customer_fax" VARCHAR(31) COLLATE NOCASE ,
"customer_ar_balance" REAL DEFAULT 0,
"customer_bill_rate" REAL DEFAULT 0,
"customer_tel" VARCHAR(31) COLLATE NOCASE ,
"date" DATETIME,
"status_description" VARCHAR(31) COLLATE NOCASE ,
"status_code" SMALLINT DEFAULT 0,
"order_comment" TEXT,
"payment_comment" VARCHAR(63) COLLATE NOCASE ,
"terms_description" VARCHAR(31) COLLATE NOCASE ,
"shipmethod_description" VARCHAR(31) COLLATE NOCASE ,
"shipmethod_amount" REAL DEFAULT 0,
"shipmethod_tax_rate" REAL DEFAULT 0,
"shipmethod_tax_code" VARCHAR(3) COLLATE NOCASE ,
"tax_total" REAL DEFAULT 0,
"ex_tax_total" REAL DEFAULT 0,
"grand_total" REAL DEFAULT 0,
"pay_amount" REAL DEFAULT 0,
"balance" REAL DEFAULT 0,
"card" VARCHAR(19) COLLATE NOCASE ,
"exp" VARCHAR(4) COLLATE NOCASE ,
"po" VARCHAR(15) COLLATE NOCASE ,
"payment_date" DATETIME,
"printed_name" VARCHAR(31) COLLATE NOCASE ,
"signature" BLOB,
"line_item_count" SMALLINT DEFAULT 0,
"flags" SMALLINT DEFAULT 0,
"employeeGuid" GUID,
"employee_bill_rate" REAL DEFAULT 0,
"employee_name" VARCHAR(31) COLLATE NOCASE ,
"date_hotsynced" DATETIME,
"date_exported_to_myob" DATETIME,
"export_status" SMALLINT DEFAULT 0,
"export_error_no" INTEGER DEFAULT 0,
"attempt_export" BOOL NOT NULL DEFAULT 1,
"invoice_status" CHAR(1) DEFAULT 'I',
"sale_type_id" INTEGER DEFAULT 1,
"export_Error_Guid" GUID,
"validated" BOOL NOT NULL DEFAULT 0,
"reconciled" BOOL NOT NULL DEFAULT 0,
"txnGuid" GUID,
"cardGuid" GUID,
"billToCardGuid" GUID,
"shipToCardGuid" GUID,
"locationFromCardGuid" GUID,
"locationToCardGuid" GUID,
"unidentified_chunks" BLOB,
"toDoGuid" GUID,
"uom_pick_mode" BOOL NOT NULL DEFAULT 0,
"validationGuid" GUID,
"territoryGuid" GUID,
"territoryGroupGuid" GUID,
"hasTerritory" BOOL NOT NULL DEFAULT 0,
"parentTranGuid" GUID,
"cartonQuantity" REAL,
"pickInstructions" VARCHAR(64) COLLATE NOCASE ,
"creator" INTEGER,
"POSMode" BOOL NOT NULL DEFAULT 0,
"Locked" BOOL NOT NULL DEFAULT 0,
"relatedTransactionGuid" GUID,
"displayMode" INTEGER,
"signature_date" DATETIME,
"freezerFull" BOOL NOT NULL DEFAULT 0,
"sortOrder" INTEGER,
"handheldViewed" BOOL NOT NULL DEFAULT 0,
"managerGuid" GUID,
"templateTranGuid" GUID,
"approved" BOOL NOT NULL DEFAULT 0, [pay_amount_exported] REAL,
CONSTRAINT "order_header_order_id" PRIMARY KEY("order_id"));
CREATE UNIQUE INDEX "order_header_txnGuid" ON "order_header" 

Re: [sqlite] Transactions

[Hick Gunter]

Yes, putting a large number of inserts that affect the same table(s) into ona 
bulk transaction can be a huge speedup, because the operations can take place 
in memory without having to reach the disk surface until commit time.

The optimal number of inserts/transaction depends on your hardware setup and 
who else needs access to CPU and I/O resources.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Michele Pradella
Gesendet: Sonntag, 05. Februar 2017 09:42
An: SQLite mailing list 
Betreff: [sqlite] Transactions

Hi all, I have a question about transactions and SQLite:

Do you think transactions are useful only when you have to do a sequence of 
statements that depends on each other and you need a way to rollback all 
statements if something goes wrong? or you can use transactions even with not 
interdependent statements for performance reason? and if yes do you think 
there's a trade-off about the number of query number in each transaction?

I'm think about 1000 INSERT query to execute, is transaction works better 
because you do not have to change index at any insert but just one time on 
commit?
___
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