-
Notifications
You must be signed in to change notification settings - Fork 0
/
TODO
189 lines (154 loc) · 7.7 KB
/
TODO
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
2024-02-14 Errores al ejecutar SQL por NULL sub_region
sqlite3 /var/dbase/tess.db < sql/006_stars266_new_unknown.sql
Error: near line 10: NOT NULL constraint failed: location_t.sub_region
Command exited with non-zero status 1
sqlite3 /var/dbase/tess.db < sql/008_stars884_new_unknown.sql
Error: near line 10: NOT NULL constraint failed: location_t.sub_region
Command exited with non-zero status 1
sqlite3 /var/dbase/tess.db < sql/009_stars911_new_unknown.sql
Error: near line 10: NOT NULL constraint failed: location_t.sub_region
Command exited with non-zero status 1
sqlite3 /var/dbase/tess.db < sql/010_stars919_new_unknown.sql
Error: near line 10: NOT NULL constraint failed: location_t.sub_region
Command exited with non-zero status 1
========================================================================================================
ME HE QUEDADO HACIENDO UN CROSS CHECK DE PLACES ENTRE MONGO Y TESSDB
ME SALEN ALGUN DUPLICADOS DE TESSDB EN CROSSDB QUE NO ME SALEN CUANDO HAGO LA COMPROBACION DE TESSDB SOLO
PARA RESOLVER EL PROBLEMA DE LOS DUPLICADOS EN TESSDB HAY QUE CREAR UN INDICE PORQUE SI NO VA MUY LENTA LA COSA
LUEGO HABRA QUE BORRAR EL INDICE Y COMPACTAR LA BD CON sqlite3 /path/to/db/mydb.sqlite3 "VACUUM;"
CREATE INDEX idx_location_id ON tess_readings_t(location_id);
UPDATE tess_readings_t SET location_id = 97 WHERE location_id = 159;
UPDATE tess_t SET location_id = 97 WHERE location_id = 159;
DELETE from location_t WHERE location_id = 159;
UPDATE tess_t SET location_id = 137 WHERE location_id = 237;
DELETE from location_t WHERE location_id = 237;
=============================================
2) Hacer un cross-check de nombres y MACs entre MongoDB y tessdb
SELECT name, mac_address, valid_since, valid_until, valid_state
FROM name_to_mac_t
WHERE name LIKE 'stars%'
ORDER BY cast(substr(name,6) as int) ASC, valid_since ASC;
SELECT count(*), name
FROM name_to_mac_t
WHERE name LIKE 'stars%'
GROUP BY name
ORDER BY cast(substr(name,6) as int) ASC;
CREATE TEMP TABLE IF NOT EXISTS names_t (name TEXT);
INSERT INTO names_t (name) VALUES ('stars1');
INSERT INTO names_t (name) VALUES ('stars2');
INSERT INTO names_t (name) VALUES ('stars8');
INSERT INTO names_t (name) VALUES ('stars65');
INSERT INTO names_t (name) VALUES ('stars88');
INSERT INTO names_t (name) VALUES ('stars223');
INSERT INTO names_t (name) VALUES ('stars275');
INSERT INTO names_t (name) VALUES ('stars301');
INSERT INTO names_t (name) VALUES ('stars307');
INSERT INTO names_t (name) VALUES ('stars315');
INSERT INTO names_t (name) VALUES ('stars341');
INSERT INTO names_t (name) VALUES ('stars351');
COMMIT;
SELECT name, mac_address, valid_since, valid_until, valid_state
FROM name_to_mac_t
WHERE name IN (SELECT * from names_t)
ORDER BY cast(substr(name,6) as int) ASC, valid_since ASC;
SELECT t.name, t.mac_address, t.valid_since, t.valid_until, t.valid_state, p.zero_point, p.filter
FROM name_to_mac_t AS t
JOIN tess_t as p USING (mac_address)
WHERE t.name IN (SELECT * from names_t)
ORDER BY cast(substr(t.name,6) as int) ASC, t.valid_since ASC, p.valid_since ASC;
SELECT t.name, t.mac_address,
t.valid_since AS ren_valid_since, t.valid_until AS ren_valid_until, t.valid_state AS ren_valid_state,
p.zero_point, p.filter,
p.valid_since AS zp_valid_since, p.valid_until AS zp_valid_until, p.valid_state AS zp_valid_state
FROM name_to_mac_t AS t
JOIN tess_t as p USING (mac_address)
WHERE t.name = 'stars1'
ORDER BY cast(substr(t.name,6) as int) ASC, t.valid_since ASC, p.valid_since ASC;
SELECT t.mac_address, t.name,
t.valid_since AS ren_valid_since, t.valid_until AS ren_valid_until, t.valid_state AS ren_valid_state,
p.zero_point, p.filter,
p.valid_since AS zp_valid_since, p.valid_until AS zp_valid_until, p.valid_state AS zp_valid_state
FROM name_to_mac_t AS t
JOIN tess_t as p USING (mac_address)
WHERE t.mac_address = '18:FE:34:D3:48:CD'
ORDER BY cast(substr(t.name,6) as int) ASC, t.valid_since ASC, p.valid_since ASC;
SELECT name, valid_status FROM name_to_mac_t GROUP BY name HAVING COUNT(*) > 1;
SELECT name, mac_address, valid_state
FROM name_to_mac_t
WHERE valid_since IN (SELECT MAX(valid_since) FROM name_to_mac_t GROUP BY name)
ORDER BY cast(substr(name,6) as int) ASC, valid_since ASC;
SELECT name, mac_address, valid_state
FROM name_to_mac_t
ORDER BY cast(substr(name,6) as int) ASC, valid_since ASC;
== ANTIGUO
Retocar la utilidad de tess para mostar min(fecha), max(fecha) con reading counts y demás
en luger de la fecha metida por linea de comandos.
tess instrument coalesce --name <name> | --mac <mac> --test
SUSTITUIR TABLA
CREATE TABLE "location_t"
(
location_id INTEGER PRIMARY KEY AUTOINCREMENT,
site TEXT,
longitude REAL,
latitude REAL,
elevation REAL,
zipcode TEXT,
location TEXT,
province TEXT,
state TEXT,
country TEXT,
timezone TEXT DEFAULT 'Etc/UTC',
contact_name TEXT,
contact_email TEXT,
organization TEXT
)
POR ESTA OTRA
CREATE TABLE location_t
(
location_id INTEGER,
place TEXT,
longitude REAL NOT NULL,
latitude REAL NOT NULL,
masl REAL, -- meters above sea level
zipcode TEXT,
town TEXT, -- it can be a village, town, city, or municipality
sub_region TEXT, -- its type can be 'state_district','province'
region TEXT, -- state
country TEXT,
timezone TEXT DEFAULT 'Etc/UTC',
contact_name TEXT, -- still used but to be deprecated
contact_email TEXT, -- still used but to be deprecated
organization TEXT, -- stiil used but to be deprecated by organization_t
UNIQUE(longitude, latitude),
PRIMARY KEY(location_id) AUTOINCREMENT
)
Los campos que vayan a ser deprecados no deberian tener operaciones de create/update en la tess utility
Tabla de observer_t que tenga una referencia a la tabla organization_t
En la tabla de hechos se almacenan tanto las referencias al observador
O BIEN
Esta tabla mixin maneja observadores que son o bien personas físicas (que pueden o no pertenecer a una organización)
o personas jurídicas (organizaciones)
Los atributos person_valid_* manejan el histórico de pertenencia de una persona a una organización
CREATE TABLE IF NOT EXISTS observer_t
(
observer_id INTEGER,
obs_type TEXT NOT NULL, -- Either 'person' or 'organization'
-----------------------------------------------------------------------
person_name TEXT, --
person_surname TEXT, --
person_title TEXT, -- 'Dr.' , 'Professor', etc. if applicable.
-- These three manages history of a person within an organization
person_valid_since TIMESTAMP, -- 'YYYY-MM-DD HH:MM:SS'
person_valid_until TIMESTAMP, -- 'YYYY-MM-DD HH:MM:SS'
person_valid_state TEXT, -- Either 'Current' or 'Expired'
------------------------------------------------------------------------
org_name TEXT, -- Organization name
org_acronym TEXT, -- Organization acronym (i.e. AAM, UCM)
org_description TEXT, -- Organization description
org_email TEXT,
org_phone TEXT,
org_logo_url, TEXT, -- Organization logo URL
org_web_url, TEXT, -- Organization web URL
------------------------------------------------------------------------
PRIMARY KEY(observer_id)
);