Re: [sqlite] A small technical question about SQLite

[Scott Robison]

Or something.

{google}

Nothing obvious from a search. It must be safe!

On Mon, Oct 24, 2016 at 8:49 PM, Jens Alfke  wrote:

> I’m guessing it’s probably a phone-sex line. No one would actually expect
> strangers to call them to answer their homework problems.
>
> —Jens
>
> > On Oct 24, 2016, at 7:20 PM, Scott Robison 
> wrote:
> >
> > Don't everyone dial at once!
> >
> > On Mon, Oct 24, 2016 at 2:36 PM, LIAT SEAGAL-DERY <
> liat.seagald...@gmail.com
> >> wrote:
> >
> >> Hi,
> >>
> >> I am a student in SUNYPOLY, working on my homework. I have a small
> >> question.
> >> Can someone please contact me? I prefer a phone call at 585-473-6501
> >>
> >> Thank you,
> >> Liat
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > Scott Robison
> > ___
> > 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
>



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

Re: [sqlite] A small technical question about SQLite

[Jens Alfke]

I’m guessing it’s probably a phone-sex line. No one would actually expect 
strangers to call them to answer their homework problems.

—Jens

> On Oct 24, 2016, at 7:20 PM, Scott Robison  wrote:
> 
> Don't everyone dial at once!
> 
> On Mon, Oct 24, 2016 at 2:36 PM, LIAT SEAGAL-DERY > wrote:
> 
>> Hi,
>> 
>> I am a student in SUNYPOLY, working on my homework. I have a small
>> question.
>> Can someone please contact me? I prefer a phone call at 585-473-6501
>> 
>> Thank you,
>> Liat
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> Scott Robison
> ___
> 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] A small technical question about SQLite

[Scott Robison]

Don't everyone dial at once!

On Mon, Oct 24, 2016 at 2:36 PM, LIAT SEAGAL-DERY  wrote:

> Hi,
>
> I am a student in SUNYPOLY, working on my homework. I have a small
> question.
> Can someone please contact me? I prefer a phone call at 585-473-6501
>
> Thank you,
> Liat
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] A small technical question about SQLite

[LIAT SEAGAL-DERY]

Hi,

I am a student in SUNYPOLY, working on my homework. I have a small question.
Can someone please contact me? I prefer a phone call at 585-473-6501

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

[sqlite] Bug in latest sqlite Release vacuum crashes?

[Christian Czech]

We have a problem with the latest stable release of SQLite. When compiled 
for 32 bit under windows and vacuum a database file larger than 2GB, memory 
storage usage gets up to 2GB and than vacuum crashes with not enough 
memory. It seems that a temp file is not generated, not for standard and 
not for Wal database.


Anybody else got into this issue?

Regards,
cc



Am 24. Oktober 2016 2:00:16 nachm. schrieb 
sqlite-users-requ...@mailinglists.sqlite.org:



Send sqlite-users mailing list submissions to
sqlite-users@mailinglists.sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
sqlite-users-requ...@mailinglists.sqlite.org

You can reach the person managing the list at
sqlite-users-ow...@mailinglists.sqlite.org

When replying, please edit your Subject line so it is more specific
than "Re: Contents of sqlite-users digest..."


Today's Topics:

   1. Re: Import 5000 xml files in a sqlite database file
  (Sylvain Pointeau)
   2. Re: Import 5000 xml files in a sqlite database file
  (Eduardo Morras)
   3. Development environment reccomendation (Philip Rhoades)
   4. How does the pager know whether its memory cache is still
  valid? (Rowan Worth)
   5. Re: How does the pager know whether its memory cache  is  still
  valid? (Clemens Ladisch)
   6. Re: How does the pager know whether its memory cache is still
  valid? (Rowan Worth)
   7. Best way to wipe out data of a closed database (Max Vlasov)
   8. Warning automatic index on (Werner Kleiner)
   9. Re: Warning automatic index on (Rowan Worth)
  10. Re: Development environment reccomendation (Simon Slavin)
  11. Re: Best way to wipe out data of a closed database (Simon Slavin)
  12. Re: Best way to wipe out data of a closed database (Richard Hipp)
  13. Re: Import 5000 xml files in a sqlite database file (Kevin Youren)
  14. Re: Warning automatic index on (Simon Slavin)
  15. Re: Import 5000 xml files in a sqlite database file
  (Preguntón Cojonero Cabrón)
  16. Re: Virtual table acting as wrapper of a regular table
  (Hick Gunter)
  17. Re: Warning automatic index on (Richard Hipp)
  18. Re: Best way to wipe out data of a closed database (Max Vlasov)
  19. Re: Best way to wipe out data of a closed database (Max Vlasov)


