Coverage for sherlock/database_cleaner.py: 74%
137 statements
« prev ^ index » next coverage.py v7.2.2, created at 2023-10-10 13:58 +0000
« prev ^ index » next coverage.py v7.2.2, created at 2023-10-10 13:58 +0000
1#!/usr/local/bin/python
2# encoding: utf-8
3"""
4*Clean up the database tables used by sherlock - maintainance tools*
6:Author:
7 David Young
8"""
9from __future__ import print_function
10from builtins import object
11import sys
12import os
13os.environ['TERM'] = 'vt100'
14import readline
15import glob
16import pickle
17from fundamentals import tools, times
18from docopt import docopt
19from fundamentals.mysql import readquery, writequery
21class database_cleaner(object):
22 """*Clean and maintain the database helper tables used by sherlock*
24 The helper tables list row counts for tables and views and provide the column maps that help sherlock know which catalogue columns relate to which parameters (e.g. RA, DEC etc)
26 **Key Arguments**
28 - ``dbConn`` -- mysql database connection
29 - ``log`` -- logger
30 - ``settings`` -- the settings dictionary
33 **Usage**
35 .. todo::
37 - add an entry in the tutorial to clean database tables
40 ```python
41 from sherlock.database_cleaner import database_cleaner
42 db = database_cleaner(
43 log=log,
44 settings=settings
45 )
46 db.clean()
47 ```
49 .. todo ::
51 - update key arguments values and definitions with defaults
52 - update return values and definitions
53 - update usage examples and text
54 - update docstring text
55 - check sublime snippet exists
56 - clip any useful text to docs mindmap
57 - regenerate the docs and check redendering of this docstring
58 """
59 # Initialisation
61 def __init__(
62 self,
63 log,
64 settings=False,
66 ):
67 self.log = log
68 log.debug("instansiating a new 'database_cleaner' object")
69 self.settings = settings
70 # xt-self-arg-tmpx
72 # INITIAL ACTIONS# Initial Actions
73 # SETUP ALL DATABASE CONNECTIONS
74 from sherlock import database
75 db = database(
76 log=self.log,
77 settings=self.settings
78 )
79 dbConns, dbVersions = db.connect()
80 self.transientsDbConn = dbConns["transients"]
81 self.cataloguesDbConn = dbConns["catalogues"]
83 return None
85 def clean(self):
86 """*clean up and run some maintance tasks on the crossmatch catalogue helper tables*
88 .. todo ::
90 - update key arguments values and definitions with defaults
91 - update return values and definitions
92 - update usage examples and text
93 - update docstring text
94 - check sublime snippet exists
95 - clip any useful text to docs mindmap
96 - regenerate the docs and check redendering of this docstring
97 """
98 self.log.debug('starting the ``get`` method')
100 self._create_tcs_help_tables()
101 self._update_tcs_helper_catalogue_tables_info_with_new_tables()
102 self._updated_row_counts_in_tcs_helper_catalogue_tables_info()
103 self._clean_up_columns()
104 self._update_tcs_helper_catalogue_views_info_with_new_views()
105 self._clean_up_columns()
106 self._updated_row_counts_in_tcs_helper_catalogue_tables_info()
108 print("`tcs_helper_catalogue_tables_info` & `tcs_helper_catalogue_views_info` database tables updated")
110 self.log.debug('completed the ``get`` method')
111 return None
113 def _updated_row_counts_in_tcs_helper_catalogue_tables_info(
114 self):
115 """ updated row counts in tcs catalogue tables
117 .. todo ::
119 - update key arguments values and definitions with defaults
120 - update return values and definitions
121 - update usage examples and text
122 - update docstring text
123 - check sublime snippet exists
124 - clip any useful text to docs mindmap
125 - regenerate the docs and check redendering of this docstring
126 """
127 self.log.debug(
128 'starting the ``_updated_row_counts_in_tcs_helper_catalogue_tables_info`` method')
130 sqlQuery = u"""
131 select * from tcs_helper_catalogue_tables_info where table_name like "%%stream" or (number_of_rows is null and legacy_table = 0)
132 """ % locals()
133 rows = readquery(
134 log=self.log,
135 sqlQuery=sqlQuery,
136 dbConn=self.cataloguesDbConn,
137 quiet=False
138 )
140 for row in rows:
141 tbName = row["table_name"]
143 sqlQuery = u"""
144 update tcs_helper_catalogue_tables_info set number_of_rows = (select count(*) as count from %(tbName)s) where table_name = "%(tbName)s"
145 """ % locals()
146 writequery(
147 log=self.log,
148 sqlQuery=sqlQuery,
149 dbConn=self.cataloguesDbConn,
150 )
152 sqlQuery = u"""
153 select * from tcs_helper_catalogue_views_info where (number_of_rows is null and legacy_view = 0)
154 """ % locals()
155 rows = readquery(
156 log=self.log,
157 sqlQuery=sqlQuery,
158 dbConn=self.cataloguesDbConn,
159 quiet=False
160 )
162 for row in rows:
163 tbName = row["view_name"]
164 print(tbName)
166 sqlQuery = u"""
167 update tcs_helper_catalogue_views_info set number_of_rows = (select count(*) as count from %(tbName)s) where view_name = "%(tbName)s"
168 """ % locals()
169 writequery(
170 log=self.log,
171 sqlQuery=sqlQuery,
172 dbConn=self.cataloguesDbConn,
173 )
175 self.log.debug(
176 'completed the ``_updated_row_counts_in_tcs_helper_catalogue_tables_info`` method')
177 return None
179 def _update_tcs_helper_catalogue_tables_info_with_new_tables(
180 self):
181 """update tcs helper catalogue tables info with new tables
183 .. todo ::
185 - update key arguments values and definitions with defaults
186 - update return values and definitions
187 - update usage examples and text
188 - update docstring text
189 - check sublime snippet exists
190 - clip any useful text to docs mindmap
191 - regenerate the docs and check redendering of this docstring
192 """
193 self.log.debug(
194 'starting the ``_update_tcs_helper_catalogue_tables_info_with_new_tables`` method')
196 sqlQuery = u"""
197 SELECT max(id) as thisId FROM tcs_helper_catalogue_tables_info;
198 """ % locals()
199 thisId = readquery(
200 log=self.log,
201 sqlQuery=sqlQuery,
202 dbConn=self.cataloguesDbConn,
203 quiet=False
204 )
205 try:
206 highestId = thisId[0]["thisId"] + 1
207 except:
208 highestId = 1
210 sqlQuery = u"""
211 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA like '%%catalogues%%' and TABLE_NAME like "tcs_cat%%";
212 """ % locals()
213 tablesInDatabase = readquery(
214 log=self.log,
215 sqlQuery=sqlQuery,
216 dbConn=self.cataloguesDbConn,
217 quiet=False
218 )
220 sqlQuery = u"""
221 SELECT table_name, old_table_name FROM tcs_helper_catalogue_tables_info;
222 """ % locals()
223 tableList = readquery(
224 log=self.log,
225 sqlQuery=sqlQuery,
226 dbConn=self.cataloguesDbConn,
227 quiet=False
228 )
229 tbList = []
230 oldList = []
231 for tb in tableList:
232 oldList.append(tb["old_table_name"])
234 for tb in tableList:
235 if tb["old_table_name"] not in tbList:
236 tbList.append(tb["old_table_name"])
237 if tb["table_name"] not in tbList:
238 tbList.append(tb["table_name"])
240 for tb in tablesInDatabase:
241 if tb["TABLE_NAME"] not in tbList:
242 thisTableName = tb["TABLE_NAME"]
243 print("`%(thisTableName)s` added to `tcs_helper_catalogue_tables_info` database table" % locals())
244 sqlQuery = u"""
245 INSERT INTO tcs_helper_catalogue_tables_info (
246 id,
247 table_name
248 )
249 VALUES (
250 %(highestId)s,
251 "%(thisTableName)s"
252 )""" % locals()
253 writequery(
254 log=self.log,
255 sqlQuery=sqlQuery,
256 dbConn=self.cataloguesDbConn,
257 )
258 highestId += 1
260 self.log.debug(
261 'completed the ``_update_tcs_helper_catalogue_tables_info_with_new_tables`` method')
262 return None
264 def _clean_up_columns(
265 self):
266 """clean up columns
268 .. todo ::
270 - update key arguments values and definitions with defaults
271 - update return values and definitions
272 - update usage examples and text
273 - update docstring text
274 - check sublime snippet exists
275 - clip any useful text to docs mindmap
276 - regenerate the docs and check redendering of this docstring
277 """
278 self.log.debug('starting the ``_clean_up_columns`` method')
280 sqlQueries = [
281 "update tcs_helper_catalogue_tables_info set old_table_name = table_name where old_table_name is null;",
282 "update tcs_helper_catalogue_tables_info set version_number = 'stream' where table_name like '%%stream' and version_number is null;",
283 """update tcs_helper_catalogue_tables_info set in_ned = 0 where table_name like '%%stream' and in_ned is null;""",
284 """update tcs_helper_catalogue_tables_info set vizier_link = 0 where table_name like '%%stream' and vizier_link is null;""",
285 "update tcs_helper_catalogue_views_info set old_view_name = view_name where old_view_name is null;",
286 ]
288 for sqlQuery in sqlQueries:
289 writequery(
290 log=self.log,
291 sqlQuery=sqlQuery,
292 dbConn=self.cataloguesDbConn,
293 )
295 # VIEW OBJECT TYPES
296 sqlQuery = u"""
297 SELECT view_name FROM tcs_helper_catalogue_views_info where legacy_view = 0 and object_type is null;
298 """ % locals()
299 rows = readquery(
300 log=self.log,
301 sqlQuery=sqlQuery,
302 dbConn=self.cataloguesDbConn,
303 quiet=False
304 )
306 for row in rows:
307 view_name = row["view_name"]
308 object_type = view_name.replace("tcs_view_", "").split("_")[0]
310 sqlQuery = u"""
311 update tcs_helper_catalogue_views_info set object_type = "%(object_type)s" where view_name = "%(view_name)s"
312 """ % locals()
313 writequery(
314 log=self.log,
315 sqlQuery=sqlQuery,
316 dbConn=self.cataloguesDbConn,
317 )
319 # MASTER TABLE ID FOR VIEWS
320 sqlQuery = u"""
321 SELECT view_name FROM tcs_helper_catalogue_views_info where legacy_view = 0 and table_id is null;
322 """ % locals()
323 rows = readquery(
324 log=self.log,
325 sqlQuery=sqlQuery,
326 dbConn=self.cataloguesDbConn,
327 quiet=False
328 )
330 for row in rows:
331 view_name = row["view_name"]
332 table_name = view_name.replace("tcs_view_", "").split("_")[1:]
333 table_name = ("_").join(table_name)
334 table_name = "tcs_cat_%(table_name)s" % locals()
336 sqlQuery = u"""
337 update tcs_helper_catalogue_views_info set table_id = (select id from tcs_helper_catalogue_tables_info where table_name = "%(table_name)s") where view_name = "%(view_name)s"
338 """ % locals()
339 writequery(
340 log=self.log,
341 sqlQuery=sqlQuery,
342 dbConn=self.cataloguesDbConn,
343 )
345 self.log.debug('completed the ``_clean_up_columns`` method')
346 return None
348 def _update_tcs_helper_catalogue_views_info_with_new_views(
349 self):
350 """ update tcs helper catalogue tables info with new tables
352 .. todo ::
354 - update key arguments values and definitions with defaults
355 - update return values and definitions
356 - update usage examples and text
357 - update docstring text
358 - check sublime snippet exists
359 - clip any useful text to docs mindmap
360 - regenerate the docs and check redendering of this docstring
361 """
362 self.log.debug(
363 'starting the ``_update_tcs_helper_catalogue_views_info_with_new_views`` method')
365 sqlQuery = u"""
366 SELECT max(id) as thisId FROM tcs_helper_catalogue_views_info;
367 """ % locals()
368 thisId = readquery(
369 log=self.log,
370 sqlQuery=sqlQuery,
371 dbConn=self.cataloguesDbConn,
372 quiet=False
373 )
374 try:
375 highestId = thisId[0]["thisId"] + 1
376 except:
377 highestId = 1
379 sqlQuery = u"""
380 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='VIEW' AND TABLE_SCHEMA like '%%catalogues%%' and TABLE_NAME like "tcs_view%%" and TABLE_NAME not like "%%helper%%";
381 """ % locals()
382 tablesInDatabase = readquery(
383 log=self.log,
384 sqlQuery=sqlQuery,
385 dbConn=self.cataloguesDbConn,
386 quiet=False
387 )
389 sqlQuery = u"""
390 SELECT view_name FROM tcs_helper_catalogue_views_info;
391 """ % locals()
392 tableList = readquery(
393 log=self.log,
394 sqlQuery=sqlQuery,
395 dbConn=self.cataloguesDbConn,
396 quiet=False
397 )
398 tbList = []
399 for tb in tableList:
400 tbList.append(tb["view_name"])
402 for tb in tablesInDatabase:
403 if tb["TABLE_NAME"] not in tbList:
404 thisViewName = tb["TABLE_NAME"]
405 print("`%(thisViewName)s` added to `tcs_helper_catalogue_views_info` database table" % locals())
406 sqlQuery = u"""
407 INSERT INTO tcs_helper_catalogue_views_info (
408 id,
409 view_name
410 )
411 VALUES (
412 %(highestId)s,
413 "%(thisViewName)s"
414 )""" % locals()
415 writequery(
416 log=self.log,
417 sqlQuery=sqlQuery,
418 dbConn=self.cataloguesDbConn,
419 )
420 highestId += 1
422 self.log.debug(
423 'completed the ``_update_tcs_helper_catalogue_views_info_with_new_views`` method')
424 return None
426 def _create_tcs_help_tables(
427 self):
428 """* create tcs help tables*
430 **Key Arguments**
432 # -
435 **Return**
437 - None
440 **Usage**
444 ```python
445 usage code
446 ```
448 ---
450 ```eval_rst
451 .. todo::
453 - add usage info
454 - create a sublime snippet for usage
455 - write a command-line tool for this method
456 - update package tutorial with command-line tool info if needed
457 ```
458 """
459 self.log.debug('starting the ``_create_tcs_help_tables`` method')
461 sqlQuery = """
462 CREATE TABLE IF NOT EXISTS `tcs_helper_catalogue_tables_info` (
463 `id` smallint(5) unsigned NOT NULL,
464 `table_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
465 `description` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
466 `url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
467 `number_of_rows` bigint(20) DEFAULT NULL,
468 `reference_url` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
469 `reference_text` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
470 `notes` text COLLATE utf8_unicode_ci,
471 `vizier_link` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
472 `in_ned` tinyint(4) DEFAULT NULL,
473 `object_types` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
474 `version_number` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
475 `last_updated` datetime DEFAULT NULL,
476 `legacy_table` tinyint(4) DEFAULT '0',
477 `old_table_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
478 `raColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
479 `decColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
480 `catalogue_object_subtypeColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
481 `catalogue_object_idColName` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
482 `zColName` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
483 `distanceColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
484 `object_type_accuracy` tinyint(2) DEFAULT NULL,
485 `semiMajorColName` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
486 `semiMajorToArcsec` float DEFAULT NULL,
487 `transientStream` tinyint(4) DEFAULT '0',
488 `photoZColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
489 `photoZErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
490 `UColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
491 `UErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
492 `BColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
493 `BErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
494 `VColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
495 `VErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
496 `RColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
497 `RErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
498 `IColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
499 `IErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
500 `JColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
501 `JErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
502 `HColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
503 `HErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
504 `KColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
505 `KErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
506 `_uColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
507 `_uErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
508 `_gColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
509 `_gErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
510 `_rColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
511 `_rErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
512 `_iColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
513 `_iErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
514 `_zColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
515 `_zErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
516 `_yColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
517 `_yErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
518 `unkMagColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
519 `unkMagErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
520 `GColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
521 `GErrColName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
522 PRIMARY KEY (`id`)
523 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
524 """
526 writequery(
527 log=self.log,
528 sqlQuery=sqlQuery,
529 dbConn=self.cataloguesDbConn
530 )
532 sqlQuery = """
533 CREATE TABLE IF NOT EXISTS `tcs_helper_catalogue_views_info` (
534 `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
535 `view_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
536 `number_of_rows` bigint(20) DEFAULT NULL,
537 `object_type` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
538 `legacy_view` tinyint(4) DEFAULT '0',
539 `old_view_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
540 `table_id` int(11) DEFAULT NULL,
541 PRIMARY KEY (`id`)
542 ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
543 """
545 writequery(
546 log=self.log,
547 sqlQuery=sqlQuery,
548 dbConn=self.cataloguesDbConn
549 )
551 self.log.debug('completed the ``_create_tcs_help_tables`` method')
552 return None
554 # use the tab-trigger below for new method
555 # xt-class-method