Re: [sqlite] [System.Data.SQLite.DLL] Retrieving table names with column names

[Hick Gunter]

Short answer: YES.

This question crops up regulary.

The SQL Standard mandates only that column names set with AS be reliably 
returned. Otherwise each implementation is free to choose whatever name it 
deems appropriate, because - by omitting the AS clause - you state that you 
"don't care". The column name may be the unqualified or qualified field name or 
even the text of the expression. It may also change between executions of the 
same query, usually because the "shape" of your data has changed enough to make 
the query planner choose a different sequence of joins.

Since your programming environment does seem to care about column names, you 
will have to set them explicitly.

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Chris Locke
Gesendet: Donnerstag, 21. Juli 2016 23:20
An: SQLite mailing list 
Betreff: [sqlite] [System.Data.SQLite.DLL] Retrieving table names with column 
names

I've a table I'm calling recursively.

CREATE TABLE "staff" ( `id` TEXT, `logonName` TEXT, `firstname` TEXT, `surname` 
TEXT, `departmentId` TEXT, `managerId` TEXT, `holidayDaysEntitlement` INTEGER, 
`holidayDaysTaken` REAL, PRIMARY
KEY(`id`) )

managerId points to the same table, so my join is simply:

select * from staff
inner join departments on staff.departmentId=departments.id inner join staff as 
managers on staff.managerId=managers.id

This works fine using DB Browser for SQLite, and adding WHERE clauses like '
managers.id='1' ' etc.  (apologies for wrapping that in quotes...)

Anyway.  Using system.data.sqlite.dll in vb.net, only the field names are 
returned.  Using this statement doesn't work:
dim s1 as string=sqlReader("managers.firstname").ToString

Retrieving the 12th field, gives me just 'firstname'
 MsgBox(sqlReader.GetName(11))

I know I can change my SQL statement to be explicit, and select each required 
field and use AS, but is that the only solution?


Thanks,
Chris
___
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] Fwd: Your message to sqlite-users awaits moderator approval

[Richard Hipp]

On 7/22/16, Chris Locke  wrote:
> Bit of a noob question, but whenever I post to the group, I get the below
> email - 'you're not part of the group'.  How does one join the group?  I've
> subscribed, so get all the emails... just wondering if there was a second
> step, or if all group messages get moderated.

Sign-ups are moderated, because otherwise robots will sign-up, send a
bunch of spam, then unsubscribe before I can stop them.  When you
originally signed up the moderator (me) was on an airplane.  I'm back
in the office now and your subscription has been approved.
-- 
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] Fwd: Your message to sqlite-users awaits moderator approval

[Chris Locke]

Bit of a noob question, but whenever I post to the group, I get the below
email - 'you're not part of the group'.  How does one join the group?  I've
subscribed, so get all the emails... just wondering if there was a second
step, or if all group messages get moderated.

Thanks,
Chris


On Wed, Jul 13, 2016 at 1:57 PM, <
sqlite-users-boun...@mailinglists.sqlite.org> wrote:

> Your mail to 'sqlite-users' with the subject
>
> Re: [sqlite] Possible index corruption
>
> Is being held until the list moderator can review it for approval.
>
> The reason it is being held:
>
> Post by non-member to a members-only list
>
> Either the message will get posted to the list, or you will receive
> notification of the moderator's decision.  If you would like to cancel
> this posting, please visit the following URL:
>
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/confirm/sqlite-users/e9805c75143435e371bcbe62c1c3294eda77ce87
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [System.Data.SQLite.DLL] Retrieving table names with column names

[Chris Locke]

I've a table I'm calling recursively.

CREATE TABLE "staff" ( `id` TEXT, `logonName` TEXT, `firstname` TEXT,
`surname` TEXT, `departmentId` TEXT, `managerId` TEXT,
`holidayDaysEntitlement` INTEGER, `holidayDaysTaken` REAL, PRIMARY
KEY(`id`) )

managerId points to the same table, so my join is simply:

select * from staff
inner join departments on staff.departmentId=departments.id
inner join staff as managers on staff.managerId=managers.id

This works fine using DB Browser for SQLite, and adding WHERE clauses like '
managers.id='1' ' etc.  (apologies for wrapping that in quotes...)

Anyway.  Using system.data.sqlite.dll in vb.net, only the field names are
returned.  Using this statement doesn't work:
dim s1 as string=sqlReader("managers.firstname").ToString

Retrieving the 12th field, gives me just 'firstname'
 MsgBox(sqlReader.GetName(11))

I know I can change my SQL statement to be explicit, and select each
required field and use AS, but is that the only solution?


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

Re: [sqlite] SQL to SQLite

[Robby Helperin]

Thanks, Dana.

Yes, that's vb.net.



-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of dmp
Sent: Thursday, July 21, 2016 11:58 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SQL to SQLite

> Thanks Dana,

> I think you're talking about doing what I did, but I'm not 100% sure.

