Re: [sqlite] Bug report + fix: SQLite 3.11+ broken on EBCDIC systems

[Scott Hess]

On Mon, Dec 12, 2016 at 9:30 PM, Bradford Larsen  wrote:
> An alternative possibility would be to revert to the pre-3.11 tokenizer on
> EBCDIC systems.  If I recall, the old tokenizer used a big switch statement
> with character literals instead of the 'aiClass' table.  I believe this
> would avoid the EBCDIC tokenizing troubles, at the expense of lower
> performance on those systems, and with the maintenance cost of keeping 2
> code paths around.

Or the build pass could compile a switch-based generator.  It would
make cross-compiling more complicated.

Perhaps easier would be to have a set of unrolled table initializers
which used the raw characters as indices, called as part of
sqlite3_init().

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

Re: [sqlite] Bug report + fix: SQLite 3.11+ broken on EBCDIC systems

[Bradford Larsen]

>
> On Sun, Dec 11, 2016 at 20:42 Richard Hipp  wrote:
>
> SQLite only uses the "[" character as a compatibility quoting
>
> mechanism for SQL Server.  Maybe the solution is for [...] quoting to
>
> simply not work on EBCDIC systems?
>
>
>
> --
>
> D. Richard Hipp
>
> d...@sqlite.org
>
> This would be one possibility—you then might be able to simultaneously
support several EBCDIC code pages with a single 'aiClass' definition (cp37
and cp1047 for example).

(For what it's worth, I'm sure my application doesn't use this
compatibility quoting mechanism on the EBCDIC system.)

However, maybe it would be better to pick a specific EBCDIC code page to
provide an 'aiClass' definition for in the SQLite sources, and make a note
about this in the docs—perhaps something like this to start:

"SQLite is designed to compile and run on EBCDIC-based systems that use
code page $CHOSEN_CODEPAGE.  If you wish to build SQLite for a system that
uses a different EBCDIC codepage, you will need to modify the definition of
the 'aiClass' table used by the SQL tokenizer."

An alternative possibility would be to revert to the pre-3.11 tokenizer on
EBCDIC systems.  If I recall, the old tokenizer used a big switch statement
with character literals instead of the 'aiClass' table.  I believe this
would avoid the EBCDIC tokenizing troubles, at the expense of lower
performance on those systems, and with the maintenance cost of keeping 2
code paths around.

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

Re: [sqlite] Disk I/O utilization

[Quan Yong Zhai]

1. The latest SQLite improved REPLACE operation, It's alot faster now.
2.use SAVEPOINT  to batch transactions. They'll save many IO.

发自我的 Mobile Phone

发件人: Vince Scafaria
发送时间: ‎2016/‎12/‎13 9:24
收件人: 
sqlite-users@mailinglists.sqlite.org
主题: [sqlite] Disk I/O utilization

Our application does a lot of SQLite read/write in a background process. On 
some environments this hammers the disk I/O (e.g. Task Manager -> Disk). Do you 
have suggestions on how we might minimize this?

Perhaps related, one operation that seems to use a lot of the Disk I/O is when 
we use INSERT OR REPLACE INTO. We use this as a workaround for performing an 
UPDATE together with JOIN, which is not allowed by SQLite. I feel as though one 
way we could minimize disk usage would be if we could use the MSSQL syntax of:

UPDATE x
SET ValueA = y.ValueA
FROM TableOne x
JOIN TableTwo y ON y.Id = x.TableTwoId
WHERE COALESCE(x.ValueA,'') != COALESCE(y.ValueA,'');

Instead we do:

INSERT OR REPLACE INTO TableOne ( Id, TableTwoId, ValueA )
SELECT x.Id, x.TableTwoId, y.ValueA
FROM TableOne x
JOIN TableTwo y ON y.Id = x.TableTwoId
WHERE COALESCE(x.ValueA,'') != COALESCE(y.ValueA,'');

Our tables all have a primary key on Id. By including Id in the statement, it 
does a REPLACE. As I understand it, replace includes a behind-the-scenes DELETE 
which triggers a re-indexing. Maybe that is part of the problem, but I don't 
see a workaround. Ideas?

Thank you!

Vince
___
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] Disk I/O utilization

[Keith Medcalf]

On Monday, 12 December, 2016 18:24, Vince Scafaria  wrote:
 
> Our application does a lot of SQLite read/write in a background process.
> On some environments this hammers the disk I/O (e.g. Task Manager ->
> Disk). Do you have suggestions on how we might minimize this?
 
