[sqlite] Why skip invoking busy handler while pBt->inTransaction!=TRANS_NONE

[Dan Kennedy]

On 02/24/2016 08:32 PM, sanhua.zh wrote:
> In the source code of SQLite, btree.c, sqlite3BtreeBeginTrans function,
> The code
>
>
>   do {
>/* Call lockBtree() until either pBt-pPage1 is populated or
>** lockBtree() returns something other than SQLITE_OK. lockBtree()
>** may return SQLITE_OK but leave pBt-pPage1 set to 0 if after
>** reading page 1 it discovers that the page-size of the database
>** file is not pBt-pageSize. In this case lockBtree() will update
>** pBt-pageSize to the page-size of the file on disk.
>*/
>while( pBt-pPage1==0  SQLITE_OK==(rc = lockBtree(pBt)) );
>
>
>if( rc==SQLITE_OK  wrflag ){
> if( (pBt-btsFlags  BTS_READ_ONLY)!=0 ){
>  rc = SQLITE_READONLY;
> }else{
>  rc = sqlite3PagerBegin(pBt-pPager,wrflag1,sqlite3TempInMemory(p-db));
>  if( rc==SQLITE_OK ){
>   rc = newDatabase(pBt);
>  }
> }
>}
>
>if( rc!=SQLITE_OK ){
> unlockBtreeIfUnused(pBt);
>}
>   }while( (rc0xFF)==SQLITE_BUSY  pBt-inTransaction==TRANS_NONE
>   btreeInvokeBusyHandler(pBt) );
>
>
>
>
> You can see pBt-inTransaction==TRANS_NONE is one of the condition that invoke 
> busy handler.
> There is a simple way to simulate a situation that does not invoke busy 
> handler:
> 1. begin a transaction without ?IMMEDIATE? and ?EXCLUSIVE?
> 2. run a read operation, like ?SELECT?. This will let pBt-inTransaction be 
> TRANS_READ
> 3. run a write operation, which will invoke sqlite3BtreeBeginTrans again. And 
> if it becomes SQLITE_BUSY, then btreeInvokeBusyHandler will be skiped and no 
> retry will happen.
>
>
> So it?s the question I confused. Why SQLite skip invoking busy handler while 
> it's in TRANS (either read or write) ?

Assuming you're not using wal-mode, it's because the two processes will 
be waiting for each other.

The transaction opened in step 1 cannot be committed until the read-only 
transaction started in step 2 has ended. So if you did invoke the 
busy-handler in step 3, the two processes would each be waiting for the 
other to give up. Not much point to that.

In wal-mode it's a little different. The transaction opened in step 1 
could be committed, but attempting to open the write-transaction in step 
3 following that would fail with SQLITE_BUSY_SNAPSHOT.


Dan.



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

[sqlite] SQLite-jdbc Bug - database table is locked

[Dan Kennedy]

On 02/24/2016 09:49 AM, Pankaj Bisen wrote:
> Hello Everyone,
>
> I have two unrelated tables table_A and table_B in my SQLite DB. Trying to
> drop table_B while a resultset is open on table_A throws
> "java.sql.SQLException: database table is locked".

That's an SQLITE_LOCKED error. Here:

https://www.sqlite.org/mark/rescode.html?DROP%20TABLE*reader#locked

Dan.



> Following simple code will illustrate the Bug clearly.
> public class Class1 {
>  public static void main(String[] args) throws Exception {
>  Class.forName("org.sqlite.JDBC");
>  Connection c = DriverManager.getConnection("jdbc:sqlite:" +
> "D:\\test.db");
>  Statement stmt1 = c.createStatement();
>  stmt1.executeUpdate("create table table_A (col1 text)");
>  stmt1.executeUpdate("insert into table_A values ('FIRST')");
>  stmt1.executeUpdate("create table table_B (col1 text)");
>  PreparedStatement ps = c.prepareStatement("select * from table_A");
>  ResultSet rs = ps.executeQuery();
>  //While the resultset is open on table_A try to
>  //drop table_B. It gives error that database table is locked
>  //Which makes no sense because there is no relation between
>  //table_A and table_B
>  stmt1.executeUpdate("drop table if exists table_B");
>  rs.close();
>  stmt1.close();
>  c.close();
>  }
> }
> *Some other details*
>
> 1. My Java version is 1.8.0_72.
> 2. I have tried this with sqlite-jdbc-3.8.11.2.jar as well as
> sqlite-jdbc-3.7.2.jar. But the result is same.
> 3. Following are some of the other things that I tried which made no
> difference.
>1. Setting auto-commit on or off
>2. Searched bug-list and googled but all hits are for "database is
>locked" but nothing for "database *table* is locked"
>3. Using try-catch-finally to properly close all resources in case of
>exception.
>4. Closing the connection and reopening it before "Preparedstatement
>ps ...".
>5. Tried to use another connection to drop the table_B. This
>expectedly gave "database is locked" error.
> 4. My actual scenario is that I have meta-data in one table based on
> which I need to drop other tables. So I need to drop other tables while
> processing resultset from meta-data table.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why skip invoking busy handler while pBt->inTransaction!=TRANS_NONE

[sanhua.zh]