--

Message: 1
Date: Sun, 23 Oct 2016 18:03:57 +0200
From: Sylvain Pointeau 
To: SQLite mailing list 
Subject: Re: [sqlite] Import 5000 xml files in a sqlite database file
Message-ID:

Re: [sqlite] Virtual table acting as wrapper of a regular table

[Dimitris Bil]

Hick,


sorry, I think I misunderstood your first reply.


Yes, of course I am calling each method twice when I use the wrapper: one time 
in the wrapper and one time in B, so an overhead is absolutely expected. But I 
would not expect this overhead to be equal to the time of the total execution 
without the wrapper, as the wrapper's methods pretty much do nothing more that 
calling the methods of table B that do the actual work. So I would expect the 
time to be double, if apart from calling the wrapper's methods I would also 
scan table A twice and searching B table double the number of the times I do in 
the normal execution.


Best,

Dimitris



From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Monday, October 24, 2016 1:59 PM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Virtual table acting as wrapper of a regular table

When you prepare() your statement, SQLite translates this into 2 nested loops:

Outer loop:
Rewind table A (retrieve first row)
Extract id from table A row
...
Retrieve next row from table A

Inner loop:
Set table B to A.id
Extract id from B
Check for match
(return result row)
Retrieve next row from table B

When you are using your wrapper, the inner loop gets replaced by:
Call xFilter() -> reset(), bind(), step(), column_value() .> Set table B to 
A.id, Extract id from table B row, check for match
Call xEof()
Call xColumn() -> return result value
Call xNext() -> step(), column_value() -> Retrieve next row from table B

This is about twice as much work.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dimitris Bil
Gesendet: Montag, 24. Oktober 2016 15:10
An: SQLite mailing list 
Betreff: Re: [sqlite] Virtual table acting as wrapper of a regular table

Indeed I am not checking argc. This code just only means to serve as a test in 
order to make comparisons for execution speed. I have stripped it of any other 
functionality.


Regarding prepare, I am only preparing the statement in xConnect (which also 
acts as xCreate). This is only executed once, when connection to the virtual 
table is opened, right?

Regarding the step method, I am executing a step the first time inside xFilter 
in order to advance the cursor in the first result. Then each time the xNext is 
called, I am executing step one more time to continue advancing the cursor. I 
think this is in accordance to what the sqlite page says: "If the virtual table 
contains one or more rows that match the search criteria, then the cursor must 
be left point at the first row. Subsequent calls to xEof must return false 
(zero). If there are no rows match, then the cursor must be left in a state 
that will cause the xEof to return true (non-zero). The SQLite engine will use 
the xColumn and xRowid methods to access that row content. The xNext method 
will be used to advance to the next row."

Also, I am using sqlite3_result_int64 only one time per result tuple inside 
xColumn. In my case, as each result tuple is the same, this indeed could be 
avoided, but I don't think that this is the cause for the overhead.

thanks again,
Dimitris




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Monday, October 24, 2016 10:39 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Virtual table acting as wrapper of a regular table

Your xFilter method is blindly assuming that there is always an argv[0] without 
checking argc first.

You are incurring an extra "prepare" in your xConnect method, an extra "step" 
and "column" in your xFilter/xNext methods and an extra "result" in your 
xColumn function. Doing twice as much work taking twice as long seems quite 
reasonable.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dimitris Bil
Gesendet: Samstag, 22. Oktober 2016 15:31
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Virtual table acting as wrapper of a regular table

Hello,

I am trying to create a simple virtual table that acts as wrapper for a normal 
database table for a specific query. For example, consider that I have tables A 
and B in my database and the query:
select count(A.id) from A CROSS JOIN B where A.id=B.id

Now I am using a virtual table acting as wrapper for table B, so I can execute 
the query select count(A.id) from A CROSS JOIN w where A.id=w.id, where w is 
the virtual table instance, and get the same result. My problem is that the 
second query is about 2 times slower. I would normally expect some overhead, 
but this seems quite slow, so I was wondering if there is something wrong with 
my code.

I am using a prepared statement in connect method for query "select id from B 
where 

Re: [sqlite] spellfix1 is unable to match words beginning with 'Kn' and 'Gn'

[Richard Hipp]

On 10/20/16, Thomas David Baker  wrote:
> Is this the right list to report issues with the spellfix1 extension?
>
> Here's a complete repro case:

lowercase your vocabulary and it should work fine.  The special case
logic for unusual letter combinations like "kn" and "gn" only works
for lowercase letters.

