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 This module provides a simple wrapper for SQL within Python. The idea
119 is to obscure the SQL code that is being generated as little as
120 possible, but to hide all the gorry details, especially of quoting and
121 escaping things, away from the programmer. Also this code is supposed
122 to be backend independent. Also this module is independent of the rest
123 of orm2.
124
125 The way it works is best described by example::
126
127 >>> ds = datasource(...some params...)
128 >>> s = select( ( quotes('first name'), 'lastname', 'age',
129 expression('age + 10'),
130 as(quotes('negative age'), 'age - 10')),
131 'person',
132 where('age > 10'), order_by('age'))
133 >>> print sql(ds)(s)
134 SELECT "first name", lastname, age, age + 10,
135 age - 10 AS "negative age" FROM person WHERE age > 10 ORDER BY "age"
136 >>> u = update( 'person', where('id = 22'),
137 firstname = string_literal('Diedrich'),
138 lastname=string_literal('Vorberg'))
139 >>> print sql(ds)(u)
140 UPDATE person SET lastname = 'Vorberg', firstname = 'Diedrich' WHERE id = 22
141 >>> d = delete ('person', where('id = 22'))
142 >>> print sql(ds)(d)
143 DELETE FROM person WHERE id = 22
144
145 """
146 __author__ = "Diedrich Vorberg <diedrich@tux4web.de>"
147 __version__ = "$Revision: 1.8 $"[11:-2]
148
149
150 from string import *
151 from types import *
152
153 NULL = "NULL"
154
155
161
162
164 """
165 A mix-in class that is inherited by datasouce.datasource_base. It
166 provies all the methods needed for a datasource to work with the
167 sql module.
168
169 This class' instances will work for most SQL92 complient backends
170 that use utf-8 unicode encoding.
171 """
172
173 escaped_chars = ( ('"', r'\"',),
174 ("'", r'\"',),
175 ("%", "%%",), )
176
178 return '"%s"' % name
179
182
188
190 return "utf-8"
191
192
194 """
195 This class is used to do something that in Haskell is called
196 Cyrrying. This is what leads to the somewhat unusual constructs in
197 this source file, that look like::
198
199 sql(ds)(some_element)
200
201 The sql class is instantiated with ds as the constructor's
202 argument. The instance implements the __call__ interface, which
203 enables me to use it like a function. This 'function' is then
204 applied to the some_element parameter. This is especially usefull
205 when programming in a functional style as I did here.
206
207 It takes a while to get used to this type of thinking, but it's
208 certainly worthwhile. Some consider this kind of programming
209 beautifull in artistic meaning of the word ;-)
210
211 @var params: After being called, this attribute contains those parameters
212 of the SQL statement that have not been escaped by this module but
213 shall be passed to cursor.execute() as second argument. Corresponding
214 ?s will be contained in the SQL statement.
215 """
216
218 if not isinstance(ds, datasource):
219 raise TypeError("sql takes a datasource as argument")
220
221 self.ds = ds
222 self.params = []
223
225 """
226 The arguments must either provide an __sql__() function or be
227 convertable to strings. The __sql__() function must return either a
228 string containing SQL or a pair as ( SQL, params, ) in which params
229 is a tuple which will be passed to cursor.execute() as second
230 arguments. These are Python instances used by the cursor to replace
231 ?s. This lets the lower level DBAPI module handle the quoting.
232 """
233 ret = []
234
235 for arg in args:
236 if type(arg) == UnicodeType:
237 raise UnicodeNotAllowedInSQL()
238 else:
239 if hasattr(arg, "__sql__"):
240 ret.append(arg.__sql__(self))
241 else:
242 ret.append(str(arg))
243
244 return join(ret, " ")
245
247 """
248 A helper function that takes a list of strings, column and relaton
249 classes and converts if it to sensible sql.
250 """
251 if type(arg) in (TupleType, ListType):
252 arg = map(runner, arg)
253 return join(arg, ", ")
254 else:
255 return runner(arg)
256
257
259 """
260 The _part class is the base class for all SQL statement classes.
261 It proviedes a __str__() method, which calls __sql__() with a minimal
262 standard datasource that will yield SQL92 compliant results.
263
264 _part instances are not hashable. This is due to the fact that they are
265 mutable and I don't want to change that. Ignoring the conflict between
266 hashability and mutablity would not result in problems in many cases, but
267 problems it would cause, would be *very* hard to track down (things would
268 suddenly disappear from dicts, see Python Reference Manual chap. 3.3.1).
269
270 If you need to use SQL objects as dictionary keys or sets use the
271 orm2.util.stupid_dict class, which implements the mapping interface
272 without relying on hashing. (It uses sequential search instead, so it's
273 not suitable for large datasets.) See the datasource.select() method for
274 an example on how to use stupid_dict instead of Set.
275 """
277 raise NotImplementedError()
278
281
283 """
284 Two SQL statements are considered equal if attributes containing
285 strings or statements are equal. (That means, that this method will
286 be called recursivly at times.
287 """
288 if not isinstance(other, self.__class__):
289
290
291 return False
292
293 for property, my_value in self.__dict__.items():
294 if not other.__dict__.has_key(property):
295
296
297 return False
298
299 other_value = other.__dict__[property]
300
301 if my_value != other_value:
302
303 return False
304
305 return True
306
308 return not self.__eq__(other)
309
311 """
312 Base class for all statements (select, update, delete, etc)
313 """
314
316 """
317 Base class for clauses. They will be ordered according to rank
318 when used to form a statement.
319 """
320 rank = 0
321
323 """
324 Base class that encapsulates all sql identifyers.
325 """
326 - def __init__(self, name, quotes=False):
329
335
337 """
338 When converted to regular strings, identifyers are not quoted.
339 """
340 return self.name
341
343 """
344 Shorthand for an identifyer that you'd like to be surrounded in
345 quotes within the sql code.
346 """
349
351 """
352 Base class for those classes that encapsulate a value that is ment
353 to go into the SQL as-such.
354 """
360
363
366 if type(i) != IntType and type(i) != LongType:
367 raise TypeError("integer_literal takes an integer as argument, not a " + repr(type(i)))
368 self.sql = str(i)
369
371 """
372 Python 2.3 still makes a difference between long and int, so I need to
373 classes here.
374 """
376 if type(i) != IntType and type(i) != LongType:
377 raise TypeError("long_literal takes a long as argument, not a " + repr(type(i)))
378 self.sql = str(i)
379
382 if type(i) != FloatType and type(i) != LongType:
383 raise TypeError("float_literal takes an float as argument, not a " + repr(type(i)))
384 self.sql = str(i)
388 if type(s) == UnicodeType:
389 raise TypeError("string_literal takes a string as argument. " + \
390 "Use unicode_literal for Unicode values.")
391
392 self.content = str(s)
393
401 - def __init__(self, u, errors="strict"):
402 """
403 The errors parameter determines what to do if a character cannot
404 be incoded in the target coding system. Other legal values 'ignore'
405 and 'replace'. See the documentation for the built-in unicode()
406 function.
407 """
408 if type(u) != UnicodeType:
409 raise TypeError("unicode_literal takes a unicode argument.")
410
411 self.content = u
412 self.errors = errors
413
423 self.content = bool(b)
424
426 if self.content:
427 return "TRUE"
428 else:
429 return "FALSE"
432 """
433 This returns a %s as SQL code and the content you pass to the constructor
434 to be quoted by the cursor's implementation rathern than by orm2.sql.
435 Refer to he sql class' __call__() method.
436 """
438 self.content = content
439
443
444
446 - def __init__(self, name, schema=None, quote=False):
447 if not isinstance(name, identifyer):
448 self.name = identifyer(name, quote)
449 else:
450 self.name = name
451
452 if type(schema) == StringType:
453 self.schema = identifyer(schema)
454 else:
455 self.schema = schema
456
458 if self.schema is not None:
459 return runner(self.schema) + "." + runner(self.name)
460 else:
461 return runner(self.name)
462
463
465 """
466 A column name. If the relation argument is passed to the
467 constructor, the sql result will look like::
468
469 relation.column_name
470
471 including appropriate quotes if desired. The relation parameter
472 may be an sql.identifyer instance if the relation name needs to be
473 quoted.
474 """
475 - def __init__(self, name, relation=None, quote=False):
485
487 if self.relation is not None:
488 return runner(self.relation) + "." + runner(self.name)
489 else:
490 return runner(self.name)
491
493 """
494 Encapsolate an SQL expression as for example a arithmetic or a
495 function call.
496
497 >>> sql()( expression('COUNT(amount) + ', 10) )
498 ==> COUNT(amount) + 10
499 """
501 self.parts = []
502 self._append(parts)
503
505 for part in parts:
506 if type(part) in ( TupleType, ListType, GeneratorType, ):
507 self._append(part)
508 else:
509 self.parts.append(part)
510
512 parts = map(runner, self.parts)
513 parts = map(strip, parts)
514 return join(parts, " ")
515
517 ret = expression()
518 ret.parts = self.parts + other.parts
519
520 return ret
521
522 -class as(expression):
523 """
524 Encapsulates an expression that goes into an AS statement in a
525 SELECT's column list.
526
527 >>> sql()( as('column', 'columns / 10') )
528 ==> columns / 10 AS column_div_by_ten
529
530 """
534
537
538 -class where(clause, expression):
539 """
540 Encapsulates the WHERE clause of a SELECT, UPDATE and DELETE
541 statement. Just an expression with WHERE prepended.
542 """
543 rank = 1
544
547
549 """
550 Adding two where clauses connects them using AND (including
551 parantheses)
552 """
553 return self.and_([self, other])
554
556 """
557 Multiplying two where clauses connects them using OR (including
558 parantheses).
559 """
560 return self.or_([self, other])
561
562 - def or_(cls, others):
563 """
564 OTHERS is a list of sql.where instances that are connected
565 using OR.
566 """
567 if len(others) < 1:
568 raise ValueError("Empty input for or_()")
569
570 ret = where()
571
572 for other in others:
573 ret.parts.append("(")
574 ret.parts += list(other.parts)
575 ret.parts.append(")")
576 ret.parts.append(" OR ")
577
578 del ret.parts[-1]
579
580 return ret
581 or_ = classmethod(or_)
582
583 - def and_(cls, others):
584 """
585 OTHERS is a list of sql.where instances that are connected
586 using OR.
587 """
588 if len(others) < 1:
589 raise ValueError("Empty input for and_()")
590
591 ret = where()
592
593 for other in others:
594 ret.parts.append("(")
595 ret.parts += list(other.parts)
596 ret.parts.append(")")
597 ret.parts.append(" AND ")
598
599 del ret.parts[-1]
600
601 return ret
602 and_ = classmethod(and_)
603
605 """
606 Encapsulate the ORDER BY clause of a SELECT statement. Takes a
607 list of columns as argument.
608
609 FIXME: order by expression, ASC, DESC!!!
610 """
611
612 rank = 2
613
615 self.columns = columns
616
617 dir = kw.get("dir", "ASC")
618 if upper(dir) not in ("ASC", "DESC",):
619 raise SQLSyntaxError("dir must bei either ASC or DESC")
620 else:
621 self.dir = dir
622
624 ret = "ORDER BY %s" % flatten_identifyer_list(runner, self.columns)
625
626 if self.dir is not None:
627 ret = "%s %s" % ( ret, self.dir, )
628
629 return ret
630
631 orderby = order_by
632
634 """
635 Encapsulate a SELECT statement's limit clause.
636 """
637
638 rank = 3
639
641 if type(limit) != IntType:
642 raise TypeError("Limit must be an integer")
643
644 self.limit = limit
645
649
651 """
652 Encapsulate a SELECT statement's offset clause.
653 """
654
655 rank = 3
656
662
666
668 """
669 Encapsulate a SELECT statement.
670 """
671
672 - def __init__(self, columns, relations, *clauses):
673 self.columns = columns
674 self.relations = relations
675 self.clauses = filter(lambda clause: clause is not None,
676 list(clauses))
677
678 for c in self.clauses:
679 if not isinstance(c, clause):
680 raise TypeError("%s is not an SQL clause" % repr(c))
681
682 - def add(self, new_clause):
683 if not isinstance(new_clause, clause):
684 raise TypeError("Argument must be a sql clause instance.")
685
686 for clause in self.clauses:
687 if isinstance(new_clause, clause.__class__):
688 raise ClauseAlreadyExists()
689
690 - def modify(self, new_clause):
691 if not isinstance(new_clause, clause):
692 raise TypeError("Argument must be a sql clause instance.")
693
694 for counter, cls in enumerate(self.clauses):
695 if isinstance(new_clause, cls.__class__):
696 del self.clauses[counter]
697 break
698
699 self.clauses.append(new_clause)
700
702 clauses = filter(lambda a: a is not None, self.clauses)
703 clauses.sort(lambda a, b: cmp(a.rank, b.rank))
704 clauses = map(runner, clauses)
705 clauses = join(clauses, " ")
706
707 columns = flatten_identifyer_list(runner, self.columns)
708 relations = flatten_identifyer_list(runner, self.relations)
709
710 return "SELECT %(columns)s FROM %(relations)s %(clauses)s" % locals()
711
713 """
714 Encapsulate an INSERT statement.
715 """
716 - def __init__(self, relation, columns, values):
724
735
737 """
738 Encapsulate a UPDATE statement.
739 """
740
741 - def __init__(self, relation, where_clause, info={}, **param_info):
742 """
743 @param relation: The relation to be updates.
744 @param where_clause: where clause that determines the row to be updated
745 @param info: Dictionary as {'column': sql_literal}
746 """
747 self.relation = relation
748 self.info = info
749 self.info.update(param_info)
750
751 if not isinstance(where_clause, where):
752 where_clause = where(where_clause)
753
754 self.where = where_clause
755
770
771
773 """
774 Encapsulate a DELETE statement.
775 """
776
777 - def __init__(self, relation, where_clause):
780
782 relation = runner(self.relation)
783 where = runner(self.where)
784
785 return "DELETE FROM %(relation)s %(where)s" % locals()
786