In the source code of SQLite, btree.c, sqlite3BtreeBeginTrans function,
The code


 do {
  /* Call lockBtree() until either pBt-pPage1 is populated or
  ** lockBtree() returns something other than SQLITE_OK. lockBtree()
  ** may return SQLITE_OK but leave pBt-pPage1 set to 0 if after
  ** reading page 1 it discovers that the page-size of the database
  ** file is not pBt-pageSize. In this case lockBtree() will update
  ** pBt-pageSize to the page-size of the file on disk.
  */
  while( pBt-pPage1==0  SQLITE_OK==(rc = lockBtree(pBt)) );


  if( rc==SQLITE_OK  wrflag ){
   if( (pBt-btsFlags  BTS_READ_ONLY)!=0 ){
rc = SQLITE_READONLY;
   }else{
rc = sqlite3PagerBegin(pBt-pPager,wrflag1,sqlite3TempInMemory(p-db));
if( rc==SQLITE_OK ){
 rc = newDatabase(pBt);
}
   }
  }

  if( rc!=SQLITE_OK ){
   unlockBtreeIfUnused(pBt);
  }
 }while( (rc0xFF)==SQLITE_BUSY  pBt-inTransaction==TRANS_NONE 
 btreeInvokeBusyHandler(pBt) );




You can see pBt-inTransaction==TRANS_NONE is one of the condition that invoke 
busy handler.
There is a simple way to simulate a situation that does not invoke busy handler:
1. begin a transaction without ?IMMEDIATE? and ?EXCLUSIVE?
2. run a read operation, like ?SELECT?. This will let pBt-inTransaction be 
TRANS_READ
3. run a write operation, which will invoke sqlite3BtreeBeginTrans again. And 
if it becomes SQLITE_BUSY, then btreeInvokeBusyHandler will be skiped and no 
retry will happen.


So it?s the question I confused. Why SQLite skip invoking busy handler while 
it's in TRANS (either read or write) ?

[sqlite] Why is a separate journal file needed ?

[Paul Sanderson]

You would need some sort of allocation table for the journal (wal or
rollback) and on a rollback commit or a wal checkpoint the allocation
would need to be cleared (or maybe some "in use" bit cleared) to show
that the pages were now free to be re-used.

The additional data stored with rollback journals (page number before
and checksum after) and wals (wal frame before) would also need to be
implemented else where and thereofre add extra logic.

It sounds doable but more complex.
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 24 February 2016 at 15:46, Igor Tandetnik  wrote:
> On 2/24/2016 10:08 AM, Stephen Chrzanowski wrote:
>>
>> IMO, all that, plus the fact that you have an easy roll back mechanism.
>> Anything that needs to be put in the database is external to the pristine
>> database.  Lock the database with a transaction, fill up the journal, the
>> power goes out, your pristine database isn't touched.
>
>
> Rollback journal works the other way round. New data is written to database
> file; previous content of overwritten pages is copied to journal file.
> Committing a transaction is fast - simply delete the journal. Rolling back
> (e.g. after power failure) means copying original pages from the journal
> back to the database file.
>
> In the world you describe - how would you implement committing a
> transaction? At some point, something must write to the database file; at
> that point, the file is no longer "pristine". What happens if power goes out
> in the middle of this?
>
> Note also that a typical application commits much more often than it rolls
> back, so the system should be designed to make the former fast; the latter
> can be slow.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Crash when running complex FT5 queries

[Gergely Lukacsy (glukacsy)]

Hi,



We run into a reproducible crash on FTS5 search when searching something 
complex such as:



"(word1* word2* word3*) OR word1* OR word2* OR word3*"



It might be that we are doing something wrong, but any insight is appreciated. 
The actual crashing call is a sqlite3_step/1 call:



while (SQLITE_ROW == stepResult)

  {

stepResult = sqlite3_step(statement); // BOOM



if (stepResult == SQLITE_ROW)

{

   ...

}

}



Regards,

Gergely



  ()Unknown

 SparkWindows.exe!sqlite3_column_name16?()C

 SparkWindows.exe!sqlite3_column_name16?()C

 SparkWindows.exe!sqlite3_column_name16?()C

 SparkWindows.exe!sqlite3_column_name16?()C

 SparkWindows.exe!sqlite3_create_function16?() C

 SparkWindows.exe!sqlite3CodecGetKey?()C

 SparkWindows.exe!sqlite3CodecGetKey?()C

 SparkWindows.exe!sqlite3_step?()   C

 SparkWindows.exe!sqlite3_column_name16?()C

 SparkWindows.exe!sqlite3_column_name16?()C

 SparkWindows.exe!sqlite3_result_subtype?()   C

 SparkWindows.exe!sqlite3CodecGetKey?()C

 SparkWindows.exe!sqlite3CodecGetKey?()C

 SparkWindows.exe!sqlite3_step?()   C

>  
> SparkWindows.exe!DatabaseWrapper::DBWrapper::_fetchFromDatabase(const 
> std::basic_string & 
> tableName, const 
> std::basic_string & 
> select, const 
> std::vector  >,DatabaseWrapper::DBType,enum 
> DatabaseWrapper::DBOperatorType>,std::allocator  >,DatabaseWrapper::DBType,enum DatabaseWrapper::DBOperatorType> > > & 
> arguments, unsigned __int64 limit, const 
> std::basic_string & 
> orderByColumnName, const bool orderByDescending) Line 504 C++

  
SparkWindows.exe!DatabaseWrapper::DBWrapper::_fetchFromDatabase(const 
std::basic_string & 
tableName, const 
std::vector,std::allocator > > & columns, const 
std::vector,DatabaseWrapper::DBType,enum 
DatabaseWrapper::DBOperatorType>,std::allocator,DatabaseWrapper::DBType,enum DatabaseWrapper::DBOperatorType> > > & 
arguments, unsigned __int64 limit, const 
std::basic_string & 
orderByColumnName, const bool orderByDescending) Line 435 C++