> Below is my solution.  Could you let me know if yours is the same or 
> something more elegant?

Hello,

I'm not sure of the programming language, VBasic, ?, but generally looking
through it looks like essentially what I indicated and my code does.

So to summarize your options, others indicated also.

Note: 1. & 2. imply different source database.

1. Dump the data, CSV/SQL, format from the source database,
   then import into the new destination SQLite database.
   Somewhat easy, but manual so slow. Could code the import
   export together to improve efficiency.

2. Do a database to database transfer, much harder, to get
   right, mainly because the data type conversions. Looks
   like you code is taking into account and SQLMate could
   help. Relatively fast.

3. If using SQLite to SQLite, looks like indicated on the
   mailing list use ATTACH. Seems the easiest approach
   and fastest.

danap.

___
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] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

[Simon Slavin]

On 22 Jul 2016, at 3:10am, Quan Yong Zhai  wrote:

> If remove use of shared cache from file specifications, they will be two 
> separated memory databases

I am sorry.  I didn't think of that.  I agree that you need shared cache to 
make your program work.

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

Re: [sqlite] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

[Quan Yong Zhai]

If remove use of shared cache from file specifications, they will be two 
separated memory databases

发件人: Simon Slavin
发送时间: ‎2016/‎7/‎22 9:32
收件人: SQLite mailing list
主题: Re: [sqlite] In what case will the online backup api hang for acquiring the 
Btree lock of the destination on-disk file?


On 22 Jul 2016, at 2:12am, Yihong Zhan  wrote:

> Thanks Quan Yhong and Stephen. Today I did more experiment and finally 
> narrowed down the cause (but not the root cause) of the problem.

Please remove all use of shared cache from your file specifications and try 
again.

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] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

[Simon Slavin]

On 22 Jul 2016, at 2:12am, Yihong Zhan  wrote:

> Thanks Quan Yhong and Stephen. Today I did more experiment and finally 
> narrowed down the cause (but not the root cause) of the problem.

Please remove all use of shared cache from your file specifications and try 
again.

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

Re: [sqlite] In what case will the online backup api hang for acquiring the Btree lock of the destination on-disk file?

[Yihong Zhan]

Thanks Quan Yhong and Stephen. Today I did more experiment and finally narrowed 
down the cause (but not the root cause) of the problem.

Under the following situation the in-memory source database and the NFS disk 
destination database will meet deadlock across 2 threads:

  *   Thread 1 (T1) creates an in-memory database with a dir-path-like database 
name, such as “/NFS/some_dir/database” (/NFS/some_dir/ exists)
 *   std::string m_tmpFileName = 
"file:/NFS/some_dir/database?mode=memory&=shared”;
 *   int rv = sqlite3_open_v2(m_tmpFileName.c_str(), _sqlObj, 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE | 
SQLITE_OPEN_URI, NULL);
  *   Thread 2 (T2) creates a NFS disk database with exactly the same 
dir-path-like database name - “/NFS/some_dir/database”
 *   std::string m_fileNameOnNFS = "/NFS/some_dir/database”;
 *   int rc = sqlite3_open(m_fileNameOnNFS.c_str(), _sqlBackupDestObj);

This is the event sequence in my scenario when deadlock is reproduced:

  1.  T1 is writing to the in-memory db. Meanwhile, T2 is trying to back up the 
in-memory db to NFS db but fails due to in-memory db is busy (sqlite3_back_step 
returns SQLITE_BUSY).
  2.  T1 finishes the writing.
  3.  T2 retries the back-up. It acquires the in-memory db’s mutex and Btree’s 
mutex. Then acquires the NFS db’s mutex. But finally fails to acquire the NFS 
db’s Btree’s lock somehow (probably due to the 2 databases share the same name)
  4.  T1 tries to write to in-memory db again. However, it fails to acquire the 
mutex since T2 has acquired it in step #3. Now, both threads cannot go ahead.

It looks sqlite does not handle the locking well for this case where both 
databases use the same dir-path-like database name. I have tried that if the 
name does not include any slashes (“/“) there would be no problem.

Thanks

From: Quan Yong Zhai >
Date: Tuesday, July 19, 2016 at 23:56
To: Yihong Zhan >, 
"sqlite-users@mailinglists.sqlite.org"
 
>
Subject: RE: [sqlite] In what case will the online backup api hang for 
acquiring the Btree lock of the destination on-disk file?

https://www.sqlite.org/faq.html#q5

“But use caution: this locking mechanism might not work correctly if the 
database file is kept on an NFS filesystem. This is because fcntl() file 
locking is broken on many NFS implementations. You should avoid putting SQLite 
database files on NFS if multiple processes might try to access the file at the 
same time.”


Sent from 
Mail
 for Windows 10

From: Yihong Zhan
Sent: 2016年7月19日 19:44
To: 
sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] In what case will the online backup api hang for acquiring 
the Btree lock of the destination on-disk file?

