1
2
3
4 """
5 This file is part of the web2py Web Framework
6 Copyrighted by Massimo Di Pierro <mdipierro@cs.depaul.edu>
7 License: LGPLv3 (http://www.gnu.org/licenses/lgpl.html)
8
9 Thanks to
10 * Niall Sweeny <niall.sweeny@fonjax.com> for MS SQL support
11 * Marcel Leuthi <mluethi@mlsystems.ch> for Oracle support
12 * Denes
13 * Chris Clark
14 * clach05
15 * Denes Lengyel
16 * and many others who have contributed to current and previous versions
17
18 This file contains the DAL support for many relational databases,
19 including:
20 - SQLite
21 - MySQL
22 - Postgres
23 - Oracle
24 - MS SQL
25 - DB2
26 - Interbase
27 - Ingres
28 - SapDB (experimental)
29 - Cubrid (experimental)
30 - CouchDB (experimental)
31 - MongoDB (in progress)
32 - Google:nosql
33 - Google:sql
34 - Teradata
35 - IMAP (experimental)
36
37 Example of usage:
38
39 >>> # from dal import DAL, Field
40
41 ### create DAL connection (and create DB if it doesn't exist)
42 >>> db = DAL(('mysql://a:b@localhost/x', 'sqlite://storage.sqlite'), folder=None)
43
44 ### define a table 'person' (create/alter as necessary)
45 >>> person = db.define_table('person',Field('name','string'))
46
47 ### insert a record
48 >>> id = person.insert(name='James')
49
50 ### retrieve it by id
51 >>> james = person(id)
52
53 ### retrieve it by name
54 >>> james = person(name='James')
55
56 ### retrieve it by arbitrary query
57 >>> query = (person.name=='James') & (person.name.startswith('J'))
58 >>> james = db(query).select(person.ALL)[0]
59
60 ### update one record
61 >>> james.update_record(name='Jim')
62
63 ### update multiple records by query
64 >>> db(person.name.like('J%')).update(name='James')
65 1
66
67 ### delete records by query
68 >>> db(person.name.lower() == 'jim').delete()
69 0
70
71 ### retrieve multiple records (rows)
72 >>> people = db(person).select(orderby=person.name, groupby=person.name, limitby=(0,100))
73
74 ### further filter them
75 >>> james = people.find(lambda row: row.name == 'James').first()
76 >>> print james.id, james.name
77 1 James
78
79 ### check aggregates
80 >>> counter = person.id.count()
81 >>> print db(person).select(counter).first()(counter)
82 1
83
84 ### delete one record
85 >>> james.delete_record()
86 1
87
88 ### delete (drop) entire database table
89 >>> person.drop()
90
91 Supported field types:
92 id string text boolean integer double decimal password upload blob time date datetime
93
94 Supported DAL URI strings:
95 'sqlite://test.db'
96 'sqlite:memory'
97 'jdbc:sqlite://test.db'
98 'mysql://root:none@localhost/test'
99 'postgres://mdipierro:password@localhost/test'
100 'postgres:psycopg2://mdipierro:password@localhost/test'
101 'postgres:pg8000://mdipierro:password@localhost/test'
102 'jdbc:postgres://mdipierro:none@localhost/test'
103 'mssql://web2py:none@A64X2/web2py_test'
104 'mssql2://web2py:none@A64X2/web2py_test' # alternate mappings
105 'oracle://username:password@database'
106 'firebird://user:password@server:3050/database'
107 'db2://DSN=dsn;UID=user;PWD=pass'
108 'firebird://username:password@hostname/database'
109 'firebird_embedded://username:password@c://path'
110 'informix://user:password@server:3050/database'
111 'informixu://user:password@server:3050/database' # unicode informix
112 'google:datastore' # for google app engine datastore
113 'google:sql' # for google app engine with sql (mysql compatible)
114 'teradata://DSN=dsn;UID=user;PWD=pass; DATABASE=database' # experimental
115 'imap://user:password@server:port' # experimental
116
117 For more info:
118 help(DAL)
119 help(Field)
120 """
121
122
123
124
125
126 __all__ = ['DAL', 'Field']
127
128 MAXCHARLENGTH = 2**15
129 DEFAULTLENGTH = {'string':512,
130 'password':512,
131 'upload':512,
132 'text':2**15,
133 'blob':2**31}
134 TIMINGSSIZE = 100
135
136 import re
137 import sys
138 import locale
139 import os
140 import types
141 import cPickle
142 import datetime
143 import threading
144 import time
145 import cStringIO
146 import csv
147 import cgi
148 import copy
149 import socket
150 import logging
151 import copy_reg
152 import base64
153 import shutil
154 import marshal
155 import decimal
156 import struct
157 import urllib
158 import hashlib
159 import uuid
160 import glob
161 import traceback
162
163 CALLABLETYPES = (types.LambdaType, types.FunctionType, types.BuiltinFunctionType,
164 types.MethodType, types.BuiltinMethodType)
165
166
167
168
169
170 try:
171 from utils import web2py_uuid
172 except ImportError:
173 import uuid
175
176 try:
177 import portalocker
178 have_portalocker = True
1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739"> 1739">