[sqlite] How this /*A-overwrites-S*/ should work ?

[Domingo Alvarez Duarte]

Thanks for the answer !  
>  Wed Feb 24 2016 6:38:46 pm CET CET from "Richard Hipp"  
>
>
>  The /*A-overwrites-S*/ comment is processed by Lemon, and Lemon has no
> knowledge of #ifdefs.
> 
>
>  



I can understand that but it will confuse other people like it did to me !  

Cheers !

[sqlite] How this /*A-overwrites-S*/ should work ?

[Domingo Alvarez Duarte]

I solved it setting a variable before " /*A-overwrites-S*/", but I's still
confusing it's usage inside a wrapped "#ifdef" that supposed only will be
executed conditionally.  

Cheers !  

oneselect(A) ::= SELECT|XSELECT(S) distinct(D) selcollist(W) from(X)
where_opt(Y)
 groupby_opt(P) having_opt(Q) orderby_opt(Z)
limit_opt(L). {
? int isXSelect = @S == TK_XSELECT;
#if SELECTTRACE_ENABLED
? Token s = S; /*A-overwrites-S*/
#endif
? A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset);
? if(isXSelect) A->selFlags |= SF_NOFlattening;

#if SELECTTRACE_ENABLED
? /* Populate the Select.zSelName[] string that is used to help with
? ** query planner debugging, to differentiate between multiple Select
? ** objects in a complex query.
? **
? ** If the SELECT keyword is immediately followed by a C-style comment
? ** then extract the first few alphanumeric characters from within that
? ** comment to be the zSelName value.? Otherwise, the label is #N where
? ** is an integer that is incremented with each SELECT statement seen.
? */
? if( A!=0 ){
??? const char *z = s.z+ (isXSelect ? 7 : 6);
??? int i;
??? sqlite3_snprintf(sizeof(A->zSelName), A->zSelName, "#%d",
 ++pParse->nSelect);
??? while( z[0]==' ' ) z++;
??? if( z[0]=='/' && z[1]=='*' ){
? z += 2;
? while( z[0]==' ' ) z++;
? for(i=0; sqlite3Isalnum(z[i]); i++){}
? sqlite3_snprintf(sizeof(A->zSelName), A->zSelName, "%.*s", i, z);
??? }
? }
#endif /* SELECTRACE_ENABLED */
}

[sqlite] How this /*A-overwrites-S*/ should work ?

[Domingo Alvarez Duarte]

Here is the full changes I did in parser.y:  

?  

%endif SQLITE_OMIT_COMPOUND_SELECT
oneselect(A) ::= SELECT|XSELECT(S) distinct(D) selcollist(W) from(X)
where_opt(Y)
 groupby_opt(P) having_opt(Q) orderby_opt(Z)
limit_opt(L). {
#if SELECTTRACE_ENABLED
? Token s = S; /*A-overwrites-S*/
#endif
? A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset);
? if(@S == TK_XSELECT) A->selFlags |= SF_NOFlattening;

#if SELECTTRACE_ENABLED
? /* Populate the Select.zSelName[] string that is used to help with
? ** query planner debugging, to differentiate between multiple Select
? ** objects in a complex query.
? **
? ** If the SELECT keyword is immediately followed by a C-style comment
? ** then extract the first few alphanumeric characters from within that
? ** comment to be the zSelName value.? Otherwise, the label is #N where
? ** is an integer that is incremented with each SELECT statement seen.
? */
? if( A!=0 ){
??? const char *z = s.z+ (@S == TK_XSELECT ? 7 : 6);
??? int i;
??? sqlite3_snprintf(sizeof(A->zSelName), A->zSelName, "#%d",
 ++pParse->nSelect);
??? while( z[0]==' ' ) z++;
??? if( z[0]=='/' && z[1]=='*' ){
? z += 2;
? while( z[0]==' ' ) z++;
? for(i=0; sqlite3Isalnum(z[i]); i++){}
? sqlite3_snprintf(sizeof(A->zSelName), A->zSelName, "%.*s", i, z);
??? }
? }
#endif /* SELECTRACE_ENABLED */
}  

?

[sqlite] How this /*A-overwrites-S*/ should work ?

[Domingo Alvarez Duarte]

Hello !  

I have a modification on the parser.y for my own purposes and it was been
parsed till the introduction of " /*A-overwrites-S*/" and it seems that
something is wrong on parser.y see bellow, after the SELECT specification the
" /*A-overwrites-S*/" is wrapped by "#if SELECTTRACE_ENABLED" that is not
enabled in my build but the " /*A-overwrites-S*/" still seem to be active,
even if I move the " /*A-overwrites-S*/" after the usage of " if(@S ==
TK_XSELECT)" I still get the same error.  

parse.y:495: Label S used after '/*A-overwrites-S*/'.  

How to get this to be parsed again ?  

Cheers !  

multiselect_op(A) ::= EXCEPT|INTERSECT(OP).? {A = @OP; /*A-overwrites-OP*/}
%endif SQLITE_OMIT_COMPOUND_SELECT
oneselect(A) ::= SELECT|XSELECT(S) distinct(D) selcollist(W) from(X)
where_opt(Y)
 groupby_opt(P) having_opt(Q) orderby_opt(Z)
