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

2019-10-27 Thread 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='consta

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

2019-10-20 Thread Ben Asher
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 &

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

2019-10-18 Thread Ben Asher
ad 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

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

2019-10-18 Thread Ben Asher
. 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 A

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

2019-10-18 Thread Ben Asher
ing 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. &

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

2019-10-18 Thread Ben Asher
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!

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

2019-10-18 Thread Ben Asher
(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 sta

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

2019-09-01 Thread Ben Kurtovic
qlite.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

2019-08-16 Thread 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 ma

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

2019-08-16 Thread Ben Asher
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

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

2019-06-25 Thread Ben Earhart
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

2019-02-08 Thread 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-c

[sqlite] ISO8601 vs Numeric Timestamp for Date Storage

2019-02-06 Thread Ben Asher
? 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] UUID extension

2018-11-17 Thread Ben Webber
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

2018-09-05 Thread Ben Caine
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?

2018-08-28 Thread 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, >

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

2018-08-28 Thread 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 col

[sqlite] virtual table module not found needs better logging

2018-08-03 Thread 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

[sqlite] documentation for sqlite3_index_info-ídxStr typo.

2018-07-04 Thread Ben Manashirov
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

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

2018-06-09 Thread 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 co

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

2018-06-09 Thread Ben Asher
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 sma

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

2018-06-09 Thread Ben Asher
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,

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

2018-06-09 Thread 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 valu

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

2018-06-09 Thread Ben Asher
(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: >

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

2018-06-09 Thread 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

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

2018-06-09 Thread 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

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

2018-06-09 Thread Ben Asher
) 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

2018-05-23 Thread Ben Asher
I see. Yep that clears things up. Thanks again! Ben On Wed, May 23, 2018 at 11:36 AM, Dan Kennedy <danielk1...@gmail.com> 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 >&g

Re: [sqlite] Tsan Bug in WAL mode

2018-05-23 Thread 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 <danielk1...@gmail.com> 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 >>

Re: [sqlite] Proper parameter usage in FTS5 queries

2018-05-23 Thread Ben Asher
. Ben On Wed, May 23, 2018 at 10:46 AM, Ben Asher <benashe...@gmail.com> 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?

Re: [sqlite] Tsan Bug in WAL mode

2018-05-23 Thread Ben Asher
: 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 <danielk1...@gmail.com> wrote: > On 05/23/2018 06:21 AM, Ben Asher wrote: > &g

Re: [sqlite] Proper parameter usage in FTS5 queries

2018-05-23 Thread Ben Asher
s 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 <danielk1...@gma

[sqlite] Tsan Bug in WAL mode

2018-05-22 Thread Ben Asher
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

[sqlite] Proper parameter usage in FTS5 queries

2018-05-22 Thread Ben Asher
sing 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

2017-04-04 Thread 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

Re: [sqlite] foreign key cardinality

2017-02-27 Thread 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,

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

2017-02-15 Thread 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

Re: [sqlite] Matching wildcards in Lemon

2017-01-25 Thread Ben
> On 24 Jan 2017, at 14:47, Richard Hipp <d...@sqlite.org> wrote: > > On 1/24/17, Ben <sqlite_l...@menial.co.uk> wrote: >> >> Just for a heads up, this isn't documented at: >> http://www.hwaci.com/sw/lemon/lemon.html >> <http://www.hwaci.com/sw/

Re: [sqlite] Matching wildcards in Lemon

2017-01-24 Thread 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 <d...@sqlite.org> wrote:

[sqlite] Matching wildcards in Lemon

2017-01-24 Thread Ben
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

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-23 Thread 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

Re: [sqlite] Date Formatting from Excel Float Date/Time Representation

2017-01-06 Thread 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;

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

2016-11-03 Thread Ben Hearsum
On Wed, Nov 02, 2016 at 11:30:37PM -0400, Richard Hipp wrote: > On 11/2/16, Ben Hearsum <bhear...@mozilla.com> 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

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

2016-11-02 Thread Ben Hearsum
to the corruption?), there are no duplicate guids in moz_places. 2016-11-02 17:27 GMT-04:00 Richard Hipp <d...@sqlite.org>: > On 11/2/16, Ben Hearsum <bhear...@mozilla.com> wrote: > > > > I then did some inserts, and discovered that the order in which the rows > are > &

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

2016-11-02 Thread Ben Hearsum
sum; 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.or

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

2016-10-17 Thread Ben Newberg
it happened. On Oct 17, 2016 5:21 PM, "Kees Nuyt" <k.n...@zonnet.nl> wrote: > On Mon, 17 Oct 2016 15:40:44 -0500, Ben Newberg > <ben.newb...@gmail.com> wrote: > > > But I've seen some strange things when messing with pragma > writable_schema. > > It appears

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

2016-10-17 Thread 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

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

2016-03-10 Thread Ben Stover
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?

2016-03-09 Thread Ben Stover
\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

2015-08-06 Thread 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,

[sqlite] Determine query type

2015-08-06 Thread 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

[sqlite] CSV excel import

2015-08-01 Thread 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

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

2015-07-11 Thread Ben Clewett
. 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 &g

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