>
> [deepthought ~] ls -l newdb
> ls: newdb: No such file or directory
> [deepthought ~] sqlite3 newdb
> SQLite version 3.14.2 2016-09-12 18:50:49
> Enter ".help" for usage hints.
> sqlite> SELECT LOAD_EXTENSION('spellfix1.dylib');
> sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS fuzzy USING spellfix1;
> sqlite> INSERT INTO fuzzy (word) VALUES ('Knight');
> sqlite> SELECT word, distance FROM fuzzy WHERE word = 'Knight';
> Knight|
> sqlite> SELECT word, distance FROM fuzzy WHERE word MATCH 'Knight';
> sqlite> SELECT word, distance FROM fuzzy WHERE word MATCH 'Night';
> Knight|100
> sqlite>
>
> Is this something configurable? Is it a bug?
>
> Thanks!
>
> Tom
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Does Reindex Change Stats Tables?

[Richard Hipp]

On 10/24/16, Denis Burke  wrote:
>> REINDEX does *not* update the stats.  You must run ANALYZE separately.
>
>> May I ask why you are running REINDEX?
>
> Sure.  We issue new releases of our application about every 3 months.  With
> some of these, we update the schema of the underlying DB.  And with some of
> these schema updates, we have changed the collation sequence (typically
> added or removed collation).

REINDEX is only needed if you change the implementation of a collation
without changing its name.

REINDEX was created to allow indexes to be rebuilt on phones when the
customer changed the collating methods for hanzi or kanji.

-- 
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] Does Reindex Change Stats Tables?

[Denis Burke]

> REINDEX does *not* update the stats.  You must run ANALYZE separately.

> May I ask why you are running REINDEX?

Sure.  We issue new releases of our application about every 3 months.  With
some of these, we update the schema of the underlying DB.  And with some of
these schema updates, we have changed the collation sequence (typically
added or removed collation).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does the pager know whether its memory cache is still valid?

[Dan Kennedy]

On 10/25/2016 01:12 AM, Jens Alfke wrote:

On Oct 24, 2016, at 1:31 AM, Rowan Worth  wrote:

OK, so the entire cache is invalidated when another process updates the DB,
which is what I feared. In this case I'm looking at too many concurrent
updates for caching to add much value.

I’m no expert on the internals, but the article linked to says that this 
counter isn’t used in WAL mode. And with the WAL the main database file is 
updated less frequently, so the cache might remain valid longer. (Again, I’m 
just hypothesizing.)


WAL mode does the same thing - just uses a different mechanism. If some 
other process modifies the db and then your process opens a new read 
transaction, it discards any cached pages it has in memory.


Dan.

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

Re: [sqlite] Does Reindex Change Stats Tables?

[Richard Hipp]

On 10/24/16, Denis Burke  wrote:
> I am wondering if I run reindex than should I always run analyze
> afterward?  If the reindex command though also updates the stats in
> addition to recreating the actual indexes, then of course i would not need
> it.
>

REINDEX does *not* update the stats.  You must run ANALYZE separately.

May I ask why you are running REINDEX?

-- 
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] Does Reindex Change Stats Tables?

[Denis Burke]

I am wondering if I run reindex than should I always run analyze
afterward?  If the reindex command though also updates the stats in
addition to recreating the actual indexes, then of course i would not need
it.

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

Re: [sqlite] How does the pager know whether its memory cache is still valid?

[Jens Alfke]

> On Oct 24, 2016, at 1:31 AM, Rowan Worth  wrote:
> 
> OK, so the entire cache is invalidated when another process updates the DB,
> which is what I feared. In this case I'm looking at too many concurrent
> updates for caching to add much value.

I’m no expert on the internals, but the article linked to says that this 
counter isn’t used in WAL mode. And with the WAL the main database file is 
updated less frequently, so the cache might remain valid longer. (Again, I’m 
just hypothesizing.)

If you’re doing a lot of updates, you should probably be using WAL mode anyway.

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

Re: [sqlite] Virtual table acting as wrapper of a regular table

[Hick Gunter]

When you prepare() your statement, SQLite translates this into 2 nested loops:

Outer loop:
Rewind table A (retrieve first row)
Extract id from table A row
...
Retrieve next row from table A

Inner loop:
Set table B to A.id
Extract id from B
Check for match
(return result row)
Retrieve next row from table B

When you are using your wrapper, the inner loop gets replaced by:
Call xFilter() -> reset(), bind(), step(), column_value() .> Set table B to 
A.id, Extract id from table B row, check for match
Call xEof()
Call xColumn() -> return result value
Call xNext() -> step(), column_value() -> Retrieve next row from table B

