Tcl_script_find

Definition

Language:	Tcl
Plugin for language:	ScriptingTcl
How to use:	Create custom SQL function. Suggested name: find
Function arguments	only 1, a string
Function usage:	``` sql

                    SELECT find('my_custom_id')                                                                                                                                                                                                                                                                                                                                                                                                                                       
                    ```                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |

| Description: | This script goes through all tables in the database, until it finds table with given string value in any cell of the table. After string was matched, it doesn't look further. It expects to find exactly the value passed as argument. The cell cannot contain any prefix or suffix. Once the table is found, function returns table name and a comma-separated list of ROWIDs in which the value was spotted. If no table was matched, then string "not found" is returned.

                    Note, that this function can be very slow, cause it does a full scan of all tables (until matched), but it still can help out in many situations.                                                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                    It also works for "WITHOUT ROWID" tables, but for those it doesn't return actual ROWIDs (as there are none), it returns only the table name.                                                                                                                                                                                                                                                                                                                                       |

Code

set value [string map [list "'" "''"] [lindex $argv 0]]
foreach table [db eval {select name from sqlite_master where type = "table"}] {
    set table $table
    set cols [list]
    foreach {cid colName colType colNN colDef colPk} [db eval "PRAGMA table_info('$table')"] {
        lappend cols "\[$colName\] = '$value'"
    }
    if {[catch {
        set res [db eval "SELECT rowid FROM \[$table\] WHERE [join $cols { OR }]"]
        if {[llength $res] > 0} {
            return "found in table $table in rows with following ROWID: [join $res ,\ ]"
        }
    }]} {
        set res [db eval "SELECT count(*) FROM \[$table\] WHERE [join $cols { OR }]"]
        if {[lindex $res 0] > 0} {
            return "found in table $table"
        }
    }
}
return "not found"