[sqlite] Tricky SQL

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-0

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

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

[sqlite] Retrieving the table info fails

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 *) co

[sqlite] Retrieving the table info fails

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

[sqlite] Retrieving the table info fails

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.

[sqlite] Retrieving the table info fails

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

[sqlite] Tricky SQL

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

[sqlite] Retrieving the table info fails

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.

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

> 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 > r

[sqlite] Retrieving the table info fails

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]

[sqlite] Retrieving the table info fails

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"

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

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_FUL

[sqlite] Retrieving the table info fails

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 affin

[sqlite] Retrieving the table info fails

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, f

[sqlite] Retrieving the table info fails

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. SQ

[sqlite] Retrieving the table info fails

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

[sqlite] Sqlite fd problem!

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. > > Wh

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

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

[sqlite] Retrieving the table info fails

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::stri

[sqlite] Sqlite fd problem!

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 fd

[sqlite] Retrieving the table info fails

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 NU

[sqlite] Retrieving the table info fails

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, 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 em

[sqlite] Retrieving the table info fails

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 in

[sqlite] Retrieving the table info fails

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( sqliteColumnTex

[sqlite] Retrieving the table info fails

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 schem

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

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] Information passing between xBestIndex and xFilter

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 t

[sqlite] Sqlite fd problem!

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

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

Using SQLite 3.8.7.1. Here and elsewhere is mentioned that the official SQLite documentation includes the line Rowids can

[sqlite] [AGAIN] SQLite on network share

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