Coverage for sherlock/database.py: 73%
88 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*the database object for sherlock, setting up ssh tunnels and various database connections*
6:Author:
7 David Young
8"""
9from builtins import object
10import sys
11import os
12os.environ['TERM'] = 'vt100'
13import readline
14import glob
15import pickle
16import time
17from subprocess import Popen, PIPE, STDOUT
18import pymysql as ms
19# import pymysql as ms
20from docopt import docopt
21from fundamentals.mysql import readquery
24class database(object):
25 """
26 *the database object for sherlock, setting up ssh tunnels and various database connections*
28 The returned dictionary of database connections contain the following databases:
29 - ``transients`` -- the database hosting the transient source data
30 - ``catalogues`` -- connection to the database hosting the contextual catalogues the transients are to be crossmatched against
32 **Key Arguments**
34 - ``log`` -- logger
35 - ``settings`` -- the settings dictionary
38 **Return**
40 - ``dbConns`` -- a dictionary of the database connections required by sherlock
43 **Usage**
45 To setup the sherlock database connections, run the following:
47 ```python
48 # SETUP ALL DATABASE CONNECTIONS
49 from sherlock import database
50 db = database(
51 log=log,
52 settings=settings
53 )
54 dbConns, dbVersions = db.connect()
55 transientsDbConn = dbConns["transients"]
56 cataloguesDbConn = dbConns["catalogues"]
57 ```
60 .. todo ::
62 - update key arguments values and definitions with defaults
63 - update return values and definitions
64 - update usage examples and text
65 - update docstring text
66 - check sublime snippet exists
67 - clip any useful text to docs mindmap
68 - regenerate the docs and check redendering of this docstring
69 """
70 # INITIALISATION
72 def __init__(
73 self,
74 log,
75 settings=False,
77 ):
78 self.log = log
79 log.debug("instansiating a new '_database' object")
80 self.settings = settings
81 return None
83 def connect(self):
84 """connect to the various databases, the credientals and settings of which are found in the sherlock settings file
86 **Return**
88 - ``transientsDbConn`` -- the database hosting the transient source data
89 - ``cataloguesDbConn`` -- connection to the database hosting the contextual catalogues the transients are to be crossmatched against
92 See the class docstring for usage
94 .. todo ::
96 - update key arguments values and definitions with defaults
97 - update return values and definitions
98 - update usage examples and text
99 - update docstring text
100 - check sublime snippet exists
101 - clip any useful text to docs mindmap
102 - regenerate the docs and check redendering of this docstring
103 """
104 self.log.debug('starting the ``get`` method')
106 # CATALOGUE DATABASE ALWAYS NEEDED
107 catalogueSettings = self.settings[
108 "database settings"]["static catalogues"]
110 # TRANSIENT DATABASE OPTIONAL
111 if "transients" in self.settings[
112 "database settings"]:
113 transientSettings = self.settings[
114 "database settings"]["transients"]
115 else:
116 transientSettings = False
118 dbConns = []
119 for dbSettings in [transientSettings, catalogueSettings]:
120 port = False
121 if dbSettings and "tunnel" in dbSettings and dbSettings["tunnel"]:
122 port = self._setup_tunnel(
123 tunnelParameters=dbSettings["tunnel"]
124 )
125 elif dbSettings and "port" in dbSettings and dbSettings["port"]:
126 port = int(dbSettings["port"])
128 if dbSettings:
129 # SETUP A DATABASE CONNECTION FOR THE STATIC CATALOGUES
130 host = dbSettings["host"]
131 user = dbSettings["user"]
132 passwd = dbSettings["password"]
133 dbName = dbSettings["db"]
134 thisConn = ms.connect(
135 host=host,
136 user=user,
137 passwd=passwd,
138 db=dbName,
139 port=port,
140 use_unicode=True,
141 charset='utf8',
142 client_flag=ms.constants.CLIENT.MULTI_STATEMENTS,
143 connect_timeout=3600
144 )
145 thisConn.autocommit(True)
146 dbConns.append(thisConn)
148 # from fundamentals.mysql import readquery
149 # sqlQuery = u"""
150 # show databases;
151 # """ % locals()
152 # rows = readquery(
153 # log=self.log,
154 # sqlQuery=sqlQuery,
155 # dbConn=thisConn,
156 # quiet=False
157 # )
158 # print(rows)
159 else:
160 dbConns.append(None)
162 # CREATE A DICTIONARY OF DATABASES
163 dbConns = {
164 "transients": dbConns[0],
165 "catalogues": dbConns[1]
166 }
168 dbVersions = {}
169 for k, v in list(dbConns.items()):
170 if v:
171 sqlQuery = u"""
172 SELECT VERSION() as v;
173 """ % locals()
174 rows = readquery(
175 log=self.log,
176 sqlQuery=sqlQuery,
177 dbConn=v,
178 quiet=False
179 )
180 version = rows[0]['v']
181 dbVersions[k] = version
182 else:
183 dbVersions[k] = None
185 self.log.debug('completed the ``get`` method')
186 return dbConns, dbVersions
188 def _setup_tunnel(
189 self,
190 tunnelParameters):
191 """
192 *setup a ssh tunnel for a database connection to port through*
194 **Key Arguments**
196 - ``tunnelParameters`` -- the tunnel parameters found associated with the database settings
199 **Return**
201 - ``sshPort`` -- the port the ssh tunnel is connected via
204 .. todo ::
206 - update key arguments values and definitions with defaults
207 - update return values and definitions
208 - update usage examples and text
209 - update docstring text
210 - check sublime snippet exists
211 - clip any useful text to docs mindmap
212 - regenerate the docs and check redendering of this docstring
213 """
214 self.log.debug('starting the ``_setup_tunnel`` method')
216 # TEST TUNNEL DOES NOT ALREADY EXIST
217 sshPort = tunnelParameters["port"]
218 connected = self._checkServer(
219 "127.0.0.1", sshPort)
220 if connected:
221 self.log.debug('ssh tunnel already exists - moving on')
222 else:
223 # GRAB TUNNEL SETTINGS FROM SETTINGS FILE
224 ru = tunnelParameters["remote user"]
225 rip = tunnelParameters["remote ip"]
226 rh = tunnelParameters["remote datbase host"]
228 cmd = "ssh -fnN %(ru)s@%(rip)s -L %(sshPort)s:%(rh)s:3306" % locals()
229 p = Popen(cmd, shell=True, close_fds=True)
230 output = p.communicate()[0]
231 self.log.debug('output: %(output)s' % locals())
233 # TEST CONNECTION - QUIT AFTER SO MANY TRIES
234 connected = False
235 count = 0
236 while not connected:
237 connected = self._checkServer(
238 "127.0.0.1", sshPort)
239 time.sleep(1)
240 count += 1
241 if count == 5:
242 self.log.error(
243 'cound not setup tunnel to remote datbase' % locals())
244 sys.exit(0)
245 return sshPort
247 def _checkServer(self, address, port):
248 """Check that the TCP Port we've decided to use for tunnelling is available
250 .. todo ::
252 - update key arguments values and definitions with defaults
253 - update return values and definitions
254 - update usage examples and text
255 - update docstring text
256 - check sublime snippet exists
257 - clip any useful text to docs mindmap
258 - regenerate the docs and check redendering of this docstring
259 """
260 self.log.debug('starting the ``_checkServer`` method')
262 # CREATE A TCP SOCKET
263 import socket
264 s = socket.socket()
265 self.log.debug(
266 """Attempting to connect to `%(address)s` on port `%(port)s`""" % locals())
267 try:
268 s.connect((address, port))
269 self.log.debug(
270 """Connected to `%(address)s` on port `%(port)s`""" % locals())
271 return True
272 except socket.error as e:
273 self.log.warning(
274 """Connection to `%(address)s` on port `%(port)s` failed - try again: %(e)s""" % locals())
275 return False
277 return None
279 # xt-class-method