~launchpad-p-s/sofastatistics/main

« back to all changes in this revision

Viewing changes to dbe_plugins/dbe_mysql.py

  • Committer: Grant Paton-Simpson
  • Date: 2009-05-19 04:21:43 UTC
  • Revision ID: g@ubuntu-20090519042143-p561mbokz3inefvd
Initial import

Show diffs side-by-side

added added

removed removed

Lines of Context:
 
1
 
 
2
from __future__ import division # so 5/2 = 2.5 not 2 !
 
3
 
 
4
import MySQLdb
 
5
import wx
 
6
import pprint
 
7
 
 
8
import getdata
 
9
import util
 
10
 
 
11
BIGINT = "bigint"
 
12
DECIMAL = "decimal"
 
13
DOUBLE = "double"
 
14
FLOAT = "float"
 
15
INT = "int"
 
16
MEDIUMINT = "mediumint"
 
17
SMALLINT = "smallint"
 
18
TINYINT = "tinyint"
 
19
 
 
20
def quote_identifier(raw_val):
 
21
    return "`%s`" % raw_val
 
22
 
 
23
def DbeSyntaxElements():
 
24
    if_clause = "IF(%s, %s, %s)"
 
25
    abs_wrapper_l = ""
 
26
    abs_wrapper_r = ""
 
27
    return if_clause, abs_wrapper_l, abs_wrapper_r
 
28
 
 
29
 
 
30
class DbDets(getdata.DbDets):
 
31
 
 
32
    def getDbTbls(self, cur, db):
 
33
        "Get table names given database and cursor"
 
34
        SQL_get_tbl_names = """SELECT TABLE_NAME 
 
35
            FROM information_schema.TABLES
 
36
            WHERE TABLE_SCHEMA = '%s'
 
37
            UNION SELECT TABLE_NAME
 
38
            FROM information_schema.VIEWS
 
39
            WHERE TABLE_SCHEMA = '%s'""" % (db, db)
 
40
        cur.execute(SQL_get_tbl_names)
 
41
        tbls = [x[0] for x in cur.fetchall()] 
 
42
        tbls.sort(key=lambda s: s.upper())
 
43
        return tbls
 
44
    
 
45
    def GetMinMax(self, col_type, num_prec, dec_pts):
 
46
        """
 
47
        Use col_type not fld_type.  The former is inconsistent - float 
 
48
            and double have unsigned at end but not rest!
 
49
        Returns minimum and maximum allowable numeric values.
 
50
        NB even though a floating point type will not store values closer 
 
51
            to zero than a certain level, such values will be accepted here.
 
52
            The database will store these as zero.
 
53
        """
 
54
        if col_type.lower().startswith(TINYINT) \
 
55
                and not col_type.lower().endswith("unsigned"):
 
56
            min = -(2**7)
 
57
            max = (2**7)-1
 
58
        elif col_type.lower().startswith(TINYINT) \
 
59
                and col_type.lower().endswith("unsigned"):
 
60
            min = 0
 
61
            max = (2**8)-1
 
62
        elif col_type.lower().startswith(SMALLINT) \
 
63
                and not col_type.lower().endswith("unsigned"):
 
64
            min = -(2**15)
 
65
            max = (2**15)-1
 
66
        elif col_type.lower().startswith(SMALLINT) \
 
67
                and col_type.lower().endswith("unsigned"):
 
68
            min = 0
 
69
            max = (2**16)-1
 
70
        elif col_type.lower().startswith(MEDIUMINT) \
 
71
                and not col_type.lower().endswith("unsigned"):
 
72
            min = -(2**23)
 
73
            max = (2**23)-1
 
74
        elif col_type.lower().startswith(MEDIUMINT) \
 
75
                and col_type.lower().endswith("unsigned"):
 
76
            min = 0
 
77
            max = (2**24)-1
 
78
        elif col_type.lower().startswith(INT) \
 
79
                and not col_type.lower().endswith("unsigned"):
 
80
            min = -(2**31)
 
81
            max = (2**31)-1
 
82
        elif col_type.lower().startswith(INT) \
 
83
                and col_type.lower().endswith("unsigned"):
 
84
            min = 0
 
85
            max = (2**32)-1
 
86
        elif col_type.lower().startswith(BIGINT) \
 
