Re: [sqlite] How to detect existing TRANSACTION before performingSAVEPOINT?

[Igor Tandetnik]

Afriza N. Arief  wrote:
> What is the correct way of detecting existing transaction?

sqlite3_get_autocommit
-- 
Igor Tandetnik

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

[sqlite] How to detect existing TRANSACTION before performing SAVEPOINT?

[Afriza N. Arief]

Hello,

Currently I am relying on the return code of sqlite3_exec(db,"BEGIN
IMMEDIATE",0,0,0) == SQLITE_ERROR as an indication that there is already a
transaction in progress and then in such cases, I will then call
sqlite3_exec(db,"SAVEPOINT mySavePoint",0,0,0) as a mean of nested
transaction.

Is this good enough? What is the correct way of detecting existing
transaction?


void func(sqlite3* db) {
  int rc, active = 0, savepoint=0;
  rc=sqlite3_exec(db,"BEGIN IMMEDIATE",0,0,0);
  if (rc==SQLITE_OK) {
active=1;
  }
  else if (rc==SQLITE_ERROR) { /* is this a good test condition? */
rc=sqlite3_exec(db,"SAVEPOINT mySavePoint",0,0,0);
if(rc==SQLITE_OK) {
  savepoint = active = 1;
}
  }

  /* do something */

  if (active) {
if (savepoint) {
  rc=sqlite3_exec(db,"RELEASE mySavePoint",0,0,0);
}
else {
  rc=sqlite3_exec(db,"END",0,0,0);
}
  }
}


Similar functions are called outside and inside TRANSACTIONs as well as
SAVEPOINTs.

Regards,

Afriza N. Arief
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Re ad Lock not released..

[mmudi]

Thanks for the reply. We debugged the sqlite structure when we encounter the
issue, and we do not see any open transactions (no initialized vdbe) and
still see that the shared_lock is set by the pager. Is there anything
obvious that we are missing. I can paste the gdb results for the various
sqlite structure if that helps. 

Thanks in advance





Jay A. Kreibich-2 wrote:
> 
> On Tue, Mar 01, 2011 at 12:20:32PM -0800, mmudi scratched on the wall:
>> 
>> We are using sqlite version 3.3.17 in a production environment, and are
>> facing an issue where a particular process is not releasing the read lock
>> on
>> the sql file. This locks up the database when any write operations are
>> attempted by other processes. The process under question is not multi
>> threaded and stack trace reveals that the process is not stuck in an
>> SQLite
>> library call but is processing other requests. 
>> 
>> A code review of the DB access API has not revealed any resources or
>> calls
>> that have not been closed/freed properly.
> 
>   This can be verified by attempting to close the database connection.
>   If there are still unresolved statements, an error will be returned.
> 
>   Additionally, you must actually call _reset() or _finalized() on a
>   statement, even if it is run until _step() returns SQLITE_DONE.  In
>   some cases, in some versions of SQLite, the locks are not released
>   until the statement is actually reset/finalized.
> 
>> Questions
>> --
>> From my debugging efforts, the issue appears to be that the process
>> entered
>> the sqlite library at some point in its operation, acquired a read lock,
>> and
>> returned from the library, but failed to release the read lock. Under
>> what
>> circumstances can this occur? 
> 
>   Many.  But if you're correctly resetting all your statements, the
>   most likely cause is an SQL "BEGIN" was issued without a
>   corresponding "COMMIT" or "ROLLBACK."
> 
>-j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Read-Lock-not-released..-tp31043990p31168820.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Optimizing list retrieval with a FTS3 table

[Scott Hess]

On Wed, Mar 16, 2011 at 12:15 PM, Travis Orr  wrote:
> Can someone enlighten me about this. To me a lot of the details appear
> to be hidden since my main SongTable is a FTS3 virtual table.

You don't provide your schema, but based on your queries, I'll make
unwarranted assumptions :-).

In fts3, there is a rowid column (standard SQLite meaning), a docid
column which is an alias of rowid, and all the columns you define are
TEXT columns.  If you say:

CREATE VIRTUAL TABLE MyTable USING FTS3(
  songid INTEGER PRIMARY KEY AUTO_INCREMENT MAGIC KEYWORDS,
  title VARCHAR(23),
  recorded DATETIME
);

All three of those columns are TEXT..  Based on your queries, I'm
betting that you're assuming that the various typing keywords for a
CREATE TABLE statement apply, but they don't.  If you want to know
why, you can scan the archives or read the source code, but suffice to
say that this is the truth at this time.

Anyhow, the gist of it is that the FTS3 table has a full-text index on
the TEXT of the columns, and that any other queries will be full table
scans, as if there were no optimizations at all.  So complicated
queries with ORDER BY, LIMIT, and OFFSET can absolutely destroy
performance if your result sets are all all big (or can be big, watch
for the query of death!).  If you will not be using MATCH, then there
is no gain at all from FTS3, and you should consider just using a
regular table.

As I understand your problem, the solution I'd probably use would be
to create a new temporary table to hold the data while scanning it.
So something like:

CREATE TEMPORARY TABLE MyResults AS
  SELECT docid, title, artist FROM songtable WHERE ... ORDER BY ...;

I _think_ the resulting table will effectively capture the ORDER BY
results, so you can then scan it using OFFSET and LIMIT (or rowid)
efficiently.  If this is too big, you could experiment with capturing
only the docid values in order, and then joining MyResults back
against songtable to get the original values.  That won't be
particularly efficient with OFFSET and LIMIT, but it should be able to
join directly with songtable.docid, so it shouldn't be particularly
inefficient, either.

Of course, you could also just read the entire docid set into memory
and manage it that way.  It's a little cumbersome because then you
have to keep re-binding the query to walk through things, but it
probably won't perform any worse.

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

Re: [sqlite] How to delete all indexes

[Simon Slavin]

On 16 Mar 2011, at 7:19pm, Jeff Archer wrote:

> I have found that I can use this select to get the names of all existing
> indexes:
> 
> select name from sqlite_master where type = 'index' and sql is not null;

You can also be specific about which table you want by filtering on the table 
name.

> Then I can iterate the result and drop each index.
> 
> 
> 
> This leaves me with 2 questions.
> 
> 1.   How stable are the column names and table contexts of the
> sqlite_master table?

They're dependable in this context.

> 2.   Is there a better way to accomplish this without resorting to
> explicit query of the sqlite_master table?

No I think you found the best way to do it with SQLite.

You should almost never be creating indexes on the fly.  Bear in mind that if 
SQLite finds a search that would be better with an index, it creates it itself 
and it is far better at working out the best index than you are.  The only 
disadvantage is that it will recreate the index each time you do that SELECT.

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

Re: [sqlite] Optimizing list retrieval with a FTS3 table

[Jim Wilcoxson]

On Wed, Mar 16, 2011 at 4:12 PM, Travis Orr  wrote:
> Jim, just did a test with your recommendation, however that ended up
> using too much memory, since it is selecting the entire list in on go
> and keeping the memory for a significant amount of time, for the
> embedded system it is running on. Other tasks started having problems.
>
> Travis

I think it should only use cachesize*pagesize memory, so you should be
able to control memory usage with pragma cache_size.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com


>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Wilcoxson
> Sent: March-16-11 12:51 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Optimizing list retrieval with a FTS3 table
>
> On Wed, Mar 16, 2011 at 3:15 PM, Travis Orr  wrote:
>> I am currently working on a project that requires retrieving a list of
>> all the rows from a FTS3 table. The ordering of the results varies by
>> search criteria. Since this is for an embedded project the list
> results
>> are passed in chunks to another module to give the appearance of
> faster
>> operations.
>
> Somewhere you have some state information so that you know what offset
> to use.  Store the SQLite cursor with that state information, and use
> it to fetch the next 2000 rows on each call, ie, only do the query
> once.
>
> Jim
> --
> HashBackup: easy onsite and offsite Unix backup
> http://www.hashbackup.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing list retrieval with a FTS3 table