limit_opt(L). {
#if SELECTTRACE_ENABLED? ///<<< this seems to intend to disable
/*A-overwrites-S*/ but it doesn't !
? Token s = S; /*A-overwrites-S*/
#endif
? A = sqlite3SelectNew(pParse,W,X,Y,P,Q,Z,D,L.pLimit,L.pOffset);
? if(@S == TK_XSELECT) A->selFlags |= SF_NOFlattening;

#if SELECTTRACE_ENABLED

[sqlite] Issue with v3.11

[Miroslav Rajcic]

Hi Richard,

that was really fast!

Indeed, I was using the SQLITE_OMIT_BLOB_LITERAL flag.
Removing the flag solved the issue.

Thank you guys for a quick solution.

Regards,
   Miroslav

On 24.2.2016. 17:20, Richard Hipp wrote:
> On 2/24/16, Richard Hipp  wrote:
>> What compile-time options are you using?
>>
> Dan deduced that you must be using -DSQLITE_OMIT_BLOB_LITERAL.  That
> (unsupported) compile-time option does indeed induce the error you are
> seeing.  A fix has been checked in
> (https://www.sqlite.org/src/info/b65217c69c59cbe9) or you can simply
> recompile without the SQLITE_OMIT_BLOB_LITERAL compile-time option.

[sqlite] Why is a separate journal file needed ?

[R Smith]

On 2016/02/24 3:49 PM, Richard Hipp wrote:
> On 2/24/16, Simon Slavin  wrote:
>> Why can't the information which SQLite
>> stores in a journal file be put in the database file ?
> Doing so would double the size of the database file.  Every database
> file would contain extra space (normally unused) set aside for the
> journal.

I suppose there is also the problem of recursion. Kinda like Java had 
where every string is an object, and every object has a name, which is a 
string, which is an object, which has a name, which is a string, which 
is an object... ad-infinitum so that they had put in a mechanism for not 
recursing objects for strings of object-names.

If the journal file to a DB is a DB, which has a journal, which is a DB, 
which has a journal... etc. etc.

Q: What does the "B" in "Benoit B. Mandelbrot" stand for?
A: "Benoit B. Mandelbrot".


:)
Ryan

[sqlite] Issue with v3.11

[Miroslav Rajcic]

Hello,
I have a program using sqlite for document storage. Sqlite layer has 
been quite stable until I replaced v3.10.2 with 3.11.

Relevant parts of the program are:
//at app startup
static void SqliteErrorLogCallback(void *pArg, int iErrCode, const char 
*zMsg)
{
 printf("Sqlite error (code: %d, txt: %s): %s\n", iErrCode, 
sqlite3_errstr(iErrCode), zMsg);
}
..
sqlite3_config(SQLITE_CONFIG_LOG, SqliteErrorLogCallback, NULL);
sqlite3_initialize();

//main code
 CppSQLite3Exception err;
 m_db.open(szFile, );
 m_db.setBusyTimeout(5000);// 5 seconds
 m_db.execQuery("PRAGMA synchronous=FULL", );

When using 3.11 program gets error when executing pragma statement. When 
I revert to 3.10.2 everything works OK.
Next I've tried to extract this code to sample project to show the 
error, but the sample works without generating error :(  I'm quite sure 
that I don't have memory overruns or leaks.
Here is the error and the stack trace (I use Visual Studio 2013, 
embedding sqlite as static library within my program):

Error:
Sqlite error (code: 1, txt: SQL logic error or missing database): 
unrecognized token: "x"

Stack trace (relevant parts):

app.cpp (911): SqliteErrorLogCallback
sqlite\sqlite\sqlite3.c (23755): renderLogMsg
sqlite\sqlite\sqlite3.c (23765): sqlite3_log
sqlite\sqlite\sqlite3.c (132362): sqlite3RunParser
sqlite\sqlite\sqlite3.c (109989): sqlite3Prepare
sqlite\sqlite\sqlite3.c (110081): sqlite3LockAndPrepare
sqlite\sqlite\sqlite3.c (110145): sqlite3_prepare
sqlite\sqlite\sqlite3.c (109477): sqlite3InitCallback
sqlite\sqlite\sqlite3.c (109559): sqlite3InitOne
sqlite\sqlite\sqlite3.c (109759): sqlite3Init
sqlite\sqlite\sqlite3.c (109796): sqlite3ReadSchema
sqlite\sqlite\sqlite3.c (107813): sqlite3Pragma
sqlite\sqlite\sqlite3.c (130990): yy_reduce
sqlite\sqlite\sqlite3.c (131399): sqlite3Parser
sqlite\sqlite\sqlite3.c (132338): sqlite3RunParser
sqlite\sqlite\sqlite3.c (109989): sqlite3Prepare
sqlite\sqlite\sqlite3.c (110081): sqlite3LockAndPrepare
sqlite\sqlite\sqlite3.c (110157): sqlite3_prepare_v2
sqlite\cppsqlite3.cpp (1392): CppSQLite3DB::compile
sqlite\cppsqlite3.cpp (1276): CppSQLite3DB::execQuery
notecasecore\notecasecore\formats\formatiosqlite.cpp (312): 
FormatIO_Sqlite::Load
notecasecore\notecasecore\notedocument.cpp (719): NoteDocument::Load

Breaking with debugger at the error inside sqlite3RunParser, it was 
processing this query:
"CREATE TABLE x(type text,name text,tbl_name text,rootpage integer,sql 
text)"

Why would the parser fail on its own internal SQL?
Any idea to point me to the possible source of error ?
File exists on disk and is readable.

Regards,
   Miroslav

[sqlite] Why is a separate journal file needed ?

[Paul]

Good point!

I can assume that the problem would be a sparsity of database file. If you mix 
normal pages and journal then database will be fragmented. 

You can't avoid it. Even if you start writing journal at the end of file, 
hoping that you can later truncate it at commit, there is no way to know how 
far, from the current end of file, should journal file start. At some point, if 
transaction is big enough, new allocated pages in database will hit the spot in 
file where journal pages are. 


24 February 2016, 14:53:55, by "Simon Slavin" :

>   In case you're new to this group, please understand that I'm experienced 
> with SQLite and I understand how it works and how it uses journal files.
> 
> Can someone explain to me -- or point at a web page which does -- why a 
> separate journal file is needed.  Why can't the information which SQLite 
> stores in a journal file be put in the database file ?  Given how SQLite 
> structures its database files this would presumably mean that the main 
> database would have journal pages as well as table and index pages.  Or that 
> there would be 'stable' table and index pages and 'journal' table and index 
> pages. 
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multicolumn unique (resending)

[Klaus Jantzen]

Hello,

after reading the SQLite documentation I decided to change some of my
database
applications to use SQLite.

Following the syntax descriptions and after some experiments I found out
that one cannot
define a multicolumn constraint   ... UNIQUE(col_a, col_b, col_c).

How would you do that in SQLite?
-- 

K.D.J.

[sqlite] How to remove duplicate records

[ad...@shuling.net]

Hi,

I am using SQLite 3.11. I create a table as follows:

CREATE TABLE MyTable (F1 INTEGER, F2
INTEGER, F3 INTEGER);

Then add the following records:

INSERT INTO MyTable (F1, F2, F3) Values (1,
2, 8); 
INSERT INTO MyTable (F1, F2, F3) Values (1,
3, 9);
INSERT INTO MyTable (F1, F2, F3) Values (2,
4, 8);
INSERT INTO MyTable (F1, F2, F3) Values (2,
5, 2);
INSERT INTO MyTable (F1, F2, F3) Values (3,
6, 4);
INSERT INTO MyTable (F1, F2, F3) Values (3,
7, 8);
INSERT INTO MyTable (F1, F2, F3) Values (3,
7, 9);
INSERT INTO MyTable (F1, F2, F3) Values (4,
2, 4);
INSERT INTO MyTable (F1, F2, F3) Values (4,
2, 6);

Now I want to eliminate duplicate record so for two records whose F1 and F2
values are identical, even if their F3 values are different, then one record
with the largest value of F3 will be kept and the other one will be removed.

Is that possible with the DELETE statement?

Thanks

[sqlite] How to remove duplicate records

[Dominique Devienne]

On Wed, Feb 24, 2016 at 1:58 PM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> >Always ran into Error: only a single result allowed for a SELECT that
> >is part of an expression. --DD
>
> In a join, you can directly use a subquery with multiple columns:
> ... JOIN (SELECT ...) ...
>

Thank you Clemens. --DD

sqlite> create table t (a, b, c);
sqlite> insert into t values (1, 1, 1), (1, 1, 2), (1, 2, 1), (2, 1, 1),
(2, 1, 2), (2, 1, 3);
sqlite> delete from t where rowid in (
   ...>   select t.rowid from t
   ...> join (select a, b, max(c) as c from t group by a, b) g
   ...>   on t.a=g.a and t.b=g.b
   ...>where t.c <> g.c
   ...> );
sqlite> select * from t;
1|1|2
1|2|1
2|1|3

[sqlite] How to remove duplicate records

[Clemens Ladisch]

Dominique Devienne wrote:
>Always ran into Error: only a single result allowed for a SELECT that
>is part of an expression. --DD

In a join, you can directly use a subquery with multiple columns:
... JOIN (SELECT ...) ...


Regards,
Clemens

[sqlite] How to remove duplicate records

[Dominique Devienne]

On Wed, Feb 24, 2016 at 8:17 AM,  wrote:

> Now I want to eliminate duplicate record so for two records whose F1 and F2
> values are identical, even if their F3 values are different, then one
> record
> with the largest value of F3 will be kept and the other one will be
> removed.
>
> Is that possible with the DELETE statement?
>

I'm no expert, but with a little thought, here's what I came up with.
I tried using a CTE to avoid the temp table, but didn't succeed...
Always ran into Error: only a single result allowed for a SELECT that is
part of an expression. --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (a, b, c);
sqlite> insert into t values (1, 1, 1), (1, 1, 2), (1, 2, 1), (2, 1, 1),
(2, 1, 2), (2, 1, 3);
sqlite> .header on
sqlite> select * from t;
a|b|c
1|1|1
1|1|2
1|2|1
2|1|1
2|1|2
2|1|3
sqlite> select a, b, max(c) from t group by a, b;
a|b|max(c)
1|1|2
1|2|1
2|1|3
sqlite> create temporary table g as select a, b, max(c) as c from t group
by a, b;
sqlite> select * from g;
a|b|c
1|1|2
1|2|1
2|1|3
sqlite> delete from t where rowid in (
   ...> select t.rowid from t join g on t.a=g.a and t.b=g.b where t.c <> g.c
   ...> );
sqlite> select * from t;
a|b|c
1|1|2
1|2|1
2|1|3
sqlite> drop table temp.g;

[sqlite] SQLite-jdbc Bug - database table is locked

[Simon Slavin]

On 24 Feb 2016, at 2:49am, Pankaj Bisen  wrote:

> I have two unrelated tables table_A and table_B in my SQLite DB. Trying to
> drop table_B while a resultset is open on table_A throws

SQLite does not have table-level locking.  If anything in the database file 
needs to be locked then the whole database file is locked.

Simon.

[sqlite] Why is a separate journal file needed ?

[Simon Slavin]

In case you're new to this group, please understand that I'm experienced with 
SQLite and I understand how it works and how it uses journal files.

Can someone explain to me -- or point at a web page which does -- why a 
separate journal file is needed.  Why can't the information which SQLite stores 
in a journal file be put in the database file ?  Given how SQLite structures 
its database files this would presumably mean that the main database would have 
journal pages as well as table and index pages.  Or that there would be 
'stable' table and index pages and 'journal' table and index pages. 

Simon.

[sqlite] How this /*A-overwrites-S*/ should work ?

[Richard Hipp]

On 2/24/16, Domingo Alvarez Duarte  wrote:
> I solved it setting a variable before " /*A-overwrites-S*/", but I's still
> confusing it's usage inside a wrapped "#ifdef" that supposed only will be
> executed conditionally.

The /*A-overwrites-S*/ comment is processed by Lemon, and Lemon has no
knowledge of #ifdefs.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] How this /*A-overwrites-S*/ should work ?

[Richard Hipp]

On 2/24/16, Domingo Alvarez Duarte  wrote:
> Hello !
>
> I have a modification on the parser.y for my own purposes and it was been
> parsed till the introduction of " /*A-overwrites-S*/" and it seems that
> something is wrong on parser.y see bellow,\

The /*A-overwrites-S*/ comment is a hint to the parser generator that
it can optimize by avoiding copy of a stack element in the PDA.  You
may safely remove the comment.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Late calculation of a field

[ad...@shuling.net]

Hi,

I am using SQLite 3.11. I create a table as follows:

CREATE TABLE MyTable (F1 INTEGER, F2
INTEGER, F3 INTEGER, F4 INTEGER);

Then add the following records:

INSERT INTO MyTable (F1, F2) Values (1, 1); 
INSERT INTO MyTable (F1, F2) Values (1, 2); 
INSERT INTO MyTable (F1, F2) Values (2, 2); 
INSERT INTO MyTable (F1, F2) Values (2, 3); 

I want to select from MyTable and sort based on F1, then F3, then F4, as
follows:

SELECT * FROM MyTable ORDER BY F1, F3, F4

But the F3 and F4 is calculated based on F1, F2 and the calculation is a bit
time-consuming. In such a case, is it possible to perform a late calculation
to improve the performance.

That is:

1.  Compare two records with their F1 values. If they are different,
then return the order directly.
2.  If their F1 values are the same, then calculate the F3 value and
sort based on F3.

Is that possible?

Thanks

[sqlite] WAL checkpoint

[Sairam Gaddam]

Before checkpointing the data from WAL, if the DB is queried, will the
result include updated data from WAL or not?
Will this situation arise? because writing to WAL and checkpoint occur very
fast but if a query comes in between, will the result be updated or not
before checkpoint.

[sqlite] Issue with v3.11

[Richard Hipp]

On 2/24/16, Richard Hipp  wrote:
>
> What compile-time options are you using?
>

Dan deduced that you must be using -DSQLITE_OMIT_BLOB_LITERAL.  That
(unsupported) compile-time option does indeed induce the error you are
seeing.  A fix has been checked in
(https://www.sqlite.org/src/info/b65217c69c59cbe9) or you can simply
recompile without the SQLITE_OMIT_BLOB_LITERAL compile-time option.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Order by multiple columns

[ad...@shuling.net]

Hi,

I am using SQLite 3.11. I create a table as follows:

CREATE TABLE MyTable (F1 INTEGER, F2
INTEGER, F3 INTEGER);

Then add the following records:

INSERT INTO MyTable (F1, F2, F3) Values (1,
2, 8); 
INSERT INTO MyTable (F1, F2, F3) Values (1,
3, 9);
INSERT INTO MyTable (F1, F2, F3) Values (2,
4, 8);
INSERT INTO MyTable (F1, F2, F3) Values (2,
5, 2);
INSERT INTO MyTable (F1, F2, F3) Values (3,
6, 4);
INSERT INTO MyTable (F1, F2, F3) Values (3,
7, 8);
INSERT INTO MyTable (F1, F2, F3) Values (4,
2, 4);
INSERT INTO
MyTable (F1, F2, F3) Values (4, 2, 6);

Now if I want to select from MyTable, and sort the result based on F1
(ascendant), and for two records with same F1, then sort based on
F2(ascendant),  then sort based on F3(ascendant) should I use the following
:

SELECT * FROM MyTable ORDER BY F1, F2, F3?

I check https://www.sqlite.org/lang_select.html#orderby and it said ?Rows
are first sorted based on the results of evaluating the left-most expression
in the ORDER BY list, then ties are broken by evaluating the second
left-most expression and so on.? So based on my understanding, my statement
is correct.

Moreover, since the sort will be performed frequently, to improve the
performance, I want to add a field F4, then update the table so that all the
records will have a unique F4 value which is the order sequence based on the
above order by clause, thus if I want to select again, I do not need to use
ORDER BY F1, F2, F3 any more, instead, I can use:

SELECT * FROM MyTable ORDER BY F4

Is that feasible? If yes, how do to that?

Thanks

[sqlite] Why skip invoking busy handler while pBt->inTransaction!=TRANS_NONE

[Igor Tandetnik]

On 2/24/2016 8:32 AM, sanhua.zh wrote:
> So it?s the question I confused. Why SQLite skip invoking busy handler while 
> it's in TRANS (either read or write) ?

Deadlock detection, I would guess. See 
https://www.sqlite.org/c3ref/busy_handler.html , look for the paragraph 
that mentions "deadlock".
-- 
Igor Tandetnik

[sqlite] Issue with v3.11

[Richard Hipp]

On 2/24/16, Miroslav Rajcic  wrote:
> Hello,
> I have a program using sqlite for document storage. Sqlite layer has
> been quite stable until I replaced v3.10.2 with 3.11.
>
> Error:
> Sqlite error (code: 1, txt: SQL logic error or missing database):
> unrecognized token: "x"
>
>
> Breaking with debugger at the error inside sqlite3RunParser, it was
> processing this query:
> "CREATE TABLE x(type text,name text,tbl_name text,rootpage integer,sql
> text)"
>
> Why would the parser fail on its own internal SQL?
> Any idea to point me to the possible source of error ?

That is very curious.  Why would this work fine for countless millions
of queries, but then suddenly break on yours?

What compile-time options are you using?

Can you send me your database via private email?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Why is a separate journal file needed ?

[Igor Tandetnik]

On 2/24/2016 10:08 AM, Stephen Chrzanowski wrote:
> IMO, all that, plus the fact that you have an easy roll back mechanism.
> Anything that needs to be put in the database is external to the pristine
> database.  Lock the database with a transaction, fill up the journal, the
> power goes out, your pristine database isn't touched.

Rollback journal works the other way round. New data is written to 
database file; previous content of overwritten pages is copied to 
journal file. Committing a transaction is fast - simply delete the 
journal. Rolling back (e.g. after power failure) means copying original 
pages from the journal back to the database file.

In the world you describe - how would you implement committing a 
transaction? At some point, something must write to the database file; 
at that point, the file is no longer "pristine". What happens if power 
goes out in the middle of this?

Note also that a typical application commits much more often than it 
rolls back, so the system should be designed to make the former fast; 
the latter can be slow.
-- 
Igor Tandetnik

[sqlite] sqlite native NuGet package maintainers?

[da...@andl.org]

I have raised an issue for cannot install Nuget native DLL into .NET 4.5
project.

I requested information about where this project is maintained and by whom.

Any response appreciated.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Leeber
Florian
Sent: Wednesday, 24 February 2016 2:40 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] sqlite native NuGet package maintainers?