> Perhaps related, one operation that seems to use a lot of the Disk I/O is
> when we use INSERT OR REPLACE INTO. We use this as a workaround for
> performing an UPDATE together with JOIN, which is not allowed by SQLite. I
> feel as though one way we could minimize disk usage would be if we could
> use the MSSQL syntax of:
 
> UPDATE x
> SET ValueA = y.ValueA
> FROM TableOne x
> JOIN TableTwo y ON y.Id = x.TableTwoId
> WHERE COALESCE(x.ValueA,'') != COALESCE(y.ValueA,'');
 
> Instead we do:
 
> INSERT OR REPLACE INTO TableOne ( Id, TableTwoId, ValueA )
> SELECT x.Id, x.TableTwoId, y.ValueA
> FROM TableOne x
> JOIN TableTwo y ON y.Id = x.TableTwoId
> WHERE COALESCE(x.ValueA,'') != COALESCE(y.ValueA,'');
 
> Our tables all have a primary key on Id. By including Id in the statement,
> it does a REPLACE. As I understand it, replace includes a behind-the-
> scenes DELETE which triggers a re-indexing. Maybe that is part of the
> problem, but I don't see a workaround. Ideas?

I do not see any need for INSERT, you are doing a simple update.  Since your 
select is constrained by existing rows in TableOne (which you are updating), 
you only need to update, not delete and insert (which you are doing for every 
row).  How about something like:

UPDATE TableOne
   SET ValueA = (SELECT ValueA
   FROM TableTwo
  WHERE TableTwo.Id = TableOne.TableTwoID)
 WHERE COALESCE(TableOne.ValueA, '') != COALESCE((SELECT ValueA
FROM TableTwo
   WHERE TableTwo.Id = 
TableOne.TableTwoID). '')

Note that if your database were properly normalized, you would not have to do 
this (that is, ValueA is entirely dependant on TableTwoID, and TableTwoID is a 
foreign key into TableTwo).  If you were not duplicating data then this issue 
would not arise.  This is part of the problem with having multiple datastores 
-- you never know which one is accurate.  And if TableOne.ValueA is merely 
duplicated data (and therefore always to be assumed to be incorrect), then you 
may as well not have it in TableOne just do a lookup on the fly.  If you think 
that makes your queries look too complicated, then you can define a view which 
does it for you and only select through the view.

If you really must de-normalize your data for some reason, then you should put 
a trigger on TableOne (and perhaps TableTwo) that keep them in sync when 
updated (or inserted into) so that you do not have to run updates such as the 
above.




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

Re: [sqlite] Disk I/O utilization

[Simon Slavin]

On 13 Dec 2016, at 1:23am, Vince Scafaria  wrote:

> Our application does a lot of SQLite read/write in a background process. On 
> some environments this hammers the disk I/O (e.g. Task Manager -> Disk). Do 
> you have suggestions on how we might minimize this?

Batch them together in a transaction:

BEGIN;
Lots of SQL changes;
COMMIT;

If you don’t explicitly make a transaction SQLite automatically makes one per 
command.  Since most of the changes made to the file are made once per 
transaction, declaring an explicit transaction for each – for example – 
thousand changes means you end up with far less disk access.

Does data integrity matter if the application crashes ?  Or is the file 
important only to the current session ?  If data integrity isn’t important you 
could try some of the PRAGMAs which speed things up.

> Our tables all have a primary key on Id. By including Id in the statement, it 
> does a REPLACE. As I understand it, replace includes a behind-the-scenes 
> DELETE which triggers a re-indexing. Maybe that is part of the problem, but I 
> don't see a workaround. Ideas?

Don’t worry about that.  It’s already being done in the most efficient way 
practicql.

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

[sqlite] Disk I/O utilization

[Vince Scafaria]

Our application does a lot of SQLite read/write in a background process. On 
some environments this hammers the disk I/O (e.g. Task Manager -> Disk). Do you 
have suggestions on how we might minimize this?

Perhaps related, one operation that seems to use a lot of the Disk I/O is when 
we use INSERT OR REPLACE INTO. We use this as a workaround for performing an 
UPDATE together with JOIN, which is not allowed by SQLite. I feel as though one 
way we could minimize disk usage would be if we could use the MSSQL syntax of:

UPDATE x
SET ValueA = y.ValueA
FROM TableOne x
JOIN TableTwo y ON y.Id = x.TableTwoId
WHERE COALESCE(x.ValueA,'') != COALESCE(y.ValueA,'');