[Travis Orr]

Jim, just did a test with your recommendation, however that ended up
using too much memory, since it is selecting the entire list in on go
and keeping the memory for a significant amount of time, for the
embedded system it is running on. Other tasks started having problems.

Travis

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Wilcoxson
Sent: March-16-11 12:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Optimizing list retrieval with a FTS3 table

On Wed, Mar 16, 2011 at 3:15 PM, Travis Orr  wrote:
> I am currently working on a project that requires retrieving a list of
> all the rows from a FTS3 table. The ordering of the results varies by
> search criteria. Since this is for an embedded project the list
results
> are passed in chunks to another module to give the appearance of
faster
> operations.

Somewhere you have some state information so that you know what offset
to use.  Store the SQLite cursor with that state information, and use
it to fetch the next 2000 rows on each call, ie, only do the query
once.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing list retrieval with a FTS3 table

[Travis Orr]

Jim,

That would require maintaining the current connection and using a
prepared statement, correct? 

Thanks for recommending that I had considered that earlier but couldn't
afford to have the db locked from writes for the length of time a query
could take. At that point in time I was still on Sqlite 3.6.xx without
shared-cache enabled. Since then I have pulled Sqlite 3.7.5 and am using
the WAL making this feasible.

Thanks,
Travis

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Wilcoxson
Sent: March-16-11 12:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Optimizing list retrieval with a FTS3 table

On Wed, Mar 16, 2011 at 3:15 PM, Travis Orr  wrote:
> I am currently working on a project that requires retrieving a list of

> all the rows from a FTS3 table. The ordering of the results varies by 
> search criteria. Since this is for an embedded project the list 
> results are passed in chunks to another module to give the appearance 
> of faster operations.

Somewhere you have some state information so that you know what offset
to use.  Store the SQLite cursor with that state information, and use it
to fetch the next 2000 rows on each call, ie, only do the query once.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizing list retrieval with a FTS3 table

[Jim Wilcoxson]

On Wed, Mar 16, 2011 at 3:15 PM, Travis Orr  wrote:
> I am currently working on a project that requires retrieving a list of
> all the rows from a FTS3 table. The ordering of the results varies by
> search criteria. Since this is for an embedded project the list results
> are passed in chunks to another module to give the appearance of faster
> operations.

Somewhere you have some state information so that you know what offset
to use.  Store the SQLite cursor with that state information, and use
it to fetch the next 2000 rows on each call, ie, only do the query
once.

Jim
--
HashBackup: easy onsite and offsite Unix backup
http://www.hashbackup.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to delete all indexes

[Jeff Archer]

Hello all and thank you in advance for your help.

 

I have just realized that a buggy code has been secretly creating indexes.
Thus I have need to make a generic function that can delete all indexes in a
database.

 

I have found that I can use this select to get the names of all existing
indexes:

select name from sqlite_master where type = 'index' and sql is not null;

 

Then I can iterate the result and drop each index.

 

This leaves me with 2 questions.

1.   How stable are the column names and table contexts of the
sqlite_master table?

2.   Is there a better way to accomplish this without resorting to
explicit query of the sqlite_master table?

 

Jeff Archer

Nanotronics Imaging

jsarc...@nanotronicsimaging.com

<330>819-4615

 

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

[sqlite] Optimizing list retrieval with a FTS3 table

[Travis Orr]

I am currently working on a project that requires retrieving a list of
all the rows from a FTS3 table. The ordering of the results varies by
search criteria. Since this is for an embedded project the list results
are passed in chunks to another module to give the appearance of faster
operations.

 

Currently the selection of rows is done as follows:

SELECT songid, title, artist FROM SongTable ORDER BY title COLLATE
NOCASE DESC, artist COLLATE NOCASE DESC, songid DESC LIMIT 2000 OFFSET
4000;

With the offset increasing for each subsequent page of data. The big
problem I have with this is that when the offset gets large there is a
huge inefficiency because all rows are selected and then stepped through
until the offset is reached. My current test database has 34,000 rows.

 