Hi sqlite experts,

I am currently incorporating the sqlite online backup API (version 3.8.0.2) 
into our product. I meet a tricky problem, where the sqlite3_backup_step hangs 
at acquiring the BTree’s lock of the destination file.

Specifically, I am backing up in-memory database to NFS disk file. The 
in-memory database is manipulated in the main thread (T1) while the back-up 
runs in another thread (T2). There are only 2 threads in the process and there 
is no other process existing in our product environment.

The stack trace I got in the T2 when the hanging happens is as following:
#0  0x003de740d654 in __lll_lock_wait () from /lib64/libpthread.so.0
#1  0x003de7408f4a in _L_lock_1034 () from /lib64/libpthread.so.0
#2  0x003de7408e0c in pthread_mutex_lock () from /lib64/libpthread.so.0
#3  0x2acc20f6 in pthreadMutexEnter (p=0x23084300) at sqlite3.c:18299
#4  0x2acc1f31 in sqlite3_mutex_enter (p=0x23084300) at sqlite3.c:17812
#5  0x2acd9e4f in lockBtreeMutex (p=0x2aaad8001040) at sqlite3.c:49323
#6  0x2acd9f49 in sqlite3BtreeEnter (p=0x2aaad8001040) at 
sqlite3.c:49410
#7  0x2acdd970 in sqlite3BtreeBeginTrans (p=0x2aaad8001040, wrflag=2) 
at sqlite3.c:52240
#8  0x2ace71b7 in sqlite3_backup_step (p=0x2aaad8000fc0, nPage=50) at 
sqlite3.c:58451
#9  0x2aaace885b37 in ResDB::backup (this=0x2314c3f0, numBlocks=10, 
numPagesPerBlock=50, blockIntervalInMillisec=50) at 

Re: [sqlite] SQL to SQLite

[dmp]

> Thanks Dana,

> I think you're talking about doing what I did, but I'm not 100% sure.

> Below is my solution.  Could you let me know if yours is the same or
> something more elegant?

Hello,

I'm not sure of the programming language, VBasic, ?, but generally
looking through it looks like essentially what I indicated and my code
does.

So to summarize your options, others indicated also.

Note: 1. & 2. imply different source database.

1. Dump the data, CSV/SQL, format from the source database,
   then import into the new destination SQLite database.
   Somewhat easy, but manual so slow. Could code the import
   export together to improve efficiency.

2. Do a database to database transfer, much harder, to get
   right, mainly because the data type conversions. Looks
   like you code is taking into account and SQLMate could
   help. Relatively fast.

3. If using SQLite to SQLite, looks like indicated on the
   mailing list use ATTACH. Seems the easiest approach
   and fastest.

danap.

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

Re: [sqlite] Error or Crash using OR in MATCH clause

[Julian Dohmen]

Dan

Thank you, and another member - Simon Slavin, for replying so quickly. It does 
look exactly like my problem: we'll await 3.14 with interest.


Julian Dohmen | developer
Evelyn Manufacturing | Alexandria & KeepnTrack 
801.943.7277 ext 565 | jdoh...@companioncorp.com 





 From:   Dan Kennedy  
 To:    
 Sent:   7/21/2016 12:01 PM 
 Subject:   Re: [sqlite] Error or Crash using OR in MATCH clause 

On 07/21/2016 11:16 PM, Julian Dohmen wrote: 
> I am encountering errors or crashes when using FTS5 and MATCH expressions 
> involving 
>   OR  
> combined with 
>   ORDER BY rank 
> = 
 
Thanks for the detailed report. I think this one was fixed a couple of  
weeks after 3.13 was released: 
 
   http://sqlite.org/src/info/64ca1a835a89fd21 
 
The fix will be part of 3.14. 
 