Instead we do:

INSERT OR REPLACE INTO TableOne ( Id, TableTwoId, ValueA )
SELECT x.Id, x.TableTwoId, y.ValueA
FROM TableOne x
JOIN TableTwo y ON y.Id = x.TableTwoId
WHERE COALESCE(x.ValueA,'') != COALESCE(y.ValueA,'');

Our tables all have a primary key on Id. By including Id in the statement, it 
does a REPLACE. As I understand it, replace includes a behind-the-scenes DELETE 
which triggers a re-indexing. Maybe that is part of the problem, but I don't 
see a workaround. Ideas?

Thank you!

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

Re: [sqlite] Error code for VT xColumn()/xRowid() if row does not exist?

[Bob Friesenhahn]

On Tue, 13 Dec 2016, Dan Kennedy wrote:


Perhaps SQLITE_NOTFOUND is a correct return code, but the documentation 
does not address return codes from virtual table modules.


What is the correct code to return?  If a cursor is being navigated, then I 
would prefer that the cursor continue to the next result row.


If you want the query to continue, the virtual table methods must return 
SQLITE_OK. Anything else will cause SQLite to abandon the query and return 
the error to the user. Have the xColumn() method return NULL in this case I 
guess.


Ok, thanks.

Or you could load all the column values into memory when your virtual table 
cursor visits each row of the underlying data. Then you wouldn't have to 
handle the current row disappearing on you.


We are normally doing column caching as you describe.  Even without 
the caching I can lock for column access at the row level.  The case 
where the row might no longer exist is for xRowid(), but this function 
does not seem to be called for the table I am looking at.


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] Error code for VT xColumn()/xRowid() if row does not exist?

[Dan Kennedy]

On 12/13/2016 01:47 AM, Bob Friesenhahn wrote:
I am reworking code for a virtual table module and need to provide 
proper error codes from the xColumn() and xRowid() callbacks for the 
case where the row id does not exist, or the current row goes away. 
This problem occurs because the current virtual table module 
implementation does not lock for its entire access (a matter for 
subsequent investigation).


The documentation just says to "return an appropriate error code".

Perhaps SQLITE_NOTFOUND is a correct return code, but the 
documentation does not address return codes from virtual table modules.


What is the correct code to return?  If a cursor is being navigated, 
then I would prefer that the cursor continue to the next result row.


If you want the query to continue, the virtual table methods must return 
SQLITE_OK. Anything else will cause SQLite to abandon the query and 
return the error to the user. Have the xColumn() method return NULL in 
this case I guess.


Or you could load all the column values into memory when your virtual 
table cursor visits each row of the underlying data. Then you wouldn't 
have to handle the current row disappearing on you.


Dan.




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

Re: [sqlite] Run non-data producing statement just once to test UDF

[Bart Smissaert]

> Select AddOne(  );

Yes, that might be the best option indeed.
Still though it needs then to detect what the function does to supply the
right arguments and to construct
the right statement in code.
I solved my problem in a different way.

RBS



On Mon, Dec 12, 2016 at 12:12 PM, Hick Gunter  wrote:

> Select AddOne(  );
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Bart Smissaert
> Gesendet: Sonntag, 11. Dezember 2016 22:39
> An: General Discussion of SQLite Database  sqlite.org>
> Betreff: [sqlite] Run non-data producing statement just once to test UDF
>
> Say I have a UDF called AddOne in a statement like this:
>
> update table1 set field1 = AddOne(field1)
>
> and say the table table1 has some million rows.
>
> Now I want to test this statement to test the UDF but I want to do this
> for only one row.
> How do I do this?
>
> limit 1 only applies to data producing statements. Adding a where clause,
> say where rowid = 1 is a bit tricky (all this has to be done in code) and
> there may not be a rowid of 1 and I can't do a sqlite3_step as that would
> run for all the rows.
> There is no such problem with select statements as I can just run one
> sqlite3_step and just fetch no data and do a sqlite3_reset.
>
> Any suggestions?
>
>
> RBS
> ___
> 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] Error code for VT xColumn()/xRowid() if row does not exist?

[Bob Friesenhahn]

I am reworking code for a virtual table module and need to provide 
proper error codes from the xColumn() and xRowid() callbacks for the 
case where the row id does not exist, or the current row goes away. 
This problem occurs because the current virtual table module 
implementation does not lock for its entire access (a matter for 
subsequent investigation).


