[sqlite] Tricky SQL

[Bart Smissaert]

Trying to get my head around a tricky SQL and maybe somebody can help me
out here:

CREATE TABLE TEST(
[ID] INTEGER,
[DATE] TEXT,
[VALUE] REAL
)

Example data:

ID  DATEVALUE
-
1   2015-01-01  14
1   2015-02-01  16
1   2015-03-01  11
1   2015-04-01  2
1   2015-05-01  12
1   2015-06-01  14
1   2015-07-01  30
1   2015-08-01  15
2   2015-01-01  6
2   2015-02-01  11
2   2015-03-01  14
2   2015-04-01  14
2   2015-05-01  50
2   2015-06-01  14
2   2015-07-01  14
2   2015-08-01  14

Now, what I need is for every row the count of preceding rows where
the ID is the same as the ID of the current row, the date is less than the
date of the current row and the value is >= 10 and <= 20. If the value is
outside
this range then it should stop the count for that current row. If the value
of the current row is outside this 10 to 20 range than the result is 0 and
there
is no need to count preceding rows.

This is what the result should be:

ID  DATEVALUE  COUNT_PRECEDING_IN_RANGE
-
1   2015-01-01   14  0
1   2015-02-01   16  1
1   2015-03-01   11  2
1   2015-04-01   20
1   2015-05-01   12  0
1   2015-06-01   14  1
1   2015-07-01   30  0
1   2015-08-01   15  0
2   2015-01-01   60
2   2015-02-01   11  0
2   2015-03-01   14  1
2   2015-04-01   14  2
2   2015-05-01   50  0
2   2015-06-01   14  0
2   2015-07-01   14  1
2   2015-08-01   14  2

Hopefully this is clear enough. Note that preceding is a defined by date
and that
the above dates are in the format -mm-dd.


RBS

[sqlite] SQLITE vs. OSX mmap == inevitable catalog corruption?

[Deon Brewis]

We found that using SQLITE memory mapped I/O (mmap_size > 0) in OSX isn't 
usable in any way shape or form. It will inevitably lead to catalog corruption 
if you hard-reboot OSX, even without the database or application open.

We've tried FULLSYNC and F_FULLFSYNC, but it makes no difference.

Repro steps:
a) Run our application and write stuff to the database
b) Close our app & wait for the .wal file to disappear AND the app to disappear 
from activity monitor
c) Wait another 2 minutes
d) Copy your SQLITE db file to a network share
e) Hard reboot OSX
f) After the reboot, copy the same SQLITE db to a second network share
g) File compare (d) vs. (f)

Observe: The files in (c) and (e) are virtually never identical. NOTE: There is 
no application or SQLITE is involved here. The app is closed. It (should have) 
checkpointed and flushed properly (FULLSYNC + FULLFSYNC). However, the MAC 
doesn't seem to write the database to disk. So once you hard-reboot you don't 
have the same file anymore. SQLITE detects this as catalog corruption around 
25% of the time, but a file compare shows differences pretty much 100% of the 
time. Not just benign differences in unused pages - the header is more often 
than not different as well.

Without the Hard reboot our database always survives a close. You can 
gracefully shutdown the application, pkill it, force terminate, crash it, soft 
reboot - it all survives. However, once you hard reboot OSX - even AFTER the 
app is closed - it has a very high probability of corrupting our database.  
I've seen a worse case scenario where the hard reboot followed an app graceful 
shutdown by 12 hours, and it still corrupted the database.

This is so easy to reproduce I'm not sure why this isn't reported as a 
large-scale problem? It also only reproduces on OSX (both El Capitan and 
Mavericks) - Android, iOS, PC all work fine.


Anyway, it's not a big deal for us to set mmap_size to 0 to work around this.

The big problem I have however is the .wal.  We use SQLITE from multiple 
threads, and as such it's using shared memory to read/write the .wal. However, 
if persisting memory mapped files on OSX is so unreliable, then how can the 
.wal be expected to survive a hard reboot... So I'd also like to have a way to 
not use memory mapped I/O for .wal files, but I don't think there is a way 
unless I change the architecture of my app to have single-threaded access to 
SQLITE? Or is there another way?

- Deon

[sqlite] Retrieving the table info fails

[R Smith]

