[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

2015-12-14 Thread Bart Smissaert
OK, thanks, will have to study this carefully.
So, if I understand you well then the way I do it now I would need
sqlite3_free?

Not sure it is helpful, but this is the callback procedure as I have it. It
will find a specified string (second argument in SQL)
in the supplied field value (first argument) and produce a string as in the
first argument string, but leaving out all starting
at the start of the second argument string. I know all this can be done
quite simple with the built-in function substr and instr
and that is probably faster, but I just use this as test code:

Sub ClearStartAtStringX(ByVal lPtr_ObjContext As Long, _
ByVal lArgCount As Long, _
ByVal lPtr_ObjSQLite3_Value As Long)

Dim lPtr1 As Long
Dim lPtr2 As Long
Dim lPtr3 As Long
Dim lPtr4 As Long
Dim str As String
Dim str2 As String
Dim lPos As Long
Dim lBytes As Long

'field value to alter
'
10  lPtr1 = MemLong(lPtr_ObjSQLite3_Value) 'copy pointer
20  lPtr2 = sqlite3_value_text(lPtr1)
30  lBytes = sqlite3_value_bytes(lPtr1)
40  If lBytes = 0 Then
50sqlite3_result_null lPtr_ObjContext
60Exit Sub
70  End If
 'produce normal VB Unicode string
80  str = cSQL.PointerToString(lPtr2, CP_UTF8, lBytes, lBytes)

'clear after finding this string
'---
90  lPtr3 = MemLong(lPtr_ObjSQLite3_Value + 4) 'copy pointer
100 lPtr4 = sqlite3_value_text(lPtr3)
110 lBytes = sqlite3_value_bytes(lPtr3)
 'produce normal VB Unicode string
120 str2 = cSQL.PointerToString(lPtr4, CP_UTF8, lBytes, lBytes)
130 lPos = InStr(1, str, str2, vbBinaryCompare) 'VB instr function

'string not found, so return original field string
'-
140 If lPos = 0 Then
150   sqlite3_result_value lPtr_ObjContext, lPtr1
160   Exit Sub
170 End If
180 sqlite3_result_text lPtr_ObjContext, StrPtr(str), _
   (lPos - 2) * 2, SQLITE_TRANSIENT

End Sub


RBS



On Mon, Dec 14, 2015 at 9:00 PM, Igor Tandetnik  wrote:

> On 12/14/2015 3:09 PM, Bart Smissaert wrote:
>
>> It could be either a pointer to sqlite3_value_text of sqlite3_value*
>>
>
> No it can't be. sqlite3_result_text takes a char*, not a sqlite3_value* or
> a const unsigned char*(*)(sqlite3_value*)
>
> If you are saying that you plan to obtain the character pointer by calling
> sqlite3_value_text, then pass that exact pointer to sqlite3_result_text,
> then I would suggest you use sqlite3_result_value instead: it takes
> sqlite3_value* directly. If you insist on round-tripping through
> sqlite3_value_text, then you must pass SQLITE_TRANSIENT for the last
> parameter - the pointer returned by sqlite3_value_text is only guaranteed
> to be valid until the custom function returns.
>
> or it could be a pointer to a locally declared variable
>>
>
> In this case, you would also use SQLITE_TRANSIENT.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] Is rowid the fastest?

2015-12-14 Thread 王庆刚
You said that "You are probably falling into the cache effect trap again. There 
is no point in indexing on the primary key, it only wastes space and CPU cycles
". 
 I do not agree with you. let me tell you why.
 Before I retrieve by index which created for primary key. The speed of 
retrieve by rowid is very stable(for a long time).
 Once I retrieve by index , the stable will broken and the speed is faster than 
its before.
 I think you could try it.







