Re: [sqlite] unique with icu

Thanks a million Simon :-) On Sun, Oct 26, 2014 at 8:11 PM, Simon Slavin wrote: > > On 26 Oct 2014, at 6:00am, dd wrote: > > > Application using sqlite database without icu extension. I am planning to > > add icu extension. for schema, add new column

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

If you're asking "if a field on a row contains a value that was previously defined a value due to the DEFAULT value in the schema, would this value be changed if the in the schema later changed", then no, it doesn't change. There is no reference to the default value in the schema once the data has

Re: [sqlite] Finding second occurrence of character in string

Another method beyond what was suggested above/below would be that since the / could be considered a delimiter, you could consider each field a word and insert each word into a separate table and index each word. Have another table reference the indexed word to match whatever table you've

Re: [sqlite] Remove me from this

On Mon, 27 Oct 2014 07:20:04 +1100, Isaac Faulkner wrote: > > >I did not sign up for this someone hacked my email stop spamming me please Visit the link below, make it send your password, then login and unsubscribe. Regards, Kees Nuyt

[sqlite] Remove me from this

I did not sign up for this someone hacked my email stop spamming me please ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Finding second occurrence of character in string

On Sun, 26 Oct 2014 15:27:24 +0300 Baruch Burstein wrote: > I need to get the path with the > first 2 parts stripped off. Currently I am doing: > > substr(path, 4+instr(substr(path,4),'/')) > > But that seems long and probably inefficient. > What is the

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

J Decker wrote: > On Sun, Oct 26, 2014 at 10:32 AM, Clemens Ladisch wrote: >> Charles Samuels wrote: >>> it was my understanding that alter table added the extra column "elsewhere". >> >> It adds the extra column "nowhere". When SQLite reads a table row has >> fewer columns than in the CREATE

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

On Sun, Oct 26, 2014 at 10:32 AM, Clemens Ladisch wrote: > Charles Samuels wrote: > > On Saturday, October 25, 2014 05:31:35 AM Richard Hipp wrote: > >> However, when updating a row, SQLite rewrites the entire row. > > > > Does this still apply if the column was added due to

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

Charles Samuels wrote: > On Saturday, October 25, 2014 05:31:35 AM Richard Hipp wrote: >> However, when updating a row, SQLite rewrites the entire row. > > Does this still apply if the column was added due to "alter table X add > column"? I ask because it was my understanding that alter table

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

Richard, On Saturday, October 25, 2014 05:31:35 AM Richard Hipp wrote: > However, when updating a row, SQLite rewrites the entire row. (It has to, > because of the use of variable-width encodings, since a change to any field > effects the location of all subsequent fields.) So if you have a

Re: [sqlite] Question on locks

On 26 Oct 2014, at 9:27am, Ali Jawad wrote: > right now this is all about the write process to > the database. 4 scripts run simultaneously, writing 500 entries each > through a while loop to 500 tables each every 10 minutes. > > The relevant part is here > > sqlite3

Re: [sqlite] unique with icu

On 26 Oct 2014, at 6:00am, dd wrote: > Application using sqlite database without icu extension. I am planning to > add icu extension. for schema, add new column and index with lower. > > Is it safe to add icu for existing db's? Yes. But once you've added it and used it

Re: [sqlite] Finding second occurrence of character in string

Sorry, this should rather be something like sqlite3_mprintf("%q", "Path-_1/path%2/path3_³); https://www.sqlite.org/c3ref/mprintf.html Am 26.10.14 14:57 schrieb "Stadin, Benjamin" unter : >char *zSQL = sqlite3_mprintf("Path-_1/path%2/path3_", zText); >

Re: [sqlite] Finding second occurrence of character in string

Hi, One possible way could be to combine this with FTS4 (with parenthesis support enabled) and a LIKE clause: SELECT substr(path, 4+instr(substr(path,4),'/‚)) as relativepath FROM table WHERE table MATCH "path:ab AND path:cd“ AND path LIKE "ab/cd%“ How it works: - The match clause efficiently

Re: [sqlite] FTS pagination

supermariobros wrote: > 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) > >

Re: [sqlite] Finding second occurrence of character in string

Hi, I don't know if the SQLite SQL function are coming from a sql specs standard. I guess however that a better usable string function for manage paths is one function that retrieve the last occurrence of a string. Because very often the need is to extract the last part of a filepath. my 2ct,

[sqlite] Finding second occurrence of character in string

Hi! I have a column which represents a file path: ab/cd/gf ab/qw/ert ab/fgrd/ert ab/foo/bar/fgr ab/bar/foo/foobar/etc ab/etc/d etc... I happen to know in my case that the first part of the path is a certain fixed string ('ab' in the above example). I need to get the path with the first 2 parts

Re: [sqlite] Question on locks

Thanks for the input so far. To clarify the whole setup works like this 1- bash scripts run every 10 minutes and generate information that is inserted into tables in a sqlite db, the tables are only accessed once simultaneously 2- PHP scripts read from those tables to display information on

Re: [sqlite] unique with icu

Thanks Richard/Joseph. Application using sqlite database without icu extension. I am planning to add icu extension. for schema, add new column and index with lower. Is it safe to add icu for existing db's? Will it lead to any corruptions? On Sun, Oct 26, 2014 at 1:22 AM, Joseph R. Justice