On 2015/11/16 7:59 PM, Igor Korot wrote:
> Stephan,
>
> On Mon, Nov 16, 2015 at 12:42 PM, Stephan Beal  
> wrote:
>> On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot  wrote:
>>
>>> The variables referenced are defined as "std::string" and the code is in
>>> C++.
>>>
>> the std::string(char const *) constructor does  not, last time i checked,
>> accept a NULL value. You will need to pass it "" in that case.
>>
>> [stephan at host:~/tmp]$ cat foo.cpp
>> #include 
>>
>> int main(){
>>  std::string s(0);
>>  return 0;
>> }
>>
>> [stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++
>> [stephan at host:~/tmp]$ ./foo
>> terminate called after throwing an instance of 'std::logic_error'
>>what():  basic_string::_S_construct null not valid
>> Aborted
> OK, it looks like the value is NULL instead of "".
> I will change the code accordingly.
>
> BTW, are only name, type and pk fields are guaranteed to have a value?

Nothing is guaranteed to have a value unless created with NOT NULL in 
the field specification in the CREATE TABLE schema SQL.

Usually (in most DBs) a Primary key is guaranteed to not have NULL 
values, but in SQLite it is permissible if the PK is not specifically 
stated to be NOT NULL.

[sqlite] Retrieving the table info fails

[Simon Slavin]

On 16 Nov 2015, at 9:09pm, Igor Korot  wrote:

>  1|name|varchar(100)|0  |  |0

Note that this does not tell you anything about the affinity of the column, or 
the types of the values in it.  SQLite doesn't even have a varchar type.

> "dflt_value" field may or may not have a value.


It might be interesting to know what type the value in the above line has.

Simon.

[sqlite] Retrieving the table info fails

[Simon Slavin]

On 16 Nov 2015, at 7:40pm, Igor Korot  wrote:

> But if I issue this PRAGMA command the field name, field type and the PK
> are guaranteed to have some values, right?

No.  Create a field without a declared type and use that as your primary key.

> You can't create a field without a name

Correct.  At least I think so.

> or the type

Wrong.

178:~ simon$ sqlite3 ~/Desktop/fred.sql
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> CREATE TABLE myTable (fred PRIMARY KEY);
sqlite> PRAGMA table_info(myTable);
0|fred||0||1
sqlite> PRAGMA index_list(myTable);
0|sqlite_autoindex_myTable_1|1|pk|0
sqlite> PRAGMA index_info(sqlite_autoindex_myTable_1);
0|0|fred
sqlite> PRAGMA index_xinfo(sqlite_autoindex_myTable_1);
0|0|fred|0|BINARY|1
1|-1||0|BINARY|0
sqlite> 

I'd like to emphasise something Scott Hess wrote.  There is considerable leeway 
in the documentation for PRAGMAs and things can change from one version of 
SQLite to another.  Pragmas like this are for use only when you don't know the 
answers.  For example for database management software which has to operate on 
any SQLite database.

Simon.

[sqlite] Retrieving the table info fails

[Simon Slavin]

On 16 Nov 2015, at 5:51pm, Igor Korot  wrote:

> It looks like I falsely assumed that it will return an empty string instead.
> Guess I was wrong.

The empty string is a perfectly legitimate default value for a column.  And 
it's not the same as NULL, a different perfectly legitimate default value for a 
column.  These two do not do the same thing:

CREATE TABLE myTable (fred DEFAULT NULL);
CREATE TABLE myTable (fred DEFAULT '');

> Also, are you saying that if I have a integer field with the default value of 
> 1,
> I will not be able to retrieve it with sqliteColumnText()?

Correct.  These two do not do the same thing:

CREATE TABLE myTable (fred DEFAULT 1);
CREATE TABLE myTable (fred DEFAULT '1');

Simon.

[sqlite] Tricky SQL

[Igor Tandetnik]

On 11/16/2015 6:37 PM, Bart Smissaert wrote:
> Now, what I need is for every row the count of preceding rows where
> the ID is the same as the ID of the current row, the date is less than the
> date of the current row and the value is >= 10 and <= 20. If the value is
> outside
> this range then it should stop the count for that current row. If the value
> of the current row is outside this 10 to 20 range than the result is 0 and
> there
> is no need to count preceding rows.

Personally, I'd do it in application code. It can be done in single 
pass. If you insist on pure SQL solution, then something like this:

select ID, DATE, VALUE,
(select count(*) from TEST prev
  where prev.ID = T.ID and prev.DATE < T.DATE and prev.DATE >
coalesce((select max(DATE) from TEST boundary
  where boundary.ID = T.ID and boundary.DATE <= T.DATE
  and not(boundary.VALUE between 10 and 20)), '')
) as COUNT_PRECEDING_IN_RANGE
from TEST T;

Performance will likely be, shall we say, less than stellar.
-- 
Igor Tandetnik

[sqlite] Retrieving the table info fails

[Stephan Beal]

On Mon, Nov 16, 2015 at 6:42 PM, Stephan Beal  wrote:

> On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot  wrote:
>
>> The variables referenced are defined as "std::string" and the code is in
>> C++.
>>
>
> the std::string(char const *) constructor does  not, last time i checked,
> accept a NULL value. You will need to pass it "" in that case.
>
> [stephan at host:~/tmp]$ cat foo.cpp
> #include 
>
> int main(){
> std::string s(0);
> return 0;
> }
>

Minor clarification: what you're doing is using the copy constructor, which
also doesn't like NULL:

[stephan at host:~/tmp]$ cat foo.cpp
int main(){
//std::string s(0);
std::string s = 0;
return 0;
}

[stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++

[stephan at host:~/tmp]$ ./foo
terminate called after throwing an instance of 'std::logic_error'
  what():  basic_string::_S_construct null not valid
Aborted


-- 
- 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] Are columns declared as 'INTEGER PRIMARY KEY' guaranteed not to change except from UPDATE statements?

[Keith Medcalf]

> Any column declared as 'INTEGER PRIMARY KEY' is said to be an alias for
> the internal 'rowid' column, and this
>  really-need-to-know/>
> proves
> that 'rowid' can change after a 'vacuum' statement is issued. Merely
> replaying the steps in the linked post, but declaring an additional column
> as 'INTEGER PRIMARY KEY' shows that even after 'vacuum' both the rowid and
> the declared primary key remain unchanged.

> My main question is: is an 'INTEGER PRIMARY KEY' column guaranteed not to
> change, except as a consequence of ordinary UPDATE statements? (And in
> particular, not as a consequence of a 'vacuum' statement.)
> 
> I'd also thank further explanation as to why the 'vacuum' statement
> doesn't change 'rowid's when an explicit 'INTEGER PRIMARY KEY' is declared.

The implicit rowid is merely exposing the actual primary key (row number in the 
btree storing the table).  Because it is not declared in the table definition, 
it does not contain a value that is relevant.  The fact that there is a rowid 
is merely a side-effect of the fact that anytime you store data, you have to 
have a rowid.

If you explicitly declare an INTEGER PRIMARY KEY, then this data is used for 
the rowid (since the table has to have a UNIQUE INTEGER rowid, the declaration 
merely assigns "meaning" to the rowid which needs to exist anyway.

In other words, when you

create table x (y text);

the only "data" in the table is the text field y.  That it has to have a rowid 
is immaterial.

Contrast to

create table x ( x integer primary key, y text);

where you have now "related" the value of x to a value of y.

Think of it like a spreadsheet.  In the first case, you have not related the 
value "y" to the row it is on.  In the second case you have -- the row number 
is just called "x" and since x and y are related, the value of x cannot change 
except explicitly.

[sqlite] Retrieving the table info fails

[Stephan Beal]

On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot  wrote:

> The variables referenced are defined as "std::string" and the code is in
> C++.
>

the std::string(char const *) constructor does  not, last time i checked,
accept a NULL value. You will need to pass it "" in that case.

[stephan at host:~/tmp]$ cat foo.cpp
#include 

int main(){
std::string s(0);
return 0;
}

[stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++
[stephan at host:~/tmp]$ ./foo
terminate called after throwing an instance of 'std::logic_error'
  what():  basic_string::_S_construct null not valid
Aborted

-- 
- 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] Retrieving the table info fails

[Igor Tandetnik]

On 11/16/2015 5:26 PM, Igor Korot wrote:
> Do you have any idea what would be the affinity if I do this:
>
> CREATE TABLE test( field1 PRIMARY KEY, field2);
>
> for both field1 and field2?

 From the aforementioned documentation article:
If the declared type for a column contains the string "BLOB" or *if no 
type is specified* then the column has affinity BLOB.

Emphasis mine.

> Again I would guess TEXT, but this is just my guess...

May I respectfully suggest that, in place of guessing, you might want to 
consider reading the fine manual? Starting from the link I humbly 
offered, perchance?
-- 
Igor Tandetnik

[sqlite] SQLITE vs. OSX mmap == inevitable catalog corruption?

[Richard Hipp]

On 11/16/15, Deon Brewis  wrote:
> We found that using SQLITE memory mapped I/O (mmap_size > 0) in OSX isn't
> usable in any way shape or form. It will inevitably lead to catalog
> corruption if you hard-reboot OSX, even without the database or application
> open.
>
> We've tried FULLSYNC and F_FULLFSYNC, but it makes no difference.
>
> Repro steps:
> a) Run our application and write stuff to the database
> b) Close our app & wait for the .wal file to disappear AND the app to
> disappear from activity monitor
> c) Wait another 2 minutes
> d) Copy your SQLITE db file to a network share
> e) Hard reboot OSX
> f) After the reboot, copy the same SQLITE db to a second network share
> g) File compare (d) vs. (f)
>
> Observe: The files in (c) and (e) are virtually never identical. NOTE: There
> is no application or SQLITE is involved here. The app is closed. It (should
> have) checkpointed and flushed properly (FULLSYNC + FULLFSYNC). However, the
> MAC doesn't seem to write the database to disk. So once you hard-reboot you
> don't have the same file anymore. SQLITE detects this as catalog corruption
> around 25% of the time, but a file compare shows differences pretty much
> 100% of the time. Not just benign differences in unused pages - the header
> is more often than not different as well.
>
> Without the Hard reboot our database always survives a close. You can
> gracefully shutdown the application, pkill it, force terminate, crash it,
> soft reboot - it all survives. However, once you hard reboot OSX - even
> AFTER the app is closed - it has a very high probability of corrupting our
> database.  I've seen a worse case scenario where the hard reboot followed an
> app graceful shutdown by 12 hours, and it still corrupted the database.
>
> This is so easy to reproduce I'm not sure why this isn't reported as a
> large-scale problem? It also only reproduces on OSX (both El Capitan and
> Mavericks) - Android, iOS, PC all work fine.
>
>
> Anyway, it's not a big deal for us to set mmap_size to 0 to work around
> this.
>
> The big problem I have however is the .wal.  We use SQLITE from multiple
> threads, and as such it's using shared memory to read/write the .wal.