87
                and not col_type.lower().endswith("unsigned"):
 
88
            min = -(2**63)
 
89
            max = (2**63)-1
 
90
        elif col_type.lower().startswith(BIGINT) \
 
91
                and col_type.lower().endswith("unsigned"):
 
92
            min = 0
 
93
            max = (2**64)-1
 
94
        elif col_type.lower().startswith(FLOAT) \
 
95
                and not col_type.lower().endswith("unsigned"):
 
96
            min = -3.402823466E+38
 
97
            max = 3.402823466E+38
 
98
        elif col_type.lower().startswith(FLOAT) \
 
99
                and col_type.lower().endswith("unsigned"):
 
100
            min = 0
 
101
            max = 3.402823466E+38
 
102
        elif col_type.lower().startswith(DOUBLE) \
 
103
                and not col_type.lower().endswith("unsigned"):
 
104
            min = -1.7976931348623157E+308
 
105
            max = 1.7976931348623157E+308
 
106
        elif col_type.lower().startswith(DOUBLE) \
 
107
                and col_type.lower().endswith("unsigned"):
 
108
            min = 0
 
109
            max = 1.7976931348623157E+308
 
110
        elif col_type.lower().startswith(DECIMAL) \
 
111
                and not col_type.lower().endswith("unsigned"):
 
112
            # e.g. 6,2 -> 9999.99
 
113
            abs_max = ((10**(num_prec + 1))-1)/(10**dec_pts)
 
114
            min = -abs_max
 
115
            max = abs_max
 
116
        elif col_type.lower().startswith(DECIMAL) \
 
117
                and col_type.lower().endswith("unsigned"):
 
118
            abs_max = ((10**(num_prec + 1))-1)/(10**dec_pts)
 
119
            min = 0
 
120
            max = abs_max
 
121
        else:
 
122
            min = None
 
123
            max = None
 
124
        return min, max
 
125
    
 
126
    def getTblFlds(self, cur, db, tbl):
 
127
        """
 
128
        Returns details for set of fields given database, table, and cursor.
 
129
        NUMERIC_SCALE - number of significant digits to right of decimal point.
 
130
            Null if not numeric.
 
131
        NUMERIC_SCALE will be Null if not numeric.
 
132
        """
 
133
        numeric_lst = [BIGINT, DECIMAL, DOUBLE, FLOAT, INT, MEDIUMINT, 
 
134
                       SMALLINT, TINYINT]
 
135
        numeric_full_lst = []
 
136
        for num_type in numeric_lst:
 
137
            numeric_full_lst.append(num_type)
 
138
            numeric_full_lst.append("%s unsigned" % num_type)
 
139
        numeric_IN_clause = "('" + "', '".join(numeric_full_lst) + "')"
 
140
        """SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME="" 
 
141
            AND TABLE_SCHEMA = "" """
 
142
        SQL_get_fld_dets = """SELECT 
 
143
            COLUMN_NAME,
 
144
                ORDINAL_POSITION - 1
 
145
            AS ord_pos,
 
146
            IS_NULLABLE,
 
147
            COLUMN_DEFAULT,
 
148
            DATA_TYPE,
 
149
            CHARACTER_MAXIMUM_LENGTH,
 
150
            CHARACTER_SET_NAME,
 
151
                LOWER(DATA_TYPE) IN %s """ % numeric_IN_clause + """
 
152
            AS bolnumeric,
 
153
                EXTRA = 'auto_increment'
 
154
            AS autonumber,
 
155
                NUMERIC_SCALE
 
156
            AS dec_pts,
 
157
            NUMERIC_PRECISION,
 
158
            COLUMN_TYPE,
 
159
                LOWER(DATA_TYPE) IN 
 
160
                ('date', 'time', 'datetime', 'timestamp', 'year')
 
161
            AS boldatetime,
 
162
                LOWER(DATA_TYPE) IN 
 
163
                ('timestamp')
 
164
            AS timestamp
 
165
            FROM INFORMATION_SCHEMA.COLUMNS
 
166
            WHERE TABLE_NAME=\"%s\"
 
167
            AND TABLE_SCHEMA = \"%s\" """ % (tbl, db) 
 
168
        cur.execute(SQL_get_fld_dets)
 
169
        fld_dets = cur.fetchall()
 
