# Oracle database string search
# FB - 20130726
import sys
import os
import csv
import re
import cx_Oracle

connection = raw_input("Enter Oracle DB connection (uid/pwd@database) : ")
searchStr = raw_input("Enter search string : ")
cs = raw_input("Case-sensitive search (y/n): ").lower()
rs = raw_input("Regex search (y/n): ").lower()
wc = raw_input("Whole column must match (y/n): ").lower()

if cs == 'n':
    caseSensitive = False
elif cs == 'y':
    caseSensitive = True
else:
    print "Wrong choice!"
    os._exit(1)

if rs == 'n':
    regexSearch = False
elif rs == 'y':
    regexSearch = True
else:
    print "Wrong choice!"
    os._exit(1)

if wc == 'n':
    wholeColumn = False
elif wc == 'y':
    wholeColumn = True
else:
    print "Wrong choice!"
    os._exit(1)

if regexSearch:
    if caseSensitive:
        searchStr = re.compile(searchStr)
    else:
        searchStr = re.compile(searchStr, re.IGNORECASE)

print2Screen = True
printHeader = True # include column headers in each table output

curs = cx_Oracle.connect(connection).cursor()

tables = curs.execute("select table_name from user_tables").fetchall()
for tableNameTuple in tables:
    tableName = tableNameTuple[0]
    print "Searching table: " + tableName
    sql = "select * from " + tableName
    curs.execute(sql)

    # search the table and create a list of rows that contains the search string
    matchedRows = []
    for row_data in curs:
        # print row_data
        for col in row_data:
            colStr = str(col).strip()

            if wholeColumn: # match if search string fully matches column value
                if regexSearch:
                    if searchStr.match(colStr):
                        matchedRows.append(row_data)
                else:
                    if caseSensitive:
                        if colStr == searchStr:
                            matchedRows.append(row_data)
                    else: # not case sensitive
                        if colStr.lower() == searchStr.lower():
                            matchedRows.append(row_data)
            else: # match if search string included anywhere in column value
                if regexSearch:
                    if searchStr.search(colStr):
                        matchedRows.append(row_data)
                else:
                    if caseSensitive:
                        if colStr.find(searchStr) >= 0:
                            matchedRows.append(row_data)
                    else: # not case sensitive
                        if colStr.lower().find(searchStr.lower()) >= 0:
                            matchedRows.append(row_data)
                
    # if some rows mathed then output them
    if len(matchedRows) > 0:
        print "Found match in table " + tableName
        if print2Screen:
            print matchedRows
            print

        # create a CSV file for the table that has any match   
        csv_file_dest = tableName + ".csv"
        outputFile = open(csv_file_dest,'w') # 'wb'
        output = csv.writer(outputFile, dialect='excel')

        if printHeader: # add column headers if requested
            cols = []
            for col in curs.description:
                cols.append(col[0])
            output.writerow(cols)

        for rows in matchedRows: # add table rows
            output.writerow(rows)

        outputFile.close()