The documentation just says to "return an appropriate error code".

Perhaps SQLITE_NOTFOUND is a correct return code, but the 
documentation does not address return codes from virtual table 
modules.


What is the correct code to return?  If a cursor is being navigated, 
then I would prefer that the cursor continue to the next result row.


Thanks,

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] System.Data.SQLite -> ChangePassword 2nd time has opposite behavior

[Jens Alfke]

> On Dec 12, 2016, at 5:00 AM, Baugher, Melody  wrote:
> 
>  string passwordPragma = "PRAGMA key='" + password + "';";

Not quoting the password string leaves you open to SQL injection attack. This 
probably isn’t an issue in an internal development tool, but if there’s any 
code like this in the main program, it’s a serious vulnerability, so I thought 
I’d point it out.

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

Re: [sqlite] System.Data.SQLite -> ChangePassword 2nd time has opposite behavior

[Simon Slavin]

On 12 Dec 2016, at 1:00pm, Baugher, Melody  wrote:

> Starting with an encrypted database, if I run this code twice in a row with 
> applyPassword=false, the password is removed the first time but then applied 
> the second time.

Your program is weird and buggy.  There is one big test in the program:

>  if (applyPassword)

Before this test is done, the program carefully constructs a command in the 
variable "passwordPragma" but uses it only if "applyPassword" is /not/ true.  
It then immediately issues

> connection.ChangePassword("");

which would undo the change it just made.

It also has several things inside the test (create a connection, use 
"activatePragma") which are done whether the test is true or false.  They 
should be moved outside the test.

Now I’ve told you that the program is weird take a look through it, tidy up the 
logic, and write a better one.

[What’s really amusing here is that decades of programming experience let me 
read that program even though I have never used whatever programming language 
it’s written in or the API it uses.]

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

[sqlite] System.Data.SQLite -> ChangePassword 2nd time has opposite behavior

[Baugher, Melody]

My SQLite databases are encrypted at build time.

Small tools were developed to remove or apply the password to all 9 databases 
after build to facilitate the work of the database developers. The tools run 
the following bit of code against each database:

  string activatePragma = "PRAGMA activate_extensions='SEE Code';";
  string passwordPragma = "PRAGMA key='" + password + "';";
  SQLiteConnection connection = new SQLiteConnection("Data Source = " + 
databaseFile);
  connection.Open();
 if (applyPassword)
  {
using (SQLiteCommand pragmaCommand = new SQLiteCommand(connection))
{
  pragmaCommand.CommandText = activatePragma;
  pragmaCommand.ExecuteNonQuery();
}
connection.ChangePassword(password);
  }
  else
  {
using (SQLiteCommand pragmaCommand = new SQLiteCommand(connection))
{
  pragmaCommand.CommandText = activatePragma;
  pragmaCommand.ExecuteNonQuery();
  pragmaCommand.CommandText = passwordPragma;
  pragmaCommand.ExecuteNonQuery();
}
connection.ChangePassword("");
  }
  connection.Close();

Starting with an encrypted database, if I run this code twice in a row with 
applyPassword=false, the password is removed the first time but then applied 
the second time.

Similarly, starting with an unencrypted database, fi I run this code twice in a 
row with applyPassword=true, the password is applied the first time but then 
removed the second time.

I can kind of see where perhaps when running the removal the second time, the 
"PRAGMA key=" bit is actually now applying the password.

But I don't understand why executing ChangePassword twice in a row on an 
unencrypted database would remove the password the second time.
Take care,
Melody R Baugher
DCS Corporation
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Run non-data producing statement just once to test UDF

[Hick Gunter]

Select AddOne(  );

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Bart Smissaert
Gesendet: Sonntag, 11. Dezember 2016 22:39
An: General Discussion of SQLite Database 
Betreff: [sqlite] Run non-data producing statement just once to test UDF

Say I have a UDF called AddOne in a statement like this:

update table1 set field1 = AddOne(field1)

and say the table table1 has some million rows.

Now I want to test this statement to test the UDF but I want to do this for 
only one row.
How do I do this?

limit 1 only applies to data producing statements. Adding a where clause, say 
where rowid = 1 is a bit tricky (all this has to be done in code) and there may 
not be a rowid of 1 and I can't do a sqlite3_step as that would run for all the 
rows.
There is no such problem with select statements as I can just run one 
sqlite3_step and just fetch no data and do a sqlite3_reset.

Any suggestions?


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