· 10 years ago · Aug 16, 2016, 11:44 AM
1# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
2# MIT License. See license.txt
3
4# Database Module
5# --------------------
6
7from __future__ import unicode_literals
8import MySQLdb
9from MySQLdb.times import DateTimeDeltaType
10from markdown2 import UnicodeWithAttrs
11import warnings
12import datetime
13import frappe
14import frappe.defaults
15import frappe.async
16import re
17import frappe.model.meta
18from frappe.utils import now, get_datetime, cstr
19from frappe import _
20from types import StringType, UnicodeType
21
22class Database:
23 """
24 Open a database connection with the given parmeters, if use_default is True, use the
25 login details from `conf.py`. This is called by the request handler and is accessible using
26 the `db` global variable. the `sql` method is also global to run queries
27 """
28 def __init__(self, host=None, user=None, password=None, ac_name=None, use_default = 0):
29 self.host = host or frappe.conf.db_host or 'localhost'
30 self.user = user or frappe.conf.db_name
31 self._conn = None
32
33 if ac_name:
34 self.user = self.get_db_login(ac_name) or frappe.conf.db_name
35
36 if use_default:
37 self.user = frappe.conf.db_name
38
39 self.transaction_writes = 0
40 self.auto_commit_on_many_writes = 0
41
42 self.password = password or frappe.conf.db_password
43 self.value_cache = {}
44
45 def get_db_login(self, ac_name):
46 return ac_name
47
48 def connect(self):
49 """Connects to a database as set in `site_config.json`."""
50 warnings.filterwarnings('ignore', category=MySQLdb.Warning)
51 self._conn = MySQLdb.connect(user=self.user, host=self.host, passwd=self.password,
52 use_unicode=True, charset='utf8')
53 self._conn.converter[246]=float
54 self._conn.converter[12]=get_datetime
55 self._conn.encoders[UnicodeWithAttrs] = self._conn.encoders[UnicodeType]
56 self._conn.encoders[DateTimeDeltaType] = self._conn.encoders[StringType]
57
58 MYSQL_OPTION_MULTI_STATEMENTS_OFF = 1
59 self._conn.set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_OFF)
60
61 self._cursor = self._conn.cursor()
62 if self.user != 'root':
63 self.use(self.user)
64 frappe.local.rollback_observers = []
65
66 def use(self, db_name):
67 """`USE` db_name."""
68 self._conn.select_db(db_name)
69 self.cur_db_name = db_name
70
71 def validate_query(self, q):
72 """Throw exception for dangerous queries: `ALTER`, `DROP`, `TRUNCATE` if not `Administrator`."""
73 cmd = q.strip().lower().split()[0]
74 if cmd in ['alter', 'drop', 'truncate'] and frappe.session.user != 'Administrator':
75 frappe.throw(_("Not permitted"), frappe.PermissionError)
76
77 def sql(self, query, values=(), as_dict = 0, as_list = 0, formatted = 0,
78 debug=0, ignore_ddl=0, as_utf8=0, auto_commit=0, update=None):
79 """Execute a SQL query and fetch all rows.
80
81 :param query: SQL query.
82 :param values: List / dict of values to be escaped and substituted in the query.
83 :param as_dict: Return as a dictionary.
84 :param as_list: Always return as a list.
85 :param formatted: Format values like date etc.
86 :param debug: Print query and `EXPLAIN` in debug log.
87 :param ignore_ddl: Catch exception if table, column missing.
88 :param as_utf8: Encode values as UTF 8.
89 :param auto_commit: Commit after executing the query.
90 :param update: Update this dict to all rows (if returned `as_dict`).
91
92 Examples:
93
94 # return customer names as dicts
95 frappe.db.sql("select name from tabCustomer", as_dict=True)
96
97 # return names beginning with a
98 frappe.db.sql("select name from tabCustomer where name like %s", "a%")
99
100 # values as dict
101 frappe.db.sql("select name from tabCustomer where name like %(name)s and owner=%(owner)s",
102 {"name": "a%", "owner":"test@example.com"})
103
104 """
105 if not self._conn:
106 self.connect()
107
108 # in transaction validations
109 self.check_transaction_status(query)
110
111 # autocommit
112 if auto_commit: self.commit()
113
114 # execute
115 try:
116 if values!=():
117 if isinstance(values, dict):
118 values = dict(values)
119
120 # MySQL-python==1.2.5 hack!
121 if not isinstance(values, (dict, tuple, list)):
122 values = (values,)
123
124 if debug:
125 try:
126 self.explain_query(query, values)
127 frappe.errprint(query % values)
128 except TypeError:
129 frappe.errprint([query, values])
130
131 if (frappe.conf.get("logging") or False)==2:
132 frappe.log("<<<< query")
133 frappe.log(query)
134 frappe.log("with values:")
135 frappe.log(values)
136 frappe.log(">>>>")
137 self._cursor.execute(query, values)
138
139 else:
140 if debug:
141 self.explain_query(query)
142 frappe.errprint(query)
143 if (frappe.conf.get("logging") or False)==2:
144 frappe.log("<<<< query")
145 frappe.log(query)
146 frappe.log(">>>>")
147
148 self._cursor.execute(query)
149
150 except Exception, e:
151 # ignore data definition errors
152 if ignore_ddl and e.args[0] in (1146,1054,1091):
153 pass
154
155 # NOTE: causes deadlock
156 # elif e.args[0]==2006:
157 # # mysql has gone away
158 # self.connect()
159 # return self.sql(query=query, values=values,
160 # as_dict=as_dict, as_list=as_list, formatted=formatted,
161 # debug=debug, ignore_ddl=ignore_ddl, as_utf8=as_utf8,
162 # auto_commit=auto_commit, update=update)
163
164 else:
165 raise
166
167 if auto_commit: self.commit()
168
169 # scrub output if required
170 if as_dict:
171 ret = self.fetch_as_dict(formatted, as_utf8)
172 if update:
173 for r in ret:
174 r.update(update)
175 return ret
176 elif as_list:
177 return self.convert_to_lists(self._cursor.fetchall(), formatted, as_utf8)
178 elif as_utf8:
179 return self.convert_to_lists(self._cursor.fetchall(), formatted, as_utf8)
180 else:
181 return self._cursor.fetchall()
182
183 def explain_query(self, query, values=None):
184 """Print `EXPLAIN` in error log."""
185 try:
186 frappe.errprint("--- query explain ---")
187 if values is None:
188 self._cursor.execute("explain " + query)
189 else:
190 self._cursor.execute("explain " + query, values)
191 import json
192 frappe.errprint(json.dumps(self.fetch_as_dict(), indent=1))
193 frappe.errprint("--- query explain end ---")
194 except:
195 frappe.errprint("error in query explain")
196
197 def sql_list(self, query, values=(), debug=False):
198 """Return data as list of single elements (first column).
199
200 Example:
201
202 # doctypes = ["DocType", "DocField", "User", ...]
203 doctypes = frappe.db.sql_list("select name from DocType")
204 """
205 return [r[0] for r in self.sql(query, values, debug=debug)]
206
207 def sql_ddl(self, query, values=(), debug=False):
208 """Commit and execute a query. DDL (Data Definition Language) queries that alter schema
209 autocommit in MariaDB."""
210 self.commit()
211 self.sql(query, debug=debug)
212
213 def check_transaction_status(self, query):
214 """Raises exception if more than 20,000 `INSERT`, `UPDATE` queries are
215 executed in one transaction. This is to ensure that writes are always flushed otherwise this
216 could cause the system to hang."""
217 if self.transaction_writes and \
218 query and query.strip().split()[0].lower() in ['start', 'alter', 'drop', 'create', "begin", "truncate"]:
219 raise Exception, 'This statement can cause implicit commit'
220
221 if query and query.strip().lower() in ('commit', 'rollback'):
222 self.transaction_writes = 0
223
224 if query[:6].lower() in ('update', 'insert', 'delete'):
225 self.transaction_writes += 1
226 if self.transaction_writes > 2000000:
227 if self.auto_commit_on_many_writes:
228 frappe.db.commit()
229 else:
230 frappe.throw(_("Too many writes in one request. Please send smaller requests"), frappe.ValidationError)
231
232 def fetch_as_dict(self, formatted=0, as_utf8=0):
233 """Internal. Converts results to dict."""
234 result = self._cursor.fetchall()
235 ret = []
236 needs_formatting = self.needs_formatting(result, formatted)
237
238 for r in result:
239 row_dict = frappe._dict({})
240 for i in range(len(r)):
241 if needs_formatting:
242 val = self.convert_to_simple_type(r[i], formatted)
243 else:
244 val = r[i]
245
246 if as_utf8 and type(val) is unicode:
247 val = val.encode('utf-8')
248 row_dict[self._cursor.description[i][0]] = val
249 ret.append(row_dict)
250 return ret
251
252 def needs_formatting(self, result, formatted):
253 """Returns true if the first row in the result has a Date, Datetime, Long Int."""
254 if result and result[0]:
255 for v in result[0]:
256 if isinstance(v, (datetime.date, datetime.timedelta, datetime.datetime, long)):
257 return True
258 if formatted and isinstance(v, (int, float)):
259 return True
260
261 return False
262
263 def get_description(self):
264 """Returns result metadata."""
265 return self._cursor.description
266
267 def convert_to_simple_type(self, v, formatted=0):
268 """Format date, time, longint values."""
269 return v
270
271 from frappe.utils import formatdate, fmt_money
272
273 if isinstance(v, (datetime.date, datetime.timedelta, datetime.datetime, long)):
274 if isinstance(v, datetime.date):
275 v = unicode(v)
276 if formatted:
277 v = formatdate(v)
278
279 # time
280 elif isinstance(v, (datetime.timedelta, datetime.datetime)):
281 v = unicode(v)
282
283 # long
284 elif isinstance(v, long):
285 v=int(v)
286
287 # convert to strings... (if formatted)
288 if formatted:
289 if isinstance(v, float):
290 v=fmt_money(v)
291 elif isinstance(v, int):
292 v = unicode(v)
293
294 return v
295
296 def convert_to_lists(self, res, formatted=0, as_utf8=0):
297 """Convert tuple output to lists (internal)."""
298 nres = []
299 needs_formatting = self.needs_formatting(res, formatted)
300 for r in res:
301 nr = []
302 for c in r:
303 if needs_formatting:
304 val = self.convert_to_simple_type(c, formatted)
305 else:
306 val = c
307 if as_utf8 and type(val) is unicode:
308 val = val.encode('utf-8')
309 nr.append(val)
310 nres.append(nr)
311 return nres
312
313 def convert_to_utf8(self, res, formatted=0):
314 """Encode result as UTF-8."""
315 nres = []
316 for r in res:
317 nr = []
318 for c in r:
319 if type(c) is unicode:
320 c = c.encode('utf-8')
321 nr.append(self.convert_to_simple_type(c, formatted))
322 nres.append(nr)
323 return nres
324
325 def build_conditions(self, filters):
326 """Convert filters sent as dict, lists to SQL conditions. filter's key
327 is passed by map function, build conditions like:
328
329 * ifnull(`fieldname`, default_value) = %(fieldname)s
330 * `fieldname` [=, !=, >, >=, <, <=] %(fieldname)s
331 """
332 conditions = []
333 values = {}
334 def _build_condition(key):
335 """
336 filter's key is passed by map function
337 build conditions like:
338 * ifnull(`fieldname`, default_value) = %(fieldname)s
339 * `fieldname` [=, !=, >, >=, <, <=] %(fieldname)s
340 """
341 _operator = "="
342 _rhs = " %(" + key + ")s"
343 value = filters.get(key)
344 values[key] = value
345 if isinstance(value, (list, tuple)):
346 # value is a tuble like ("!=", 0)
347 _operator = value[0]
348 values[key] = value[1]
349 if isinstance(value[1], (tuple, list)):
350 # value is a list in tuple ("in", ("A", "B"))
351 inner_list = []
352 for i, v in enumerate(value[1]):
353 inner_key = "{0}_{1}".format(key, i)
354 values[inner_key] = v
355 inner_list.append("%({0})s".format(inner_key))
356
357 _rhs = " ({0})".format(", ".join(inner_list))
358 del values[key]
359
360 if _operator not in ["=", "!=", ">", ">=", "<", "<=", "like", "in", "not in", "not like"]:
361 _operator = "="
362
363 if "[" in key:
364 split_key = key.split("[")
365 condition = "ifnull(`" + split_key[0] + "`, " + split_key[1][:-1] + ") " \
366 + _operator + _rhs
367 else:
368 condition = "`" + key + "` " + _operator + _rhs
369
370 conditions.append(condition)
371
372 if isinstance(filters, basestring):
373 filters = { "name": filters }
374
375 for f in filters:
376 _build_condition(f)
377
378 return " and ".join(conditions), values
379
380 def get(self, doctype, filters=None, as_dict=True, cache=False):
381 """Returns `get_value` with fieldname='*'"""
382 return self.get_value(doctype, filters, "*", as_dict=as_dict, cache=cache)
383
384 def get_value(self, doctype, filters=None, fieldname="name", ignore=None, as_dict=False,
385 debug=False, cache=False):
386 """Returns a document property or list of properties.
387
388 :param doctype: DocType name.
389 :param filters: Filters like `{"x":"y"}` or name of the document. `None` if Single DocType.
390 :param fieldname: Column name.
391 :param ignore: Don't raise exception if table, column is missing.
392 :param as_dict: Return values as dict.
393 :param debug: Print query in error log.
394
395 Example:
396
397 # return first customer starting with a
398 frappe.db.get_value("Customer", {"name": ("like a%")})
399
400 # return last login of **User** `test@example.com`
401 frappe.db.get_value("User", "test@example.com", "last_login")
402
403 last_login, last_ip = frappe.db.get_value("User", "test@example.com",
404 ["last_login", "last_ip"])
405
406 # returns default date_format
407 frappe.db.get_value("System Settings", None, "date_format")
408 """
409
410 ret = self.get_values(doctype, filters, fieldname, ignore, as_dict, debug, cache=cache)
411
412 return ((len(ret[0]) > 1 or as_dict) and ret[0] or ret[0][0]) if ret else None
413
414 def get_values(self, doctype, filters=None, fieldname="name", ignore=None, as_dict=False,
415 debug=False, order_by=None, update=None, cache=False):
416 """Returns multiple document properties.
417
418 :param doctype: DocType name.
419 :param filters: Filters like `{"x":"y"}` or name of the document.
420 :param fieldname: Column name.
421 :param ignore: Don't raise exception if table, column is missing.
422 :param as_dict: Return values as dict.
423 :param debug: Print query in error log.
424
425 Example:
426
427 # return first customer starting with a
428 customers = frappe.db.get_values("Customer", {"name": ("like a%")})
429
430 # return last login of **User** `test@example.com`
431 user = frappe.db.get_values("User", "test@example.com", "*")[0]
432 """
433 out = None
434 if cache and isinstance(filters, basestring) and \
435 (doctype, filters, fieldname) in self.value_cache:
436 return self.value_cache[(doctype, filters, fieldname)]
437
438 if isinstance(filters, list):
439 out = self._get_value_for_many_names(doctype, filters, fieldname, debug=debug)
440
441 else:
442 fields = fieldname
443 if fieldname!="*":
444 if isinstance(fieldname, basestring):
445 fields = [fieldname]
446 else:
447 fields = fieldname
448
449 if (filters is not None) and (filters!=doctype or doctype=="DocType"):
450 try:
451 out = self._get_values_from_table(fields, filters, doctype, as_dict, debug, order_by, update)
452 except Exception, e:
453 if ignore and e.args[0] in (1146, 1054):
454 # table or column not found, return None
455 out = None
456 elif (not ignore) and e.args[0]==1146:
457 # table not found, look in singles
458 out = self.get_values_from_single(fields, filters, doctype, as_dict, debug, update)
459 else:
460 raise
461 else:
462 out = self.get_values_from_single(fields, filters, doctype, as_dict, debug, update)
463
464 if cache and isinstance(filters, basestring):
465 self.value_cache[(doctype, filters, fieldname)] = out
466
467 return out
468
469 def get_values_from_single(self, fields, filters, doctype, as_dict=False, debug=False, update=None):
470 """Get values from `tabSingles` (Single DocTypes) (internal).
471
472 :param fields: List of fields,
473 :param filters: Filters (dict).
474 :param doctype: DocType name.
475 """
476 # TODO
477 # if not frappe.model.meta.is_single(doctype):
478 # raise frappe.DoesNotExistError("DocType", doctype)
479
480 if fields=="*" or isinstance(filters, dict):
481 # check if single doc matches with filters
482 values = self.get_singles_dict(doctype)
483 if isinstance(filters, dict):
484 for key, value in filters.items():
485 if values.get(key) != value:
486 return []
487
488 if as_dict:
489 return values and [values] or []
490
491 if isinstance(fields, list):
492 return [map(lambda d: values.get(d), fields)]
493
494 else:
495 r = self.sql("""select field, value
496 from tabSingles where field in (%s) and doctype=%s""" \
497 % (', '.join(['%s'] * len(fields)), '%s'),
498 tuple(fields) + (doctype,), as_dict=False, debug=debug)
499
500 if as_dict:
501 if r:
502 r = frappe._dict(r)
503 if update:
504 r.update(update)
505 return [r]
506 else:
507 return []
508 else:
509 return r and [[i[1] for i in r]] or []
510
511 def get_singles_dict(self, doctype):
512 """Get Single DocType as dict."""
513 return frappe._dict(self.sql("""select field, value from
514 tabSingles where doctype=%s""", doctype))
515
516 def get_all(self, *args, **kwargs):
517 return frappe.get_all(*args, **kwargs)
518
519 def get_list(self, *args, **kwargs):
520 return frappe.get_list(*args, **kwargs)
521
522 def get_single_value(self, doctype, fieldname, cache=False):
523 """Get property of Single DocType. Cache locally by default"""
524 value = self.value_cache.setdefault(doctype, {}).get(fieldname)
525 if value:
526 return value
527
528 val = self.sql("""select value from
529 tabSingles where doctype=%s and field=%s""", (doctype, fieldname))
530 val = val[0][0] if val else None
531
532 if val=="0" or val=="1":
533 # check type
534 val = int(val)
535
536 self.value_cache[doctype][fieldname] = val
537
538 return val
539
540 def get_singles_value(self, *args, **kwargs):
541 """Alias for get_single_value"""
542 return self.get_single_value(*args, **kwargs)
543
544 def _get_values_from_table(self, fields, filters, doctype, as_dict, debug, order_by=None, update=None):
545 fl = []
546 if isinstance(fields, (list, tuple)):
547 for f in fields:
548 if "(" in f or " as " in f: # function
549 fl.append(f)
550 else:
551 fl.append("`" + f + "`")
552 fl = ", ".join(fl)
553 else:
554 fl = fields
555 if fields=="*":
556 as_dict = True
557
558 conditions, values = self.build_conditions(filters)
559
560 order_by = ("order by " + order_by) if order_by else ""
561
562 r = self.sql("select {0} from `tab{1}` where {2} {3}".format(fl, doctype,
563 conditions, order_by), values, as_dict=as_dict, debug=debug, update=update)
564
565 return r
566
567 def _get_value_for_many_names(self, doctype, names, field, debug=False):
568 names = filter(None, names)
569
570 if names:
571 return dict(self.sql("select name, `%s` from `tab%s` where name in (%s)" \
572 % (field, doctype, ", ".join(["%s"]*len(names))), names, debug=debug))
573 else:
574 return {}
575
576 def update(self, *args, **kwargs):
577 """Update multiple values. Alias for `set_value`."""
578 return self.set_value(*args, **kwargs)
579
580 def set_value(self, dt, dn, field, val, modified=None, modified_by=None,
581 update_modified=True, debug=False):
582 """Set a single value in the database, do not call the ORM triggers
583 but update the modified timestamp (unless specified not to).
584
585 **Warning:** this function will not call Document events and should be avoided in normal cases.
586
587 :param dt: DocType name.
588 :param dn: Document name.
589 :param field: Property / field name.
590 :param value: Value to be updated.
591 :param modified: Use this as the `modified` timestamp.
592 :param modified_by: Set this user as `modified_by`.
593 :param update_modified: default True. Set as false, if you don't want to update the timestamp.
594 :param debug: Print the query in the developer / js console.
595 """
596 if not modified:
597 modified = now()
598 if not modified_by:
599 modified_by = frappe.session.user
600
601 if dn and dt!=dn:
602 conditions, values = self.build_conditions(dn)
603
604 values.update({"val": val, "modified": modified, "modified_by": modified_by})
605
606 if update_modified:
607 self.sql("""update `tab{0}` set `{1}`=%(val)s, modified=%(modified)s, modified_by=%(modified_by)s where
608 {2}""".format(dt, field, conditions), values, debug=debug)
609 else:
610 self.sql("""update `tab{0}` set `{1}`=%(val)s where
611 {2}""".format(dt, field, conditions), values, debug=debug)
612
613
614 else:
615 self.sql("delete from tabSingles where field=%s and doctype=%s", (field, dt))
616 self.sql("insert into tabSingles(doctype, field, value) values (%s, %s, %s)",
617 (dt, field, val), debug=debug)
618
619 if update_modified and (field not in ("modified", "modified_by")):
620 self.set_value(dt, dn, "modified", modified)
621 self.set_value(dt, dn, "modified_by", modified_by)
622
623
624 if dt in self.value_cache:
625 del self.value_cache[dt]
626
627 def set(self, doc, field, val):
628 """Set value in document. **Avoid**"""
629 doc.db_set(field, val)
630
631 def touch(self, doctype, docname):
632 """Update the modified timestamp of this document."""
633 from frappe.utils import now
634 modified = now()
635 frappe.db.sql("""update `tab{doctype}` set `modified`=%s
636 where name=%s""".format(doctype=doctype), (modified, docname))
637 return modified
638
639 def set_temp(self, value):
640 """Set a temperory value and return a key."""
641 key = frappe.generate_hash()
642 frappe.cache().hset("temp", key, value)
643 return key
644
645 def get_temp(self, key):
646 """Return the temperory value and delete it."""
647 return frappe.cache().hget("temp", key)
648
649 def set_global(self, key, val, user='__global'):
650 """Save a global key value. Global values will be automatically set if they match fieldname."""
651 self.set_default(key, val, user)
652
653 def get_global(self, key, user='__global'):
654 """Returns a global key value."""
655 return self.get_default(key, user)
656
657 def set_default(self, key, val, parent="__default", parenttype=None):
658 """Sets a global / user default value."""
659 frappe.defaults.set_default(key, val, parent, parenttype)
660
661 def add_default(self, key, val, parent="__default", parenttype=None):
662 """Append a default value for a key, there can be multiple default values for a particular key."""
663 frappe.defaults.add_default(key, val, parent, parenttype)
664
665 def get_default(self, key, parent="__default"):
666 """Returns default value as a list if multiple or single"""
667 d = self.get_defaults(key, parent)
668 return isinstance(d, list) and d[0] or d
669
670 def get_defaults(self, key=None, parent="__default"):
671 """Get all defaults"""
672 if key:
673 defaults = frappe.defaults.get_defaults(parent)
674 d = defaults.get(key, None)
675 if(not d and key != frappe.scrub(key)):
676 d = defaults.get(frappe.scrub(key), None)
677 return d
678 else:
679 return frappe.defaults.get_defaults(parent)
680
681 def begin(self):
682 pass
683 #self.sql("start transaction")
684
685 def commit(self):
686 """Commit current transaction. Calls SQL `COMMIT`."""
687 self.sql("commit")
688 frappe.local.rollback_observers = []
689 self.flush_realtime_log()
690
691 def flush_realtime_log(self):
692 for args in frappe.local.realtime_log:
693 frappe.async.emit_via_redis(*args)
694
695 frappe.local.realtime_log = []
696
697
698 def rollback(self):
699 """`ROLLBACK` current transaction."""
700 self.sql("rollback")
701 self.begin()
702 for obj in frappe.local.rollback_observers:
703 if hasattr(obj, "on_rollback"):
704 obj.on_rollback()
705 frappe.local.rollback_observers = []
706
707 def field_exists(self, dt, fn):
708 """Return true of field exists."""
709 return self.sql("select name from tabDocField where fieldname=%s and parent=%s", (dt, fn))
710
711 def table_exists(self, tablename):
712 """Returns True if table exists."""
713 return ("tab" + tablename) in self.get_tables()
714
715 def get_tables(self):
716 return [d[0] for d in self.sql("show tables")]
717
718 def a_row_exists(self, doctype):
719 """Returns True if atleast one row exists."""
720 return self.sql("select name from `tab{doctype}` limit 1".format(doctype=doctype))
721
722 def exists(self, dt, dn=None):
723 """Returns true if document exists.
724
725 :param dt: DocType name.
726 :param dn: Document name or filter dict."""
727 if isinstance(dt, basestring):
728 if dt!="DocType" and dt==dn:
729 return True # single always exists (!)
730 try:
731 return self.get_value(dt, dn, "name")
732 except:
733 return None
734 elif isinstance(dt, dict) and dt.get('doctype'):
735 try:
736 conditions = []
737 for d in dt:
738 if d == 'doctype': continue
739 conditions.append('`%s` = "%s"' % (d, cstr(dt[d]).replace('"', '\"')))
740 return self.sql('select name from `tab%s` where %s' % \
741 (dt['doctype'], " and ".join(conditions)))
742 except:
743 return None
744
745 def count(self, dt, filters=None, debug=False):
746 """Returns `COUNT(*)` for given DocType and filters."""
747 if filters:
748 conditions, filters = self.build_conditions(filters)
749 return frappe.db.sql("""select count(*)
750 from `tab%s` where %s""" % (dt, conditions), filters, debug=debug)[0][0]
751 else:
752 return frappe.db.sql("""select count(*)
753 from `tab%s`""" % (dt,))[0][0]
754
755
756 def get_creation_count(self, doctype, minutes):
757 """Get count of records created in the last x minutes"""
758 from frappe.utils import now_datetime
759 from dateutil.relativedelta import relativedelta
760
761 return frappe.db.sql("""select count(name) from `tab{doctype}`
762 where creation >= %s""".format(doctype=doctype),
763 now_datetime() - relativedelta(minutes=minutes))[0][0]
764
765 def get_table_columns(self, doctype):
766 """Returns list of column names from given doctype."""
767 return [r[0] for r in self.sql("DESC `tab%s`" % doctype)]
768
769 def has_column(self, doctype, column):
770 """Returns True if column exists in database."""
771 return column in self.get_table_columns(doctype)
772
773 def add_index(self, doctype, fields, index_name=None):
774 """Creates an index with given fields if not already created.
775 Index name will be `fieldname1_fieldname2_index`"""
776 if not index_name:
777 index_name = "_".join(fields) + "_index"
778
779 # remove index length if present e.g. (10) from index name
780 index_name = re.sub(r"\s*\([^)]+\)\s*", r"", index_name)
781
782 if not frappe.db.sql("""show index from `tab%s` where Key_name="%s" """ % (doctype, index_name)):
783 frappe.db.commit()
784 frappe.db.sql("""alter table `tab%s`
785 add index `%s`(%s)""" % (doctype, index_name, ", ".join(fields)))
786
787 def add_unique(self, doctype, fields, constraint_name=None):
788 if isinstance(fields, basestring):
789 fields = [fields]
790 if not constraint_name:
791 constraint_name = "unique_" + "_".join(fields)
792
793 if not frappe.db.sql("""select CONSTRAINT_NAME from information_schema.TABLE_CONSTRAINTS
794 where table_name=%s and constraint_type='UNIQUE' and CONSTRAINT_NAME=%s""",
795 ('tab' + doctype, constraint_name)):
796 frappe.db.commit()
797 frappe.db.sql("""alter table `tab%s`
798 add unique `%s`(%s)""" % (doctype, constraint_name, ", ".join(fields)))
799
800 def get_system_setting(self, key):
801 def _load_system_settings():
802 return self.get_singles_dict("System Settings")
803 return frappe.cache().get_value("system_settings", _load_system_settings).get(key)
804
805 def close(self):
806 """Close database connection."""
807 if self._conn:
808 self._cursor.close()
809 self._conn.close()
810 self._cursor = None
811 self._conn = None
812
813 def escape(self, s, percent=True):
814 """Excape quotes and percent in given string."""
815 if isinstance(s, unicode):
816 s = (s or "").encode("utf-8")
817
818 s = unicode(MySQLdb.escape_string(s), "utf-8").replace("`", "\\`")
819
820 # NOTE separating % escape, because % escape should only be done when using LIKE operator
821 # or when you use python format string to generate query that already has a %s
822 # for example: sql("select name from `tabUser` where name=%s and {0}".format(conditions), something)
823 # defaulting it to True, as this is the most frequent use case
824 # ideally we shouldn't have to use ESCAPE and strive to pass values via the values argument of sql
825 if percent:
826 s = s.replace("%", "%%")
827
828 return s