Re: [sqlite] unicode case insensitive

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

Re: [sqlite] unicode case insensitive

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] quasi-bug related to locking, and attached databases....

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"

[sqlite] How to enable icu for sqlite shell

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] [regression] SQLite 3.8.7 causes Evolution to crash

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] unique with icu

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

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] Performing a SELECT on an enormous database...

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] Performing a SELECT on an enormous database...

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

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

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

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

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

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

[sqlite] Minor Documentation Issue - Virtual Tables

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

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

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

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] Please fix the EBCDIC support

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] Regression with sqlite 3.8.7

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

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

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] Inserting a row with all defaults set in table

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

Re: [sqlite] Question on locks

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

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] Inserting a row with all defaults set in table

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] Question on locks

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] Question on locks

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

[sqlite] Installing 3.8.7 gives 3.8.6

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] Installing 3.8.7 gives 3.8.6

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

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

> -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] Question on locks

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] Question on locks

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

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] Regression with sqlite 3.8.7

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] unique with icu

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] unique with icu

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] Performing a SELECT on an enormous database...

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] Installing 3.8.7 gives 3.8.6

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

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] Performing a SELECT on an enormous database...

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] unique with icu

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] quasi-bug related to locking, and attached databases....

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

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

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] FTS pagination

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