sqlpython: sqlpyPlus.py annotate

annotate sqlpyPlus.py @ 79:01d578f4e6e7

prevent crash when pull unresolved

author	catherine@DellZilla.myhome.westell.com
date	Mon, 28 Apr 2008 12:20:53 -0400
parents	8529876f7541
children	83de0cb04f12

rev	line source
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	1 """sqlpyPlus - extra features (inspired by Oracle SQL*Plus) for Luca Canali's sqlpython.py
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	2
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	3 Features include:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	4 - SQL*Plus-style bind variables
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	5 - Query result stored in special bind variable ":_" if one row, one item
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	6 - SQL buffer with list, run, ed, get, etc.; unlike SQL*Plus, buffer stores session's full history
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	7 - @script.sql loads and runs (like SQL*Plus)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	8 - ! runs operating-system command
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	9 - show and set to control sqlpython parameters
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	10 - SQL*Plus-style describe, spool
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	11 - write sends query result directly to file
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	12 - comments shows table and column comments
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	13 - compare ... to ... graphically compares results of two queries
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	14 - commands are case-insensitive
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	15
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	16 Use 'help' within sqlpython for details.
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	17
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	18 Compatible with sqlpython v1.3
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	19
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	20 Set bind variables the hard (SQL*Plus) way
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	21 exec :b = 3
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	22 or with a python-like shorthand
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	23 :b = 3
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	24
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	25 - catherinedevlin.blogspot.com May 31, 2006
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	26 """
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	27 # note in cmd.cmd about supporting emacs commands?
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	28
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	29 descQueries = {
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	30 'TABLE': ("""
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	31 atc.column_name,
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	32 CASE atc.nullable WHEN 'Y' THEN 'NULL' ELSE 'NOT NULL' END "Null?",
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	33 atc.data_type \|\|
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	34 CASE atc.data_type WHEN 'DATE' THEN ''
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	35 ELSE '(' \|\|
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	36 CASE atc.data_type WHEN 'NUMBER' THEN TO_CHAR(atc.data_precision) \|\|
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	37 CASE atc.data_scale WHEN 0 THEN ''
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	38 ELSE ',' \|\| TO_CHAR(atc.data_scale) END
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	39 ELSE TO_CHAR(atc.data_length) END
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	40 END \|\|
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	41 CASE atc.data_type WHEN 'DATE' THEN '' ELSE ')' END
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	42 data_type
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	43 FROM all_tab_columns atc
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	44 WHERE atc.table_name = :object_name
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	45 AND atc.owner = :owner
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	46 ORDER BY atc.column_id;""",),
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	47 'PROCEDURE': ("""
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	48 argument_name,
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	49 data_type,
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	50 in_out,
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	51 default_value
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	52 FROM all_arguments
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	53 WHERE object_name = :object_name
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	54 AND owner = :owner
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	55 AND package_name IS NULL
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	56 AND argument_name IS NOT NULL
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	57 ORDER BY sequence;""",),
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	58 'PackageObjects':("""
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	59 SELECT DISTINCT object_name
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	60 FROM all_arguments
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	61 WHERE package_name = :package_name
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	62 AND owner = :owner""",),
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	63 'PackageObjArgs':("""
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	64 object_name,
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	65 argument_name,
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	66 data_type,
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	67 in_out,
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	68 default_value
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	69 FROM all_arguments
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	70 WHERE package_name = :package_name
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	71 AND object_name = :object_name
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	72 AND owner = :owner
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	73 AND argument_name IS NOT NULL
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	74 ORDER BY sequence""",),
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	75 'TRIGGER':("""
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	76 description
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	77 FROM all_triggers
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	78 WHERE owner = :owner
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	79 AND trigger_name = :object_name
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	80 """,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	81 """
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	82 table_owner,
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	83 base_object_type,
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	84 table_name,
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	85 column_name,
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	86 when_clause,
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	87 status,
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	88 action_type,
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	89 crossedition
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	90 FROM all_triggers
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	91 WHERE owner = :owner
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	92 AND trigger_name = :object_name
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	93 \\t
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	94 """,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	95 ),
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	96 'INDEX':("""
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	97 index_type,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	98 table_owner,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	99 table_name,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	100 table_type,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	101 uniqueness,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	102 compression,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	103 partitioned,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	104 temporary,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	105 generated,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	106 secondary,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	107 dropped,
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	108 visibility
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	109 FROM all_indexes
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	110 WHERE owner = :owner
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	111 AND index_name = :object_name
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	112 \\t
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	113 """,)
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	114 }
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	115 descQueries['VIEW'] = descQueries['TABLE']
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	116 descQueries['FUNCTION'] = descQueries['PROCEDURE']
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	117
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	118 queries = {
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	119 'resolve': """
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	120 SELECT object_type, object_name, owner FROM (
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	121 SELECT object_type, object_name, user owner, 1 priority
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	122 FROM user_objects
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	123 WHERE object_name = :objName
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	124 UNION ALL
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	125 SELECT ao.object_type, ao.object_name, ao.owner, 2 priority
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	126 FROM all_objects ao
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	127 JOIN user_synonyms us ON (us.table_owner = ao.owner AND us.table_name = ao.object_name)
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	128 WHERE us.synonym_name = :objName
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	129 AND ao.object_type != 'SYNONYM'
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	130 UNION ALL
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	131 SELECT ao.object_type, ao.object_name, ao.owner, 3 priority
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	132 FROM all_objects ao
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	133 JOIN all_synonyms asyn ON (asyn.table_owner = ao.owner AND asyn.table_name = ao.object_name)
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	134 WHERE asyn.synonym_name = :objName
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	135 AND ao.object_type != 'SYNONYM'
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	136 AND asyn.owner = 'PUBLIC'
6 f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	137 UNION ALL
f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	138 SELECT 'DIRECTORY' object_type, dir.directory_name, dir.owner, 6 priority
f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	139 FROM all_directories dir
f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	140 WHERE dir.directory_name = :objName
f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	141 UNION ALL
f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	142 SELECT 'DATABASE LINK' object_type, db_link, owner, 7 priority
f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	143 FROM all_db_links dbl
f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	144 WHERE dbl.db_link = :objName
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	145 ) ORDER BY priority ASC""",
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,
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 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': """
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	161 NULL referenced_by,
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	162 c2.table_name references,
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	163 c1.constraint_name constraint
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	164 FROM
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	165 user_constraints c1,
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	166 user_constraints c2
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	167 WHERE
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	168 c1.table_name = :object_name
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	169 and c1.constraint_type ='R'
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	170 and c1.r_constraint_name = c2.constraint_name
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	171 and c1.r_owner = c2.owner
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	172 and c1.owner = :owner
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,
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	175 NULL references,
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	176 c1.constraint_name constraint
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	177 FROM
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	178 user_constraints c1,
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	179 user_constraints c2
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	180 WHERE
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	181 c2.table_name = :object_name
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	182 and c1.constraint_type ='R'
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	183 and c1.r_constraint_name = c2.constraint_name
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	184 and c1.r_owner = c2.owner
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	185 and c1.owner = :owner
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
39 5d29e6a21c6f ready to package cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 34 diff changeset	189 import sys, os, re, sqlpython, cx_Oracle, pyparsing
60 4f80329a1905 loading fix catherine.devlin@gmail.com parents: 58 diff changeset	190 from cmd2 import flagReader, Cmd
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)
12 7d27bb74ba90 holy cow, pull -f works devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 11 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 """
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	215
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	216 Using %s. Note that there are better options available for %s,
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	217 but %s couldn't find a better one in your PATH.
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	218 Feel free to open up %s
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	219 and customize it to find your favorite %s program.
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	220
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	221 """ % (software, self.purpose, __file__, __file__, self.purpose)
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,
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	228 or customize %s to make it aware of yours.
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	229 Looked for these programs:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	230 %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	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': [
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	235 ('/usr/bin/meld',"%s %s %s"),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	236 ('/usr/bin/kdiff3',"%s %s %s"),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	237 (r'C:\Program Files\Araxis\Araxis Merge v6.5\Merge.exe','"%s" %s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	238 (r'C:\Program Files\TortoiseSVN\bin\TortoiseMerge.exe', '"%s" /base:"%s" /mine:"%s"'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	239 ('FileMerge','%s %s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	240 ('kompare','%s %s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	241 ('WinMerge','%s %s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	242 ('xxdiff','%s %s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	243 ('fldiff','%s %s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	244 ('gtkdiff','%s %s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	245 ('tkdiff','%s %s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	246 ('gvimdiff','%s %s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	247 ('diff',"%s %s %s"),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	248 (r'c:\windows\system32\comp.exe',"%s %s %s")],
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	249 'text editor': [
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	250 ('gedit', '%s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	251 ('textpad', '%s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	252 ('notepad.exe', '%s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	253 ('pico', '%s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	254 ('emacs', '%s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	255 ('vim', '%s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	256 ('vi', '%s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	257 ('ed', '%s %s'),
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	258 ('edlin', '%s %s')
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	259 ]
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
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	327 pipeSeparator = Parser(pyparsing.SkipTo((pyparsing.Literal('\|') ^ pyparsing.StringEnd()), include=True), retainSeparator=False)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	328 bindScanner = Parser(pyparsing.Literal(':') + pyparsing.Word( pyparsing.alphanums + "_$#" ))
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	329 commandSeparator = Parser(pyparsing.SkipTo((pyparsing.Literal(';') ^ pyparsing.StringEnd()), include=True))
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	330
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	331 def findBinds(target, existingBinds, givenBindVars = {}):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	332 result = givenBindVars
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	333 for finding, startat, endat in bindScanner.scanner.scanString(target):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	334 varname = finding[1]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	335 try:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	336 result[varname] = existingBinds[varname]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	337 except KeyError:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	338 if not givenBindVars.has_key(varname):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	339 print 'Bind variable %s not defined.' % (varname)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	340 return result
3 cd23cd62de3c history working pretty well devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 2 diff changeset	341
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	342 class sqlpyPlus(sqlpython.sqlpython):
21 8b55aaa52ce9 working on load, and preserving stdin/out devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 20 diff changeset	343 defaultExtension = 'sql'
42 05c20d6bcec4 picking up from hiatus devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 41 diff changeset	344 shortcuts = {'?': 'help', '@': 'getrun', '!': 'shell', ':': 'setbind', '\\': 'psql'}
21 8b55aaa52ce9 working on load, and preserving stdin/out devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 20 diff changeset	345 multilineCommands = '''select insert update delete tselect
8b55aaa52ce9 working on load, and preserving stdin/out devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 20 diff changeset	346 create drop alter'''.split()
42 05c20d6bcec4 picking up from hiatus devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 41 diff changeset	347 defaultFileName = 'afiedt.buf'
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	348 def __init__(self):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	349 sqlpython.sqlpython.__init__(self)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	350 self.binds = CaselessDict()
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	351 self.sqlBuffer = []
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	352 self.settable = ['maxtselctrows', 'maxfetch', 'autobind', 'failover', 'timeout'] # settables must be lowercase
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	353 self.stdoutBeforeSpool = sys.stdout
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	354 self.spoolFile = None
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	355 self.autobind = False
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	356 self.failover = False
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	357 def default(self, arg, do_everywhere=False):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	358 sqlpython.sqlpython.default(self, arg, do_everywhere)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	359 self.sqlBuffer.append(self.query)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	360
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	361 # overrides cmd's parseline
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	362 def parseline(self, line):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	363 """Parse the line into a command name and a string containing
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	364 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	365 '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	366 Overrides cmd.cmd.parseline to accept variety of shortcuts.."""
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	367
41 33c9bc61db66 separation surgery successful? devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 40 diff changeset	368 cmd, arg, line = sqlpython.sqlpython.parseline(self, line)
20 d6d64c2e3b98 shortcuts devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 16 diff changeset	369 if cmd in ('select', 'sleect', 'insert', 'update', 'delete', 'describe',
14 1300a997d319 find works devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 13 diff changeset	370 'desc', 'comments', 'pull', 'refs', 'desc', 'triggers', 'find') \
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	371 and not hasattr(self, 'curs'):
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	372 print 'Not connected.'
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	373 return '', '', ''
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	374 return cmd, arg, line
1 8fa146b9a2d7 reworking multiline devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 0 diff changeset	375
7 d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	376 def onecmd_plus_hooks(self, line):
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	377 line = self.precmd(line)
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	378 stop = self.onecmd(line)
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	379 stop = self.postcmd(stop, line)
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	380
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	381 def do_shortcuts(self,arg):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	382 """Lists available first-character shortcuts
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	383 (i.e. '!dir' is equivalent to 'shell dir')"""
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	384 for (scchar, scto) in self.shortcuts.items():
58 de6278a3bf53 adding compare help catherine@cordelia parents: 57 diff changeset	385 print '%s: %s' % (scchar, scto)
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	386
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	387 def colnames(self):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	388 return [d[0] for d in curs.description]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	389
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	390 def sql_format_itm(self, itm, needsquotes):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	391 if itm is None:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	392 return 'NULL'
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	393 if needsquotes:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	394 return "'%s'" % str(itm)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	395 return str(itm)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	396 def output_as_insert_statements(self):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	397 usequotes = [d[1] != cx_Oracle.NUMBER for d in self.curs.description]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	398 def formatRow(row):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	399 return ','.join(self.sql_format_itm(itm, useq)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	400 for (itm, useq) in zip(row, usequotes))
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	401 result = ['INSERT INTO %s (%s) VALUES (%s);' %
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	402 (self.tblname, ','.join(self.colnames), formatRow(row))
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	403 for row in self.rows]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	404 return '\n'.join(result)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	405
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	406 def output_row_as_xml(self, row):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	407 result = [' <%s>\n %s\n </%s>' %
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	408 (colname.lower(), str('' if (itm is None) else itm), colname.lower())
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	409 for (itm, colname) in zip(row, self.colnames)]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	410 return '\n'.join(result)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	411 def output_as_xml(self):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	412 result = ['<%s>\n%s\n</%s>' %
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	413 (self.tblname, self.output_row_as_xml(row), self.tblname)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	414 for row in self.rows]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	415 return '\n'.join(result)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	416
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	417 def output_as_html_table(self):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	418 result = ''.join('<th>%s</th>' % c for c in self.colnames)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	419 result = [' <tr>\n %s\n </tr>' % result]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	420 for row in self.rows:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	421 result.append(' <tr>\n %s\n </tr>' %
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	422 (''.join('<td>%s</td>' %
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	423 str('' if (itm is None) else itm)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	424 for itm in row)))
1 8fa146b9a2d7 reworking multiline devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 0 diff changeset	425 result = '''<table id="%s">
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	426 %s
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	427 </table>''' % (self.tblname, '\n'.join(result))
1 8fa146b9a2d7 reworking multiline devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 0 diff changeset	428 return result
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	429
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	430 def output_as_list(self, align):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	431 result = []
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	432 colnamelen = max(len(colname) for colname in self.colnames) + 1
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	433 for (idx, row) in enumerate(self.rows):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	434 result.append('\n**** Row: %d' % (idx+1))
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	435 for (itm, colname) in zip(row, self.colnames):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	436 if align:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	437 colname = colname.ljust(colnamelen)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	438 result.append('%s: %s' % (colname, itm))
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	439 return '\n'.join(result)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	440
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	441 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	442 def output(self, outformat, rowlimit):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	443 self.tblname = self.tableNameFinder.search(self.curs.statement).group(1)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	444 self.colnames = [d[0] for d in self.curs.description]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	445 if outformat == '\\i':
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	446 result = self.output_as_insert_statements()
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	447 elif outformat == '\\x':
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	448 result = self.output_as_xml()
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	449 elif outformat == '\\g':
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	450 result = self.output_as_list(align=False)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	451 elif outformat == '\\G':
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	452 result = self.output_as_list(align=True)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	453 elif outformat in ('\\s', '\\S', '\\c', '\\C'): #csv
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	454 result = []
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	455 if outformat in ('\\s', '\\c'):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	456 result.append(','.join('"%s"' % colname for colname in self.colnames))
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	457 for row in self.rows:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	458 result.append(','.join('"%s"' % ('' if itm is None else itm) for itm in row))
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	459 result = '\n'.join(result)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	460 elif outformat == '\\h':
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	461 result = self.output_as_html_table()
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	462 elif outformat == '\\t':
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	463 rows = [self.colnames]
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	464 rows.extend(list(self.rows))
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	465 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	466 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	467 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	468 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	469 rname = transpr[x][0]
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	470 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	471 transpr[x][0] = rname
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	472 newdesc[0][0] = 'COLUMN NAME'
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	473 result = '\n' + sqlpython.pmatrix(transpr,newdesc)
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	474 else:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	475 result = sqlpython.pmatrix(self.rows, self.curs.description, self.maxfetch)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	476 return result
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	477
46 ad41f6a577f7 think terminators are working now catherine.devlin@gmail.com parents: 45 diff changeset	478 statementEndPattern = re.compile(r'(.)(;\|\\[gGhtxicCsS])\s(\d*)$', re.DOTALL \| re.MULTILINE)
45 71576bf684c8 before switch to stdout catherine.devlin@gmail.com parents: 43 diff changeset	479 # what about quote-enclosed?
71576bf684c8 before switch to stdout catherine.devlin@gmail.com parents: 43 diff changeset	480
41 33c9bc61db66 separation surgery successful? devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 40 diff changeset	481 def findTerminator(self, statement):
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	482 m = self.statementEndPattern.search(statement)
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	483 if m:
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	484 return m.groups()
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	485 else:
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	486 return statement, None, None
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	487
42 05c20d6bcec4 picking up from hiatus devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 41 diff changeset	488 legalOracle = re.compile('[a-zA-Z_$#]')
05c20d6bcec4 picking up from hiatus devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 41 diff changeset	489
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	490 def do_select(self, arg, bindVarsIn=None, override_terminator=None):
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	491 """Fetch rows from a table.
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	492
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	493 Limit the number of rows retrieved by appending
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	494 an integer after the terminator
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	495 (example: SELECT * FROM mytable;10 )
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	496
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	497 Output may be formatted by choosing an alternative terminator
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	498 ("help terminators" for details)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	499 """
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	500 bindVarsIn = bindVarsIn or {}
1 8fa146b9a2d7 reworking multiline devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 0 diff changeset	501 self.query = 'select ' + arg
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	502 (self.query, terminator, rowlimit) = self.findTerminator(self.query)
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	503 if override_terminator:
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	504 terminator = override_terminator
1 8fa146b9a2d7 reworking multiline devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 0 diff changeset	505 rowlimit = int(rowlimit or 0)
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	506 try:
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	507 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	508 self.curs.execute(self.query, self.varsUsed)
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	509 self.rows = self.curs.fetchmany(min(self.maxfetch, (rowlimit or self.maxfetch)))
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	510 self.desc = self.curs.description
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	511 self.rc = self.curs.rowcount
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	512 if self.rc > 0:
48 ce2b64554091 oops, output back to stdout catherine.devlin@gmail.com parents: 47 diff changeset	513 self.stdout.write('\n%s\n' % (self.output(terminator, rowlimit)))
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	514 if self.rc == 0:
47 46b7b31dc395 feedback should print, not stdout catherine.devlin@gmail.com parents: 46 diff changeset	515 print '\nNo rows Selected.\n'
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	516 elif self.rc == 1:
47 46b7b31dc395 feedback should print, not stdout catherine.devlin@gmail.com parents: 46 diff changeset	517 print '\n1 row selected.\n'
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	518 if self.autobind:
42 05c20d6bcec4 picking up from hiatus devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 41 diff changeset	519 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	520 if len(self.desc) == 1:
05c20d6bcec4 picking up from hiatus devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 41 diff changeset	521 self.binds['_'] = self.rows[0][0]
5 65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	522 elif self.rc < self.maxfetch:
47 46b7b31dc395 feedback should print, not stdout catherine.devlin@gmail.com parents: 46 diff changeset	523 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	524 else:
47 46b7b31dc395 feedback should print, not stdout catherine.devlin@gmail.com parents: 46 diff changeset	525 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	526 except Exception, e:
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	527 print e
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	528 import traceback
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	529 traceback.print_exc(file=sys.stdout)
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	530 self.sqlBuffer.append(self.query)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	531
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	532 pullflags = flagReader.FlagSet([flagReader.Flag('full')])
6 f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	533 def do_pull(self, arg):
14 1300a997d319 find works devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 13 diff changeset	534 """Displays source code.
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	535
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	536 --full, -f: get dependent objects as well"""
6 f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	537
12 7d27bb74ba90 holy cow, pull -f works devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 11 diff changeset	538 options, arg = self.pullflags.parse(arg)
6 f9ec5c20beb4 working on resolving more objects devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 5 diff changeset	539 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
79 01d578f4e6e7 prevent crash when pull unresolved catherine@DellZilla.myhome.westell.com parents: 78 diff changeset	540 if not object_type:
01d578f4e6e7 prevent crash when pull unresolved catherine@DellZilla.myhome.westell.com parents: 78 diff changeset	541 return
46 ad41f6a577f7 think terminators are working now catherine.devlin@gmail.com parents: 45 diff changeset	542 self.stdout.write("%s %s.%s\n" % (object_type, owner, object_name))
63 16618ff91c63 fixed pull bug catherine@cordelia parents: 62 diff changeset	543 self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DDL', cx_Oracle.CLOB,
16618ff91c63 fixed pull bug catherine@cordelia parents: 62 diff changeset	544 [object_type, object_name, owner])))
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	545 if options.has_key('full'):
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	546 for dependent_type in ('OBJECT_GRANT', 'CONSTRAINT', 'TRIGGER'):
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	547 try:
63 16618ff91c63 fixed pull bug catherine@cordelia parents: 62 diff changeset	548 self.stdout.write(str(self.curs.callfunc('DBMS_METADATA.GET_DEPENDENT_DDL', cx_Oracle.CLOB,
16618ff91c63 fixed pull bug catherine@cordelia parents: 62 diff changeset	549 [dependent_type, object_name, owner])))
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	550 except cx_Oracle.DatabaseError:
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	551 pass
10 2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	552
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	553 findflags = flagReader.FlagSet([flagReader.Flag('insensitive')])
14 1300a997d319 find works devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 13 diff changeset	554 def do_find(self, arg):
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	555 """Finds argument in source code.
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	556
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	557 --insensitive, -i: case-insensitive search"""
14 1300a997d319 find works devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 13 diff changeset	558
1300a997d319 find works devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 13 diff changeset	559 options, arg = self.findflags.parse(arg)
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	560 if options.has_key('insensitive'):
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	561 searchfor = "LOWER(text)"
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	562 arg = arg.lower()
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	563 else:
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	564 searchfor = "text"
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	565 self.do_select("* from all_source where %s like '%%%s%%'" % (searchfor, arg))
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	566
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	567 def do_describe(self, arg):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	568 "emulates SQL*Plus's DESCRIBE"
77 4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	569
4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	570 if not arg:
4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	571 self.do_select("""object_name, object_type FROM all_objects WHERE object_type IN ('TABLE','VIEW','INDEX') ORDER BY object_name""")
4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	572 return
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	573 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
77 4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	574 if not object_type:
4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	575 self.do_select("""object_name, object_type FROM all_objects
4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	576 WHERE object_type IN ('TABLE','VIEW','INDEX')
4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	577 AND object_name LIKE '%%%s%%'
4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	578 ORDER BY object_name""" % arg.upper() )
4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	579 return
46 ad41f6a577f7 think terminators are working now catherine.devlin@gmail.com parents: 45 diff changeset	580 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	581 descQ = descQueries.get(object_type)
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	582 if descQ:
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	583 for q in descQ:
65ae6cec71c6 expanded desc good so far devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 4 diff changeset	584 self.do_select(q,bindVarsIn={'object_name':object_name, 'owner':owner})
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	585 elif object_type == 'PACKAGE':
10 2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	586 self.curs.execute(descQueries['PackageObjects'][0], {'package_name':object_name, 'owner':owner})
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	587 packageContents = self.curs.fetchall()
10 2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	588 for (packageObj_name,) in packageContents:
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	589 print packageObj_name
10 2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	590 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	591 do_desc = do_describe
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	592
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	593 def do_comments(self, arg):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	594 'Prints comments on a table and its columns.'
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	595 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	596 if object_type:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	597 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	598 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	599 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	600
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	601 def resolve(self, identifier):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	602 """Checks (my objects).name, (my synonyms).name, (public synonyms).name
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	603 to resolve a database object's name. """
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	604 parts = identifier.split('.')
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	605 try:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	606 if len(parts) == 2:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	607 owner, object_name = parts
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	608 self.curs.execute('SELECT object_type FROM all_objects WHERE owner = :owner AND object_name = :object_name',
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	609 {'owner': owner, 'object_name': object_name})
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	610 object_type = self.curs.fetchone()[0]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	611 elif len(parts) == 1:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	612 object_name = parts[0]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	613 self.curs.execute(queries['resolve'], {'objName':object_name})
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	614 object_type, object_name, owner = self.curs.fetchone()
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	615 except TypeError:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	616 print 'Could not resolve object %s.' % identifier
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	617 object_type, owner, object_name = '', '', ''
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	618 return object_type, owner, object_name
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	619
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	620 def do_resolve(self, arg):
46 ad41f6a577f7 think terminators are working now catherine.devlin@gmail.com parents: 45 diff changeset	621 self.stdout.write(self.resolve(arg)+'\n')
ad41f6a577f7 think terminators are working now catherine.devlin@gmail.com parents: 45 diff changeset	622
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	623 def spoolstop(self):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	624 if self.spoolFile:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	625 sys.stdout = self.stdoutBeforeSpool
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	626 print 'Finished spooling to ', self.spoolFile.name
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	627 self.spoolFile.close()
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	628 self.spoolFile = None
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	629
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	630 def do_spool(self, arg):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	631 """spool [filename] - begins redirecting output to FILENAME."""
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	632 self.spoolstop()
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	633 arg = arg.strip()
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	634 if not arg:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	635 arg = 'output.lst'
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	636 if arg.lower() != 'off':
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	637 if '.' not in arg:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	638 arg = '%s.lst' % arg
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	639 print 'Sending output to %s (until SPOOL OFF received)' % (arg)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	640 self.spoolFile = open(arg, 'w')
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	641 sys.stdout = self.spoolFile
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	642
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	643 def write(self, arg, fname):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	644 originalOut = sys.stdout
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	645 f = open(fname, 'w')
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	646 sys.stdout = f
7 d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	647 self.onecmd_plus_hooks(arg)
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	648 f.close()
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	649 sys.stdout = originalOut
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	650
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	651 def do_write(self, args):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	652 'write [filename.extension] query - writes result to a file'
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	653 words = args.split(None, 1)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	654 if len(words) > 1 and '.' in words[0]:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	655 fname, command = words
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	656 else:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	657 fname, command = 'output.txt', args
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	658 self.write(command, fname)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	659 print 'Results written to %s' % os.path.join(os.getcwd(), fname)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	660
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	661 def do_compare(self, args):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	662 """COMPARE query1 TO query2 - uses external tool to display differences.
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	663
58 de6278a3bf53 adding compare help catherine@cordelia parents: 57 diff changeset	664 Sorting is recommended to avoid false hits.
de6278a3bf53 adding compare help catherine@cordelia parents: 57 diff changeset	665 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	666 if they are installed."""
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	667 fnames = []
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	668 args2 = args.split(' to ')
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	669 for n in range(len(args2)):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	670 query = args2[n]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	671 fnames.append('compare%s.txt' % n)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	672 if query.rstrip()[-1] != self.terminator:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	673 query = '%s%s' % (query, self.terminator)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	674 self.write(query, fnames[n])
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	675 diffMergeSearcher.invoke(fnames[0], fnames[1])
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	676
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	677 bufferPosPattern = re.compile('\d+')
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	678 rangeIndicators = ('-',':')
16 2776755a3a7e beginning separation of cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 15 diff changeset	679
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	680 def do_getrun(self, fname):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	681 'Brings SQL commands from a file to the in-memory SQL buffer, and executes them.'
60 4f80329a1905 loading fix catherine.devlin@gmail.com parents: 58 diff changeset	682 Cmd.do_load(self, fname)
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	683 def do_psql(self, arg):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	684 '''Shortcut commands emulating psql's backslash commands.
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	685
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	686 \c connect
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	687 \d desc
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	688 \e edit
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	689 \g run
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	690 \h help
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	691 \i getrun
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	692 \o spool
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	693 \p list
77 4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	694 \q quit
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	695 \w save
61 1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	696 \db _dir_tablespaces
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	697 \dd comments
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	698 \dn _dir_schemas
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	699 \dt _dir_tables
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	700 \dv _dir_views
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	701 \di _dir_indexes
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	702 \? help psql'''
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	703 commands = {}
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	704 for c in self.do_psql.__doc__.splitlines()[2:]:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	705 (abbrev, command) = c.split(None, 1)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	706 commands[abbrev[1:]] = command
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	707 words = arg.split(None,1)
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	708 abbrev = words[0]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	709 try:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	710 args = words[1]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	711 except IndexError:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	712 args = ''
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	713 try:
77 4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	714 return self.onecmd('%s %s' % (commands[abbrev], args))
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	715 except KeyError:
61 1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	716 print 'psql command \%s not yet supported.' % abbrev
77 4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	717
61 1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	718 def do__dir_tables(self, arg):
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	719 self.do_select("""table_name, 'TABLE' as type, owner FROM all_tables WHERE table_name LIKE '%%%s%%'""" % arg.upper())
77 4b5d4e5798dd \d with wildcard catherine@cordelia parents: 76 diff changeset	720
61 1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	721 def do__dir_views(self, arg):
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	722 self.do_select("""view_name, 'VIEW' as type, owner FROM all_views WHERE view_name LIKE '%%%s%%'""" % arg.upper())
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	723
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	724 def do__dir_indexes(self, arg):
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	725 self.do_select("""index_name, index_type, owner FROM all_indexes WHERE index_name LIKE '%%%s%%' OR table_name LIKE '%%%s%%'""" % (arg.upper(), arg.upper()))
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	726
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	727 def do__dir_tablespaces(self, arg):
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	728 self.do_select("""tablespace_name, file_name from dba_data_files""")
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	729
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	730 def do__dir_schemas(self, arg):
1eefe17b3630 adding more psql \d commands catherine@cordelia parents: 58 diff changeset	731 self.do_select("""owner, count(*) AS objects FROM all_objects GROUP BY owner ORDER BY owner""")
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	732
62 833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	733 def do_head(self, arg):
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	734 nrows = 10
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	735 args = arg.split()
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	736 if len(args) > 1:
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	737 for a in args:
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	738 if a[0] == '-':
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	739 try:
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	740 nrows = int(a[1:])
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	741 args.remove(a)
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	742 except:
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	743 pass
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	744 arg = ' '.join(args)
833e9d251da3 add head catherine@cordelia parents: 61 diff changeset	745 self.do_select('* from %s;%d' % (arg, nrows))
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	746
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	747 def do_print(self, arg):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	748 'print VARNAME: Show current value of bind variable VARNAME.'
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	749 if arg:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	750 if arg[0] == ':':
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	751 arg = arg[1:]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	752 try:
46 ad41f6a577f7 think terminators are working now catherine.devlin@gmail.com parents: 45 diff changeset	753 self.stdout.write(self.binds[arg]+'\n')
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	754 except KeyError:
46 ad41f6a577f7 think terminators are working now catherine.devlin@gmail.com parents: 45 diff changeset	755 self.stdout.write('No bind variable %s\n' % arg)
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	756 else:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	757 for (var, val) in self.binds.items():
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	758 print ':%s = %s' % (var, val)
74 bfc81b9b99a7 sql setbinds partially done? catherine@cordelia parents: 73 diff changeset	759
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	760 def do_setbind(self, arg):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	761 args = arg.split(None, 2)
72 db0e1ff5e407 condensed do_setbind with do_print catherine@cordelia parents: 71 diff changeset	762 if len(args) < 2:
db0e1ff5e407 condensed do_setbind with do_print catherine@cordelia parents: 71 diff changeset	763 self.do_print(arg)
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	764 elif len(args) > 2 and args[1] in ('=',':='):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	765 var, val = args[0], args[2]
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	766 if val[0] == val[-1] == "'" and len(val) > 1:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	767 val = val[1:-1]
74 bfc81b9b99a7 sql setbinds partially done? catherine@cordelia parents: 73 diff changeset	768 try:
bfc81b9b99a7 sql setbinds partially done? catherine@cordelia parents: 73 diff changeset	769 val = int(val)
bfc81b9b99a7 sql setbinds partially done? catherine@cordelia parents: 73 diff changeset	770 except ValueError:
bfc81b9b99a7 sql setbinds partially done? catherine@cordelia parents: 73 diff changeset	771 try:
bfc81b9b99a7 sql setbinds partially done? catherine@cordelia parents: 73 diff changeset	772 val = float(val)
bfc81b9b99a7 sql setbinds partially done? catherine@cordelia parents: 73 diff changeset	773 except ValueError:
bfc81b9b99a7 sql setbinds partially done? catherine@cordelia parents: 73 diff changeset	774 val = self.curs.callfunc(val, [])
bfc81b9b99a7 sql setbinds partially done? catherine@cordelia parents: 73 diff changeset	775 # submit to sql
75 2060f9e4b27b outstanding changes? catherine@cordelia parents: 74 diff changeset	776
71 7bb52cc35332 before reworking setbind catherine@cordelia parents: 70 diff changeset	777 self.binds[var] = val # but what if val is a function call?
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	778 else:
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	779 print 'Could not parse ', args
71 7bb52cc35332 before reworking setbind catherine@cordelia parents: 70 diff changeset	780
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	781 def do_exec(self, arg):
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	782 if arg[0] == ':':
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	783 self.do_setbind(arg[1:])
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	784 else:
71 7bb52cc35332 before reworking setbind catherine@cordelia parents: 70 diff changeset	785 try:
7bb52cc35332 before reworking setbind catherine@cordelia parents: 70 diff changeset	786 self.curs.execute('begin\n%s;end;' % arg)
7bb52cc35332 before reworking setbind catherine@cordelia parents: 70 diff changeset	787 except Exception, e:
7bb52cc35332 before reworking setbind catherine@cordelia parents: 70 diff changeset	788 print e
7bb52cc35332 before reworking setbind catherine@cordelia parents: 70 diff changeset	789 '''
7bb52cc35332 before reworking setbind catherine@cordelia parents: 70 diff changeset	790 exec :x := 'box'
7bb52cc35332 before reworking setbind catherine@cordelia parents: 70 diff changeset	791 exec :y := sysdate
7bb52cc35332 before reworking setbind catherine@cordelia parents: 70 diff changeset	792 '''
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	793
70 13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	794 def anon_plsql(self, line1):
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	795 lines = [line1]
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	796 while True:
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	797 line = self.pseudo_raw_input(self.continuationPrompt)
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	798 if line.strip() == '/':
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	799 try:
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	800 self.curs.execute('\n'.join(lines))
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	801 except Exception, e:
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	802 print e
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	803 return
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	804 lines.append(line)
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	805
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	806 def do_begin(self, arg):
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	807 self.anon_plsql('begin ' + arg)
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	808
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	809 def do_declare(self, arg):
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	810 self.anon_plsql('declare ' + arg)
13edc2731d6b begin, declare catherine@cordelia parents: 65 diff changeset	811
78 8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	812 def do_create(self, arg):
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	813 self.anon_plsql('create ' + arg)
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	814
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	815 lsflags = flagReader.FlagSet([flagReader.Flag('long')])
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	816 def do_ls(self, arg):
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	817 options, arg = self.lsflags.parse(arg)
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	818 where = ''
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	819 if arg:
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	820 where = """\nWHERE object_type \|\| '/' \|\| object_name
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	821 LIKE '%%%s%%'""" % (arg.upper().replace('*','%'))
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	822 else:
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	823 where = ''
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	824 result = []
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	825 statement = '''SELECT object_type, object_name,
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	826 status, last_ddl_time
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	827 FROM user_objects %s
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	828 ORDER BY object_type, object_name''' % (where)
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	829 self.curs.execute(statement)
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	830 for (object_type, object_name, status, last_ddl_time) in self.curs.fetchall():
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	831 if options.has_key('long'):
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	832 result.append('%s\t%s\t%s/%s' % (status, last_ddl_time, object_type, object_name))
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	833 else:
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	834 result.append('%s/%s' % (object_type, object_name))
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	835 self.stdout.write('\n'.join(result) + '\n')
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	836
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	837
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	838 if options.has_key('insensitive'):
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	839 searchfor = "LOWER(text)"
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	840 arg = arg.lower()
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	841
8529876f7541 ls working catherine@cordelia parents: 77 diff changeset	842
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	843 def do_cat(self, arg):
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	844 targets = arg.split()
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	845 for target in targets:
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	846 self.do_select('* from %s' % target)
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	847
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	848 def do_grep(self, arg):
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	849 """grep PATTERN TABLE - search for term in any of TABLE's fields"""
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	850 targets = arg.split()
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	851 pattern = targets.pop(0)
23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	852 for target in targets:
10 2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	853 target = target.rstrip(';')
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	854 sql = []
7 d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	855 try:
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	856 self.curs.execute('select * from %s where 1=0' % target)
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	857 sql = ' or '.join("%s LIKE '%%%s%%'" % (d[0], pattern) for d in self.curs.description)
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	858 sql = '* FROM %s WHERE %s' % (target, sql)
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	859 self.do_select(sql)
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	860 except Exception, e:
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	861 print e
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	862 import traceback
d44784122203 more history tweaks devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 6 diff changeset	863 traceback.print_exc(file=sys.stdout)
4 23c3a58d7804 about to strip out tselect devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 3 diff changeset	864
10 2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	865 def do_refs(self, arg):
2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	866 object_type, owner, object_name = self.resolve(arg.strip(self.terminator).upper())
40 1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	867 if object_type == 'TABLE':
1fb9f7dee7d8 tearing out cmd2 devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 39 diff changeset	868 self.do_select(queries['refs'],bindVarsIn={'object_name':object_name, 'owner':owner})
10 2ef0e2608123 reworking pull devlinjs@FA7CZA6N1254998.wrightpatterson.afmc.ds.af.mil parents: 8 diff changeset	869
0 9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	870 def _test():
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	871 import doctest
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	872 doctest.testmod()
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	873
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	874 if __name__ == "__main__":
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	875 "Silent return implies that all unit tests succeeded. Use -v to see details."
9c87fa772ec1 before big refactor catherine@serenity.wpafb.af.mil parents: diff changeset	876 _test()

Mercurial > sqlpython

annotate sqlpyPlus.py @ 79:01d578f4e6e7