At 2015-12-14 20:58:07, "Hick Gunter"  wrote:
>You are probably falling into the cache effect trap again. There is no point 
>in indexing on the primary key, it only wastes space and CPU cycles
>
>-Urspr?ngliche Nachricht-
>Von: sqlite-users-bounces at mailinglists.sqlite.org 
>[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von ???
>Gesendet: Montag, 14. Dezember 2015 13:54
>An: sqlite-users at mailinglists.sqlite.org
>Betreff: [sqlite] Is rowid the fastest?
>
>hi,
> create a table using the following sql.
> CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
>  So the x is the alias of the rowid. Retrieving records by rowid around twice 
> as fast as other indexs values.
>  Because of the x is the alias of rowid, so retrieving records by x is also 
> as fast as rowid. Is that right?
>
>  In order to testing the efficiency. After I create index on t(x).
>  And this give me a suprise, now I retrieve by x , I found that its is faster 
> than its before.
>  does retrieve by the rowid the fastest method?
>  Has creating index on t(x) anything influence to do with the rowid?
>
>  best regards.
>___
>sqlite-users mailing list
>sqlite-users at 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: hick at 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 at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Is rowid the fastest?

2015-12-14 Thread 王庆刚
hi,
 create a table using the following sql.
 CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
  So the x is the alias of the rowid. Retrieving records by rowid around twice 
as fast as other indexs values.
  Because of the x is the alias of rowid, so retrieving records by x is also as 
fast as rowid. Is that right?

  In order to testing the efficiency. After I create index on t(x).
  And this give me a suprise, now I retrieve by x , I found that its is faster 
than its before.
  does retrieve by the rowid the fastest method? 
  Has creating index on t(x) anything influence to do with the rowid?

  best regards.


[sqlite] Geting the errorcode in Java

2015-12-14 Thread Cecil Westerhof
I have the following code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;


public class CheckProverbsLocked {
private CheckProverbsLocked() {
}

public static void main(String [] args) throws Exception {
Connection   conn;
Statementstmt;

Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:proverbs.sqlite");
stmt  = conn.createStatement();
try {
stmt.executeUpdate("begin immediate");
} catch (SQLException e) {
System.out.println(e.getErrorCode());
System.out.println(e.getMessage());
System.out.println(e.getSQLState());
}
stmt.close();
conn.close();
}

}


?I get the following output when the database is locked:
0
database is locked
null

?I would expect the first one to be 5?. What am I doing wrong?

-- 
Cecil Westerhof


[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

2015-12-14 Thread Igor Tandetnik
On 12/14/2015 7:42 PM, Bart Smissaert wrote:
> Yes, str and str2 are Unicode string, 2 bytes per character.
> lPos counts per character, not byte, so if the string in the database is
> abcde and I want to find the first position
> of d in that string then lPos will be 4.

... and then you pass (lPos-2)*2 == 4 - but the string "abc" is not 4 
bytes long, no matter how you count.

>> You are converting in one direction (SQLite to VB), but not in the other
> I thought SQLite would handle VB Unicode strings to UTF8 strings

It would, if you use the correct API function, so that SQLite knows that 
the string is in fact Unicode.
-- 
Igor Tandetnik



[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

2015-12-14 Thread Bart Smissaert
It could be either a pointer to sqlite3_value_text of sqlite3_value* or it
could be a pointer to a locally declared variable, so that is declared in a
procedure
in a VB6 ActiveX dll. Do I need to run sqlite3_free in the first case and
not in the second case?
If do need to run it how do I do it?
As an argument in sqlite3_result_text or after running sqlite3_result_text?
Do I need to run it with a pointer?

RBS

On Mon, Dec 14, 2015 at 7:27 PM, Igor Tandetnik  wrote:

> On 12/14/2015 2:21 PM, Bart Smissaert wrote:
>
>> Not sure if I need to call sqlite3_free after running sqlite3_result_text
>> or
>> if sqlite3_free should be an argument  (last one) in sqlite3_result_text.
>>
>
> That depends on how the memory was obtained that the second argument
> points to.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


[sqlite] 答复: Very Strange and Interesting Problem

2015-12-14 Thread 王庆刚

https://www.sqlite.org/lang_createtable.html#rowid
The above link websit is very valuable.
Thanks for Quan Yong Zhai.







At 2015-12-14 19:08:59, "Quan Yong Zhai"  wrote:
>https://www.sqlite.org/lang_createtable.html#rowid
>
>???: ???
>: ?2015/?12/?14 18:21
>???: sqlite-users at mailinglists.sqlite.orgmailinglists.sqlite.org>
>??: [sqlite] Very Strange and Interesting Problem
>
>hi, every one.
> Here is a very strange and interesting problem.
> I used the following SQL to create the table teacher.
> CREATE TABLE techer(poiId INTEGER NOT NULL PRIMARY KEY,
>versionId INTEGER NOT NULL,
>regionId INTEGER ,
>postalCode TEXT ,
>phone TEXT ,
>attrBitMask INTEGER ,
>attributeBlob BLOB)
>
> and  then I used the following SQL,
> select rowid,*  from teacher.So the strange and interesting problem 
> happen.
> the name of the rowid column change to poiId, and the name of the poiId 
> become poiId_1. But the two columns have the same values.
>
> Why the rowid change is column name?
> Is the problem reasonable ?  How do you think about this problem? why?
> I  am looking forward to hearing from you.
>
> Best regards.
> WQG
>
>
>
>
>
>
>
>
>___
>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] sqlite3_free needed when calling sqlite3_result_text ?

2015-12-14 Thread Igor Tandetnik
On 12/14/2015 5:46 PM, Bart Smissaert wrote:
> OK, thanks, will have to study this carefully.
> So, if I understand you well then the way I do it now I would need
> sqlite3_free?

First, I don't know how you do it now - you've never described that. 
Second, I have not ever said you needed sqlite3_free; nothing in your 
description of the problem so far suggests you need it.

>  'string not found, so return original field string
>  '-
> 140 If lPos = 0 Then
> 150   sqlite3_result_value lPtr_ObjContext, lPtr1
> 160   Exit Sub
> 170 End If
> 180 sqlite3_result_text lPtr_ObjContext, StrPtr(str), _
> (lPos - 2) * 2, SQLITE_TRANSIENT

To the extent I understand what's going on (I'm not really familiar with 
VB, so I'm taking and educated guess for the most part), the memory 
management part looks OK to me.

However, I have doubts about encoding. Comments seem to suggest str 
points to a Unicode string; also the fact that you multiply lPos by 2. 
But sqlite3_result_text expects UTF-8 string. You are converting in one 
direction (SQLite to VB), but not in the other, as far as I can tell. I 
suspect you need sqlite3_result_text16 instead.

Also lPos-2 looks wrong. Can't the substring be found at lPos == 1 ?
-- 
Igor Tandetnik



[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

2015-12-14 Thread Bart Smissaert
Not sure if I need to call sqlite3_free after running sqlite3_result_text or
if sqlite3_free should be an argument  (last one) in sqlite3_result_text.
Currently I am using SQLITE_TRANSIENT as the last argument, so that is
after the number of bytes, but have feeling I might be doing this wrong.
Thanks for any advice.

RBS


[sqlite] Problem when upgrading from FTS3/4 toFTS5modules(revisited)

2015-12-14 Thread Dan Kennedy
On 12/13/2015 08:05 PM, ajm at zator.com wrote:
>>  Mensaje original 
>> De: Dan Kennedy 
>> Para:  sqlite-users at mailinglists.sqlite.org
>> Fecha:  Fri, 11 Dec 2015 22:54:45 +0700
>> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 
>> toFTS5modules(revisited)
>>
> 1a.- Delete the previous table.
> DROP TABLE IF EXIST ftsm
>
> This scheme work ok with the FST3/4 modules, but compiling with FTS5 
> gives an erroro in 1a:  "database disk image is malformed".
>
> Note that in previous attemps I believed that the problem was into try to 
> delete a ftsm table build with the previous modules, but the error happen 
> when trying delete a table build with the FTS5 module.
>
> ...
>>> Some clues?
>> Not really sure why it might fail there. Can you post the entire
>> database schema (results of "SELECT * FROM sqlite_master" or the output
>> of the .schema shell tool command)?
>>
> Dan:
>
> Here is the schema:
 That all looks Ok.

 If you use the shell tool to execute the "DROP TABLE IF EXISTS ftsm"
 command does it report an error as well?

>>> Dan:
>>>
>>> Surprisingly, the shell does not complain when using the same query, and 
>>> indeed, drop the table.
>>>
>>> Yeah, I also believe that the problem is in my roof, although that code has 
>>> been behaving Ok from ages with the FTS3/4 modules (only changes some 
>>> directives in other places of the code).
>>>
>>> Any way, when running again the code when the table has been previously 
>>> erased (with the shell), the routine works without complaint (executes the 
>>> query correctly), and despite the warning, the rest of the app behaves 
>>> correctly and the queries related with the search works fine.
>>>
>>> When the table exist, the debugger shows that sqlite3_prepare_v2() ends Ok, 
>>> but sqlite3_step() ends with error 11.
>> Are you able to compile a debugging build of SQLite and set a breakpoint
>> in sqlite3CorruptError()?
>>
>> Assuming the breakpoint is triggered within the DROP TABLE IF EXISTS
>> statement (pretty good chance), the stack trace will tell us more about
>> the form of corruption SQLite thinks it has found.
>>
> Dan:
>
> I managed a breakpoint int the requested function, and can tell that it has 
> been called twice inside the sqlite3_step() function before it returns.
>
> Here you have the call's stack:
>
> First call of sqlite3CorrupError()  lineno == 56209
>
> sqlite3CorruptError(int lineno) Line 133961
> decodeFlags(MemPage * pPage, int flagByte) Line 56209
> btreeInitPage(MemPage * pPage) Line 56251
> getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, 
> BtCursor * pCur, int bReadOnly) Line 56495
> clearDatabasePage(BtShared * pBt, unsigned int pgno, int freePageFlag, int * 
> pnChange) Line 62907
> sqlite3BtreeClearTable(Btree * p, int iTable, int * pnChange) Line 62970
> btreeDropTable(Btree * p, unsigned int iTable, int * piMoved) Line 63028
> sqlite3BtreeDropTable(Btree * p, int iTable, int * piMoved) Line 63111
> sqlite3VdbeExec(Vdbe * p) Line 77954
> sqlite3Step(Vdbe * p) Line 71546
> sqlite3_step(sqlite3_stmt * pStmt) Line 71608
> sqlite3_exec(sqlite3 * db, const char * zSql, int (void *, int, char * *, 
> char * *) * xCallback, void * pArg, char * * pzErrMsg) Line 103955
> fts5ExecPrintf(sqlite3 * db, char * * pzErr, const char * zFormat, ...) Line 
> 180863
> sqlite3Fts5DropAll(Fts5Config * pConfig) Line 180876
> fts5DestroyMethod(sqlite3_vtab * pVtab) Line 178532
> sqlite3VtabCallDestroy(sqlite3 * db, int iDb, const char * zTab) Line 117587
> sqlite3VdbeExec(Vdbe * p) Line 79084
> sqlite3Step(Vdbe * p) Line 71546
> sqlite3_step(sqlite3_stmt * pStmt) Line 71608
>
> Secon call of sqlite3CorrupError() lineno == 56251
>
> sqlite3CorruptError(int lineno) Line 133961
> btreeInitPage(MemPage * pPage) Line 56251
> getAndInitPage(BtShared * pBt, unsigned int pgno, MemPage * * ppPage, 
> BtCursor * pCur, int bReadOnly) Line 56495
> ...
> Rest the same...
>
> Note that the line numbers can be slightly greater than the ones in the 
> original file (SQLite 3.9.1) because the inclusion of some comments.
>
> As I can reproduce the problem as needed, In case of interest, I can try to 
> provide any intermediate value inside the stack.

So that looks like database corruption, except we don't think the 
database is actually corrupt as the DROP TABLE statement did not fail 
when run in the shell tool. So perhaps heap-corruption has caused the 
in-memory cache to become corrupt.

Can you run the whole thing under valgrind?

Dan.




[sqlite] 答复: Very Strange and Interesting Problem

2015-12-14 Thread Quan Yong Zhai
https://www.sqlite.org/lang_createtable.html#rowid

???: ???
: ?2015/?12/?14 18:21
???: sqlite-users at mailinglists.sqlite.org
??: [sqlite] Very Strange and Interesting Problem

hi, every one.
 Here is a very strange and interesting problem.
 I used the following SQL to create the table teacher.
 CREATE TABLE techer(poiId INTEGER NOT NULL PRIMARY KEY,
versionId INTEGER NOT NULL,
regionId INTEGER ,
postalCode TEXT ,
phone TEXT ,
attrBitMask INTEGER ,
attributeBlob BLOB)

 and  then I used the following SQL,
 select rowid,*  from teacher.So the strange and interesting problem 
happen.
 the name of the rowid column change to poiId, and the name of the poiId 
become poiId_1. But the two columns have the same values.

 Why the rowid change is column name?
 Is the problem reasonable ?  How do you think about this problem? why?
 I  am looking forward to hearing from you.

 Best regards.
 WQG








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


[sqlite] Very Strange and Interesting Problem

2015-12-14 Thread 王庆刚
hi, every one.
 Here is a very strange and interesting problem.
 I used the following SQL to create the table teacher.
 CREATE TABLE techer(poiId INTEGER NOT NULL PRIMARY KEY,
versionId INTEGER NOT NULL,
regionId INTEGER ,
postalCode TEXT ,
phone TEXT ,
attrBitMask INTEGER ,
attributeBlob BLOB)

 and  then I used the following SQL,
 select rowid,*  from teacher.So the strange and interesting problem 
happen.
 the name of the rowid column change to poiId, and the name of the poiId 
become poiId_1. But the two columns have the same values.

 Why the rowid change is column name?
 Is the problem reasonable ?  How do you think about this problem? why?
 I  am looking forward to hearing from you.

 Best regards.
 WQG










[sqlite] Why SQLITE_BUSY?

2015-12-14 Thread sanhua.zh
I queue all my db operation into one thread with single sqlite conn. neither 
multi-thread nor multi-process operation happened.But some SQLITE_BUSY error 
code still be catched.
I can not re-produce this error code indeveloping environment,because it happen 
in alow probability. I only catch this error report online.


So how did it happen?
I guess that, when WAL reach the checkpoint, sqlite will write the data back to 
original db file in background thread. So writing will be busy at this time. 
But I?m not sure.
I hope that you will not stint your criticism


[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

2015-12-14 Thread Igor Tandetnik
On 12/14/2015 3:09 PM, Bart Smissaert wrote:
> It could be either a pointer to sqlite3_value_text of sqlite3_value*

No it can't be. sqlite3_result_text takes a char*, not a sqlite3_value* 
or a const unsigned char*(*)(sqlite3_value*)

If you are saying that you plan to obtain the character pointer by 
calling sqlite3_value_text, then pass that exact pointer to 
sqlite3_result_text, then I would suggest you use sqlite3_result_value 
instead: it takes sqlite3_value* directly. If you insist on 
round-tripping through sqlite3_value_text, then you must pass 
SQLITE_TRANSIENT for the last parameter - the pointer returned by 
sqlite3_value_text is only guaranteed to be valid until the custom 
function returns.

> or it could be a pointer to a locally declared variable

In this case, you would also use SQLITE_TRANSIENT.
-- 
Igor Tandetnik



[sqlite] Is rowid the fastest?

2015-12-14 Thread Clemens Ladisch
??? wrote:
>  CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
>
>   In order to testing the efficiency. After I create index on t(x).
>   And this give me a suprise, now I retrieve by x , I found that its is 
> faster than its before.

An index entry is smaller than a table row, so if you want to read
only the values in the x column, searching in the index is likely to
be faster then searching the rowid in the table, because less I/O
needs to be done.  This is called a covering index.


Regards,
Clemens


[sqlite] Locked database

2015-12-14 Thread Clemens Ladisch
Cecil Westerhof wrote:
> sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null
> errorCode="${?}"
> if [[ "${errorCode}" -eq 5 ]] ; then
> printf "${DATABASE} is locked\n"
>
> I saw that when it is locked I get back a 5. Is this always the case?

sqlite3 returns the SQLite error code, and 5 indeed is SQLITE_BUSY.
(Non-SQL errors result in 1, which would be the same as SQLITE_ERROR.)


Regards,
Clemens


[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

2015-12-14 Thread Igor Tandetnik
On 12/14/2015 2:21 PM, Bart Smissaert wrote:
> Not sure if I need to call sqlite3_free after running sqlite3_result_text or
> if sqlite3_free should be an argument  (last one) in sqlite3_result_text.

That depends on how the memory was obtained that the second argument 
points to.
-- 
Igor Tandetnik



[sqlite] Is rowid the fastest?

2015-12-14 Thread Hick Gunter
Does it revert back to slower speed after dropping the index?
Can you compare the EXPLAIN output produced with and without the index?

There is no difference on my machine (Version 3.7.14); if yours behaves the 
same way then whatever changes speed is definitely not because SQLite is doing 
something differently.

asql> create temp table t (x integer primary key asc, y,z);
asql> .explain
asql> explain select * from t where rowid=15;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Integer151 000  NULL
2 Goto   0 12000  NULL
3 OpenRead   0 2 1 3  00  t
4 MustBeInt  1 10000  NULL
5 NotExists  0 10100  pk
6 Rowid  0 3 000  NULL
7 Column 0 1 400  t.y
8 Column 0 2 500  t.z
9 ResultRow  3 3 000  NULL
10Close  0 0 000  NULL
11Halt   0 0 000  NULL
12Transaction1 0 000  NULL
13VerifyCookie   1 1 000  NULL
14TableLock  1 2 0 t  00  NULL
15Goto   0 3 000  NULL
asql> explain select * from t where x=15;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Integer151 000  NULL
2 Goto   0 12000  NULL
3 OpenRead   0 2 1 3  00  t
4 MustBeInt  1 10000  NULL
5 NotExists  0 10100  pk
6 Rowid  0 3 000  NULL
7 Column 0 1 400  t.y
8 Column 0 2 500  t.z
9 ResultRow  3 3 000  NULL
10Close  0 0 000  NULL
11Halt   0 0 000  NULL
12Transaction1 0 000  NULL
13VerifyCookie   1 1 000  NULL
14TableLock  1 2 0 t  00  NULL
15Goto   0 3 000  NULL
asql> create index t_x on t(x);
asql> explain select * from t where x=15;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Integer151 000  NULL
2 Goto   0 12000  NULL
3 OpenRead   0 2 1 3  00  t
4 MustBeInt  1 10000  NULL
5 NotExists  0 10100  pk
6 Rowid  0 3 000  NULL
7 Column 0 1 400  t.y
8 Column 0 2 500  t.z
9 ResultRow  3 3 000  NULL
10Close  0 0 000  NULL
11Halt   0 0 000  NULL
12Transaction1 0 000  NULL
13VerifyCookie   1 2 000  NULL
14TableLock  1 2 0 t  00  NULL
15Goto   0 3 000  NULL

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von ???
Gesendet: Montag, 14. Dezember 2015 14:06
An: SQLite mailing list
Betreff: [sqlite] Is rowid the fastest?

You said that "You are probably falling into the cache effect trap again. There 
is no point in indexing on the primary key, it only wastes space and CPU cycles 
".
 I do not agree with you. let me tell you why.
 Before I retrieve by index which created for primary key. The speed of 
retrieve by rowid is very stable(for a long time).
 Once I retrieve by index , the stable will broken and the speed is faster than 
its before.
 I think you could try it.





___
 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: hick at scigames.at

This communication (including any attachments) is intended for the use of 

[sqlite] Locked database

2015-12-14 Thread Cecil Westerhof
2015-12-14 12:40 GMT+01:00 Clemens Ladisch :

> Cecil Westerhof wrote:
> > I have a crontab job which uses a SQLite database. Sometimes this
> database
> > is locked because I used SQLite DB Browser, but did not Write or Revert
> > Changes. It looks like that when a database is locked there is a file
> with
> > the database name with -journal appended to it. Can I count on this?
>
> The -journal file exists when there is some changed data that might
> need to be rolled back.  It is possible for the DB to be locked before
> some changed data is actually written.
>
> In WAL mode, there is no journal.  (And if your job is read only, it
> then would not be blocked by concurrent writes.)
>

?No, I need to write also.?




> > Then I could write a script to warn me about the lock.
>
> You could use SQLite to check whether the DB is locked:
>
>   if ! sqlite3 my.db "begin immediate"; then
> echo "is locked"
>   fi
>

?Thanks, I improved a little on it:
sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null
errorCode="${?}"
if [[ "${errorCode}" -eq 5 ]] ; then
printf "${DATABASE} is locked\n"
elif [[ "${errorCode}" -ne 0 ]] ; then
printf "Error ${errorCode} while accessing ${DATABASE}\n"
else
printf "${DATABASE} is free\n"
fi

I saw that when it is locked I get back a 5. Is this always the case? In
this way I can see the difference between a lock and another error.

?If this code is correct I rewrite it for crontab. ;-)?

-- 
Cecil Westerhof


[sqlite] Is rowid the fastest?

2015-12-14 Thread Hick Gunter
You are probably falling into the cache effect trap again. There is no point in 
indexing on the primary key, it only wastes space and CPU cycles

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von ???
Gesendet: Montag, 14. Dezember 2015 13:54
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Is rowid the fastest?

hi,
 create a table using the following sql.
 CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
  So the x is the alias of the rowid. Retrieving records by rowid around twice 
as fast as other indexs values.
  Because of the x is the alias of rowid, so retrieving records by x is also as 
fast as rowid. Is that right?

  In order to testing the efficiency. After I create index on t(x).
  And this give me a suprise, now I retrieve by x , I found that its is faster 
than its before.
  does retrieve by the rowid the fastest method?
  Has creating index on t(x) anything influence to do with the rowid?

  best regards.
___
sqlite-users mailing list
sqlite-users at 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: hick at 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] Locked database

2015-12-14 Thread Clemens Ladisch
Cecil Westerhof wrote:
> I have a crontab job which uses a SQLite database. Sometimes this database
> is locked because I used SQLite DB Browser, but did not Write or Revert
> Changes. It looks like that when a database is locked there is a file with
> the database name with -journal appended to it. Can I count on this?

The -journal file exists when there is some changed data that might
need to be rolled back.  It is possible for the DB to be locked before
some changed data is actually written.

In WAL mode, there is no journal.  (And if your job is read only, it
then would not be blocked by concurrent writes.)

> Then I could write a script to warn me about the lock.

You could use SQLite to check whether the DB is locked:

  if ! sqlite3 my.db "begin immediate"; then
echo "is locked"
  fi


Regards,
Clemens


[sqlite] Very Strange and Interesting Problem

2015-12-14 Thread Stephan Beal
On Mon, Dec 14, 2015 at 11:47 AM, Stephan Beal 
wrote:

> On Mon, Dec 14, 2015 at 11:21 AM, ??? <2004wqg2008 at 163.com> wrote:
>
>> hi, every one.
>>  Here is a very strange and interesting problem.
>>  I used the following SQL to create the table teacher.
>>  CREATE TABLE techer(poiId INTEGER NOT NULL PRIMARY KEY,...
>
>

> 1) if you do not alias column names using "AS" then the name you get from
>> the db driver is UNDEFINED. It might call all of them "foo" and still be
>> legal. The ONLY way to guaranty the names is to use "AS" to give them an
>> explicit name.
>>
>
>

> 2) i'm guessing that you are using an abstraction layer which is changing
> the returned name of your rowid.
>

i overlooked that you explicitly declared a rowid replacement. That is the
reason for what you are seeing. The link from Quan explains it in more
detail.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"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] Locked database

2015-12-14 Thread Cecil Westerhof
I have a crontab job which uses a SQLite database. Sometimes this database
is locked because I used SQLite DB Browser, but did not Write or Revert
Changes. It looks like that when a database is locked there is a file with
the database name with -journal appended to it. Can I count on this? Then I
could write a script to warn me about the lock.

-- 
Cecil Westerhof


[sqlite] Very Strange and Interesting Problem

2015-12-14 Thread Stephan Beal
On Mon, Dec 14, 2015 at 11:21 AM, ??? <2004wqg2008 at 163.com> wrote:

> hi, every one.
>  Here is a very strange and interesting problem.
>  I used the following SQL to create the table teacher.
>  CREATE TABLE techer(poiId INTEGER NOT NULL PRIMARY KEY,
> versionId INTEGER NOT NULL,
> regionId INTEGER ,
> postalCode TEXT ,
> phone TEXT ,
> attrBitMask INTEGER ,
> attributeBlob BLOB)
>
>  and  then I used the following SQL,
>  select rowid,*  from teacher.So the strange and interesting
> problem happen.
>  the name of the rowid column change to poiId, and the name of the
> poiId become poiId_1. But the two columns have the same values.
>
>  Why the rowid change is column name?
>  Is the problem reasonable ?  How do you think about this problem? why?
>  I  am looking forward to hearing from you.
>

Two answers come to mind:

1) if you do not alias column names using "AS" then the name you get from
the db driver is UNDEFINED. It might call all of them "foo" and still be
legal. The ONLY way to guaranty the names is to use "AS" to give them an
explicit name.