Dear All,

wrote a mail to Taylor Southwick, but just wanted to confirm he is the right
guy for issues with the NuGet package of sqlite? Specifically the problem is
the inclusion of the Debug build of an old MSVCRT which requires Visual
Studio to be installed, in case you want to run your project in Debug
configurations...

Thx!


Kind regards,

Ing. Florian Leeber, Bakk.
ANDRITZ METALS
MERe
Project Engineer

ANDRITZ AG
Eibesbrunnergasse 20
1121 Vienna, Austria
Phone: +43.5.0805-55581
Mobile: +43.664.9614355
Fax: +43.5.0805-51018
florian.leeber at andritz.com
www.andritz.com




#

This message and any attachments are solely for the use of the intended
recipients. They may contain privileged and/or confidential information or
other information protected from disclosure. If you are not an intended
recipient, you are hereby notified that you received this email in error and
that any review, dissemination, distribution or copying of this email and
any attachment is strictly prohibited. If you have received this email in
error, please contact the sender and delete the message and any attachment
from your system.

Thank You.

Andritz AG
Rechtsform/ Legal form: Aktiengesellschaft/ Corporation Firmensitz/
Registered seat: Graz Firmenbuchgericht/ Court of registry: Landes- als
Handelsgericht Graz Firmenbuchnummer/ Company registration: FN 50935 f
DVR: 005 0008
UID: ATU28609707