2015-07-10 Thread Ben Clewett
. 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

2015-06-21 Thread Ben Weisburd
? 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

2014-11-11 Thread 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" <d...@sqlite.org> wrote: > On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik <i...@tandetnik.org> > wrote: > >

Re: [sqlite] Triggers and CTE's

2014-11-11 Thread 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

[sqlite] Triggers and CTE's

2014-11-11 Thread Ben Newberg
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 sq

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

2014-10-16 Thread 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

[sqlite] sqlite data source not available

2014-10-10 Thread 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] sqlite data source not available

2014-10-09 Thread 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

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

2014-08-13 Thread Ben
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 <kmedc...@dessus.com> wrote: > > I don't

[sqlite] SQLite relative dates and join query help

2014-08-12 Thread Ben
he 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

2014-04-06 Thread Ben
On 6 Apr 2014, at 21:28, Richard Hipp <d...@sqlite.org> wrote: > On Sun, Apr 6, 2014 at 4:18 PM, Ben <sqlite_l...@menial.co.uk> wrote: > >> Hi all, >> >> Is there any advantage to using the encoding specific functions from the C >> api? >> >&g

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

2014-03-21 Thread Ben Peng
<danielk1...@gmail.com> 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

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

2014-03-21 Thread Ben Peng
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: > &

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

2014-03-21 Thread 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

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

2013-11-08 Thread Ben
e annoying and still has some bugs, but works. - Ben On 7 Nov 2013, at 17:42, L. Wood <lwoo...@live.com> 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 App

[sqlite] Compile option documentation conflict

2013-07-23 Thread Ben
tml 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 jus

Re: [sqlite] GUI for SQLite

2013-06-27 Thread 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" <jbr...@utk.edu> wrote: > fyi > > http://www.barefeetware.com/sqli

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

2012-11-21 Thread Ben
pplication 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:80

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

2012-10-19 Thread Ben Morris
, 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 <nick.s...@citysync.co.uk> wrote: > Richard Hipp wrote: > > Ben Morris <magospiet...@gmail.com>

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

2012-10-19 Thread 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

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

2012-10-18 Thread Ben
On 18 Oct 2012, at 20:07, Richard Hipp <d...@sqlite.org> wrote: > On Thu, Oct 18, 2012 at 3:03 PM, Ben <sqlite_l...@menial.co.uk> wrote: > >> Hi list, >> >> I'm having a little trouble getting my head around memory management >> within a Lem

[sqlite] Lemon: Non-terminal destructors and cleanup

2012-10-18 Thread Ben
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] Problem in sqlite3VXPrintf due to floating point optimization

2012-09-15 Thread 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

[sqlite] SQLite feature request: strict quoting pragma

2011-10-04 Thread 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

[sqlite] Shapefile 2.0

2011-09-16 Thread 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

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

2011-09-09 Thread Ben Harper
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

[sqlite] Patch to pass collation through to Virtual Tables

2011-09-09 Thread Ben Harper
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] LIKE operator on virtual table assumes ASCII collation

2011-09-09 Thread Ben Harper
is 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

2011-09-07 Thread 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

Re: [sqlite] caveat on blanket UPDATE triggers

2011-07-14 Thread 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

[sqlite] caveat on blanket UPDATE triggers

2011-07-14 Thread Ben Harper
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

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

2011-07-07 Thread 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

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

2011-07-07 Thread Ben Harper
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

2011-07-05 Thread Ben Harper
To: General Discussion of SQLite Database Subject: Re: [sqlite] False negatives from RTree On Tue, Jul 5, 2011 at 8:55 AM, Ben Harper <b...@imqs.co.za> wrote: > Hi, > Has anybody ever seen anomalous behaviour on an R*Tree. Specifically, false > negatives? > I'm using Spatialite 2.4.0

Re: [sqlite] False negatives from RTree

2011-07-05 Thread Ben Harper
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 <b...@imqs.co.za> wrote: > Hi, > Has anybody ever s

[sqlite] False negatives from RTree

2011-07-05 Thread Ben Harper
re. 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

Re: [sqlite] SQLite IDE's

2011-03-22 Thread 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 g

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

2011-02-19 Thread Ben
inue 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: > >>

[sqlite] ANN: Base 2.0, Mac SQLite GUI

2011-02-19 Thread Ben
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

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

2010-12-18 Thread 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

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

2010-12-17 Thread 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] BUSY on COMMIT when EXCLUSIVE is held is an unexpected result

2010-12-17 Thread Ben Harper
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

2010-11-25 Thread Ben Harper
u 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: 2

Re: [sqlite] WAL file size

2010-11-24 Thread Ben Harper
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

Re: [sqlite] virtual tables

2010-11-22 Thread Ben Harper
, 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

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

2010-11-19 Thread Ben Harper
= '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

[sqlite] WAL hash collisions

2010-11-06 Thread Ben Harper
In wal.c, it reads "8 or 10 comparisons (on average) suffice to either locate a frame in the WAL or to establish that the frame does not exist in the WAL". I'm wondering -- how often does it occur that only a small subset of pages is written to again and again, in sequence, such that the WAL

  1   2   3   >