2) i'm guessing that you are using an abstraction layer which is changing
the returned name of your rowid.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"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] non-integer rowid/document id

2015-12-14 Thread jer...@copiousfreetime.org
Hi all,

I'm working with FTS5 and I'd like to guarantee that a particular document is
indexed only once. I have what I consider to be a document id, but it is not an
integer value its a hexadecimal string, think GUID/md5/sha1. Since the fts5
rowid column is an integer, it appears I'll need to create a mapping from my
document id to an fts5 rowid.

I'm thinking the best method for me to resolve this would to just use an
external content table with triggers to update FTS5 table. Pretty much exactly
like https://sqlite.org/fts5.html#section_4_4_2 and use triggers to keep the
FTS5 table in sync with the external content table.

If I'm going to have to use an external table to create a rowid for the
fts5 table, I might as well use use the external content table.

Any additional thoughts on this from the more knowledgeable?

enjoy,

-jeremy

--?

Jeremy Hinegarnder
Copious Free Time, LLC


[sqlite] Very Strange and Interesting Problem

2015-12-14 Thread Simon Slavin

On 14 Dec 2015, at 10:21am, ??? <2004wqg2008 at 163.com> wrote:

> Here is a very strange and interesting problem.

Your problem with renaming is not caused by SQLite.

How are you talking to SQLite ?  Have you written a C program which calls the 
SQLite API ?  Are you using the command line program supplied by the SQLite 
team ?