This is about twice as much work.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dimitris Bil
Gesendet: Montag, 24. Oktober 2016 15:10
An: SQLite mailing list 
Betreff: Re: [sqlite] Virtual table acting as wrapper of a regular table

Indeed I am not checking argc. This code just only means to serve as a test in 
order to make comparisons for execution speed. I have stripped it of any other 
functionality.


Regarding prepare, I am only preparing the statement in xConnect (which also 
acts as xCreate). This is only executed once, when connection to the virtual 
table is opened, right?

Regarding the step method, I am executing a step the first time inside xFilter 
in order to advance the cursor in the first result. Then each time the xNext is 
called, I am executing step one more time to continue advancing the cursor. I 
think this is in accordance to what the sqlite page says: "If the virtual table 
contains one or more rows that match the search criteria, then the cursor must 
be left point at the first row. Subsequent calls to xEof must return false 
(zero). If there are no rows match, then the cursor must be left in a state 
that will cause the xEof to return true (non-zero). The SQLite engine will use 
the xColumn and xRowid methods to access that row content. The xNext method 
will be used to advance to the next row."

Also, I am using sqlite3_result_int64 only one time per result tuple inside 
xColumn. In my case, as each result tuple is the same, this indeed could be 
avoided, but I don't think that this is the cause for the overhead.

thanks again,
Dimitris




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Monday, October 24, 2016 10:39 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Virtual table acting as wrapper of a regular table

Your xFilter method is blindly assuming that there is always an argv[0] without 
checking argc first.

You are incurring an extra "prepare" in your xConnect method, an extra "step" 
and "column" in your xFilter/xNext methods and an extra "result" in your 
xColumn function. Doing twice as much work taking twice as long seems quite 
reasonable.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dimitris Bil
Gesendet: Samstag, 22. Oktober 2016 15:31
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Virtual table acting as wrapper of a regular table

Hello,

I am trying to create a simple virtual table that acts as wrapper for a normal 
database table for a specific query. For example, consider that I have tables A 
and B in my database and the query:
select count(A.id) from A CROSS JOIN B where A.id=B.id

Now I am using a virtual table acting as wrapper for table B, so I can execute 
the query select count(A.id) from A CROSS JOIN w where A.id=w.id, where w is 
the virtual table instance, and get the same result. My problem is that the 
second query is about 2 times slower. I would normally expect some overhead, 
but this seems quite slow, so I was wondering if there is something wrong with 
my code.

I am using a prepared statement in connect method for query "select id from B 
where id=?1" and reset/bind/step in filter method and (if there are more 
results) step in next method.

Here's my code (I am just setting id=0 to denote eof) 
http://pastebin.com/ce8b4aLL

Do you think there's something wrong or it's an unavoidable overhead that comes 
with the virtual table usage? Are there any chances to improve performance?

thanks,
Dimitris

___
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 

Re: [sqlite] Best way to wipe out data of a closed database

[Max Vlasov]

On Mon, Oct 24, 2016 at 4:28 PM, Wade, William  wrote:
> It sounds like you've got a way forward on leaks via the malloc() system 
> within the process space.
>
> 1) The region of the C process stack that was reached by some deep call stack.
> 2) Processor registers.
> 3) Process pages that were copied to disk by the OS (this could be a problem 
> even if you otherwise have a good handle on clearing malloc() blocks).
>

Good points, thanks, especially (3) as having more likely scenario to
contain all kind of data and lengths. Probably in case of predictable
memory requirements this one can be handled by providing zero-malloc
implementation with a physically locked region of memory (in case of
Windows - VirualLock API).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to wipe out data of a closed database

[Wade, William]

It sounds like you've got a way forward on leaks via the malloc() system within 
the process space.

Be aware that depending on your system (and the attackers' capabilities), you 
might have to worry about other leaks. For instance, if I did a query that 
involved a FLOAT index, and then closed the sqlite session and cleared the 
zero-malloc block, it wouldn't be at all surprising if some float values 
related to database contents, or to my query, were still in

1) The region of the C process stack that was reached by some deep call stack.
2) Processor registers.
3) Process pages that were copied to disk by the OS (this could be a problem 
even if you otherwise have a good handle on clearing malloc() blocks).

Regards,
Bill

-Original Message-
From: Max Vlasov [mailto:max.vla...@gmail.com]
Subject: [sqlite] Best way to wipe out data of a closed database

Hi,

in an application that implements encryption/decryption with VFS, what is the 
best way to ensure that the memory of the application doesn't contain decrypted 
data after the database is closed. So no side application could retrieve 
sensitive information by reading this process memory. Not only the base as a 
whole but also fragments of database sectors anywhere in the process memory 
space.

