Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Ben Asher]

This is exactly the kind of advice I was looking for.

Thanks again!

Ben

> On Oct 27, 2019, at 4:04 PM, Simon Slavin  wrote:
> 
> On 27 Oct 2019, at 10:45pm, Benjamin Asher  wrote:
> 
>> Query A
>> 
>> SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE 
>> tab1.x='constant’;
>> 
>> Query B
>> 
>> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant'
> 
> Your use of JOIN here is extremely usual.  Depending on your data, you either 
> don't need it, or it does nothing.
> 
> Suppose you have many rows in tab1 where x='constant' and also many rows in 
> tab2 where x='constant'.  What are you expecting the engine to do with them ? 
>  Should it match one tab1 row with one tab2 row ?  Or should it generate a 
> set of every possible combination ?  This tells you what your ON clause 
> should be.
> 
> Generally, a constant belongs in a WHERE clause.  To answer your original 
> question
> 
>> Is there an advantage to putting WHERE-type filtering in the ON constraint 
>> vs leaving it in the WHERE clause for LEFT JOINs?
> 
> SQLite reads the entire statement and does things to figure out the best way 
> to execute it given the indexes available.  The kind of deconstruction you 
> did to the query in your head, happens inside the SQL engine.  So we cannot 
> give you an answer "always put it in ON" or "always put it in WHERE".  
> Instead we say "Don't make your query look weird just to save a millisecond.  
> Have your query make sense, so someone who reads it understands what you are 
> doing.".
> 
> If you want to worry about speed or efficiency, worry about making useful 
> indexes for your tables instead of exactly how to phrase your query.
> ___
> 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] Count Rows Inserted into FTS5 Index w/ External Content Table

[Ben Asher]

Interesting! I appreciate the detailed response. I don't think the shadow table 
digging fits our risk profile exactly :), but it's interesting to know where to 
look if we want to check ourselves. I realized after rereading all of this that 
ultimately we want to keep track of the max rowid inserted into the index, so I 
think we'll just track that separately on our own.

Thanks again!

Ben

> On Oct 20, 2019, at 1:45 AM, Dan Kennedy  wrote:
> 
> 
>> On 19/10/62 06:31, Ben Asher wrote:
>> Hello! I'm trying to write some code to keep an external content table in
>> sync with the index. To do this, I need to be able to get some state about
>> the index: either how many rows have been inserted so far or the max rowid
>> that has been inserted into the index. However, if I try to run queries
>> (where "fts" is the name of the FTS5 virtual table) like "SELECT COUNT(*)
>> FROM fts" or "SELECT MAX(rowid) FROM fts", the result always ends up being
>> an answer as if I had run those queries on the external content table. Is
>> there some other way I can query the state in this case?
> 
> You can, but it's not quite as easy as it could be. Assuming you're not using 
> the "columnsize=0" option, the xRowCount() API, which is only available from 
> within an auxiliary function returns the value you want:
> 
> https://sqlite.org/fts5.html#xRowCount
> 
> The xRowCount() will just fall back to the "SELECT count(*)..." method, which 
> will report the number of rows in the external content table, not the number 
> of rows that have been added to the index.
> 
> So if you're prepared to write an auxiliary fts5 function in C that invokes 
> the xRowCount() API and returns its value, you could do something like:
> 
>   SELECT row_count(text) FROM text LIMIT 1;
> 
> Assuming your fts5 table is named "text". Or, if you include the "matchinfo" 
> demo code in your app from here:
> 
> https://sqlite.org/src/artifact/08c11ec968148d4c
> 
> in your build:
> 
>   SELECT matchinfo(text, 'n') FROM text LIMIT 1;
> 
> Both of these will only work if there is at least one row in the external 
> content table (as otherwise the query will return zero rows). If your 
> external content table is sometimes empty, you'll have to figure out a 
> full-text query that always returns at least one row to use.
> 
> Another way to go, if you're a risk-taking sort, is to read the same value 
> directly from the fts5 shadow tables. The query:
> 
>   SELECT block FROM test_data WHERE id=1;
> 
> returns a blob that consists of a series of SQLite varints. The first of 
> those varints is the number of rows in the index. Again, assuming your fts5 
> table is named "test".
> 
> This isn't actually a public interface, so you might get burned if it changes 
> at some point. I think that's pretty unlikely, but no promises!
> 
> On consideration, I'd probably go for the direct query on the test_data 
> table. Not least because if you use the xRowCount() API from a non-full-text 
> query you will need this fix, which won't be released until 3.31.0:
> 
>   https://sqlite.org/src/info/b528bdcd45db1b78
> 
> But have a test in place to ensure it doesn't break when you upgrade SQLite. 
> And if you can, build SQLite directly into the app (almost always a good 
> idea), don't use the system version.
> 
> Good luck,
> 
> Dan.
> 
> 
> 
> 
> 
>> 
>> Thanks!
>> 
>> Ben
>> ___
>> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Ben Asher]

Hi D. Richard Hipp. Sorry for the late reply. I had some email settings
wrong somewhere, and I didn't realize I had a reply until I remembered this
and checked the archives months later. You had written:

>>
>> Is there something we need to do proactively to ensure that schema update
>> appears immediately from other threads?
>>
>
>When a database connection has a read transaction open, it continues
>to see a snapshot of the database as it existed when the read
>transaction was first opened.  Outside changes to the database,
>including schema changes, are invisible to the connection holding the
>transaction.  This is the "I" in "ACID".
>
>As soon as you release the read transaction and start another, all
>changes will immediately become visible.
>
>If you are not deliberately holding a read transaction open, perhaps
>you are doing so accidentally by failing to sqlite3_reset() or
>sqlite3_finalize() a prepared statement.  You can perhaps figure out
>which statement that is by running:
>
>   SELECT sql FROM sqlite_stmt WHERE busy;

That is very interesting. We definitely don't expect a read transaction to
be open at that point. This is super helpful. I'll check on this next week.

Thank you!

Ben


On Fri, Aug 16, 2019 at 12:49 PM Ben Asher  wrote:

> To clarify, we add a column on our writer connection, and then "SELECT *
> FROM table" on the reader connection does not include the column that was
> added.
>
> Ben
>
> On Fri, Aug 16, 2019 at 11:32 AM Ben Asher  wrote:
>
>> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
>> schema update (adding a column to a table) on our writer connection, but
>> then the schema update isn't immediately available on the read-only
>> connections that we use on other threads, which causes a crash in our
>> application (app expects the column to exist at that point). I've verified
>> that the column does indeed get added, and everything works fine after
>> restarting the application (i.e. all connections loaded fresh pickup the
>> schema update).
>>
>> Is there something we need to do proactively to ensure that schema update
>> appears immediately from other threads?
>>
>> Some notes about our setup:
>>
>> sqlite 3.27.2
>> Using multithread mode (SQLITE_OPEN_NOMUTEX)
>> Using WAL mode
>>
>> Thanks!
>>
>> Ben
>>
>
>
> --
> Ben
>


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

Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Ben Asher]

Hi Simon. Sorry for the late reply. I had some email settings wrong
somewhere, and I didn't realize I had a reply until I remembered this and
checked the archives months later. You had written:

>Can I ask the maximum number of columns you expect to exist in that table
?  I'm working up to trying to convince you to add a row to something
instead, but I want to make sure you're doing what I think you're doing.
>
>Other people may be able to answer your question.

It's a small number of columns– less than 10. The table already has data,
and we added the column with a default value.

Thanks!

Ben

On Fri, Aug 16, 2019 at 12:49 PM Ben Asher  wrote:

> To clarify, we add a column on our writer connection, and then "SELECT *
> FROM table" on the reader connection does not include the column that was
> added.
>
> Ben
>
> On Fri, Aug 16, 2019 at 11:32 AM Ben Asher  wrote:
>
>> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
>> schema update (adding a column to a table) on our writer connection, but
>> then the schema update isn't immediately available on the read-only
>> connections that we use on other threads, which causes a crash in our
>> application (app expects the column to exist at that point). I've verified
>> that the column does indeed get added, and everything works fine after
>> restarting the application (i.e. all connections loaded fresh pickup the
>> schema update).
>>
>> Is there something we need to do proactively to ensure that schema update
>> appears immediately from other threads?
>>
>> Some notes about our setup:
>>
>> sqlite 3.27.2
>> Using multithread mode (SQLITE_OPEN_NOMUTEX)
>> Using WAL mode
>>
>> Thanks!
>>
>> Ben
>>
>
>
> --
> Ben
>


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

Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Ben Asher]

Hi José. Sorry for the late reply. I had some email settings wrong
somewhere, and I didn't realize I had a reply until I remembered this and
checked the archives months later. You had asked:

> Are you using BEGIN and END before and after the schema update?

Yes that's correct. We are doing the schema updates inside of an explicit
transaction.

Thanks!

Ben

On Fri, Aug 16, 2019 at 12:49 PM Ben Asher  wrote:

> To clarify, we add a column on our writer connection, and then "SELECT *
> FROM table" on the reader connection does not include the column that was
> added.
>
> Ben
>
> On Fri, Aug 16, 2019 at 11:32 AM Ben Asher  wrote:
>
>> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
>> schema update (adding a column to a table) on our writer connection, but
>> then the schema update isn't immediately available on the read-only
>> connections that we use on other threads, which causes a crash in our
>> application (app expects the column to exist at that point). I've verified
>> that the column does indeed get added, and everything works fine after
>> restarting the application (i.e. all connections loaded fresh pickup the
>> schema update).
>>
>> Is there something we need to do proactively to ensure that schema update
>> appears immediately from other threads?
>>
>> Some notes about our setup:
>>
>> sqlite 3.27.2
>> Using multithread mode (SQLITE_OPEN_NOMUTEX)
>> Using WAL mode
>>
>> Thanks!
>>
>> Ben
>>
>
>
> --
> Ben
>


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

Re: [sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table

[Ben Asher]

I should have included this in the first place. Here are the queries used
to setup this table and an example:

CREATE TABLE text (text TEXT NONNULL);
CREATE VIRTUAL TABLE text_fts_index USING fts5(text, content=text,
content_rowid=rowid, prefix='2 3 4 5 6 7 8 9 10', tokenize='unicode61');
INSERT INTO text VALUES ('hello');

-- I was hoping the following would return 0, but it returns 1, presumably
because it's getting the answer from the external content table
SELECT COUNT(content_rowid) FROM text_fts_index;

Thanks again!

Ben

On Fri, Oct 18, 2019 at 4:31 PM Ben Asher  wrote:

> Hello! I'm trying to write some code to keep an external content table in
> sync with the index. To do this, I need to be able to get some state about
> the index: either how many rows have been inserted so far or the max rowid
> that has been inserted into the index. However, if I try to run queries
> (where "fts" is the name of the FTS5 virtual table) like "SELECT COUNT(*)
> FROM fts" or "SELECT MAX(rowid) FROM fts", the result always ends up being
> an answer as if I had run those queries on the external content table. Is
> there some other way I can query the state in this case?
>
> Thanks!
>
> Ben
>


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

[sqlite] Count Rows Inserted into FTS5 Index w/ External Content Table

[Ben Asher]

Hello! I'm trying to write some code to keep an external content table in
sync with the index. To do this, I need to be able to get some state about
the index: either how many rows have been inserted so far or the max rowid
that has been inserted into the index. However, if I try to run queries
(where "fts" is the name of the FTS5 virtual table) like "SELECT COUNT(*)
FROM fts" or "SELECT MAX(rowid) FROM fts", the result always ends up being
an answer as if I had run those queries on the external content table. Is
there some other way I can query the state in this case?

Thanks!

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

Re: [sqlite] Non-keyword quoted identifiers parsed as string literals

[Ben Kurtovic]

> Is this working as intended? Are there plans to make SQLite reject such
> examples as malformed queries instead of implicitly coercing?

This problematic behavior, including discussion on how to disable it, is 
documented here: https://www.sqlite.org/quirks.html#dblquote 
<https://www.sqlite.org/quirks.html#dblquote>

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

Re: [sqlite] Schema updates across threads in WAL & multithread mode

[Ben Asher]

To clarify, we add a column on our writer connection, and then "SELECT *
FROM table" on the reader connection does not include the column that was
added.

Ben

On Fri, Aug 16, 2019 at 11:32 AM Ben Asher  wrote:

> Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
> schema update (adding a column to a table) on our writer connection, but
> then the schema update isn't immediately available on the read-only
> connections that we use on other threads, which causes a crash in our
> application (app expects the column to exist at that point). I've verified
> that the column does indeed get added, and everything works fine after
> restarting the application (i.e. all connections loaded fresh pickup the
> schema update).
>
> Is there something we need to do proactively to ensure that schema update
> appears immediately from other threads?
>
> Some notes about our setup:
>
> sqlite 3.27.2
> Using multithread mode (SQLITE_OPEN_NOMUTEX)
> Using WAL mode
>
> Thanks!
>
> Ben
>


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

[sqlite] Schema updates across threads in WAL & multithread mode

[Ben Asher]

Hi folks! We're running (sqlite 3.27.2) into an issue where we make a
schema update (adding a column to a table) on our writer connection, but
then the schema update isn't immediately available on the read-only
connections that we use on other threads, which causes a crash in our
application (app expects the column to exist at that point). I've verified
that the column does indeed get added, and everything works fine after
restarting the application (i.e. all connections loaded fresh pickup the
schema update).

Is there something we need to do proactively to ensure that schema update
appears immediately from other threads?

Some notes about our setup:

sqlite 3.27.2
Using multithread mode (SQLITE_OPEN_NOMUTEX)
Using WAL mode

Thanks!

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

[sqlite] the sqlite3 documentation would be pretty good if it wasn't tragic...

[Ben Earhart]

...that the person(s) that has no problem writing small, but solid, walls
of technical detail and drawing intricate circularly recursive syntax
diagrams which require multiple levels of detail to coherently represent,
can't be bothered to write example sql code for the top dozen things that
most surely cover better than the majority of real-world uses cases.

Does anybody here know where such a thing might exist? I think sqlite3 is a
gem but, for whatever reason, I have had poor luck getting sqlite3 sql code
examples - just scraps here and there. I don't use it near as much as I
could simply because of difficulty getting examples for a few basic schema
patterns.

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

Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage

[Ben Asher]

Thanks all! Super helpful.

Ben

On Wed, Feb 6, 2019 at 11:55 AM Ben Asher  wrote:

> Hi there! We're having a debate at my company about date storage in
> SQLite. SQLite has builtin support for ISO8601 in its date functions, so
> some folks have started storing dates as ISO8601 SQLite-compatible date
> strings. Are there pitfalls to storing dates this way compared to a unix
> timestamp? I'm curious to know if anyone has experience and would highly
> recommend sticking to one or the other for a particular reason. I'd also be
> grateful if anyone could point me to any articles exploring this subject.
>
> Thanks!
>
> Ben
>


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

[sqlite] ISO8601 vs Numeric Timestamp for Date Storage

[Ben Asher]

Hi there! We're having a debate at my company about date storage in SQLite.
SQLite has builtin support for ISO8601 in its date functions, so some folks
have started storing dates as ISO8601 SQLite-compatible date strings. Are
there pitfalls to storing dates this way compared to a unix timestamp? I'm
curious to know if anyone has experience and would highly recommend
sticking to one or the other for a particular reason. I'd also be grateful
if anyone could point me to any articles exploring this subject.

Thanks!

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

[sqlite] UUID extension

[Ben Webber]

Hi folks,

I've written an extension for generating RFC 4122 UUIDs:

https://github.com/benwebber/sqlite3-uuid

The API is inspired by PostgreSQL's uuid-ossp extension and Python's uuid
module. It generates TEXT UUIDs by default, but there's a helper function
to convert them to BLOBs for more efficient storage.

A user contacted me on GitHub and suggested this would be a good fit for
the loadable extension collection (<
https://github.com/benwebber/sqlite3-uuid/issues/3>).

Is there any interest in merging this with the main repository? I'm happy
to put in the work to bring it up to par with the rest of the codebase.

Cheers,

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

[sqlite] CAST AS STRING always returns 0 for STRING columns

[Ben Caine]

CAST AS STRING always returns 0 for columns that are already of STRING type.

Steps to reproduce:

sqlite> CREATE TABLE A(col1 STRING);
sqlite> INSERT INTO A VALUES('asdf');
sqlite> SELECT CAST(col1 AS STRING) FROM A;
0

We are generating SQL code programmatically, and it would be useful to not
have to special-case casting based on column type.
-- 
--

Ben Caine | Software Engineer | Verily
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Blob and Text columns: what's the difference?

[Ben Asher]

Ah great to know. Thanks!

Ben

On Tue, Aug 28, 2018 at 7:29 AM Richard Hipp  wrote:

> On 8/28/18, Ben Asher  wrote:
> > I seem to remember that BLOBs cannot be indexed. I can’t find
> documentation
> > on that though. Does anyone else recall the same thing and have a link,
> or
> > maybe someone can correct me?
>
> You might be remembering the limitations of Oracle.  Other database
> engines might also have this limitation too.  But not SQLite.  SQLite
> allows any column to be indexed, including BLOB colums.
> --
> 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
>
-- 
Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Blob and Text columns: what's the difference?

[Ben Asher]

I seem to remember that BLOBs cannot be indexed. I can’t find documentation
on that though. Does anyone else recall the same thing and have a link, or
maybe someone can correct me?

Ben

On Tue, Aug 28, 2018 at 6:50 AM Tim Streater  wrote:

> What is actually the difference between a column declared as TEXT and one
> declared as BLOB in an SQLite database? What does SQLite do to textual data
> that I ask it to put into a TEXT column? How does it know not to do that if
> I want to send some binary data to a Text column?
>
> The reason I'm interested is that I have a table with a TEXT column. For
> backwards compatibility reasons, I'd like not to change the column's type
> or even add another column with BLOB type. Up to now I've assumed that the
> data to go in the column was all ASCII or perhaps UTF-8. Now it seems that
> it's legal for it to be a mixture of encodings. So I want to treat it as
> binary and be able to just use the existing TEXT column. Anything I need to
> look out for?
>
>
> --
> Cheers  --  Tim
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
-- 
Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] virtual table module not found needs better logging

[Ben Manashirov]

 if( pMod==0 || pMod->pModule->xCreate==0 || pMod->pModule->xDestroy==0 ){
*pzErr = sqlite3MPrintf(db, "no such module: %s", zMod);
rc = SQLITE_ERROR;
}else{
rc = vtabCallConstructor(db, pTab, pMod, pMod->pModule->xCreate, pzErr);
}


That code doesn't tell me I have not implemented xDestroy.. I forgot to
implement xDestroy and was wondering for a while why it was returning no
such module. It would be better to add another check for xCreate & xDestroy
separately to report that the module is found but doesn't have the
requirement methods. Same goes for other mandatory methods. I haven't
tested if other mandatory methods are checked but for sqlite3_create_module
it should ideally check that the module has all the mandatory methods
implemented and report it to the log. xDestroy in version 3.18 is not
reported. I didn't check latest version of sqlite, but I looked at the
release notes all the way up to latest and didn't see anything mentioned
regarding this.

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

[sqlite] documentation for sqlite3_index_info-ídxStr typo.

[Ben Manashirov]

https://www.sqlite.org/c3ref/index_info.html

Instead of referring to idxStr there is text that talks about idxPtr. It
should mention if idxStr must point to a valid string or can it point to
arbitrary data. If the latter than idxStr type should change to void*
perhaps. Reading this doc it sounds like idxStr can point to anything and
sqlite will treat it as arbitrary data instead of a string.


Happy Canada Day :)
Ben Manashirov
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Return a column's contents to the free list

