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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
|
#!/usr/local/bin/python
#-*- coding: ISO-8859-1 -*-
#ident "@(#) $Id: array.py,v 1.4 2003/07/12 17:19:06 ghaering Exp $"
# vi:set sw=4 ts=8 showmode ai:
#-----------------------------------------------------------------------+
# Name: array.py |
# |
# Description: array.py contains test cases for using the PostgreSQL |
# array type from within the PgSQL module. |
# |
# Note: These test cases requires that a test database named |
# pypgsql exists and that the person running the test has |
# full rights to the database. |
#=======================================================================|
# Copyright 2002 by Gerhard Hring. |
# All rights reserved. |
# |
# Permission to use, copy, modify, and distribute this software and its |
# documentation for any purpose and without fee is hereby granted, pro- |
# vided that the above copyright notice appear in all copies and that |
# both that copyright notice and this permission notice appear in sup- |
# porting documentation, and that the copyright owner's name not be |
# used in advertising or publicity pertaining to distribution of the |
# software without specific, written prior permission. |
# |
# THE AUTHOR(S) DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE, |
# INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN |
# NO EVENT SHALL THE AUTHOR(S) BE LIABLE FOR ANY SPECIAL, INDIRECT OR |
# CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS |
# OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE |
# OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE |
# USE OR PERFORMANCE OF THIS SOFTWARE. |
#=======================================================================|
# Revision History: |
# |
# Date Ini Description |
# --------- --- ------------------------------------------------------- |
# 02NOV2002 bga Modified code to use the PgArray class for PostgreSQL |
# arrays. |
# 21APR2002 gh Added some more mean testcases, which test for control |
# characters and other special characters inside strings, |
# and multi-dimensional arrays. |
# 16APR2002 gh Initial release by Gerhard Hring. |
#-----------------------------------------------------------------------+
import unittest, types
import sys
from pyPgSQL import PgSQL
PgArray = PgSQL.PgArray
class ArrayTestCases(unittest.TestCase):
def setUp(self):
self.conn = PgSQL.connect(database="pypgsql")
self.conn.autocommit = 1
def checkEqual(self, real, planned):
self.failUnlessEqual(real.value, planned.value, \
"Received %s, should have been %s" % (repr(real), repr(planned)))
def CheckForIntegerInsert(self):
ivalues = PgArray([3,4,5])
cursor = self.conn.cursor()
try:
cursor.execute("drop table test")
except:
pass
cursor.execute("create table test (ia int[])")
cursor.execute("insert into test(ia) values (%s)", ivalues)
cursor.execute("select ia from test")
result = cursor.fetchone()
self.failUnless(isinstance(result.ia, PgArray), \
"the integer array isn't returned as a PgArray")
self.checkEqual(result.ia, ivalues)
def CheckForNumericInsert(self):
numlist = PgArray(map(PgSQL.PgNumeric, ['4.7', '-3.2', '23.17']))
cursor = self.conn.cursor()
try:
cursor.execute("drop table test")
except:
pass
cursor.execute("create table test(na numeric[])")
cursor.execute("insert into test(na) values (%s)", numlist)
cursor.execute("select na from test")
result = cursor.fetchone()
self.failUnless(isinstance(result.na, PgArray), \
"the numeric array isn't returned as a PgArray")
self.checkEqual(result.na, numlist)
def CheckForStringInsert(self):
cursor = self.conn.cursor()
stringlists = PgArray([['hey', 'you', 'there'],
['fancy', ',', '{chars}', '"'],
['what about', 'some \\backslashes'],
['some more \\', '\\ and \\ etc'],
['aa', '%s\n%s' % (chr(10), chr(29))],
['what about backslashes', 'at the end\\'],
['interesting', '"', '\\"', '\\"\\'],
['{}\\', '"\\}}\\\'"']])
for stringlist in stringlists:
try:
cursor.execute("drop table test")
except:
pass
cursor.execute("create table test(va varchar[])")
cursor.execute("insert into test(va) values (%s)", stringlist)
cursor.execute("select va from test")
result = cursor.fetchone()
self.failUnless(isinstance(result.va, PgArray), \
"the varchar array isn't returned as a PgArray")
self.checkEqual(result.va, stringlist)
def CheckForStringInsertBruteForce(self):
cursor = self.conn.cursor()
chars = "\"'{}\\\n%s%sabc" % (chr(14), chr(5))
try:
cursor.execute("drop table test")
except:
pass
cursor.execute("create table test(va varchar[])")
self.conn.commit()
for i in chars:
for j in chars:
for k in chars:
cursor.execute("insert into test(va) values (%s)", PgArray([i+j+k]))
cursor.execute("select va from test where oid=%s", cursor.oidValue)
result = cursor.fetchone()
self.failUnless(isinstance(result.va, PgArray), \
"the varchar array isn't returned as a PgArray")
self.checkEqual(result.va, [i+j+k])
def CheckForStringInsertMultiDim(self):
cursor = self.conn.cursor()
stringlists = PgArray([[["a", "b"], ["c", "d"]],
[[["a", "b"], ["c", "d"]],
[["e", None], ["g", "h"]]],
[[["{}", "\005{}\\'"], ["'asf'", "[\\{]"]],
[["e", "f"], ["g", "h"]]],
[["a", "b"], ["c", "d"]]])
for stringlist in stringlists:
try:
cursor.execute("drop table test")
except:
pass
cursor.execute("create table test(va varchar[])")
cursor.execute("insert into test(va) values (%s)", stringlist)
cursor.execute("select va from test")
result = cursor.fetchone()
self.failUnless(isinstance(result.va, PgArray), \
"the varchar array isn't returned as a PgArray")
expected = stringlist[:]
try:
if expected[1][0][1] is None:
expected[1][0][1] = ''
except IndexError, reason:
pass
self.checkEqual(result.va, expected)
def CheckForIntInsertMultiDim(self):
cursor = self.conn.cursor()
intlists = PgArray([[[1,2], [3,4]],
[[-5,3,4], [2, None, 10]]])
excpected_intlists = PgArray([[[1,2], [3,4]],
[[-5,3,4], [2, 0, 10]]])
for intlist, expected in zip(intlists, excpected_intlists):
try:
cursor.execute("drop table test")
except:
pass
cursor.execute("create table test(ia int[])")
cursor.execute("insert into test(ia) values (%s)", intlist)
cursor.execute("select ia from test")
result = cursor.fetchone()
self.failUnless(isinstance(result.ia, PgArray), \
"the int array isn't returned as a PgArray")
self.checkEqual(result.ia, expected)
if __name__ == "__main__":
TestSuite = unittest.TestSuite()
TestSuite.addTest(ArrayTestCases("CheckForIntegerInsert"))
TestSuite.addTest(ArrayTestCases("CheckForNumericInsert"))
TestSuite.addTest(ArrayTestCases("CheckForStringInsert"))
#TestSuite.addTest(ArrayTestCases("CheckForStringInsertBruteForce"))
TestSuite.addTest(ArrayTestCases("CheckForStringInsertMultiDim"))
TestSuite.addTest(ArrayTestCases("CheckForIntInsertMultiDim"))
runner = unittest.TextTestRunner()
runner.run(TestSuite)
|