One of the trick possible is to add additional zeroing out to the global free 
handler, but this can probably introduce performance penalties.

Is there any other way to do this?

Thanks,

Max


**
This e-mail and any attachments thereto may contain confidential information 
and/or information protected by intellectual property rights for the exclusive 
attention of the intended addressees named above. If you have received this 
transmission in error, please immediately notify the sender by return e-mail 
and delete this message and its attachments. Unauthorized use, copying or 
further full or partial distribution of this e-mail or its contents is 
prohibited.
**
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Virtual table acting as wrapper of a regular table

[Dimitris Bil]

Indeed I am not checking argc. This code just only means to serve as a test in 
order to make comparisons for execution speed. I have stripped it of any other 
functionality.


Regarding prepare, I am only preparing the statement in xConnect (which also 
acts as xCreate). This is only executed once, when connection to the virtual 
table is opened, right?

Regarding the step method, I am executing a step the first time inside xFilter 
in order to advance the cursor in the first result. Then each time the xNext is 
called, I am executing step one more time to continue advancing the cursor. I 
think this is in accordance to what the sqlite page says: "If the virtual table 
contains one or more rows that match the search criteria, then the cursor must 
be left point at the first row. Subsequent calls to xEof must return false 
(zero). If there are no rows match, then the cursor must be left in a state 
that will cause the xEof to return true (non-zero). The SQLite engine will use 
the xColumn and xRowid methods to access that row content. The xNext method 
will be used to advance to the next row."

Also, I am using sqlite3_result_int64 only one time per result tuple inside 
xColumn. In my case, as each result tuple is the same, this indeed could be 
avoided, but I don't think that this is the cause for the overhead.

thanks again,
Dimitris




From: sqlite-users  on behalf of 
Hick Gunter 
Sent: Monday, October 24, 2016 10:39 AM
To: 'SQLite mailing list'
Subject: Re: [sqlite] Virtual table acting as wrapper of a regular table

Your xFilter method is blindly assuming that there is always an argv[0] without 
checking argc first.

You are incurring an extra "prepare" in your xConnect method, an extra "step" 
and "column" in your xFilter/xNext methods and an extra "result" in your 
xColumn function. Doing twice as much work taking twice as long seems quite 
reasonable.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dimitris Bil
Gesendet: Samstag, 22. Oktober 2016 15:31
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Virtual table acting as wrapper of a regular table

Hello,

I am trying to create a simple virtual table that acts as wrapper for a normal 
database table for a specific query. For example, consider that I have tables A 
and B in my database and the query:
select count(A.id) from A CROSS JOIN B where A.id=B.id

Now I am using a virtual table acting as wrapper for table B, so I can execute 
the query select count(A.id) from A CROSS JOIN w where A.id=w.id, where w is 
the virtual table instance, and get the same result. My problem is that the 
second query is about 2 times slower. I would normally expect some overhead, 
but this seems quite slow, so I was wondering if there is something wrong with 
my code.

I am using a prepared statement in connect method for query "select id from B 
where id=?1" and reset/bind/step in filter method and (if there are more 
results) step in next method.

Here's my code (I am just setting id=0 to denote eof) 
http://pastebin.com/ce8b4aLL

Do you think there's something wrong or it's an unavoidable overhead that comes 
with the virtual table usage? Are there any chances to improve performance?

thanks,
Dimitris

___
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] Warning automatic index on

[Richard Hipp]

On 10/24/16, Werner Kleiner  wrote:
>
> In an error log there is a message like:
> SQlite warning (284) automatic index on is_mytable(internalvalue)
>

I do not know why SQLite is warning you about a lack of an index on
internalvalue when it is really missing an index on installid.  I am
unable to reproduce the problem.  If you can send us a script that
will generate the incorrect warning message, we'll happily look into
the problem.

-- 
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] Warning automatic index on

[Werner Kleiner]

The result is:

2015-05-04 19:13:25 850c11866686a7b39d7b163fb60898c11283688e

2016-10-24 14:28 GMT+02:00 Richard Hipp :
> Please post the result of the following query:
>
> SELECT sqlite_source_id();
>
> On 10/24/16, Werner Kleiner  wrote:
>> I have dropped the double index
>> DROP INDEX 'InternalName';
>> Then executed the SQL Select statement which caused the sqlite warning.
>> But same warning, nothing changed.
>>
>> Then I added a new index CREATE INDEX idx_installid ON ...
>>
>>
>> Now the sqlite warning is gone away. :-)
>>
>> The SQL statement has a JOIN like;
>> ..
>> LEFT JOIN
>> is_mytable H ON H.InstallD = I.InstallD
>>
>> perhaps this was the problem?
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Warning automatic index on