[Ben Asher]

Oh interesting! Is that optimization documented anywhere (website, code, or
otherwise)?

Ben

On Sat, Jun 9, 2018 at 10:36 AM Simon Slavin  wrote:

> On 9 Jun 2018, at 4:28pm, Jay Kreibich  wrote:
>
> > If a full VACUUM is not feasible, you can simply copy the table after
> the column is removed.
>
> Note that SQLite has special optimization for the command
>
> DELETE FROM oldTable
>
> with no "WHERE" clause.  So the best way to do it would be
>
> BEGIN;
> CREATE tempTable AS SELECT * FROM oldTable;
> DELETE FROM oldTable;
> INSERT INTO oldTable SELECT * FROM tempTable;
> COMMIT;
>
> Of course, if this is the master table for a foreign key, you have
> additional problems.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Return a column's contents to the free list

[Ben Asher]

Good tip on sqlite_analyze thanks!

In this particular case, it's one large table. The column we're obsoleting
is relatively small (20-30 bytes per row), but for a given instance of this
table, we could be freeing upwards of 20MB of data (across all rows in the
table for this column). Therefore, copying this table to remove the small
column isn't worth the trouble relative to the time and disk space required
to do it, compared to just living with the empty column.

Alternative, the ability to rename, and therefore repurpose, the column
would also be welcome. I understand there are constraints there too.

Ben

On Sat, Jun 9, 2018 at 10:29 AM Jay Kreibich  wrote:

>
> > On Jun 9, 2018, at 10:16 AM, Ben Asher  wrote:
> >
> > To further clarify on VACUUM, we actually want to actively avoid this
> > because it's expensive, and we'd be running these on small devices like
> > iPhones and iPads with large-ish DBs.
>
> If a full VACUUM is not feasible, you can simply copy the table after the
> column is removed.  This may or may not be feasible, depending on if you
> have a small number of large tables, or a large number of medium/small
> tables.  For smaller tables you can clear the column, then create a copy of
> the table with a new name, use something like INSERT INTO…SELECT to copy
> the data, then drop the old table, and finally rename the new one back to
> the old name.  You’ll want to do all that in a transaction which will lock
> the database, but the individual tables are not huge that might be much
> faster than a VACUUM.  Of course, it requires two copies of the table to
> exist in the database at the same time, but when you’re done, the new table
> will be smaller and all the pages used by the old table will go into the
> free list.
>
> I’d also suggest you get a copy of sqlite_analyze running, as it will tell
> you stats about page usage and such.  That will give you some insight into
> if this process is worth the effort.
>
>  -j
>
>
>
>
>
> > We'd also expect lots more writes in
> > the future (after obsoleting the column), so it seems preferable to focus
> > on making sure that the space that was used by the column is just
> returned
> > to be used by SQLite for those future writes.
> >
> > Ben
> >
> > On Sat, Jun 9, 2018 at 10:13 AM Simon Slavin 
> wrote:
> >
> >> On 9 Jun 2018, at 3:52pm, Ben Asher  wrote:
> >>
> >>> we would like to make sure that the space occupied by the columns
> >> contents is zeroed and returned to SQLite's free list (
> >> https://www.sqlite.org/faq.html#q12) to be re-used. Is setting the
> >> column's contents to "" (for a TEXT column
> >>> specifically) sufficient to do that?
> >>
> >> I recommend you use NULL instead:
> >>
> >> UPDATE myTable SET deadColumn = NULL WHERE [whatever];
> >> VACUUM;
> >>
> >> I have three reasons for preferring NULL:
> >>
> >> 1) NULL takes the minimum amount of space to store
> >> 2) handing NULLs is much faster than handling strings
> >> 3) NULL in SQL literally means "value missing" or "value unknown".
> >>
> >> [later seeing a followup]
> >>
> >> If you can't use NULL, use the integer 0.  Even if the column has TEXT
> >> affiliation.  The values NULL, 0 and 1 each take no storage (they are
> >> special value types) and all three values are handled very efficiently.
> >>
> >> VACUUM will do the actual returning of space and shrink the file size.
> >> Doing it with VACUUM does it all at once at a time of your controlling
> >> rather than slowing down subsequent operations.
> >>
> >> Simon.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> > --
> > Ben
> > ___
> > 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
>


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

Re: [sqlite] Return a column's contents to the free list

[Ben Asher]

I see. Thanks for the detailed explanation!

Given how expensive a full DB VACUUM can be (also seems impossible to
determine how expensive without running VACUUM), could a table-level VACUUM
make sense for this scenario? One could even make it a command as specific
as CLEAR COLUMN, which has this side effect.

But actually, I think that what you're saying is that when setting the
column to 0, if each row fits into a page, there's nothing to do. If there
were spill pages being used by that row, then those will be recovered? If I
understand correctly then, the only benefit is recovering those spill pages
if possible, and that will already happen by 0'ing the column if possible.
If that's the case, I think I can be happy that some space will be
recovered if possible, short of doing the full VACUUM.

Ben

On Sat, Jun 9, 2018 at 10:17 AM Jay Kreibich  wrote:

>
> > On Jun 9, 2018, at 10:04 AM, Ben Asher  wrote:
> >
> > Right we'll prefer NULL, but some columns are NON NULL. In this scenario,
> > we're not worried about returning space to the OS, but good point out
> > VACUUM. Thanks!
>
> Without digging through some detailed docs, I’m pretty sure empty string
> and NULL require the same amount of storage space.  If not, the difference
> is maybe one byte.
>
> As for freeing the pages, the issue is that row values are stored in leaf
> pages, kind of like a big array or list, packed together— not only are the
> values in a row packed, the rows themselves are them packed into pages.  If
> you just set existing column value to NULL (or empty string) it is going to
> re-pack and re-write the values in that page, but it isn’t going to
> globally recover the space used by that column because it is intermixed
> with all the other columns.  The only case when a single column change
> would trigger page recovery is if the value in that column is so big the
> row overflows a single page and requires spill pages.  Since a column clear
> generally won’t free whole pages, there is nothing to put on the free list.
>
> The point of the VACUUM is not to release free pages back to the OS, but
> to force the database to re-write (and therefore re-pack) all the rows, so
> that the whole table will require fewer pages, and whatever is left over
> can be freed (or in the case of a VACUUM, never re-written).
>
> It is different when you delete rows, since the the whole row record is
> deleted and it tends to free up bigger chunks.  But clearing the data out
> of a column only clears values in the middle of row records, so it is
> unlikely to free up pages by itself.
>
>   -j
>
>
>
> > Ben
> >
> > On Sat, Jun 9, 2018 at 10:01 AM Jay Kreibich  wrote:
> >
> >>
> >>> On Jun 9, 2018, at 9:52 AM, Ben Asher  wrote:
> >>>
> >>> Hi! I've read a lot of discussion about the constraints related to why
> >>> SQLite doesn't have destructive column commands (MODIFY, RENAME, DROP,
> >>> etc.). Despite that, we still have situations where our data model
> >> changes,
> >>> and a column becomes obsolete. Given the constraints, we've decided to
> >>> create a column "graveyard" at the application level: basically a list
> of
> >>> columns that exist but are obsolete. While we cannot drop columns, we
> >> would
> >>> like to make sure that the space occupied by the columns contents is
> >> zeroed
> >>> and returned to SQLite's free list (
> https://www.sqlite.org/faq.html#q12)
> >> to
> >>> be re-used. Is setting the column's contents to "" (for a TEXT column
> >>> specifically) sufficient to do that?
> >>
> >> That or NULL.  You also have to vacuum the database to re-pack the
> >> database pages and actually recover the disk space.  Some of the space
> may
> >> be recovered if the TEXT records were really long (and required spill
> >> pages) but if most of the column were a dozen bytes or so (more typical)
> >> you’ll want to do a VACUUM.
> >>
> >> -j
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> > --
> > Ben
> > ___
> > 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
>


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

Re: [sqlite] Return a column's contents to the free list

[Ben Asher]

That would be great! The 0/1 thing feels like a trick that, while I'm glad
I learned it, could be abstracted away by SQLite.

Ben

On Sat, Jun 9, 2018 at 10:16 AM Simon Slavin  wrote:

> Reading the thread suggests to me that a future version of SQLite might
> profit from a special value-type for the empty string, like there are
> already special column types for 0 and 1.  I wonder what proportion of
> string values stored in SQLite databases are "".
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Return a column's contents to the free list

[Ben Asher]

Thanks for that helpful tip on 0/1! We'll definitely use those then.

To further clarify on VACUUM, we actually want to actively avoid this
because it's expensive, and we'd be running these on small devices like
iPhones and iPads with large-ish DBs. We'd also expect lots more writes in
the future (after obsoleting the column), so it seems preferable to focus
on making sure that the space that was used by the column is just returned
to be used by SQLite for those future writes.

Ben

On Sat, Jun 9, 2018 at 10:13 AM Simon Slavin  wrote:

> On 9 Jun 2018, at 3:52pm, Ben Asher  wrote:
>
> > we would like to make sure that the space occupied by the columns
> contents is zeroed and returned to SQLite's free list (
> https://www.sqlite.org/faq.html#q12) to be re-used. Is setting the
> column's contents to "" (for a TEXT column
> > specifically) sufficient to do that?
>
> I recommend you use NULL instead:
>
> UPDATE myTable SET deadColumn = NULL WHERE [whatever];
> VACUUM;
>
> I have three reasons for preferring NULL:
>
> 1) NULL takes the minimum amount of space to store
> 2) handing NULLs is much faster than handling strings
> 3) NULL in SQL literally means "value missing" or "value unknown".
>
> [later seeing a followup]
>
> If you can't use NULL, use the integer 0.  Even if the column has TEXT
> affiliation.  The values NULL, 0 and 1 each take no storage (they are
> special value types) and all three values are handled very efficiently.
>
> VACUUM will do the actual returning of space and shrink the file size.
> Doing it with VACUUM does it all at once at a time of your controlling
> rather than slowing down subsequent operations.
>
> Simon.
> _______
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: [sqlite] Return a column's contents to the free list

[Ben Asher]

And for TEXT records, it sounds like this isn't always the case and is
dependent on original record size?

Ben

On Sat, Jun 9, 2018 at 10:04 AM Ben Asher  wrote:

> Right we'll prefer NULL, but some columns are NON NULL. In this scenario,
> we're not worried about returning space to the OS, but good point out
> VACUUM. Thanks!
>
> So for NON NULL columns, setting contents to empty should return the freed
> space to internal free list?
>
> Ben
>
> On Sat, Jun 9, 2018 at 10:01 AM Jay Kreibich  wrote:
>
>>
>> > On Jun 9, 2018, at 9:52 AM, Ben Asher  wrote:
>> >
>> > Hi! I've read a lot of discussion about the constraints related to why
>> > SQLite doesn't have destructive column commands (MODIFY, RENAME, DROP,
>> > etc.). Despite that, we still have situations where our data model
>> changes,
>> > and a column becomes obsolete. Given the constraints, we've decided to
>> > create a column "graveyard" at the application level: basically a list
>> of
>> > columns that exist but are obsolete. While we cannot drop columns, we
>> would
>> > like to make sure that the space occupied by the columns contents is
>> zeroed
>> > and returned to SQLite's free list (https://www.sqlite.org/faq.html#q12)
>> to
>> > be re-used. Is setting the column's contents to "" (for a TEXT column
>> > specifically) sufficient to do that?
>>
>> That or NULL.  You also have to vacuum the database to re-pack the
>> database pages and actually recover the disk space.  Some of the space may
>> be recovered if the TEXT records were really long (and required spill
>> pages) but if most of the column were a dozen bytes or so (more typical)
>> you’ll want to do a VACUUM.
>>
>>  -j
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> Ben
>


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

Re: [sqlite] Return a column's contents to the free list

[Ben Asher]

Right we'll prefer NULL, but some columns are NON NULL. In this scenario,
we're not worried about returning space to the OS, but good point out
VACUUM. Thanks!

So for NON NULL columns, setting contents to empty should return the freed
space to internal free list?

Ben

On Sat, Jun 9, 2018 at 10:01 AM Jay Kreibich  wrote:

>
> > On Jun 9, 2018, at 9:52 AM, Ben Asher  wrote:
> >
> > Hi! I've read a lot of discussion about the constraints related to why
> > SQLite doesn't have destructive column commands (MODIFY, RENAME, DROP,
> > etc.). Despite that, we still have situations where our data model
> changes,
> > and a column becomes obsolete. Given the constraints, we've decided to
> > create a column "graveyard" at the application level: basically a list of
> > columns that exist but are obsolete. While we cannot drop columns, we
> would
> > like to make sure that the space occupied by the columns contents is
> zeroed
> > and returned to SQLite's free list (https://www.sqlite.org/faq.html#q12)
> to
> > be re-used. Is setting the column's contents to "" (for a TEXT column
> > specifically) sufficient to do that?
>
> That or NULL.  You also have to vacuum the database to re-pack the
> database pages and actually recover the disk space.  Some of the space may
> be recovered if the TEXT records were really long (and required spill
> pages) but if most of the column were a dozen bytes or so (more typical)
> you’ll want to do a VACUUM.
>
>  -j
>
> _______
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Return a column's contents to the free list

[Ben Asher]

Hi! I've read a lot of discussion about the constraints related to why
SQLite doesn't have destructive column commands (MODIFY, RENAME, DROP,
etc.). Despite that, we still have situations where our data model changes,
and a column becomes obsolete. Given the constraints, we've decided to
create a column "graveyard" at the application level: basically a list of
columns that exist but are obsolete. While we cannot drop columns, we would
like to make sure that the space occupied by the columns contents is zeroed
and returned to SQLite's free list (https://www.sqlite.org/faq.html#q12) to
be re-used. Is setting the column's contents to "" (for a TEXT column
specifically) sufficient to do that?

Thanks!

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

Re: [sqlite] Proper parameter usage in FTS5 queries

[Ben Asher]

I see. Yep that clears things up. Thanks again!

Ben

On Wed, May 23, 2018 at 11:36 AM, Dan Kennedy  wrote:

> On 05/24/2018 01:27 AM, Ben Asher wrote:
>
>> Also one other question: with this method of using a parameter in the
>> MATCH
>> expression, do I still need to manually escape double quotes in the string
>> bound to the parameter by replacing them with a pair of double quotes (for
>> FTS5)?
>>
>
> Maybe. What do you need this to do?
>
> If you execute:
>
>   SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*'
>
> then the core evaluates the SQL expression ('"' || ? || '"*') and passes
> the results to FTS5. So if you bind the 5 byte string [a b c] to the
> variable, then your query is equivalent to:
>
>   SELECT rowid FROM test_fts_index WHERE text MATCH '"a b c"*';
>
> If you bind the 3 byte string [a"c], then your query is as:
>
>   SELECT rowid FROM test_fts_index WHERE text MATCH '"a"b"c"*';
>
> and FTS5 reports an error.
>
> Dan.
>
>
>
>
>
>   I'm not seeing any errors, but now I'm second guessing myself.
>>
>> Ben
>>
>> On Wed, May 23, 2018 at 10:46 AM, Ben Asher  wrote:
>>
>> Ah I see. Clever! The final solution ended up being:
>>>
>>> SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*'
>>>
>>> with || on either side of the ?. Does that sound right? Without that
>>> extra
>>> ||, sqlite returned a syntax error (near ?).
>>>
>>> As follow up, it'd be great to see this solution documented somewhere on
>>> the FTS5 page: https://sqlite.org/fts5.html. I'm glad there's a way to
>>> accomplish this, but the solution is a bit more clever than I would have
>>> come up with on my own.
>>>
>>> Thanks again for your help!
>>>
>>> Ben
>>>
>>> On Wed, May 23, 2018 at 1:15 AM, Dan Kennedy 
>>> wrote:
>>>
>>> On 05/23/2018 06:02 AM, Ben Asher wrote:
>>>>
>>>> Hi there! I'm working with a FTS5 query like this:
>>>>>
>>>>> SELECT rowid FROM text_fts_index WHERE text MATCH ?
>>>>>
>>>>> The parameter is something like this: "user input"*
>>>>>
>>>>> The idea here is to do a MATCH prefix-type query in FTS5. The problem
>>>>> with
>>>>> this query is that it breaks if the user input contains double quotes.
>>>>> What
>>>>> I want to do is something like this:
>>>>>
>>>>> SELECT rowid FROM text_fts_index WHERE text MATCH "?"*
>>>>>
>>>>> Maybe:
>>>>
>>>>SELECT rowid FROM test_fts_index WHERE text MATCH '"' ? || '"*'
>>>>
>>>> Dan.
>>>>
>>>>
>>>> This 2nd version of the query doesn't appear to work though because the
>>>>> ?
>>>>> is escaped in the quotes and doesn't appear to be recognized as a
>>>>> variable.
>>>>> Is there a way I'm missing to do this kind of MATCH prefix query and
>>>>> only
>>>>> escape the user input?
>>>>>
>>>>> Thanks!
>>>>>
>>>>> Ben
>>>>> ___
>>>>> 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
>>>>
>>>>
>>>
>>> --
>>> Ben
>>>
>>>
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Tsan Bug in WAL mode

[Ben Asher]

Got it. I'll add that one to our blacklist as well then. Thanks again!

Ben

On Wed, May 23, 2018 at 11:29 AM, Dan Kennedy  wrote:

> On 05/24/2018 01:01 AM, Ben Asher wrote:
>
>> I see. I think that makes sense! I've gone ahead and added these 2
>> functions to our local Tsan blacklist then. In researching this further, I
>> found one more Tsan issue here in walTryBeginRead:
>>
>> - One thread is here:
>> http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2558
>> - One thread is here:
>> http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2571
>>
>> Is this one expected as well? If so, I'll also add this function to our
>> blacklist.
>>
>
> It is expected, yes.
>
> The writer at line 2571 is holding an EXCLUSIVE posix lock when it writes
> the aReadMark[] entry. The reader at 2558 is not holding any lock, but
> later on in the same function it takes a SHARED lock and checks that the
> value is as it was earlier. And retries everything if it is not.
>
>   http://www.sqlite.org/src/artifact/aa9cffc7a2bad?2589..2591
>
> Dan.
>
>
>
>
>
>
>
>> Thanks again!
>>
>> Ben
>>
>>
>> On Wed, May 23, 2018 at 1:24 AM, Dan Kennedy 
>> wrote:
>>
>> On 05/23/2018 06:21 AM, Ben Asher wrote:
>>>
>>> Hi there! I believe I've found a SQLite bug that triggers TSAN. I'm
>>>> hoping
>>>> to either confirm this bug or learn what I'm doing wrong. Some
>>>> background:
>>>> in our code, we make sure to synchronize writes to our database such
>>>> that
>>>> only one write can happen at any given time for the same database in our
>>>> application (i.e. there's one synchronized writer connection). However,
>>>> we
>>>> allow any number of reads (readers create their own connections, which
>>>> are
>>>> similarly not shared with other threads) to happen on the same database
>>>> (with a cap on the number of connections to respect resource limits).
>>>> Additionally, the database has PRAGMA journal_mode=WAL; set. At the
>>>> moment
>>>> when TSAN is triggered, there is one reader and one writer each with
>>>> their
>>>> own connection to the same db performing a read and a write:
>>>>
>>>> - Reader is running: sqlite3WalFindFrame, specifically the line with
>>>> this
>>>> code (line 59679 in my sqlite.c):
>>>>
>>>> for(iKey=walHash(pgno); aHash[iKey]; iKey=walNextHash(iKey)){
>>>>
>>>>
>>>> - Writer is running walIndexAppend, specifically the line with this code
>>>> (line 57856 in my sqlite.c):
>>>>
>>>> Thanks for the thread-testing. This one is a known condition. Tsan can't
>>> see it, but we think it is safe. See the comment above the block in
>>> sqlite3WalFindFrame() for details:
>>>
>>>http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2859..2883
>>>
>>> Basically the race condition is only on the append-only hash-table. But
>>> the hash-table is only used to accelerate lookups on the aPgno[] array.
>>> And
>>> since the hash-table is append-only, it can only produce false-positives
>>> (which are filtered out when aPgno[] is inspected).
>>>
>>> Dan.
>>>
>>>
>>>
>>>
>>>
>>> aHash[iKey] = (ht_slot)idx;
>>>>
>>>>
>>>> I apologize if those line numbers aren't helpful, however I hope the
>>>> function names + code are unique enough to locate the lines in question.
>>>> This is sqlite3 version 3.23.1.
>>>>
>>>> Please let me know if there's any other information I can provide.
>>>> Thanks!
>>>>
>>>> Ben
>>>> ___
>>>> 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
>>>
>>>
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Proper parameter usage in FTS5 queries

[Ben Asher]

Also one other question: with this method of using a parameter in the MATCH
expression, do I still need to manually escape double quotes in the string
bound to the parameter by replacing them with a pair of double quotes (for
FTS5)? I'm not seeing any errors, but now I'm second guessing myself.

Ben

On Wed, May 23, 2018 at 10:46 AM, Ben Asher  wrote:

> Ah I see. Clever! The final solution ended up being:
>
> SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*'
>
> with || on either side of the ?. Does that sound right? Without that extra
> ||, sqlite returned a syntax error (near ?).
>
> As follow up, it'd be great to see this solution documented somewhere on
> the FTS5 page: https://sqlite.org/fts5.html. I'm glad there's a way to
> accomplish this, but the solution is a bit more clever than I would have
> come up with on my own.
>
> Thanks again for your help!
>
> Ben
>
> On Wed, May 23, 2018 at 1:15 AM, Dan Kennedy 
> wrote:
>
>> On 05/23/2018 06:02 AM, Ben Asher wrote:
>>
>>> Hi there! I'm working with a FTS5 query like this:
>>>
>>> SELECT rowid FROM text_fts_index WHERE text MATCH ?
>>>
>>> The parameter is something like this: "user input"*
>>>
>>> The idea here is to do a MATCH prefix-type query in FTS5. The problem
>>> with
>>> this query is that it breaks if the user input contains double quotes.
>>> What
>>> I want to do is something like this:
>>>
>>> SELECT rowid FROM text_fts_index WHERE text MATCH "?"*
>>>
>>
>> Maybe:
>>
>>   SELECT rowid FROM test_fts_index WHERE text MATCH '"' ? || '"*'
>>
>> Dan.
>>
>>
>>> This 2nd version of the query doesn't appear to work though because the ?
>>> is escaped in the quotes and doesn't appear to be recognized as a
>>> variable.
>>> Is there a way I'm missing to do this kind of MATCH prefix query and only
>>> escape the user input?
>>>
>>> Thanks!
>>>
>>> Ben
>>> ___
>>> 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
>>
>
>
>
> --
> Ben
>



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

Re: [sqlite] Tsan Bug in WAL mode

[Ben Asher]

I see. I think that makes sense! I've gone ahead and added these 2
functions to our local Tsan blacklist then. In researching this further, I
found one more Tsan issue here in walTryBeginRead:

- One thread is here:
http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2558
- One thread is here:
http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2571

Is this one expected as well? If so, I'll also add this function to our
blacklist.

Thanks again!

Ben


On Wed, May 23, 2018 at 1:24 AM, Dan Kennedy  wrote:

> On 05/23/2018 06:21 AM, Ben Asher wrote:
>
>> Hi there! I believe I've found a SQLite bug that triggers TSAN. I'm hoping
>> to either confirm this bug or learn what I'm doing wrong. Some background:
>> in our code, we make sure to synchronize writes to our database such that
>> only one write can happen at any given time for the same database in our
>> application (i.e. there's one synchronized writer connection). However, we
>> allow any number of reads (readers create their own connections, which are
>> similarly not shared with other threads) to happen on the same database
>> (with a cap on the number of connections to respect resource limits).
>> Additionally, the database has PRAGMA journal_mode=WAL; set. At the moment
>> when TSAN is triggered, there is one reader and one writer each with their
>> own connection to the same db performing a read and a write:
>>
>> - Reader is running: sqlite3WalFindFrame, specifically the line with this
>> code (line 59679 in my sqlite.c):
>>
>> for(iKey=walHash(pgno); aHash[iKey]; iKey=walNextHash(iKey)){
>>
>>
>> - Writer is running walIndexAppend, specifically the line with this code
>> (line 57856 in my sqlite.c):
>>
>
> Thanks for the thread-testing. This one is a known condition. Tsan can't
> see it, but we think it is safe. See the comment above the block in
> sqlite3WalFindFrame() for details:
>
>   http://www.sqlite.org/src/artifact/aa9cffc7a2bad?ln=2859..2883
>
> Basically the race condition is only on the append-only hash-table. But
> the hash-table is only used to accelerate lookups on the aPgno[] array. And
> since the hash-table is append-only, it can only produce false-positives
> (which are filtered out when aPgno[] is inspected).
>
> Dan.
>
>
>
>
>
>> aHash[iKey] = (ht_slot)idx;
>>
>>
>> I apologize if those line numbers aren't helpful, however I hope the
>> function names + code are unique enough to locate the lines in question.
>> This is sqlite3 version 3.23.1.
>>
>> Please let me know if there's any other information I can provide. Thanks!
>>
>> Ben
>> ___
>> 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
>



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

Re: [sqlite] Proper parameter usage in FTS5 queries

[Ben Asher]

Ah I see. Clever! The final solution ended up being:

SELECT rowid FROM test_fts_index WHERE text MATCH '"' || ? || '"*'

with || on either side of the ?. Does that sound right? Without that extra
||, sqlite returned a syntax error (near ?).

As follow up, it'd be great to see this solution documented somewhere on
the FTS5 page: https://sqlite.org/fts5.html. I'm glad there's a way to
accomplish this, but the solution is a bit more clever than I would have
come up with on my own.

Thanks again for your help!

Ben

On Wed, May 23, 2018 at 1:15 AM, Dan Kennedy  wrote:

> On 05/23/2018 06:02 AM, Ben Asher wrote:
>
>> Hi there! I'm working with a FTS5 query like this:
>>
>> SELECT rowid FROM text_fts_index WHERE text MATCH ?
>>
>> The parameter is something like this: "user input"*
>>
>> The idea here is to do a MATCH prefix-type query in FTS5. The problem with
>> this query is that it breaks if the user input contains double quotes.
>> What
>> I want to do is something like this:
>>
>> SELECT rowid FROM text_fts_index WHERE text MATCH "?"*
>>
>
> Maybe:
>
>   SELECT rowid FROM test_fts_index WHERE text MATCH '"' ? || '"*'
>
> Dan.
>
>
>> This 2nd version of the query doesn't appear to work though because the ?
>> is escaped in the quotes and doesn't appear to be recognized as a
>> variable.
>> Is there a way I'm missing to do this kind of MATCH prefix query and only
>> escape the user input?
>>
>> Thanks!
>>
>> Ben
>> ___
>> 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
>



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

[sqlite] Tsan Bug in WAL mode

[Ben Asher]

Hi there! I believe I've found a SQLite bug that triggers TSAN. I'm hoping
to either confirm this bug or learn what I'm doing wrong. Some background:
in our code, we make sure to synchronize writes to our database such that
only one write can happen at any given time for the same database in our
application (i.e. there's one synchronized writer connection). However, we
allow any number of reads (readers create their own connections, which are
similarly not shared with other threads) to happen on the same database
(with a cap on the number of connections to respect resource limits).
Additionally, the database has PRAGMA journal_mode=WAL; set. At the moment
when TSAN is triggered, there is one reader and one writer each with their
own connection to the same db performing a read and a write:

- Reader is running: sqlite3WalFindFrame, specifically the line with this
code (line 59679 in my sqlite.c):

for(iKey=walHash(pgno); aHash[iKey]; iKey=walNextHash(iKey)){


- Writer is running walIndexAppend, specifically the line with this code
(line 57856 in my sqlite.c):

aHash[iKey] = (ht_slot)idx;


I apologize if those line numbers aren't helpful, however I hope the
function names + code are unique enough to locate the lines in question.
This is sqlite3 version 3.23.1.

Please let me know if there's any other information I can provide. Thanks!

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

[sqlite] Proper parameter usage in FTS5 queries

[Ben Asher]

Hi there! I'm working with a FTS5 query like this:

SELECT rowid FROM text_fts_index WHERE text MATCH ?

The parameter is something like this: "user input"*

The idea here is to do a MATCH prefix-type query in FTS5. The problem with
this query is that it breaks if the user input contains double quotes. What
I want to do is something like this:

SELECT rowid FROM text_fts_index WHERE text MATCH "?"*

This 2nd version of the query doesn't appear to work though because the ?
is escaped in the quotes and doesn't appear to be recognized as a variable.
Is there a way I'm missing to do this kind of MATCH prefix query and only
escape the user input?

Thanks!

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

[sqlite] Vacuum results in larger database after running pragma integrity_check

[Ben Newberg]

I've noticed with 3.18.0 that it's possible to make a database increase in
size after running pragma integrity_check (which returns "ok") and then
running vacuum.

Alternatively, vacuuming without running pragma integrity_check first keeps
the database the same size as before.

The page size on the database in question is 1024. The database starts with
no pages in the freelist. I can't reproduce this with 3.17.0.

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.

--1) Before the vacuum. Database page count = 3077, and the freelist page
count is 0:
sqlite> .dbinfo
database page size:  1024
write format:1
read format: 1
reserved bytes:  0
file change counter: 52
database page count: 3077
freelist page count: 0
schema cookie:   19
schema format:   4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:   1 (utf8)
user version:0
application id:  0
software version:3008010
number of tables:2
number of indexes:   1
number of triggers:  0
number of views: 0
schema size: 309

--2) Running a vacuum (without pragma integrity_check) results in the same
size of database: 3077 page count and 0 freelist page count:
sqlite> vacuum;
sqlite> .dbinfo
database page size:  1024
write format:1
read format: 1
reserved bytes:  0
file change counter: 53
database page count: 3077
freelist page count: 0
schema cookie:   20
schema format:   4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:   1 (utf8)
user version:0
application id:  0
software version:3018000
number of tables:2
number of indexes:   1
number of triggers:  0
number of views: 0
schema size: 309

--3) Now running pragma integrity_check which returns "ok", and then
vacuuming. This increases the database page count to 3236:
sqlite> pragma integrity_check;
ok
sqlite> vacuum;
sqlite> .dbinfo
database page size:  1024
write format:1
read format: 1
reserved bytes:  0
file change counter: 54
database page count: 3236
freelist page count: 0
schema cookie:   21
schema format:   4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:   1 (utf8)
user version:0
application id:  0
software version:3018000
number of tables:2
number of indexes:   1
number of triggers:  0
number of views: 0
schema size: 309
sqlite>

Here is the full schema:
CREATE TABLE bids_list (report_id INTEGER, price_id INTEGER, premium_id
INTEGER, period_inactive INTEGER DEFAULT (0) CHECK (period_inactive IN (0,
1)));
CREATE TABLE bids_dates (report_id integer primary key, date text, current
integer check (current in (0, 1)));
CREATE INDEX idx_price_id ON bids_list (price_id);
/* No STAT tables available */

Is the pragma fixing something in the index perhaps?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign key cardinality

[Ben Newberg]

The column can be unique as well, correct?

SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> pragma foreign_keys = 1;
sqlite> create table x (a integer primary key, b integer unique, c text);
sqlite> insert into x (a, b, c) values (1, 11, 'a');
sqlite> insert into x (a, b, c) values (2, 22, 'a');
sqlite> insert into x (a, b, c) values (3, 33, 'b');
sqlite> create table y1 (a integer references x(a));
sqlite> insert into y1 values(1);
sqlite> create table y2 (b integer references x(b));
sqlite> insert into y2 values(11);
sqlite> create table y3 (c text references x(c));
sqlite> insert into y3 values('a');
Error: foreign key mismatch - "y3" referencing "x"
sqlite> insert into y3 values('b');
Error: foreign key mismatch - "y3" referencing "x"
sqlite>

On Mon, Feb 27, 2017 at 3:07 PM, Richard Hipp  wrote:

> On 2/27/17, James K. Lowden  wrote:
> > SQLite requires that foreign keys refer to primary
> > keys.
>
> No it doesn't.  Where did you get that impression?
>
> --
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .timer command missing from CLI ?

[Ben Newberg]

Is that a homebrew version of 3.16.0?

SQLite version 3.16.0 2017-01-02 11:57:58
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite>

3.15.1 was released on 2016-11-04, but it works on that version too.

On Wed, Feb 15, 2017 at 1:09 PM, Simon Slavin  wrote:

> simon$ sqlite3 ~/Desktop/fred.sql
> SQLite version 3.16.0 2016-11-04 19:09:39
> Enter ".help" for usage hints.
> sqlite> .timer on
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for
> help
> sqlite> .timer off
> Error: unknown command or invalid arguments:  "timer". Enter ".help" for
> help
>
>
> It’s still there in the .help command.  What happened ?  Has it been fixed
> in a later version ?
>
> 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] Matching wildcards in Lemon

[Ben]

> On 24 Jan 2017, at 14:47, Richard Hipp  wrote:
> 
> On 1/24/17, Ben  wrote:
>> 
>> Just for a heads up, this isn't documented at:
>> http://www.hwaci.com/sw/lemon/lemon.html
>> <http://www.hwaci.com/sw/lemon/lemon.html>
>> 
> 
> Documentation patches gladly accepted :-)

Looks like it's already written up at 
https://www.sqlite.org/src/doc/trunk/doc/lemon.html 
<https://www.sqlite.org/src/doc/trunk/doc/lemon.html> , but just not on the 
link I found before.

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

Re: [sqlite] Matching wildcards in Lemon

[Ben]

Thank you, I should have thought to search the source.

Just for a heads up, this isn't documented at: 
http://www.hwaci.com/sw/lemon/lemon.html 
<http://www.hwaci.com/sw/lemon/lemon.html>

Regards,

Ben Barnett



> On 24 Jan 2017, at 12:25, Richard Hipp  wrote:
> 
> On 1/24/17, Ben  wrote:
>> Hi all,
>> 
>> Is it possible to match any sort of wildcards in Lemon?
> 
> The %wildcard directive does this.  See the SQLite grammar at
> https://www.sqlite.org/src/artifact/29153738a73 and search for the ANY
> token.  This is used to match arbitrary input in the argument to the
> virtual table name in a CREATE VIRTUAL TABLE statement.
> 
> -- 
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Matching wildcards in Lemon

[Ben]

Hi all,

Is it possible to match any sort of wildcards in Lemon? I'd like to accept and 
capture any extra input at the end of a string to be parsed.

What I'm hoping to achieve is something like this:

start ::= first second THIRD everything_else.
first ::= code_goes_here.
second ::= code_goes_here.
everything_else ::= /* match everything up to the end of the input */


The only thing I can think of so far is to list all possible terminals in a 
series like:
everything_else ::= .
everthing_else ::= everything_else ITEM_ONE
everthing_else ::= everything_else ITEM_TWO

but this seems inelegant, particularly as the number of terminals grows.

Worst case I can fall back to splitting the input before parsing.

Does anyone have any suggestions?

Thank you.

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

Re: [sqlite] How to circumvent UNIQUE constraint

[Ben Newberg]

what i've done in the past is append a character to the value and make use
of "cast":

update desktops set indexNo = indexNo || '_';
update desktops set indexNo = cast(indexNo as integer) + 1;

then:
insert into desktops values (new row with index = 1);

from the docs, which i hope i'm not misreading:

http://sqlite.org/lang_expr.html#castexpr
"When casting a TEXT value to INTEGER, the longest possible prefix of the
value that can be interpreted as an integer number is extracted from the
TEXT value and the remainder ignored."

has worked for me for years but ymmv


On Mon, Jan 23, 2017 at 11:58 AM, Cecil Westerhof 
wrote:

> 2017-01-23 16:53 GMT+01:00 Clemens Ladisch :
>
> > Cecil Westerhof wrote:
> > >> UPDATE desktops
> > >> SET indexNo = indexNo  + 1
> > >>
> > >> But it does not, it gives:
> > >> Error: UNIQUE constraint failed: desktops.indexNo
> > >
> > > ​It is actually quite simple:
> > > PRAGMA ignore_check_constraints = ON
> >
> > A UNIQUE constraint is not a CHECK constraint.
> >
>
> ​But it works.
>
> --
> Cecil Westerhof
> ___
> 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] Date Formatting from Excel Float Date/Time Representation

[Ben Newberg]

select t0.key, "Issue Type",  strftime('%Y-%m-%d %H:%M:%S', (Updated -
(julianday('1970-01-01') - julianday('1899-12-30'))) + 2440587.5) Updated
from JIRA_Stat_0_20170106124800 t0 inner join JIRA_Stat_1_20170106124800 t1
on t0.key = t1.key

where "Last Comment" is not null

order by assignee;

give that a try




On Fri, Jan 6, 2017 at 2:23 PM, Jens Alfke  wrote:

>
> > On Jan 6, 2017, at 12:16 PM, Ed Lipson  wrote:
> >
> > The Updated column is properly reflected as a float, as that is the
> > internal Excel format. What formatting functions can I use to get it to
> > appear as a date time in SQL output, as it appears in Excel? I have tried
> > strftime and date but I don't get any meaningful data.
>
> This doesn’t seem like a question related to SQLite; you’ll need to find
> out the definition of Excel’s internal date/time format. Most numeric time
> formats have much larger numbers since they count in seconds, but the
> values around 42,000 that you showed would be consistent with a number of
> days since 1/1/1900. You can probably use one of the standard library
> functions to convert that into a time_t and print it.
>
> —Jens
> ___
> 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] UNIQUE INDEX behaves differently depending on the order rows are inserted

