Re: [sqlite] [RFE bug] Improve error reporting for foreign keys
On Mon, 2014-10-27 at 15:49 +0900, Tristan Van Berkom wrote: > This is just a request-for-enhancement bug report, I've went to the > trouble or reproducing this problem in a simple test case and while > I probably wont be able to immediately benefit from an upstream fix > for this, I hope that this bug report will be perceived as helpful > and entered into your upstream bug tracking system (which seems to > not be public at this time). [snip] It seems that the sqlite-users mailing list does not take kindly to attachments, so I've posted the said reduced test case where it can be accessed: https://people.gnome.org/~tvb/test-fk-bug.sql Best Regards, -Tristan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [RFE bug] Improve error reporting for foreign keys
This is just a request-for-enhancement bug report, I've went to the trouble or reproducing this problem in a simple test case and while I probably wont be able to immediately benefit from an upstream fix for this, I hope that this bug report will be perceived as helpful and entered into your upstream bug tracking system (which seems to not be public at this time). This week I ran into a bug in my ~700 line complex schema which was very hard to find. After trying many things, including stepping through the locateFKeyIndex() function issuing the not-so-informative message "foreign key mismatch", I finally found that the error was coming from another table with an incorrectly defined foreign key. In this case, the FK was defined to refer to a non-unique column in the parent table. Suggestions to fix error reporting: o When foreign keys are enabled at CREATE TABLE time, it would be very helpful at this point to issue an error if a foreign key is declared which refers to a non-unique column (or compound FK referring to a non-unique set of keys in the parent table). o Alternatively, at least the error message issued from locateFKeyIndex() could be improved. I could almost contribute a patch for this but it would require I spend a hand full of hours understanding the Table & FKey structures in SQLite, I would suggest something to the effect of: sprintf (message, "Foreign key for column %s on table %s refers to" "non-unique column %s in parent table %s", column, table, parent_column, parent_table); And something a little more involved to construct a proper message for a similar error when it occurs on a compound FK. Either of these options, preferably both, would greatly improve usability of SQLite by saving people time debugging the schemas they create, and I'm sure that the effort required to enhance the error messages would cost very little effort to those who are already committers to the SQLite codebase. I've attached here a simplified test case from my schema, the bug in the schema occurs on the 'event_participant_activity' table, however the problem occurs when trying to delete rows from the 'event_participant' table (so it was difficult without any reliable error reporting to really find where the problem was coming from). Running the attached script should illustrate the issue, it also includes a fixed version of the schema (which defines a compound foreign key instead and fixes the problem so it refers to the correct and unique data). This is the first time I've reported any bug on SQLite and again, I hope that you maintainers perceive this as a helpful thing, I am not trying to lay blame here, I would consider this an enhancement class bug in any project that I maintain and would happily record it to our bug trackers, so I hope you see this the way it was intended. Best Regards, -Tristan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 and index with lower. > > > > Is it safe to add icu for existing db's? > > Yes. But once you've added it and used it there will be a problem if you > ever try to use the database without it. > > > Will it lead to any corruptions? > > No. > > 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
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
been inserted. So if you have a DEFAULT 'ABC', insert 100 rows, then
change the schema to DEFAULT 'DEF', then the 100 rows would still contain
'ABC'. If you add a new field to the database with a DEFAULT 'XYZ' then
that new field in ALL existing rows would contain 'XYZ' but 'ABC' or 'DEF'
would still exist.
The action SQLite takes when inserting physical data into the field is
based on what the current DEFAULT value is in the schema. If you have two
fields, one that has to be defined on an insert and one with a default
value, SQLite will basically change this:
*insert into MyTable (Letters) values ('ABC')*
into
*insert into MyTable (Letters, DefaultsToABC) values ('ABC','ABC')*
Think of it this way;
If you had a table with a default value, and you go to enter a value
manually that just happens to match the schemas default value, it wouldn't
make sense to change the value of that value in an existing row if you
change the default value in the schema. Based on the output of a SQL
command, you wouldn't be able to tell the difference if 'ABC' is the
default value or if 'ABC' was manually entered. So if you go and change
the value of the default and SQLite DID go and change the default values,
you'd get confused to why you have some rows with 'ABC' and some with 'DEF'.
On Sun, Oct 26, 2014 at 1:40 PM, J Decker wrote:
>
> That is interesting; so if I alter the default values, all rows that
> existed before the column added and the default change get the changed
> value? .
>
>
___
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
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
mentioned. This is my personal pref, as I've not a lot of experience with
FTS.
So using your sample data above, I designed this database;
CREATE TABLE [main].[RawData] (
[PathID] INTEGER PRIMARY KEY AUTOINCREMENT,
[Path] CHAR);
CREATE TABLE [main].[Keywords] (
[KeywordID] INTEGER PRIMARY KEY AUTOINCREMENT,
[Keyword] CHAR);
CREATE UNIQUE INDEX [main].[idxKeywords] ON [Keywords] ([Keyword] COLLATE
NOCASE);
CREATE TABLE [main].[DataKeywords] (
[PathID] INTEGER NOT NULL,
[KeywordID] integer NOT NULL,
CONSTRAINT [sqlite_autoindex_DataKeywords_1] PRIMARY KEY ([PathID],
[KeywordID]));
insert into [main].[RawData] values(1, 'ab/cd/gf');
insert into [main].[RawData] values(2, 'ab/qw/ert');
insert into [main].[RawData] values(3, 'ab/fgrd/ert');
insert into [main].[RawData] values(4, 'ab/foo/bar/fgr');
insert into [main].[RawData] values(5, 'ab/bar/foo/foobar/etc');
insert into [main].[RawData] values(6, 'ab/etc/d');
insert into [main].[Keywords] values(1, 'ab');
insert into [main].[Keywords] values(2, 'cd');
insert into [main].[Keywords] values(3, 'gf');
insert into [main].[Keywords] values(4, 'qw');
insert into [main].[Keywords] values(5, 'ert');
insert into [main].[Keywords] values(6, 'fgrd');
insert into [main].[Keywords] values(8, 'foo');
insert into [main].[Keywords] values(9, 'bar');
insert into [main].[Keywords] values(10, 'fgr');
insert into [main].[Keywords] values(11, 'foobar');
insert into [main].[Keywords] values(12, 'etc');
insert into [main].[Keywords] values(13, 'd');
insert into [main].[DataKeywords] values(1, 1);
insert into [main].[DataKeywords] values(1, 2);
insert into [main].[DataKeywords] values(1, 3);
insert into [main].[DataKeywords] values(2, 1);
insert into [main].[DataKeywords] values(2, 4);
insert into [main].[DataKeywords] values(2, 5);
insert into [main].[DataKeywords] values(3, 1);
insert into [main].[DataKeywords] values(3, 6);
insert into [main].[DataKeywords] values(3, 5);
insert into [main].[DataKeywords] values(4, 1);
insert into [main].[DataKeywords] values(4, 8);
insert into [main].[DataKeywords] values(4, 9);
insert into [main].[DataKeywords] values(4, 10);
insert into [main].[DataKeywords] values(5, 1);
insert into [main].[DataKeywords] values(5, 9);
insert into [main].[DataKeywords] values(5, 8);
insert into [main].[DataKeywords] values(5, 11);
insert into [main].[DataKeywords] values(5, 12);
insert into [main].[DataKeywords] values(6, 1);
insert into [main].[DataKeywords] values(6, 12);
insert into [main].[DataKeywords] values(6, 13);
select RawData.PathID, Path from RawData
join DataKeywords on RawData.PathID=DataKeywords.PathID
join Keywords on DataKeywords.KeywordID=Keywords.KeywordID
where keyword='ert'
PathID Path
-- ---
2 ab/qw/ert
3 ab/fgrd/ert
select RawData.PathID, Path from RawData
join DataKeywords on RawData.PathID=DataKeywords.PathID
join Keywords on DataKeywords.KeywordID=Keywords.KeywordID
where keyword='ab'
PathID Path
-- -
1 ab/cd/gf
2 ab/qw/ert
3 ab/fgrd/ert
4 ab/foo/bar/fgr
5 ab/bar/foo/foobar/etc
6 ab/etc/d
On Sun, Oct 26, 2014 at 8:27 AM, Baruch Burstein
wrote:
> 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 stripped off. Currently I am doing:
>
> substr(path, 4+instr(substr(path,4),'/'))
>
> But that seems long and probably inefficient.
> What is the best/simplest way to find the second occurrence of the '/' in a
> string?
>
> Also, a suggestion for an SQLite improvement: The builtin function instr()
> should have another form that takes 3 arguments, with the 3rd being either
> an offset from where to start the search, or which occurrence to search for
> (1st, 2nd, etc.)
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> ___
> 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] 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-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] 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 best/simplest way to find the second occurrence of the > '/' in a string? Fast is fast enough. If your SQL solves your problem acceptably fast, you're done. If you need something faster, you could implement support for regular expressions. Posix filename rules are very strict: the only disallowed characters are '/' and NUL. A simple regex returns the Nth occurence of a pattern. I would expect such a solution to move the performance constraint from the SQL interpreter (if that's where it is) to I/O. My example implementation is at http://www.schemamania.org/sql/sqlite/udf/regex.pdf Note however I wrote it as a user-defined function before I understood that REGEXP is a supported keyword in the SQLite syntax. If you implement a regexp() user function, you have access to it as an SQL operator, not just as a UDF. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 TABLE statement, the remaining columns' >> values are assumed to have their default values. > > That is interesting; so if I alter the default values, all rows that > existed before the column added and the default change get the changed > value? The ALTER TABLE command cannot change the default values, so this cannot happen. ... well, "cannot" is relative: $ sqlite3 test.db sqlite> CREATE TABLE t(x); sqlite> INSERT INTO t(x) VALUES (1); sqlite> ALTER TABLE t ADD y DEFAULT 42; sqlite> INSERT INTO t(x) VALUES (2); sqlite> SELECT * FROM t; 1|42 2|42 sqlite> PRAGMA writable_schema = on; sqlite> UPDATE sqlite_master SET sql = 'CREATE TABLE t(x, y DEFAULT 666)' WHERE name = 't' AND type = 'table'; sqlite> ^D $ sqlite3 test.db sqlite> SELECT * FROM t; 1|666 2|42 (The documentation of PRAGMA writable_schema says: "Warning: misuse of this pragma can easily result in a corrupt database file." Well, there you have your corruption.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 "alter table X add > > column"? I ask because 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 TABLE statement, the remaining columns' > values are assumed to have their default values. > > That is interesting; so if I alter the default values, all rows that existed before the column added and the default change get the changed value? . > When a row is (re)written, all columns are written (even those that > happen to have default values). > > > Regards, > Clemens > ___ > 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] 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 added the > extra column "elsewhere". It adds the extra column "nowhere". When SQLite reads a table row has fewer columns than in the CREATE TABLE statement, the remaining columns' values are assumed to have their default values. When a row is (re)written, all columns are written (even those that happen to have default values). Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 row with > both a BOOLEAN and a 1MB BLOB, you have to write 1MB in order to change the > value of the BOOLEAN. 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 added the extra column "elsewhere". It seems as a workaround, you could create a table with some metadata, then add each huge blob at the end of it with alter table. Charles ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 websites.db "PRAGMA busy_timeout=1500;insert into [$SITE] > (date,eu,us) values ($DATE,$DIFF,$DIFF2);" First, a database with 500 tables in is probably badly organised and will lead to slow operations (and therefore locks !). Any time you find yourself using a 500-value data variable as a table name (in your case, $SITE) you're probably doing something wrong. It would be better to organise your table so that the $SITE name is a column in a table: CREATE TABLE samples (sitename TEXT,date TEXT,eu ,us ) insert into samples (sitename,date,eu,us) values ($SITE,$DATE,$DIFF,$DIFF2); It also means that you never have to worry about $SITE containing a character that is not legal in a table name. However, in the rest of this reply I will assume that you have good reasons for not wanting to reorganise your data in this way. Second, you are opening and closing the database 500 times, and opening and closing the database requires a huge amount of unique access, and is therefore keeping it busy, and therefore locked for a long time. So instead of running the SQLite shell tool 500 times, run it just once. In your script which runs the shell tool, instead of putting the data directly into the database, have it write the data to a text file. This text file should read PRAGMA busy_timeout=1500; BEGIN; insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2); ... all your other INSERTs here ... END; Once you've written the whole text file you can tell the shell tool to execute it using the following command sqlite3 websites.db ".read commands.txt" All the INSERTs will happen while the file is open once, and the BEGIN/END means that they'll all happen in the same transaction, which will also make everything far faster. In SQLite it's transactions that take time, not individual commands. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 there will be a problem if you ever try to use the database without it. > Will it lead to any corruptions? No. Simon. ___ 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
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);
>
___
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
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 filters for all records containing ab and
cd (the / is treated as a separator for words by the fulltext tokenizer).
- The records returned by match mean that the words we searched for
occurred SOMEWHERE within the record. Since you’re only interested those
beginning with "ab/cd“ we must use another LIKE. But this is not a
peformance issue, because you effectively only apply the LIKE to the
records that MATCH returned
- The substr(…) is just applied to those records returned
It should be fairly efficient this way. A limitation is however that MATCH
doesn’t like special characters. If you have for example hyphens or spaces
in your path names. But even then it would work, you’d just need to build
an array of words, replacing any special chars with spaces. For example:
Path-_1/path%2/path3_
When we build an array by replacing all non-alphanumeric chars with empty
spaces we get:
"Path 1 path 2 path3 "
Notice we have two spaces in path 1 for „-_“. Normalize that as well,
removing duplicate as well as leading / trailing spaces:
"Path 1 path 2 path3“
Make an array of search words, breaking at the spaces:
[Path, 1, path, 2, path3]
Create your match statement with that:
MATCH "pathcolumn:Path AND pathcolumn:1, AND pathcolumn:path AND
pathcolumn:2 AND pathcolumn:path3“
Create your like statement, appending the % at the end, and normalizing
the input string (to make % inside your text not recognized by like but
treated as test really):
char *zSQL = sqlite3_mprintf("Path-_1/path%2/path3_", zText);
This will give some properly escaped string. Then use that string for your
LIKE statement, which will look something like (just quick example, not
sure if entirely correct)
LIKE „Path-_1/path\%2/path3_"
Regards
Ben
Am 26.10.14 13:27 schrieb "Baruch Burstein" unter :
>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 stripped off. Currently I am doing:
>
>substr(path, 4+instr(substr(path,4),'/'))
>
>But that seems long and probably inefficient.
>What is the best/simplest way to find the second occurrence of the '/' in
>a
>string?
>
>Also, a suggestion for an SQLite improvement: The builtin function instr()
>should have another form that takes 3 arguments, with the 3rd being either
>an offset from where to start the search, or which occurrence to search
>for
>(1st, 2nd, etc.)
>
>--
>˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>___
>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] 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) > > sqlite> EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE > activity_text_content MATCH 'x' AND rowid>1000 ORDER BY rowid ASC LIMIT 10; > 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4:ASC (~0 rows) > > sqlite> EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE > activity_text_content MATCH 'x' LIMIT 100; > 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4: (~0 rows) > > It almost looks like EXPLAIN ignores the second part where rowid is compared > or sorted. It doesn't change the way in which the database accesses the table, which implies that the rowid comparison is not using any index, i.e., the FTS module first computes the results, and then the rows with small rowid values are thrown away. Regards, Clemens ___ 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
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,
A.
2014-10-26 13:27 GMT+01:00 Baruch Burstein :
> 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 stripped off. Currently I am doing:
>
> substr(path, 4+instr(substr(path,4),'/'))
>
> But that seems long and probably inefficient.
> What is the best/simplest way to find the second occurrence of the '/' in a
> string?
>
> Also, a suggestion for an SQLite improvement: The builtin function instr()
> should have another form that takes 3 arguments, with the 3rd being either
> an offset from where to start the search, or which occurrence to search for
> (1st, 2nd, etc.)
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 stripped off. Currently I am doing:
substr(path, 4+instr(substr(path,4),'/'))
But that seems long and probably inefficient.
What is the best/simplest way to find the second occurrence of the '/' in a
string?
Also, a suggestion for an SQLite improvement: The builtin function instr()
should have another form that takes 3 arguments, with the 3rd being either
an offset from where to start the search, or which occurrence to search for
(1st, 2nd, etc.)
--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 frontend. I did disable part two, so 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 websites.db "PRAGMA busy_timeout=1500;insert into [$SITE] (date,eu,us) values ($DATE,$DIFF,$DIFF2);" But I still get frequent "Error: database is locked" , for further testing I did only run one script at one time, but the error still occurs at the same rate. Please advice. Regards On Sat, Oct 25, 2014 at 8:19 PM, Simon Slavin wrote: > > 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 > > this a mistake. And will the pragma for php eliminate locks ? > > You should definitely execute the PRAGMA as a separate command, not as > part of your SELECT command. > > I do not know for sure that, done as above, it will fix your lock. I'm > not sure why you are getting the locks. But it is the next step for you to > try, and if it doesn't fix them it will provide good diagnostic information. > > 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