[Richard Hipp]

Please post the result of the following query:

SELECT sqlite_source_id();

On 10/24/16, Werner Kleiner  wrote:
> I have dropped the double index
> DROP INDEX 'InternalName';
> Then executed the SQL Select statement which caused the sqlite warning.
> But same warning, nothing changed.
>
> Then I added a new index CREATE INDEX idx_installid ON ...
>
>
> Now the sqlite warning is gone away. :-)
>
> The SQL statement has a JOIN like;
> ..
> LEFT JOIN
> is_mytable H ON H.InstallD = I.InstallD
>
> perhaps this was the problem?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Warning automatic index on

[Werner Kleiner]

I have dropped the double index
DROP INDEX 'InternalName';
Then executed the SQL Select statement which caused the sqlite warning.
But same warning, nothing changed.

Then I added a new index CREATE INDEX idx_installid ON ...


Now the sqlite warning is gone away. :-)

The SQL statement has a JOIN like;
..
LEFT JOIN
is_mytable H ON H.InstallD = I.InstallD

perhaps this was the problem?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to wipe out data of a closed database

[Max Vlasov]

On Mon, Oct 24, 2016 at 1:36 PM, Richard Hipp  wrote:
>
> Memsys5 is also faster than your global system memory allocator
> (before the extra overhead of zeroing, at least).  But on the other
> hand, you have to know the maximum amount of memory SQLite will want
> at the very beginning, and that memory will be used only by SQLite and
> not other parts of your application, so memory utilization is not as
> efficient.
>

Thanks, I understand the risks and benefits, so probably it will be
either zero-malloc allocator or my own allocator replacement.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best way to wipe out data of a closed database

[Max Vlasov]

Simon, thanks
never heard of secure_delete, interesting, but probably no use in case
of VFS Layer that leaves only encrypted data on disk.
As for zero-malloc option, it looks promising.

On Mon, Oct 24, 2016 at 1:34 PM, Simon Slavin  wrote:
>
> On 24 Oct 2016, at 9:58am, Max Vlasov  wrote:
>
>> in an application that implements encryption/decryption with VFS, what
>> is the best way to ensure that the memory of the application doesn't
>> contain decrypted data after the database is closed.
>
> We can't answer about memory that your own application handles, of course.
>
> To ensure zeroing out of memory I suggest you use the zero-malloc option as 
> provided by SQLite's memory allocator.  For more details on them see sections 
> 3.1.4 and 3.1.5 of
>
> 
>
> It's also worth noting here that SQLite has the following PRAGMA:
>
> PRAGMA schema.secure_delete = boolean
>
> which zeros space in files.  However I don't remember this working by zeroing 
> out the memory copy of the file then writing that block to disk.
>
> 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] Warning automatic index on

[Richard Hipp]

On 10/24/16, Simon Slavin  wrote:
>
> I think it's the "DESC".  Since SQLite understands how to search an index
> backwards when it needs to, try dropping the DESC on that index and see if
> the problem goes away.

He already has another index without the DESC :-\

I think those indexes are redundant.  I cannot, off hand, think of a
set of queries that would benefit from having them both.  SQLite is
perfectly capable using a DESC index for an ASC scan and vice verse.

I don't think the MySQL-backtics are a factor either.  Though they
should be fixed, SQLite at least is able to deal with them.
-- 
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] Virtual table acting as wrapper of a regular table

[Hick Gunter]

Your xFilter method is blindly assuming that there is always an argv[0] without 
checking argc first.

You are incurring an extra "prepare" in your xConnect method, an extra "step" 
and "column" in your xFilter/xNext methods and an extra "result" in your 
xColumn function. Doing twice as much work taking twice as long seems quite 
reasonable.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Dimitris Bil
Gesendet: Samstag, 22. Oktober 2016 15:31
An: sqlite-users@mailinglists.sqlite.org
Betreff: [sqlite] Virtual table acting as wrapper of a regular table

Hello,

I am trying to create a simple virtual table that acts as wrapper for a normal 
database table for a specific query. For example, consider that I have tables A 
and B in my database and the query:
select count(A.id) from A CROSS JOIN B where A.id=B.id

Now I am using a virtual table acting as wrapper for table B, so I can execute 
the query select count(A.id) from A CROSS JOIN w where A.id=w.id, where w is 
the virtual table instance, and get the same result. My problem is that the 
second query is about 2 times slower. I would normally expect some overhead, 
but this seems quite slow, so I was wondering if there is something wrong with 
my code.