170
        # build dic of fields, each with dic of characteristics
 
171
        flds = {}
 
172
        for (fld_name, ord_pos, nullable, fld_default, fld_type, max_len, 
 
173
                 charset, numeric, autonum, dec_pts, num_prec, col_type, 
 
174
                 boldatetime, timestamp) in fld_dets:
 
175
            bolnullable = True if nullable == "YES" else False
 
176
            boldata_entry_ok = False if (autonum or timestamp) else True
 
177
            bolnumeric = True if numeric else False
 
178
            fld_txt = not bolnumeric and not boldatetime
 
179
            bolsigned = (col_type.find("unsigned") == -1)
 
180
            min_val, max_val = self.GetMinMax(col_type, num_prec, dec_pts)
 
181
            dets_dic = {
 
182
                        getdata.FLD_SEQ: ord_pos,
 
183
                        getdata.FLD_BOLNULLABLE: bolnullable,
 
184
                        getdata.FLD_DATA_ENTRY_OK: boldata_entry_ok,
 
185
                        getdata.FLD_COLUMN_DEFAULT: fld_default,
 
186
                        getdata.FLD_BOLTEXT: fld_txt,
 
187
                        getdata.FLD_TEXT_LENGTH: max_len,
 
188
                        getdata.FLD_CHARSET: charset,
 
189
                        getdata.FLD_BOLNUMERIC: bolnumeric,
 
190
                        getdata.FLD_BOLAUTONUMBER: autonum,
 
191
                        getdata.FLD_DECPTS: dec_pts,
 
192
                        getdata.FLD_NUM_WIDTH: num_prec,
 
193
                        getdata.FLD_BOL_NUM_SIGNED: bolsigned,
 
194
                        getdata.FLD_NUM_MIN_VAL: min_val,
 
195
                        getdata.FLD_NUM_MAX_VAL: max_val,
 
196
                        getdata.FLD_BOLDATETIME: boldatetime,
 
197
                        }
 
198
            flds[fld_name] = dets_dic
 
199
        return flds
 
200
 
 
201
    def getIndexDets(self, cur, db, tbl):
 
202
        """
 
203
        has_unique - boolean
 
204
        idxs = [idx0, idx1, ...]
 
205
        each idx is a dict name, is_unique, flds
 
206
        """
 
207
        SQL_get_index_dets = """SELECT 
 
208
            INDEX_NAME, 
 
209
                GROUP_CONCAT(COLUMN_NAME) 
 
210
            AS fld_names,
 
211
                NOT NON_UNIQUE 
 
212
            AS unique_index
 
213
            FROM INFORMATION_SCHEMA.STATISTICS
 
214
            WHERE table_name = "%s"
 
215
            AND table_schema = "%s"
 
216
            GROUP BY INDEX_NAME""" % (tbl, db)
 
217
        cur.execute(SQL_get_index_dets)
 
218
        index_dets = cur.fetchall()
 
219
        # [(INDEX_NAME, fld_names, unique_index), ...]
 
220
        # initialise
 
221
        has_unique = False
 
222
        idxs = []
 
223
        for idx_name, raw_fld_names, unique_index in index_dets:
 
224
            fld_names = [x.strip() for x in raw_fld_names.split(",")]
 
225
            if unique_index:
 
226
                has_unique = True
 
227
            idx_dic = {getdata.IDX_NAME: idx_name, 
 
228
                       getdata.IDX_IS_UNIQUE: unique_index, 
 
229
                       getdata.IDX_FLDS: fld_names}
 
230
            idxs.append(idx_dic)
 
231
        debug = False
 
232
        if debug:
 
233
            pprint.pprint(idxs)
 
234
            print has_unique
 
235
        return has_unique, idxs
 
236
 
 
237
    def getDbDets(self):
 
238
        """
 
239
        Return connection, cursor, and get lists of 
 
240
            databases, tables, and fields 
 
241
            based on the MySQL database connection details provided.
 
242
        The database used will be the first if none provided.
 
243
        The table used will be the first if none provided.
 
244
        The field dets will be taken from the table used.
 
245
        Returns conn, cur, dbs, tbls, flds, has_unique, idxs.
 
246
        """
 
247
        conn_dets_mysql = self.conn_dets.get(getdata.DBE_MYSQL)
 