No.  It uses shared memory for the ".shm" file, which is only a
performance optimization and is not used for recovery.  The ".wal"
file is written using write() or pwrite().


> However, if persisting memory mapped files on OSX is so unreliable, then how
> can the .wal be expected to survive a hard reboot... So I'd also like to
> have a way to not use memory mapped I/O for .wal files, but I don't think
> there is a way unless I change the architecture of my app to have
> single-threaded access to SQLITE? Or is there another way?

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Retrieving the table info fails

[Igor Korot]

Igor,

On Mon, Nov 16, 2015 at 4:50 PM, Igor Tandetnik  wrote:
> On 11/16/2015 4:20 PM, Simon Slavin wrote:
>>
>>
>> On 16 Nov 2015, at 9:09pm, Igor Korot  wrote:
>>
>>>   1|name|varchar(100)|0  |  |0
>>
>>
>> Note that this does not tell you anything about the affinity of the column
>
>
> Does too. Column affinity is deduced from declared type, following the rules
> described here: http://www.sqlite.org/datatype3.html#affname
>
>> SQLite doesn't even have a varchar type.
>
>
> ... but it has TEXT column affinity, which "varchar" indicates.

Do you have any idea what would be the affinity if I do this:

CREATE TABLE test( field1 PRIMARY KEY, field2);