#


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

[sqlite] Why is a separate journal file needed ?

[Stephen Chrzanowski]

IMO, all that, plus the fact that you have an easy roll back mechanism.
Anything that needs to be put in the database is external to the pristine
database.  Lock the database with a transaction, fill up the journal, the
power goes out, your pristine database isn't touched.  Just whatever
happens in the journal might get damaged due to caching at the memory or
HDD level.

On Wed, Feb 24, 2016 at 8:50 AM, Paul  wrote:

> Good point!
>
> I can assume that the problem would be a sparsity of database file. If you
> mix normal pages and journal then database will be fragmented.
>
> You can't avoid it. Even if you start writing journal at the end of file,
> hoping that you can later truncate it at commit, there is no way to know
> how far, from the current end of file, should journal file start. At some
> point, if transaction is big enough, new allocated pages in database will
> hit the spot in file where journal pages are.
>
>
> 24 February 2016, 14:53:55, by "Simon Slavin" :
>
> >   In case you're new to this group, please understand that I'm
> experienced with SQLite and I understand how it works and how it uses
> journal files.
> >
> > Can someone explain to me -- or point at a web page which does -- why a
> separate journal file is needed.  Why can't the information which SQLite
> stores in a journal file be put in the database file ?  Given how SQLite
> structures its database files this would presumably mean that the main
> database would have journal pages as well as table and index pages.  Or
> that there would be 'stable' table and index pages and 'journal' table and
> index pages.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Multicolumn unique (resending)