[Ben Hearsum]

On Wed, Nov 02, 2016 at 11:30:37PM -0400, Richard Hipp wrote:
> On 11/2/16, Ben Hearsum  wrote:
> > I've also had a couple of kernel panics lately, and I imagine that could
> > either directly cause issues, or cause issues the next time Firefox starts
> > (and tries to recover).
> 
> SQLite is suppose to recover from a kernel panic without corrupting.
> So I hope that is not the cause of your problem.  See
> https://www.sqlite.org/atomiccommit.html

I strongly suspect Firefox can be blamed, in this case. We occasionly see 
issues for profiles that are shared between multiple versions of the browesr. I 
wish I could confirm this, but I'm not able to reliably reproduce the issue.

> 
> >
> > With that said, I'm still not clear on one thing: how is it that the *new*
> > table and index I've created managed to fail the UNIQUE constraint? Unless
> > my SELECTs were lying to me (maybe due to the corruption?), there are no
> > duplicate guids in moz_places.
> 
> Yes, the SELECTs are lying to you because of the corruption.  Here is
> a SELECT that gets you an honest answer:
> 
> SELECT * FROM moz_places WHERE +guid='zK2DS06hrAfO';
> 
> The "+" in front of "guid" prevents the use of an index and thereby
> keeps the answer honest.  You'll notice that the above gives you two
> rows.  But only one of those two rows is in the
> moz_places_guid_uniqueindex index and most of the time that index is
> used for lookups, thus hiding the second row.

Ah, I see! Thank you very much for you time and explanation here.

- Ben


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

Re: [sqlite] UNIQUE INDEX behaves differently depending on the order rows are inserted

[Ben Hearsum]

Just in case it's not obvious, this is a database that's part of my Firefox
profile. The database was likely corrupted during an upgrade to Firefox
(I've been speaking with the folks that work on Firefox profiles, too), but
I've also had a couple of kernel panics lately, and I imagine that could
either directly cause issues, or cause issues the next time Firefox starts
(and tries to recover).

With that said, I'm still not clear on one thing: how is it that the *new*
table and index I've created managed to fail the UNIQUE constraint? Unless
my SELECTs were lying to me (maybe due to the corruption?), there are no
duplicate guids in moz_places.


2016-11-02 17:27 GMT-04:00 Richard Hipp :

> On 11/2/16, Ben Hearsum  wrote:
> >
> > I then did some inserts, and discovered that the order in which the rows
> are
> > inserted appears to be affecting the uniqueness of the index:
> > sqlite> insert into moz_places_bhearsum select * from moz_places order by
> > guid;
> > sqlite> reindex moz_places_bhearsum;
> > sqlite> delete from moz_places_bhearsum;
> > sqlite> insert into moz_places_bhearsum select * from moz_places;
> > sqlite> reindex moz_places_bhearsum;
> > Error: UNIQUE constraint failed: moz_places_bhearsum.guid
> >
> > Is this a real bug, or am I misunderstanding something about the way
> things
> > are supposed to work?
> >
>
> [Aside: Ben sent me the database via a private side-channel.]
>
> The database is corrupt.  You can see this if you run "PRAGMA
> integrity_check;"  Or if you run REINDEX; (with no arguments) you'll
> immediately get the UNIQUE constraint failure.
>
> All of the errors seem to be within indexes.  So normally a REINDEX
> command would fix this.  But in your case, some of the UNIQUE
> constraints are violated.
>
> So the question becomes:  How did the database manage to go corrupt?
>
> See https://www.sqlite.org/howtocorrupt.html for a list of known ways
> to corrupt an SQLite database.  Are any of these applicable here?
>
>
>
> --
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UNIQUE INDEX behaves differently depending on the order rows are inserted

[Ben Hearsum]

Hi there,

I recently had an issue with an index in a large-ish (>100,000 rows) table 
becoming corrupted. I tried to issue a REINDEX on it, but it complained of 
duplicated values. I dug into that a bit, and couldn't find any duplicate 
entries:
sqlite> select count(*) from moz_places;
100575
sqlite> select count(distinct guid) from moz_places;
100575
sqlite> select guid from moz_places group by guid having count(*) >1;
sqlite> 

I then tried to bisect to find which entry was actually causing a problem by 
creating a new table + index:
CREATE TABLE moz_places_bhearsum  (   id INTEGER PRIMARY KEY, url LONGVARCHAR, 
title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden 
INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id 
INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER , guid 
TEXT, foreign_count INTEGER DEFAULT 0 NOT NULL, url_hash INTEGER DEFAULT 0 NOT 
NULL);
sqlite> CREATE UNIQUE INDEX moz_places_bhearsum_guid_uniqueindex ON 
moz_places_bhearsum (guid);

I then did some inserts, and discovered that the order in which the rows are 
inserted appears to be affecting the uniqueness of the index:
sqlite> insert into moz_places_bhearsum select * from moz_places order by guid;
sqlite> reindex moz_places_bhearsum;
sqlite> delete from moz_places_bhearsum;
sqlite> insert into moz_places_bhearsum select * from moz_places;
sqlite> reindex moz_places_bhearsum;
Error: UNIQUE constraint failed: moz_places_bhearsum.guid


Is this a real bug, or am I misunderstanding something about the way things are 
supposed to work?

- Ben


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

Re: [sqlite] pragma integrity_check doesn't check constraints without indices

[Ben Newberg]

To be clear, my question about all bets being off sounded more rhetorical
in my head than it came out. I'm perfectly content with the outcome, just
wanted to share an example of something I accidently learned the hard way
when messing with schemas. And thanks to you all I know more about why it
happened.

On Oct 17, 2016 5:21 PM, "Kees Nuyt"  wrote:

> On Mon, 17 Oct 2016 15:40:44 -0500, Ben Newberg
>  wrote:
>
> > But I've seen some strange things when messing with pragma
> writable_schema.
> > It appears all bets are off?
>
> Yes. that's why there's a warning:
> "Warning: misuse of this pragma can easily result in
>  a corrupt database file."
>
> When changing anything in the sqlite_master table you are
> circumventing all mechanisms SQLite has to keep the database
> consistent.
> What you did in your example is creating a table description in
> sqlite_master that doesn't match the physical table in the
> database, so yes, all bets are off.
>
> --
> Regards,
>
> Kees Nuyt
> ___
> 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] pragma integrity_check doesn't check constraints without indices

[Ben Newberg]

First off, my apologies for hijacking this thread.
But I've seen some strange things when messing with pragma writable_schema.
It appears all bets are off?

example: create a table of columns (x, y, z), and fill it with values.
then, modify sqlite_master to take out column z.
let's say later on down the line, you either add a column using the
pragma writable_schema, or the ALTER TABLE.
What happens is the new column is filled with the old column's values:

SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
sqlite> .headers on
sqlite> create table t (x, y, z);
sqlite> insert into t values (1, 'a', 'A'), (2, 'b', 'B'), (3, 'c', 'C');
sqlite> select * from t;
x|y|z
1|a|A
2|b|B
3|c|C
sqlite> pragma writable_schema = 1;
sqlite> update sqlite_master set sql = 'create table t (x, y)' where name =
't';
sqlite> select * from t;
x|y|z
1|a|A
2|b|B
3|c|C  -- we are still seeing column z, until we vaccum:
sqlite> vacuum;
Error: table vacuum_db.t has 2 columns but 3 values were supplied
sqlite> select * from t;
x|y
1|a
2|b
3|c
sqlite> alter table t add column w;
sqlite> insert into t values (4, 'd', 'D');
sqlite> select * from t;
x|y|w
1|a|A
2|b|B
3|c|C
4|d|D
sqlite>

On Mon, Oct 17, 2016 at 4:03 AM, Simon Slavin  wrote:

>
> On 17 Oct 2016, at 8:17am, Torsten Landschoff 
> wrote:
>
> > So much about my attempt to report a bug. If you don't want to believe my
> > report, then don't.
>
> Sorry, just to make it clear, I'm just a fellow-user of SQLite.  I'm not
> on the development team.  And I totally believe what you wrote.  One of
> more of the development team might be figuring out the problem right now.
>
> 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

[sqlite] sqlite3.exe as 64bit stand-alone pre-compiled binary version for Windows available?

[Ben Stover]

When I go to page

http://www.sqlite.org/download.html

then there are a 32bit and a 64bit DLL library version of SQLite.

Unfortunately there is only the 32bit version of the stand-alone engine.

Ok, I know, I can run the 32bit version as well under 64bit Windows but I 
prefer a 64bit version since it 
is generally faster on 64bit system.

Is there really no 64bit satnd-alone version?

Why?

Ben

[sqlite] How to export all entries from a sqlite database into a textfile?

[Ben Stover]

As you may know Firefox browser uses a places.sqlite to store all its bookmarks.

Assume the used sqlite database is

D:\firefox\myprofile\places.sqlite

How can I use sqlite3.exe (under Windows 7) to extract/export all entries from 
this database into a text file

D:\firefox\bookmarks\extracted.txt

...and how can import them later?

The textfile should be (if possible) human readable.

Does it matter if the sqlite database is currently locked by firefox?

Related question: Is it possible to create a single file PER ENTRY (instead of 
a whole textfile)?

Ben

[sqlite] Determine query type

[Ben Newberg]

Excellent. This is exactly what I was looking for.

Thanks.

On Thu, Aug 6, 2015 at 11:50 AM, Stephan Beal  wrote:

> On Thu, Aug 6, 2015 at 6:46 PM, Ben Newberg  wrote:
>
> > Is there a good way to determine if a sql query is either attempting to
> > modify a database, or simply querying it?
> >
>
> Check the column count of the prepared statement: it will be >0 for a
> select or select-like pragma. Anything else is an update, delete, drop,
> create table/view, non-select-like pragma, or similar.
>
> --
> - 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] Determine query type

[Ben Newberg]

Is there a good way to determine if a sql query is either attempting to
modify a database, or simply querying it?

