Cubulus/sqlutil.py at master · ddimmich/Cubulus · GitHub

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
import cherrypy, logging, time

from constants import *

from DBUtils.PooledDB import PooledDB

if DBLIB == 'mysql':
	#you will have to manually install MySQLdb
	import MySQLdb
elif DBLIB == 'postgres':
	import bpgsql
elif DBLIB == 'monet5':
	import MonetSQLdb
else:
	try:
		#Python2.5 has builtin sqlite3
		from sqlite3 import dbapi2 as sqlite
	except:
		#in Python 2.4 (and earlier?) you can install for ex: pysqlite package
		from pysqlite2 import dbapi2 as sqlite
	#you can't login & logoff SQLite
	from DBUtils.PersistentDB import PersistentDB


################################
dbServersPool = [] # init later
################################

def getPW():
	def searchHelper(s, subs):
		start = s.find(subs) + len(subs)+2
		end = s.find('"', start+1)
		res = s[start:end]
		return res

	try:
		f=open(PASSWORDFILE,'r')
		s=f.read()
		f.close()
		return (searchHelper(s, 'password'),searchHelper(s, 'user'))
	except IOError:
		return ('','')

def dbInit(workerThreadNr):
	(pw,usr) = getPW()
	for i in xrange(len(DBPORTS)):
		try:
			srv = (workerThreadNr + i) % THREADPOOL_SECONDARY
			if DBLIB == 'mysql':
				if DBSERVERS[srv] != 'localhost':
					res = PooledDB(MySQLdb, \
						mincached = 1, maxcached = 1+THREADPOOL_PRIMARY,
						maxconnections = 1+THREADPOOL_SECONDARY,\
						host = DBSERVERS[srv], \
						port = DBPORTS[srv], \
						db = DATABASE, passwd = pw, user = usr)
				else:
					res = PooledDB(MySQLdb, \
						mincached = 1, maxcached = 1+THREADPOOL_PRIMARY,
						maxconnections = 1+THREADPOOL_SECONDARY,\
						db = DATABASE, passwd = pw, user = usr)

			elif DBLIB == 'postgres':
				res = PooledDB(bpgsql, \
					host = DBSERVERS[srv], \
					dbname = DATABASE, user = 'cubulus', password='cubulus')
			elif DBLIB == 'monet5':
				res = PooledDB(MonetSQLdb, host = DBSERVERS[srv], lang = 'sql')
			else:
				res = PersistentDB(sqlite, 0, database = "cubulus.db")
			print 'dbInit()', res
			return res
		except: # OperationalError:
			print 'error init connection to %s:%d' % \
				(DBSERVERS[srv] , DBPORTS[srv])

	try:
		cherrypy.log('all databases down !!!', \
			context='dbInit', \
			severity=logging.ERROR, traceback=False)
	except:
		pass
	print 'dbInit: all databases down !!!'


def poolInit():
	return [dbInit(workerThreadNr) \
		for workerThreadNr in xrange(THREADPOOL_SECONDARY)]

################################
def runSQL_uncached(sql, workerThreadNr=0):
	global dbServersPool
	if SQLDEBUG:
		print '>'*5,sql
	try:
		#prevent repeated initialization of connections
		dummy = dbServersPool[0]
	except IndexError:
		dbServersPool = poolInit()

	for reconnect in xrange(2):
		for i in xrange(len(DBPORTS)):
			try:
				dbIdx = (workerThreadNr + i) % len(DBPORTS)
				#find working server, starting with assigned one
				db = dbServersPool[dbIdx].connection()

				c = db.cursor()
				c.execute(sql)
				res = c.fetchall()
				c.close()
				try:
					#fails for PersistentDB , ex: for sqlite
					dbServersPool[dbIdx].close()
				except AttributeError:
					pass

				if SQLDEBUG:
					print '>'*5,'.'*5, res
					#c.execute('explain partitions '+sql)
					#print '>plan>', c.fetchall()
				return res

			except:
				import traceback
				print traceback.print_exc()
				emsg = DBSERVERS[dbIdx] + ':' + str(DBPORTS[dbIdx])
				try:
					cherrypy.log('database %s down !!!' % emsg, \
						context='runSQL_uncached', \
						severity=logging.ERROR, traceback=False)
				except:
					pass
				print 'runSQL_uncached: database %s down !!!' % emsg


		#try re-connecting again to all servers
		#some may be live again
		#
		time.sleep(2)
		#dbServersPool = poolInit()


	try:
		cherrypy.log('all databases down !!!', \
			context='runSQL_uncached', \
			severity=logging.ERROR, traceback=False)
	except:
		pass
	print 'all databases down !!!'
	return None