I am using a prepared statement in connect method for query "select id from B 
where id=?1" and reset/bind/step in filter method and (if there are more 
results) step in next method.

Here's my code (I am just setting id=0 to denote eof) 
http://pastebin.com/ce8b4aLL

Do you think there's something wrong or it's an unavoidable overhead that comes 
with the virtual table usage? Are there any chances to improve performance?

thanks,
Dimitris

___
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] Import 5000 xml files in a sqlite database file

[Preguntón Cojonero Cabrón]

Scripting powershell? C#?

El 23/10/2016 18:04, "Sylvain Pointeau" 
escribió:

> hello,
>
> I am not sure if Oxygen or another XML specialized software could do it,
> however it would be easy done using C or C++ or Java. Advantage is that it
> is then easy to automatize in a batch mode. I can propose you my services
> if you are interested.
>
> Best regards,
> Sylvain
>
> Le samedi 22 octobre 2016,  a écrit :
>
> > Hi,
> >
> > I have more than 5000 xml files. All files have the same xml-structure.
> >
> > Each file has different values (timestamps, numbers and strings). I would
> > like to put all these values in a sqlite database tabke, all in one
> table.
> > => Import the data values into a sqlite database table.
> >
> > Can you please tell me a software program, that can do this quickly?
> >
> > Thank you for your answers.
> >
> > Best regards
> >
> > Bob
> > ___
> > 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] Import 5000 xml files in a sqlite database file

[Kevin Youren]

Bob,

my name is Kevin Youren, and I did this task about 4 years ago in
Python 3, by parsing XML files and creating CSV files. The CSV files
were used to load Sqlite tables, MS Xcel spreadsheets and IBM mainframe
DB2 tables. The XML data was mildly complex, large, and error prone.

If you have a sample, say 2 or 3 of the normal files, I could make some
suggestions.

Please note that unless the data is ultra simple, XML is generally
better translated as several tables.

For example, my application stored Control-M scheduler information.

Two tables for the schedule group or table name.

The third table for the jobs in each schedule group/table.

The fourth table for the conditions for the jobs for the schedule
group/table.

Each table had columns for the tags or attributes.

regs,

Kev






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

Re: [sqlite] Warning automatic index on

[Simon Slavin]

On 24 Oct 2016, at 10:11am, Rowan Worth  wrote:

> However I see you already have an index 'InternalName' which covers the
> 'internalvalue' column, so not sure why that isn't being used. Maybe it's
> confused by the DESC or the MySQL back-ticks?

I think it's the "DESC".  Since SQLite understands how to search an index 
backwards when it needs to, try dropping the DESC on that index and see if the 
problem goes away.

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

Re: [sqlite] Best way to wipe out data of a closed database

[Richard Hipp]

On 10/24/16, Max Vlasov  wrote:
>
> One of the trick possible is to add additional zeroing out to the
> global free handler, but this can probably introduce performance
> penalties.
>
> Is there any other way to do this?

If you set up to use memsys5 at compile-time
(https://www.sqlite.org/malloc.html#zero_malloc_memory_allocator) then
after all use of SQLite has ended, you are left with a single big
chunk of memory that can be zeroed.

Memsys5 is also faster than your global system memory allocator
(before the extra overhead of zeroing, at least).  But on the other
hand, you have to know the maximum amount of memory SQLite will want
at the very beginning, and that memory will be used only by SQLite and
not other parts of your application, so memory utilization is not as
efficient.

-- 
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] Best way to wipe out data of a closed database

[Simon Slavin]

On 24 Oct 2016, at 9:58am, Max Vlasov  wrote:

> in an application that implements encryption/decryption with VFS, what
> is the best way to ensure that the memory of the application doesn't
> contain decrypted data after the database is closed.

We can't answer about memory that your own application handles, of course.

To ensure zeroing out of memory I suggest you use the zero-malloc option as 
provided by SQLite's memory allocator.  For more details on them see sections 
3.1.4 and 3.1.5 of



It's also worth noting here that SQLite has the following PRAGMA:

PRAGMA schema.secure_delete = boolean

which zeros space in files.  However I don't remember this working by zeroing 
out the memory copy of the file then writing that block to disk.

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

Re: [sqlite] Development environment reccomendation

[Simon Slavin]

On 24 Oct 2016, at 6:26am, Philip Rhoades  wrote:

> What development environment would people suggest for building the sqlite app?

If I understand correctly, 'mmssms.db' is itself a SQLite database file.  So 
your choice comes down to whatever programming language/environment you're 
familiar with, as long as it supports the SQLite API or has a library which 
does.

Ruby is fine, since you mention that:



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

Re: [sqlite] Warning automatic index on