Dan. 
 
 
 
 
 
 
> 
> Simplest example [using SQLite program]: 
> --- 
> DROP TABLE IF EXISTS VTest; 
> CREATE virtual TABLE VTest USING FTS5(Title, AUthor, tokenize ='porter 
> unicode61 remove_diacritics 1', columnsize='1', detail=full); 
> INSERT INTO VTest (Title, Author) VALUES ('wrinkle in time', 'Bill Smith'); 
> 
> SELECT * FROM VTest; 
> OK 
> 
> SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time OR a wrinkle in time'; 
> OK 
> 
> SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time' ORDER BY rank 
> OK 
> 
> SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time OR a wrinkle in time' 
> ORDER BY rank; 
> ERROR 
> Error: database disk image is malformed 
> 
> Note that the error [or commonly a crash in my system with a real table/data] 
> seems to occur when the OR is combined with ORDER BY 
> These [simplified] results were obtained with the SQLite shell program built 
> with SQLite 3.13. 
> = 
> 
> SQLite build: [Windows 10, VS 2015] Preprocessor flags: 
> --- 
> WIN32 
> _DEBUG 
> _WINDOWS 
> SQLITE_THREADSAFE=2 
> SQLITE_ALLOW_COVERING_INDEX_SCAN=1 
> SQLITE_ENABLE_STAT4=1 
> SQLITE_TEMP_STORE=2 
> SQLITE_ENABLE_FTS5=1 
> SQLITE_ENABLE_FTS4=1 
> SQLITE_ENABLE_FTS3=1 
> SQLITE_ENABLE_FTS3_PARENTHESIS=1 
>  
> 
> I also see this kind of problem in SQLiteStudio with SQLite 3.12.2 and [most 
> importantly to us] in our application built with SQLite3.13 [with same flags 
> above] 
> I assume that I misunderstood something about the syntax or flags, but 
> reviewing the SQLite docs on FTS5 I don't see where I went wrong. 
> = 
> 
> Further details: 
> --- 
> In our program [32-bit debug build with above flags] with VS 2015 debugger 
> set to trap exceptions we see this stack trace 
> 
>       ()    Unknown 
>       [Frames below may be incorrect and/or missing] 
>      Alexandria.exe!fts5MultiIterNext(Fts5Index * p, Fts5Iter * pIter, int 
> bFrom, __int64 iFrom) Line 186755    C 
>       Alexandria.exe!fts5MultiIterNextFrom(Fts5Index * p, Fts5Iter * pIter, 
> __int64 iMatch) Line 187418    C 
>       Alexandria.exe!sqlite3Fts5IterNextFrom(Fts5IndexIter * pIndexIter, 
> __int64 iMatch) Line 189325    C 
>       Alexandria.exe!fts5ExprNodeNext_TERM(Fts5Expr * pExpr, Fts5ExprNode * 
> pNode, int bFromValid, __int64 iFrom) Line 181773    C 
>       Alexandria.exe!fts5ExprNodeTest_AND(Fts5Expr * pExpr, Fts5ExprNode * 
> pAnd) Line 181852    C 
>       Alexandria.exe!fts5ExprNodeNext_AND(Fts5Expr * pExpr, Fts5ExprNode * 
> pNode, int bFromValid, __int64 iFrom) Line 181891    C 
>       Alexandria.exe!fts5ExprNodeNext_OR(Fts5Expr * pExpr, Fts5ExprNode * 
> pNode, int bFromValid, __int64 iFrom) Line 181821    C 
>       Alexandria.exe!sqlite3Fts5ExprNext(Fts5Expr * p, __int64 iLast) Line 
> 182079    C 
>       Alexandria.exe!fts5NextMethod(sqlite3_vtab_cursor * pCursor) Line 
> 191208    C 
>       Alexandria.exe!sqlite3VdbeExec(Vdbe * p) Line 83260    C 
>       Alexandria.exe!sqlite3Step(Vdbe * p) Line 75131    C 
>       Alexandria.exe!sqlite3_step(sqlite3_stmt * pStmt) Line 75193    C 
>       Alexandria.exe!fts5SorterNext(Fts5Cursor * pCsr) Line 191102    C 
>       Alexandria.exe!fts5CursorFirstSorted(Fts5Table * pTab, Fts5Cursor * 
> pCsr, int bDesc) Line 191303    C 
>       Alexandria.exe!fts5FilterMethod(sqlite3_vtab_cursor * pCursor, int 
> idxNum, const char * zUnused, int nVal, Mem * * apVal) Line 191588    C 
>       Alexandria.exe!sqlite3VdbeExec(Vdbe * p) Line 83174    C 
>       Alexandria.exe!sqlite3Step(Vdbe * p) Line 75131    C 
>       Alexandria.exe!sqlite3_step(sqlite3_stmt * pStmt) Line 75193    C 
>       Alexandria.exe!nsFTS5SearchService::`anonymous 
> namespace'::DB_Worker::operator()(const 
> std::basic_string & 
> csQuery, unsigned int nMaxToFind, 
> std::vector  > 

Re: [sqlite] Error or Crash using OR in MATCH clause

[Dan Kennedy]

On 07/21/2016 11:16 PM, Julian Dohmen wrote:

I am encountering errors or crashes when using FTS5 and MATCH expressions 
involving
  OR 
combined with
  ORDER BY rank
=


Thanks for the detailed report. I think this one was fixed a couple of 
weeks after 3.13 was released:


  http://sqlite.org/src/info/64ca1a835a89fd21

The fix will be part of 3.14.

Dan.








Simplest example [using SQLite program]:
---
DROP TABLE IF EXISTS VTest;
CREATE virtual TABLE VTest USING FTS5(Title, AUthor, tokenize ='porter 
unicode61 remove_diacritics 1', columnsize='1', detail=full);
INSERT INTO VTest (Title, Author) VALUES ('wrinkle in time', 'Bill Smith');

SELECT * FROM VTest;
OK

SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time OR a wrinkle in time';
OK

SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time' ORDER BY rank
OK

SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time OR a wrinkle in time' 
ORDER BY rank;
ERROR
Error: database disk image is malformed

Note that the error [or commonly a crash in my system with a real table/data] 
seems to occur when the OR is combined with ORDER BY
These [simplified] results were obtained with the SQLite shell program built 
with SQLite 3.13.
=

SQLite build: [Windows 10, VS 2015] Preprocessor flags:
---
WIN32
_DEBUG
_WINDOWS
SQLITE_THREADSAFE=2
SQLITE_ALLOW_COVERING_INDEX_SCAN=1
SQLITE_ENABLE_STAT4=1
SQLITE_TEMP_STORE=2
SQLITE_ENABLE_FTS5=1
SQLITE_ENABLE_FTS4=1
SQLITE_ENABLE_FTS3=1
SQLITE_ENABLE_FTS3_PARENTHESIS=1


I also see this kind of problem in SQLiteStudio with SQLite 3.12.2 and [most 
importantly to us] in our application built with SQLite3.13 [with same flags 
above]
I assume that I misunderstood something about the syntax or flags, but 
reviewing the SQLite docs on FTS5 I don't see where I went wrong.
=

Further details:
---
In our program [32-bit debug build with above flags] with VS 2015 debugger set 
to trap exceptions we see this stack trace

  ()Unknown
  [Frames below may be incorrect and/or missing]
 Alexandria.exe!fts5MultiIterNext(Fts5Index * p, Fts5Iter * pIter, int 
bFrom, __int64 iFrom) Line 186755C
  Alexandria.exe!fts5MultiIterNextFrom(Fts5Index * p, Fts5Iter * pIter, 
__int64 iMatch) Line 187418C
  Alexandria.exe!sqlite3Fts5IterNextFrom(Fts5IndexIter * pIndexIter, 
__int64 iMatch) Line 189325C
  Alexandria.exe!fts5ExprNodeNext_TERM(Fts5Expr * pExpr, Fts5ExprNode * 
pNode, int bFromValid, __int64 iFrom) Line 181773C
  Alexandria.exe!fts5ExprNodeTest_AND(Fts5Expr * pExpr, Fts5ExprNode * 
pAnd) Line 181852C
  Alexandria.exe!fts5ExprNodeNext_AND(Fts5Expr * pExpr, Fts5ExprNode * 
pNode, int bFromValid, __int64 iFrom) Line 181891C
  Alexandria.exe!fts5ExprNodeNext_OR(Fts5Expr * pExpr, Fts5ExprNode * 
pNode, int bFromValid, __int64 iFrom) Line 181821C
  Alexandria.exe!sqlite3Fts5ExprNext(Fts5Expr * p, __int64 iLast) Line 
182079C
  Alexandria.exe!fts5NextMethod(sqlite3_vtab_cursor * pCursor) Line 191208  
  C
  Alexandria.exe!sqlite3VdbeExec(Vdbe * p) Line 83260C
  Alexandria.exe!sqlite3Step(Vdbe * p) Line 75131C
  Alexandria.exe!sqlite3_step(sqlite3_stmt * pStmt) Line 75193C
  Alexandria.exe!fts5SorterNext(Fts5Cursor * pCsr) Line 191102C
  Alexandria.exe!fts5CursorFirstSorted(Fts5Table * pTab, Fts5Cursor * pCsr, 
int bDesc) Line 191303C
  Alexandria.exe!fts5FilterMethod(sqlite3_vtab_cursor * pCursor, int 
idxNum, const char * zUnused, int nVal, Mem * * apVal) Line 191588C
  Alexandria.exe!sqlite3VdbeExec(Vdbe * p) Line 83174C
  Alexandria.exe!sqlite3Step(Vdbe * p) Line 75131C
  Alexandria.exe!sqlite3_step(sqlite3_stmt * pStmt) Line 75193C
  Alexandria.exe!nsFTS5SearchService::`anonymous namespace'::DB_Worker::operator()(const 
std::basic_string & csQuery, unsigned int 
nMaxToFind, 
std::vector & vecResp) Line 76C++