[Richard Hipp]

On 2/24/16, Klaus Jantzen  wrote:
> Hello,
>
> after reading the SQLite documentation I decided to change some of my
> database
> applications to use SQLite.
>
> Following the syntax descriptions and after some experiments I found out
> that one cannot
> define a multicolumn constraint   ... UNIQUE(col_a, col_b, col_c).
>
> How would you do that in SQLite?

It is done in SQLite as in all other SQL database engines, using a
table constraint.  Like this:

   CREATE TABLE t1(a,b,c,d,UNIQUE(a,b,c));

Note the comma before the UNIQUE keyword.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Why is a separate journal file needed ?

[Richard Hipp]

On 2/24/16, Simon Slavin  wrote:
> Why can't the information which SQLite
> stores in a journal file be put in the database file ?

Doing so would double the size of the database file.  Every database
file would contain extra space (normally unused) set aside for the
journal.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] SQLite-jdbc Bug - database table is locked

[Pankaj Bisen]

Hello Everyone,

I have two unrelated tables table_A and table_B in my SQLite DB. Trying to
drop table_B while a resultset is open on table_A throws
"java.sql.SQLException: database table is locked".
Following simple code will illustrate the Bug clearly.
public class Class1 {
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection c = DriverManager.getConnection("jdbc:sqlite:" +
"D:\\test.db");
Statement stmt1 = c.createStatement();
stmt1.executeUpdate("create table table_A (col1 text)");
stmt1.executeUpdate("insert into table_A values ('FIRST')");
stmt1.executeUpdate("create table table_B (col1 text)");
PreparedStatement ps = c.prepareStatement("select * from table_A");
ResultSet rs = ps.executeQuery();
//While the resultset is open on table_A try to
//drop table_B. It gives error that database table is locked
//Which makes no sense because there is no relation between
//table_A and table_B
stmt1.executeUpdate("drop table if exists table_B");
rs.close();
stmt1.close();
c.close();
}
}
*Some other details*

   1. My Java version is 1.8.0_72.
   2. I have tried this with sqlite-jdbc-3.8.11.2.jar as well as
   sqlite-jdbc-3.7.2.jar. But the result is same.
   3. Following are some of the other things that I tried which made no
   difference.
  1. Setting auto-commit on or off
  2. Searched bug-list and googled but all hits are for "database is
  locked" but nothing for "database *table* is locked"
  3. Using try-catch-finally to properly close all resources in case of
  exception.
  4. Closing the connection and reopening it before "Preparedstatement
  ps ...".
  5. Tried to use another connection to drop the table_B. This
  expectedly gave "database is locked" error.
   4. My actual scenario is that I have meta-data in one table based on
   which I need to drop other tables. So I need to drop other tables while
   processing resultset from meta-data table.

