annotate sqlpyPlus.py @ 146:d5917f02ae83

html output switched to genshi
author catherine@Elli.myhome.westell.com
date Thu, 25 Sep 2008 15:40:20 -0400
parents 7e5105efa15d
children a2731c87b837
rev   line source
144
5a021524805a fixed do_resolve
catherine@Elli.myhome.westell.com
parents: 138
diff changeset
1 """sqlpyPlus - extra features (inspired by Oracle SQL*Plus) for Luca Canali's sqlpython.py
0
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
138
3b3c78bad48f need completion too
catherine@Elli.myhome.westell.com
parents: 137
diff changeset
15 - context-sensitive tab-completion for table names, column names, etc.
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
16
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
17 Use 'help' within sqlpython for details.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
18
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
19 Set bind variables the hard (SQL*Plus) way
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
20 exec :b = 3
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
21 or with a python-like shorthand
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
22 :b = 3
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
23
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
24 - catherinedevlin.blogspot.com May 31, 2006
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
25 """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
26 # note in cmd.cmd about supporting emacs commands?
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
27
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
28 descQueries = {
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
29 'TABLE': ("""
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
30 atc.column_name,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
31 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
32 atc.data_type ||
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
33 CASE atc.data_type WHEN 'DATE' THEN ''
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
34 ELSE '(' ||
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
35 CASE atc.data_type WHEN 'NUMBER' THEN TO_CHAR(atc.data_precision) ||
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
36 CASE atc.data_scale WHEN 0 THEN ''
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
37 ELSE ',' || TO_CHAR(atc.data_scale) END
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
38 ELSE TO_CHAR(atc.data_length) END
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
39 END ||
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
40 CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
41 data_type
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
42 FROM all_tab_columns atc
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
43 WHERE atc.table_name = :object_name
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
44 AND atc.owner = :owner
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
45 ORDER BY atc.column_id;""",),
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
46 'PROCEDURE': ("""
122
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
47 NVL(argument_name, 'Return Value') argument_name,
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
48 data_type,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
49 in_out,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
50 default_value
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
51 FROM all_arguments
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
52 WHERE object_name = :object_name
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
53 AND owner = :owner
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
54 AND package_name IS NULL
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
55 ORDER BY sequence;""",),
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
56 'PackageObjects':("""
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
57 SELECT DISTINCT object_name
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
58 FROM all_arguments
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
59 WHERE package_name = :package_name
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
60 AND owner = :owner""",),
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
61 'PackageObjArgs':("""
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
62 object_name,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
63 argument_name,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
64 data_type,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
65 in_out,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
66 default_value
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
67 FROM all_arguments
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
68 WHERE package_name = :package_name
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
69 AND object_name = :object_name
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
70 AND owner = :owner
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
71 AND argument_name IS NOT NULL
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
72 ORDER BY sequence""",),
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
73 'TRIGGER':("""
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
74 description
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
75 FROM all_triggers
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
76 WHERE owner = :owner
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
77 AND trigger_name = :object_name
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
78 """,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
79 """
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
80 table_owner,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
81 base_object_type,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
82 table_name,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
83 column_name,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
84 when_clause,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
85 status,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
86 action_type,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
87 crossedition
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
88 FROM all_triggers
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
89 WHERE owner = :owner
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
90 AND trigger_name = :object_name
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
91 \\t
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
92 """,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
93 ),
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
94 'INDEX':("""
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
95 index_type,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
96 table_owner,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
97 table_name,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
98 table_type,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
99 uniqueness,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
100 compression,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
101 partitioned,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
102 temporary,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
103 generated,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
104 secondary,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
105 dropped,
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
106 visibility
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
107 FROM all_indexes
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
108 WHERE owner = :owner
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
109 AND index_name = :object_name
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
110 \\t
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
111 """,)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
112 }
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
113 descQueries['VIEW'] = descQueries['TABLE']
122
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
114 descQueries['FUNCTION'] = descQueries['PROCEDURE']
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
115
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
116 queries = {
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
117 'resolve': """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
118 SELECT object_type, object_name, owner FROM (
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
119 SELECT object_type, object_name, user owner, 1 priority
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
120 FROM user_objects
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
121 WHERE object_name = :objName
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
122 UNION ALL
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
123 SELECT ao.object_type, ao.object_name, ao.owner, 2 priority
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
124 FROM all_objects ao
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
125 JOIN user_synonyms us ON (us.table_owner = ao.owner AND us.table_name = ao.object_name)
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
126 WHERE us.synonym_name = :objName
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
127 AND ao.object_type != 'SYNONYM'
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
128 UNION ALL
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
129 SELECT ao.object_type, ao.object_name, ao.owner, 3 priority
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
130 FROM all_objects ao
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
131 JOIN all_synonyms asyn ON (asyn.table_owner = ao.owner AND asyn.table_name = ao.object_name)
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
132 WHERE asyn.synonym_name = :objName
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
133 AND ao.object_type != 'SYNONYM'
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
134 AND asyn.owner = 'PUBLIC'
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
135 UNION ALL
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
136 SELECT 'DIRECTORY' object_type, dir.directory_name, dir.owner, 6 priority
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
137 FROM all_directories dir
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
138 WHERE dir.directory_name = :objName
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
139 UNION ALL
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
140 SELECT 'DATABASE LINK' object_type, db_link, owner, 7 priority
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
141 FROM all_db_links dbl
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
142 WHERE dbl.db_link = :objName
127
5d3f0b9c01df resolve ambiguities in resolve
catherine@localhost
parents: 123
diff changeset
143 ) ORDER BY priority ASC,
5d3f0b9c01df resolve ambiguities in resolve
catherine@localhost
parents: 123
diff changeset
144 length(object_type) ASC,
5d3f0b9c01df resolve ambiguities in resolve
catherine@localhost
parents: 123
diff changeset
145 object_type DESC""", # preference: PACKAGE before PACKAGE BODY, TABLE before INDEX
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
146 'tabComments': """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
147 SELECT comments
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
148 FROM all_tab_comments
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
149 WHERE owner = :owner
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
150 AND table_name = :table_name""",
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
151 'colComments': """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
152 atc.column_name,
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
153 acc.comments
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
154 FROM all_tab_columns atc
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
155 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
156 WHERE atc.table_name = :object_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
157 AND atc.owner = :owner
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
158 ORDER BY atc.column_id;""",
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
159 #thanks to Senora.pm for "refs"
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
160 'refs': """
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
161 NULL referenced_by,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
162 c2.table_name references,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
163 c1.constraint_name constraint
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
164 FROM
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
165 user_constraints c1,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
166 user_constraints c2
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
167 WHERE
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
168 c1.table_name = :object_name
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
169 and c1.constraint_type ='R'
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
170 and c1.r_constraint_name = c2.constraint_name
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
171 and c1.r_owner = c2.owner
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
172 and c1.owner = :owner
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
173 UNION
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
174 SELECT c1.table_name referenced_by,
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
175 NULL references,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
176 c1.constraint_name constraint
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
177 FROM
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
178 user_constraints c1,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
179 user_constraints c2
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
180 WHERE
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
181 c2.table_name = :object_name
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
182 and c1.constraint_type ='R'
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
183 and c1.r_constraint_name = c2.constraint_name
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
184 and c1.r_owner = c2.owner
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
185 and c1.owner = :owner
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
186 """
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
187 }
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
188
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
189 import sys, os, re, sqlpython, cx_Oracle, pyparsing, re, completion, genshi
122
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
190 from cmd2 import Cmd, make_option, options, Statekeeper
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
191
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
192 if float(sys.version[:3]) < 2.3:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
193 def enumerate(lst):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
194 return zip(range(len(lst)), lst)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
195
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
196 class SoftwareSearcher(object):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
197 def __init__(self, softwareList, purpose):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
198 self.softwareList = softwareList
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
199 self.purpose = purpose
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
200 self.software = None
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
201 def invoke(self, *args):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
202 if not self.software:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
203 (self.software, self.invokeString) = self.find()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
204 argTuple = tuple([self.software] + list(args))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
205 os.system(self.invokeString % argTuple)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
206 def find(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
207 if self.purpose == 'text editor':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
208 software = os.environ.get('EDITOR')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
209 if software:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
210 return (software, '%s %s')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
211 for (n, (software, invokeString)) in enumerate(self.softwareList):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
212 if os.path.exists(software):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
213 if n > (len(self.softwareList) * 0.7):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
214 print """
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
215
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
216 Using %s. Note that there are better options available for %s,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
217 but %s couldn't find a better one in your PATH.
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
218 Feel free to open up %s
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
219 and customize it to find your favorite %s program.
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
220
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
221 """ % (software, self.purpose, __file__, __file__, self.purpose)
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
222 return (software, invokeString)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
223 stem = os.path.split(software)[1]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
224 for p in os.environ['PATH'].split(os.pathsep):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
225 if os.path.exists(os.sep.join([p, stem])):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
226 return (stem, invokeString)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
227 raise (OSError, """Could not find any %s programs. You will need to install one,
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
228 or customize %s to make it aware of yours.
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
229 Looked for these programs:
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
230 %s""" % (self.purpose, __file__, "\n".join([s[0] for s in self.softwareList])))
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
231 #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
232
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
233 softwareLists = {
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
234 'diff/merge': [
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
235 ('/usr/bin/meld',"%s %s %s"),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
236 ('/usr/bin/kdiff3',"%s %s %s"),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
237 (r'C:\Program Files\Araxis\Araxis Merge v6.5\Merge.exe','"%s" %s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
238 (r'C:\Program Files\TortoiseSVN\bin\TortoiseMerge.exe', '"%s" /base:"%s" /mine:"%s"'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
239 ('FileMerge','%s %s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
240 ('kompare','%s %s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
241 ('WinMerge','%s %s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
242 ('xxdiff','%s %s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
243 ('fldiff','%s %s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
244 ('gtkdiff','%s %s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
245 ('tkdiff','%s %s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
246 ('gvimdiff','%s %s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
247 ('diff',"%s %s %s"),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
248 (r'c:\windows\system32\comp.exe',"%s %s %s")],
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
249 'text editor': [
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
250 ('gedit', '%s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
251 ('textpad', '%s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
252 ('notepad.exe', '%s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
253 ('pico', '%s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
254 ('emacs', '%s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
255 ('vim', '%s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
256 ('vi', '%s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
257 ('ed', '%s %s'),
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
258 ('edlin', '%s %s')
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
259 ]
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
260 }
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
261
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
262 diffMergeSearcher = SoftwareSearcher(softwareLists['diff/merge'],'diff/merge')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
263 editSearcher = SoftwareSearcher(softwareLists['text editor'], 'text editor')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
264 editor = os.environ.get('EDITOR')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
265 if editor:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
266 editSearcher.find = lambda: (editor, "%s %s")
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
267
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
268 class CaselessDict(dict):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
269 """dict with case-insensitive keys.
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
270
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
271 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
272 def __init__(self, other=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
273 if other:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
274 # Doesn't do keyword args
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
275 if isinstance(other, dict):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
276 for k,v in other.items():
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
277 dict.__setitem__(self, k.lower(), v)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
278 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
279 for k,v in other:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
280 dict.__setitem__(self, k.lower(), v)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
281 def __getitem__(self, key):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
282 return dict.__getitem__(self, key.lower())
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
283 def __setitem__(self, key, value):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
284 dict.__setitem__(self, key.lower(), value)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
285 def __contains__(self, key):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
286 return dict.__contains__(self, key.lower())
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
287 def has_key(self, key):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
288 return dict.has_key(self, key.lower())
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
289 def get(self, key, def_val=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
290 return dict.get(self, key.lower(), def_val)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
291 def setdefault(self, key, def_val=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
292 return dict.setdefault(self, key.lower(), def_val)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
293 def update(self, other):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
294 for k,v in other.items():
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
295 dict.__setitem__(self, k.lower(), v)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
296 def fromkeys(self, iterable, value=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
297 d = CaselessDict()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
298 for k in iterable:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
299 dict.__setitem__(d, k.lower(), value)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
300 return d
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
301 def pop(self, key, def_val=None):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
302 return dict.pop(self, key.lower(), def_val)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
303
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
304 class Parser(object):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
305 comment_def = "--" + pyparsing.ZeroOrMore(pyparsing.CharsNotIn("\n"))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
306 def __init__(self, scanner, retainSeparator=True):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
307 self.scanner = scanner
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
308 self.scanner.ignore(pyparsing.sglQuotedString)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
309 self.scanner.ignore(pyparsing.dblQuotedString)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
310 self.scanner.ignore(self.comment_def)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
311 self.scanner.ignore(pyparsing.cStyleComment)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
312 self.retainSeparator = retainSeparator
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
313 def separate(self, txt):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
314 itms = []
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
315 for (sqlcommand, start, end) in self.scanner.scanString(txt):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
316 if sqlcommand:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
317 if type(sqlcommand[0]) == pyparsing.ParseResults:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
318 if self.retainSeparator:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
319 itms.append("".join(sqlcommand[0]))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
320 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
321 itms.append(sqlcommand[0][0])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
322 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
323 if sqlcommand[0]:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
324 itms.append(sqlcommand[0])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
325 return itms
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
326
131
2b7ce838120d experimenting with completion
catherine@Elli.myhome.westell.com
parents: 130
diff changeset
327 bindScanner = Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" ))
2b7ce838120d experimenting with completion
catherine@Elli.myhome.westell.com
parents: 130
diff changeset
328
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
329 def findBinds(target, existingBinds, givenBindVars = {}):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
330 result = givenBindVars
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
331 for finding, startat, endat in bindScanner.scanner.scanString(target):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
332 varname = finding[1]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
333 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
334 result[varname] = existingBinds[varname]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
335 except KeyError:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
336 if not givenBindVars.has_key(varname):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
337 print 'Bind variable %s not defined.' % (varname)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
338 return result
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
339
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
340 class sqlpyPlus(sqlpython.sqlpython):
21
8b55aaa52ce9 working on load, and preserving stdin/out
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 20
diff changeset
341 defaultExtension = 'sql'
92
fa8c9eb8908f accepting command-line args
catherine@cordelia
parents: 91
diff changeset
342 sqlpython.sqlpython.shortcuts.update({':': 'setbind', '\\': 'psql', '@': '_load'})
21
8b55aaa52ce9 working on load, and preserving stdin/out
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 20
diff changeset
343 multilineCommands = '''select insert update delete tselect
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
344 create drop alter'''.split()
42
05c20d6bcec4 picking up from hiatus
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 41
diff changeset
345 defaultFileName = 'afiedt.buf'
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
346 def __init__(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
347 sqlpython.sqlpython.__init__(self)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
348 self.binds = CaselessDict()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
349 self.sqlBuffer = []
123
898ed97bec38 fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents: 122
diff changeset
350 self.settable = ['maxtselctrows', 'maxfetch', 'autobind',
898ed97bec38 fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents: 122
diff changeset
351 'failover', 'timeout', 'commit_on_exit'] # settables must be lowercase
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
352 self.stdoutBeforeSpool = sys.stdout
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
353 self.spoolFile = None
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
354 self.autobind = False
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
355 self.failover = False
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
356 def default(self, arg, do_everywhere=False):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
357 sqlpython.sqlpython.default(self, arg, do_everywhere)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
358 self.sqlBuffer.append(self.query)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
359
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
360 # overrides cmd's parseline
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
361 def parseline(self, line):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
362 """Parse the line into a command name and a string containing
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
363 the arguments. Returns a tuple containing (command, args, line).
40
1fb9f7dee7d8 tearing out cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 39
diff changeset
364 'command' and 'args' may be None if the line couldn't be parsed.
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
365 Overrides cmd.cmd.parseline to accept variety of shortcuts.."""
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
366
41
33c9bc61db66 separation surgery successful?
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 40
diff changeset
367 cmd, arg, line = sqlpython.sqlpython.parseline(self, line)
20
d6d64c2e3b98 shortcuts
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 16
diff changeset
368 if cmd in ('select', 'sleect', 'insert', 'update', 'delete', 'describe',
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
369 'desc', 'comments', 'pull', 'refs', 'desc', 'triggers', 'find') \
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
370 and not hasattr(self, 'curs'):
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
371 print 'Not connected.'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
372 return '', '', ''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
373 return cmd, arg, line
92
fa8c9eb8908f accepting command-line args
catherine@cordelia
parents: 91
diff changeset
374
fa8c9eb8908f accepting command-line args
catherine@cordelia
parents: 91
diff changeset
375 do__load = Cmd.do_load
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
376
7
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
377 def onecmd_plus_hooks(self, line):
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
378 line = self.precmd(line)
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
379 stop = self.onecmd(line)
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
380 stop = self.postcmd(stop, line)
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
381
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
382 def do_shortcuts(self,arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
383 """Lists available first-character shortcuts
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
384 (i.e. '!dir' is equivalent to 'shell dir')"""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
385 for (scchar, scto) in self.shortcuts.items():
58
de6278a3bf53 adding compare help
catherine@cordelia
parents: 57
diff changeset
386 print '%s: %s' % (scchar, scto)
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
387
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
388 def colnames(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
389 return [d[0] for d in curs.description]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
390
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
391 def sql_format_itm(self, itm, needsquotes):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
392 if itm is None:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
393 return 'NULL'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
394 if needsquotes:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
395 return "'%s'" % str(itm)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
396 return str(itm)
105
1e69f3a26216 fixed python2.4 compat
catherine@localhost
parents: 98
diff changeset
397 def str_or_empty(self, itm):
1e69f3a26216 fixed python2.4 compat
catherine@localhost
parents: 98
diff changeset
398 if itm is None:
1e69f3a26216 fixed python2.4 compat
catherine@localhost
parents: 98
diff changeset
399 return ''
1e69f3a26216 fixed python2.4 compat
catherine@localhost
parents: 98
diff changeset
400 return str(itm)
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
401 def output_as_insert_statements(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
402 usequotes = [d[1] != cx_Oracle.NUMBER for d in self.curs.description]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
403 def formatRow(row):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
404 return ','.join(self.sql_format_itm(itm, useq)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
405 for (itm, useq) in zip(row, usequotes))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
406 result = ['INSERT INTO %s (%s) VALUES (%s);' %
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
407 (self.tblname, ','.join(self.colnames), formatRow(row))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
408 for row in self.rows]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
409 return '\n'.join(result)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
410
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
411 xml_template = genshi.template.MarkupTemplate('''
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
412 <ul xmlns:py="http://genshi.edgewall.org/">
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
413 <li py:for="item in items">${item}</li>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
414 </ul>''')
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
415 def output_row_as_xml(self, row):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
416 result = [' <%s>\n %s\n </%s>' %
105
1e69f3a26216 fixed python2.4 compat
catherine@localhost
parents: 98
diff changeset
417 (colname.lower(), self.str_or_empty(itm), colname.lower())
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
418 for (itm, colname) in zip(row, self.colnames)]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
419 return '\n'.join(result)
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
420 '''
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
421 xml_template = genshi.template.MarkupTemplate("""
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
422 <xml xmlns:py="http://genshi.edgewall.org/">
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
423 <relation py:attr="{'type':tblname}">
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
424 <tuple py:for="row in rows" py:attr="{'type':>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
425 </tuple>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
426 </relation>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
427 <tr>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
428 <th py:for="colname in colnames">
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
429 <span py:replace="colname">Column Name</span>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
430 </th>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
431 </tr>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
432 <tr py:for="row in rows">
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
433 <td py:for="itm in row">
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
434 <span py:replace="str(itm)">Value</span>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
435 </td>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
436 </tr>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
437 </table>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
438 </body>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
439 </html>""")
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
440 '''
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
441 def output_as_xml(self):
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
442 return self.xml_template(**self.__dict__)
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
443 result = ['<%s>\n%s\n</%s>' %
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
444 (self.tblname, self.output_row_as_xml(row), self.tblname)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
445 for row in self.rows]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
446 return '\n'.join(result)
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
447
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
448 html_template = genshi.template.MarkupTemplate("""
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
449 <html xmlns:py="http://genshi.edgewall.org/">
111
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
450 <head>
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
451 <title py:content="tblname">Table Name</title>
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
452 </head>
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
453 <body>
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
454 <table py:attrs="{'id':tblname}">
111
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
455 <tr>
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
456 <th py:for="colname in colnames">
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
457 <span py:replace="colname.lower()">Column Name</span>
111
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
458 </th>
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
459 </tr>
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
460 <tr py:for="row in rows">
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
461 <td py:for="itm in row">
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
462 <span py:replace="str(itm)">Value</span>
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
463 </td>
111
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
464 </tr>
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
465 </table>
289b0a472b65 print results in describe
catherine@Elli.myhome.westell.com
parents: 109
diff changeset
466 </body>
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
467 </html>""")
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
468
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
469 def output_as_list(self, align):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
470 result = []
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
471 colnamelen = max(len(colname) for colname in self.colnames) + 1
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
472 for (idx, row) in enumerate(self.rows):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
473 result.append('\n**** Row: %d' % (idx+1))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
474 for (itm, colname) in zip(row, self.colnames):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
475 if align:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
476 colname = colname.ljust(colnamelen)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
477 result.append('%s: %s' % (colname, itm))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
478 return '\n'.join(result)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
479
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
480 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
481 def output(self, outformat, rowlimit):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
482 self.tblname = self.tableNameFinder.search(self.curs.statement).group(1)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
483 self.colnames = [d[0] for d in self.curs.description]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
484 if outformat == '\\i':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
485 result = self.output_as_insert_statements()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
486 elif outformat == '\\x':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
487 result = self.output_as_xml()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
488 elif outformat == '\\g':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
489 result = self.output_as_list(align=False)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
490 elif outformat == '\\G':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
491 result = self.output_as_list(align=True)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
492 elif outformat in ('\\s', '\\S', '\\c', '\\C'): #csv
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
493 result = []
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
494 if outformat in ('\\s', '\\c'):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
495 result.append(','.join('"%s"' % colname for colname in self.colnames))
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
496 for row in self.rows:
106
31de4c0b06ee fixed python2.4 compat
catherine@localhost
parents: 105
diff changeset
497 result.append(','.join('"%s"' % self.str_or_empty(itm) for itm in row))
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
498 result = '\n'.join(result)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
499 elif outformat == '\\h':
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
500 result = self.html_template.generate(**self.__dict__)
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
501 elif outformat == '\\t':
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
502 rows = [self.colnames]
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
503 rows.extend(list(self.rows))
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
504 transpr = [[rows[y][x] for y in range(len(rows))]for x in range(len(rows[0]))] # matrix transpose
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
505 newdesc = [['ROW N.'+str(y),10] for y in range(len(rows))]
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
506 for x in range(len(self.curs.description)):
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
507 if str(self.curs.description[x][1]) == "<type 'cx_Oracle.BINARY'>": # handles RAW columns
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
508 rname = transpr[x][0]
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
509 transpr[x] = map(binascii.b2a_hex, transpr[x])
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
510 transpr[x][0] = rname
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
511 newdesc[0][0] = 'COLUMN NAME'
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
512 result = '\n' + sqlpython.pmatrix(transpr,newdesc)
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
513 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
514 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
515 return result
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
516
42
05c20d6bcec4 picking up from hiatus
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 41
diff changeset
517 legalOracle = re.compile('[a-zA-Z_$#]')
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
518
137
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
519 def select_scalar_list(self, sql, binds={}):
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
520 self.curs.execute(sql, binds)
132
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
521 return [r[0] for r in self.curs.fetchall()]
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
522
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
523 columnNameRegex = re.compile(
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
524 r'select\s+(.*)from',
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
525 re.IGNORECASE | re.DOTALL | re.MULTILINE)
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
526 def completedefault(self, text, line, begidx, endidx):
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
527 segment = completion.whichSegment(line)
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
528 text = text.upper()
131
2b7ce838120d experimenting with completion
catherine@Elli.myhome.westell.com
parents: 130
diff changeset
529 completions = []
132
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
530 if segment == 'select':
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
531 stmt = "SELECT column_name FROM user_tab_columns WHERE column_name LIKE '%s%%'"
137
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
532 completions = self.select_scalar_list(stmt % (text))
132
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
533 if not completions:
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
534 stmt = "SELECT column_name FROM all_tab_columns WHERE column_name LIKE '%s%%'"
137
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
535 completions = self.select_scalar_list(stmt % (text))
133
25cbb45b190d column completion working
catherine@Elli.myhome.westell.com
parents: 132
diff changeset
536 if segment == 'from':
25cbb45b190d column completion working
catherine@Elli.myhome.westell.com
parents: 132
diff changeset
537 columnNames = self.columnNameRegex.search(line)
25cbb45b190d column completion working
catherine@Elli.myhome.westell.com
parents: 132
diff changeset
538 if columnNames:
25cbb45b190d column completion working
catherine@Elli.myhome.westell.com
parents: 132
diff changeset
539 columnNames = columnNames.group(1)
25cbb45b190d column completion working
catherine@Elli.myhome.westell.com
parents: 132
diff changeset
540 columnNames = [c.strip().upper() for c in columnNames.split(',')]
25cbb45b190d column completion working
catherine@Elli.myhome.westell.com
parents: 132
diff changeset
541 stmt1 = "SELECT table_name FROM all_tab_columns WHERE column_name = '%s' AND table_name LIKE '%s%%'"
25cbb45b190d column completion working
catherine@Elli.myhome.westell.com
parents: 132
diff changeset
542 for columnName in columnNames:
138
3b3c78bad48f need completion too
catherine@Elli.myhome.westell.com
parents: 137
diff changeset
543 # and if columnName is * ?
137
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
544 completions.extend(self.select_scalar_list(stmt1 % (columnName, text)))
134
b532bc8430a6 completion is nice. i is proud.
catherine@Elli.myhome.westell.com
parents: 133
diff changeset
545 if segment in ('from', 'update', 'insert into') and (not completions):
132
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
546 stmt = "SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'"
137
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
547 completions = self.select_scalar_list(stmt % (text))
132
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
548 if not completions:
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
549 stmt = """SELECT table_name FROM user_tables WHERE table_name LIKE '%s%%'
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
550 UNION
2baecb3d5356 improving tab completion
catherine@Elli.myhome.westell.com
parents: 131
diff changeset
551 SELECT DISTINCT owner FROM all_tables WHERE owner LIKE '%%%s'"""
137
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
552 completions = self.select_scalar_list(stmt % (text, text))
134
b532bc8430a6 completion is nice. i is proud.
catherine@Elli.myhome.westell.com
parents: 133
diff changeset
553 if segment in ('where', 'group by', 'order by', 'having', 'set'):
136
2e69a257b6ab now catching multi table names in from clause
catherine@Elli.myhome.westell.com
parents: 135
diff changeset
554 tableNames = completion.tableNamesFromFromClause(line)
2e69a257b6ab now catching multi table names in from clause
catherine@Elli.myhome.westell.com
parents: 135
diff changeset
555 if tableNames:
2e69a257b6ab now catching multi table names in from clause
catherine@Elli.myhome.westell.com
parents: 135
diff changeset
556 stmt = """SELECT column_name FROM all_tab_columns
2e69a257b6ab now catching multi table names in from clause
catherine@Elli.myhome.westell.com
parents: 135
diff changeset
557 WHERE table_name IN (%s)""" % \
2e69a257b6ab now catching multi table names in from clause
catherine@Elli.myhome.westell.com
parents: 135
diff changeset
558 (','.join("'%s'" % (t) for t in tableNames))
133
25cbb45b190d column completion working
catherine@Elli.myhome.westell.com
parents: 132
diff changeset
559 stmt = "%s AND column_name LIKE '%s%%'" % (stmt, text)
137
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
560 completions = self.select_scalar_list(stmt)
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
561 if not segment:
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
562 stmt = "SELECT object_name FROM all_objects WHERE object_name LIKE '%s%%'"
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
563 completions = self.select_scalar_list(stmt % (text))
131
2b7ce838120d experimenting with completion
catherine@Elli.myhome.westell.com
parents: 130
diff changeset
564 return completions
2b7ce838120d experimenting with completion
catherine@Elli.myhome.westell.com
parents: 130
diff changeset
565
116
6e346ae994b9 beginning adaptation to new cmd2
catherine@Elli.myhome.westell.com
parents: 111
diff changeset
566 rowlimitPattern = pyparsing.Word(pyparsing.nums)('rowlimit')
145
7e5105efa15d catch all terminators when parsing
catherine@Elli.myhome.westell.com
parents: 144
diff changeset
567 terminatorPattern = (pyparsing.oneOf('; \\s \\S \\c \\C \\t \\x \\h \\g \\G \\i')
130
40bbe808e98a seems to have fixed editing of hanging files
catherine@Elli.myhome.westell.com
parents: 129
diff changeset
568 ^ pyparsing.Literal('\n/') ^ \
40bbe808e98a seems to have fixed editing of hanging files
catherine@Elli.myhome.westell.com
parents: 129
diff changeset
569 (pyparsing.Literal('\nEOF') + pyparsing.stringEnd)) \
40bbe808e98a seems to have fixed editing of hanging files
catherine@Elli.myhome.westell.com
parents: 129
diff changeset
570 ('terminator') + \
146
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
571 pyparsing.Optional(rowlimitPattern) #+ \
d5917f02ae83 html output switched to genshi
catherine@Elli.myhome.westell.com
parents: 145
diff changeset
572 #pyparsing.FollowedBy(pyparsing.LineEnd())
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
573 def do_select(self, arg, bindVarsIn=None, override_terminator=None):
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
574 """Fetch rows from a table.
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
575
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
576 Limit the number of rows retrieved by appending
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
577 an integer after the terminator
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
578 (example: SELECT * FROM mytable;10 )
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
579
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
580 Output may be formatted by choosing an alternative terminator
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
581 ("help terminators" for details)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
582 """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
583 bindVarsIn = bindVarsIn or {}
116
6e346ae994b9 beginning adaptation to new cmd2
catherine@Elli.myhome.westell.com
parents: 111
diff changeset
584 statement = self.parsed('select ' + arg)
117
dfb71885dd41 everything seems working
catherine@Elli.myhome.westell.com
parents: 116
diff changeset
585 self.query = statement.unterminated
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
586 if override_terminator:
116
6e346ae994b9 beginning adaptation to new cmd2
catherine@Elli.myhome.westell.com
parents: 111
diff changeset
587 statement['terminator'] = override_terminator
6e346ae994b9 beginning adaptation to new cmd2
catherine@Elli.myhome.westell.com
parents: 111
diff changeset
588 statement['rowlimit'] = int(statement.rowlimit or 0)
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
589 try:
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
590 self.varsUsed = findBinds(self.query, self.binds, bindVarsIn)
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
591 self.curs.execute(self.query, self.varsUsed)
116
6e346ae994b9 beginning adaptation to new cmd2
catherine@Elli.myhome.westell.com
parents: 111
diff changeset
592 self.rows = self.curs.fetchmany(min(self.maxfetch, (statement.rowlimit or self.maxfetch)))
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
593 self.desc = self.curs.description
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
594 self.rc = self.curs.rowcount
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
595 if self.rc > 0:
117
dfb71885dd41 everything seems working
catherine@Elli.myhome.westell.com
parents: 116
diff changeset
596 self.stdout.write('\n%s\n' % (self.output(statement.terminator, statement.rowlimit)))
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
597 if self.rc == 0:
47
46b7b31dc395 feedback should print, not stdout
catherine.devlin@gmail.com
parents: 46
diff changeset
598 print '\nNo rows Selected.\n'
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
599 elif self.rc == 1:
47
46b7b31dc395 feedback should print, not stdout
catherine.devlin@gmail.com
parents: 46
diff changeset
600 print '\n1 row selected.\n'
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
601 if self.autobind:
42
05c20d6bcec4 picking up from hiatus
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 41
diff changeset
602 self.binds.update(dict(zip([''.join(l for l in d[0] if l.isalnum()) for d in self.desc], self.rows[0])))
05c20d6bcec4 picking up from hiatus
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 41
diff changeset
603 if len(self.desc) == 1:
05c20d6bcec4 picking up from hiatus
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 41
diff changeset
604 self.binds['_'] = self.rows[0][0]
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
605 elif self.rc < self.maxfetch:
47
46b7b31dc395 feedback should print, not stdout
catherine.devlin@gmail.com
parents: 46
diff changeset
606 print '\n%d rows selected.\n' % self.rc
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
607 else:
47
46b7b31dc395 feedback should print, not stdout
catherine.devlin@gmail.com
parents: 46
diff changeset
608 print '\nSelected Max Num rows (%d)' % self.rc
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
609 except Exception, e:
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
610 print e
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
611 import traceback
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
612 traceback.print_exc(file=sys.stdout)
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
613 self.sqlBuffer.append(self.query)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
614
131
2b7ce838120d experimenting with completion
catherine@Elli.myhome.westell.com
parents: 130
diff changeset
615
82
5485b66c3445 initial switchover done
catherine@localhost
parents: 81
diff changeset
616 @options([make_option('-f', '--full', action='store_true', help='get dependent objects as well')])
81
32c868fca272 midway through converting to optparse
catherine@localhost
parents: 80
diff changeset
617 def do_pull(self, arg, opts):
32c868fca272 midway through converting to optparse
catherine@localhost
parents: 80
diff changeset
618 """Displays source code."""
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
619
144
5a021524805a fixed do_resolve
catherine@Elli.myhome.westell.com
parents: 138
diff changeset
620 arg = self.parsed(arg).unterminated.upper()
5a021524805a fixed do_resolve
catherine@Elli.myhome.westell.com
parents: 138
diff changeset
621 object_type, owner, object_name = self.resolve(arg)
79
01d578f4e6e7 prevent crash when pull unresolved
catherine@DellZilla.myhome.westell.com
parents: 78
diff changeset
622 if not object_type:
01d578f4e6e7 prevent crash when pull unresolved
catherine@DellZilla.myhome.westell.com
parents: 78
diff changeset
623 return
46
ad41f6a577f7 think terminators are working now
catherine.devlin@gmail.com
parents: 45
diff changeset
624 self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name))
63
16618ff91c63 fixed pull bug
catherine@cordelia
parents: 62
diff changeset
625 self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB,
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
626 [object_type, object_name, owner])))
81
32c868fca272 midway through converting to optparse
catherine@localhost
parents: 80
diff changeset
627 if opts.full:
40
1fb9f7dee7d8 tearing out cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 39
diff changeset
628 for dependent_type in ('OBJECT_GRANT', 'CONSTRAINT', 'TRIGGER'):
1fb9f7dee7d8 tearing out cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 39
diff changeset
629 try:
63
16618ff91c63 fixed pull bug
catherine@cordelia
parents: 62
diff changeset
630 self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB,
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
631 [dependent_type, object_name, owner])))
40
1fb9f7dee7d8 tearing out cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 39
diff changeset
632 except cx_Oracle.DatabaseError:
1fb9f7dee7d8 tearing out cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 39
diff changeset
633 pass
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
634
129
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
635 @options([make_option('-a','--all',action='store_true', help='Find in all schemas (not just my own)'),
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
636 make_option('-i', '--insensitive', action='store_true', help='case-insensitive search'),
122
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
637 make_option('-c', '--col', action='store_true', help='find column'),
129
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
638 make_option('-t', '--table', action='store_true', help='find table')])
81
32c868fca272 midway through converting to optparse
catherine@localhost
parents: 80
diff changeset
639 def do_find(self, arg, opts):
121
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
640 """Finds argument in source code or (with -c) in column definitions."""
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
641
129
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
642 arg = self.parsed(arg).unterminated.upper()
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
643
121
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
644 if opts.col:
129
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
645 sql = "owner, table_name, column_name from all_tab_columns where column_name like '%%%s%%'" % (arg)
122
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
646 elif opts.table:
129
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
647 sql = "owner, table_name from all_tables where table_name like '%%%s%%'" % (arg)
40
1fb9f7dee7d8 tearing out cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 39
diff changeset
648 else:
121
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
649 if opts.insensitive:
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
650 searchfor = "LOWER(text)"
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
651 arg = arg.lower()
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
652 else:
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
653 searchfor = "text"
129
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
654 sql = "* from all_source where %s like '%%%s%%'" % (searchfor, arg)
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
655 if not opts.all:
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
656 sql = '%s and owner = user' % (sql)
d8661065fb77 added -c option to find
catherine@localhost
parents: 128
diff changeset
657 self.do_select(sql)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
658
83
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
659 @options([make_option('-a','--all',action='store_true',
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
660 help='Describe all objects (not just my own)')])
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
661 def do_describe(self, arg, opts):
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
662 "emulates SQL*Plus's DESCRIBE"
121
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
663
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
664 arg = self.parsed(arg).unterminated.upper()
83
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
665 if opts.all:
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
666 which_view = (', owner', 'all')
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
667 else:
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
668 which_view = ('', 'user')
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
669
77
4b5d4e5798dd \d with wildcard
catherine@cordelia
parents: 76
diff changeset
670 if not arg:
83
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
671 self.do_select("""object_name, object_type%s FROM %s_objects WHERE object_type IN ('TABLE','VIEW','INDEX') ORDER BY object_name""" % which_view)
77
4b5d4e5798dd \d with wildcard
catherine@cordelia
parents: 76
diff changeset
672 return
121
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
673 object_type, owner, object_name = self.resolve(arg)
77
4b5d4e5798dd \d with wildcard
catherine@cordelia
parents: 76
diff changeset
674 if not object_type:
87
2de82dd6eba2 back to dellzilla
catherine@cordelia
parents: 86
diff changeset
675 self.do_select("""object_name, object_type%s FROM %s_objects
2de82dd6eba2 back to dellzilla
catherine@cordelia
parents: 86
diff changeset
676 WHERE object_type IN ('TABLE','VIEW','INDEX')
2de82dd6eba2 back to dellzilla
catherine@cordelia
parents: 86
diff changeset
677 AND object_name LIKE '%%%s%%'
2de82dd6eba2 back to dellzilla
catherine@cordelia
parents: 86
diff changeset
678 ORDER BY object_name""" %
2de82dd6eba2 back to dellzilla
catherine@cordelia
parents: 86
diff changeset
679 (which_view[0], which_view[1], arg.upper()) )
77
4b5d4e5798dd \d with wildcard
catherine@cordelia
parents: 76
diff changeset
680 return
46
ad41f6a577f7 think terminators are working now
catherine.devlin@gmail.com
parents: 45
diff changeset
681 self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name))
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
682 descQ = descQueries.get(object_type)
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
683 if descQ:
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
684 for q in descQ:
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
685 self.do_select(q,bindVarsIn={'object_name':object_name, 'owner':owner})
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
686 elif object_type == 'PACKAGE':
137
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
687 packageContents = self.select_scalar_list(descQueries['PackageObjects'][0], {'package_name':object_name, 'owner':owner})
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
688 for packageObj_name in packageContents:
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
689 self.stdout.write('Arguments to %s\n' % (packageObj_name))
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
690 self.do_select(descQueries['PackageObjArgs'][0],bindVarsIn={'package_name':object_name, 'owner':owner, 'object_name':packageObj_name})
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
691 do_desc = do_describe
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
692
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
693 def do_deps(self, arg):
121
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
694 arg = self.parsed(arg).unterminated.upper()
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
695 object_type, owner, object_name = self.resolve(arg)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
696 if object_type == 'PACKAGE BODY':
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
697 q = "and (type != 'PACKAGE BODY' or name != :object_name)'"
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
698 object_type = 'PACKAGE'
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
699 else:
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
700 q = ""
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
701 q = """ name,
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
702 type
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
703 from user_dependencies
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
704 where
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
705 referenced_name like :object_name
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
706 and referenced_type like :object_type
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
707 and referenced_owner like :owner
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
708 %s""" % (q)
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
709 self.do_select(q, {'object_name':object_name, 'object_type':object_type, 'owner':owner})
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
710
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
711 def do_comments(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
712 'Prints comments on a table and its columns.'
121
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
713 arg = self.parsed(arg).unterminated.upper()
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
714 object_type, owner, object_name = self.resolve(arg)
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
715 if object_type:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
716 self.curs.execute(queries['tabComments'],{'table_name':object_name, 'owner':owner})
46
ad41f6a577f7 think terminators are working now
catherine.devlin@gmail.com
parents: 45
diff changeset
717 self.stdout.write("%s %s.%s: %s\n" % (object_type, owner, object_name, self.curs.fetchone()[0]))
5
65ae6cec71c6 expanded desc good so far
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 4
diff changeset
718 self.do_select(queries['colComments'],bindVarsIn={'owner':owner, 'object_name': object_name})
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
719
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
720 def resolve(self, identifier):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
721 """Checks (my objects).name, (my synonyms).name, (public synonyms).name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
722 to resolve a database object's name. """
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
723 parts = identifier.split('.')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
724 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
725 if len(parts) == 2:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
726 owner, object_name = parts
137
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
727 object_type = self.select_scalar_list('SELECT object_type FROM all_objects WHERE owner = :owner AND object_name = :object_name',
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
728 {'owner': owner, 'object_name': object_name}
c27eeeea8752 completion extended to catchall
catherine@Elli.myhome.westell.com
parents: 136
diff changeset
729 )[0]
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
730 elif len(parts) == 1:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
731 object_name = parts[0]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
732 self.curs.execute(queries['resolve'], {'objName':object_name})
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
733 object_type, object_name, owner = self.curs.fetchone()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
734 except TypeError:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
735 print 'Could not resolve object %s.' % identifier
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
736 object_type, owner, object_name = '', '', ''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
737 return object_type, owner, object_name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
738
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
739 def do_resolve(self, arg):
144
5a021524805a fixed do_resolve
catherine@Elli.myhome.westell.com
parents: 138
diff changeset
740 arg = self.parsed(arg).unterminated.upper()
5a021524805a fixed do_resolve
catherine@Elli.myhome.westell.com
parents: 138
diff changeset
741 self.stdout.write(','.join(self.resolve(arg))+'\n')
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
742
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
743 def spoolstop(self):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
744 if self.spoolFile:
88
d3da34473a8e fixed spool
catherine@localhost
parents: 87
diff changeset
745 self.stdout = self.stdoutBeforeSpool
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
746 print 'Finished spooling to ', self.spoolFile.name
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
747 self.spoolFile.close()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
748 self.spoolFile = None
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
749
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
750 def do_spool(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
751 """spool [filename] - begins redirecting output to FILENAME."""
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
752 self.spoolstop()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
753 arg = arg.strip()
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
754 if not arg:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
755 arg = 'output.lst'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
756 if arg.lower() != 'off':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
757 if '.' not in arg:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
758 arg = '%s.lst' % arg
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
759 print 'Sending output to %s (until SPOOL OFF received)' % (arg)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
760 self.spoolFile = open(arg, 'w')
88
d3da34473a8e fixed spool
catherine@localhost
parents: 87
diff changeset
761 self.stdout = self.spoolFile
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
762
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
763 def do_write(self, args):
88
d3da34473a8e fixed spool
catherine@localhost
parents: 87
diff changeset
764 print 'Use (query) > outfilename instead.'
d3da34473a8e fixed spool
catherine@localhost
parents: 87
diff changeset
765 return
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
766
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
767 def do_compare(self, args):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
768 """COMPARE query1 TO query2 - uses external tool to display differences.
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
769
58
de6278a3bf53 adding compare help
catherine@cordelia
parents: 57
diff changeset
770 Sorting is recommended to avoid false hits.
de6278a3bf53 adding compare help
catherine@cordelia
parents: 57
diff changeset
771 Will attempt to use a graphical diff/merge tool like kdiff3, meld, or Araxis Merge,
de6278a3bf53 adding compare help
catherine@cordelia
parents: 57
diff changeset
772 if they are installed."""
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
773 fnames = []
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
774 args2 = args.split(' to ')
88
d3da34473a8e fixed spool
catherine@localhost
parents: 87
diff changeset
775 if len(args2) < 2:
d3da34473a8e fixed spool
catherine@localhost
parents: 87
diff changeset
776 print self.do_compare.__doc__
d3da34473a8e fixed spool
catherine@localhost
parents: 87
diff changeset
777 return
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
778 for n in range(len(args2)):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
779 query = args2[n]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
780 fnames.append('compare%s.txt' % n)
121
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
781 #TODO: update this terminator-stripping
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
782 if query.rstrip()[-1] != self.terminator:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
783 query = '%s%s' % (query, self.terminator)
88
d3da34473a8e fixed spool
catherine@localhost
parents: 87
diff changeset
784 self.onecmd_plus_hooks('%s > %s' % (query, fnames[n]))
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
785 diffMergeSearcher.invoke(fnames[0], fnames[1])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
786
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
787 bufferPosPattern = re.compile('\d+')
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
788 rangeIndicators = ('-',':')
16
2776755a3a7e beginning separation of cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 15
diff changeset
789
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
790 def do_psql(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
791 '''Shortcut commands emulating psql's backslash commands.
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
792
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
793 \c connect
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
794 \d desc
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
795 \e edit
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
796 \g run
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
797 \h help
92
fa8c9eb8908f accepting command-line args
catherine@cordelia
parents: 91
diff changeset
798 \i load
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
799 \o spool
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
800 \p list
77
4b5d4e5798dd \d with wildcard
catherine@cordelia
parents: 76
diff changeset
801 \q quit
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
802 \w save
61
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
803 \db _dir_tablespaces
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
804 \dd comments
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
805 \dn _dir_schemas
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
806 \dt _dir_tables
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
807 \dv _dir_views
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
808 \di _dir_indexes
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
809 \? help psql'''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
810 commands = {}
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
811 for c in self.do_psql.__doc__.splitlines()[2:]:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
812 (abbrev, command) = c.split(None, 1)
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
813 commands[abbrev[1:]] = command
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
814 words = arg.split(None,1)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
815 try:
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
816 abbrev = words[0]
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
817 except IndexError:
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
818 return
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
819 try:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
820 args = words[1]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
821 except IndexError:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
822 args = ''
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
823 try:
77
4b5d4e5798dd \d with wildcard
catherine@cordelia
parents: 76
diff changeset
824 return self.onecmd('%s %s' % (commands[abbrev], args))
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
825 except KeyError:
61
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
826 print 'psql command \%s not yet supported.' % abbrev
77
4b5d4e5798dd \d with wildcard
catherine@cordelia
parents: 76
diff changeset
827
83
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
828 @options([make_option('-a','--all',action='store_true',
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
829 help='Describe all objects (not just my own)')])
84
a7be838c4ad5 fixes to --all
catherine@cordelia
parents: 83
diff changeset
830 def do__dir_tables(self, arg, opts):
83
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
831 if opts.all:
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
832 which_view = (', owner', 'all')
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
833 else:
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
834 which_view = ('', 'user')
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
835 self.do_select("""table_name, 'TABLE' as type%s FROM %s_tables WHERE table_name LIKE '%%%s%%'""" %
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
836 (which_view[0], which_view[1], arg.upper()))
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
837
83
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
838 @options([make_option('-a','--all',action='store_true',
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
839 help='Describe all objects (not just my own)')])
84
a7be838c4ad5 fixes to --all
catherine@cordelia
parents: 83
diff changeset
840 def do__dir_views(self, arg, opts):
83
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
841 if opts.all:
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
842 which_view = (', owner', 'all')
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
843 else:
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
844 which_view = ('', 'user')
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
845 self.do_select("""view_name, 'VIEW' as type%s FROM %s_views WHERE view_name LIKE '%%%s%%'""" %
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
846 (which_view[0], which_view[1], arg.upper()))
61
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
847
83
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
848 @options([make_option('-a','--all',action='store_true',
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
849 help='Describe all objects (not just my own)')])
84
a7be838c4ad5 fixes to --all
catherine@cordelia
parents: 83
diff changeset
850 def do__dir_indexes(self, arg, opts):
83
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
851 if opts.all:
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
852 which_view = (', owner', 'all')
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
853 else:
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
854 which_view = ('', 'user')
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
855 self.do_select("""index_name, index_type%s FROM %s_indexes WHERE index_name LIKE '%%%s%%' OR table_name LIKE '%%%s%%'""" %
5701fb63e81d trying --all option for dt, di, etc.
catherine@cordelia
parents: 82
diff changeset
856 (which_view[0], which_view[1], arg.upper(), arg.upper()))
61
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
857
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
858 def do__dir_tablespaces(self, arg):
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
859 self.do_select("""tablespace_name, file_name from dba_data_files""")
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
860
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
861 def do__dir_schemas(self, arg):
1eefe17b3630 adding more psql \d commands
catherine@cordelia
parents: 58
diff changeset
862 self.do_select("""owner, count(*) AS objects FROM all_objects GROUP BY owner ORDER BY owner""")
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
863
62
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
864 def do_head(self, arg):
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
865 nrows = 10
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
866 args = arg.split()
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
867 if len(args) > 1:
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
868 for a in args:
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
869 if a[0] == '-':
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
870 try:
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
871 nrows = int(a[1:])
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
872 args.remove(a)
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
873 except:
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
874 pass
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
875 arg = ' '.join(args)
833e9d251da3 add head
catherine@cordelia
parents: 61
diff changeset
876 self.do_select('* from %s;%d' % (arg, nrows))
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
877
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
878 def do_print(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
879 'print VARNAME: Show current value of bind variable VARNAME.'
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
880 if arg:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
881 if arg[0] == ':':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
882 arg = arg[1:]
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
883 try:
95
84a26822e28c was crashing on print
catherine@cordelia
parents: 94
diff changeset
884 self.stdout.write(str(self.binds[arg])+'\n')
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
885 except KeyError:
46
ad41f6a577f7 think terminators are working now
catherine.devlin@gmail.com
parents: 45
diff changeset
886 self.stdout.write('No bind variable %s\n' % arg)
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
887 else:
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
888 for (var, val) in self.binds.items():
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
889 print ':%s = %s' % (var, val)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
890
122
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
891 assignmentScanner = Parser(pyparsing.Literal(':=') ^ '=')
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
892 def do_setbind(self, arg):
121
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
893 arg = self.parsed(arg).unterminated
122
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
894 try:
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
895 assigner, startat, endat = self.assignmentScanner.scanner.scanString(arg).next()
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
896 except StopIteration:
72
db0e1ff5e407 condensed do_setbind with do_print
catherine@cordelia
parents: 71
diff changeset
897 self.do_print(arg)
118
0776ceacfc79 send up
catherine@Elli.myhome.westell.com
parents: 117
diff changeset
898 return
122
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
899 var, val = arg[:startat].strip(), arg[endat:].strip()
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
900 if val[0] == val[-1] == "'" and len(val) > 1:
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
901 self.binds[var] = val[1:-1]
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
902 return
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
903 try:
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
904 self.binds[var] = int(val)
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
905 return
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
906 except ValueError:
74
bfc81b9b99a7 sql setbinds partially done?
catherine@cordelia
parents: 73
diff changeset
907 try:
122
61e2a824b66b wow, assignment from function call working?
catherine@Elli.myhome.westell.com
parents: 121
diff changeset
908 self.binds[var] = float(val)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
909 return
74
bfc81b9b99a7 sql setbinds partially done?
catherine@cordelia
parents: 73
diff changeset
910 except ValueError:
123
898ed97bec38 fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents: 122
diff changeset
911 statekeeper = Statekeeper(self, ('autobind',))
898ed97bec38 fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents: 122
diff changeset
912 self.autobind = True
898ed97bec38 fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents: 122
diff changeset
913 self.do_select('%s AS %s FROM dual;' % (val, var))
898ed97bec38 fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents: 122
diff changeset
914 statekeeper.restore()
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
915
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
916 def do_exec(self, arg):
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
917 if arg[0] == ':':
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
918 self.do_setbind(arg[1:])
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
919 else:
123
898ed97bec38 fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents: 122
diff changeset
920 arg = self.parsed(arg).unterminated
119
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
921 varsUsed = findBinds(arg, self.binds, {})
71
7bb52cc35332 before reworking setbind
catherine@cordelia
parents: 70
diff changeset
922 try:
119
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
923 self.curs.execute('begin\n%s;end;' % arg, varsUsed)
71
7bb52cc35332 before reworking setbind
catherine@cordelia
parents: 70
diff changeset
924 except Exception, e:
7bb52cc35332 before reworking setbind
catherine@cordelia
parents: 70
diff changeset
925 print e
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
926
123
898ed97bec38 fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents: 122
diff changeset
927 '''
96
5e274ecfd679 testing for pl/sql usage
catherine@cordelia
parents: 95
diff changeset
928 Fails:
123
898ed97bec38 fixed bug in setting parameters
catherine@Elli.myhome.westell.com
parents: 122
diff changeset
929 select n into :n from test;'''
96
5e274ecfd679 testing for pl/sql usage
catherine@cordelia
parents: 95
diff changeset
930
70
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
931 def anon_plsql(self, line1):
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
932 lines = [line1]
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
933 while True:
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
934 line = self.pseudo_raw_input(self.continuationPrompt)
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
935 if line.strip() == '/':
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
936 try:
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
937 self.curs.execute('\n'.join(lines))
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
938 except Exception, e:
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
939 print e
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
940 return
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
941 lines.append(line)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
942
70
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
943 def do_begin(self, arg):
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
944 self.anon_plsql('begin ' + arg)
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
945
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
946 def do_declare(self, arg):
13edc2731d6b begin, declare
catherine@cordelia
parents: 65
diff changeset
947 self.anon_plsql('declare ' + arg)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
948
87
2de82dd6eba2 back to dellzilla
catherine@cordelia
parents: 86
diff changeset
949 #def do_create(self, arg):
2de82dd6eba2 back to dellzilla
catherine@cordelia
parents: 86
diff changeset
950 # self.anon_plsql('create ' + arg)
78
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
951
119
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
952 @options([make_option('-l', '--long', action='store_true', help='long descriptions'),
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
953 make_option('-a', '--all', action='store_true', help="all schemas' objects")])
81
32c868fca272 midway through converting to optparse
catherine@localhost
parents: 80
diff changeset
954 def do_ls(self, arg, opts):
78
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
955 where = ''
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
956 if arg:
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
957 where = """\nWHERE object_type || '/' || object_name
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
958 LIKE '%%%s%%'""" % (arg.upper().replace('*','%'))
78
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
959 else:
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
960 where = ''
119
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
961 if opts.all:
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
962 owner = 'owner'
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
963 whose = 'all'
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
964 else:
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
965 owner = "'' AS owner"
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
966 whose = 'user'
78
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
967 result = []
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
968 statement = '''SELECT object_type, object_name,
119
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
969 status, last_ddl_time, %s
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
970 FROM %s_objects %s
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
971 ORDER BY object_type, object_name''' % (owner, whose, where)
78
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
972 self.curs.execute(statement)
119
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
973 for (object_type, object_name, status, last_ddl_time, owner) in self.curs.fetchall():
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
974 if opts.all:
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
975 qualified_name = '%s.%s' % (owner, object_name)
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
976 else:
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
977 qualified_name = object_name
81
32c868fca272 midway through converting to optparse
catherine@localhost
parents: 80
diff changeset
978 if opts.long:
119
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
979 result.append('%s\t%s\t%s/%s' % (status, last_ddl_time, object_type, qualified_name))
78
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
980 else:
119
catherine@Elli.myhome.westell.com
parents: 118
diff changeset
981 result.append('%s/%s' % (object_type, qualified_name))
78
8529876f7541 ls working
catherine@cordelia
parents: 77
diff changeset
982 self.stdout.write('\n'.join(result) + '\n')
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
983
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
984 def do_cat(self, arg):
128
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
985 '''cat TABLENAME --> SELECT * FROM equivalent'''
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
986 targets = arg.split()
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
987 for target in targets:
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
988 self.do_select('* from %s' % target)
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
989
86
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
990 @options([make_option('-i', '--ignore-case', dest='ignorecase', action='store_true', help='Case-insensitive search')])
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
991 def do_grep(self, arg, opts):
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
992 """grep PATTERN TABLE - search for term in any of TABLE's fields"""
128
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
993
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
994 arg = self.parsed(arg)
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
995 targetnames = arg.unterminated.split()
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
996 pattern = targetnames.pop(0)
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
997 targets = []
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
998 for target in targetnames:
86
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
999 if '*' in target:
128
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
1000 self.curs.execute("SELECT owner, table_name FROM all_tables WHERE table_name LIKE '%s'%s" %
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
1001 (target.upper().replace('*','%')), arg.terminator)
86
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
1002 for row in self.curs:
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
1003 targets.append('%s.%s' % row)
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
1004 else:
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
1005 targets.append(target)
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
1006 for target in targets:
86
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
1007 print target
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
1008 target = target.rstrip(';')
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
1009 sql = []
7
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
1010 try:
128
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
1011 self.curs.execute('select * from %s where 1=0' % target) # just to fill description
86
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
1012 if opts.ignorecase:
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
1013 sql = ' or '.join("LOWER(%s) LIKE '%%%s%%'" % (d[0], pattern.lower()) for d in self.curs.description)
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
1014 else:
ca5d615d8207 hmmm, super-big grep
catherine@localhost
parents: 85
diff changeset
1015 sql = ' or '.join("%s LIKE '%%%s%%'" % (d[0], pattern) for d in self.curs.description)
7
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
1016 sql = '* FROM %s WHERE %s' % (target, sql)
128
c5e6e475cdbe allow terminators in grep
catherine@localhost
parents: 127
diff changeset
1017 self.do_select('%s%s%s' % (sql, arg.terminator, arg.rowlimit))
7
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
1018 except Exception, e:
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
1019 print e
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
1020 import traceback
d44784122203 more history tweaks
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 6
diff changeset
1021 traceback.print_exc(file=sys.stdout)
4
23c3a58d7804 about to strip out tselect
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 3
diff changeset
1022
10
2ef0e2608123 reworking pull
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 8
diff changeset
1023 def do_refs(self, arg):
121
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
1024 arg = self.parsed(arg).unterminated.upper()
3dd852ab45c0 fixing terminator stripping
catherine@Elli.myhome.westell.com
parents: 120
diff changeset
1025 object_type, owner, object_name = self.resolve(arg)
40
1fb9f7dee7d8 tearing out cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 39
diff changeset
1026 if object_type == 'TABLE':
1fb9f7dee7d8 tearing out cmd2
devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil
parents: 39
diff changeset
1027 self.do_select(queries['refs'],bindVarsIn={'object_name':object_name, 'owner':owner})
131
2b7ce838120d experimenting with completion
catherine@Elli.myhome.westell.com
parents: 130
diff changeset
1028
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
1029 def _test():
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
1030 import doctest
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
1031 doctest.testmod()
80
83de0cb04f12 prevent crash on lone backslash
catherine@localhost
parents: 79
diff changeset
1032
0
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
1033 if __name__ == "__main__":
9c87fa772ec1 before big refactor
catherine@serenity.wpafb.af.mil
parents:
diff changeset
1034 "Silent return implies that all unit tests succeeded. Use -v to see details."
109
38ee1ca92801 version 1.4.6.1
catherine@Elli.myhome.westell.com
parents: 106
diff changeset
1035 _test()