With this query:
SELECT T2TFTSrsn FROM T2TitleFTS WHERE  T2TitleFTS MATCH '{T2TFTStitle 
T2TFTSseries}:a wrinkle in time OR wrinkle in time' ORDER BY rank LIMIT 50;
=



Julian Dohmen | developer
Evelyn Manufacturing | Alexandria & KeepnTrack
801.943.7277 ext 565 | jdoh...@companioncorp.com

___
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] Error or Crash using OR in MATCH clause

[Julian Dohmen]

In SQLite.exe [the source of the simple example I sent]
PRAGMA integrity_check;
Ok


Julian Dohmen | developer
Evelyn Manufacturing | Alexandria & KeepnTrack 
801.943.7277 ext 565 | jdoh...@companioncorp.com 





 From:   Simon Slavin  
 To:   SQLite mailing list  
 Sent:   7/21/2016 11:45 AM 
 Subject:   Re: [sqlite] Error or Crash using OR in MATCH clause 

 
On 21 Jul 2016, at 5:16pm, Julian Dohmen  wrote: 
 
> SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time OR a wrinkle in time' 
> ORDER BY rank; 
> ERROR 
> Error: database disk image is malformed 
 
What is the result of 
 
PRAGMA integrity_check 
 
? 
 
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] Error or Crash using OR in MATCH clause

