+ 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
CREATE TABLE Devices (
pft3Name TEXT,
residentDateTime : TEXT,
PRIMARY KEY (pft3Name, resident DateTime)
);
CREATE INDEX pft3NameRegularIndex ON Devices (pft3Name);
CREATE INDEX residenDateTimeRegularIndex ON Devices (residentDateTime);
CREATE TABLE Memories (
name TEXT PRIMARY KEY,
startAddress UNSIGNED INTEGER,
size UNSIGNED INTEGER,
type TEXT
);
CREATE TABLE UsedMemory (
id INTEGER PRIMARY KEY,
pft3Name TEXT,
memName TEXT,
indexInSameTypeMemSequence INTEGER,
FOREIGN KEY (pft3Name) REFERENCES Devices (pft3Name) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (memName) REFERENCES Memories (name) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE UNIQUE INDEX usedMemoryUniqueRecordIndex ON UsedMemory (pft3Name, memName, indexInSameTypeMemSequence);
CREATE TABLE MemTestHeaders (
usedMemId INTEGER,
residentDateTime TEXT,
testNumber INTEGER,
testTitle TEXT,
FOREIGN KEY (usedMemId) REFERENCES UsedMemory (id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (residentDateTime) REFERENCES Devices (residenDateTime)
);
CREATE UNIQUE INDEX memTestHeadersUniqueRecordIndex ON MemTestHeaders (usedMemId, residentDateTime, testNumber);
CREATE TABLE UnreachableMemory (
residentDateTime TEXT,
usedMemId INTEGER,
unreachableFrom INTEGER,
unreachableSize INTEGER,
FOREIGN KEY (residentDateTime) REFERENCES Devices (residentDateTime) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (usedMemId) REFERENCES UsedMemory (id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE UNIQUE INDEX unreachableMemoryUniqueRecordIndex ON UnreachableMemory (residentDateTime, usedMemId, unreachableFrom, unreachableSize);
/*Заполним данными*/
+ 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
INSERT INTO Devices (pft3Name, residentDateTime) VALUES ('TPB02', '2012-11-29 16:02:00');
INSERT INTO Memories (name, startAddress, size, type) VALUES ('STM32F207 IntFlash', 2147483648, 524288, 'IntFlash');
INSERT INTO Memories (name, startAddress, size, type) VALUES ('STM32F207 IntSRAM' , 536870912, 131072, 'IntSRAM');
INSERT INTO Memories (name, startAddress, size, type) VALUES ('STM32F207 ExtSRAM' , 1610612736, 2097152, 'ExtSRAM');
INSERT INTO Memories (name, startAddress, size, type) VALUES ('ExtSEEPROM 128' , 0, 131072, 'ExtSEEPROM');
INSERT INTO UsedMemory (id, pft3Name, memName, indexInSameTypeMemSequence) VALUES (NULL, 'TPB02', 'STM32F207 IntFlash', 0);
INSERT INTO UsedMemory (id, pft3Name, memName, indexInSameTypeMemSequence) VALUES (NULL, 'TPB02', 'STM32F207 IntSRAM' , 0);
INSERT INTO UsedMemory (id, pft3Name, memName, indexInSameTypeMemSequence) VALUES (NULL, 'TPB02', 'STM32F207 ExtSRAM' , 0);
INSERT INTO UsedMemory (id, pft3Name, memName, indexInSameTypeMemSequence) VALUES (NULL, 'TPB02', 'ExtSEEPROM 128' , 0);
INSERT INTO UsedMemory (id, pft3Name, memName, indexInSameTypeMemSequence) VALUES (NULL, 'TPB02', 'ExtSEEPROM 128' , 1);
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (1, '2012-11-29 16:02:00', 0, 'Бегущая 1');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (2, '2012-11-29 16:02:00', 0, 'Бегущая 1');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (4, '2012-11-29 16:02:00', 0, 'Бегущая 1');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (5, '2012-11-29 16:02:00', 0, 'Бегущая 1');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (1, '2012-11-29 16:02:00', 1, 'Бегущий 0');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (2, '2012-11-29 16:02:00', 1, 'Бегущий 0');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (4, '2012-11-29 16:02:00', 1, 'Бегущий 0');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (5, '2012-11-29 16:02:00', 1, 'Бегущий 0');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (1, '2012-11-29 16:02:00', 2, 'Тяжёлый код');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (2, '2012-11-29 16:02:00', 2, 'Тяжёлый код');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (4, '2012-11-29 16:02:00', 2, 'Тяжёлый код');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (5, '2012-11-29 16:02:00', 2, 'Тяжёлый код');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (3, '2012-11-29 16:02:00', 0, 'ШД - бегущая 1');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (3, '2012-11-29 16:02:00', 1, 'ШД - бегущий 0');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (3, '2012-11-29 16:02:00', 2, 'ША');
INSERT INTO memTestHeaders (usedMemId, residentDateTime, testNumber, testTitle) VALUES (3, '2012-11-29 16:02:00', 3, 'Тяжёлый код');
INSERT INTO UnreachableMemory (residentDateTime, usedMemId, unreachableFrom, unreachableSize) VALUES ('2012-11-29 16:02:00', 1, 0, 19152);
INSERT INTO UnreachableMemory (residentDateTime, usedMemId, unreachableFrom, unreachableSize) VALUES ('2012-11-29 16:02:00', 2, 0, 9472);
Задача: сделать запрос, чтобы выводил перечисленные в SELECT поля для записей, у которых значение поля residentDateTime является ближайшим, среди меньших либо равных заданному значению (Изменения в названиях и кол-ве проверок, областях памяти, которые нельзя трогать происходят не при каждом обновлении резидентной порграммы, поэтому надо находить наиболее вероятное).
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT DISTINCT Memories.type, UsedMemory.indexInSameTypeMemSequence, Memories.startAddress, Memories.size,
UnreachableMemory.unreachableFrom, UnreachableMemory.unreachablesize
FROM Memories,
(INNER JOIN UsedMemory ON (UsedMemory.pft3Name LIKE 'TPB02%') AND (Memories.name = UsedMemory.memName) ),
(LEFT OUTER JOIN UnreachableMemory ON (UnreachableMemory.id IN (
SELECT DISTINCT id
FROM UsedMemory
WHERE ((UsedMemory.pft3Name LIKE 'TPB02%') AND (Memories.name = UsedMemory.memName)) ) ) AND (julianday(UnreachableMemory.residenDateTime) = (
SELECT julianday(residentDateTime)
FROM UnreachableMemory
WHERE ( (julianday('2012-11-29 16:02:00') - julianday(residentDateTime)) >= 0)
ORDER BY julianday(residentDateTime) ASC LIMIT 1
) ) )
GROUP BY Memories.type
ORDER BY UsedMemory.indexInSameTypeMemSequence, UnreachableMemory.unreachableFrom;
Ожидаемый результат
1.
2.
3.
4.
5.
6.
type indexIn... startAddress size un...From un...Size
'IntFlash' 0 2147483648 524288 0 19152
'IntSRAM' 0 536870912 131072 0 9472
'ExtSRAM' 0 1610612736 2097152 NULL NULL
'ExtSEEPROM' 0 0 131072 NULL NULL
'ExtSEEPROM' 1 0 131072 NULL NULL
Полученный результат при выполнении qsqlQuery->prepare(<...>):
RIGHT and FULL OUTER JOINs are not currently supported Unable to execute statement при попытке выполнить qsqlQuery.prepare(<...>)
Qt 4.7.3. Ни RIGHT ни FULL OUTER JOIN в запросе не присутствует
Благодарю за помощь.
Модератор: Портянки, вроде заполнения данных, лучше обрамлять тегом spoiler. Я километровые запросы одной строкой
лучше отформатировать. Иначе хреново читается. |