Are you programming in some other language ?  Are you using a program someone 
else wrote ?

Simon.


[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

2015-12-14 Thread Olivier Mascia
> Keith:
> 
> The documentation for sqlite3_last_insert_rowid clearly states that it 
> returns the rowid last inserted on the connection, as does the documentation 
> for sqlite3_changes.  While I agree that it may very well be possible to 
> maintain the data by statement, that is not what the current functions as 
> documented do.
> 
> Perhaps you can/might want to request an enhancement?

Exactly.  Which brings us to my very initial post:

> Why isn't there some:
> 
>   sqlite3_int64 sqlite3_stmt_last_insert_rowid(sqlite3_stmt*);
>   int sqlite3_stmt_changes(sqlite3_stmt*);
> 
> in addition to these:
> 
>   sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*);
>   int sqlite3_changes(sqlite3*);

I was asking for experience from SQLite users, before making it a feature 
request.
I surely wouldn't expect the current functions (taking sqlite3*) to return 
anything else than information maintained by connection. But I would have 
valuable usage of the same functions (taking sqlite3_stmt*), returning such 
information maintained by statement.

I'll check what are the proper way to draw a documented request for enhancement 
for SQLite. We will anyway surely buy some yearly technical support, or at the 
very least maintenance subscription, which I guess will streamline the 
introduction of enhancement requests.

--
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20151214/7b567d13/attachment.pgp>


[sqlite] SELECT CAST('' AS INTEGER) returns zero instead of null

2015-12-14 Thread Anthony Damico
hi, sql standard says to strip whitespace and then convert.  "" coercing to
zero instead of NULL strikes me as very odd..  thanks



some code here-

https://github.com/rstats-db/RSQLite/issues/127


[sqlite] Bug with DATETIME('localtime')

2015-12-14 Thread Hick Gunter
>...
>We need a metric calendar. I propose redefining the second so that a day is
>100,000 seconds long... ;)
>
>--
>Scott Robison

And while we are already redefining the fundamental constants of measuring, we 
could redefine the meter to be exactly three feet and the kilogram to be 
exactly two pounds. I have no idea what havoc that would cause for gallons and 
litres, but I am quite sure musicians will be thrilled to tune their 
instruments to a standard of 380,16 "new hertz" ;)


___
 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: hick at 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.