248
        if not conn_dets_mysql:
 
249
            raise Exception, "No connection details available for MySQL"
 
250
        conn = MySQLdb.connect(**conn_dets_mysql)
 
251
        cur = conn.cursor() # must return tuples not dics
 
252
        # get database name
 
253
        SQL_get_db_names = """SELECT SCHEMA_NAME 
 
254
            FROM information_schema.SCHEMATA
 
255
            WHERE SCHEMA_NAME <> 'information_schema'"""
 
256
        cur.execute(SQL_get_db_names)
 
257
        dbs = [x[0] for x in cur.fetchall()]
 
258
        # get table names (from first db if none provided)
 
259
        try:
 
260
            conn.select_db(self.db)
 
261
            db_to_use = self.db
 
262
        except Exception:
 
263
            db_to_use = dbs[0]
 
264
        tbls = self.getDbTbls(cur, db_to_use)
 
265
        # get field names (from first table if none provided)
 
266
        tbl_to_use = self.tbl if self.tbl else tbls[0]
 
267
        flds = self.getTblFlds(cur, db_to_use, tbl_to_use)
 
268
        has_unique, idxs = self.getIndexDets(cur, db_to_use, tbl_to_use)
 
269
        debug = False
 
270
        if debug:
 
271
            print self.db
 
272
            print self.tbl
 
273
            pprint.pprint(tbls)
 
274
            pprint.pprint(flds)
 
275
            pprint.pprint(idxs)
 
276
        return conn, cur, dbs, tbls, flds, has_unique, idxs
 
277
 
 
278
 
 
279
def InsertRow(conn, cur, tbl_name, data):
 
280
    """
 
281
    data = [(value as string, fld_name, fld_dets), ...]
 
282
    Modify any values (according to field details) to be ready for insertion.
 
283
    Use placeholders in execute statement.
 
284
    Commit insert statement.
 
285
    """
 
286
    debug = False
 
287
    # pprint.pprint(data)
 
288
    fld_dics = [x[2] for x in data]
 
289
    fld_names = [x[1] for x in data]
 
290
    fld_names_clause = " (`" + "`, `".join(fld_names) + "`) "
 
291
    # e.g. (`fname`, `lname`, `dob` ...)
 
292
    fld_placeholders_clause = " (" + \
 
293
        ", ".join(["%s" for x in range(len(data))]) + ") "
 
294
    # e.g. " (%s, %s, %s ...) "
 
295
    SQL_insert = "INSERT INTO `%s` " % tbl_name + fld_names_clause + \
 
296
        "VALUES %s" % fld_placeholders_clause
 
297
    if debug: print SQL_insert
 
298
    data_lst = []
 
299
    for i, data_dets in enumerate(data):
 
300
        if debug: pprint.pprint(data_dets)
 
301
        val, fld_name, fld_dic = data_dets
 
302
        if val == None:
 
303
            val2add = val
 
304
        elif fld_dic[getdata.FLD_BOLDATETIME]:
 
305
            valid_datetime, t = util.datetime_str_valid(val)
 
306
            if not valid_datetime:
 
307
                raise Exception, "Invalid datetime \"%s\" slipped through" % \
 
308
                    val
 
309
            else:
 
310
                if debug: print t
 
311
            val2add = "%s-%s-%s %s:%s:%s" % (t[0], t[1], t[2], 
 
312
                                             t[3], t[4], t[5])
 
313
        else:
 
314
            val2add = val
 
315
        if debug: print str(val2add) 
 
316
        data_lst.append(val2add)
 
317
    data_tup = tuple(data_lst)
 
318
    if debug: pprint.pprint(data_tup)
 
319
    try:
 
320
        cur.execute(SQL_insert, data_tup)
 
321
        conn.commit()
 
322
        return True
 
323
    except Exception, e:
 
324
        if debug: print "Failed to insert row.  SQL: %s, Data: %s" % \
 
325
            (SQL_insert, str(data_tup)) + "\n\nOriginal error: %s" % e
 
326
        return False
 
327
 
 
328
def setDataConnGui(parent, read_only, scroll, szr, lblfont):
 
329
    ""
 
330
    # default database
 
331
    parent.lblMysqlDefaultDb = wx.StaticText(scroll, -1, "Default Database:")
 