[Simon Slavin]

On 21 Jul 2016, at 5:16pm, Julian Dohmen  wrote:

> SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time OR a wrinkle in time' 
> ORDER BY rank;
> ERROR
> Error: database disk image is malformed

What is the result of

PRAGMA integrity_check

?

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

[sqlite] Error or Crash using OR in MATCH clause

[Julian Dohmen]

I am encountering errors or crashes when using FTS5 and MATCH expressions 
involving
 OR 
combined with
 ORDER BY rank
=

Simplest example [using SQLite program]:
---
DROP TABLE IF EXISTS VTest;
CREATE virtual TABLE VTest USING FTS5(Title, AUthor, tokenize ='porter 
unicode61 remove_diacritics 1', columnsize='1', detail=full);
INSERT INTO VTest (Title, Author) VALUES ('wrinkle in time', 'Bill Smith');

SELECT * FROM VTest;
OK

SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time OR a wrinkle in time';
OK

SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time' ORDER BY rank
OK

SELECT * FROM VTest WHERE VTest MATCH 'wrinkle in time OR a wrinkle in time' 
ORDER BY rank;
ERROR
Error: database disk image is malformed

Note that the error [or commonly a crash in my system with a real table/data] 
seems to occur when the OR is combined with ORDER BY
These [simplified] results were obtained with the SQLite shell program built 
with SQLite 3.13.
=

SQLite build: [Windows 10, VS 2015] Preprocessor flags:
---
WIN32
_DEBUG
_WINDOWS
SQLITE_THREADSAFE=2
SQLITE_ALLOW_COVERING_INDEX_SCAN=1
SQLITE_ENABLE_STAT4=1
SQLITE_TEMP_STORE=2
SQLITE_ENABLE_FTS5=1
SQLITE_ENABLE_FTS4=1
SQLITE_ENABLE_FTS3=1
SQLITE_ENABLE_FTS3_PARENTHESIS=1


I also see this kind of problem in SQLiteStudio with SQLite 3.12.2 and [most 
importantly to us] in our application built with SQLite3.13 [with same flags 
above]
I assume that I misunderstood something about the syntax or flags, but 
reviewing the SQLite docs on FTS5 I don't see where I went wrong.
=

Further details:
---
In our program [32-bit debug build with above flags] with VS 2015 debugger set 
to trap exceptions we see this stack trace

 ()    Unknown
 [Frames below may be incorrect and/or missing]    
    Alexandria.exe!fts5MultiIterNext(Fts5Index * p, Fts5Iter * pIter, int 
bFrom, __int64 iFrom) Line 186755    C
 Alexandria.exe!fts5MultiIterNextFrom(Fts5Index * p, Fts5Iter * pIter, 
__int64 iMatch) Line 187418    C
 Alexandria.exe!sqlite3Fts5IterNextFrom(Fts5IndexIter * pIndexIter, __int64 
iMatch) Line 189325    C
 Alexandria.exe!fts5ExprNodeNext_TERM(Fts5Expr * pExpr, Fts5ExprNode * 
pNode, int bFromValid, __int64 iFrom) Line 181773    C
 Alexandria.exe!fts5ExprNodeTest_AND(Fts5Expr * pExpr, Fts5ExprNode * pAnd) 
Line 181852    C
 Alexandria.exe!fts5ExprNodeNext_AND(Fts5Expr * pExpr, Fts5ExprNode * 
pNode, int bFromValid, __int64 iFrom) Line 181891    C
 Alexandria.exe!fts5ExprNodeNext_OR(Fts5Expr * pExpr, Fts5ExprNode * pNode, 
int bFromValid, __int64 iFrom) Line 181821    C
 Alexandria.exe!sqlite3Fts5ExprNext(Fts5Expr * p, __int64 iLast) Line 
182079    C
 Alexandria.exe!fts5NextMethod(sqlite3_vtab_cursor * pCursor) Line 191208   
 C
 Alexandria.exe!sqlite3VdbeExec(Vdbe * p) Line 83260    C
 Alexandria.exe!sqlite3Step(Vdbe * p) Line 75131    C
 Alexandria.exe!sqlite3_step(sqlite3_stmt * pStmt) Line 75193    C
 Alexandria.exe!fts5SorterNext(Fts5Cursor * pCsr) Line 191102    C
 Alexandria.exe!fts5CursorFirstSorted(Fts5Table * pTab, Fts5Cursor * pCsr, 
int bDesc) Line 191303    C
 Alexandria.exe!fts5FilterMethod(sqlite3_vtab_cursor * pCursor, int idxNum, 
const char * zUnused, int nVal, Mem * * apVal) Line 191588    C
 Alexandria.exe!sqlite3VdbeExec(Vdbe * p) Line 83174    C
 Alexandria.exe!sqlite3Step(Vdbe * p) Line 75131    C
 Alexandria.exe!sqlite3_step(sqlite3_stmt * pStmt) Line 75193    C
 Alexandria.exe!nsFTS5SearchService::`anonymous 
namespace'::DB_Worker::operator()(const 
std::basic_string & csQuery, 
unsigned int nMaxToFind, 
std::vector & vecResp) Line 76    C++

With this query:
SELECT T2TFTSrsn FROM T2TitleFTS WHERE  T2TitleFTS MATCH '{T2TFTStitle 
T2TFTSseries}:a wrinkle in time OR wrinkle in time' ORDER BY rank LIMIT 50;
=



Julian Dohmen | developer
Evelyn Manufacturing | Alexandria & KeepnTrack 
801.943.7277 ext 565 | jdoh...@companioncorp.com 

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

Re: [sqlite] SQL to SQLite

[Tim Streater]

On 20 Jul 2016 at 23:14, Robby Helperin  wrote: 

> Thanks, Simon, this looks promising!
>
> Is this something that can be done programmatically in code (vb.net) or only
> from the command prompt?
>
> If programmatically, could you shoot me a quick syntax example?

Here's an example of what I do in PHP to move a row from one database to 
another. I need to go via a memory database in order to get a unique absid in 
the destination db:

 $dbh->exec ("attach database ':memory:' as mem');
 $dbh->exec ("create table mem.messages as select * from main.messages 
where absid=" . $absid);
 $dbh->exec ("update mem.messages set absid=null");

 $dbh->exec ("attach database '/path/to/destination/db' as dst");
 $dbh->exec ("insert into dst.messages select * from mem.messages");
 $dbh->exec ("delete from main.messages where absid=" . $absid);

Here, $dbh is a handle for the source database, $absid gives the id of the row 
to be moved from the source db. In the destination db it has a new, unique, id.

Doing it as above means that you don't need to know what the schema is for the 
messages table, as long as it is the same in both db.

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

Re: [sqlite] SQL to SQLite

[Robby Helperin]

Thanks, Simon, this looks promising!

Is this something that can be done programmatically in code (vb.net) or only
from the command prompt?

If programmatically, could you shoot me a quick syntax example?



-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Wednesday, July 20, 2016 6:46 AM
To: SQLite mailing list
Subject: Re: [sqlite] SQL to SQLite


On 20 Jul 2016, at 12:21am, Robby Helperin  wrote:

> Any SQLite string is going to refer to just one database, so you can't 
> write an INSERT command that will take from one database and write to 
> another, or can you?  How would this normally done?

See the ATTACH command:



Open one database file and ATTACH another.  Once command can refer to tables
in both database files.

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] SQL to SQLite

[Robby Helperin]

Thanks Dana,

I think you're talking about doing what I did, but I'm not 100% sure.  

Below is my solution.  Could you let me know if yours is the same or
something more elegant?

If it's the same, maybe this code will be helpful for other people.  How'd I
do?  (I'm not a pro, so go easy):


Public Sub InsertSongVers(ByVal tableWanted As String)

'SQL Server
Dim myTable As String = tableWanted

dim sConnString As String = "..."   'insert your
connection to SQL string here
dim myconnection As New SqlConnection(sConnString)
myconnection.Open()
Dim SCMSdataadapter As New SqlDataAdapter("Select * from " &
myTable, myconnection)
Dim dtSCMS As New DataTable(myTable)
SCMSdataadapter.Fill(dtSCMS)
myconnection.Close()

   


'create an array with the column data types
Dim mySQLFields As String = "("
Dim columnNames = New List(Of String)
Dim columnTypes = New List(Of String)

For Each column As DataColumn In dtSCMS.Columns
Dim newItem As String = column.ColumnName
columnNames.Add(newItem)

Dim newTypeItem As String
newTypeItem = column.DataType.ToString
columnTypes.Add(newTypeItem)

If mySQLFields = "(" Then
mySQLFields = mySQLFields & "[" & newItem & "]"
Else
mySQLFields = mySQLFields & ",[" & newItem & "]"
End If

Next
mySQLFields = mySQLFields & ")"
Debug.Writeline("Here's the list of data types: " & mySQLFields)

'create a string with the data

Dim myRowNum As Integer = 0
For Each row As DataRow In dtSCMS.Rows
Dim mySQLRowData As String = "("
Dim columnIndex As Integer = -1 ' because first index is 0
For Each cell In row.ItemArray  
columnIndex = columnIndex + 1
Dim myAddSt As String = cell.ToString
Dim myType As String = ""

Select Case columnTypes.Item(columnIndex)
Case "System.String"'nvarchar(50)
myAddSt = "'" & Replace(myAddSt, "'", "''") & "'"
Case "System.Int32"'bigint
If cell.GetType.ToString = "System.DBNull" Then
myAddSt = "'null'"
Case "System.DBNull"'null   -don't think this can
happen now

Case "System.DateTime"'datetime
myAddSt = "'" & myAddSt & "'"
Case "System.Boolean"'bit
Select Case myAddSt
Case "False"
myAddSt = "0"
Case "True"
myAddSt = "1"
Case IsNothing(myAddSt) 'don't know if this
one's possible, but what the hey
myAddSt = "0"
Case ""
myAddSt = "0"
End Select
Case "System.Byte[]"
Dim rowNumber As Int64 = BitConverter.ToInt64(cell,
0)
myAddSt = rowNumber.ToString() '"'" & myAddSt & "'"
Case Else
MsgBox("Didn't expect to see " &
cell.GetType.ToString & " for type.  Check it out.")
End Select


If mySQLRowData = "(" Then
mySQLRowData = mySQLRowData & myAddSt
Else
mySQLRowData = mySQLRowData & "," & myAddSt
End If




Next
mySQLRowData = mySQLRowData & ");"
Debug.WriteLine("Here's the data: " & mySQLRowData)


Dim mySQLUp As String = ("INSERT INTO [" & myTable & "] " &
mySQLFields & " VALUES " & mySQLRowData)
Debug.WriteLine("Here's the full SQLite syntax: " & mySQLUp)
  

Try
'SQLite
Dim connectionString As String = "Data Source=...path to
your Database" 
Dim dbConnection As New SQLiteConnection(connectionString)

dbConnection.Open()
Dim command = New SQLiteCommand(mySQLUp, dbConnection)
command.ExecuteNonQuery()
dbConnection.Close()
 Catch ex As Exception
If ex.Message = "constraint failed" & vbCrLf & "UNIQUE
constraint failed: SongVersionsTEST.SongVerID" Then
Debug.WriteLine("That record is already in the table.")
'I think this line came from the "INSERT" version of this code I wrote and
probably don't need it here, sorry.
End If
End Try

myRowNum = myRowNum + 1
Debug.WriteLine(myRowNum)
Next 'next row


MsgBox("All done! " & myRowNum & " records")

-Original Message-
From: 

Re: [sqlite] [Spellfix] Inexplicable discrepancy concerning query results

[Adamek, Jochen (CQTN)]

Yes, you are right. I am using an older SQLite version in Python: 3.8.6.

I will update and check that.

Thanks.

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Stephan 
Beal
Gesendet: Donnerstag, 21. Juli 2016 10:57
An: SQLite mailing list
Betreff: Re: [sqlite] [Spellfix] Inexplicable discrepancy concerning query 
results

On Thu, Jul 21, 2016 at 10:54 AM, Adamek, Jochen (CQTN) <
jochen.ada...@carmeq.com> wrote:

> The first 4 rows have a different score and a different distance. This
> discrepancy occurs with most example queries. But changing the scope value
> of the query in my Python code, it changes the result, so it is not the
> case, that SQLite does not process the scope operation via the
> Python-SQLite interface.
>
> Any idea?
>

It sounds to me like your python and local sqlite shell are using different
sqlite versions.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
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] [Spellfix] Inexplicable discrepancy concerning query results

[Stephan Beal]

On Thu, Jul 21, 2016 at 10:54 AM, Adamek, Jochen (CQTN) <
jochen.ada...@carmeq.com> wrote:

> The first 4 rows have a different score and a different distance. This
> discrepancy occurs with most example queries. But changing the scope value
> of the query in my Python code, it changes the result, so it is not the
> case, that SQLite does not process the scope operation via the
> Python-SQLite interface.
>
> Any idea?
>

It sounds to me like your python and local sqlite shell are using different
sqlite versions.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [Spellfix] Inexplicable discrepancy concerning query results

[Adamek, Jochen (CQTN)]

Hello,

using the spellfix-functionality in 
SQLite, I realized, that in Python, the following query leads to different 
results compared to the direct execution with SQLite.

Code fragment in Python:

qS = "select * from pf where word MATCH 'ro:|z@nStr|?a|s@' and scope=8 and 
rank=14";
cS = connection.execute(qS)


This leads to:
ro:|z=n|StY|k6|ve:k
raI|C=n|StaI|n6|ho:f
ro:|z=n|Stra:|s@

When I execute the same query directly with  SQLite, I get the following result:

ro:|z=n|Stra:|s@
re:|z=n|Stra:|s@
rOY|z=n|Stra:|s@

The first 4 rows have a different score and a different distance. This 
discrepancy occurs with most example queries. But changing the scope value of 
the query in my Python code, it changes the result, so it is not the case, that 
SQLite does not process the scope operation via the Python-SQLite interface.

Any idea?

Kind regards

Jochen Adamek




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