[sqlite] Late calculation of a field

[Simon Slavin]

On 24 Feb 2016, at 3:28am, admin at shuling.net wrote:

> Is that possible?

No.

If you are going to make lots of changes to the table and rarely use SELECT, do 
the calculation in the SELECT command.

If you are going to make few changes to the table but do lots of searching and 
sorting, create real F3 and F4 columns and do the calculation in your INSERT 
command.


By the way, your questions suggest you're (A) a beginner with SQL and (B) very 
worried about optimizing your setup so that it runs quickly.  This is common 
and you should not worry so much.  SQLite is extremely fast.  You can best 
spend your time creating simple software which is easy to program and easy to 
debug.

Write the program.  See how fast it does the things you need doing.  Only if 
you find it is too slow should you worry about improving speed.

Simon.

[sqlite] Order by multiple columns

[Simon Slavin]

On 24 Feb 2016, at 3:15am, admin at shuling.net wrote:

> CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER, F3 INTEGER);
> 
> Now if I want to select from MyTable, and sort the result based on F1
> (ascendant), and for two records with same F1, then sort based on
> F2(ascendant),  then sort based on F3(ascendant) should I use the following
> :
> 
> SELECT * FROM MyTable ORDER BY F1, F2, F3?

Yes.  Why not try it ?  Download the SQLite shell tool from the SQLite download 
page and experiment with it.  Instructions here:



> Moreover, since the sort will be performed frequently, to improve the
> performance, I want to add a field F4, then update the table so that all the
> records will have a unique F4 value which is the order sequence based on the
> above order by clause, thus if I want to select again, I do not need to use
> ORDER BY F1, F2, F3 any more, instead, I can use:
> 
> SELECT * FROM MyTable ORDER BY F4
> 
> Is that feasible? If yes, how do to that?

You don't do it like that.  Instead you create an index:

CREATE INDEX m_f1f2f3 ON MyTable (F1, F2, F3)

This does something similar to what you were trying to do with F4, but it means 
that the database has a copy of that table pre-sorted into the order your 
SELECT needs.  The next time you execute the SELECT SQLite will automatically 
spot that it has an idea index already prepared and it will use it.

You will not notice much difference in the amount of time taken with your 
sample database because it has only 7 rows, but once you have 7000 rows the 
figures should be easy to tell apart.

An index is automatically updated every time the data in the table changes.  In 
other words, once you have created it you can forget about it.

Simon.

[sqlite] WAL checkpoint

[Igor Tandetnik]

On 2/24/2016 12:56 AM, Sairam Gaddam wrote:
> Before checkpointing the data from WAL, if the DB is queried, will the
> result include updated data from WAL or not?

It will. A transaction reads both from WAL and the original database 
file - whichever contains the fresher data.
-- 
Igor Tandetnik