Hide keyboard shortcuts

Hot-keys on this page

r m x p   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

1#!/usr/local/bin/python 

2# encoding: utf-8 

3""" 

4*Clean up the database tables used by sherlock - maintainance tools* 

5 

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 

20 

21class database_cleaner(object): 

22 """*Clean and maintain the database helper tables used by sherlock* 

23 

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) 

25 

26 **Key Arguments** 

27 

28 - ``dbConn`` -- mysql database connection 

29 - ``log`` -- logger 

30 - ``settings`` -- the settings dictionary 

31  

32 

33 **Usage** 

34 

35 .. todo:: 

36 

37 - add an entry in the tutorial to clean database tables 

38  

39 

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 ``` 

48 

49 .. todo :: 

50 

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 

60 

61 def __init__( 

62 self, 

63 log, 

64 settings=False, 

65 

66 ): 

67 self.log = log 

68 log.debug("instansiating a new 'database_cleaner' object") 

69 self.settings = settings 

70 # xt-self-arg-tmpx 

71 

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"] 

82 

83 return None 

84 

85 def clean(self): 

86 """*clean up and run some maintance tasks on the crossmatch catalogue helper tables* 

87 

88 .. todo :: 

89 

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') 

99 

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() 

107 

108 print("`tcs_helper_catalogue_tables_info` & `tcs_helper_catalogue_views_info` database tables updated") 

109 

110 self.log.debug('completed the ``get`` method') 

111 return None 

112 

113 def _updated_row_counts_in_tcs_helper_catalogue_tables_info( 

114 self): 

115 """ updated row counts in tcs catalogue tables 

116 

117 .. todo :: 

118 

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') 

129 

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 ) 

139 

140 for row in rows: 

141 tbName = row["table_name"] 

142 

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 ) 

151 

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 ) 

161 

162 for row in rows: 

163 tbName = row["view_name"] 

164 print(tbName) 

165 

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 ) 

174 

175 self.log.debug( 

176 'completed the ``_updated_row_counts_in_tcs_helper_catalogue_tables_info`` method') 

177 return None 

178 

179 def _update_tcs_helper_catalogue_tables_info_with_new_tables( 

180 self): 

181 """update tcs helper catalogue tables info with new tables 

182 

183 .. todo :: 

184 

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') 

195 

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 

209 

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 ) 

219 

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"]) 

233 

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"]) 

239 

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 

259 

260 self.log.debug( 

261 'completed the ``_update_tcs_helper_catalogue_tables_info_with_new_tables`` method') 

262 return None 

263 

264 def _clean_up_columns( 

265 self): 

266 """clean up columns 

267 

268 .. todo :: 

269 

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') 

279 

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 ] 

287 

288 for sqlQuery in sqlQueries: 

289 writequery( 

290 log=self.log, 

291 sqlQuery=sqlQuery, 

292 dbConn=self.cataloguesDbConn, 

293 ) 

294 

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 ) 

305 

306 for row in rows: 

307 view_name = row["view_name"] 

308 object_type = view_name.replace("tcs_view_", "").split("_")[0] 

309 

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 ) 

318 

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 ) 

329 

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() 

335 

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 ) 

344 

345 self.log.debug('completed the ``_clean_up_columns`` method') 

346 return None 

347 

348 def _update_tcs_helper_catalogue_views_info_with_new_views( 

349 self): 

350 """ update tcs helper catalogue tables info with new tables 

351 

352 .. todo :: 

353 

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') 

364 

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 

378 

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 ) 

388 

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"]) 

401 

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 

421 

422 self.log.debug( 

423 'completed the ``_update_tcs_helper_catalogue_views_info_with_new_views`` method') 

424 return None 

425 

426 def _create_tcs_help_tables( 

427 self): 

428 """* create tcs help tables* 

429 

430 **Key Arguments** 

431 

432 # - 

433  

434 

435 **Return** 

436 

437 - None 

438  

439 

440 **Usage** 

441 

442  

443 

444 ```python 

445 usage code  

446 ``` 

447 

448 --- 

449 

450 ```eval_rst 

451 .. todo:: 

452 

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') 

460 

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 """ 

525 

526 writequery( 

527 log=self.log, 

528 sqlQuery=sqlQuery, 

529 dbConn=self.cataloguesDbConn 

530 ) 

531 

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 """ 

544 

545 writequery( 

546 log=self.log, 

547 sqlQuery=sqlQuery, 

548 dbConn=self.cataloguesDbConn 

549 ) 

550 

551 self.log.debug('completed the ``_create_tcs_help_tables`` method') 

552 return None 

553 

554 # use the tab-trigger below for new method 

555 # xt-class-method