On Tue Feb 25, 2014 at 05:24:55PM +0100, Mark Lawrence wrote:
> On Tue Feb 25, 2014 at 09:37:41AM -0500, Richard Hipp wrote:
> > Can you please send the database schema, and possibly some test data?
>
> Attached is an SQL file containing enough to reproduce the issue on my
> system:
Righto, now included *inline* is the following:
* Schema for the tables
* Rows for the tables
* 4 queries demonstrating the issue:
1. Single SELECT query #1
2. Single SELECT query #2
3. UNION ALL query of #1 and #2
4. UNION ALL query of #1 and #2 plus ORDER BY
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE updates (
id integer NOT NULL PRIMARY KEY,
uuid char(40) NOT NULL UNIQUE DEFAULT '',
parent_id integer,
itime integer,
mtime integer NOT NULL
DEFAULT (strftime('%s','now')),
mtimetz integer NOT NULL
DEFAULT (strftime('%s','now','localtime') - strftime('%s','now')),
path varchar,
author varchar(255) NOT NULL,
email varchar(255) NOT NULL,
lang varchar(8) NOT NULL DEFAULT 'en',
message text NOT NULL DEFAULT '',
prefix varchar COLLATE NOCASE,
FOREIGN KEY(parent_id) REFERENCES updates(id)
ON DELETE CASCADE
);
CREATE TABLE topics (
id integer NOT NULL PRIMARY KEY,
uuid char(40) NOT NULL UNIQUE DEFAULT '',
first_update_id INTEGER NOT NULL,
kind varchar NOT NULL,
ctime integer NOT NULL,
ctimetz integer NOT NULL,
mtime integer NOT NULL,
mtimetz integer NOT NULL,
lang varchar(8) NOT NULL DEFAULT 'en',
hash varchar,
num_updates integer,
FOREIGN KEY(first_update_id) REFERENCES updates(id) ON DELETE CASCADE
);
CREATE TABLE projects (
id integer NOT NULL PRIMARY KEY,
parent_id integer,
name varchar(40) NOT NULL,
title varchar(1024) NOT NULL,
path varchar collate nocase,
status_id integer NOT NULL DEFAULT -1,
hash varchar,
num_updates integer,
FOREIGN KEY(id) REFERENCES topics(id)
ON DELETE CASCADE
FOREIGN KEY(parent_id) REFERENCES projects(id)
ON DELETE CASCADE,
FOREIGN KEY(status_id,id) REFERENCES project_status(id,project_id)
DEFERRABLE INITIALLY DEFERRED
);
CREATE TABLE project_updates (
id integer NOT NULL PRIMARY KEY DEFAULT (nextval('update_order')),
update_id integer NOT NULL,
project_id integer NOT NULL,
new integer,
parent_id integer,
name varchar(40),
title varchar(1024),
status_id integer,
FOREIGN KEY(update_id) REFERENCES updates(id)
ON DELETE CASCADE,
FOREIGN KEY(project_id) REFERENCES projects(id)
ON DELETE CASCADE
FOREIGN KEY(status_id,project_id) REFERENCES project_status(id,project_id)
ON DELETE CASCADE
) WITHOUT ROWID;
CREATE TABLE project_status (
id integer NOT NULL PRIMARY KEY,
project_id integer NOT NULL,
status varchar(40) NOT NULL,
rank integer NOT NULL,
UNIQUE (project_id,status),
UNIQUE (id,project_id), -- projects references this
FOREIGN KEY (id) REFERENCES topics(id) ON DELETE CASCADE,
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
CREATE TABLE project_status_updates (
id integer NOT NULL PRIMARY KEY DEFAULT (nextval('update_order')),
update_id integer NOT NULL,
project_status_id integer NOT NULL,
new integer,
status varchar,
rank integer,
UNIQUE(update_id,project_status_id), -- one change per update
FOREIGN KEY(update_id) REFERENCES updates(id) ON DELETE CASCADE
FOREIGN KEY(project_status_id) REFERENCES project_status(id)
ON DELETE CASCADE
) WITHOUT ROWID;
INSERT INTO "updates"
VALUES(1,'055ec25ae2e4498fd4be328feda54bcf9e77700a',NULL,1392381391,1392381391,3600,'201404512363105','Mark
Lawrence','[email protected]','en','init /home/mark/src/bif','055ec');
INSERT INTO "updates"
VALUES(2,'70d3ddc37b4dafb3a91a4f3657df2f133abcc049',NULL,1392381392,1392381392,3600,'201404512363270','Mark
Lawrence','[email protected]','en','m','70d3d');
INSERT INTO "updates"
VALUES(3,'9b7ba714cca0e21fd1b305a44b08de3fac7d69f9',1,1392381392,1392381392,3600,'201404512363105/20140451236329b','Mark
Lawrence','[email protected]','en','new project 2 [x]','9b7ba');
INSERT INTO "updates"
VALUES(4,'d7b78fe7911ad7c36341a225f5597dcb9c2c6e77',NULL,1392381392,1392381312,3600,'2014045123512d7','Mark
Lawrence','[email protected]','en','init /home/mark/src/bif/hub
--bare','d7b78');
INSERT INTO "updates"
VALUES(5,'51ffb8e75a1d9ad92f102edfe5e644b478a1925d',1,1392381392,1392381392,3600,'201404512363105/201404512363251','Mark
Lawrence','[email protected]','en','register /home/mark/src/bif/hub
[+1]','51ffb');
INSERT INTO "updates"
VALUES(6,'001ab58d663b01c6661e5ff86e3535ff5deaf971',1,1392623436,1392623436,3600,'201404512363105/201404807503600','Mark
Lawrence','[email protected]','en','sync /home/mark/src/bif/hub [+0]','001ab');
INSERT INTO "updates"
VALUES(7,'40277f8de4d83a176f2fd8ad0af203d3031116e8',2,1392632027,1392632027,3600,'201404512363270/201404810134740','Mark
Lawrence','[email protected]','en','More than an update, but not realy much
text.
','40277');
INSERT INTO "updates"
VALUES(8,'a47a672292998aace4b2f631c7a1a7cf41c09699',1,1392632027,1392632027,3600,'201404512363105/2014048101347a4','Mark
Lawrence','[email protected]','en','update project 2 [x]','a47a6');
INSERT INTO "updates"
VALUES(9,'b7960861cc8da17e995769d599ffe8004bd0df8e',2,1392632102,1392632102,3600,'201404512363270/2014048101502b7','Mark
Lawrence','[email protected]','en','fdslkjjjlkjjjjjj fdslkj
','b7960');
INSERT INTO "updates"
VALUES(10,'f841f315977a416acabbcc2ffed808e0880ff27f',1,1392632102,1392632102,3600,'201404512363105/2014048101502f8','Mark
Lawrence','[email protected]','en','update project 2 [2]','f841f');
INSERT INTO "updates"
VALUES(11,'3aa582307dc15a1cf91bb593bb471529e846426b',1,1392632384,1392632384,3600,'201404512363105/20140481019443a','Mark
Lawrence','[email protected]','en','sync /home/mark/src/bif/hub [+0]','3aa58');
INSERT INTO "updates"
VALUES(12,'e272ad4223d6e87f0fa14bab7579eefb97119519',NULL,1392632484,1392632484,3600,'2014048102124e2','Mark
Lawrence','[email protected]','en','Not much to be said about it hough.
','e272a');
INSERT INTO "updates"
VALUES(13,'4f466aa9b954b1be2c415bf07aa486ef2b16ebe6',1,1392632484,1392632484,3600,'201404512363105/20140481021244f','Mark
Lawrence','[email protected]','en','new task 17 [x]','4f466');
INSERT INTO "updates"
VALUES(14,'93ac7177ea9e0336e02ca732629c9e7d4b90ba60',NULL,1392909532,1392909532,3600,'201405115185293','Mark
Lawrence','[email protected]','en','This is sldkjf al fljds lkjs
','93ac7');
INSERT INTO "updates"
VALUES(15,'2a01d6e028972411590cd68ef36a152bc8e8a9cd',1,1392909532,1392909532,3600,'201404512363105/20140511518522a','Mark
Lawrence','[email protected]','en','new issue 18 [x]','2a01d');
INSERT INTO "updates"
VALUES(16,'b51f292891a906407336c0f3813caa15b0b5d9f8',2,1392910035,1392910035,3600,'201404512363270/2014051152715b5','Mark
Lawrence','[email protected]','en','sdfffljk fdslkj dsflj dsflj dsflk lkj
','b51f2');
INSERT INTO "updates"
VALUES(17,'f777d2410a9f86789c7804b5b81417fe2af0fc72',1,1392910035,1392910035,3600,'201404512363105/2014051152715f7','Mark
Lawrence','[email protected]','en','update project 2 [x]','f777d');
INSERT INTO "updates"
VALUES(18,'6b2b07c0f11080d24b928dcdc0ac53d969afc6e6',1,1393322606,1393322606,3600,'201404512363105/20140561003266b','Mark
Lawrence','[email protected]','en','sync /home/mark/src/bif/hub [+0]','6b2b0');
INSERT INTO "updates"
VALUES(19,'3a9e1ff53fbbd1195aa7672423c474a970e34e8f',NULL,1393330710,1393330710,3600,'20140561218303a','Mark
Lawrence','[email protected]','en','lkj fdslk lk lkjfds
','3a9e1');
INSERT INTO "updates"
VALUES(20,'68e593bbc573732936e050706f681a41c3513363',1,1393330710,1393330710,3600,'201404512363105/201405612183068','Mark
Lawrence','[email protected]','en','new task 19 [x]','68e59');
INSERT INTO "updates"
VALUES(21,'aa159803c2562a0e315f79ef481ed8aef80f1d1e',12,1393331171,1393331171,3600,'2014048102124e2/2014056122611aa','Mark
Lawrence','[email protected]','en','We are now making this thing closed.
','aa159');
INSERT INTO "updates"
VALUES(22,'ca333342fe55b409697f1407927558723d40c586',1,1393331171,1393331171,3600,'201404512363105/2014056122611ca','Mark
Lawrence','[email protected]','en','update task 17 [x][closed]','ca333');
INSERT INTO "updates"
VALUES(23,'277f5a0c86eac65796138ae4006363b72703602d',14,1393332016,1393332016,3600,'201405115185293/201405612401627','Mark
Lawrence','[email protected]','en','ldskfj lkjf lk fdslkdslkfjl
','277f5');
INSERT INTO "updates"
VALUES(24,'6f02e77a97a4109def585f7bdddcfa203c37e78d',1,1393332016,1393332016,3600,'201404512363105/20140561240166f','Mark
Lawrence','[email protected]','en','update issue 18 [x]','6f02e');
INSERT INTO "updates"
VALUES(25,'ac3b97967c851d31c0fd30bdba9b3038b0b2b9f5',12,1393332092,1393332092,3600,'2014048102124e2/2014056124132ac','Mark
Lawrence','[email protected]','en','fdslkj fdslkj fdslk fdslkj
','ac3b9');
INSERT INTO "updates"
VALUES(26,'51a265b1a0e5f78746103e840c591309f8bf711a',1,1393332092,1393332092,3600,'201404512363105/201405612413251','Mark
Lawrence','[email protected]','en','update task 17 [x]','51a26');
INSERT INTO "updates"
VALUES(27,'d3f1257cebc70b1751fce594080834cc40d3dc72',21,1393332137,1393332137,3600,'2014048102124e2/2014056122611aa/2014056124217d3','Mark
Lawrence','[email protected]','en','More than a inline hierarchical way to
looking at things.
','d3f12');
INSERT INTO "updates"
VALUES(28,'e3d80cfef05a31f04b9aec7c072603d4d1a511b6',1,1393332137,1393332137,3600,'201404512363105/2014056124217e3','Mark
Lawrence','[email protected]','en','update task []','e3d80');
INSERT INTO "updates"
VALUES(29,'e17a13778b372506337c0a8452658f90f731e562',NULL,1393332256,1393332256,3600,'2014056124416e1','Mark
Lawrence','[email protected]','en','fdslkjjlk fdslkj
','e17a1');
INSERT INTO "updates"
VALUES(30,'e1276227dfe157a253e84e837598418e2b90d65d',1,1393332256,1393332256,3600,'201404512363105/2014056124416e1','Mark
Lawrence','[email protected]','en','new project 20 [agian]','e1276');
INSERT INTO "updates"
VALUES(31,'ddd4b6a8e14ee85bf0e9ae1a9325b6a6240fafbf',14,1393332306,1393332306,3600,'201405115185293/2014056124506dd','Mark
Lawrence','[email protected]','en','[pushed from <WHERE> to agian<STATUS>]
Please enter your message and so on...
','ddd4b');
INSERT INTO "updates"
VALUES(32,'67a51ae035deb9c9d8927a2ef6e66d440afdce15',1,1393332306,1393332306,3600,'201404512363105/201405612450667','Mark
Lawrence','[email protected]','en','push issue 18 agian','67a51');
INSERT INTO "updates"
VALUES(33,'11be7549771e11f891f9da422d68100437e7cf73',14,1393332411,1393332411,3600,'201405115185293/201405612465111','Mark
Lawrence','[email protected]','en','fdslkj fdslkj jfdslk
','11be7');
INSERT INTO "updates"
VALUES(34,'a132eb614ffa8599a325410fc7c95fc26806ed56',1,1393332412,1393332412,3600,'201404512363105/2014056124652a1','Mark
Lawrence','[email protected]','en','update issue 34 [agian][stalled]','a132e');
INSERT INTO "updates"
VALUES(35,'879acaaa042b196c5e80e021171818f7c26d21b5',14,1393335541,1393335541,3600,'201405115185293/201405613390187','Mark
Lawrence','[email protected]','en','lskjfd lkj fdslkj fdslkj slkjfd
','879ac');
INSERT INTO "updates"
VALUES(36,'238eef014e86bbf0af65bb6a8884b85d693e68ac',1,1393335541,1393335541,3600,'201404512363105/201405613390123','Mark
Lawrence','[email protected]','en','update issue 34 [agian][closed]','238ee');
INSERT INTO "updates"
VALUES(37,'cce0cafc54f5f8a0c932954b474de2428963e2de',1,1393335679,1393335679,3600,'201404512363105/2014056134119cc','Mark
Lawrence','[email protected]','en','sync /home/mark/src/bif/hub [+0]','cce0c');
INSERT INTO "updates"
VALUES(38,'6185e15caf7a818cc0897685370b56ed4032be20',1,1393335698,1393335698,3600,'201404512363105/201405613413861','Mark
Lawrence','[email protected]','en','sync /home/mark/src/bif/hub [+0]','6185e');
INSERT INTO "updates"
VALUES(39,'3b10fec4bf5fe0208df0b026b5025dd1bb5adf48',1,1393335720,1393335720,3600,'201404512363105/20140561342003b','Mark
Lawrence','[email protected]','en','sync /home/mark/src/bif/hub [+0]','3b10f');
INSERT INTO "updates"
VALUES(40,'80130cca4ca87bc8ebef1ea8a0104d649f854310',NULL,1393335996,1393335970,3600,'201405613461080','Mark
Lawrence','[email protected]','en','init /home/mark/src/bif/hub2
--bare','80130');
INSERT INTO "updates"
VALUES(41,'1febe1263664afe366adca41d6f996f9ff364d59',NULL,1393335996,1393335985,3600,'20140561346251f','Mark
Lawrence','[email protected]','en','lkjfds
','1febe');
INSERT INTO "updates"
VALUES(42,'bfb2ce78ec6ec444eeecb134c3b3b20562670e91',40,1393335997,1393335985,3600,'201405613461080/2014056134625bf','Mark
Lawrence','[email protected]','en','new project 2 [lkj]','bfb2c');
INSERT INTO "updates"
VALUES(43,'2f1b681e69d3d9bbdc189c0aec157c7efebf899a',NULL,1393335997,1393335992,3600,'20140561346322f','Mark
Lawrence','[email protected]','en','jfdslijsldjflsdf kjs fdlkj dslf lds lkjfds
','2f1b6');
INSERT INTO "updates"
VALUES(44,'840bceaf232d48f564cbf0646b0b7e11b4be3ed5',40,1393335997,1393335992,3600,'201405613461080/201405613463284','Mark
Lawrence','[email protected]','en','new project 16 [uio]','840bc');
INSERT INTO "updates"
VALUES(45,'a2108886121a723db068a84c0d23b75908f38f5a',1,1393335997,1393335997,3600,'201404512363105/2014056134637a2','Mark
Lawrence','[email protected]','en','register /home/mark/src/bif/hub2
[+5]','a2108');
INSERT INTO "topics"
VALUES(1,'78384c4a43dca41a6eea8c350c1f657610b12f7a',1,'repo',1392381391,3600,1392381391,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(2,'388d358c5d52843c1cf60b4c5cf281c23057d14b',2,'project',1392381392,3600,1392910035,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(3,'97870770cc307f60b9e2d7178bedb4691f8d8bca',2,'project_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(4,'f12165cc6fa9c8cf286b39f01d0f247c3090e381',2,'project_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(5,'d2e2b16d45d4a7e514da610cdc46cbcfec29431a',2,'project_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(6,'5800a5ca27b92366f4756d745a924f139cac07a2',2,'project_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(7,'adb947460fa78747acbe5b7c20bc7bdeb61ac97d',2,'project_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(8,'cd36f38a4dfa682a95f0213194bac1eb4b56ae8e',2,'task_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(9,'db2c4831e089d6ed7e581f9ecbc89eae8d2d6bf8',2,'task_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(10,'009c7b6b87b9c629c85307fc19b3aac3c62a9e42',2,'task_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(11,'3adf9e02e89fa53181490c1fe7823c4ec06f332d',2,'task_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(12,'9c13dc6cc0e19e2ebed7e0cbbb935a322b8e2184',2,'issue_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(13,'49c366273041392e751d940ece922be929280714',2,'issue_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(14,'442f26ff903b89bf2f18c6959f589808564daae6',2,'issue_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(15,'b250712a846e2ae70faec637ac63a4da84f78b4c',2,'issue_status',1392381392,3600,1392381392,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(16,'53458fc40ccedb3e14ec820b194a6a7b441d73bc',4,'repo',1392381312,3600,1392381312,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(17,'fd064276d7d6a9d2816a08fa904ced8cec59c105',12,'task',1392632484,3600,1393332137,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(18,'f0fdd908ab60527f137f09c2a381dd1597fcc4e0',14,'issue',1392909532,3600,1393335541,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(19,'0d4cad2e262e2ded32ece945ba9e97c819eb1ead',19,'task',1393330710,3600,1393330710,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(20,'f077f4aac819ea5d6136a5f19d7189cb123cb420',29,'project',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(21,'d36f082912c76eb2293ef23bb2bdb4f5af87e8de',29,'project_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(22,'fd9ab488c3879f13517180feba27eb43512a36d9',29,'project_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(23,'b9eeccd3f8294191d014a65915c20d1160a957fe',29,'project_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(24,'c68c135f476246d467cb677ed5b99fa822c267ae',29,'project_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(25,'e6ce03a016d19f515c0181d9285ae284d514f854',29,'project_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(26,'96f596bc09d81dc3d7e6c60f842cdccae37ec6c7',29,'task_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(27,'59043c1a6107e975b1252a731520bc6d11ad1cb3',29,'task_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(28,'09b9870cb78a0feb0d79014aae9351b6ce9be45d',29,'task_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(29,'9b81318b222f925fc03cd300a88e615656ab5b4f',29,'task_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(30,'c6cbdb1ae99d4a7dc41b4237b954937571eea5bc',29,'issue_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(31,'f8db45317d475622e5f0a0082f660980b2249af6',29,'issue_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(32,'90248d41ae93c77714421b403a3d5e3719311c5b',29,'issue_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(33,'a257ef030d8e5feaa31354368ddbc7588c75a4dc',29,'issue_status',1393332256,3600,1393332256,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(35,'9da559f16139ba66ea805ee121372608f461ea09',40,'repo',1393335970,3600,1393335970,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(36,'49781bfb8118f11fb896b8c4089b16417bc17ec4',41,'project',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(37,'63ad3af3167d3ccd64327800fca4a1b1057fd2e3',41,'project_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(38,'634e0d76071d9825ecd25bd44d6f46c87e62e18d',41,'project_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(39,'0627c56773e4873818781a42367e46a1caa302db',41,'project_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(40,'3dda044ee75dbe6de2fcf5dc242fcd46c415604c',41,'project_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(41,'0cb0626eab9beb98e0dc18851c9d490cc5df71ab',41,'project_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(42,'5f051a6ff1ff8f4520383ab803dc5b1bf95e2879',41,'task_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(43,'fa2d7a916de5f0c28478ae9aba9e8193ae74c249',41,'task_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(44,'1f74c29790d2fd09fa85f1ed370a2e719eb4079b',41,'task_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(45,'0ecad69caf8fba0749f6b98d8fa395aebb8c02b7',41,'task_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(46,'7922d346c1a3d1ef0d704e82ae8cfb0d5f1dc73e',41,'issue_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(47,'7ba5b54fb807f5747268a689c4ce24b20575c3ab',41,'issue_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(48,'1e64017e98cc09d782f0fe505aa954e6d99f53e9',41,'issue_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(49,'db10cf8adb0619e14a864acd4e702f69bf1c5786',41,'issue_status',1393335985,3600,1393335985,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(50,'ef85716da049ff03627861923f8d94991072ea15',43,'project',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(51,'e21a336c5a1853638bb5f1a12854158e1b0d0c48',43,'project_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(52,'9dfe4288742c51daa559f3facb000214d52b305f',43,'project_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(53,'2b7f61840899129e3ba6058494288043e1a4f644',43,'project_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(54,'719b4415ca262c8d6aba8a119e1c7f23f4eb07d6',43,'project_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(55,'6ea3baf125242782ec789bd0c1c800cb00e3852e',43,'project_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(56,'f1aca1e0196f0971622ea04b2509775176184dca',43,'task_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(57,'a3b8b86328b9e2570e44a3c3a6752eebdae6cd7e',43,'task_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(58,'4e705c3198277c41ce895221e958c0390f7ce62d',43,'task_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(59,'e89387ef64813dc380ed713b4ff20b91970e0313',43,'task_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(60,'33e759a754661a3fb5580c497a5d91ece126d467',43,'issue_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(61,'59a34823353ac83513552379bead58e01bd408cf',43,'issue_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(62,'d69eabeb28b4812624221cb93f59a127e3b20660',43,'issue_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "topics"
VALUES(63,'3b271934016f28fc26512939837c167f24a365bc',43,'issue_status',1393335992,3600,1393335992,3600,'en',NULL,NULL);
INSERT INTO "projects" VALUES(2,NULL,'x','The Linux
Kernel','x',5,'48a7461b',14);
INSERT INTO "projects" VALUES(20,NULL,'agian','This is another again
project','agian',23,'ee7cc3eb',4);
INSERT INTO "projects" VALUES(36,NULL,'lkj','lkjsfd kjl
fdslkjfds','lkj',39,'4fc86581',1);
INSERT INTO "projects" VALUES(50,NULL,'uio','lkjfds
lkjjjjjjfds','uio',53,'46801ff9',1);
INSERT INTO "project_updates" VALUES(2,2,2,1,NULL,'x','t',NULL);
INSERT INTO "project_updates" VALUES(8,2,2,NULL,NULL,NULL,NULL,5);
INSERT INTO "project_updates" VALUES(21,7,2,NULL,NULL,NULL,'The Linux
Kernel',NULL);
INSERT INTO "project_updates" VALUES(23,9,2,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "project_updates" VALUES(30,16,2,NULL,NULL,NULL,NULL,NULL);
INSERT INTO "project_updates" VALUES(43,29,20,1,NULL,'agian','This is another
again project',NULL);
INSERT INTO "project_updates" VALUES(49,29,20,NULL,NULL,NULL,NULL,23);
INSERT INTO "project_updates" VALUES(69,41,36,1,NULL,'lkj','lkjsfd kjl
fdslkjfds',NULL);
INSERT INTO "project_updates" VALUES(75,41,36,NULL,NULL,NULL,NULL,39);
INSERT INTO "project_updates" VALUES(85,43,50,1,NULL,'uio','lkjfds
lkjjjjjjfds',NULL);
INSERT INTO "project_updates" VALUES(91,43,50,NULL,NULL,NULL,NULL,53);
INSERT INTO "project_status" VALUES(3,2,'define',10);
INSERT INTO "project_status" VALUES(4,2,'plan',20);
INSERT INTO "project_status" VALUES(5,2,'run',30);
INSERT INTO "project_status" VALUES(6,2,'eval',40);
INSERT INTO "project_status" VALUES(7,2,'closed',50);
INSERT INTO "project_status" VALUES(21,20,'define',10);
INSERT INTO "project_status" VALUES(22,20,'plan',20);
INSERT INTO "project_status" VALUES(23,20,'run',30);
INSERT INTO "project_status" VALUES(24,20,'eval',40);
INSERT INTO "project_status" VALUES(25,20,'closed',50);
INSERT INTO "project_status" VALUES(37,36,'define',10);
INSERT INTO "project_status" VALUES(38,36,'plan',20);
INSERT INTO "project_status" VALUES(39,36,'run',30);
INSERT INTO "project_status" VALUES(40,36,'eval',40);
INSERT INTO "project_status" VALUES(41,36,'closed',50);
INSERT INTO "project_status" VALUES(51,50,'define',10);
INSERT INTO "project_status" VALUES(52,50,'plan',20);
INSERT INTO "project_status" VALUES(53,50,'run',30);
INSERT INTO "project_status" VALUES(54,50,'eval',40);
INSERT INTO "project_status" VALUES(55,50,'closed',50);
INSERT INTO "project_status_updates" VALUES(3,2,3,1,'define',10);
INSERT INTO "project_status_updates" VALUES(4,2,4,1,'plan',20);
INSERT INTO "project_status_updates" VALUES(5,2,5,1,'run',30);
INSERT INTO "project_status_updates" VALUES(6,2,6,1,'eval',40);
INSERT INTO "project_status_updates" VALUES(7,2,7,1,'closed',50);
INSERT INTO "project_status_updates" VALUES(44,29,21,1,'define',10);
INSERT INTO "project_status_updates" VALUES(45,29,22,1,'plan',20);
INSERT INTO "project_status_updates" VALUES(46,29,23,1,'run',30);
INSERT INTO "project_status_updates" VALUES(47,29,24,1,'eval',40);
INSERT INTO "project_status_updates" VALUES(48,29,25,1,'closed',50);
INSERT INTO "project_status_updates" VALUES(70,41,37,1,'define',10);
INSERT INTO "project_status_updates" VALUES(71,41,38,1,'plan',20);
INSERT INTO "project_status_updates" VALUES(72,41,39,1,'run',30);
INSERT INTO "project_status_updates" VALUES(73,41,40,1,'eval',40);
INSERT INTO "project_status_updates" VALUES(74,41,41,1,'closed',50);
INSERT INTO "project_status_updates" VALUES(86,43,51,1,'define',10);
INSERT INTO "project_status_updates" VALUES(87,43,52,1,'plan',20);
INSERT INTO "project_status_updates" VALUES(88,43,53,1,'run',30);
INSERT INTO "project_status_updates" VALUES(89,43,54,1,'eval',40);
INSERT INTO "project_status_updates" VALUES(90,43,55,1,'closed',50);
COMMIT;
.mode column
.width 20 50
SELECT
'project' AS "kind",
status.uuid as update_order
FROM
project_updates
INNER JOIN
topics AS projects
ON
projects.id = project_updates.project_id
LEFT JOIN
topics AS status
ON
status.id = project_updates.status_id
WHERE
project_updates.update_id = 2
;
SELECT
'project_status' AS "kind",
1 AS update_order
FROM
updates
INNER JOIN
project_status_updates
ON
project_status_updates.update_id = updates.id
WHERE
updates.id = 2
ORDER BY
update_order
;
SELECT
'project' AS "kind",
status.uuid as update_order
FROM
project_updates
INNER JOIN
topics AS projects
ON
projects.id = project_updates.project_id
LEFT JOIN
topics AS status
ON
status.id = project_updates.status_id
WHERE
project_updates.update_id = 2
UNION ALL
SELECT
'project_status' AS "kind",
1 AS update_order
FROM
updates
INNER JOIN
project_status_updates
ON
project_status_updates.update_id = updates.id
WHERE
updates.id = 2
;
SELECT
'project' AS "kind",
status.uuid as update_order
FROM
project_updates
INNER JOIN
topics AS projects
ON
projects.id = project_updates.project_id
LEFT JOIN
topics AS status
ON
status.id = project_updates.status_id
WHERE
project_updates.update_id = 2
UNION ALL
SELECT
'project_status' AS "kind",
1 AS update_order
FROM
updates
INNER JOIN
project_status_updates
ON
project_status_updates.update_id = updates.id
WHERE
updates.id = 2
ORDER BY
update_order
;
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