In the past, I have used sqlite3_update_hook with good results, but in this
current project, I am looking for a way to determine the type of query even
before the prepared statement is stepped thru at all. Ie, based on the
query text alone, I want to try to make this determination.

Would Explain / [Query Plan] be useful in determining the type of query?

Thanks all.

[sqlite] CSV excel import

[Ben Newberg]

+1 for the use of R for this task. I use the below if the file is in XLSX
format:

library(sqldf)
library(openxlsx)
df <- read.xlsx("mytable.xlsx", sheet=1, startRow=1, colNames=TRUE)
db <- dbConnect(SQLite(), "mydatabase.db")
dbWriteTable(db, "mytable", df)

On Sat, Aug 1, 2015 at 8:13 AM, Gabor Grothendieck 
wrote:

> Here is how to do it in R.  Download, install and start R and then paste
> the following code into R.
>
> Uncomment the first line (the line starting with  a hash) if you don't
> already have sqldf installed.  This not only installs sqldf but also the
> RSQLite driver and SQLite itself.
>
> The code assumes that mytable.csv is the input file, DF is the table name
> to create and db  is the name of the SQLite database to use (or create).
> Change these as needed.   It will use the first line of the input file as
> the column names and will automatically determine the types of  columns by
> examining the first few data rows of the input.
>
> # install.packages("sqldf")
>
> library(sqldf)
> DF <- read.csv("mytable.csv")
> sqldf(c("attach db as new", "create table new.DF as select * from DF"))
>
>
> On Thu, Jul 30, 2015 at 1:58 PM, Sylvain Pointeau <
> sylvain.pointeau at gmail.com> wrote:
>
> > I understood from the mailing list, that CSV is not a defined format,
> then
> > let's propose another format, well defined, the Excel one (which is in my
> > experience a format to is good every time I had to exchange CSV files).
> >
> > Then why don't you propose an import of CSV from Excel (or similar)?
> > csv(excel)
> >
> > is it possible? in a lot of cases, I cannot use sqlite (executable)
> because
> > of the lack of a good CSV import. It would really great if this could be
> > addressed.
> >
> > Best regards,
> > Sylvain
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] System.Data.Sqlite3.dll for OSX

[Ben Clewett]

Hi Joe,

Thanks for the idea.  The resulting .DLL will not load.  I believe it contains 
the native component of sqlite compiled for x86_Windows and not x86_OSX.

However, I found it.  Those nice guys at Xamarin/Mono provide a compiled 
version for the local platform, called Mono.Data.Sqlite.  This seems to be 
working.  Although it seems unexpectantly slow, but that?s another thread.

Many thanks,

Ben

> On 2015-07-11, at 00:43, Joe Mistachkin  wrote:
> 
> 
> Ben Clewett wrote:
>> 
>> I have having great difficulty finding a copy of the .NET (mono) assembly 
>> System.Data.Sqlite3.dll for OS X.  None I have tried work, and the Xamarin
> 
>> Studio can't compile the C++ needed to build my own.
>> 
> 
> Are you able to compile managed binaries on Windows?
> 
>   https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q6
> 
> --
> Joe Mistachkin
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.Sqlite3.dll for OSX

[Ben Clewett]

Sorry if this has been asked many times before.

I have having great difficulty finding a copy of the .NET (mono) assembly 
System.Data.Sqlite3.dll for OS X.  None I have tried work, and the Xamarin 
Studio can?t compile the C++ needed to build my own.

Tried many libs, none of seems to work.

After getting to the end of Google, I fall on the advise of this group.  If 
anybody can recommend a place where this can be located, it would be very 
welcome.

Regards,

Ben.

[sqlite] Question about ZIPVFS compression ratios, query speed

[Ben Weisburd]

I'm evaluating databases to use in a genomic data warehouse project.
I have looked into MySQL InnoDB and TokuDB (with zlib compression) and
would like to compare their performance to SQLite with ZIPVFS in terms of
compression ratios and query speeds.

Are such metrics already available somewhere?
and is there a test version of ZIPVFS that I could use to do the comparison
on my data?

Thanks,
-Ben

Re: [sqlite] Triggers and CTE's

[Ben Newberg]

Thanks everyone. I will have the programming language do the work on this
one instead of going the trigger route.
On Nov 11, 2014 7:39 PM, "Richard Hipp"  wrote:

> On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik 
> wrote:
>
> On 11/11/2014 6:15 PM, Ben Newberg wrote:
> >
> > Looks like a bug to me. The statement works standalone, but not within a
> > trigger.
> >
>
> There are many limitations and restrictions on the statements inside of
> triggers.  See the trigger documentation (
> https://www.sqlite.org/lang_createtrigger.html) for details.  It looks
> like
> I need to add "no CTEs" to the list of restrictions.
>
> This is not a bug - it is an intentional omission.  A lot of extra code
> would need to be added to support this and that is not something we want to
> do right now.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers and CTE's

[Ben Newberg]

Thanks Simon.

The 10 is just an arbitrary value I chose for this example. The user
actually determines the value at run-time, so this value could be any
integer. I have a way to settle that, if only I could figure out how I can
get this trigger working.

BEN

On Tue, Nov 11, 2014 at 5:41 PM, Simon Slavin  wrote:

>
> On 11 Nov 2014, at 11:15pm, Ben Newberg  wrote:
>
> > WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks
> > limit 10)
> > INSERT INTO zWeeks (Week) select wk from Weeks;
>
> Just use 10 INSERT commands.
>
> I don't know what's causing your error message, but your code will be
> simpler if you just don't use RECURSIVE unnecessarily.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Triggers and CTE's

[Ben Newberg]

All,

Is it possible to have CTE's within triggers? The way I read the 'SQL As
Understood By SQLite', one can, but I could be misinterpreting it.

My DDL for my trigger is as follows:

CREATE TRIGGER t_populate_zweeks
AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1
BEGIN
DELETE FROM zWeeks;
WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks
limit 10)
INSERT INTO zWeeks (Week) select wk from Weeks;
END;

When I run this, I get an error message from sqlite3_errmsg() saying syntax
error near "INSERT". However, when I run the DELETE, WITH and INSERT
statements above separately without the Create Trigger DDL, the query runs
successfully and populates my zWeeks table with values 1 through 10.

Do triggers not support this behavior, or is my syntax incorrect?

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

[sqlite] specified store provider cannot be found in the configuration, or is not valid exception

[Ben Lam]

Hi,

Really appreciate any help. I get the exception 'The specified store provider 
cannot be found in the configuration, or is not valid.' on this line in the 
code:

using (var handheldEntities = new HandheldDatabaseOnDesktopEntities())

My environment: VS2010 SP1, WIN 8.1 PRO

Snippet from app.config:



  
  
  

  
  


  
  

  


I am using the Free version of SPAMfighter.
SPAMfighter has removed 805 of my spam emails to date.

Do you have a slow PC? Try 
a free scan!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite data source not available

[Ben Lam]

Hi,

Really appreciate any help, spent a day and a half trying to figure this out 
without success. I can't get 'System.Data.SQLite Database File' to show up as 
an option I the 'choose data source' window.

My environment: VS2010 SP1, WIN 8.1 PRO

1. Installed sqlite-netFx40-setup-bundle-x86-2010-1.0.94.0.exe, ran as 
Administrator, checkmarked 'Install the designer components for Visual Studio 
2010', avast! antivirus disabled
2. Created new WPF project
3. Used Nuget package manager console: Install-Package System.Data.SQLite
4. Right click on project, Add -> Data -> ADO.NET Entity Data Model; Generate 
from database; New Connection, in the 'Choose Data Source' window I don't see 
'System.Data.Sqlite Database File' as expected

Snippet from app.config:


  
  
  
  

  
  


  
  

  



I am using the Free version of SPAMfighter.
SPAMfighter has removed 689 of my spam emails to date.

Do you have a slow PC? Try 
a free scan!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite data source not available

[Ben Lam]

Hi,

Really appreciate any help, spent a day and a half trying to figure this out 
without success. I can't get 'System.Data.SQLite Database File' to show up as 
an option I the 'choose data source' window.

My environment: VS2010 SP1, WIN 8.1 PRO

1. Installed sqlite-netFx40-setup-bundle-x86-2010-1.0.94.0.exe, ran as 
Administrator, checkmarked 'Install the designer components for Visual Studio 
2010', avast! antivirus disabled
2. Created new WPF project
3. Used Nuget package manager console: Install-Package System.Data.SQLite
4. Right click on project, Add -> Data -> ADO.NET Entity Data Model; Generate 
from database; New Connection, in the 'Choose Data Source' window I don't see 
'System.Data.Sqlite Database File' as expected

Snippet from app.config:


  
  
  
  

  
  


  
  

  


I am using the Free version of SPAMfighter.
SPAMfighter has removed 674 of my spam emails to date.

Do you have a slow PC? Try 
a free scan!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite relative dates and join query help

[Ben]

Thanks for the advice everyone, I'd not considered the group by clause here. 
The following seems to work so far:

SELECT items.id,
   items.prod_code,
   items.creation_date,
   collections.book_in_date as last_book_in_date,
   collections.collection_date as last_collection_date

FROM items, collections

WHERE collections.collection_date <= date(items.creation_date, '+50 days')

GROUP BY items.id

ORDER BY prod_code ASC,
 last_collection_date ASC


Thanks again,

Ben


On 13 Aug 2014, at 02:43, Keith Medcalf  wrote:

> 
> I don't think you want max() around collections.book_in_date.  You want the 
> max(collection_date) but the book_in_date from that row.  Since the 
> collection_date is unique, the book_in_date can only come from one record.
> 
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Petite Abeille
>> Sent: Tuesday, 12 August, 2014 12:15
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] SQLite relative dates and join query help
>> 
>> 
>> On Aug 12, 2014, at 7:38 PM, Ben  wrote:
>> 
>>> The result I'm after is:
>>> 
>>> id, prod_code, creation_date, last_book_in_date, last_collection_date
>>> 
>>> Where the final two columns are from the collection which is the
>> farthest in the future, but still within the 50-day period from creation.
>> 
>> Perhaps something along these lines:
>> 
>> selectitems.id,
>> items.prod_code,
>> items.creation_date,
>> max( collections.book_in_date ) as last_book_in_date,
>> max( collection_date ) as last_collection_date
>> from  items
>> 
>> join  collections
>> oncollections.id = items.collection_id
>> 
>> where collections.book_in_date between items.creation_date and
>> items.creation_date + 50
>> and   collections.collection_date between items.creation_date and
>> items.creation_date + 50
>> 
>> group by  items.id,
>> items.prod_code,
>> items.creation_date
>> 
>> N.B.
>> 
>> There is no 'date' type in SQLite. Will assume something else, say, a
>> Julian number for ease of date manipulation.
>> 
>>> Should I instead be processing this in the application rather than
>> database?
>> 
>> No. A database is the perfect place to process data.
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] SQLite relative dates and join query help

[Ben]

I'm trying to solve the following problem in SQLite:

Items are being produced with a short, fixed shelf life. Say 50 days. 
Item can be collected every two weeks, where each item must be registered a 
week before.

I am trying to create a query where I can list current items and the latest day 
they can be registered/collected.

Given the tables:

CREATE TABLE "items" (
"id" INTEGER PRIMARY KEY,
"prod_code" TEXT UNIQUE,
"creation_date" DATE,
"collection_id" INTEGER REFERENCES "collections"("id"), -- for when it's been 
booked for collection
)
CREATE TABLE "collections" (
"id" INTEGER PRIMARY KEY,
"book_in_date" DATE,
"collection_date" DATE UNIQUE
)

The result I'm after is:

id, prod_code, creation_date, last_book_in_date, last_collection_date

Where the final two columns are from the collection which is the farthest in 
the future, but still within the 50-day period from creation.


I think this sort of problem would require a subquery, but I can't seem to pass 
the creation_date of an item to a subquery for collections. Should I instead be 
processing this in the application rather than database?

Can anyone point me in the right direction for how to approach this kind of 
problem? 

Thank you,

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

Re: [sqlite] SQLite encoding-specific functions

[Ben]

On 6 Apr 2014, at 21:28, Richard Hipp  wrote:

> On Sun, Apr 6, 2014 at 4:18 PM, Ben  wrote:
> 
>> Hi all,
>> 
>> Is there any advantage to using the encoding specific functions from the C
>> api?
>> 
>> For example, given a database with its encoding set to UTF-16, should I
>> try to use the _bytes16() / _text16() functions?
>> 
>> Or should I just say "I'm UTF-8 all the way" and use the other functions,
>> allowing SQLite to do the conversion for me?
>> 
>> I realise that both methods work, but I'm wondering if one is actually the
>> more correct way.
>> 
>> 
>> 
> If you request text in the same encoding as it is stored in the database
> file, it runs faster.
> 
> My advice:  Always use the UTF8 functions and strive to ensure that all of
> your databases use the UTF8 encoding.
> 
> -- 
> D. Richard Hipp

Thanks very much, this simplifies things a bit.

- Ben

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

[sqlite] SQLite encoding-specific functions

[Ben]

Hi all,

Is there any advantage to using the encoding specific functions from the C api?

For example, given a database with its encoding set to UTF-16, should I try to 
use the _bytes16() / _text16() functions?

Or should I just say "I'm UTF-8 all the way" and use the other functions, 
allowing SQLite to do the conversion for me?

I realise that both methods work, but I'm wondering if one is actually the more 
correct way.

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

Re: [sqlite] Using a customized collate function to mimic integer array type.

[Ben Peng]

Thanks Dan,

I think I get your point. COLLATE not only provides a new way to compare
values (what is what I need), but also gives new appearances to existing
values by which they are sorted or grouped. So 158;42;76 can not appear as
158, 42, and 76 at the same time (158;42;76 == 42, 158;42;76 == 76, does
not imply 42 == 76).

I guess I will have to take the longer route, namely define a customized
comparison function and translate user input internally.

Thanks again for all the help, I am glad that I asked before I write any
code.

Bo



On Fri, Mar 21, 2014 at 10:44 AM, Dan Kennedy  wrote:

> On 03/21/2014 10:33 PM, Ben Peng wrote:
>
>> Hi, Tristan,
>>
>> Your solution definitely works (we have defined a few custom functions)
>> but
>> our application hides databases from users but allows users to use simple
>> conditions to retrieve results. To use this function, we would have to
>>
>> 1. teach users use this function, which is hard to do because it is
>> application specific and they need to know which fields need to use this
>> function.
>>
>> 2. translate user input to use this function internally. We will need to
>> use a separate table to record the fields that need translation, and
>> change
>> user input accordingly.
>>
>> A COLLATE function seems to mark the columns directly and save us from the
>> trouble of translating user input, so it might be a better solution in
>> this
>> case.
>>
>
> SQLite assumes that collation sequences are internally consistent. From
> the docs:
>
>  1. If A==B then B==A.
>  2. If A==B and B==C then A==C.
>  3. If AA.
>  4. If A
> But this would not be true of the proposed collation sequence. So although
> some queries will appear to work, you'll be in trouble if SQLite ever
> decides to create an automatic index based on the collation sequence. Or if
> anybody ever uses an ORDER BY or GROUP BY clause that uses it. Various
> other problems can likely arise if the optimizer starts commuting
> expressions and so on, which it sometimes does.
>
> Of course I don't know exactly what kinds of queries you are expecting,
> but this seems like the kind of thing that will come back and bite you to
> me.
>
> Dan.
>
>
>
>  On Fri, 2014-03-21 at 09:35 -0500, Ben Peng wrote:
>>>
>>>> Dear sqlite experts,
>>>>
>>> I'm far from an "sqlite expert", others should be able to
>>> provide a more authoritive answer
>>>
>>> I think what you want is rather to simply define your own custom
>>> function to implement a custom match.
>>>
>>> I think using COLLATE is wrong in this case, because the use case of
>>> COLLATE is generally to implement ORDER BY, or perhaps to build a custom
>>> index (to replace the regular strcmp() like behavior) but what you want
>>> is a rather loose matching algorithm.
>>>
>>> If you create a function using sqlite3_create_function(), similar to how
>>> you might define a REGEX function, you might execute a query with:
>>>
>>>SELECT * FROM table WHERE custom_match (table.column, 42) = 0;
>>>
>>> And then, lets assume that "custom_match" uses sqlite3_result_int()
>>> to set the result to 0 if there is a match, or -1 if there is no match,
>>> then you should get a match for any row where table.column = 158;42;76
>>>
>>> You can of course give more parameters to "custom_match", so that you
>>> can use invocations like this:
>>>custom_match (GREATER, table.column, 42)
>>>custom_match (EQUALS, table.column, 42)
>>>custom_match (LESSER, table.column, 42)
>>>
>>> Make sense ?
>>>
>>> Cheers,
>>>  -Tristan
>>>
>>>  I have an application where data stored in columns can be lists of
>>>>
>>> integers
>>>
>>>> (e.g. 158;42;76). I cannot really split such record into multiple
>>>> records
>>>> (one for 158, one for 42 etc) and I am currently storing them as VARCHAR
>>>> because they represent a complete piece of information. However, when I
>>>> select records, I would like to compare or match any of the values. For
>>>> example, the record could be selected by either
>>>>
>>>> C > 100 (match 158)
>>>> C < 100 (match 42 and 76)
>>>> C = 42 (match 42)
>>>>
>>>>  From what I have read so far, I could define a special collate function
>>>>
>>> for
>>>
>>>> these columns (sqlite3

Re: [sqlite] Using a customized collate function to mimic integer array type.

[Ben Peng]

Hi, Tristan,

Your solution definitely works (we have defined a few custom functions) but
our application hides databases from users but allows users to use simple
conditions to retrieve results. To use this function, we would have to

1. teach users use this function, which is hard to do because it is
application specific and they need to know which fields need to use this
function.

2. translate user input to use this function internally. We will need to
use a separate table to record the fields that need translation, and change
user input accordingly.

A COLLATE function seems to mark the columns directly and save us from the
trouble of translating user input, so it might be a better solution in this
case.

Thanks,
Bo



On Fri, Mar 21, 2014 at 10:05 AM, Tristan Van Berkom <
tris...@upstairslabs.com> wrote:

> On Fri, 2014-03-21 at 09:35 -0500, Ben Peng wrote:
> > Dear sqlite experts,
>
> I'm far from an "sqlite expert", others should be able to
> provide a more authoritive answer
>
> I think what you want is rather to simply define your own custom
> function to implement a custom match.
>
> I think using COLLATE is wrong in this case, because the use case of
> COLLATE is generally to implement ORDER BY, or perhaps to build a custom
> index (to replace the regular strcmp() like behavior) but what you want
> is a rather loose matching algorithm.
>
> If you create a function using sqlite3_create_function(), similar to how
> you might define a REGEX function, you might execute a query with:
>
>   SELECT * FROM table WHERE custom_match (table.column, 42) = 0;
>
> And then, lets assume that "custom_match" uses sqlite3_result_int()
> to set the result to 0 if there is a match, or -1 if there is no match,
> then you should get a match for any row where table.column = 158;42;76
>
> You can of course give more parameters to "custom_match", so that you
> can use invocations like this:
>   custom_match (GREATER, table.column, 42)
>   custom_match (EQUALS, table.column, 42)
>   custom_match (LESSER, table.column, 42)
>
> Make sense ?
>
> Cheers,
> -Tristan
>
> >
> > I have an application where data stored in columns can be lists of
> integers
> > (e.g. 158;42;76). I cannot really split such record into multiple records
> > (one for 158, one for 42 etc) and I am currently storing them as VARCHAR
> > because they represent a complete piece of information. However, when I
> > select records, I would like to compare or match any of the values. For
> > example, the record could be selected by either
> >
> > C > 100 (match 158)
> > C < 100 (match 42 and 76)
> > C = 42 (match 42)
> >
> > From what I have read so far, I could define a special collate function
> for
> > these columns (sqlite3_create_collation etc), but before I jump into the
> > details, does anyone know
> >
> > 1. Is this the correct way to proceed?
> > 2. Has anyone done anything similar so that I do not have to reinvent the
> > wheel?
> >
> > Thank you very much,
> > Bo
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using a customized collate function to mimic integer array type.

[Ben Peng]

Dear sqlite experts,

I have an application where data stored in columns can be lists of integers
(e.g. 158;42;76). I cannot really split such record into multiple records
(one for 158, one for 42 etc) and I am currently storing them as VARCHAR
because they represent a complete piece of information. However, when I
select records, I would like to compare or match any of the values. For
example, the record could be selected by either

C > 100 (match 158)
C < 100 (match 42 and 76)
C = 42 (match 42)

>From what I have read so far, I could define a special collate function for
these columns (sqlite3_create_collation etc), but before I jump into the
details, does anyone know

1. Is this the correct way to proceed?
2. Has anyone done anything similar so that I do not have to reinvent the
wheel?

Thank you very much,
Bo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Where does SQLite write out files?

[Ben]

Don't try to redirect the SQLite temporary files. In the long run, you'll be in 
for more maintenance. Instead work within the sandbox limitations.

Are you using SQLite as a file format or looking to edit arbitrary SQLite files?

If you're making a custom file format, put the SQLite database inside a package 
(search for "nsdocument package" if you're unfamiliar with this). This makes 
sandboxing almost irrelevant since your app opens the root directory of the 
package and then your app has pretty much free access to anything inside that 
directory. This is bar far the easiest approach if you can use it.

If you need access to arbitrary SQLite files, then you'll need to read up on 
NSFilePresenter and Related Items. I think this is mentioned in this one or two 
of this years WWDC videos. Try http://asciiwwdc.com for searchable 
transcriptions of the WWDC videos. It's more annoying and still has some bugs, 
but works.

- Ben


On 7 Nov 2013, at 17:42, L. Wood  wrote:

> What directories can SQLite possibly write files to?
> 
> Modern Mac OS X programs run in "sandbox mode". This is a requirement to 
> publish apps on Apple's Mac App Store. "Sandboxing" means that it is 
> impossible for an app to write files into locations outside a given 
> authorized "sandbox".
> 
> For instance, a proper way to get a safe temporary directory is Apple's own C 
> API, NSTemporaryDirectory().
> 
> How would you adapt SQLite to this environment?
> 
> 
> Here are my own thoughts so far (based on the webpage 
> http://sqlite.org/tempfiles.html):
> 
> * I'm aware of the files that SQLite can write to the *same* directory as 
> that of the actual database file, and I have ways to deal with that (by 
> putting the database file into a so-called package, so all the files will be 
> together as a unit).
> 
> * If I set the global variable sqlite3_temp_directory 
> (http://sqlite.org/c3ref/temp_directory.html) to the value returned by 
> Apple's NSTemporaryDirectory(), I'm hoping to be fine. Will I?
> 
> Unfortunately, the webpage also says:
> 
> "The manner in which SQLite uses temporary files is not considered part of 
> the contract that SQLite makes with applications. The information in this 
> document is a correct description of how SQLite operates at the time that 
> this document was written or last updated. But there is no guarantee that 
> future versions of SQLite will use temporary files in the same way. New kinds 
> of temporary files might be employed and some of the current temporary file 
> uses might be discontinued in future releases of SQLite."
> 
> This renders any efforts in this regard ill-defined. Are there any solid ways 
> out? I'm using SQLite 3.8.1, and are the rules on what webpage even true for 
> this version? That is nowhere mentioned. 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Compile option documentation conflict

[Ben]

There is a minor conflict in the online documentation.

This page: http://www.sqlite.org/fts3.html#section_2
States that: "There is not a separate SQLITE_ENABLE_FTS4 compile-time option" 
and that enabling FTS3 also includes FTS4 support.

This page: http://www.sqlite.org/compile.html
States that the option "SQLITE_ENABLE_FTS3" enables FTS3 support (without 
mentioning FTS4).
It also lists the option "SQLITE_ENABLE_FTS4" with a description stating that 
this enables FTS3 and 4.


For myself at least, adding SQLITE_ENABLE_FTS3 seems to enable both just fine.

Regards,

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

Re: [sqlite] GUI for SQLite

[Ben]

That first link is *well* out of date and the second only compares three 
editors. I don't believe there is a comprehensive comparison anywhere right now.

- Ben


On 26 Jun 2013, at 17:46, "Rose, John B"  wrote:

> fyi
> 
> http://www.barefeetware.com/sqlite/compare/?ml/
> 
> http://devtest.ws.utk.edu/tutorials/sqlite/gui/
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Rob Richardson [rdrichard...@rad-con.com]
> Sent: Wednesday, June 26, 2013 12:40 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] GUI for SQLite
> 
> Not all of us.  Thanks for the list.
> 
> RobR, SQLiteSpy user and about to find Navicat.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Finding .db file in XCode project

[Ben]

On 21 Nov 2012, at 16:06, Donald Steele  wrote:

> I am not trying to access it on a iDevice. I am trying to access it on my Mac.
> 
> How do I build the database and then give the iOS app access?
> 

It will probably be in a subfolder of:

~/Library/Application Support/iPhone Simulator/6.0/Applications

There's a load of UUID-named folders in there, one for each simulator app. Poke 
around in there and you'll likely find it.

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

Re: [sqlite] Resolving Database locking issues in a multi-user environment

[Ben Morris]

Thank you for the responses all, and please accept my apologies; I have
obviously not achieved my intended level of clarity in the scenario
description I provided.

Firstly, by multi-user I think the most precise definition would be:
'multiple, concurrent and distributed users'.

The key point that I did not make explicit is that the central database
will also be a SQLite database, and thus a synchronisation algorithm will
not avoid the locking issues I mentioned in my original post. Potentially
we could have around fifty client applications all attempting simultaneous
writes to a SQLite database stored on a file server. Our application
generates an audit trail, so these writes will be happening very regularly
during normal operation - this isn't just a case of generating a few COUNT
queries.

I'm certainly not against an eventually-consistent implementation (in fact
I can see both benefits to existing use cases and new use cases emerging
from such an implementation). However, my colleague is attempting to
achieve the impression of immediate transactional consistency by executing
the synchronisation procedure once every second.

I have read the documentation thoroughly, and presented this to our
management team, however this seems to have been to no avail, so this post
was something of an appeal to authority.

On Fri, Oct 19, 2012 at 4:21 PM, Nick Shaw  wrote:

> Richard Hipp wrote:
> > Ben Morris  wrote:
> >> If anyone could share their honest opinions of both my suggested
> >> approach, and my colleague's, I would be very grateful.
> >
> > [snip]
> > Using a separate SQLite database on each client to serve as a local
> cache of the master database and then
> > periodically synchronizing with the master is a very reasonable thing to
> do in many situations.  Such an
> > approach can be very bandwidth efficient (if implemented correctly) and
> it has the huge advantage that is
> > allows for disconnected operation - it allows the clients to continue
> functioning when the network goes down.
> > [snip]
>
> I completely agree; this is what I do in our database application - the
> clients have a local SqLite copy of a central MSSQL database, so the
> clients can operate with the data when the network goes down (which on some
> customers' sites is a fairly regular occurrence!).  To avoid having to
> rectify duplicate rows / primary key violations / etc when down, we just
> mark the local database as 'read-only' when the link to MSSQL goes down so
> no changes can  be made to it 'offline'.  Whenever the link is up, we poll
> the MSSQL database for changes every 15 seconds or so, so the clients'
> SqLite copies are pretty much always in sync.  It adds a bit of network
> traffic doing this every 15 seconds, but data doesn't change very often in
> our application so there's rarely anything more than a few COUNT queries
> going on.  Plus it means the client-side app can usually just query the
> local SqLite database instead of talking over the network to MSSQL all the
> time, which can dramatically speed things
>   up on slow networks.
>
> So I see no fundamental problem in using this kind of approach.  But as
> Richard Hipp says, it depends on what your system needs are.  Operating on
> an SqLite database from multiple networked clients (especially when on a
> Windows network) with data that is changing a lot is not advised when
> performance and concurrency are important factors.  (See
> http://www.sqlite.org/whentouse.html for specific details, as Simon
> Slavin recommended).
>
> Nick.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Resolving Database locking issues in a multi-user environment

[Ben Morris]

Hello all,

I've recently joined an organization that is attempting to use SQLite in a
multi-user environment (C#, using System.Data.SQLite, layered under the
DevExpress XPO ORM). Due to the high-latency nature of the networks on
which our application is deployed, we're seeing a very high number of
database locked exceptions generated (as well as very poor performance).

Performance was understandably worse when the default journalling mode was
enabled, so this has been turned off, leading to almost daily occurrences
of malformed databases.

I've repeatedly argued that SQLite is clearly inappropriate for use in a
multi-user environment, as per the documentation, and that we should make
the switch to a client/server RDBMS as soon as possible. However, given my
perceived lack of seniority, the management team has elected to go with a
local-master synchronisation process as recommended by the primary
developer.

This developer is currently implementing this synchronisation algorithm to
allow users to work on a local SQLite database and have that data
transferred to a master SQLite database on the network.

When a local row is changed, a Sync flag is set to true. The
synchronisation algorithm is triggered by a timer running every second, and
consists of two methods: Upload and Download. Any SQL commands mentioned
below are being built using String.Format, by combining data and metadata
extracted from methods available in the ORM layer.

During Upload, the local database is scanned for rows where the Sync flag
is true. Each row like this is either updated or inserted into the master
database (depending on whether a row can be found with the same PK).

During Download, every local table is compared, row-by-row, field-by-field,
to the corresponding table in the master database (both tables are loaded
into memory as array structures to speed up the comparison). If any
differences are found, or the local row is missing, the local row is
updated/inserted using the data from the master database

Ignoring the obvious fact that this will not resolve our locking and
malformation problems, I'm also deeply concerned about the maintainability
and supportability of such code. Given the fact that I cannot get any
traction with my management team, I was hoping an appeal to authority might
make them see sense.

If anyone could share their honest opinions of both my suggested approach,
and my colleague's, I would be very grateful.

Many thanks,

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

Re: [sqlite] Lemon: Non-terminal destructors and cleanup

[Ben]

On 18 Oct 2012, at 20:07, Richard Hipp  wrote:

> On Thu, Oct 18, 2012 at 3:03 PM, Ben  wrote:
> 
>> Hi list,
>> 
>> I'm having a little trouble getting my head around memory management
>> within a Lemon-generated parser. Specifically the part of the docs stating
>> when a destructor will or will not be called.
>> 
>> For example, this is a portion of a grammar based on the SQLite parse.y
>> file:
>> 
>> 
>> columnName ::= nm(N). {
>>BSSQLiteColumn *col = [[BSSQLiteColumn alloc] init];
>>col.name = N.textValue;
>>[[parsedTable columns] addObject:col];
>> }
>> 
>> nm(A) ::= id(X). { A = X; }
>> nm(A) ::= STRING(X). { A = X; }
>> id(A) ::= ID(X). { A = X; }
>> 
>> Notes:
>> - The token type here is a struct containing an Objective-C string which
>> needs freeing when done with.
>> - Only a %token_destructor is defined, not any others
>> 
>> 
>> I know that the last three assignments are leaking memory, but I don't
>> know when I should be explicitly freeing my allocated memory within a token
>> and when I should be relying on the destructor defined by
>> %token_destructor{}. Or for that matter whether I should be declaring a
>> more specific symbol destructor.
>> 
>> Can anyone shed some light on how this should be done?
>> 
> 
> If the nonterminal payload is passed into an action (as in your example
> where N is processed because of nm(N)) then Lemon assume that your code
> will free the content, if needed.
> 
> If the rule had been:  columnName ::= nm {...}   (without the (N) argument
> to nm) then the destructor would have been called.

Got it. I've added two release calls for X at the end of the C code blocks for 
these two:
nm(A) ::= STRING(X). { A = X; }
id(A) ::= ID(X). { A = X; }

and now it's working leak-free.

Thank you.

Ben


> The destructor is also called if nm is popped from the stack for any reason
> other than the columnName ::= nm rule, such as when the stack is popped
> during error recovery.
> 
> 
>> 
>> Thanks,
>> 
>> Ben
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Lemon: Non-terminal destructors and cleanup

[Ben]

Hi list,

I'm having a little trouble getting my head around memory management within a 
Lemon-generated parser. Specifically the part of the docs stating when a 
destructor will or will not be called.

For example, this is a portion of a grammar based on the SQLite parse.y file:


columnName ::= nm(N). {
BSSQLiteColumn *col = [[BSSQLiteColumn alloc] init];
col.name = N.textValue;
[[parsedTable columns] addObject:col];
}

nm(A) ::= id(X). { A = X; }
nm(A) ::= STRING(X). { A = X; }
id(A) ::= ID(X). { A = X; }

Notes:
- The token type here is a struct containing an Objective-C string which needs 
freeing when done with.
- Only a %token_destructor is defined, not any others


I know that the last three assignments are leaking memory, but I don't know 
when I should be explicitly freeing my allocated memory within a token and when 
I should be relying on the destructor defined by %token_destructor{}. Or for 
that matter whether I should be declaring a more specific symbol destructor.

Can anyone shed some light on how this should be done?

Thanks,

Ben


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

[sqlite] Problem in sqlite3VXPrintf due to floating point optimization

[van der Merwe, Ben]

Hi,

If we compile sqlite3.c (version 3.7.14) using Microsoft Visual C++ 2010 with 
maximum optimization, /Ox, then we experience this problem:

The code in sqlite3VXPrintf is sensitive to numerical optimization. With 
optimizations turned on, a number like:

99.943

*sometimes* is returned as the string:

:0.0

(The first letter is a colon, which has an ASCII value just higher than that of 
'9').
And with optimizations turned off, you correctly get the string:

100.0

If you look at the code in that method, you can see that numbers like this can 
be problematic, as there is some room for difference between the code that 
determines the value for e2 and the code that actually processes it. It is one 
of those "At what point does 0.999... become 1?" type situations.

One potential fix is to change the code in sqlite3.c (around line 19737 in the 
amalgamated version) from:

/* Digits prior to the decimal point */
if( e2<0 ){
  *(bufpt++) = '0';
}else{
  for(; e2>=0; e2--){
*(bufpt++) = et_getdigit(&realvalue,&nsd);
  }
}

To this:

/* Digits prior to the decimal point */
if( e2<0 ){
  *(bufpt++) = '0';
}else{
  for(; e2>=0; e2--){
 char digit = et_getdigit(&realvalue,&nsd);
 if (digit == ':'){
   *(bufpt++) = '1';
   *(bufpt++) = '0';
 }
 else{
   *(bufpt++) = digit;
 }
  }
}

The latter form will handle the (rare) eventually of numerical round off 
causing the first digit of the number trying to be '9'+1, or "10".

Any comments on this code change? Is this a safe fix? Any alternate suggestions 
for fixing this in the code?

Can this please be logged as bug? And no, strictly speaking technically it is 
perhaps not a bug, but at least a request to review the code in that method and 
make it a little more defensive / less vulnerable given situations like this? 
Change to code to handle this better, so as to continue the culture of sqlite 
working and compiling under just about anything with no problem.

I have played around with the C++ optimization settings to get more 
consistency, with little luck, unless you turn it all off (which is 
undesirable), and then you still wonder.
Plus it is better to change code to be less vulnerable to such optimizations 
anyway.

SQLite is wonderful! One of the greatest open source gems out there ever.

Any thoughts or suggestions would be much appreciated,
Thank you.
---
This e-mail is confidential and intended only for the individual(s) to whom it 
is addressed. If you or your organisation is not an intended recipient of this 
e-mail, please notify the sender by replying and do not read or disseminate its 
information. Please delete all copies from your system. KBC is liable neither 
for the proper or complete transmission of the information contained in this 
communication nor for any delay in its receipt. Opinions, conclusions and other 
information in this message and attachments that do not relate to the official 
business of KBC are neither given nor endorsed by it. Even though the Mimecast 
Virus Centre has checked this message for all known viruses, you should carry 
out your own virus checks before opening any attachments. Thank you for your 
co-operation. 
www.kbcat.com-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite feature request: strict quoting pragma

[Ben Denckla (Orders)]

Upon reading "Programmers are cautioned not to use the two exceptions described 
in the previous bullets" at http://www.sqlite.org/lang_keywords.html, the goody 
two-shoes in me thought, I would like a pragma that disables those exceptions, 
i.e. a "strict quoting" pragma. Then I could use that pragma to assure myself 
that I was not inadvertently taking advantage of those exceptions.

Perhaps it might also be nice to have an "always quote" pragma, which requires 
that anything that can be quoted is quoted. Then I could use that pragma to 
assure myself that my generated SQL is immune to a change of identifier from 
something that doesn't need a quote to something that does, i.e. user_id to 
user-id. E.g. to assure myself that I have called Perl's $dbh->quote_identifier 
where appropriate. This assumes that my generated SQL is intended to be "dumb" 
or conservative, in that it quotes everything regardless of whether it needs to 
be quoted.

Is this kind of pragma hard to make because it changes the language syntax, and 
the language syntax is assumed to be fixed? So would they in fact have to be a 
compile option, not a pragma?

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

[sqlite] Shapefile 2.0

[Ben Harper]

SQLite, in its 'default' code configuration, contains enough functionality to 
be a replacement for a shapefile.
However, there is one very significant feature that is wasteful to ignore: 
spatial indexes.
In order for the SQLite RTree to be used, it needs accompanying code that 
understands the geometry data inside the database.

And that, quite simply, is what I have here:

http://www.baboonz.org/shapefile2.php

Only 1269 lines of code, liberally licensed.


--
The content of this e-mail message and all attachments thereto (`this message`) 
does not necessarily reflect the views of IMQS Software (Pty) Ltd (`IMQS`). 
Before acting on the contents thereof, the recipient should verify that the 
originator has the appropriate delegated authority. In the event that this 
message has not been appropriately authorised in terms of IMQS`s delegation of 
authority, or in the event of the personal usage of IMQS`s e-mail facility, 
IMQS will not be liable for the contents of this message.

1. This message may contain information which is confidential, private or 
privileged in nature and subject to legal privilege. If you are not the 
intended recipient you may not peruse, use, disseminate, distribute or copy 
this message or its attachments. Please notify the sender immediately by 
e-mail, facsimile or telephone and thereafter return and or destroy this 
original message.

2. Please note that the recipient must scan this message and any attachments 
for viruses and the like. IMQS accepts no liability, damage or expense 
resulting directly or indirectly from the access of any message or it`s 
attachments or the use thereof.

3. IMQS reserves the right to monitor, read, filter, block, delete, use and act 
upon any incoming or outgoing message or it`s attachments sent or received by 
the employee, including hyperlinks in such message attachments and files copied 
or saved, automatically or by the employee on IMQS`s equipment.
--
Please report email abuse / misuse to itsupp...@imqs.co.za

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

Re: [sqlite] Patch to pass collation through to Virtual Tables (The Patch Text)

[Ben Harper]

nt, const void*);
-  void (*xDel)(void*);  /* Destructor for pUser */
-};
-
-/*
-** Allowed values of CollSeq.type:
-*/
-#define SQLITE_COLL_BINARY  1  /* The default memcmp() collating sequence */
-#define SQLITE_COLL_NOCASE  2  /* The built-in NOCASE collating sequence */
-#define SQLITE_COLL_REVERSE 3  /* The built-in REVERSE collating sequence */
-#define SQLITE_COLL_USER0  /* Any other user-defined collating sequence */
 
 /*
 ** A sort order can be either ASC or DESC.
diff -r 354a72d834e6 third_party/sqlite/sqlite3/where.c
--- a/third_party/sqlite/sqlite3/where.cFri Sep 09 15:13:23 2011 +0200
+++ b/third_party/sqlite/sqlite3/where.cFri Sep 09 15:17:16 2011 +0200
@@ -1971,6 +1971,7 @@
 pIdxCons[j].iColumn = pTerm->u.leftColumn;
 pIdxCons[j].iTermOffset = i;
 pIdxCons[j].op = (u8)pTerm->eOperator;
+pIdxCons[j].pColl = sqlite3BinaryCompareCollSeq(pParse, 
pTerm->pExpr->pLeft, pTerm->pExpr->pRight);
 /* The direct assignment in the previous line is possible only because
 ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical.  The
 ** following asserts verify this fact. */



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Ben Harper
Sent: 09 September 2011 03:18 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Patch to pass collation through to Virtual Tables

Hi,

I have attached a one-liner (vtab-collation.patch) that sends collation 
information through to virtual tables.
The bigger patch (vtab-collation-full.patch) illustrates the header file 
changes necessary to make this compile.

Unfortunately, it's not as simple as just one line of code, since my patch 
requires exposure of CollSeq to the outside world.

What this patch does is to grant the virtual table the opportunity to 
distinguish between the following two queries:

SELECT * FROM vtab WHERE field = 'abc';
SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;

This also grants the virtual table the ability to recognized case-insensitive 
LIKE queries, which is also not currently possible.

I basically just copied the single line from bestBtreeIndex into 
bestVirtualIndex.

Is there any chance something like this could get merged into the main line?

Regards,
Ben




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

[sqlite] Patch to pass collation through to Virtual Tables

[Ben Harper]

Hi,

I have attached a one-liner (vtab-collation.patch) that sends collation 
information through to virtual tables.
The bigger patch (vtab-collation-full.patch) illustrates the header file 
changes necessary to make this compile.

Unfortunately, it's not as simple as just one line of code, since my patch 
requires exposure of CollSeq to the outside world.

What this patch does is to grant the virtual table the opportunity to 
distinguish between the following two queries:

SELECT * FROM vtab WHERE field = 'abc';
SELECT * FROM vtab WHERE field = 'abc' COLLATE NOCASE;

This also grants the virtual table the ability to recognized case-insensitive 
LIKE queries, which is also not currently possible.

I basically just copied the single line from bestBtreeIndex into 
bestVirtualIndex.

Is there any chance something like this could get merged into the main line?

Regards,
Ben




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

[sqlite] LIKE operator on virtual table assumes ASCII collation

[Ben Harper]

>From xBestIndex and xFilter, there is no way to determine the intended 
>collation.
The default collation for a field is binary.
However, a LIKE operator needs an ASCII collation, and in the case of a virtual 
table, the
constraints passed in to xBestIndex and xFilter assume an ASCII collation. 
However, it is
impossible for xBestIndex or xFilter to know this.

For example (pseudo code):

CREATE VIRTUAL TABLE tab( txt TEXT );
INSERT INTO tab VALUES( 'AA');
INSERT INTO tab VALUES( 'AB');

SELECT * FROM tab WHERE txt LIKE 'AA%';
AA  -- This is correct

However, xBestIndex receives the equivalent of the following constraints:

SELECT * FROM tab WHERE txt >= 'AA' AND txt < 'Ab';
AA
AB -- Wrong

Now, these constraints are correct if we're working with an ASCII collation, 
but xBestIndex has no way of knowing that.

Am I missing some piece of information here, or is this a genuine shortcoming 
of the virtual table mechanism?

Regards,
Ben

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

[sqlite] SQLite giving error with nested inner joins

[Ben van der Merwe]

Hi,
We make use of a number of queries which we also run against other databases. 
Consider this simplified example:
SELECT psim_objects.ObjectName, property.AttributeTextValue, data.Attribute, 
data.AttributeUOM FROM psim_objects  INNER JOIN (psim_objectdata data  INNER 
JOIN psim_objectdata property ON data.ObjectID = property.ObjectID)  ON 
psim_objects.ObjectID = data.OwnerObject 
It executes fine on other databases, but on SQLite it gives the error:
SQL Error: Error running Query - no such column: property.AttributeTextValue
Now, one can rewrite this query so it does work, but it should work as is on 
SQLite, it works as is on other databases, and a number of tools that build 
queries tend to build queries like this.
Please evaluate this and log it as a bug or enhancement request as appropriate?
Also, I am sure this must be logged somewhere, yet I can not see it in the bug 
and enhancement list, but it would be very convenient if SQLite would support 
more of the now standard SQL string functions like LEFT, RIGHT, SUBSTRING, etc. 
I know SQLite has SUBSTR and you can extend the functions and SQLite does not 
compete with Oracle, SQL Server etc. but there is some benefit in being able to 
write one SQL query that runs on a number of databases rather than "if Oracle 
do this... If sqlite do this...". I think SQLite is possibly the only database 
which does not have the LEFT string function. Please log that as an enhancement 
request.
Thank you kindly for your consideration.
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] caveat on blanket UPDATE triggers

[Ben Harper]

Apologies!

My mistake. The UPDATE trigger can be catch-all-fields (ie no fields specified),
and it is still correct.

To answer your question Dan, I am not creating a trigger on an RTree.
I am creating a trigger on a regular table, which keeps the RTree up to date.

Thanks,
Ben


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dan Kennedy [danielk1...@gmail.com]
Sent: 14 July 2011 06:02 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] caveat on blanket UPDATE triggers

On 07/14/2011 10:42 PM, Ben Harper wrote:
> This may be specific to RTree indexes - I haven't taken the time to fully 
> understand it.
>
> Dangerous:
> CREATE TRIGGER ON UPDATE ON tablename { SET RTREE MIN/MAX }
>
> Fine:
> CREATE TRIGGER ON UPDATE OF geometry OF tablename { SET RTREE MIN/MAX }
>
> Failure to specify the field name explicitly results in a corrupt RTree.

Is this an SQLite problem? Or a problem with some system built on
top of SQLite? If it is an SQLite problem, do you have an SQL
script or program to demonstrate it?

SQLite is not supposed to allow triggers on r-tree tables.

Thanks,
Dan.

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

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

[sqlite] caveat on blanket UPDATE triggers

[Ben Harper]

This may be specific to RTree indexes - I haven't taken the time to fully 
understand it.

Dangerous:
CREATE TRIGGER ON UPDATE ON tablename { SET RTREE MIN/MAX }

Fine:
CREATE TRIGGER ON UPDATE OF geometry OF tablename { SET RTREE MIN/MAX }

Failure to specify the field name explicitly results in a corrupt RTree.

I assume that either OLD.geometry or NEW.geometry is not available during
the execution of the trigger, so the engine doesn't run the MAXX,MINX,MAXY,
MINY functions, but I haven't taken the time to verify that.

All that I can verify is that if I place a breakpoint inside my MINX class of 
functions,
that breakpoint does not get hit, but the RTree is nevertheless updated.

One symptom of the corruption is:
SELECT * from idx_table_field_geometry,
and  you'll end up with a bunch of 0,0,0,0 MBRs.

Ben


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

Re: [sqlite] DROP TABLE yields SQLITE_LOCKED from user-defined function

[Ben Harper]

OK.

I'm assuming this is not a regular pattern that has an idiomatic workaround?

Ben

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: 07 July 2011 05:18 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] DROP TABLE yields SQLITE_LOCKED from user-defined function

> Is there something special one needs to do in order to drop a table from 
> inside a user-defined function?
>
> To test, I call it simply like so:
> SELECT my_function();

It shouldn't be ever possible to change the database from within a
function called from SELECT statement. SELECT query doesn't ever
changes any data.


Pavel


On Thu, Jul 7, 2011 at 10:38 AM, Ben Harper  wrote:
> I have a user-defined function that I register with create_function.
>
> Inside my function I try to drop an r-tree virtual table, but I get the error 
> SQLITE_LOCKED.
>
> Is there something special one needs to do in order to drop a table from 
> inside a user-defined function?
>
> To test, I call it simply like so:
> SELECT my_function();
>
> Thanks,
> Ben
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] DROP TABLE yields SQLITE_LOCKED from user-defined function

[Ben Harper]

I have a user-defined function that I register with create_function.

Inside my function I try to drop an r-tree virtual table, but I get the error 
SQLITE_LOCKED.

Is there something special one needs to do in order to drop a table from inside 
a user-defined function?

To test, I call it simply like so:
SELECT my_function();

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

Re: [sqlite] False negatives from RTree

[Ben Harper]

I've tried stepping into the rtree code, and the right code paths
all seem to get hit.
Nevertheless, once the transaction completes, I open the table again,
and the freshly inserted records do not appear in the 
idx_elec_lv_cable_geometry_rowid table. And this is especially strange,
since I debug-step-over the code that performs the insertion into
idx_elec_lv_cable_geometry_rowid, and it all appears to run without
a hitch.



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: 05 July 2011 03:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] False negatives from RTree

On Tue, Jul 5, 2011 at 8:55 AM, Ben Harper  wrote:

> Hi,
> Has anybody ever seen anomalous behaviour on an R*Tree. Specifically, false
> negatives?
> I'm using Spatialite 2.4.0 RC.
>
> What I end up with is a table into which I can insert new geometry, but
> that geometry does not get retrieved by an appropriate RTree search.
> Indeed, even a search which spans all of my geometry does not yield these
> newly inserted records.
>
> For example:
> select count(rowid) from idx_elec_lv_cable_geometry where xmax >= -9e30 AND
> xmin <= 9e30 AND ymax >= -9e90 AND ymin <= 9e30;
> 17235
>
> select (count*) from elec_lv_cable;
> 17241
>
> So I'm missing 6 records there.
>

What does the following show:

SELECT * FROM idx_elec_lv_cable_geometry
EXCEPT SELECT * FROM idx_elec_lv_cable_geometry where xmax >= -9e30 AND xmin
<= 9e30 AND ymax >= -9e90 AND ymin <= 9e30;

That query should show just the 6 records that are missing.


>
> I can't discern anything strange about the data, or my methods.
> I'm curious to know whether anybody has seen this kind of thing before?
>
> Thanks,
> Ben
>
> ps. This message is cross-posted to the Spatialite mailing list.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] False negatives from RTree

[Ben Harper]

The statement <<

SELECT * FROM idx_elec_lv_cable_geometry
EXCEPT SELECT * FROM idx_elec_lv_cable_geometry where xmax >= -9e30 AND xmin
<= 9e30 AND ymax >= -9e90 AND ymin <= 9e30;

>>

yields an empty set.



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: 05 July 2011 03:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] False negatives from RTree

On Tue, Jul 5, 2011 at 8:55 AM, Ben Harper  wrote:

> Hi,
> Has anybody ever seen anomalous behaviour on an R*Tree. Specifically, false
> negatives?
> I'm using Spatialite 2.4.0 RC.
>
> What I end up with is a table into which I can insert new geometry, but
> that geometry does not get retrieved by an appropriate RTree search.
> Indeed, even a search which spans all of my geometry does not yield these
> newly inserted records.
>
> For example:
> select count(rowid) from idx_elec_lv_cable_geometry where xmax >= -9e30 AND
> xmin <= 9e30 AND ymax >= -9e90 AND ymin <= 9e30;
> 17235
>
> select (count*) from elec_lv_cable;
> 17241
>
> So I'm missing 6 records there.
>

What does the following show:

SELECT * FROM idx_elec_lv_cable_geometry
EXCEPT SELECT * FROM idx_elec_lv_cable_geometry where xmax >= -9e30 AND xmin
<= 9e30 AND ymax >= -9e90 AND ymin <= 9e30;

That query should show just the 6 records that are missing.


>
> I can't discern anything strange about the data, or my methods.
> I'm curious to know whether anybody has seen this kind of thing before?
>
> Thanks,
> Ben
>
> ps. This message is cross-posted to the Spatialite mailing list.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] False negatives from RTree

[Ben Harper]

Hi,
Has anybody ever seen anomalous behaviour on an R*Tree. Specifically, false 
negatives?
I'm using Spatialite 2.4.0 RC.

What I end up with is a table into which I can insert new geometry, but that 
geometry does not get retrieved by an appropriate RTree search.
Indeed, even a search which spans all of my geometry does not yield these newly 
inserted records.

For example:
select count(rowid) from idx_elec_lv_cable_geometry where xmax >= -9e30 AND 
xmin <= 9e30 AND ymax >= -9e90 AND ymin <= 9e30;
17235

select (count*) from elec_lv_cable;
17241

So I'm missing 6 records there.

I can't discern anything strange about the data, or my methods.
I'm curious to know whether anybody has seen this kind of thing before?

Thanks,
Ben

ps. This message is cross-posted to the Spatialite mailing list.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite IDE's

[Ben]

You don't mention which platform you're on, but for OS X there's a good 
comparison table of SQLite editors here:

http://www.barefeetware.com/sqlite/compare/?ml

- Ben


On 22 Mar 2011, at 18:46, Sam Carleton wrote:

> I am looking for a good SQLite IDE, SQLite Maestro looks like a good
> candidate with most all the features I need.  The price is good, too.
> The one feature I don't see is a tool that can do a diff on the DDL of
> two SQLite db's.  Does anyone know of any other SQLite IDE's that have
> that ability?
> 
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] ANN: Base 2.0, Mac SQLite GUI

[Ben]

Hi Tom,

Thanks for the kind words. Also thanks for the bug report, I'll investigate & 
fix that.

However, could I ask that any future comments/bugs are sent off-list please? Dr 
Hipp was kind enough to let me place an announcement on the mailing list, but I 
feel it's a bit impolite to continue discussions here. If anyone has any 
comments or problems, you can contact me off-list on i...@menial.co.uk, or 
using the from address in this message.

- Ben



On 20 Feb 2011, at 06:19, BareFeetWare wrote:

> Hi Ben,
> 
> In reply to your announcement of Base 2:
> 
>> Just a short message to announce that version 2.0 of Base, our Mac SQLite 
>> GUI is now available.
> 
> The new version looks great. Congrats :-)
> 
>> It's a major upgrade, the highlight of which is the ability for the app to 
>> view, create & alter tables with support for *all* table- and column-level 
>> constraints.
> 
> Neat.
> 
> When I choose "Alter Table", it nicely shows the list of columns, but 
> mistakenly also shows the constraints as if they were columns, with nothing 
> in the Constraints list. I tested a few schemas, including this one:
> 
> create table "Products Detail"
> (
>   Supplier integer
>   not null
>   references Suppliers (ID)
>   on delete restrict
>   on update cascade
> , Code text
>   not null
>   collate nocase
> , Detail text
>   not null
>   collate nocase
> , primary key (Supplier, Code)
> , foreign key (Supplier, Code)
>   references "Products Base" (Supplier, Code)
>   on delete cascade
>   on update cascade
> )
> ;
> 
> which shows in Base 2 as:
> 
>>> Columns:
>>> 
>>> Name  Type Constraints
>>> Supplier  integer  NF
>>> Code  test NC
>>> Detailtext NC
>>> primary   key
>>> 
>>> Constraints:
>>> 
>>> none listed
> 
> The "Alter" panel also shows an "Origin" column, which I think only makes 
> sense in a view.
> 
> When saving a changed table, Base seems to try to explicitly save any auto 
> indexes (which of course fails). For instance, when saving changes to the 
> above table, Base informed me:
> 
>>> There were problems re-creating table indexes. One or more indexes present 
>>> in the table before modification could not be recreated. Their original SQL 
>>> is listed below:
>>> 
>>> CREATE UNIQUE INDEX "sqlite_autoindex_Products Detail_1" ON "Products 
>>> Detail" ("Supplier", "Code");
> 
> I also failed to save the trigger, but that was probably due to it messing up 
> the create table columns (eg adding a column called "primary" etc).
> 
>> You can read the full announcement (with a link to release notes) here: 
>> http://menial.co.uk/2011/02/18/base-2-0/
> 
> I've updated the details for Base 2.0 on my SQLite GUI comparison page at:
> http://www.barefeetware.com/sqlite/compare/?ml
> 
> If there are any corrections or additions you'd like, please let me know.
> 
> Thanks,
> Tom
> BareFeetWare
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] ANN: Base 2.0, Mac SQLite GUI

[Ben]

Hi List,

Just a short message to announce that version 2.0 of Base, our Mac SQLite GUI 
is now available.

It's a major upgrade, the highlight of which is the ability for the app to 
view, create & alter tables with support for *all* table- and column-level 
constraints.

You can read the full announcement (with a link to release notes) here: 
http://menial.co.uk/2011/02/18/base-2-0/

I hope this is something of interest to list subscribers.

Ben Barnett
Menial

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

Re: [sqlite] Windows performance problems associated with malloc()

[Ben Harper]

This is low probability, but maybe the Fault Tolerant Heap is turned on for 
sqlite.exe?
I believe you'll see the exe mentioned in here if that is the case:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\FTH\State
FTH was introduced in Windows 7.

Ben

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

Re: [sqlite] BUSY on COMMIT when EXCLUSIVE is held is an unexpected result

[Ben Harper]

I'm using rollback - and no it's nothing like trying to commit inside a 
user-defined function. Very simple usage pattern.
I'll isolate the code so that it's small enough to post here. 
I just noticed that I do sqlite3_busy_timeout(DB,0) - if that's relevant.

Ben

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dan Kennedy
Sent: 17 December 2010 03:53 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] BUSY on COMMIT when EXCLUSIVE is held is an unexpected 
result

On 12/17/2010 07:54 PM, Ben Harper wrote:
> I have this case:
>
> Thread 1 Thread 2
> --
> BEGIN EXCLUSIVE
>   BEGIN EXCLUSIVE ->  BUSY
>   ... etc ...
>   BEGIN EXCLUSIVE ->  BUSY
> COMMIT
>   BEGIN EXCLUSIVE ->  OK
>   ...
>
>
> The commit statement of Thread 1 will sometimes fail with a BUSY error.
> A simple workaround is to try the commit a couple times if you receive
> a BUSY error. Having done this, I cannot get this test to fail anymore.

I'm not sure how you got this to happen. One way is to try the COMMIT
from within a user-defined function executed by a write statement - but
that's not terribly likely.

Were you using WAL mode? Or regular rollback mode (the default)?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] BUSY on COMMIT when EXCLUSIVE is held is an unexpected result

[Ben Harper]

I have this case:

Thread 1 Thread 2
--
BEGIN EXCLUSIVE
 BEGIN EXCLUSIVE -> BUSY
 ... etc ...
 BEGIN EXCLUSIVE -> BUSY
COMMIT
 BEGIN EXCLUSIVE -> OK
 ...


The commit statement of Thread 1 will sometimes fail with a BUSY error.
A simple workaround is to try the commit a couple times if you receive
a BUSY error. Having done this, I cannot get this test to fail anymore.

However, how many people realize this? It was, to me, an unexpected scenario.
Would it not be better if the DB tried harder to make that COMMIT succeed,
if it knows that it has an EXCLUSIVE lock? I realize this might be murky
territory, since raising some magic number just pushes the failure cases
further from the realm of 'likely to encounter in ad-hoc testing', but I
thought I should bring this up anyway.

Regards,
Ben

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

Re: [sqlite] WAL file size

[Ben Harper]

> threads reading and writing to the DB non-stop, there always will be at 
> least one reader who block the checkpoint from being completed, thus

It is in essence writing, not reading, that blocks the checkpointing.
If you stopped writing for a while, the checkpointer would catch up,
and it could rewind.

> I think this kind of a solution can work of me too (I dont want to block 
> the main thread EVER, and I have a steady stream of writes to the DB).
> Can you please elaborate how did you implemented your solution?

My specific application maintains a history of tiles that are touched by
a renderer. A tile record looks like this:

struct Tile { char Signature[20]; int64 X, int64 Y; }

I simply keep a queue, in essence:
vector Queue;

The UI thread adds tiles to the queue, and a background thread
fetches them off the queue, does the writing, and the checkpointing.
By having the writing and checkpointing on the same thread you can
guarantee that the WAL will rewind.

By the sounds of it, your application may be harder to abstract, the
way I do here, with a simple queue of instructions. Aside from rolling
WAL logs, I can't think of any other way that you could achieve the
responsiveness that you require. I have no contact with the SQLite
authors, but I wouldn't place my bets on rolling logs being available
any time soon.

Ben



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Yoni
Sent: 25 November 2010 11:08 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] WAL file size

Hi,

 > In which case the writer would have to block on the reader,
 > which is something that the current system doesn't suffer from.
I agree when we are talking about small systems, which does not 
read/write allot. But, on a larger scale system, who have a several 
threads reading and writing to the DB non-stop, there always will be at 
least one reader who block the checkpoint from being completed, thus 
making the WAL file grow forever (unless of course, you block during the 
checkpoint - but then you make your system unresponsive).

So, if you have a large scale system, you have two choices: block during 
the checkpoint, or suffer from huge WAL file (and a crash if the file is 
too big).

 > You can never be sure when is the right time to wrap around.
 > If you wrap around too early, you run the risk of hitting the wall
 > put up by the earliest reader, who is still using the end of the
 > WAL file.
If we choose a large enough size to be free at the beginning of the file 
(can be proportional to the WAL file size of just a constant), the 
chances that this will happen a very low. and even when this will 
happen, the write wont have to block the reader, it just have to force 
running checkpoint (while they are reading a page), and wait until they 
finish read the specific page (not the whole read transaction), since 
the next page they need to read is already in the main DB file.

 > The only solution around this would be to have more than 1 WAL file.
 > To avoid hitting this same problem with multiple WAL files, you'd
 > need to support an arbitrary N number of WAL files.
I think my solution will work too. on PostgreSQL they already attend the 
problem of how many WAL files should exist. see here: 
http://www.sql.org/sql-database/postgresql/manual/wal-configuration.html.

 > On one system of mine, where blocking is an issue, I
 > buffer up the write messages, and flush them on a background thread.
 > Of course this may not be practical for you...
I think this kind of a solution can work of me too (I dont want to block 
the main thread EVER, and I have a steady stream of writes to the DB).
Can you please elaborate how did you implemented your solution?

Yoni.


On 25/11/2010 8:42 AM, Ben Harper wrote:
> A cyclical WAL file has this problem:
>
> You can never be sure when is the right time to wrap around.
> If you wrap around too early, you run the risk of hitting the wall
> put up by the earliest reader, who is still using the end of the
> WAL file. In which case the writer would have to block on the reader,
> which is something that the current system doesn't suffer from.
> My guess is that a very high proportion of use cases would never
> suffer this issue, provided they set an appropriate wrap around size
> but this does place that burden on the implementer - of picking
> the right heuristics.
>
> It is a pathological case, but imagine an implementer never tests
> for this wraparound issue, and builds a deadlock into his app
> that arises when a writer blocks on a reader. This is the kind of
> unexpected behaviour that could really bite you.
>
> The only solution around this would be to have more than 1 WAL file.
> To avoid hitting this same problem with multiple WAL files, you'd
> need to support an arbitrary N num

Re: [sqlite] WAL file size

[Ben Harper]

A cyclical WAL file has this problem:

You can never be sure when is the right time to wrap around.
If you wrap around too early, you run the risk of hitting the wall
put up by the earliest reader, who is still using the end of the
WAL file. In which case the writer would have to block on the reader,
which is something that the current system doesn't suffer from.
My guess is that a very high proportion of use cases would never
suffer this issue, provided they set an appropriate wrap around size
but this does place that burden on the implementer - of picking
the right heuristics.

It is a pathological case, but imagine an implementer never tests
for this wraparound issue, and builds a deadlock into his app
that arises when a writer blocks on a reader. This is the kind of
unexpected behaviour that could really bite you.

The only solution around this would be to have more than 1 WAL file.
To avoid hitting this same problem with multiple WAL files, you'd
need to support an arbitrary N number of WAL files. And that,
undoubtedly, is a more complex implementation than what currently
exists. I imagine it's quite a task for the SQLite developers to
get 100% branch test coverage.

On one system of mine, where blocking is an issue, I
buffer up the write messages, and flush them on a background thread.
Of course this may not be practical for you...

Ben

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Yoni
Sent: 24 November 2010 04:46 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] WAL file size

Hi,

I am currently developing a system, which have the following requirements:
1. should be very fast and responsive
2. run forever (or at least a very long time) without restart.
3. have steady stream of writes to the DB.

Currently I am using sqlite with one sql connection, and I run sql 
checkpoint every once in a while (e.g. after 5 minutes, or after 1 
inserts to the DB) - I don't use auto checkpoint.
Since I run the checkpoint in the main thread, the system is 
unresponsive for a long time (can take from 400ms to 2ms!)

What I would like to do, is to run the checkpoint in another thread, so 
the main thread can keep working (and using sql), and do the long 
checkpoint work in the background.

The problem is that when I try to do it, the WAL file grows without limit.
I wrote a little program that do exactly this (insert rows as fast as C 
allows, and in the background run checkpoints).
The result (after 30 secs and 400K records) was: DB size 19MB and WAL 
size 451MB! (tried on linux Debian with sqlite 3.7.2, and on winXP with 
same sqlite version).

I think that the problem relies on the fact the during the checkpoint, I 
keep writing to the WAL file, thus the checkpoint can never get to the 
end of the file. And since WAL file is not cyclic, it will grow forever, 
leaving the most of the file unused (the beginning), and using only the 
end of the file for real data.

I found this thread 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg56074.html, 
discussing a similar problem, and also saw dan's commit 
(http://www.sqlite.org/src/info/72787c010c) approaching the same 
problem, but not solving it, since it requires to block all DB activity.

I think this can be fixed, if sqlite use a cyclic WAL file.
I found that PostgreSQL also bumped into this, and used multiple WAL 
files, in order to avoid this kind of problem 
(http://www.sql.org/sql-database/postgresql/manual/wal-implementation.html).
Now, we do not need multiple files to do it, we just need to write to 
the beginning of the WAL file, when we have enough space (e.g. at least 
16MB of unused space - configurable).
So the WAL file might look like this:

 logical end   logical start
  ||
  VV
--
header | committed data  | unused space   | committed data   |==>
--  |
^|
 |

when the data at the head of the file is logically at the end of it.

When the writing is too fast, eventually they will get to logical start 
of the file. in this point we can force a blocking checkpoint, since 
this situation is very rare.

This will allow limiting WAL file size, while avoiding blocking 
checkpoint, and will increase responsiveness significantly!

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


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

Re: [sqlite] virtual tables

[Ben Harper]

I have a database library that abstracts various DB types, such as DBF, 
shapefiles, CSV, PostGIS, etc.
I use the Sqlite virtual table mechanism to provide SQL functionality on top of 
any of these DB types.
Normally one would only need to do this for crude databases, such as a CSV 
file, but
the manner in which I cater for long transactions makes it necessary for me to 
strap Sqlite onto,
for instance, a modified Postgres table that has not yet been committed to the 
DB.

Ben

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of dcharno
Sent: 23 November 2010 05:00 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] virtual tables

What are some of the things that people use virtual tables for?  Are 
there any good example usages?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

[sqlite] How can xBestIndex discern 'a' = col COLLATE NOCASE vs 'a' = col

[Ben Harper]

I have a virtual table, but I cannot figure out how to discover the
collating sequence of a query expression passed to xBestIndex.

As far as I can tell, these three statements ...

SELECT * FROM tab WHERE col = 'val';
SELECT * FROM tab WHERE col = 'val' COLLATE BINARY;
SELECT * FROM tab WHERE col = 'val' COLLATE NOCASE;

... send identical data to xBestIndex.

Am I right if I conclude that the xBestIndex interface would need to be extended
in order to make it possible to distinguish between these three different 
queries?

Thanks,
Ben


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