332
    parent.lblMysqlDefaultDb.SetFont(lblfont)
 
333
    mysql_default_db = parent.mysql_default_db if parent.mysql_default_db \
 
334
        else ""
 
335
    parent.txtMysqlDefaultDb = wx.TextCtrl(scroll, -1, 
 
336
                                           mysql_default_db, 
 
337
                                           size=(250,-1))
 
338
    parent.txtMysqlDefaultDb.Enable(not read_only)
 
339
    # default table
 
340
    parent.lblMysqlDefaultTbl = wx.StaticText(scroll, -1, 
 
341
                                       "Default Table:")
 
342
    parent.lblMysqlDefaultTbl.SetFont(lblfont)
 
343
    mysql_default_tbl = parent.mysql_default_tbl if parent.mysql_default_tbl \
 
344
        else ""
 
345
    parent.txtMysqlDefaultTbl = wx.TextCtrl(scroll, -1, 
 
346
                                            mysql_default_tbl, 
 
347
                                            size=(250,-1))
 
348
    parent.txtMysqlDefaultTbl.Enable(not read_only)
 
349
    # host
 
350
    parent.lblMysqlHost = wx.StaticText(scroll, -1, "Host:")
 
351
    parent.lblMysqlHost.SetFont(lblfont)
 
352
    mysql_host = parent.mysql_host if parent.mysql_host else ""
 
353
    parent.txtMysqlHost = wx.TextCtrl(scroll, -1, mysql_host, 
 
354
                                      size=(100,-1))
 
355
    parent.txtMysqlHost.Enable(not read_only)
 
356
    # user
 
357
    parent.lblMysqlUser = wx.StaticText(scroll, -1, "User:")
 
358
    parent.lblMysqlUser.SetFont(lblfont)
 
359
    mysql_user = parent.mysql_user if parent.mysql_user else ""
 
360
    parent.txtMysqlUser = wx.TextCtrl(scroll, -1, mysql_user, 
 
361
                                      size=(100,-1))
 
362
    parent.txtMysqlUser.Enable(not read_only)
 
363
    # password
 
364
    parent.lblMysqlPwd = wx.StaticText(scroll, -1, "Password:")
 
365
    parent.lblMysqlPwd.SetFont(lblfont)
 
366
    mysql_pwd = parent.mysql_pwd if parent.mysql_pwd else ""
 
367
    parent.txtMysqlPwd = wx.TextCtrl(scroll, -1, mysql_pwd, 
 
368
                                     size=(300,-1))
 
369
    parent.txtMysqlPwd.Enable(not read_only)
 
370
    #2 MYSQL
 
371
    bxMysql= wx.StaticBox(scroll, -1, "MySQL")
 
372
    parent.szrMysql = wx.StaticBoxSizer(bxMysql, wx.VERTICAL)
 
373
    #3 MYSQL INNER
 
374
    #4 MYSQL INNER TOP
 
375
    szrMysqlInnerTop = wx.BoxSizer(wx.HORIZONTAL)
 
376
    # default database
 
377
    szrMysqlInnerTop.Add(parent.lblMysqlDefaultDb, 0, wx.LEFT|wx.RIGHT, 5)
 
378
    szrMysqlInnerTop.Add(parent.txtMysqlDefaultDb, 1, wx.GROW|wx.RIGHT, 10)
 
379
    # default table
 
380
    szrMysqlInnerTop.Add(parent.lblMysqlDefaultTbl, 0, wx.LEFT|wx.RIGHT, 5)
 
381
    szrMysqlInnerTop.Add(parent.txtMysqlDefaultTbl, 1, wx.GROW|wx.RIGHT, 10)
 
382
    #4 MYSQL INNER BOTTOM
 
383
    szrMysqlInnerBtm = wx.BoxSizer(wx.HORIZONTAL)
 
384
    # host 
 
385
    szrMysqlInnerBtm.Add(parent.lblMysqlHost, 0, wx.LEFT|wx.RIGHT, 5)
 
386
    szrMysqlInnerBtm.Add(parent.txtMysqlHost, 0, wx.RIGHT, 10)
 
387
    # user
 
388
    szrMysqlInnerBtm.Add(parent.lblMysqlUser, 0, wx.LEFT|wx.RIGHT, 5)
 