I am considering changing to queries of the following style but am
unsure of the performance gains since I will still not be using the
MATCH operator on the data.

SELECT songid, title, artist FROM songtable WHERE (title = "some title
AND artist = "some artist" AND songid > 4194419) OR (title = "some
title" AND artist > "some artist") OR title > "some title" ORDER BY
title, artist, songid LIMIT 2000;

When using the "explain query plan" operator I see that both of the
above cases only use "VIRTUAL TABLE INDEX 0"

 

The advantage of the second case is that is only selects the necessary
rows, however it does use need to perform comparisons and I am unsure
what the performance hit of the comparison operators on a FTS3 table is.

 

sqlite> explain query plan SELECT songid, title, artist FROM songtable
WHERE (title = "some title AND artist = "some artist" AND songid >
4194419) OR (title = "some title" AND artist > "some artist") OR title >
"some title" ORDER BY title, artist, songid LIMIT 2000;

0|0|0|SCAN TABLE songtable VIRTUAL TABLE INDEX 0: (~0 rows)

0|0|0|SCAN TABLE songtable VIRTUAL TABLE INDEX 0: (~0 rows)

0|0|0|SCAN TABLE songtable VIRTUAL TABLE INDEX 0: (~0 rows)

0|0|0|USE TEMP B-TREE FOR ORDER BY

sqlite> explain query plan SELECT songid, title, artist FROM SongTable
ORDER BY title COLLATE NOCASE DESC, artist COLLATE NOCASE DESC, songid
DESC LIMIT 2000 OFFSET 4000;

0|0|0|SCAN TABLE songtable VIRTUAL TABLE INDEX 0: (~0 rows)

0|0|0|USE TEMP B-TREE FOR ORDER BY

 

Can someone enlighten me about this. To me a lot of the details appear
to be hidden since my main SongTable is a FTS3 virtual table.

 

I hope this makes sense.

 

Thanks,

Travis

 

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

Re: [sqlite] Comparing Value of a Table and creating a Delta Report

[Igor Tandetnik]

On 3/16/2011 2:21 PM, Ralf Jantschek wrote:
> ok I give it a try ( I just recognized, I made a mistake with the status,
> this is the correct version).
>
> Table 1  t1.id,   t1.name,t1.status:
>   1   "Program 1" 1
>   2   "Program 1" 0
>
> Table 2   t1.id,  t3.id:
>   1   1
>   1   2
>   2   3
>   2   4
>
> Table 3   t3.id,  t3.key, t3.value
>   1   "Key 1" "Value 1"
>   2   "Key 2" "Value 2"
>   3   "Key 1" "Value 3"
>   4   "Key 2" "Value 2"
>
> What do I have to do, to find out that "Key 1" got a new value?

select * from
   t1 t1s0 join t2s0 using ("t1.id") join t3s0 using ("t3.id"),
   t1 t1s1 join t2s1 using ("t1.id") join t3s1 using ("t3.id")
where
   t1s0."t1.name" =  t1s1."t1.name" and t1s0."t1.status" = 0 and 
t1s0."t1.status" = 1 and
   t3s0."t3.key" = t3s1."t3.key" and t3s0."t3.value" != t3s1."t3.value";

-- 
Igor Tandetnik

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

Re: [sqlite] Comparing Value of a Table and creating a Delta Report

[Ralf Jantschek]

Hi,

ok I give it a try ( I just recognized, I made a mistake with the status,
this is the correct version).

Table 1t1.id,   t1.name,t1.status:
1   "Program 1" 1
2   "Program 1" 0

Table 2 t1.id,  t3.id:
1   1
1   2
2   3
2   4

Table 3 t3.id,  t3.key, t3.value
1   "Key 1" "Value 1"
2   "Key 2" "Value 2"
3   "Key 1" "Value 3"
4   "Key 2" "Value 2"

What do I have to do, to find out that "Key 1" got a new value?

I hope this helps to clarify.

Thanks
Ralf

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