Re: [sqlite] FTS pagination

2014-10-25 Thread supermariobros
Well, they all give exactly the same output. sqlite> EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE activity_text_content MATCH 'x' ORDER BY rowid ASC LIMIT 100; 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4:ASC (~0 rows) sqlite> EXPLAIN QUERY PLAN SELECT rowid

[sqlite] bug, or misuse? crash using sqlite3_mutex_alloc()...

2014-10-25 Thread dave
I am doing the following, which causes a crash: sqlite3_mutex_enter(sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER)); The crash occus if this is the first sqlite call I make. Looking at the source, there is a line: if( id<=SQLITE_MUTEX_RECURSIVE && sqlite3_initialize() ) return 0; Since

Re: [sqlite] quasi-bug related to locking, and attached databases....

2014-10-25 Thread dave
> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephan Beal > Sent: Saturday, October 25, 2014 3:32 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] quasi-bug related to locking,and > attached

Re: [sqlite] unique with icu

2014-10-25 Thread Joseph R. Justice
On Sat, Oct 25, 2014 at 3:44 PM, Richard Hipp wrote: > On Sat, Oct 25, 2014 at 7:09 AM, dd wrote: > > > Hi, > > > > icu enabled for sqlite. I didn't do any custom collations/like > operator. > > > > CREATE TABLE test(id integer primary key

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Ross Altman
Oh, I see! Thanks for the clarification, Simon! I wasn't aware that there was a command for indexing. That should definitely help a lot. On Sat, Oct 25, 2014 at 4:32 PM, Simon Slavin wrote: > > On 25 Oct 2014, at 9:07pm, Ross Altman wrote: > > >

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Simon Slavin
On 25 Oct 2014, at 9:07pm, Ross Altman wrote: > Thanks for all the responses. The small integer column H11 comes before the > large string column NVERTS, so doesn't that mean SQLite is only loading the > minimum required while filtering? If that's the case then I don't

Re: [sqlite] Installing 3.8.7 gives 3.8.6

2014-10-25 Thread Arnaud Meuret
Interesting, thanks. It is notorious that whoever maintains the RedHat package curiouly decided a few years ago to break this and realese "a 0.8.6" version that does not include sqlite3_prepare_v2() creating a lot of confusion. Can you confirm that it is safe to nuke this crippled version

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Ross Altman
Thanks for all the responses. The small integer column H11 comes before the large string column NVERTS, so doesn't that mean SQLite is only loading the minimum required while filtering? If that's the case then I don't understand why it's taking up to 15 minutes to load. Also, yes this database is

Re: [sqlite] unique with icu

2014-10-25 Thread Richard Hipp
On Sat, Oct 25, 2014 at 7:09 AM, dd wrote: > Hi, > > icu enabled for sqlite. I didn't do any custom collations/like operator. > > CREATE TABLE test(id integer primary key autoincrement, t text collate > nocase, unique(t)); > > Case 1: When I try to insert 'd' and 'D',

Re: [sqlite] unique with icu

2014-10-25 Thread dd
any inputs. On Sat, Oct 25, 2014 at 3:09 PM, dd wrote: > Hi, > > icu enabled for sqlite. I didn't do any custom collations/like operator. > > CREATE TABLE test(id integer primary key autoincrement, t text collate > nocase, unique(t)); > > Case 1: When I try to insert

Re: [sqlite] Regression with sqlite 3.8.7

2014-10-25 Thread Luigi Iemma
thanks for answers Luigi. 2014-10-25 15:46 GMT+02:00 Richard Hipp : > On Thu, Oct 23, 2014 at 11:55 AM, Luigi Iemma > wrote: > > > Hi, > > > > SELECT TdoIdoc,RdoCart,RdoQuat > > FROM Tesdoc > > INNER JOIN Rigdoc ON RdoIdoc BETWEEN TdoIdoc*1000 AND >

Re: [sqlite] Question on locks

2014-10-25 Thread Simon Slavin
On 25 Oct 2014, at 7:16pm, Ali Jawad wrote: > Thanks Simon, the create process is a one off. As for the table name I did > use this approach as to not accumulate too much data in one table and > instead split the data in multiple tables. From a design POV in sqlite is >

Re: [sqlite] Question on locks

2014-10-25 Thread Ali Jawad
Thanks Simon, the create process is a one off. As for the table name I did use this approach as to not accumulate too much data in one table and instead split the data in multiple tables. From a design POV in sqlite is this a mistake. And will the pragma for php eliminate locks ? On Oct 25, 2014

Re: [sqlite] Question on locks