[Rowan Worth]

On 24 October 2016 at 16:59, Werner Kleiner  wrote:

> Hello,
>
> In an error log there is a message like:
> SQlite warning (284) automatic index on is_mytable(internalvalue)
>
> What does this mean?
>

It means that SQLite's query optimiser has decided the most efficient way
to get the results you asked for is to:

(a) create an index on the 'internalvalue' column of 'is_mytable'
(b) use the index from (a) to help execute the query
(c) drop the index created in (a)

However I see you already have an index 'InternalName' which covers the
'internalvalue' column, so not sure why that isn't being used. Maybe it's
confused by the DESC or the MySQL back-ticks?


What query are you running which produces the warning?

What version of SQLite are you using?
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Warning automatic index on

[Werner Kleiner]

Hello,

In an error log there is a message like:
SQlite warning (284) automatic index on is_mytable(internalvalue)

What does this mean?
Can sqlite not use the index correct?
How can we check or optimize the index?
What do I have to do to cancel the message?

Here is the Table DDL:

-- Table: is_mytable
CREATE TABLE "is_mytable" (
"mytableid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL  ,
"compcid" INT  NULL DEFAULT 0 ,
"installid" INT  NOT NULL DEFAULT 276 ,
"internalvalue" VARCHAR(250)  NOT NULL  COLLATE NOCASE,
"namekey" VARCHAR(250)  NOT NULL DEFAULT 'Document' COLLATE NOCASE,
"textid" INT  NOT NULL  ,"defaultvalue" INT  NOT NULL DEFAULT 0 );

-- Index: InternalName
CREATE INDEX 'InternalName' ON 'is_mytable' (`internalvalue` DESC);

-- Index: OpenUI
CREATE INDEX 'OpenUI' ON 'is_mytable' (`namekey` DESC);

-- Index: idx_mytable_compcid
CREATE INDEX 'idx_mytable_compcid' ON 'is_mytable' (`compcid` DESC);

-- Index: idx_mytable_internalvalue
CREATE INDEX idx_mytable_internalvalue ON is_mytable (internalvalue);

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

[sqlite] Best way to wipe out data of a closed database

[Max Vlasov]

Hi,

in an application that implements encryption/decryption with VFS, what
is the best way to ensure that the memory of the application doesn't
contain decrypted data after the database is closed. So no side
application could retrieve sensitive information by reading this
process memory. Not only the base as a whole but also fragments of
database sectors anywhere in the process memory space.

One of the trick possible is to add additional zeroing out to the
global free handler, but this can probably introduce performance
penalties.

Is there any other way to do this?

Thanks,

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

Re: [sqlite] How does the pager know whether its memory cache is still valid?

[Rowan Worth]

On 24 October 2016 at 15:44, Clemens Ladisch  wrote:

> Rowan Worth wrote:
> > How does sqlite determine that the cached page is out of date?
>
> http://www.sqlite.org/fileformat2.html#file_change_counter
>
> > Ultimately the question I'm trying to answer is whether increasing the
> size
> > of the pager cache will reduce the amount of I/O required by a single
> > process in the case where a database is modified concurrently.
>
> The only way to determine how big the effect is in your specific
> situation is to measure it yourself.
>

OK, so the entire cache is invalidated when another process updates the DB,
which is what I feared. In this case I'm looking at too many concurrent
updates for caching to add much value.

Thanks! I had been over the file format -- clearly I need to read slower :)
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does the pager know whether its memory cache is still valid?

[Clemens Ladisch]

Rowan Worth wrote:
> How does sqlite determine that the cached page is out of date?

http://www.sqlite.org/fileformat2.html#file_change_counter

> Ultimately the question I'm trying to answer is whether increasing the size
> of the pager cache will reduce the amount of I/O required by a single
> process in the case where a database is modified concurrently.

The only way to determine how big the effect is in your specific
situation is to measure it yourself.


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

[sqlite] How does the pager know whether its memory cache is still valid?

[Rowan Worth]

Hi guys,

I haven't been able to figure this one out from the docs, nor have I
stumbled onto the right section of the source.

Say you have two separate processes accessing an sqlite DB. P1 starts a
transaction, reads page #5, ends transaction. P2 starts a transaction,
modifies page #5, ends transaction. P1 starts a new transaction and reads
page #5 again.

At this point P1 still has page #5 cached in memory, but clearly it needs
to read from disk to pick up the changes. How does sqlite determine that
the cached page is out of date?


Ultimately the question I'm trying to answer is whether increasing the size
of the pager cache will reduce the amount of I/O required by a single
process in the case where a database is modified concurrently.

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