389
    szrMysqlInnerBtm.Add(parent.txtMysqlUser, 0, wx.RIGHT, 10)
 
390
    # password
 
391
    szrMysqlInnerBtm.Add(parent.lblMysqlPwd, 0, wx.LEFT|wx.RIGHT, 5)
 
392
    szrMysqlInnerBtm.Add(parent.txtMysqlPwd, 1, wx.GROW|wx.RIGHT, 10)
 
393
    #2 combine
 
394
    parent.szrMysql.Add(szrMysqlInnerTop, 0, wx.GROW|wx.ALL, 5)
 
395
    parent.szrMysql.Add(szrMysqlInnerBtm, 0, wx.ALL, 5)
 
396
    szr.Add(parent.szrMysql, 0, wx.GROW|wx.ALL, 10)
 
397
    
 
398
def getProjSettings(parent, proj_dic):
 
399
    ""
 
400
    parent.mysql_default_db = proj_dic["default_dbs"][getdata.DBE_MYSQL]
 
401
    parent.mysql_default_tbl = proj_dic["default_tbls"][getdata.DBE_MYSQL]
 
402
    # optional (although if any mysql, for eg, must have all)
 
403
    if proj_dic["conn_dets"].get(getdata.DBE_MYSQL):
 
404
        parent.mysql_host = proj_dic["conn_dets"][getdata.DBE_MYSQL]["host"]
 
405
        parent.mysql_user = proj_dic["conn_dets"][getdata.DBE_MYSQL]["user"]
 
406
        parent.mysql_pwd = proj_dic["conn_dets"][getdata.DBE_MYSQL]["passwd"]
 
407
    else:
 
408
        parent.mysql_host, parent.mysql_user, parent.mysql_pwd = "", "", ""
 
409
 
 
410
def setConnDetDefaults(parent):
 
411
    try:
 
412
        parent.mysql_default_db
 
413
    except AttributeError:
 
414
        parent.mysql_default_db = ""
 
415
    try:
 
416
        parent.mysql_default_tbl
 
417
    except AttributeError: 
 
418
        parent.mysql_default_tbl = ""
 
419
    try:
 
420
        parent.mysql_host
 
421
    except AttributeError: 
 
422
        parent.mysql_host = ""
 
423
    try:
 
424
        parent.mysql_user
 
425
    except AttributeError: 
 
426
        parent.mysql_user = ""
 
427
    try:            
 
428
        parent.mysql_pwd
 
429
    except AttributeError: 
 
430
        parent.mysql_pwd = ""
 
431
    
 
432
def processConnDets(parent, default_dbs, default_tbls, conn_dets):
 
433
    mysql_default_db = parent.txtMysqlDefaultDb.GetValue()
 
434
    mysql_default_tbl = parent.txtMysqlDefaultTbl.GetValue()
 
435
    mysql_host = parent.txtMysqlHost.GetValue()
 
436
    mysql_user = parent.txtMysqlUser.GetValue()
 
437
    mysql_pwd = parent.txtMysqlPwd.GetValue()
 
438
    has_mysql_conn = mysql_host and mysql_user and mysql_pwd \
 
439
        and mysql_default_db and mysql_default_tbl
 
440
    incomplete_mysql = (mysql_host or mysql_user or mysql_pwd \
 
441
        or mysql_default_db or mysql_default_tbl) and not has_mysql_conn
 
442
    if incomplete_mysql:
 
443
        wx.MessageBox("The MySQL details are incomplete")
 
444
        parent.txtMysqlDefaultDb.SetFocus()
 
445
    default_dbs[getdata.DBE_MYSQL] = mysql_default_db \
 
446
        if mysql_default_db else None    
 
447
    default_tbls[getdata.DBE_MYSQL] = mysql_default_tbl \
 
448
        if mysql_default_tbl else None
 
449
    if mysql_host and mysql_user and mysql_pwd:
 
450
        conn_dets_mysql = {"host": mysql_host, "user": mysql_user, 
 
451
                           "passwd": mysql_pwd}
 
452
        conn_dets[getdata.DBE_MYSQL] = conn_dets_mysql
 
453
    return incomplete_mysql, has_mysql_conn
 
454
    
 
455
    
 
 
b'\\ No newline at end of file'