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
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
|
#!usr/bin/python3
def start(filter, cat='', pn='', desc=''):
NOVIRT = "AND cat NOT LIKE 'virtual'"
SELECTS = {
'all': f'''SELECT
i.category AS cat,
i.name as pn,
i.version as iv,
IFNULL(a.version, 'alien') AS av,
d.description AS desc
FROM local_packages AS i LEFT OUTER JOIN remote_packages as a
ON i.category = a.category
AND i.name = a.name
AND i.slot = i.slot
LEFT JOIN remote_descriptions AS d ON i.name = d.name AND i.category = d.category
WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}
UNION
SELECT
a.category AS cat,
a.name as pn,
IFNULL(i.version, 'None') AS iv,
a.version as av,
d.description AS desc
FROM remote_packages AS a LEFT OUTER JOIN local_packages AS i
ON a.category = i.category
AND a.name = i.name
AND a.slot = i.slot
LEFT JOIN remote_descriptions AS d ON a.name = d.name AND a.category = d.category
WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}''',
'installed': f'''SELECT
i.category AS cat,
i.name AS pn,
i.version AS iv,
a.version as av,
d.description AS desc
FROM local_packages AS i
LEFT JOIN remote_packages AS a
ON i.category = a.category
AND i.name = a.name
AND i.slot = i.slot
LEFT JOIN remote_descriptions AS d ON i.name = d.name AND i.category = d.category
WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}''',
'alien': f'''SELECT
i.category AS cat,
i.name AS pn,
i.version as iv,
IFNULL(a.version, 'alien') AS av,
d.description AS desc
FROM local_packages AS i
LEFT JOIN remote_packages AS a
ON a.category = i.category
AND a.name = i.name
AND a.slot = i.slot
LEFT JOIN remote_descriptions AS d ON i.name = d.name AND i.category = d.category
WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}
AND av IS 'alien' ''',
'available': f'''SELECT
a.category AS cat,
a.name AS pn,
i.version as iv,
a.version AS av,
d.description AS desc
FROM remote_packages AS a
LEFT JOIN local_packages AS i
ON a.category = i.category
AND a.name = i.name
AND a.slot = i.slot
LEFT JOIN remote_descriptions AS d ON a.name = d.name AND a.category = d.category
WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}
AND iv IS NULL''',
'upgradable': f'''SELECT
i.category AS cat,
i.name AS pn,
i.version as iv,
a.version AS av,
d.description AS desc
FROM local_packages AS i
INNER JOIN remote_packages AS a
ON i.category = a.category
AND i.name = a.name
AND i.slot = i.slot
LEFT JOIN remote_descriptions AS d ON i.name = d.name AND i.category = d.category
WHERE cat LIKE '%{cat}%' AND pn LIKE '%{pn}%' AND desc LIKE '%{desc}%' {NOVIRT}
AND iv <> av'''
}
return SELECTS[filter]
|
