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*the database object for sherlock, setting up ssh tunnels and various database connections* 

5 

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 

22 

23 

24class database(object): 

25 """ 

26 *the database object for sherlock, setting up ssh tunnels and various database connections* 

27 

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 

31 

32 **Key Arguments** 

33 

34 - ``log`` -- logger 

35 - ``settings`` -- the settings dictionary 

36 

37 

38 **Return** 

39 

40 - ``dbConns`` -- a dictionary of the database connections required by sherlock 

41 

42 

43 **Usage** 

44 

45 To setup the sherlock database connections, run the following: 

46 

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

58 

59 

60 .. todo :: 

61 

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 

71 

72 def __init__( 

73 self, 

74 log, 

75 settings=False, 

76 

77 ): 

78 self.log = log 

79 log.debug("instansiating a new '_database' object") 

80 self.settings = settings 

81 return None 

82 

83 def connect(self): 

84 """connect to the various databases, the credientals and settings of which are found in the sherlock settings file 

85 

86 **Return** 

87 

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 

90 

91 

92 See the class docstring for usage 

93 

94 .. todo :: 

95 

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

105 

106 # CATALOGUE DATABASE ALWAYS NEEDED 

107 catalogueSettings = self.settings[ 

108 "database settings"]["static catalogues"] 

109 

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 

117 

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

127 

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) 

147 

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) 

161 

162 # CREATE A DICTIONARY OF DATABASES 

163 dbConns = { 

164 "transients": dbConns[0], 

165 "catalogues": dbConns[1] 

166 } 

167 

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 

184 

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

186 return dbConns, dbVersions 

187 

188 def _setup_tunnel( 

189 self, 

190 tunnelParameters): 

191 """ 

192 *setup a ssh tunnel for a database connection to port through* 

193 

194 **Key Arguments** 

195 

196 - ``tunnelParameters`` -- the tunnel parameters found associated with the database settings 

197 

198 

199 **Return** 

200 

201 - ``sshPort`` -- the port the ssh tunnel is connected via 

202 

203 

204 .. todo :: 

205 

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

215 

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

227 

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

232 

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 

246 

247 def _checkServer(self, address, port): 

248 """Check that the TCP Port we've decided to use for tunnelling is available 

249 

250 .. todo :: 

251 

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

261 

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 

276 

277 return None 

278 

279 # xt-class-method