2014-10-25 Thread Simon Slavin
On 25 Oct 2014, at 3:31pm, Ali Jawad wrote: > bash script > > sqlite3 websites.db "PRAGMA busy_timeout=1500;CREATE TABLE [$SITE] (DATE > INT ,EU INT , US INT);" Creating and destroying tables always involves a long lock. > php script > > $ret = $db->query("PRAGMA

Re: [sqlite] How to enable icu for sqlite shell

2014-10-25 Thread dave
> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dd > Sent: Saturday, October 25, 2014 3:59 AM > To: General Discussion of SQLite Database > Subject: [sqlite] How to enable icu for sqlite shell > > > Hi, > > I need

Re: [sqlite] Installing 3.8.7 gives 3.8.6

2014-10-25 Thread Dan Kennedy
On 10/25/2014 09:36 PM, Arnaud Meuret wrote: Hi everyone, On a fresh CentOS 5, compiling SQLite using the [current tarball][1] for 3.8.7, the version being installed ends up in `/usr/local/lib` as **3.8.6**: bash-3.2# make install make[1]: Entering directory

[sqlite] Installing 3.8.7 gives 3.8.6

2014-10-25 Thread Arnaud Meuret
Hi everyone, On a fresh CentOS 5, compiling SQLite using the [current tarball][1] for 3.8.7, the version being installed ends up in `/usr/local/lib` as **3.8.6**: bash-3.2# make install make[1]: Entering directory `/mongrel2/sqlite-autoconf-3080700' /bin/mkdir -p

Re: [sqlite] Question on locks

2014-10-25 Thread Ali Jawad
Yes please see examples "these are in for loops, but these are the only occurances of Sqlite in the files in question": bash script sqlite3 websites.db "PRAGMA busy_timeout=1500;CREATE TABLE [$SITE] (DATE INT ,EU INT , US INT);" php script $ret = $db->query("PRAGMA busy_timeout=1500;SELECT

Re: [sqlite] Question on locks

2014-10-25 Thread Stephen Chrzanowski
You did that per connection? On Sat, Oct 25, 2014 at 10:02 AM, Ali Jawad wrote: > Thanks for the input, I did add PRAGMA busy_timeout=1500; before any query > but I am still getting loads of locked database errors, please advice >

Re: [sqlite] Inserting a row with all defaults set in table

2014-10-25 Thread Stephen Chrzanowski
Perfect. Thanks guys. On Sat, Oct 25, 2014 at 10:03 AM, Simon Davies wrote: > On 25 October 2014 14:49, Stephen Chrzanowski wrote: > > I've got a table that has defaults set for all fields > > > > CREATE TABLE [tEvents] ( > > [EventID]

Re: [sqlite] Inserting a row with all defaults set in table

2014-10-25 Thread Simon Davies
On 25 October 2014 14:49, Stephen Chrzanowski wrote: > I've got a table that has defaults set for all fields > > CREATE TABLE [tEvents] ( > [EventID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > [Airline] CHAR DEFAULT '', > [TicketID] INTEGER DEFAULT 0, > [Resolved]

Re: [sqlite] Question on locks

2014-10-25 Thread Ali Jawad
Thanks for the input, I did add PRAGMA busy_timeout=1500; before any query but I am still getting loads of locked database errors, please advice On Mon, Oct 20, 2014 at 3:44 AM, Keith Medcalf wrote: > > Actually, you should set the timeout for each connection. The computer

Re: [sqlite] Inserting a row with all defaults set in table

2014-10-25 Thread Richard Hipp
On Sat, Oct 25, 2014 at 9:49 AM, Stephen Chrzanowski wrote: > I've got a table that has defaults set for all fields > > CREATE TABLE [tEvents] ( > [EventID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > [Airline] CHAR DEFAULT '', > [TicketID] INTEGER DEFAULT 0, >

[sqlite] Inserting a row with all defaults set in table

2014-10-25 Thread Stephen Chrzanowski
I've got a table that has defaults set for all fields CREATE TABLE [tEvents] ( [EventID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Airline] CHAR DEFAULT '', [TicketID] INTEGER DEFAULT 0, [Resolved] BOOL DEFAULT 0); Seems to me it'd be a bit redundant to do an "insert into tEvents

Re: [sqlite] Regression with sqlite 3.8.7

2014-10-25 Thread Richard Hipp
On Thu, Oct 23, 2014 at 11:55 AM, Luigi Iemma wrote: > Hi, > > SELECT TdoIdoc,RdoCart,RdoQuat > FROM Tesdoc > INNER JOIN Rigdoc ON RdoIdoc BETWEEN TdoIdoc*1000 AND > TdoIdoc*1000+999 > WHERE TdoTipo=60 AND TdoAnno BETWEEN 2014 AND 2014 > GROUP BY TdoIdoc > >

Re: [sqlite] Please fix the EBCDIC support

2014-10-25 Thread mario@tiscali
Hello, I just found this thread and I consider it very interesting. John's porting of SQLite to z/OS is a very nice option, and as he mentioned before, the modifications to get to it were minor, if any. As far as I understand though, the result is a database which stores numbers as big-endian

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Richard Hipp
On Sat, Oct 25, 2014 at 9:14 AM, Stephen Chrzanowski wrote: > Ahh.. Thanks Richard. So if you were to have blobs live at the front of > the row, it'll have to read through that blob to get that byte in the next > field, correct? > > Correct. -- D. Richard Hipp

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Stephen Chrzanowski
Ahh.. Thanks Richard. So if you were to have blobs live at the front of the row, it'll have to read through that blob to get that byte in the next field, correct? On Sat, Oct 25, 2014 at 8:31 AM, Richard Hipp wrote: > On Sat, Oct 25, 2014 at 8:15 AM, Stephen Chrzanowski

[sqlite] Minor Documentation Issue - Virtual Tables

2014-10-25 Thread Stephen Chrzanowski
https://www.sqlite.org/vtab.html I was trying to understand what Virtual Tables are, and how they'd benefit me in a new app I'm building, but I noticed that the numbering system on the page is incorrect, or, they slipped something into my coffee this AM. (I should thank them if they did!) The

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Richard Hipp
On Sat, Oct 25, 2014 at 8:15 AM, Stephen Chrzanowski wrote: > > SQLite apparently will load an entire row of data out of the database, even > if the query doesn't need the additional data, so if you have a huge blob, > it'll read in that entire blob then toss the blob to

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Robert Hairgrove
On Fri, 2014-10-24 at 19:09 -0400, Ross Altman wrote: > I'm currently working with a pretty gigantic database (116 Gb at the > moment, and growing). Performing a simple SELECT routine with one filter > takes between 7 and 15 minutes, which is starting to become a problem. The > command I'm using

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Teg
Hello Ross, Is H11 indexed? 15 minutes suggests to me that it's doing a linear scan and you need an index. I have databases that big and performance is pretty decent most of the time. I'm sure the boys here will ask you for a schema probably an "explain" on the query. C Friday, October

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Stephen Chrzanowski
Make sure H11 has an index on it. Also ensure that ToricCY doesn't have blobs attached to it. Throw the blobs into a different table and use a 1:1 relationship to link them, then only pull the blobs when needed. SQLite apparently will load an entire row of data out of the database, even if the

Re: [sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Alessandro Marzocchi
Do you have an index on that column? Il 25/ott/2014 14:03 "Ross Altman" ha scritto: > Hi guys, > > I'm currently working with a pretty gigantic database (116 Gb at the > moment, and growing). Performing a simple SELECT routine with one filter > takes between 7 and 15

[sqlite] Performing a SELECT on an enormous database...

2014-10-25 Thread Ross Altman
Hi guys, I'm currently working with a pretty gigantic database (116 Gb at the moment, and growing). Performing a simple SELECT routine with one filter takes between 7 and 15 minutes, which is starting to become a problem. The command I'm using is the following: SELECT NVERTS FROM ToricCY WHERE

Re: [sqlite] [regression] SQLite 3.8.7 causes Evolution to crash

2014-10-25 Thread Richard Hipp
On Sat, Oct 25, 2014 at 6:15 AM, Paul Menzel < paulepan...@users.sourceforge.net> wrote: > > thank you a lot for the analysis leading to a solution. > > Was it just bad luck that such a change to use different code paths is > done for a bug fix release (3.8.6 to 3.8.7)? Or don’t you use semantic

[sqlite] unique with icu

2014-10-25 Thread dd
Hi, icu enabled for sqlite. I didn't do any custom collations/like operator. CREATE TABLE test(id integer primary key autoincrement, t text collate nocase, unique(t)); Case 1: When I try to insert 'd' and 'D', throwing constraint violation. (SUCCESS) Case 2: When I try to insert 'ö' and

Re: [sqlite] [regression] SQLite 3.8.7 causes Evolution to crash

2014-10-25 Thread Paul Menzel
Dear Richard, Am Mittwoch, den 22.10.2014, 21:53 -0400 schrieb Richard Hipp: > On Wed, Oct 22, 2014 at 5:14 PM, Paul Menzel wrote: > > after the upgrade of libsqlite3 from 3.8.6 to 3.8.7 Evolution crashes > > with a segmentation fault. > > > > pool[6371]: segfault at 0 ip (null) sp

[sqlite] How to enable icu for sqlite shell

2014-10-25 Thread dd
Hi, I need to verify unicode stuff with shell. Is it possible? Tried with .load icu, thrown "icu.so cannot open...". Where can I find icu.so on sqlite.org? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] quasi-bug related to locking, and attached databases....

2014-10-25 Thread Stephan Beal
On Fri, Oct 24, 2014 at 11:16 PM, Simon Slavin wrote: > Which version of SQLite are you using ? > What operating system are you using (including which version) ? > What formats are the volumes those files are stored on ? > Simon, FYI: this is the "'main' db aliasing"

Re: [sqlite] unicode case insensitive

2014-10-25 Thread dd
typo: *I am summarizing options to support unicode* case-insensitive*: On Sat, Oct 25, 2014 at 10:34 AM, dd wrote: > I am summarizing options to support unicode case-sensitive: > > 1. Richard Hipp: icu ext > 2. Aleksey Tulinov:

Re: [sqlite] unicode case insensitive

2014-10-25 Thread dd
I am summarizing options to support unicode case-sensitive: 1. Richard Hipp: icu ext 2. Aleksey Tulinov: https://bitbucket.org/alekseyt/nunicode#markdown- header-sqlite3-extension 3. Grey's suggestion: custom collation Please add in options list if I missed. I don't have experience in