for both field1 and field2?

Again I would guess TEXT, but this is just my guess...

Thank you.

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

[sqlite] Retrieving the table info fails

[Igor Tandetnik]

On 11/16/2015 4:20 PM, Simon Slavin wrote:
>
> On 16 Nov 2015, at 9:09pm, Igor Korot  wrote:
>
>>   1|name|varchar(100)|0  |  |0
>
> Note that this does not tell you anything about the affinity of the column

Does too. Column affinity is deduced from declared type, following the 
rules described here: http://www.sqlite.org/datatype3.html#affname

> SQLite doesn't even have a varchar type.

... but it has TEXT column affinity, which "varchar" indicates.
-- 
Igor Tandetnik

[sqlite] Retrieving the table info fails

[Igor Korot]

Simon,

On Mon, Nov 16, 2015 at 4:20 PM, Simon Slavin  wrote:
>
> On 16 Nov 2015, at 9:09pm, Igor Korot  wrote:
>
>>  1|name|varchar(100)|0  |  |0
>
> Note that this does not tell you anything about the affinity of the column, 
> or the types of the values in it.  SQLite doesn't even have a varchar type.

No, it does not.
But I would guess it should have a "string" affinity. Is there a
command to check on that?

>
>> "dflt_value" field may or may not have a value.
>
>
> It might be interesting to know what type the value in the above line has.

