This is a SQL Script
/*
Application linking to SQLite using the Amalgation.
Build Tool: C++, Visual Studio 2012, Windows 7 64-Bit
The table schema and the query enclosed below are in use
for over one year and various SQLite versions.
After downloading and compiling in the SQLite 3.7.16.2, we have a problem.
The INSERT statement which takes only a few seconds with previous builds
now does not return, at least not within several minutes.
It's hard to debug the amalgation in Visual Studio but it looks like
SQLite would be caught in an internal loop inside the step() function call.
Replacing the latest version of SQLite with 3.7.15.1 (which was the one we
used before)
and re-compiling our application solves the problem. The INSERT works again
in a few seconds.
We can provide a sample database etc. on request.
*/
/* Create */
CREATE VIRTUAL TABLE md_fts_core USING
fts4(group_oid,tag_oid,file_oid,lang,data);
CREATE TABLE md_fts_core_tag (oid INTEGER PRIMARY KEY, type INTEGER);
CREATE INDEX idx_md_fts_core_tag_type ON md_fts_core_tag(type);
CREATE TABLE md_tag (oid INTEGER PRIMARY KEY, class INTEGER, group_oid
INTEGER, id TEXT, tag TEXT, idx INTEGER, dtype INTEGER, ntype TEXT, cnt
INTEGER, repeat INTEGER, flags INTEGER, FOREIGN KEY(group_oid) REFERENCES
md_tag_group(oid) ON DELETE CASCADE);
CREATE INDEX idx_md_tag_tag ON md_tag(tag);
CREATE TABLE md_tag_group (oid INTEGER PRIMARY KEY, src INTEGER, id TEXT);
CREATE TABLE md_tag_data (oid INTEGER, tag_oid INTEGER, tdata TEXT, rdata
TEXT, lang TEXT, flags INTEGER, FOREIGN KEY(tag_oid) REFERENCES md_tag(oid)
ON DELETE CASCADE);
CREATE INDEX idx_md_tag_data_flags ON md_tag_data(flags);
CREATE INDEX idx_md_tag_data_oid ON md_tag_data(oid);
CREATE INDEX idx_md_tag_data_oid_tag_oid ON md_tag_data(oid,tag_oid);
CREATE INDEX idx_md_tag_data_tag_oid ON md_tag_data(tag_oid);
/* This insert does not return (at least not within several minutes in
3.7.16.2, but takes about 5-10 seconds with build 3.7.15.1 */
INSERT INTO md_fts_core (group_oid,tag_oid,file_oid,lang,data)
SELECT
g.oid, d.tag_oid, d.oid, d.lang, d.tdata from md_tag_data d
INNER JOIN
md_tag t ON d.tag_oid = t.oid
INNER JOIN
md_tag_group g ON t.group_oid = g.oid
INNER JOIN
md_fts_core_tag ct ON ct.type = 0 AND ct.oid = d.tag_oid /* which
tags to include */
WHERE
d.oid IN
(1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3
4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59
,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,
85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107
,108)
AND d.tag_oid IN (2157,7309,16265,16579)
UNION
SELECT
/* We use group_concat to fold multiple values for one tag into one
value for FTS */
g.oid, d.tag_oid, d.oid, d.lang, group_concat(d.tdata,' ')
FROM
md_tag_data d
INNER JOIN
md_tag t ON d.tag_oid = t.oid
INNER JOIN
md_tag_group g ON t.group_oid = g.oid
INNER JOIN
md_fts_core_tag ct ON ct.type = 1 AND ct.oid = d.tag_oid /* which
tags to include */
WHERE
d.oid IN
(1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3
4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59
,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,
85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107
,108)
AND d.tag_oid IN (2157,7309,16265,16579)
GROUP BY
d.tag_oid,d.oid
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
