annotate sqlpyPlus.py @ 2:59903dcaf327

working on history
author devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
date Mon, 03 Dec 2007 14:29:16 -0500
parents 8fa146b9a2d7
children cd23cd62de3c
rev   line source
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
1 """sqlpyPlus - extra features (inspired by Oracle SQL*Plus) for Luca Canali's sqlpython.py
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
2
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
3 Features include:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
4 - SQL*Plus-style bind variables
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
5 - Query result stored in special bind variable ":_" if one row, one item
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
6 - SQL buffer with list, run, ed, get, etc.; unlike SQL*Plus, buffer stores session's full history
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
7 - @script.sql loads and runs (like SQL*Plus)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
8 - ! runs operating-system command
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
9 - show and set to control sqlpython parameters
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
10 - SQL*Plus-style describe, spool
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
11 - write sends query result directly to file
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
12 - comments shows table and column comments
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
13 - compare ... to ... graphically compares results of two queries
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
14 - commands are case-insensitive
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
15
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
16 Use 'help' within sqlpython for details.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
17
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
18 Compatible with sqlpython v1.3
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
19
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
20 Set bind variables the hard (SQL*Plus) way
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
21 exec :b = 3
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
22 or with a python-like shorthand
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
23 :b = 3
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
24
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
25 - catherinedevlin.blogspot.com May 31, 2006
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
26 """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
27 # note in cmd.cmd about supporting emacs commands?
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
28
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
29 queries = {
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
30 'resolve': """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
31 SELECT object_type, object_name, owner FROM (
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
32 SELECT object_type, object_name, user owner, 1 priority
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
33 FROM user_objects
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
34 WHERE object_name = :objName
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
35 UNION ALL
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
36 SELECT ao.object_type, ao.object_name, ao.owner, 2 priority
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
37 FROM all_objects ao
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
38 JOIN user_synonyms us ON (us.table_owner = ao.owner AND us.table_name = ao.object_name)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
39 WHERE us.synonym_name = :objName
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
40 AND ao.object_type != 'SYNONYM'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
41 UNION ALL
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
42 SELECT ao.object_type, ao.object_name, ao.owner, 3 priority
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
43 FROM all_objects ao
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
44 JOIN all_synonyms asyn ON (asyn.table_owner = ao.owner AND asyn.table_name = ao.object_name)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
45 WHERE asyn.synonym_name = :objName
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
46 AND ao.object_type != 'SYNONYM'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
47 AND asyn.owner = 'PUBLIC'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
48 ) ORDER BY priority ASC""",
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
49 'descTable': """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
50 atc.column_name,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
51 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
52 atc.data_type ||
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
53 CASE atc.data_type WHEN 'DATE' THEN ''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
54 ELSE '(' ||
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
55 CASE atc.data_type WHEN 'NUMBER' THEN TO_CHAR(atc.data_precision) ||
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
56 CASE atc.data_scale WHEN 0 THEN ''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
57 ELSE ',' || TO_CHAR(atc.data_scale) END
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
58 ELSE TO_CHAR(atc.data_length) END
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
59 END ||
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
60 CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
61 data_type
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
62 FROM all_tab_columns atc
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
63 WHERE atc.table_name = :object_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
64 AND atc.owner = :owner
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
65 ORDER BY atc.column_id;""",
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
66 'PackageObjects':"""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
67 SELECT DISTINCT object_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
68 FROM all_arguments
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
69 WHERE package_name = :package_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
70 AND owner = :owner""",
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
71 'PackageObjArgs':"""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
72 object_name,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
73 argument_name,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
74 data_type,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
75 in_out,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
76 default_value
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
77 FROM all_arguments
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
78 WHERE package_name = :package_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
79 AND object_name = :object_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
80 AND owner = :owner
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
81 AND argument_name IS NOT NULL
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
82 ORDER BY sequence""",
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
83 'descProcedure':"""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
84 argument_name,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
85 data_type,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
86 in_out,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
87 default_value
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
88 FROM all_arguments
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
89 WHERE object_name = :object_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
90 AND owner = :owner
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
91 AND package_name IS NULL
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
92 AND argument_name IS NOT NULL
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
93 ORDER BY sequence;""",
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
94 'tabComments': """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
95 SELECT comments
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
96 FROM all_tab_comments
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
97 WHERE owner = :owner
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
98 AND table_name = :table_name""",
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
99 'colComments': """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
100 atc.column_name,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
101 acc.comments
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
102 FROM all_tab_columns atc
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
103 JOIN all_col_comments acc ON (atc.owner = acc.owner and atc.table_name = acc.table_name and atc.column_name = acc.column_name)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
104 WHERE atc.table_name = :object_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
105 AND atc.owner = :owner
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
106 ORDER BY atc.column_id;""",
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
107 }
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
108
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
109 import sys, os, re, sqlpython, cx_Oracle, pyparsing
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
110
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
111 if float(sys.version[:3]) < 2.3:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
112 def enumerate(lst):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
113 return zip(range(len(lst)), lst)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
114
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
115 class SoftwareSearcher(object):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
116 def __init__(self, softwareList, purpose):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
117 self.softwareList = softwareList
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
118 self.purpose = purpose
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
119 self.software = None
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
120 def invoke(self, *args):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
121 if not self.software:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
122 (self.software, self.invokeString) = self.find()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
123 argTuple = tuple([self.software] + list(args))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
124 os.system(self.invokeString % argTuple)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
125 def find(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
126 if self.purpose == 'text editor':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
127 software = os.environ.get('EDITOR')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
128 if software:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
129 return (software, '%s %s')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
130 for (n, (software, invokeString)) in enumerate(self.softwareList):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
131 if os.path.exists(software):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
132 if n > (len(self.softwareList) * 0.7):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
133 print """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
134
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
135 Using %s. Note that there are better options available for %s,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
136 but %s couldn't find a better one in your PATH.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
137 Feel free to open up %s
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
138 and customize it to find your favorite %s program.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
139
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
140 """ % (software, self.purpose, __file__, __file__, self.purpose)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
141 return (software, invokeString)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
142 stem = os.path.split(software)[1]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
143 for p in os.environ['PATH'].split(os.pathsep):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
144 if os.path.exists(os.sep.join([p, stem])):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
145 return (stem, invokeString)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
146 raise (OSError, """Could not find any %s programs. You will need to install one,
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
147 or customize %s to make it aware of yours.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
148 Looked for these programs:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
149 %s""" % (self.purpose, __file__, "\n".join([s[0] for s in self.softwareList])))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
150 #v2.4: %s""" % (self.purpose, __file__, "\n".join(s[0] for s in self.softwareList)))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
151
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
152 softwareLists = {
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
153 'diff/merge': [
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
154 ('/usr/bin/meld',"%s %s %s"),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
155 ('/usr/bin/kdiff3',"%s %s %s"),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
156 (r'C:\Program Files\Araxis\Araxis Merge v6.5\Merge.exe','"%s" %s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
157 (r'C:\Program Files\TortoiseSVN\bin\TortoiseMerge.exe', '"%s" /base:"%s" /mine:"%s"'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
158 ('FileMerge','%s %s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
159 ('kompare','%s %s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
160 ('WinMerge','%s %s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
161 ('xxdiff','%s %s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
162 ('fldiff','%s %s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
163 ('gtkdiff','%s %s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
164 ('tkdiff','%s %s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
165 ('gvimdiff','%s %s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
166 ('diff',"%s %s %s"),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
167 (r'c:\windows\system32\comp.exe',"%s %s %s")],
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
168 'text editor': [
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
169 ('gedit', '%s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
170 ('textpad', '%s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
171 ('notepad.exe', '%s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
172 ('pico', '%s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
173 ('emacs', '%s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
174 ('vim', '%s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
175 ('vi', '%s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
176 ('ed', '%s %s'),
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
177 ('edlin', '%s %s')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
178 ]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
179 }
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
180
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
181 diffMergeSearcher = SoftwareSearcher(softwareLists['diff/merge'],'diff/merge')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
182 editSearcher = SoftwareSearcher(softwareLists['text editor'], 'text editor')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
183 editor = os.environ.get('EDITOR')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
184 if editor:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
185 editSearcher.find = lambda: (editor, "%s %s")
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
186
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
187 class CaselessDict(dict):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
188 """dict with case-insensitive keys.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
189
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
190 Posted to ASPN Python Cookbook by Jeff Donner - http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/66315"""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
191 def __init__(self, other=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
192 if other:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
193 # Doesn't do keyword args
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
194 if isinstance(other, dict):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
195 for k,v in other.items():
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
196 dict.__setitem__(self, k.lower(), v)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
197 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
198 for k,v in other:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
199 dict.__setitem__(self, k.lower(), v)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
200 def __getitem__(self, key):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
201 return dict.__getitem__(self, key.lower())
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
202 def __setitem__(self, key, value):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
203 dict.__setitem__(self, key.lower(), value)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
204 def __contains__(self, key):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
205 return dict.__contains__(self, key.lower())
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
206 def has_key(self, key):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
207 return dict.has_key(self, key.lower())
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
208 def get(self, key, def_val=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
209 return dict.get(self, key.lower(), def_val)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
210 def setdefault(self, key, def_val=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
211 return dict.setdefault(self, key.lower(), def_val)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
212 def update(self, other):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
213 for k,v in other.items():
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
214 dict.__setitem__(self, k.lower(), v)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
215 def fromkeys(self, iterable, value=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
216 d = CaselessDict()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
217 for k in iterable:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
218 dict.__setitem__(d, k.lower(), value)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
219 return d
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
220 def pop(self, key, def_val=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
221 return dict.pop(self, key.lower(), def_val)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
222
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
223 class NotSettableError(Exception):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
224 None
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
225
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
226 class Parser(object):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
227 comment_def = "--" + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n"))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
228 def __init__(self, scanner, retainSeparator=True):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
229 self.scanner = scanner
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
230 self.scanner.ignore(pyparsing.sglQuotedString)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
231 self.scanner.ignore(pyparsing.dblQuotedString)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
232 self.scanner.ignore(self.comment_def)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
233 self.scanner.ignore(pyparsing.cStyleComment)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
234 self.retainSeparator = retainSeparator
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
235 def separate(self, txt):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
236 itms = []
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
237 for (sqlcommand, start, end) in self.scanner.scanString(txt):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
238 if sqlcommand:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
239 if type(sqlcommand[0]) == pyparsing.ParseResults:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
240 if self.retainSeparator:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
241 itms.append("".join(sqlcommand[0]))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
242 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
243 itms.append(sqlcommand[0][0])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
244 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
245 if sqlcommand[0]:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
246 itms.append(sqlcommand[0])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
247 return itms
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
248
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
249 pipeSeparator = Parser(pyparsing.SkipTo((pyparsing.Literal('|') ^ pyparsing.StringEnd()), include=True), retainSeparator=False)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
250 bindScanner = Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" ))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
251 commandSeparator = Parser(pyparsing.SkipTo((pyparsing.Literal(';') ^ pyparsing.StringEnd()), include=True))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
252
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
253 def findBinds(target, existingBinds, givenBindVars = {}):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
254 result = givenBindVars
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
255 for finding, startat, endat in bindScanner.scanner.scanString(target):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
256 varname = finding[1]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
257 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
258 result[varname] = existingBinds[varname]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
259 except KeyError:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
260 if not givenBindVars.has_key(varname):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
261 print 'Bind variable %s not defined.' % (varname)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
262 return result
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
263
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
264 class sqlpyPlus(sqlpython.sqlpython):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
265 def __init__(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
266 sqlpython.sqlpython.__init__(self)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
267 self.binds = CaselessDict()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
268 self.sqlBuffer = []
1
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
269 self.history = []
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
270 self.settable = ['maxtselctrows', 'maxfetch', 'autobind', 'failover', 'timeout'] # settables must be lowercase
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
271 self.stdoutBeforeSpool = sys.stdout
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
272 self.spoolFile = None
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
273 self.autobind = False
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
274 self.failover = False
2
59903dcaf327 working on history
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 1
diff changeset
275 self.singleline = '''select insert update delete
59903dcaf327 working on history
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 1
diff changeset
276 create drop alter'''.split()
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
277
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
278 def default(self, arg, do_everywhere=False):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
279 sqlpython.sqlpython.default(self, arg, do_everywhere)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
280 self.sqlBuffer.append(self.query)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
281
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
282 # overrides cmd's parseline
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
283 shortcuts = {'?': 'help', '@': 'getrun', '!': 'shell', ':': 'setbind', '\\': 'psql'}
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
284 def parseline(self, line):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
285 """Parse the line into a command name and a string containing
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
286 the arguments. Returns a tuple containing (command, args, line).
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
287 'command' and 'args' may be None if the line couldn't be parsed.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
288 Overrides cmd.cmd.parseline to accept variety of shortcuts.."""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
289 line = line.strip()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
290 if not line:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
291 return None, None, line
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
292 shortcut = self.shortcuts.get(line[0])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
293 if shortcut:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
294 cmd, arg = shortcut, line[1:].strip()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
295 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
296 i, n = 0, len(line)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
297 while i < n and line[i] in self.identchars: i = i+1
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
298 cmd, arg = line[:i], line[i:].strip()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
299 if cmd.lower() in ('select', 'sleect', 'insert', 'update', 'delete', 'describe',
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
300 'desc', 'comments') \
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
301 and not hasattr(self, 'curs'):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
302 print 'Not connected.'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
303 return '', '', ''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
304 return cmd, arg, line
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
305
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
306 def precmd(self, line):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
307 """Hook method executed just before the command line is
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
308 interpreted, but after the input prompt is generated and issued.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
309 Makes commands case-insensitive (but unfortunately does not alter command completion).
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
310 """
1
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
311 '''
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
312 savestdout = sys.stdout
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
313 pipefilename = 'sqlpython.pipeline.tmp'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
314 pipedCommands = pipeSeparator.separate(line)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
315 if len(pipedCommands) > 1:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
316 f = open(pipefilename,'w')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
317 sys.stdout = f
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
318 self.precmd(pipedCommands[0])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
319 self.onecmd(pipedCommands[0])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
320 self.postcmd(False, pipedCommands[0])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
321 f.close()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
322 sys.stdout = savestdout
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
323 os.system('%s < %s' % (pipedCommands[1], pipefilename))
1
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
324 '''
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
325 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
326 args = line.split(None,1)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
327 args[0] = args[0].lower()
2
59903dcaf327 working on history
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 1
diff changeset
328 statement = ' '.join(args)
59903dcaf327 working on history
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 1
diff changeset
329 if args[0] in self.singleline:
59903dcaf327 working on history
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 1
diff changeset
330 statement = sqlpython.finishStatement(statement)
1
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
331 return statement
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
332 except Exception:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
333 return line
1
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
334
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
335 def do_shortcuts(self,arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
336 """Lists available first-character shortcuts
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
337 (i.e. '!dir' is equivalent to 'shell dir')"""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
338 for (scchar, scto) in self.shortcuts.items():
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
339 print '%s: %s' % (scchar, scto)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
340
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
341
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
342 def colnames(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
343 return [d[0] for d in curs.description]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
344
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
345 def sql_format_itm(self, itm, needsquotes):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
346 if itm is None:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
347 return 'NULL'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
348 if needsquotes:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
349 return "'%s'" % str(itm)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
350 return str(itm)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
351 def output_as_insert_statements(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
352 usequotes = [d[1] != cx_Oracle.NUMBER for d in self.curs.description]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
353 def formatRow(row):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
354 return ','.join(self.sql_format_itm(itm, useq)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
355 for (itm, useq) in zip(row, usequotes))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
356 result = ['INSERT INTO %s (%s) VALUES (%s);' %
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
357 (self.tblname, ','.join(self.colnames), formatRow(row))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
358 for row in self.rows]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
359 return '\n'.join(result)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
360
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
361 def output_row_as_xml(self, row):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
362 result = [' <%s>\n %s\n </%s>' %
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
363 (colname.lower(), str('' if (itm is None) else itm), colname.lower())
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
364 for (itm, colname) in zip(row, self.colnames)]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
365 return '\n'.join(result)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
366 def output_as_xml(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
367 result = ['<%s>\n%s\n</%s>' %
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
368 (self.tblname, self.output_row_as_xml(row), self.tblname)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
369 for row in self.rows]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
370 return '\n'.join(result)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
371
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
372 def output_as_html_table(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
373 result = ''.join('<th>%s</th>' % c for c in self.colnames)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
374 result = [' <tr>\n %s\n </tr>' % result]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
375 for row in self.rows:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
376 result.append(' <tr>\n %s\n </tr>' %
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
377 (''.join('<td>%s</td>' %
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
378 str('' if (itm is None) else itm)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
379 for itm in row)))
1
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
380 result = '''<table id="%s">
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
381 %s
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
382 </table>''' % (self.tblname, '\n'.join(result))
1
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
383 return result
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
384
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
385 def output_as_list(self, align):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
386 result = []
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
387 colnamelen = max(len(colname) for colname in self.colnames) + 1
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
388 for (idx, row) in enumerate(self.rows):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
389 result.append('\n**** Row: %d' % (idx+1))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
390 for (itm, colname) in zip(row, self.colnames):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
391 if align:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
392 colname = colname.ljust(colnamelen)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
393 result.append('%s: %s' % (colname, itm))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
394 return '\n'.join(result)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
395
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
396 tableNameFinder = re.compile(r'from\s+([\w$#_"]+)', re.IGNORECASE | re.MULTILINE | re.DOTALL)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
397 def output(self, outformat, rowlimit):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
398 self.tblname = self.tableNameFinder.search(self.curs.statement).group(1)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
399 self.colnames = [d[0] for d in self.curs.description]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
400 if outformat == '\\i':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
401 result = self.output_as_insert_statements()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
402 elif outformat == '\\x':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
403 result = self.output_as_xml()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
404 elif outformat == '\\g':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
405 result = self.output_as_list(align=False)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
406 elif outformat == '\\G':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
407 result = self.output_as_list(align=True)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
408 elif outformat in ('\\s', '\\S', '\\c', '\\C'): #csv
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
409 result = []
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
410 if outformat in ('\\s', '\\c'):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
411 result.append(','.join('"%s"' % colname for colname in self.colnames))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
412 for row in self.rows:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
413 result.append(','.join('"%s"' % ('' if itm is None else itm) for itm in row))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
414 result = '\n'.join(result)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
415 elif outformat == '\\h':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
416 result = self.output_as_html_table()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
417 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
418 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
419 return result
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
420
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
421 def do_select(self, arg, bindVarsIn=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
422 """Fetch rows from a table.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
423
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
424 Limit the number of rows retrieved by appending
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
425 an integer after the terminator
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
426 (example: SELECT * FROM mytable;10 )
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
427
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
428 Output may be formatted by choosing an alternative terminator
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
429 ("help terminators" for details)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
430 """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
431 bindVarsIn = bindVarsIn or {}
1
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
432 self.query = 'select ' + arg
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
433 (self.query, terminator, rowlimit) = sqlpython.findTerminator(self.query)
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
434 rowlimit = int(rowlimit or 0)
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
435 if terminator == '\\t':
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
436 self.do_tselect(' '.join(self.query.split()[1:]) + ';', rowlimit)
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
437 return
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
438 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
439 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
440 self.varsUsed = findBinds(self.query, self.binds, bindVarsIn)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
441 self.curs.execute(self.query, self.varsUsed)
2
59903dcaf327 working on history
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 1
diff changeset
442 self.rows = self.curs.fetchmany(min(self.maxfetch, (rowlimit or self.maxfetch)))
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
443 self.desc = self.curs.description
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
444 self.rc = self.curs.rowcount
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
445 if self.rc > 0:
2
59903dcaf327 working on history
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 1
diff changeset
446 print '\n' + self.output(terminator, rowlimit)
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
447 if self.rc == 0:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
448 print '\nNo rows Selected.\n'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
449 elif self.rc == 1:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
450 print '\n1 row selected.\n'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
451 if self.autobind:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
452 self.binds.update(dict(zip([d[0] for d in self.desc], self.rows[0])))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
453 elif self.rc < self.maxfetch:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
454 print '\n%d rows selected.\n' % self.rc
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
455 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
456 print '\nSelected Max Num rows (%d)' % self.rc
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
457 except Exception, e:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
458 print e
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
459 import traceback
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
460 traceback.print_exc(file=sys.stdout)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
461 self.sqlBuffer.append(self.query)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
462
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
463 def showParam(self, param):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
464 param = param.strip().lower()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
465 if param in self.settable:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
466 val = getattr(self, param)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
467 print '%s: %s' % (param, str(getattr(self, param)))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
468
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
469 def do_show(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
470 'Shows value of a (sqlpython, not ORACLE) parameter'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
471 arg = arg.strip().lower()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
472 if arg:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
473 self.showParam(arg)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
474 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
475 for param in self.settable:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
476 self.showParam(param)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
477
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
478 def cast(self, current, new):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
479 typ = type(current)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
480 if typ == bool:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
481 new = new.lower()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
482 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
483 if (new=='on') or (new[0] in ('y','t')):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
484 return True
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
485 return False
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
486 except TypeError:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
487 None
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
488 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
489 return typ(new)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
490 except:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
491 print "Problem setting parameter (now %s) to %s; incorrect type?" % (current, new)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
492 return current
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
493
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
494 def do_set(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
495 'Sets a (sqlpython, not ORACLE) parameter'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
496 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
497 paramName, val = arg.split(None, 1)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
498 except Exception:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
499 self.do_show(arg)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
500 return
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
501 paramName = paramName.lower()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
502 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
503 current = getattr(self, paramName)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
504 if callable(current):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
505 raise NotSettableError
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
506 except (AttributeError, NotSettableError):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
507 self.fail('set %s' % arg)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
508 return
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
509 val = self.cast(current, val.strip(';'))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
510 print paramName, ' - was: ', current
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
511 setattr(self, paramName.lower(), val)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
512 print 'now: ', val
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
513
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
514 def do_describe(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
515 "emulates SQL*Plus's DESCRIBE"
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
516 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
517 print "%s %s.%s" % (object_type, owner, object_name)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
518 if object_type in ('TABLE','VIEW'):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
519 self.do_select(queries['descTable'],{'object_name':object_name, 'owner':owner})
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
520 elif object_type == 'PACKAGE':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
521 self.curs.execute(queries['PackageObjects'], {'package_name':object_name, 'owner':owner})
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
522 for (packageObj_name,) in self.curs:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
523 print packageObj_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
524 self.do_select(queries['PackageObjArgs'],{'package_name':object_name, 'owner':owner, 'object_name':packageObj_name})
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
525 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
526 self.do_select(queries['descProcedure'],{'owner':owner, 'object_name':object_name})
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
527 do_desc = do_describe
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
528
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
529 def do_comments(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
530 'Prints comments on a table and its columns.'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
531 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
532 if object_type:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
533 self.curs.execute(queries['tabComments'],{'table_name':object_name, 'owner':owner})
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
534 print "%s %s.%s: %s" % (object_type, owner, object_name, self.curs.fetchone()[0])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
535 self.do_select(queries['colComments'],{'owner':owner, 'object_name': object_name})
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
536
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
537 def resolve(self, identifier):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
538 """Checks (my objects).name, (my synonyms).name, (public synonyms).name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
539 to resolve a database object's name. """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
540 parts = identifier.split('.')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
541 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
542 if len(parts) == 2:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
543 owner, object_name = parts
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
544 self.curs.execute('SELECT object_type FROM all_objects WHERE owner = :owner AND object_name = :object_name',
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
545 {'owner': owner, 'object_name': object_name})
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
546 object_type = self.curs.fetchone()[0]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
547 elif len(parts) == 1:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
548 object_name = parts[0]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
549 self.curs.execute(queries['resolve'], {'objName':object_name})
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
550 object_type, object_name, owner = self.curs.fetchone()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
551 except TypeError:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
552 print 'Could not resolve object %s.' % identifier
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
553 object_type, owner, object_name = '', '', ''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
554 return object_type, owner, object_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
555
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
556 def do_shell(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
557 'execute a command as if at the OS prompt.'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
558 os.system(arg)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
559
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
560 def spoolstop(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
561 if self.spoolFile:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
562 sys.stdout = self.stdoutBeforeSpool
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
563 print 'Finished spooling to ', self.spoolFile.name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
564 self.spoolFile.close()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
565 self.spoolFile = None
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
566
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
567 def do_spool(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
568 """spool [filename] - begins redirecting output to FILENAME."""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
569 self.spoolstop()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
570 arg = arg.strip()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
571 if not arg:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
572 arg = 'output.lst'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
573 if arg.lower() != 'off':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
574 if '.' not in arg:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
575 arg = '%s.lst' % arg
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
576 print 'Sending output to %s (until SPOOL OFF received)' % (arg)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
577 self.spoolFile = open(arg, 'w')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
578 sys.stdout = self.spoolFile
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
579
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
580 def write(self, arg, fname):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
581 originalOut = sys.stdout
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
582 f = open(fname, 'w')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
583 sys.stdout = f
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
584 self.onecmd(arg)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
585 f.close()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
586 sys.stdout = originalOut
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
587
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
588 def do_write(self, args):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
589 'write [filename.extension] query - writes result to a file'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
590 words = args.split(None, 1)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
591 if len(words) > 1 and '.' in words[0]:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
592 fname, command = words
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
593 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
594 fname, command = 'output.txt', args
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
595 self.write(command, fname)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
596 print 'Results written to %s' % os.path.join(os.getcwd(), fname)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
597
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
598 def do_compare(self, args):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
599 """COMPARE query1 TO query2 - uses external tool to display differences.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
600
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
601 Sorting is recommended to avoid false hits."""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
602 fnames = []
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
603 args2 = args.split(' to ')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
604 for n in range(len(args2)):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
605 query = args2[n]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
606 fnames.append('compare%s.txt' % n)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
607 if query.rstrip()[-1] != self.terminator:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
608 query = '%s%s' % (query, self.terminator)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
609 self.write(query, fnames[n])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
610 diffMergeSearcher.invoke(fnames[0], fnames[1])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
611
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
612 bufferPosPattern = re.compile('\d+')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
613 rangeIndicators = ('-',':')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
614 def bufferPositions(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
615 if not self.sqlBuffer:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
616 return []
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
617 arg = arg.strip(self.terminator)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
618 arg = arg.strip()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
619 if not arg:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
620 return [0]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
621 arg = arg.strip().lower()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
622 if arg in ('*', 'all', '-', ':'):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
623 return range(len(self.sqlBuffer))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
624
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
625 edges = [e for e in self.bufferPosPattern.findall(arg)]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
626 edges = [int(e) for e in edges]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
627 if len(edges) > 1:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
628 edges = edges[:2]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
629 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
630 if arg[0] in self.rangeIndicators or arg[-1] in self.rangeIndicators:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
631 edges.append(0)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
632 edges.sort()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
633 start = max(edges[0], 0)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
634 end = min(edges[-1], len(self.sqlBuffer)-1)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
635 return range(start, end+1)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
636 def do_run(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
637 'run [N]: runs the SQL that was run N commands ago'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
638 for pos in self.bufferPositions(arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
639 self.onecmd(self.sqlBuffer[-1-pos])
1
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
640 def do_history(self, arg):
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
641 for (i, itm) in enumerate(self.history):
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
642 print '-------------------------[%d]' % (i+1)
8fa146b9a2d7 reworking multiline
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 0
diff changeset
643 print itm
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
644 def do_list(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
645 'list [N]: lists the SQL that was run N commands ago'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
646 for pos in self.bufferPositions(arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
647 print '*** %i statements ago ***' % pos
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
648 print self.sqlBuffer[-1-pos]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
649 def load(self, fname):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
650 """Pulls command(s) into sql buffer. Returns number of commands loaded."""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
651 initialLength = len(self.sqlBuffer)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
652 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
653 f = open(fname, 'r')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
654 except IOError, e:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
655 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
656 f = open('%s.sql' % fname, 'r')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
657 except:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
658 print 'Problem opening file %s: \n%s' % (fname, e)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
659 return 0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
660 txt = f.read()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
661 f.close()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
662 self.sqlBuffer.extend(commandSeparator.separate(txt))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
663 return len(self.sqlBuffer) - initialLength
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
664 def do_ed(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
665 'ed [N]: brings up SQL from N commands ago in text editor, and puts result in SQL buffer.'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
666 fname = 'mysqlpy_temp.sql'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
667 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
668 buffer = self.sqlBuffer[-1 - (int(arg or 0))]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
669 except IndexError:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
670 buffer = ''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
671 f = open(fname, 'w')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
672 f.write(buffer)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
673 f.close()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
674 editSearcher.invoke(fname)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
675 self.load(fname)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
676 do_edit = do_ed
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
677 def do_get(self, fname):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
678 'Brings SQL commands from a file to the in-memory SQL buffer.'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
679 commandsLoaded = self.load(fname)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
680 if commandsLoaded:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
681 self.do_list('1-%d' % (commandsLoaded-1))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
682 def do_getrun(self, fname):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
683 'Brings SQL commands from a file to the in-memory SQL buffer, and executes them.'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
684 commandNums = range(self.load(fname))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
685 commandNums.reverse()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
686 for commandNum in commandNums:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
687 self.do_run(str(commandNum))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
688 self.sqlBuffer.pop()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
689 def do_psql(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
690 '''Shortcut commands emulating psql's backslash commands.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
691
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
692 \c connect
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
693 \d desc
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
694 \e edit
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
695 \g run
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
696 \h help
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
697 \i getrun
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
698 \o spool
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
699 \p list
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
700 \w save
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
701 \? help psql'''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
702 commands = {}
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
703 for c in self.do_psql.__doc__.splitlines()[2:]:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
704 (abbrev, command) = c.split(None, 1)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
705 commands[abbrev[1:]] = command
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
706 words = arg.split(None,1)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
707 abbrev = words[0]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
708 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
709 args = words[1]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
710 except IndexError:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
711 args = ''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
712 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
713 self.onecmd('%s %s' % (commands[abbrev], args))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
714 self.onecmd('q')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
715 except KeyError:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
716 print 'psql command \%s not yet supported.' % abbrev
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
717 def do_save(self, fname):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
718 'save FILENAME: Saves most recent SQL command to disk.'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
719 f = open(fname, 'w')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
720 f.write(self.sqlBuffer[-1])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
721 f.close()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
722
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
723 def do_print(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
724 'print VARNAME: Show current value of bind variable VARNAME.'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
725 if arg:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
726 if arg[0] == ':':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
727 arg = arg[1:]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
728 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
729 print self.binds[arg]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
730 except KeyError:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
731 print 'No bind variable ', arg
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
732 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
733 self.do_setbind('')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
734 def do_setbind(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
735 args = arg.split(None, 2)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
736 if len(args) == 0:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
737 for (var, val) in self.binds.items():
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
738 print ':%s = %s' % (var, val)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
739 elif len(args) == 1:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
740 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
741 print ':%s = %s' % (args[0], self.binds[args[0]])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
742 except KeyError, e:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
743 print noSuchBindMsg % args[0]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
744 elif len(args) > 2 and args[1] in ('=',':='):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
745 var, val = args[0], args[2]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
746 if val[0] == val[-1] == "'" and len(val) > 1:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
747 val = val[1:-1]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
748 self.binds[var] = val
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
749 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
750 print 'Could not parse ', args
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
751 def do_exec(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
752 if arg[0] == ':':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
753 self.do_setbind(arg[1:])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
754 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
755 self.default('exec %s' % arg)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
756
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
757 def _test():
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
758 import doctest
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
759 doctest.testmod()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
760
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
761 if __name__ == "__main__":
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
762 "Silent return implies that all unit tests succeeded. Use -v to see details."
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
763 _test()