The type or affinity?

Thank you.

P.S.: I just made that table as compatible with other DBMS.

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

[sqlite] Retrieving the table info fails

[Igor Korot]

Simon,

On Mon, Nov 16, 2015 at 3:13 PM, Simon Slavin  wrote:
>
> On 16 Nov 2015, at 7:40pm, Igor Korot  wrote:
>
>> But if I issue this PRAGMA command the field name, field type and the PK
>> are guaranteed to have some values, right?
>
> No.  Create a field without a declared type and use that as your primary key.
>
>> You can't create a field without a name
>
> Correct.  At least I think so.
>
>> or the type
>
> Wrong.
>
> 178:~ simon$ sqlite3 ~/Desktop/fred.sql
> SQLite version 3.8.10.2 2015-05-20 18:17:19
> Enter ".help" for usage hints.
> sqlite> CREATE TABLE myTable (fred PRIMARY KEY);
> sqlite> PRAGMA table_info(myTable);
> 0|fred||0||1
> sqlite> PRAGMA index_list(myTable);
> 0|sqlite_autoindex_myTable_1|1|pk|0
> sqlite> PRAGMA index_info(sqlite_autoindex_myTable_1);
> 0|0|fred
> sqlite> PRAGMA index_xinfo(sqlite_autoindex_myTable_1);
> 0|0|fred|0|BINARY|1
> 1|-1||0|BINARY|0
> sqlite>
>
> I'd like to emphasise something Scott Hess wrote.  There is considerable 
> leeway in the documentation for PRAGMAs and things can change from one 
> version of SQLite to another.  Pragmas like this are for use only when you 
> don't know the answers.  For example for database management software which 
> has to operate on any SQLite database.

Let's go to the example (I understand better this way):

sqlite> PRAGMA table_info(leagues);
cid|name|type   |notnull|  dflt_value|pk
  0|id  |integer   |0  |  |1
  1|name|varchar(100)|0  |  |0
  2|drafttype   |integer(1)|0  |  |0
  3|scoringtype   |integer(1)|0  |  |0
  4|roundvalues   |integer(1)|0  |  |0
  5|leaguetype|char(5)|0  | |0
  6|salary   |integer|0  |  |0
  7|benchplayers|integer(1) |0  |  |0

In this output the field name is always will have a value. The field
"type" may or may not have it.
The "notnull" field I believe also will always have a value - if one
won't set it on the table creation,
the engine will assign it.
"dflt_value" field may or may not have a value.
And finally a "pk" field will always have a value - it is a boolean
field which wil be defined either
by the programmer or engine.
Am I right?

Thank you.

BTW, you don't have to make a field primary key in order for it not to
have a type.
I guess its just a beauty of the SQLite engine.

And of course those things should be documented somewhere -
inconsistencies between PRAGMA's in different
SQLite versions.



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

[sqlite] Sqlite fd problem!

[Marcus Grimm]

you are probably missing to finalize the statement
prior calling sqlite3_close().
additionally you may check the return value of
sqlite3_close() - it will tell you something.

marcus

Am 16.11.2015 um 12:02 schrieb Nader Lavasani:
> Hi all,
>
> This happened in iOS with Objective-C language.
>
> When we open a database(sqlite3_open()), that creates a fd(file descriptor)
> and when we close the database(sqlite3_close()) that only close connection
> and fd remain open.so when user open and close many db(or one db for many
> time) so many fds remain open and when fds reach to 255, app not opening
> any file or db.
>
> this is disclosure video : https://www.youtube.com/watch?v=uvKwko4LhWo
>
>
> Bug? or my silly mistake?!
>
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Are columns declared as 'INTEGER PRIMARY KEY' guaranteed not to change except from UPDATE statements?

[Simon Slavin]

On 16 Nov 2015, at 12:24am, Randy Eels  wrote:

> My main question is: is an 'INTEGER PRIMARY KEY' column guaranteed not to
> change, except as a consequence of ordinary UPDATE statements? (And in
> particular, not as a consequence of a 'vacuum' statement.)

Values in the rowid column (which has a number of other names) can change only 
if that columns is not aliased by the table definition.  Declaring a named 
column as 'INTEGER PRIMARY KEY' counts as an alias.

> I'd also thank further explanation as to why the 'vacuum' statement doesn't
> change 'rowid's when an explicit 'INTEGER PRIMARY KEY' is declared.

