annotate sqlpython/sqlpyPlus.py @ 274:0ae3a8227bc0

added binds to help
author catherine@Elli.myhome.westell.com
date Wed, 18 Mar 2009 19:03:27 -0400
parents 9d67065ea030
children df78546969c9
rev   line source
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1 """sqlpyPlus - extra features (inspired by Oracle SQL*Plus) for Luca Canali's sqlpython.py
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
2
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
3 Features include:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
4 - SQL*Plus-style bind variables
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
5 - `set autobind on` stores single-line result sets in bind variables automatically
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
6 - SQL buffer with list, run, ed, get, etc.; unlike SQL*Plus, buffer stores session's full history
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
7 - @script.sql loads and runs (like SQL*Plus)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
8 - ! runs operating-system command
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
9 - show and set to control sqlpython parameters
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
10 - SQL*Plus-style describe, spool
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
11 - write sends query result directly to file
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
12 - comments shows table and column comments
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
13 - compare ... to ... graphically compares results of two queries
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
14 - commands are case-insensitive
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
15 - context-sensitive tab-completion for table names, column names, etc.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
16
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
17 Use 'help' within sqlpython for details.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
18
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
19 Set bind variables the hard (SQL*Plus) way
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
20 exec :b = 3
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
21 or with a python-like shorthand
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
22 :b = 3
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
23
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
24 - catherinedevlin.blogspot.com May 31, 2006
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
25 """
220
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
26 import sys, os, re, sqlpython, cx_Oracle, pyparsing, re, completion, datetime, pickle, binascii, subprocess
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
27 from cmd2 import Cmd, make_option, options, Statekeeper, Cmd2TestCase
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
28 from output_templates import output_templates
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
29 from plothandler import Plot
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
30 try:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
31 import pylab
198
b2d8bf5f89db merged with changes from work
catherine@Elli.myhome.westell.com
parents: 196
diff changeset
32 except (RuntimeError, ImportError):
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
33 pass
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
34
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
35 descQueries = {
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
36 'TABLE': ("""
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
37 SELECT atc.column_name,
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
38 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
39 atc.data_type ||
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
40 CASE atc.data_type WHEN 'DATE' THEN ''
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
41 ELSE '(' ||
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
42 CASE atc.data_type WHEN 'NUMBER' THEN TO_CHAR(atc.data_precision) ||
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
43 CASE atc.data_scale WHEN 0 THEN ''
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
44 ELSE ',' || TO_CHAR(atc.data_scale) END
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
45 ELSE TO_CHAR(atc.data_length) END
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
46 END ||
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
47 CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
48 data_type
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
49 FROM all_tab_columns atc
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
50 WHERE atc.table_name = :object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
51 AND atc.owner = :owner
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
52 ORDER BY atc.column_id;""",),
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
53 'PROCEDURE': ("""
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
54 SELECT NVL(argument_name, 'Return Value') argument_name,
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
55 data_type,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
56 in_out,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
57 default_value
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
58 FROM all_arguments
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
59 WHERE object_name = :object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
60 AND owner = :owner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
61 AND package_name IS NULL
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
62 ORDER BY sequence;""",),
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
63 'PackageObjects':("""
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
64 SELECT DISTINCT object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
65 FROM all_arguments
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
66 WHERE package_name = :package_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
67 AND owner = :owner""",),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
68 'PackageObjArgs':("""
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
69 SELECT object_name,
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
70 argument_name,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
71 data_type,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
72 in_out,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
73 default_value
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
74 FROM all_arguments
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
75 WHERE package_name = :package_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
76 AND object_name = :object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
77 AND owner = :owner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
78 AND argument_name IS NOT NULL
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
79 ORDER BY sequence;""",),
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
80 'TRIGGER':("""
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
81 SELECT description
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
82 FROM all_triggers
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
83 WHERE owner = :owner
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
84 AND trigger_name = :object_name;
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
85 """,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
86 """
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
87 SELECT table_owner,
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
88 base_object_type,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
89 table_name,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
90 column_name,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
91 when_clause,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
92 status,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
93 action_type,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
94 crossedition
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
95 FROM all_triggers
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
96 WHERE owner = :owner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
97 AND trigger_name = :object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
98 \\t
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
99 """,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
100 ),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
101 'INDEX':("""
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
102 SELECT index_type,
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
103 table_owner,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
104 table_name,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
105 table_type,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
106 uniqueness,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
107 compression,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
108 partitioned,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
109 temporary,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
110 generated,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
111 secondary,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
112 dropped,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
113 visibility
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
114 FROM all_indexes
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
115 WHERE owner = :owner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
116 AND index_name = :object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
117 \\t
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
118 """,)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
119 }
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
120 descQueries['VIEW'] = descQueries['TABLE']
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
121 descQueries['FUNCTION'] = descQueries['PROCEDURE']
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
122
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
123 queries = {
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
124 'resolve': """
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
125 SELECT object_type, object_name, owner FROM (
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
126 SELECT object_type, object_name, user owner, 1 priority
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
127 FROM user_objects
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
128 WHERE object_name = :objName
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
129 UNION ALL
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
130 SELECT ao.object_type, ao.object_name, ao.owner, 2 priority
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
131 FROM all_objects ao
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
132 JOIN user_synonyms us ON (us.table_owner = ao.owner AND us.table_name = ao.object_name)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
133 WHERE us.synonym_name = :objName
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
134 AND ao.object_type != 'SYNONYM'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
135 UNION ALL
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
136 SELECT ao.object_type, ao.object_name, ao.owner, 3 priority
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
137 FROM all_objects ao
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
138 JOIN all_synonyms asyn ON (asyn.table_owner = ao.owner AND asyn.table_name = ao.object_name)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
139 WHERE asyn.synonym_name = :objName
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
140 AND ao.object_type != 'SYNONYM'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
141 AND asyn.owner = 'PUBLIC'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
142 UNION ALL
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
143 SELECT 'DIRECTORY' object_type, dir.directory_name, dir.owner, 6 priority
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
144 FROM all_directories dir
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
145 WHERE dir.directory_name = :objName
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
146 UNION ALL
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
147 SELECT 'DATABASE LINK' object_type, db_link, owner, 7 priority
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
148 FROM all_db_links dbl
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
149 WHERE dbl.db_link = :objName
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
150 ) ORDER BY priority ASC,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
151 length(object_type) ASC,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
152 object_type DESC""", # preference: PACKAGE before PACKAGE BODY, TABLE before INDEX
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
153 'tabComments': """
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
154 SELECT comments
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
155 FROM all_tab_comments
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
156 WHERE owner = :owner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
157 AND table_name = :table_name""",
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
158 'colComments': """
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
159 SELECT
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
160 atc.column_name,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
161 acc.comments
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
162 FROM all_tab_columns atc
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
163 JOIN all_col_comments acc ON (atc.owner = acc.owner and atc.table_name = acc.table_name and atc.column_name = acc.column_name)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
164 WHERE atc.table_name = :object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
165 AND atc.owner = :owner
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
166 ORDER BY atc.column_id;""",
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
167 'oneColComments': """
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
168 SELECTatc.column_name,
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
169 acc.comments
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
170 FROM all_tab_columns atc
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
171 JOIN all_col_comments acc ON (atc.owner = acc.owner and atc.table_name = acc.table_name and atc.column_name = acc.column_name)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
172 WHERE atc.table_name = :object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
173 AND atc.owner = :owner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
174 AND acc.column_name = :column_name
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
175 ORDER BY atc.column_id;""",
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
176 #thanks to Senora.pm for "refs"
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
177 'refs': """
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
178 NULL referenced_by,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
179 c2.table_name references,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
180 c1.constraint_name constraint
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
181 FROM
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
182 user_constraints c1,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
183 user_constraints c2
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
184 WHERE
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
185 c1.table_name = :object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
186 and c1.constraint_type ='R'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
187 and c1.r_constraint_name = c2.constraint_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
188 and c1.r_owner = c2.owner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
189 and c1.owner = :owner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
190 UNION
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
191 SELECT c1.table_name referenced_by,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
192 NULL references,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
193 c1.constraint_name constraint
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
194 FROM
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
195 user_constraints c1,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
196 user_constraints c2
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
197 WHERE
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
198 c2.table_name = :object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
199 and c1.constraint_type ='R'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
200 and c1.r_constraint_name = c2.constraint_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
201 and c1.r_owner = c2.owner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
202 and c1.owner = :owner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
203 """
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
204 }
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
205
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
206 class SoftwareSearcher(object):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
207 def __init__(self, softwareList, purpose):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
208 self.softwareList = softwareList
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
209 self.purpose = purpose
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
210 self.software = None
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
211 def invoke(self, *args):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
212 if not self.software:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
213 (self.software, self.invokeString) = self.find()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
214 argTuple = tuple([self.software] + list(args))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
215 os.system(self.invokeString % argTuple)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
216 def find(self):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
217 if self.purpose == 'text editor':
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
218 software = os.environ.get('EDITOR')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
219 if software:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
220 return (software, '%s %s')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
221 for (n, (software, invokeString)) in enumerate(self.softwareList):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
222 if os.path.exists(software):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
223 if n > (len(self.softwareList) * 0.7):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
224 print """
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
225
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
226 Using %s. Note that there are better options available for %s,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
227 but %s couldn't find a better one in your PATH.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
228 Feel free to open up %s
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
229 and customize it to find your favorite %s program.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
230
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
231 """ % (software, self.purpose, __file__, __file__, self.purpose)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
232 return (software, invokeString)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
233 stem = os.path.split(software)[1]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
234 for p in os.environ['PATH'].split(os.pathsep):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
235 if os.path.exists(os.sep.join([p, stem])):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
236 return (stem, invokeString)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
237 raise (OSError, """Could not find any %s programs. You will need to install one,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
238 or customize %s to make it aware of yours.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
239 Looked for these programs:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
240 %s""" % (self.purpose, __file__, "\n".join([s[0] for s in self.softwareList])))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
241
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
242 softwareLists = {
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
243 'diff/merge': [
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
244 ('/usr/bin/meld',"%s %s %s"),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
245 ('/usr/bin/kdiff3',"%s %s %s"),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
246 (r'C:\Program Files\Araxis\Araxis Merge v6.5\Merge.exe','"%s" %s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
247 (r'C:\Program Files\TortoiseSVN\bin\TortoiseMerge.exe', '"%s" /base:"%s" /mine:"%s"'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
248 ('FileMerge','%s %s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
249 ('kompare','%s %s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
250 ('WinMerge','%s %s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
251 ('xxdiff','%s %s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
252 ('fldiff','%s %s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
253 ('gtkdiff','%s %s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
254 ('tkdiff','%s %s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
255 ('gvimdiff','%s %s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
256 ('diff',"%s %s %s"),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
257 (r'c:\windows\system32\comp.exe',"%s %s %s")],
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
258 'text editor': [
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
259 ('gedit', '%s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
260 ('textpad', '%s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
261 ('notepad.exe', '%s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
262 ('pico', '%s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
263 ('emacs', '%s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
264 ('vim', '%s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
265 ('vi', '%s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
266 ('ed', '%s %s'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
267 ('edlin', '%s %s')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
268 ]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
269 }
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
270
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
271 diffMergeSearcher = SoftwareSearcher(softwareLists['diff/merge'],'diff/merge')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
272 editSearcher = SoftwareSearcher(softwareLists['text editor'], 'text editor')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
273 editor = os.environ.get('EDITOR')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
274 if editor:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
275 editSearcher.find = lambda: (editor, "%s %s")
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
276
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
277 class CaselessDict(dict):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
278 """dict with case-insensitive keys.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
279
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
280 Posted to ASPN Python Cookbook by Jeff Donner - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/66315"""
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
281 def __init__(self, other=None):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
282 if other:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
283 # Doesn't do keyword args
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
284 if isinstance(other, dict):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
285 for k,v in other.items():
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
286 dict.__setitem__(self, k.lower(), v)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
287 else:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
288 for k,v in other:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
289 dict.__setitem__(self, k.lower(), v)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
290 def __getitem__(self, key):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
291 return dict.__getitem__(self, key.lower())
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
292 def __setitem__(self, key, value):
273
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
293 try:
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
294 key = key.lower()
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
295 except AttributeError:
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
296 pass
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
297 dict.__setitem__(self, key, value)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
298 def __contains__(self, key):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
299 return dict.__contains__(self, key.lower())
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
300 def has_key(self, key):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
301 return dict.has_key(self, key.lower())
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
302 def get(self, key, def_val=None):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
303 return dict.get(self, key.lower(), def_val)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
304 def setdefault(self, key, def_val=None):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
305 return dict.setdefault(self, key.lower(), def_val)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
306 def update(self, other):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
307 for k,v in other.items():
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
308 dict.__setitem__(self, k.lower(), v)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
309 def fromkeys(self, iterable, value=None):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
310 d = CaselessDict()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
311 for k in iterable:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
312 dict.__setitem__(d, k.lower(), value)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
313 return d
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
314 def pop(self, key, def_val=None):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
315 return dict.pop(self, key.lower(), def_val)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
316
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
317 class Parser(object):
246
b5d4a122354a can round-trip a package now
catherine@dellzilla
parents: 245
diff changeset
318 comment_def = "--" + ~ ('-' + pyparsing.CaselessKeyword('begin')) + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n"))
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
319 def __init__(self, scanner, retainSeparator=True):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
320 self.scanner = scanner
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
321 self.scanner.ignore(pyparsing.sglQuotedString)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
322 self.scanner.ignore(pyparsing.dblQuotedString)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
323 self.scanner.ignore(self.comment_def)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
324 self.scanner.ignore(pyparsing.cStyleComment)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
325 self.retainSeparator = retainSeparator
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
326 def separate(self, txt):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
327 itms = []
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
328 for (sqlcommand, start, end) in self.scanner.scanString(txt):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
329 if sqlcommand:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
330 if type(sqlcommand[0]) == pyparsing.ParseResults:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
331 if self.retainSeparator:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
332 itms.append("".join(sqlcommand[0]))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
333 else:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
334 itms.append(sqlcommand[0][0])
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
335 else:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
336 if sqlcommand[0]:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
337 itms.append(sqlcommand[0])
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
338 return itms
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
339
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
340 bindScanner = Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" ))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
341
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
342 def findBinds(target, existingBinds, givenBindVars = {}):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
343 result = givenBindVars
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
344 for finding, startat, endat in bindScanner.scanner.scanString(target):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
345 varname = finding[1]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
346 try:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
347 result[varname] = existingBinds[varname]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
348 except KeyError:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
349 if not givenBindVars.has_key(varname):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
350 print 'Bind variable %s not defined.' % (varname)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
351 return result
192
6bb8a112af6b accept special terminators on most anything
catherine@dellzilla
parents: 191
diff changeset
352
271
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
353 class ResultSet(list):
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
354 pass
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
355
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
356 class Result(tuple):
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
357 def __str__(self):
272
fee766daa57b yay py works fully
catherine@Elli.myhome.westell.com
parents: 271
diff changeset
358 return '\n'.join('%s: %s' % (colname, self[idx])
fee766daa57b yay py works fully
catherine@Elli.myhome.westell.com
parents: 271
diff changeset
359 for (idx, colname) in enumerate(self.resultset.colnames))
271
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
360 def __getattr__(self, attr):
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
361 attr = attr.lower()
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
362 try:
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
363 return self[self.resultset.colnames.index(attr)]
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
364 except ValueError:
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
365 if attr in ('colnames', 'statement', 'bindvars'):
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
366 return getattr(self.resultset, attr)
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
367 else:
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
368 raise AttributeError, "available columns are: " + ", ".join(self.resultset.colnames)
273
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
369 def bind(self):
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
370 for (idx, colname) in enumerate(self.resultset.colnames):
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
371 self.resultset.pystate['binds'][colname] = self[idx]
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
372 self.resultset.pystate['binds'][idx+1] = self[idx]
271
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
373
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
374 class sqlpyPlus(sqlpython.sqlpython):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
375 defaultExtension = 'sql'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
376 sqlpython.sqlpython.shortcuts.update({':': 'setbind',
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
377 '\\': 'psql',
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
378 '@': '_load'})
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
379 multilineCommands = '''select insert update delete tselect
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
380 create drop alter _multiline_comment'''.split()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
381 sqlpython.sqlpython.noSpecialParse.append('spool')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
382 commentGrammars = pyparsing.Or([pyparsing.Literal('--') + pyparsing.restOfLine, pyparsing.cStyleComment])
246
b5d4a122354a can round-trip a package now
catherine@dellzilla
parents: 245
diff changeset
383 commentGrammars = pyparsing.Or([Parser.comment_def, pyparsing.cStyleComment])
259
c0847a4c7f49 one-shot connection changes
catherine@Elli.myhome.westell.com
parents: 257
diff changeset
384 prefixParser = pyparsing.Optional(pyparsing.Word(pyparsing.nums)('connection_number')
c0847a4c7f49 one-shot connection changes
catherine@Elli.myhome.westell.com
parents: 257
diff changeset
385 + ':')
247
f0f293d83337 begin docs
catherine@dellzilla
parents: 246
diff changeset
386 default_file_name = 'afiedt.buf'
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
387 def __init__(self):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
388 sqlpython.sqlpython.__init__(self)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
389 self.binds = CaselessDict()
271
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
390 self.settable += 'autobind commit_on_exit maxfetch maxtselctrows result_history_max_mbytes scan serveroutput sql_echo timeout heading wildsql'.split()
247
f0f293d83337 begin docs
catherine@dellzilla
parents: 246
diff changeset
391 self.settable.remove('case_insensitive')
230
e1e6b820f81b improving ambiguous SETs
catherine@dellzilla
parents: 229
diff changeset
392 self.settable.sort()
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
393 self.stdoutBeforeSpool = sys.stdout
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
394 self.sql_echo = False
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
395 self.spoolFile = None
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
396 self.autobind = False
229
411f78dc1e07 accept SET HEADING
catherine@dellzilla
parents: 228
diff changeset
397 self.heading = True
240
6d9a65b442b5 negative wildcards fixed
catherine@Elli.myhome.westell.com
parents: 239
diff changeset
398 self.wildsql = False
257
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
399 self.serveroutput = True
271
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
400 self.scan = True
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
401 self.substvars = {}
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
402 self.result_history = []
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
403 self.result_history_max_mbytes = 10
273
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
404 self.pystate = {'r': [], 'binds': self.binds}
257
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
405
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
406 # overrides cmd's parseline
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
407 def parseline(self, line):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
408 """Parse the line into a command name and a string containing
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
409 the arguments. Returns a tuple containing (command, args, line).
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
410 'command' and 'args' may be None if the line couldn't be parsed.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
411 Overrides cmd.cmd.parseline to accept variety of shortcuts.."""
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
412
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
413 cmd, arg, line = sqlpython.sqlpython.parseline(self, line)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
414 if cmd in ('select', 'sleect', 'insert', 'update', 'delete', 'describe',
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
415 'desc', 'comments', 'pull', 'refs', 'desc', 'triggers', 'find') \
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
416 and not hasattr(self, 'curs'):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
417 print 'Not connected.'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
418 return '', '', ''
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
419 return cmd, arg, line
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
420
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
421 do__load = Cmd.do_load
257
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
422
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
423 def dbms_output(self):
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
424 "Dumps contents of Oracle's DBMS_OUTPUT buffer (where PUT_LINE goes)"
261
0044cfa5867a disconnections worked out
catherine@dellzilla
parents: 259
diff changeset
425 try:
0044cfa5867a disconnections worked out
catherine@dellzilla
parents: 259
diff changeset
426 line = self.curs.var(cx_Oracle.STRING)
0044cfa5867a disconnections worked out
catherine@dellzilla
parents: 259
diff changeset
427 status = self.curs.var(cx_Oracle.NUMBER)
257
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
428 self.curs.callproc('dbms_output.get_line', [line, status])
261
0044cfa5867a disconnections worked out
catherine@dellzilla
parents: 259
diff changeset
429 while not status.getvalue():
0044cfa5867a disconnections worked out
catherine@dellzilla
parents: 259
diff changeset
430 self.stdout.write(line.getvalue())
0044cfa5867a disconnections worked out
catherine@dellzilla
parents: 259
diff changeset
431 self.stdout.write('\n')
0044cfa5867a disconnections worked out
catherine@dellzilla
parents: 259
diff changeset
432 self.curs.callproc('dbms_output.get_line', [line, status])
0044cfa5867a disconnections worked out
catherine@dellzilla
parents: 259
diff changeset
433 except AttributeError:
0044cfa5867a disconnections worked out
catherine@dellzilla
parents: 259
diff changeset
434 pass
257
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
435
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
436 def postcmd(self, stop, line):
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
437 """Hook method executed just after a command dispatch is finished."""
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
438 if self.serveroutput:
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
439 self.dbms_output()
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
440 return stop
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
441
245
05c90f80815c trying REMARK BEGIN / REMARK END
catherine@Elli.myhome.westell.com
parents: 244
diff changeset
442 def do_remark(self, line):
242
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
443 '''
245
05c90f80815c trying REMARK BEGIN / REMARK END
catherine@Elli.myhome.westell.com
parents: 244
diff changeset
444 REMARK is one way to denote a comment in SQL*Plus.
05c90f80815c trying REMARK BEGIN / REMARK END
catherine@Elli.myhome.westell.com
parents: 244
diff changeset
445
05c90f80815c trying REMARK BEGIN / REMARK END
catherine@Elli.myhome.westell.com
parents: 244
diff changeset
446 Wrapping a *single* SQL or PL/SQL statement in `REMARK BEGIN` and `REMARK END`
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
447 tells sqlpython to submit the enclosed code directly to Oracle as a single
242
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
448 unit of code.
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
449
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
450 Without these markers, sqlpython fails to properly distinguish the beginning
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
451 and end of all but the simplest PL/SQL blocks, causing errors. sqlpython also
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
452 slows down when parsing long SQL statements as it tries to determine whether
245
05c90f80815c trying REMARK BEGIN / REMARK END
catherine@Elli.myhome.westell.com
parents: 244
diff changeset
453 the statement has ended yet; `REMARK BEGIN` and `REMARK END` allow it to skip this
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
454 parsing.
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
455
245
05c90f80815c trying REMARK BEGIN / REMARK END
catherine@Elli.myhome.westell.com
parents: 244
diff changeset
456 Standard SQL*Plus interprets REMARK BEGIN and REMARK END as comments, so it is
242
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
457 safe to include them in SQL*Plus scripts.
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
458 '''
245
05c90f80815c trying REMARK BEGIN / REMARK END
catherine@Elli.myhome.westell.com
parents: 244
diff changeset
459 if not line.lower().strip().startswith('begin'):
05c90f80815c trying REMARK BEGIN / REMARK END
catherine@Elli.myhome.westell.com
parents: 244
diff changeset
460 return
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
461 statement = []
247
f0f293d83337 begin docs
catherine@dellzilla
parents: 246
diff changeset
462 next = self.pseudo_raw_input(self.continuation_prompt)
245
05c90f80815c trying REMARK BEGIN / REMARK END
catherine@Elli.myhome.westell.com
parents: 244
diff changeset
463 while next.lower().split()[:2] != ['remark','end']:
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
464 statement.append(next)
247
f0f293d83337 begin docs
catherine@dellzilla
parents: 246
diff changeset
465 next = self.pseudo_raw_input(self.continuation_prompt)
242
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
466 return self.onecmd('\n'.join(statement))
272
fee766daa57b yay py works fully
catherine@Elli.myhome.westell.com
parents: 271
diff changeset
467
fee766daa57b yay py works fully
catherine@Elli.myhome.westell.com
parents: 271
diff changeset
468 def do_py(self, arg):
fee766daa57b yay py works fully
catherine@Elli.myhome.westell.com
parents: 271
diff changeset
469 '''
fee766daa57b yay py works fully
catherine@Elli.myhome.westell.com
parents: 271
diff changeset
470 py <command>: Executes a Python command.
fee766daa57b yay py works fully
catherine@Elli.myhome.westell.com
parents: 271
diff changeset
471 py: Enters interactive Python mode (end with `\py`).
fee766daa57b yay py works fully
catherine@Elli.myhome.westell.com
parents: 271
diff changeset
472 Past SELECT results are stored in list `r`; most recent resultset is `r[-1]`.
274
0ae3a8227bc0 added binds to help
catherine@Elli.myhome.westell.com
parents: 273
diff changeset
473 SQL bind variables can be accessed/changed via `binds`.
272
fee766daa57b yay py works fully
catherine@Elli.myhome.westell.com
parents: 271
diff changeset
474 '''
273
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
475 return Cmd.do_py(self, arg)
272
fee766daa57b yay py works fully
catherine@Elli.myhome.westell.com
parents: 271
diff changeset
476
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
477 def onecmd_plus_hooks(self, line):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
478 line = self.precmd(line)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
479 stop = self.onecmd(line)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
480 stop = self.postcmd(stop, line)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
481
257
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
482 def _onchange_serveroutput(self, old, new):
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
483 if new:
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
484 self.curs.callproc('dbms_output.enable', [])
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
485 else:
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
486 self.curs.callproc('dbms_output.disable', [])
6d4d90fb2082 dbms_output.put_line working
catherine@Elli.myhome.westell.com
parents: 254
diff changeset
487
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
488 def do_shortcuts(self,arg):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
489 """Lists available first-character shortcuts
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
490 (i.e. '!dir' is equivalent to 'shell dir')"""
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
491 for (scchar, scto) in self.shortcuts.items():
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
492 print '%s: %s' % (scchar, scto)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
493
254
b61e21386383 oops, restore lines of code after sql_format_item
catherine@Elli.myhome.westell.com
parents: 253
diff changeset
494 tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL)
b61e21386383 oops, restore lines of code after sql_format_item
catherine@Elli.myhome.westell.com
parents: 253
diff changeset
495 inputStatementFormatters = {
b61e21386383 oops, restore lines of code after sql_format_item
catherine@Elli.myhome.westell.com
parents: 253
diff changeset
496 cx_Oracle.STRING: "'%s'",
b61e21386383 oops, restore lines of code after sql_format_item
catherine@Elli.myhome.westell.com
parents: 253
diff changeset
497 cx_Oracle.DATETIME: "TO_DATE('%s', 'YYYY-MM-DD HH24:MI:SS')"}
b61e21386383 oops, restore lines of code after sql_format_item
catherine@Elli.myhome.westell.com
parents: 253
diff changeset
498 inputStatementFormatters[cx_Oracle.CLOB] = inputStatementFormatters[cx_Oracle.STRING]
b61e21386383 oops, restore lines of code after sql_format_item
catherine@Elli.myhome.westell.com
parents: 253
diff changeset
499 inputStatementFormatters[cx_Oracle.TIMESTAMP] = inputStatementFormatters[cx_Oracle.DATETIME]
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
500 def output(self, outformat, rowlimit):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
501 self.tblname = self.tableNameFinder.search(self.curs.statement).group(1)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
502 self.colnames = [d[0] for d in self.curs.description]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
503 if outformat in output_templates:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
504 self.colnamelen = max(len(colname) for colname in self.colnames)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
505 self.coltypes = [d[1] for d in self.curs.description]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
506 self.formatters = [self.inputStatementFormatters.get(typ, '%s') for typ in self.coltypes]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
507 result = output_templates[outformat].generate(**self.__dict__)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
508 elif outformat == '\\t': # transposed
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
509 rows = [self.colnames]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
510 rows.extend(list(self.rows))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
511 transpr = [[rows[y][x] for y in range(len(rows))]for x in range(len(rows[0]))] # matrix transpose
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
512 newdesc = [['ROW N.'+str(y),10] for y in range(len(rows))]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
513 for x in range(len(self.curs.description)):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
514 if str(self.curs.description[x][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
515 rname = transpr[x][0]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
516 transpr[x] = map(binascii.b2a_hex, transpr[x])
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
517 transpr[x][0] = rname
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
518 newdesc[0][0] = 'COLUMN NAME'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
519 result = '\n' + sqlpython.pmatrix(transpr,newdesc)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
520 elif outformat in ('\\l', '\\L', '\\p', '\\b'):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
521 plot = Plot()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
522 plot.build(self, outformat)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
523 plot.shelve()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
524 plot.draw()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
525 return ''
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
526 else:
229
411f78dc1e07 accept SET HEADING
catherine@dellzilla
parents: 228
diff changeset
527 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch, heading=self.heading)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
528 return result
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
529
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
530 legalOracle = re.compile('[a-zA-Z_$#]')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
531
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
532 def select_scalar_list(self, sql, binds={}):
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
533 self._execute(sql, binds)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
534 return [r[0] for r in self.curs.fetchall()]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
535
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
536 columnNameRegex = re.compile(
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
537 r'select\s+(.*)from',
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
538 re.IGNORECASE | re.DOTALL | re.MULTILINE)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
539 def completedefault(self, text, line, begidx, endidx):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
540 segment = completion.whichSegment(line)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
541 text = text.upper()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
542 completions = []
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
543 if segment == 'select':
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
544 stmt = "SELECT column_name FROM user_tab_columns WHERE column_name LIKE '%s%%'"
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
545 completions = self.select_scalar_list(stmt % (text))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
546 if not completions:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
547 stmt = "SELECT column_name FROM all_tab_columns WHERE column_name LIKE '%s%%'"
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
548 completions = self.select_scalar_list(stmt % (text))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
549 if segment == 'from':
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
550 columnNames = self.columnNameRegex.search(line)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
551 if columnNames:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
552 columnNames = columnNames.group(1)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
553 columnNames = [c.strip().upper() for c in columnNames.split(',')]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
554 stmt1 = "SELECT table_name FROM all_tab_columns WHERE column_name = '%s' AND table_name LIKE '%s%%'"
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
555 for columnName in columnNames:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
556 # and if columnName is * ?
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
557 completions.extend(self.select_scalar_list(stmt1 % (columnName, text)))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
558 if segment in ('from', 'update', 'insert into') and (not completions):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
559 stmt = "SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'"
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
560 completions = self.select_scalar_list(stmt % (text))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
561 if not completions:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
562 stmt = """SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
563 UNION
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
564 SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'"""
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
565 completions = self.select_scalar_list(stmt % (text, text))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
566 if segment in ('where', 'group by', 'order by', 'having', 'set'):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
567 tableNames = completion.tableNamesFromFromClause(line)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
568 if tableNames:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
569 stmt = """SELECT column_name FROM all_tab_columns
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
570 WHERE table_name IN (%s)""" % \
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
571 (','.join("'%s'" % (t) for t in tableNames))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
572 stmt = "%s AND column_name LIKE '%s%%'" % (stmt, text)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
573 completions = self.select_scalar_list(stmt)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
574 if not segment:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
575 stmt = "SELECT object_name FROM all_objects WHERE object_name LIKE '%s%%'"
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
576 completions = self.select_scalar_list(stmt % (text))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
577 return completions
233
dc7683970717 beginning wildsql
catherine@Elli.myhome.westell.com
parents: 232
diff changeset
578
234
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
579 columnlistPattern = pyparsing.SkipTo(pyparsing.CaselessKeyword('from'))('columns') + \
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
580 pyparsing.SkipTo(pyparsing.stringEnd)('remainder')
233
dc7683970717 beginning wildsql
catherine@Elli.myhome.westell.com
parents: 232
diff changeset
581
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
582 negator = pyparsing.Literal('!')('exclude')
242
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
583 colNumber = pyparsing.Optional(negator) + pyparsing.Literal('#') + pyparsing.Word('-' + pyparsing.nums, pyparsing.nums)('column_number')
238
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
584 colName = negator + pyparsing.Word('$_#' + pyparsing.alphas, '$_#' + pyparsing.alphanums)('column_name')
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
585 wildColName = pyparsing.Optional(negator) + pyparsing.Word('?*%$_#' + pyparsing.alphas, '?*%$_#' + pyparsing.alphanums, min=2)('column_name')
242
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
586 colNumber.ignore(pyparsing.cStyleComment).ignore(Parser.comment_def). \
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
587 ignore(pyparsing.sglQuotedString).ignore(pyparsing.dblQuotedString)
235
8e5f58d504d4 tightening up wildcard pyparsing
catherine@dellzilla
parents: 234
diff changeset
588 wildSqlParser = colNumber ^ colName ^ wildColName
8e5f58d504d4 tightening up wildcard pyparsing
catherine@dellzilla
parents: 234
diff changeset
589 wildSqlParser.ignore(pyparsing.cStyleComment).ignore(Parser.comment_def). \
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
590 ignore(pyparsing.sglQuotedString).ignore(pyparsing.dblQuotedString)
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
591 emptyCommaRegex = re.compile(',\s*,', re.DOTALL)
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
592 deadStarterCommaRegex = re.compile('^\s*,', re.DOTALL)
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
593 deadEnderCommaRegex = re.compile(',\s*$', re.DOTALL)
233
dc7683970717 beginning wildsql
catherine@Elli.myhome.westell.com
parents: 232
diff changeset
594 def expandWildSql(self, arg):
dc7683970717 beginning wildsql
catherine@Elli.myhome.westell.com
parents: 232
diff changeset
595 try:
234
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
596 columnlist = self.columnlistPattern.parseString(arg)
233
dc7683970717 beginning wildsql
catherine@Elli.myhome.westell.com
parents: 232
diff changeset
597 except pyparsing.ParseException:
dc7683970717 beginning wildsql
catherine@Elli.myhome.westell.com
parents: 232
diff changeset
598 return arg
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
599 parseresults = list(self.wildSqlParser.scanString(columnlist.columns))
239
4c563c2218e6 catching standard names caught
catherine@Elli.myhome.westell.com
parents: 238
diff changeset
600 # I would rather exclude non-wild column names in the grammar,
4c563c2218e6 catching standard names caught
catherine@Elli.myhome.westell.com
parents: 238
diff changeset
601 # but can't figure out how
4c563c2218e6 catching standard names caught
catherine@Elli.myhome.westell.com
parents: 238
diff changeset
602 parseresults = [p for p in parseresults if
4c563c2218e6 catching standard names caught
catherine@Elli.myhome.westell.com
parents: 238
diff changeset
603 p[0].column_number or
4c563c2218e6 catching standard names caught
catherine@Elli.myhome.westell.com
parents: 238
diff changeset
604 '*' in p[0].column_name or
4c563c2218e6 catching standard names caught
catherine@Elli.myhome.westell.com
parents: 238
diff changeset
605 '%' in p[0].column_name or
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
606 '?' in p[0].column_name or
239
4c563c2218e6 catching standard names caught
catherine@Elli.myhome.westell.com
parents: 238
diff changeset
607 p[0].exclude]
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
608 if not parseresults:
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
609 return arg
234
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
610 self.curs.execute('select * ' + columnlist.remainder, self.varsUsed)
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
611 columns_available = [d[0] for d in self.curs.description]
234
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
612 replacers = {}
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
613 included = set()
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
614 excluded = set()
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
615 for (col, startpos, endpos) in parseresults:
238
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
616 replacers[arg[startpos:endpos]] = []
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
617 if col.column_name:
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
618 finder = col.column_name.replace('*','.*')
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
619 finder = finder.replace('%','.*')
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
620 finder = finder.replace('?','.')
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
621 colnames = [c for c in columns_available if re.match(finder + '$', c, re.IGNORECASE)]
238
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
622 elif col.column_number:
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
623 idx = int(col.column_number)
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
624 if idx > 0:
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
625 idx -= 1
238
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
626 colnames = [columns_available[idx]]
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
627 if not colnames:
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
628 print 'No columns found matching criteria.'
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
629 return 'null from dual'
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
630 for colname in colnames:
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
631 if col.exclude:
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
632 included.discard(colname)
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
633 include_here = columns_available[:]
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
634 include_here.remove(colname)
240
6d9a65b442b5 negative wildcards fixed
catherine@Elli.myhome.westell.com
parents: 239
diff changeset
635 replacers[arg[startpos:endpos]].extend(i for i in include_here if i not in replacers[arg[startpos:endpos]])
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
636 excluded.add(colname)
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
637 else:
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
638 excluded.discard(colname)
238
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
639 replacers[arg[startpos:endpos]].append(colname)
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
640
234
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
641 replacers = sorted(replacers.items(), key=len, reverse=True)
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
642 result = columnlist.columns
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
643 for (target, replacement) in replacers:
242
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
644 cols = [r for r in replacement if r not in excluded and r not in included]
238
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
645 replacement = ', '.join(cols)
254fb9d3f4c3 must fix catching regular cols as wilds, repeating on eqdbw/mtndew@orcl
catherine@Elli.myhome.westell.com
parents: 237
diff changeset
646 included.update(cols)
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
647 result = result.replace(target, replacement)
242
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
648 # some column names could get wiped out completely, so we fix their dangling commas
236
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
649 result = self.emptyCommaRegex.sub(',', result)
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
650 result = self.deadStarterCommaRegex.sub('', result)
7f999b141fcd debugging streamlined wild sql
catherine@dellzilla
parents: 235
diff changeset
651 result = self.deadEnderCommaRegex.sub('', result)
242
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
652 if not result.strip():
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
653 print 'No columns found matching criteria.'
8866fe0706c3 message when all columns excluded
catherine@dellzilla
parents: 241
diff changeset
654 return 'null from dual'
237
95070e01907d not col num works
catherine@Elli.myhome.westell.com
parents: 236
diff changeset
655 return result + ' ' + columnlist.remainder
233
dc7683970717 beginning wildsql
catherine@Elli.myhome.westell.com
parents: 232
diff changeset
656
271
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
657 def do_ampersand_substitution(self, raw, regexpr, isglobal):
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
658 subst = regexpr.search(raw)
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
659 while subst:
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
660 fullexpr, var = subst.group(1), subst.group(2)
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
661 print 'Substitution variable %s found in:' % fullexpr
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
662 print raw[max(subst.start()-20, 0):subst.end()+20]
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
663 if isglobal and (var in self.substvars):
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
664 val = self.substvars[var]
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
665 else:
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
666 val = raw_input('Substitution for %s (SET SCAN OFF to halt substitution): ' % fullexpr)
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
667 if val.lower().split() == ['set','scan','off']:
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
668 self.scan = False
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
669 return raw
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
670 if isglobal:
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
671 self.substvars[var] = val
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
672 raw = raw.replace(fullexpr, val)
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
673 print 'Substituted %s for %s' % (val, fullexpr)
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
674 subst = regexpr.search(raw) # do not FINDALL b/c we don't want to ask twice
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
675 return raw
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
676
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
677 doubleampre = re.compile('(&&([a-zA-Z\d_$#]+))')
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
678 singleampre = re.compile( '(&([a-zA-Z\d_$#]+))')
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
679 def preparse(self, raw, **kwargs):
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
680 if self.scan:
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
681 raw = self.do_ampersand_substitution(raw, regexpr=self.doubleampre, isglobal=True)
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
682 if self.scan:
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
683 raw = self.do_ampersand_substitution(raw, regexpr=self.singleampre, isglobal=False)
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
684 return raw
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
685
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
686 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit')
204
1c3df79fd088 \n\n finally works
catherine@dellzilla
parents: 203
diff changeset
687 terminators = '; \\C \\t \\i \\p \\l \\L \\b '.split() + output_templates.keys()
199
09592342a33d ugh - parsing stripping command causes real trouble
catherine@dellzilla
parents: 198
diff changeset
688
273
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
689 def do_bind(self, arg):
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
690 self.pystate['r'][-1][-1].bind()
271
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
691
192
6bb8a112af6b accept special terminators on most anything
catherine@dellzilla
parents: 191
diff changeset
692 def do_select(self, arg, bindVarsIn=None, terminator=None):
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
693 """Fetch rows from a table.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
694
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
695 Limit the number of rows retrieved by appending
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
696 an integer after the terminator
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
697 (example: SELECT * FROM mytable;10 )
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
698
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
699 Output may be formatted by choosing an alternative terminator
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
700 ("help terminators" for details)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
701 """
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
702 bindVarsIn = bindVarsIn or {}
196
7940955920a8 little fixes
catherine@dellzilla
parents: 195
diff changeset
703 try:
7940955920a8 little fixes
catherine@dellzilla
parents: 195
diff changeset
704 rowlimit = int(arg.parsed.suffix or 0)
7940955920a8 little fixes
catherine@dellzilla
parents: 195
diff changeset
705 except ValueError:
7940955920a8 little fixes
catherine@dellzilla
parents: 195
diff changeset
706 rowlimit = 0
206
ed46f2dba929 fixed sessinfo
catherine@Elli.myhome.westell.com
parents: 204
diff changeset
707 print "Specify desired number of rows after terminator (not '%s')" % arg.parsed.suffix
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
708 self.varsUsed = findBinds(arg, self.binds, bindVarsIn)
233
dc7683970717 beginning wildsql
catherine@Elli.myhome.westell.com
parents: 232
diff changeset
709 if self.wildsql:
234
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
710 selecttext = self.expandWildSql(arg)
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
711 else:
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
712 selecttext = arg
a86efbca3da9 ha ha ha - wildcards in selects working now
catherine@dellzilla
parents: 233
diff changeset
713 self.curs.execute('select ' + selecttext, self.varsUsed)
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
714 self.rows = self.curs.fetchmany(min(self.maxfetch, (rowlimit or self.maxfetch)))
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
715 self.rc = self.curs.rowcount
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
716 if self.rc > 0:
271
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
717 resultset = ResultSet()
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
718 resultset.colnames = [d[0].lower() for d in self.curs.description]
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
719 resultset.statement = 'select ' + selecttext
273
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
720 resultset.varsUsed = self.varsUsed
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
721 resultset.pystate = self.pystate
271
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
722 resultset.extend([Result(r) for r in self.rows])
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
723 for row in resultset:
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
724 row.resultset = resultset
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
725 self.stdout.write('\n%s\n' % (self.output(arg.parsed.terminator, rowlimit)))
271
fbe23b635300 py object manipulation almost done
catherine@dellzilla
parents: 265
diff changeset
726 self.pystate['r'].append(resultset)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
727 if self.rc == 0:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
728 print '\nNo rows Selected.\n'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
729 elif self.rc == 1:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
730 print '\n1 row selected.\n'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
731 if self.autobind:
273
9d67065ea030 data into/out of py via binds
catherine@Elli.myhome.westell.com
parents: 272
diff changeset
732 self.do_bind(None)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
733 elif self.rc < self.maxfetch:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
734 print '\n%d rows selected.\n' % self.rc
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
735 else:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
736 print '\nSelected Max Num rows (%d)' % self.rc
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
737
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
738 def do_cat(self, arg):
226
6701c3f097f9 more comments
catherine@Elli.myhome.westell.com
parents: 224
diff changeset
739 '''Shortcut for SELECT * FROM'''
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
740 return self.do_select(self.parsed('SELECT * FROM %s;' % arg,
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
741 terminator = arg.parsed.terminator or ';',
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
742 suffix = arg.parsed.suffix))
220
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
743
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
744 def _pull(self, arg, opts, vc=None):
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
745 """Displays source code."""
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
746 if opts.dump:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
747 statekeeper = Statekeeper(self, ('stdout',))
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
748 try:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
749 for (owner, object_type, object_name) in self.resolve_many(arg, opts):
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
750 if object_type in self.supported_ddl_types:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
751 object_type = {'DATABASE LINK': 'DB_LINK', 'JAVA CLASS': 'JAVA_SOURCE'
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
752 }.get(object_type) or object_type
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
753 object_type = object_type.replace(' ', '_')
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
754 if opts.dump:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
755 try:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
756 os.makedirs(os.path.join(owner.lower(), object_type.lower()))
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
757 except OSError:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
758 pass
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
759 filename = os.path.join(owner.lower(), object_type.lower(), '%s.sql' % object_name.lower())
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
760 self.stdout = open(filename, 'w')
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
761 if vc:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
762 subprocess.call(vc + [filename])
248
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
763 if object_type == 'PACKAGE':
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
764 ddl = [['PACKAGE_SPEC', object_name, owner],['PACKAGE_BODY', object_name, owner]]
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
765 elif object_type in ['CONTEXT', 'DIRECTORY', 'JOB']:
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
766 ddl = [[object_type, object_name]]
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
767 else:
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
768 ddl = [[object_type, object_name, owner]]
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
769 for ddlargs in ddl:
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
770 try:
246
b5d4a122354a can round-trip a package now
catherine@dellzilla
parents: 245
diff changeset
771 self.stdout.write('REMARK BEGIN %s\n%s\nREMARK END\n\n' % (object_name, str(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB, ddlargs))))
248
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
772 except cx_Oracle.DatabaseError, errmsg:
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
773 if object_type == 'JOB':
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
774 print '%s: DBMS_METADATA.GET_DDL does not support JOBs (MetaLink DocID 567504.1)' % object_name
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
775 elif 'ORA-31603' in str(errmsg): # not found, as in package w/o package body
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
776 pass
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
777 else:
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
778 raise
220
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
779 if opts.full:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
780 for dependent_type in ('OBJECT_GRANT', 'CONSTRAINT', 'TRIGGER'):
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
781 try:
245
05c90f80815c trying REMARK BEGIN / REMARK END
catherine@Elli.myhome.westell.com
parents: 244
diff changeset
782 self.stdout.write('REMARK BEGIN\n%s\nREMARK END\n\n' % str(self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB,
220
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
783 [dependent_type, object_name, owner])))
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
784 except cx_Oracle.DatabaseError:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
785 pass
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
786 if opts.dump:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
787 self.stdout.close()
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
788 except:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
789 if opts.dump:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
790 statekeeper.restore()
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
791 raise
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
792 if opts.dump:
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
793 statekeeper.restore()
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
794
221
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
795 def do_show(self, arg):
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
796 '''
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
797 show - display value of all sqlpython parameters
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
798 show (parameter name) - display value of a sqlpython parameter
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
799 show parameter (parameter name) - display value of an ORACLE parameter
265
041c656dc8e5 show err working nicely now
catherine@Elli.myhome.westell.com
parents: 264
diff changeset
800 show err (object type/name) - errors from latest PL/SQL object compilation.
041c656dc8e5 show err working nicely now
catherine@Elli.myhome.westell.com
parents: 264
diff changeset
801 show all err (type/name) - all compilation errors from the user's PL/SQL objects.
221
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
802 '''
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
803 if arg.startswith('param'):
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
804 try:
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
805 paramname = arg.split()[1].lower()
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
806 except IndexError:
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
807 paramname = ''
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
808 self.onecmd("""SELECT name,
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
809 CASE type WHEN 1 THEN 'BOOLEAN'
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
810 WHEN 2 THEN 'STRING'
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
811 WHEN 3 THEN 'INTEGER'
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
812 WHEN 4 THEN 'PARAMETER FILE'
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
813 WHEN 5 THEN 'RESERVED'
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
814 WHEN 6 THEN 'BIG INTEGER' END type,
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
815 value FROM v$parameter WHERE name LIKE '%%%s%%';""" % paramname)
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
816 else:
264
a8deaa38f11e show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents: 261
diff changeset
817 argpieces = arg.lower().split()
a8deaa38f11e show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents: 261
diff changeset
818 try:
a8deaa38f11e show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents: 261
diff changeset
819 if argpieces[0][:3] == 'err':
265
041c656dc8e5 show err working nicely now
catherine@Elli.myhome.westell.com
parents: 264
diff changeset
820 return self._show_errors(all_users=False, limit=1, targets=argpieces[1:])
264
a8deaa38f11e show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents: 261
diff changeset
821 elif (argpieces[0], argpieces[1][:3]) == ('all','err'):
265
041c656dc8e5 show err working nicely now
catherine@Elli.myhome.westell.com
parents: 264
diff changeset
822 return self._show_errors(all_users=False, limit=None, targets=argpieces[2:])
264
a8deaa38f11e show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents: 261
diff changeset
823 except IndexError:
a8deaa38f11e show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents: 261
diff changeset
824 pass
221
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
825 return Cmd.do_show(self, arg)
264
a8deaa38f11e show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents: 261
diff changeset
826 do_sho = do_show
221
3a59ca05f980 added show parameter
catherine@Elli.myhome.westell.com
parents: 220
diff changeset
827
218
397979c7f6d6 dumping working but not for wildcards
catherine@Elli.myhome.westell.com
parents: 217
diff changeset
828 @options([make_option('-d', '--dump', action='store_true', help='dump results to files'),
397979c7f6d6 dumping working but not for wildcards
catherine@Elli.myhome.westell.com
parents: 217
diff changeset
829 make_option('-f', '--full', action='store_true', help='get dependent objects as well'),
217
a65b98938596 multi-pull working pretty well
catherine@Elli.myhome.westell.com
parents: 216
diff changeset
830 make_option('-a', '--all', action='store_true', help="all schemas' objects"),
218
397979c7f6d6 dumping working but not for wildcards
catherine@Elli.myhome.westell.com
parents: 217
diff changeset
831 make_option('-x', '--exact', action='store_true', help="match object name exactly")])
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
832 def do_pull(self, arg, opts):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
833 """Displays source code."""
220
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
834 self._pull(arg, opts)
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
835
247
f0f293d83337 begin docs
catherine@dellzilla
parents: 246
diff changeset
836 supported_ddl_types = 'CLUSTER, CONTEXT, DATABASE LINK, DIRECTORY, FUNCTION, INDEX, JOB, LIBRARY, MATERIALIZED VIEW, PACKAGE, PACKAGE BODY, PACKAGE SPEC, OPERATOR, PACKAGE, PROCEDURE, SEQUENCE, SYNONYM, TABLE, TRIGGER, VIEW, TYPE, TYPE BODY, XML SCHEMA'
220
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
837 do_pull.__doc__ += '\n\nSupported DDL types: ' + supported_ddl_types
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
838 supported_ddl_types = supported_ddl_types.split(', ')
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
839
224
582c84365f6a svn sucks - supporting git instead
catherine@dellzilla
parents: 223
diff changeset
840 def _vc(self, arg, opts, program):
248
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
841 if not os.path.exists('.%s' % program):
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
842 create = raw_input('%s repository not yet in current directory (%s). Create (y/N)? ' %
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
843 (program, os.getcwd()))
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
844 if not create.strip().lower().startswith('y'):
230447ce6e60 end wed
catherine@dellzilla
parents: 247
diff changeset
845 return
224
582c84365f6a svn sucks - supporting git instead
catherine@dellzilla
parents: 223
diff changeset
846 subprocess.call([program, 'init'])
220
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
847 opts.dump = True
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
848 self._pull(arg, opts, vc=[program, 'add'])
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
849 subprocess.call([program, 'commit', '-m', '"%s"' % opts.message or 'committed from sqlpython'])
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
850
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
851 @options([
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
852 make_option('-f', '--full', action='store_true', help='get dependent objects as well'),
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
853 make_option('-a', '--all', action='store_true', help="all schemas' objects"),
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
854 make_option('-x', '--exact', action='store_true', help="match object name exactly"),
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
855 make_option('-m', '--message', action='store', type='string', dest='message', help="message to save to hg log during commit")])
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
856 def do_hg(self, arg, opts):
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
857 '''hg (opts) (objects):
226
6701c3f097f9 more comments
catherine@Elli.myhome.westell.com
parents: 224
diff changeset
858 Stores DDL on disk and puts files under Mercurial version control.
6701c3f097f9 more comments
catherine@Elli.myhome.westell.com
parents: 224
diff changeset
859 Args specify which objects to store, same format as `ls`.'''
224
582c84365f6a svn sucks - supporting git instead
catherine@dellzilla
parents: 223
diff changeset
860 self._vc(arg, opts, 'hg')
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
861
220
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
862 @options([
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
863 make_option('-f', '--full', action='store_true', help='get dependent objects as well'),
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
864 make_option('-a', '--all', action='store_true', help="all schemas' objects"),
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
865 make_option('-x', '--exact', action='store_true', help="match object name exactly"),
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
866 make_option('-m', '--message', action='store', type='string', dest='message', help="message to save to hg log during commit")])
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
867 def do_bzr(self, arg, opts):
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
868 '''bzr (opts) (objects):
226
6701c3f097f9 more comments
catherine@Elli.myhome.westell.com
parents: 224
diff changeset
869 Stores DDL on disk and puts files under Bazaar version control.
6701c3f097f9 more comments
catherine@Elli.myhome.westell.com
parents: 224
diff changeset
870 Args specify which objects to store, same format as `ls`.'''
224
582c84365f6a svn sucks - supporting git instead
catherine@dellzilla
parents: 223
diff changeset
871 self._vc(arg, opts, 'bzr')
220
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
872
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
873 @options([
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
874 make_option('-f', '--full', action='store_true', help='get dependent objects as well'),
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
875 make_option('-a', '--all', action='store_true', help="all schemas' objects"),
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
876 make_option('-x', '--exact', action='store_true', help="match object name exactly"),
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
877 make_option('-m', '--message', action='store', type='string', dest='message', help="message to save to hg log during commit")])
224
582c84365f6a svn sucks - supporting git instead
catherine@dellzilla
parents: 223
diff changeset
878 def do_git(self, arg, opts):
582c84365f6a svn sucks - supporting git instead
catherine@dellzilla
parents: 223
diff changeset
879 '''git (opts) (objects):
226
6701c3f097f9 more comments
catherine@Elli.myhome.westell.com
parents: 224
diff changeset
880 Stores DDL on disk and puts files under git version control.
6701c3f097f9 more comments
catherine@Elli.myhome.westell.com
parents: 224
diff changeset
881 Args specify which objects to store, same format as `ls`.'''
224
582c84365f6a svn sucks - supporting git instead
catherine@dellzilla
parents: 223
diff changeset
882 self._vc(arg, opts, 'git')
220
01af6bfbe78f hg works
catherine@Elli.myhome.westell.com
parents: 219
diff changeset
883
196
7940955920a8 little fixes
catherine@dellzilla
parents: 195
diff changeset
884 all_users_option = make_option('-a', action='store_const', dest="scope",
222
catherine@dellzilla
parents: 221
diff changeset
885 default={'col':'', 'view':'user', 'schemas':'user', 'firstcol': ''},
catherine@dellzilla
parents: 221
diff changeset
886 const={'col':', owner', 'view':'all', 'schemas':'all', 'firstcol': 'owner, '},
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
887 help='Describe all objects (not just my own)')
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
888 @options([all_users_option,
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
889 make_option('-c', '--col', action='store_true', help='find column'),
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
890 make_option('-t', '--table', action='store_true', help='find table')])
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
891 def do_find(self, arg, opts):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
892 """Finds argument in source code or (with -c) in column definitions."""
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
893
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
894 capArg = arg.upper()
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
895
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
896 if opts.col:
222
catherine@dellzilla
parents: 221
diff changeset
897 sql = "SELECT table_name, column_name %s FROM %s_tab_columns where column_name like '%%%s%%' ORDER BY %s table_name, column_name;" \
catherine@dellzilla
parents: 221
diff changeset
898 % (opts.scope['col'], opts.scope['view'], capArg, opts.scope['firstcol'])
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
899 elif opts.table:
222
catherine@dellzilla
parents: 221
diff changeset
900 sql = "SELECT table_name %s from %s_tables where table_name like '%%%s%%' ORDER BY %s table_name;" \
catherine@dellzilla
parents: 221
diff changeset
901 % (opts.scope['col'], opts.scope['view'], capArg, opts.scope['firstcol'])
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
902 else:
196
7940955920a8 little fixes
catherine@dellzilla
parents: 195
diff changeset
903 sql = "SELECT * from %s_source where UPPER(text) like '%%%s%%';" % (opts.scope['view'], capArg)
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
904 self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
905
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
906 @options([all_users_option])
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
907 def do_describe(self, arg, opts):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
908 "emulates SQL*Plus's DESCRIBE"
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
909 target = arg.upper()
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
910 if not target:
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
911 return self.do_select(self.parsed("""SELECT object_name, object_type%s
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
912 FROM %s_objects
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
913 WHERE object_type IN ('TABLE','VIEW','INDEX')
196
7940955920a8 little fixes
catherine@dellzilla
parents: 195
diff changeset
914 ORDER BY object_name;""" % (opts.scope['col'], opts.scope['view']),
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
915 terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
916 object_type, owner, object_name = self.resolve(target)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
917 if not object_type:
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
918 return self.do_select(self.parsed("""SELECT object_name, object_type%s FROM %s_objects
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
919 WHERE object_type IN ('TABLE','VIEW','INDEX')
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
920 AND object_name LIKE '%%%s%%'
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
921 ORDER BY object_name;""" %
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
922 (opts.scope['col'], opts.scope['view'], target),
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
923 terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
924 self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
925 descQ = descQueries.get(object_type)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
926 if descQ:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
927 for q in descQ:
204
1c3df79fd088 \n\n finally works
catherine@dellzilla
parents: 203
diff changeset
928 self.do_select(self.parsed(q, terminator=arg.parsed.terminator or ';' , suffix=arg.parsed.suffix),
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
929 bindVarsIn={'object_name':object_name, 'owner':owner})
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
930 elif object_type == 'PACKAGE':
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
931 packageContents = self.select_scalar_list(descQueries['PackageObjects'][0], {'package_name':object_name, 'owner':owner})
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
932 for packageObj_name in packageContents:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
933 self.stdout.write('Arguments to %s\n' % (packageObj_name))
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
934 sql = self.parsed(descQueries['PackageObjArgs'][0], terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix)
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
935 self.do_select(sql, bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name})
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
936 do_desc = do_describe
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
937
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
938 def do_deps(self, arg):
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
939 target = arg.upper()
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
940 object_type, owner, object_name = self.resolve(target)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
941 if object_type == 'PACKAGE BODY':
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
942 q = "and (type != 'PACKAGE BODY' or name != :object_name)'"
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
943 object_type = 'PACKAGE'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
944 else:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
945 q = ""
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
946 q = """SELECT name,
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
947 type
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
948 from user_dependencies
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
949 where
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
950 referenced_name like :object_name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
951 and referenced_type like :object_type
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
952 and referenced_owner like :owner
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
953 %s;""" % (q)
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
954 self.do_select(self.parsed(q, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix),
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
955 bindVarsIn={'object_name':object_name, 'object_type':object_type, 'owner':owner})
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
956
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
957 def do_comments(self, arg):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
958 'Prints comments on a table and its columns.'
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
959 target = arg.upper()
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
960 object_type, owner, object_name, colName = self.resolve_with_column(target)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
961 if object_type:
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
962 self._execute(queries['tabComments'], {'table_name':object_name, 'owner':owner})
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
963 self.stdout.write("%s %s.%s: %s\n" % (object_type, owner, object_name, self.curs.fetchone()[0]))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
964 if colName:
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
965 sql = queries['oneColComments']
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
966 bindVarsIn={'owner':owner, 'object_name': object_name, 'column_name': colName}
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
967 else:
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
968 sql = queries['colComments']
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
969 bindVarsIn={'owner':owner, 'object_name': object_name}
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
970 self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix),
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
971 bindVarsIn=bindVarsIn)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
972
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
973 def _resolve(self, identifier):
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
974 parts = identifier.split('.')
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
975 if len(parts) == 2:
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
976 owner, object_name = parts
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
977 object_type = self.select_scalar_list('SELECT object_type FROM all_objects WHERE owner = :owner AND object_name = :object_name',
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
978 {'owner': owner, 'object_name': object_name.upper()}
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
979 )[0]
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
980 elif len(parts) == 1:
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
981 object_name = parts[0]
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
982 self._execute(queries['resolve'], {'objName':object_name.upper()})
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
983 object_type, object_name, owner = self.curs.fetchone()
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
984 return object_type, owner, object_name
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
985
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
986 def resolve(self, identifier):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
987 """Checks (my objects).name, (my synonyms).name, (public synonyms).name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
988 to resolve a database object's name. """
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
989 try:
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
990 return self._resolve(identifier)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
991 except (TypeError, IndexError):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
992 print 'Could not resolve object %s.' % identifier
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
993 return '', '', ''
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
994
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
995 def resolve_with_column(self, identifier):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
996 colName = None
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
997 object_type, owner, object_name = self.resolve(identifier)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
998 if not object_type:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
999 parts = identifier.split('.')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1000 if len(parts) > 1:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1001 colName = parts[-1]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1002 identifier = '.'.join(parts[:-1])
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1003 object_type, owner, object_name = self.resolve(identifier)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1004 return object_type, owner, object_name, colName
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1005
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1006 def do_resolve(self, arg):
195
4a3af9ac215f wow, parsing is broken
catherine@dellzilla
parents: 194
diff changeset
1007 target = arg.upper()
4a3af9ac215f wow, parsing is broken
catherine@dellzilla
parents: 194
diff changeset
1008 self.stdout.write(','.join(self.resolve(target))+'\n')
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1009
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1010 def spoolstop(self):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1011 if self.spoolFile:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1012 self.stdout = self.stdoutBeforeSpool
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1013 print 'Finished spooling to ', self.spoolFile.name
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1014 self.spoolFile.close()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1015 self.spoolFile = None
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1016
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1017 def do_spool(self, arg):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1018 """spool [filename] - begins redirecting output to FILENAME."""
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1019 self.spoolstop()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1020 arg = arg.strip()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1021 if not arg:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1022 arg = 'output.lst'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1023 if arg.lower() != 'off':
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1024 if '.' not in arg:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1025 arg = '%s.lst' % arg
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1026 print 'Sending output to %s (until SPOOL OFF received)' % (arg)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1027 self.spoolFile = open(arg, 'w')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1028 self.stdout = self.spoolFile
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1029
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1030 def do_write(self, args):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1031 print 'Use (query) > outfilename instead.'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1032 return
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1033
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1034 def do_compare(self, args):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1035 """COMPARE query1 TO query2 - uses external tool to display differences.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1036
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1037 Sorting is recommended to avoid false hits.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1038 Will attempt to use a graphical diff/merge tool like kdiff3, meld, or Araxis Merge,
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1039 if they are installed."""
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
1040 #TODO: Update this to use pyparsing
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1041 fnames = []
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1042 args2 = args.split(' to ')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1043 if len(args2) < 2:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1044 print self.do_compare.__doc__
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1045 return
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1046 for n in range(len(args2)):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1047 query = args2[n]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1048 fnames.append('compare%s.txt' % n)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1049 #TODO: update this terminator-stripping
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1050 if query.rstrip()[-1] != self.terminator:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1051 query = '%s%s' % (query, self.terminator)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1052 self.onecmd_plus_hooks('%s > %s' % (query, fnames[n]))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1053 diffMergeSearcher.invoke(fnames[0], fnames[1])
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1054
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1055 bufferPosPattern = re.compile('\d+')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1056 rangeIndicators = ('-',':')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1057
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1058 def do_psql(self, arg):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1059 '''Shortcut commands emulating psql's backslash commands.
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1060
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1061 \c connect
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1062 \d desc
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1063 \e edit
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1064 \g run
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1065 \h help
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1066 \i load
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1067 \o spool
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1068 \p list
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1069 \q quit
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1070 \w save
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1071 \db _dir_tablespaces
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1072 \dd comments
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1073 \dn _dir_schemas
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1074 \dt _dir_tables
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1075 \dv _dir_views
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1076 \di _dir_indexes
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1077 \? help psql'''
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1078 commands = {}
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1079 for c in self.do_psql.__doc__.splitlines()[2:]:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1080 (abbrev, command) = c.split(None, 1)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1081 commands[abbrev[1:]] = command
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1082 words = arg.split(None,1)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1083 try:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1084 abbrev = words[0]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1085 except IndexError:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1086 return
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1087 try:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1088 args = words[1]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1089 except IndexError:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1090 args = ''
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1091 try:
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
1092 return self.onecmd('%s %s%s%s' % (commands[abbrev], args, arg.parsed.terminator, arg.parsed.suffix))
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1093 except KeyError:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1094 print 'psql command \%s not yet supported.' % abbrev
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1095
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
1096 @options([all_users_option])
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1097 def do__dir_tables(self, arg, opts):
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1098 '''
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1099 Lists all tables whose names match argument.
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1100 '''
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
1101 sql = """SELECT table_name, 'TABLE' as type%s FROM %s_tables WHERE table_name LIKE '%%%s%%';""" % \
196
7940955920a8 little fixes
catherine@dellzilla
parents: 195
diff changeset
1102 (opts.scope['col'], opts.scope['view'], arg.upper())
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1103 if self.sql_echo:
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1104 print sql
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
1105 self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
1106
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
1107 @options([all_users_option])
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1108 def do__dir_views(self, arg, opts):
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1109 '''
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1110 Lists all views whose names match argument.
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1111 '''
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
1112 sql = """SELECT view_name, 'VIEW' as type%s FROM %s_views WHERE view_name LIKE '%%%s%%';""" % \
196
7940955920a8 little fixes
catherine@dellzilla
parents: 195
diff changeset
1113 (opts.scope['col'], opts.scope['view'], arg.upper())
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1114 if self.sql_echo:
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1115 print sql
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
1116 self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
1117
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1118 def do__dir_indexes(self, arg):
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1119 '''
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1120 Called with an exact table name, lists the indexes of that table.
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1121 Otherwise, acts as shortcut for `ls index/*(arg)*`
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1122 '''
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1123 try:
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1124 table_type, table_owner, table_name = self._resolve(arg)
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1125 except TypeError, IndexError:
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1126 return self.onecmd('ls Index/*%s*' % arg)
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1127 sql = """SELECT owner, index_name, index_type FROM all_indexes
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1128 WHERE table_owner = :table_owner
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1129 AND table_name = :table_name;
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1130 ORDER BY owner, index_name"""
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1131 self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix),
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1132 bindVarsIn = {'table_owner': table_owner, 'table_name': table_name})
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1133
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1134 def do__dir_tablespaces(self, arg):
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1135 '''
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1136 Lists all tablespaces.
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1137 '''
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
1138 sql = """SELECT tablespace_name, file_name from dba_data_files;"""
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1139 if self.sql_echo:
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1140 print sql
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
1141 self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1142
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1143 def do__dir_schemas(self, arg):
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1144 '''
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1145 Lists all object owners, together with the number of objects they own.
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1146 '''
194
932893dcf0c9 useTerminatorFrom
catherine@dellzilla
parents: 193
diff changeset
1147 sql = """SELECT owner, count(*) AS objects FROM all_objects GROUP BY owner ORDER BY owner;"""
251
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1148 if self.sql_echo:
aa33f495a289 reworked \di - not truly better?
catherine@Elli.myhome.westell.com
parents: 250
diff changeset
1149 print sql
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
1150 self.do_select(self.parsed(sql, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1151
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1152 def do_head(self, arg):
226
6701c3f097f9 more comments
catherine@Elli.myhome.westell.com
parents: 224
diff changeset
1153 '''Shortcut for SELECT * FROM <arg>;10
6701c3f097f9 more comments
catherine@Elli.myhome.westell.com
parents: 224
diff changeset
1154 The terminator (\\t, \\g, \\x, etc.) and number of rows can
6701c3f097f9 more comments
catherine@Elli.myhome.westell.com
parents: 224
diff changeset
1155 be changed as for any other SELECT statement.'''
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
1156 sql = self.parsed('SELECT * FROM %s;' % arg, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix)
195
4a3af9ac215f wow, parsing is broken
catherine@dellzilla
parents: 194
diff changeset
1157 sql.parsed['suffix'] = sql.parsed.suffix or '10'
196
7940955920a8 little fixes
catherine@dellzilla
parents: 195
diff changeset
1158 self.do_select(self.parsed(sql))
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1159
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1160 def do_print(self, arg):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1161 'print VARNAME: Show current value of bind variable VARNAME.'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1162 if arg:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1163 if arg[0] == ':':
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1164 arg = arg[1:]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1165 try:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1166 self.stdout.write(str(self.binds[arg])+'\n')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1167 except KeyError:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1168 self.stdout.write('No bind variable %s\n' % arg)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1169 else:
250
aec778ef82b6 print full help on -h
catherine@Elli.myhome.westell.com
parents: 249
diff changeset
1170 for (var, val) in sorted(self.binds.items()):
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1171 print ':%s = %s' % (var, val)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1172
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1173 assignmentScanner = Parser(pyparsing.Literal(':=') ^ '=')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1174 def do_setbind(self, arg):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1175 if not arg:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1176 return self.do_print(arg)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1177 try:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1178 assigner, startat, endat = self.assignmentScanner.scanner.scanString(arg).next()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1179 except StopIteration:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1180 self.do_print(arg)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1181 return
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1182 var, val = arg[:startat].strip(), arg[endat:].strip()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1183 if val[0] == val[-1] == "'" and len(val) > 1:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1184 self.binds[var] = val[1:-1]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1185 return
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1186 try:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1187 self.binds[var] = int(val)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1188 return
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1189 except ValueError:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1190 try:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1191 self.binds[var] = float(val)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1192 return
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1193 except ValueError:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1194 statekeeper = Statekeeper(self, ('autobind',))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1195 self.autobind = True
193
01548a399ccf big switch to ParsedString
catherine@dellzilla
parents: 192
diff changeset
1196 self.onecmd('SELECT %s AS %s FROM dual;' % (val, var))
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1197 statekeeper.restore()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1198
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1199 def do_exec(self, arg):
213
a3eeea9ee8cc synched with cmd2 0.4.5
catherine@Elli.myhome.westell.com
parents: 211
diff changeset
1200 if arg.startswith(':'):
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1201 self.do_setbind(arg[1:])
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1202 else:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1203 varsUsed = findBinds(arg, self.binds, {})
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1204 try:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1205 self.curs.execute('begin\n%s;end;' % arg, varsUsed)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1206 except Exception, e:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1207 print e
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1208
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1209 '''
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1210 Fails:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1211 select n into :n from test;'''
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1212
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1213 def anon_plsql(self, line1):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1214 lines = [line1]
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1215 while True:
247
f0f293d83337 begin docs
catherine@dellzilla
parents: 246
diff changeset
1216 line = self.pseudo_raw_input(self.continuation_prompt)
241
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
1217 if line == 'EOF':
d1f1022d2387 trying remark_begin
catherine@Elli.myhome.westell.com
parents: 240
diff changeset
1218 return
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1219 if line.strip() == '/':
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1220 try:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1221 self.curs.execute('\n'.join(lines))
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1222 except Exception, e:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1223 print e
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1224 return
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1225 lines.append(line)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1226
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1227 def do_begin(self, arg):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1228 self.anon_plsql('begin ' + arg)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1229
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1230 def do_declare(self, arg):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1231 self.anon_plsql('declare ' + arg)
216
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1232
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1233 def _ls_statement(self, arg, opts):
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1234 if arg:
217
a65b98938596 multi-pull working pretty well
catherine@Elli.myhome.westell.com
parents: 216
diff changeset
1235 target = arg.upper()
232
52adb09094b3 fixed bugs in VC introduced by sort-order options
catherine@dellzilla
parents: 230
diff changeset
1236 if hasattr(opts, 'exact') and opts.exact:
217
a65b98938596 multi-pull working pretty well
catherine@Elli.myhome.westell.com
parents: 216
diff changeset
1237 where = """\nWHERE object_name = '%s'
a65b98938596 multi-pull working pretty well
catherine@Elli.myhome.westell.com
parents: 216
diff changeset
1238 OR object_type || '/' || object_name = '%s'""" % \
a65b98938596 multi-pull working pretty well
catherine@Elli.myhome.westell.com
parents: 216
diff changeset
1239 (target, target)
a65b98938596 multi-pull working pretty well
catherine@Elli.myhome.westell.com
parents: 216
diff changeset
1240 else:
264
a8deaa38f11e show errors works. limiting ls
catherine@Elli.myhome.westell.com
parents: 261
diff changeset
1241 where = "\nWHERE object_type || '/' || object_name LIKE '%s'" % (arg.upper().replace('*','%'))
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1242 else:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1243 where = ''
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1244 if opts.all:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1245 whose = 'all'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1246 objname = "owner || '.' || object_name"
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1247 else:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1248 whose = 'user'
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1249 objname = 'object_name'
232
52adb09094b3 fixed bugs in VC introduced by sort-order options
catherine@dellzilla
parents: 230
diff changeset
1250 if hasattr(opts, 'long') and opts.long:
228
84905120d6c7 last_ddl_time is proper label
catherine@dellzilla
parents: 227
diff changeset
1251 moreColumns = ', status, last_ddl_time'
216
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1252 else:
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1253 moreColumns = ''
227
5cfa3eff8e4b time and reverse sorting added to ls
catherine@dellzilla
parents: 226
diff changeset
1254
5cfa3eff8e4b time and reverse sorting added to ls
catherine@dellzilla
parents: 226
diff changeset
1255 # 'Normal' sort order is DATE DESC (maybe), object type ASC, object name ASC
244
a7bc7da97a81 keeping python 2.4 compatibility
catherine@dellzilla
parents: 242
diff changeset
1256 sortdirection = (hasattr(opts, 'reverse') and opts.reverse and 'DESC') or 'ASC'
227
5cfa3eff8e4b time and reverse sorting added to ls
catherine@dellzilla
parents: 226
diff changeset
1257 orderby = 'object_type %s, object_name %s' % (sortdirection, sortdirection)
232
52adb09094b3 fixed bugs in VC introduced by sort-order options
catherine@dellzilla
parents: 230
diff changeset
1258 if hasattr(opts, 'timesort') and opts.timesort:
52adb09094b3 fixed bugs in VC introduced by sort-order options
catherine@dellzilla
parents: 230
diff changeset
1259 orderby = 'last_ddl_time %s, %s' % (('ASC' if hasattr(opts, 'reverse') and opts.reverse else 'DESC'), orderby)
216
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1260 return {'objname': objname, 'moreColumns': moreColumns,
227
5cfa3eff8e4b time and reverse sorting added to ls
catherine@dellzilla
parents: 226
diff changeset
1261 'whose': whose, 'where': where, 'orderby': orderby}
216
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1262
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1263 def resolve_many(self, arg, opts):
217
a65b98938596 multi-pull working pretty well
catherine@Elli.myhome.westell.com
parents: 216
diff changeset
1264 opts.long = False
216
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1265 clauses = self._ls_statement(arg, opts)
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1266 if opts.all:
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1267 clauses['owner'] = 'owner'
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1268 else:
216
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1269 clauses['owner'] = 'user'
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1270 statement = '''SELECT %(owner)s, object_type, object_name
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1271 FROM %(whose)s_objects %(where)s
217
a65b98938596 multi-pull working pretty well
catherine@Elli.myhome.westell.com
parents: 216
diff changeset
1272 ORDER BY object_type, object_name''' % clauses
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1273 self._execute(statement)
216
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1274 return self.curs.fetchall()
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1275
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1276 @options([make_option('-l', '--long', action='store_true', help='long descriptions'),
217
a65b98938596 multi-pull working pretty well
catherine@Elli.myhome.westell.com
parents: 216
diff changeset
1277 make_option('-a', '--all', action='store_true', help="all schemas' objects"),
227
5cfa3eff8e4b time and reverse sorting added to ls
catherine@dellzilla
parents: 226
diff changeset
1278 make_option('-t', '--timesort', action='store_true', help="Sort by last_ddl_time"),
5cfa3eff8e4b time and reverse sorting added to ls
catherine@dellzilla
parents: 226
diff changeset
1279 make_option('-r', '--reverse', action='store_true', help="Reverse order while sorting"),
217
a65b98938596 multi-pull working pretty well
catherine@Elli.myhome.westell.com
parents: 216
diff changeset
1280 make_option('-x', '--exact', action='store_true', default=False, help="match name exactly")])
216
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1281 def do_ls(self, arg, opts):
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1282 statement = '''SELECT object_type || '/' || %(objname)s AS name %(moreColumns)s
c5a49947eedc going to try multiple pull
catherine@Elli.myhome.westell.com
parents: 213
diff changeset
1283 FROM %(whose)s_objects %(where)s
227
5cfa3eff8e4b time and reverse sorting added to ls
catherine@dellzilla
parents: 226
diff changeset
1284 ORDER BY %(orderby)s;''' % self._ls_statement(arg, opts)
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
1285 self.do_select(self.parsed(statement, terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix))
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1286
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1287 @options([make_option('-i', '--ignore-case', dest='ignorecase', action='store_true', help='Case-insensitive search')])
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1288 def do_grep(self, arg, opts):
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1289 """grep PATTERN TABLE - search for term in any of TABLE's fields"""
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1290
195
4a3af9ac215f wow, parsing is broken
catherine@dellzilla
parents: 194
diff changeset
1291 targetnames = arg.split()
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1292 pattern = targetnames.pop(0)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1293 targets = []
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1294 for target in targetnames:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1295 if '*' in target:
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1296 self._execute("SELECT owner, table_name FROM all_tables WHERE table_name LIKE '%s'%s" %
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1297 (target.upper().replace('*','%')), arg.terminator)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1298 for row in self.curs:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1299 targets.append('%s.%s' % row)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1300 else:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1301 targets.append(target)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1302 for target in targets:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1303 print target
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1304 target = target.rstrip(';')
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1305 try:
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1306 self._execute('select * from %s where 1=0' % target) # just to fill description
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1307 if opts.ignorecase:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1308 sql = ' or '.join("LOWER(%s) LIKE '%%%s%%'" % (d[0], pattern.lower()) for d in self.curs.description)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1309 else:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1310 sql = ' or '.join("%s LIKE '%%%s%%'" % (d[0], pattern) for d in self.curs.description)
200
54cd1e802fa0 terminators + suffixes now preserved
catherine@dellzilla
parents: 199
diff changeset
1311 sql = self.parsed('SELECT * FROM %s WHERE %s;' % (target, sql), terminator=arg.parsed.terminator or ';', suffix=arg.parsed.suffix)
199
09592342a33d ugh - parsing stripping command causes real trouble
catherine@dellzilla
parents: 198
diff changeset
1312 self.do_select(sql)
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1313 except Exception, e:
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1314 print e
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1315 import traceback
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1316 traceback.print_exc(file=sys.stdout)
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1317
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1318 def _execute(self, sql, bindvars={}):
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1319 if self.sql_echo:
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1320 print sql
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1321 self.curs.execute(sql, bindvars)
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1322
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1323 def do_refs(self, arg):
211
catherine@Elli.myhome.westell.com
parents: 210
diff changeset
1324 '''Lists referential integrity (foreign key constraints) on an object.'''
catherine@Elli.myhome.westell.com
parents: 210
diff changeset
1325
catherine@Elli.myhome.westell.com
parents: 210
diff changeset
1326 if not arg.strip():
catherine@Elli.myhome.westell.com
parents: 210
diff changeset
1327 print 'Usage: refs (table name)'
190
e9d0492d7358 changing refs
catherine@dellzilla
parents: 189
diff changeset
1328 result = []
205
catherine@dellzilla
parents: 204
diff changeset
1329 (type, owner, table_name) = self.resolve(arg.upper())
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1330 sql = """SELECT constraint_name, r_owner, r_constraint_name
191
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1331 FROM all_constraints
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1332 WHERE constraint_type = 'R'
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1333 AND owner = :owner
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1334 AND table_name = :table_name"""
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1335 self._execute(sql, {"owner": owner, "table_name": table_name})
191
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1336 for (constraint_name, remote_owner, remote_constraint_name) in self.curs.fetchall():
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1337 result.append('%s on %s.%s:' % (constraint_name, owner, table_name))
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1338
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1339 self._execute("SELECT column_name FROM all_cons_columns WHERE owner = :owner AND constraint_name = :constraint_name ORDER BY position",
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1340 {'constraint_name': constraint_name, 'owner': owner})
191
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1341 result.append(" (%s)" % (",".join(col[0] for col in self.curs.fetchall())))
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1342 self._execute("SELECT table_name FROM all_constraints WHERE owner = :remote_owner AND constraint_name = :remote_constraint_name",
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1343 {'remote_owner': remote_owner, 'remote_constraint_name': remote_constraint_name})
191
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1344 remote_table_name = self.curs.fetchone()[0]
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1345 result.append("must be in %s:" % (remote_table_name))
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1346 self._execute("SELECT column_name FROM all_cons_columns WHERE owner = :remote_owner AND constraint_name = :remote_constraint_name ORDER BY position",
191
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1347 {'remote_constraint_name': remote_constraint_name, 'remote_owner': remote_owner})
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1348 result.append(' (%s)\n' % (",".join(col[0] for col in self.curs.fetchall())))
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1349 remote_table_name = table_name
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1350 remote_owner = owner
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1351 self._execute("""SELECT owner, constraint_name, table_name, r_constraint_name
191
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1352 FROM all_constraints
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1353 WHERE (r_owner, r_constraint_name) IN
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1354 ( SELECT owner, constraint_name
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1355 FROM all_constraints
192
6bb8a112af6b accept special terminators on most anything
catherine@dellzilla
parents: 191
diff changeset
1356 WHERE table_name = :remote_table_name
191
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1357 AND owner = :remote_owner )""",
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1358 {'remote_table_name': remote_table_name, 'remote_owner': remote_owner})
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1359 for (owner, constraint_name, table_name, remote_constraint_name) in self.curs.fetchall():
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1360 result.append('%s on %s.%s:' % (constraint_name, owner, table_name))
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1361 self._execute("SELECT column_name FROM all_cons_columns WHERE owner = :owner AND constraint_name = :constraint_name ORDER BY position",
191
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1362 {'constraint_name': constraint_name, 'owner': owner})
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1363 result.append(" (%s)" % (",".join(col[0] for col in self.curs.fetchall())))
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1364 self._execute("SELECT table_name FROM all_constraints WHERE owner = :remote_owner AND constraint_name = :remote_constraint_name",
191
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1365 {'remote_owner': remote_owner, 'remote_constraint_name': remote_constraint_name})
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1366 remote_table_name = self.curs.fetchone()[0]
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1367 result.append("must be in %s:" % (remote_table_name))
249
9e3e49c95abf added sql_echo
catherine@Elli.myhome.westell.com
parents: 248
diff changeset
1368 self._execute("SELECT column_name FROM all_cons_columns WHERE owner = :remote_owner AND constraint_name = :remote_constraint_name ORDER BY position",
191
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1369 {'remote_constraint_name': remote_constraint_name, 'remote_owner': remote_owner})
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1370 result.append(' (%s)\n' % (",".join(col[0] for col in self.curs.fetchall())))
eca4361bfdb6 improved refs
catherine@dellzilla
parents: 190
diff changeset
1371 self.stdout.write('\n'.join(result) + "\n")
190
e9d0492d7358 changing refs
catherine@dellzilla
parents: 189
diff changeset
1372
189
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1373 def _test():
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1374 import doctest
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1375 doctest.testmod()
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1376
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1377 if __name__ == "__main__":
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1378 "Silent return implies that all unit tests succeeded. Use -v to see details."
c5398d87498e cat bug
catherine@dellzilla
parents:
diff changeset
1379 _test()
198
b2d8bf5f89db merged with changes from work
catherine@Elli.myhome.westell.com
parents: 196
diff changeset
1380 if __name__ == "__main__":
b2d8bf5f89db merged with changes from work
catherine@Elli.myhome.westell.com
parents: 196
diff changeset
1381 "Silent return implies that all unit tests succeeded. Use -v to see details."
b2d8bf5f89db merged with changes from work
catherine@Elli.myhome.westell.com
parents: 196
diff changeset
1382 _test()