If you declare your own column as being INTEGER PRIMARY KEY, the rowid will not 
change by anything SQLite does automatically.  This is because SQLite knows 
that you know what the column is called, so it thinks you might be relying on 
its values.

The programmer can still change rowid by explicitly updating it, or any column 
aliased to it, using an UPDATE command.

Simon.

[sqlite] Retrieving the table info fails

[Igor Korot]

Hi,

On Mon, Nov 16, 2015 at 2:20 PM, R Smith  wrote:
>
>
> On 2015/11/16 7:59 PM, Igor Korot wrote:
>>
>> Stephan,
>>
>> On Mon, Nov 16, 2015 at 12:42 PM, Stephan Beal 
>> wrote:
>>>
>>> On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot  wrote:
>>>
 The variables referenced are defined as "std::string" and the code is in
 C++.

>>> the std::string(char const *) constructor does  not, last time i checked,
>>> accept a NULL value. You will need to pass it "" in that case.
>>>
>>> [stephan at host:~/tmp]$ cat foo.cpp
>>> #include 
>>>
>>> int main(){
>>>  std::string s(0);
>>>  return 0;
>>> }
>>>
>>> [stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++
>>> [stephan at host:~/tmp]$ ./foo
>>> terminate called after throwing an instance of 'std::logic_error'
>>>what():  basic_string::_S_construct null not valid
>>> Aborted
>>
>> OK, it looks like the value is NULL instead of "".
>> I will change the code accordingly.
>>
>> BTW, are only name, type and pk fields are guaranteed to have a value?
>
>
> Nothing is guaranteed to have a value unless created with NOT NULL in the
> field specification in the CREATE TABLE schema SQL.
>
> Usually (in most DBs) a Primary key is guaranteed to not have NULL values,
> but in SQLite it is permissible if the PK is not specifically stated to be
> NOT NULL.

Yes.
But if I issue this PRAGMA command the field name, field type and the PK
are guaranteed to have some values, right?

You can't create a field without a name or the type and the field is either part
of the PK or not.

Please correct me if I'm wrong.

Thank you.

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

[sqlite] Sqlite fd problem!

[Nader Lavasani]

Hi all,

This happened in iOS with Objective-C language.

When we open a database(sqlite3_open()), that creates a fd(file descriptor)
and when we close the database(sqlite3_close()) that only close connection
and fd remain open.so when user open and close many db(or one db for many
time) so many fds remain open and when fds reach to 255, app not opening
any file or db.

this is disclosure video : https://www.youtube.com/watch?v=uvKwko4LhWo


Bug? or my silly mistake?!


Thanks

[sqlite] Retrieving the table info fails

[Igor Korot]

Stephan,

On Mon, Nov 16, 2015 at 12:42 PM, Stephan Beal  wrote:
> On Mon, Nov 16, 2015 at 6:11 PM, Igor Korot  wrote:
>
>> The variables referenced are defined as "std::string" and the code is in
>> C++.
>>
>
> the std::string(char const *) constructor does  not, last time i checked,
> accept a NULL value. You will need to pass it "" in that case.
>
> [stephan at host:~/tmp]$ cat foo.cpp
> #include 
>
> int main(){
> std::string s(0);
> return 0;
> }
>
> [stephan at host:~/tmp]$ gcc -o foo foo.cpp -lstdc++
> [stephan at host:~/tmp]$ ./foo
> terminate called after throwing an instance of 'std::logic_error'
>   what():  basic_string::_S_construct null not valid
> Aborted

OK, it looks like the value is NULL instead of "".
I will change the code accordingly.

BTW, are only name, type and pk fields are guaranteed to have a value?

Thank you.

>
> --
> - 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Retrieving the table info fails

[Igor Tandetnik]

On 11/16/2015 12:51 PM, Igor Korot wrote:
> Also, are you saying that if I have a integer field with the default value of 
> 1,
> I will not be able to retrieve it with sqliteColumnText()?

You might be - I think SQLite will automatically convert it to the 
string "1".
-- 
Igor Tandetnik

[sqlite] Retrieving the table info fails

[Igor Korot]

Igor,

On Mon, Nov 16, 2015 at 12:42 PM, Igor Tandetnik  wrote:
> On 11/16/2015 12:11 PM, Igor Korot wrote:
>>
>> The crash occurs when the program tries to retrieve the value for
>> "fieldDefaultValue".
>> Looking at the output of "PRAGMA table_info();" I see
>> that the default value column is empty (i.e. the column does not have
>> any value) and the column type is integer and field is a PK.
>> I think it also may be set as "Auto-Increment".
>>
>> The crash happens because the program hit a NULL pointer.
>>
>> Any idea on what to look for?
>
>
> I'm not sure I understand the nature of the problem. Yes, it is entirely
> possible - common even - for a column to have no declared default value; in
> that case, PRAGMA table_info would report NULL in the corresponding column.
> Further, where there is a default value, it doesn't have to be of TEXT type.

It looks like I falsely assumed that it will return an empty string instead.
Guess I was wrong.
Also, are you saying that if I have a integer field with the default value of 1,
I will not be able to retrieve it with sqliteColumnText()?

>
> You seem to assume the default value always exists and is always a string,
> and so it's OK to use sqliteColumnText unconditionally - either assumption
> may not hold, and so it's not OK.

See above. I know the default value is optional, but thought it will be "".

Thank you.

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

[sqlite] Retrieving the table info fails

[Igor Tandetnik]

On 11/16/2015 12:11 PM, Igor Korot wrote:
> The crash occurs when the program tries to retrieve the value for
> "fieldDefaultValue".
> Looking at the output of "PRAGMA table_info();" I see
> that the default value column is empty (i.e. the column does not have
> any value) and the column type is integer and field is a PK.
> I think it also may be set as "Auto-Increment".
>
> The crash happens because the program hit a NULL pointer.
>
> Any idea on what to look for?

I'm not sure I understand the nature of the problem. Yes, it is entirely 
possible - common even - for a column to have no declared default value; 
in that case, PRAGMA table_info would report NULL in the corresponding 
column. Further, where there is a default value, it doesn't have to be 
of TEXT type.

You seem to assume the default value always exists and is always a 
string, and so it's OK to use sqliteColumnText unconditionally - either 
assumption may not hold, and so it's not OK.
-- 
Igor Tandetnik

[sqlite] Retrieving the table info fails

[Igor Korot]

Hi, ALL,
I was successfully able to use Mr. Hipp' suggestion:

char *z = sqlite3_mprintf("PRAGMA table_info(\"%w\");", zTableName);
The prepare and step z.
Then:  sqlite3_free(z);

However, for some reason, I'm getting crash. Here is the relevant code:

fieldName = reinterpret_cast( sqliteColumnText( stmt2, 1 ) );
fieldType = reinterpret_cast( sqliteColumnText( stmt2, 2 ) );
fieldIsNull = sqliteColumnInt( stmt2, 3 );
fieldDefaultValue = reinterpret_cast( sqliteColumnText(
stmt2, 4 ) );
fieldPK = sqliteColumnInt( stmt2, 5 );
fields.push_back( Field( fieldName, fieldType, fieldDefaultValue,
fieldIsNull, fieldPK == 1 ? true : false ) );

The crash occurs when the program tries to retrieve the value for
"fieldDefaultValue".
Looking at the output of "PRAGMA table_info();" I see
that the default value column is empty (i.e. the column does not have
any value) and the column type is integer and field is a PK.
I think it also may be set as "Auto-Increment".

The crash happens because the program hit a NULL pointer.

Any idea on what to look for?
The variables referenced are defined as "std::string" and the code is in C++.

Thank you.

[sqlite] Retrieving the table info fails

[Scott Hess]

On Mon, Nov 16, 2015 at 11:20 AM, R Smith  wrote:

> On 2015/11/16 7:59 PM, Igor Korot wrote:
>>
>> BTW, are only name, type and pk fields are guaranteed to have a value?
>>
>
> Nothing is guaranteed to have a value unless created with NOT NULL in the
> field specification in the CREATE TABLE schema SQL.
>
> Usually (in most DBs) a Primary key is guaranteed to not have NULL values,
> but in SQLite it is permissible if the PK is not specifically stated to be
> NOT NULL.


I'll go one further - since this is the results from a PRAGMA call, IMHO
you should make no assumptions at all about whether any of the return
values can be NULL.  PRAGMA are entirely non-standard, and unless something
is specifically guaranteed the outcome is open to changes due to future
implementation changes.

-scott

[sqlite] Are columns declared as 'INTEGER PRIMARY KEY' guaranteed not to change except from UPDATE statements?

[Igor Tandetnik]

On 11/15/2015 7:24 PM, Randy Eels wrote:
> My main question is: is an 'INTEGER PRIMARY KEY' column guaranteed not to
> change, except as a consequence of ordinary UPDATE statements? (And in
> particular, not as a consequence of a 'vacuum' statement.)

Yes. From the documentation for VACUUM ( 
http://sqlite.org/lang_vacuum.html ):

"The VACUUM command may change the ROWIDs of entries in any tables that 
do not have an explicit INTEGER PRIMARY KEY."

> I'd also thank further explanation as to why the 'vacuum' statement doesn't
> change 'rowid's when an explicit 'INTEGER PRIMARY KEY' is declared.

Because it's deliberately implemented this way, and documented to behave 
this way. I suppose I don't quite understand the question.
-- 
Igor Tandetnik

[sqlite] Information passing between xBestIndex and xFilter

[Hick Gunter]

It is up to your xBestIndex method to confern this information to your xFilter 
method, e.g. by setting the idxStr return parameter in a way these methods 
understand (e.g. leave it pointing to a character that encodes the required 
comparison). The idxStr is passed to xFilter unchanged from what the specific 
call to xBestIndex returns. If the idxStr is dynamically allocated, it is a 
good idea to set the "idxStr needs to be freed" return parameter. Remember that 
there can be more than one call to xBestIndex while the SQLite Query Planner is 
trying to figure out the optimum plan.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Johnny 
Wezel
Gesendet: Freitag, 13. November 2015 18:34
An: SQLite mailing list
Betreff: [sqlite] Information passing between xBestIndex and xFilter

I think there is a flaw in information passing between the xBestIndex and 
xFilter methods in virtual tables.

The information about the constraint operation in the aConstraint array can't 
reach xFilter. But how is xFilter to know how to set up the cursor when a 
statement like

SELECT * FROM MyTable WHERE a > 10

is given? I can pass the index covering a and I can make SQLite pass the 
constant 10 to xFilter, but not the > operation.

IMHO xBestIndex should be called after xOpen as opposed to before and have a 
cursor parameter so I can set up the whole filtering information on my terms.

Any thoughts on that?

Cheers,
Johnny
___
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] Sqlite fd problem!

[Random Coder]

On Mon, Nov 16, 2015 at 3:02 AM, Nader Lavasani
 wrote:
> Bug? or my silly mistake?!

>From the documentation for sqlite3_prepare_v2:

"The calling procedure is responsible for deleting the compiled SQL
statement using sqlite3_finalize() after it has finished with it."

You're not doing this.  Also, really consider using sqlite3_bind_text
or one of the other bind APIs instead of generating SQL statements in
code that can contain user input.

[sqlite] Are columns declared as 'INTEGER PRIMARY KEY' guaranteed not to change except from UPDATE statements?

[Randy Eels]

Using SQLite 3.8.7.1.

Here

and elsewhere

is
mentioned that the official SQLite documentation includes the line

Rowids can change at any time and without notice. If you need to depend on
> your rowid, make it an INTEGER PRIMARY KEY, then it is guaranteed not to
> change


However, I haven't been able to find that in the official documentation. I
wonder whether such an assertion is true.

Any column declared as 'INTEGER PRIMARY KEY' is said to be an alias for the
internal 'rowid' column, and this

proves
that 'rowid' can change after a 'vacuum' statement is issued. Merely
replaying the steps in the linked post, but declaring an additional column
as 'INTEGER PRIMARY KEY' shows that even after 'vacuum' both the rowid and
the declared primary key remain unchanged.

My main question is: is an 'INTEGER PRIMARY KEY' column guaranteed not to
change, except as a consequence of ordinary UPDATE statements? (And in
particular, not as a consequence of a 'vacuum' statement.)

I'd also thank further explanation as to why the 'vacuum' statement doesn't
change 'rowid's when an explicit 'INTEGER PRIMARY KEY' is declared.

Thanks in advance.

[sqlite] [AGAIN] SQLite on network share

[Stephen Chrzanowski]

Where's the like button when you actually want to use it?

On Sun, Nov 15, 2015 at 8:05 PM, James K. Lowden 
wrote:

> On Fri, 13 Nov 2015 13:19:33 -0800
> Roger Binns  wrote:
>
> > On talking to sites that had the competitor devices, we'd find they
> > did notice increases in programs crashing and data file issues, but
> > had written it off as the kind of thing that happens with Windows.
>
> Q:  Why doesn't Microsoft write fault-tolerant software?
>
> A:  No need, they have fault-tolerant customers.
>
> (I don't know whom to credit.)
>
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>