<commit_before>= Schema Sleuth

A ruby script (for *nix) to search a database for related records based given a column name and value and recursively follows and outputs related rows. Attempts to avoid excessive recursion via related record row limit default of 1000 (otherwise can be slow and may run out of memory), a default max related table depth level of 5, etc.

== Installation

Install Git and Ruby.

Install RBI and Trollop:

  gem install dbi
  gem install trollop

Clone this project.

  cd ~
  git clone http://github.com/garysweaver/schema-sleuth.git

Then add the following to your .bash_profile, or whatever you want:

  #schema-sleuth
  export PATH=$PATH:~/schema-sleuth

Restart Terminal.app or whatever, and then test by doing:

  ssleuth -h
	
== Usage

  Connects to and spiders a database, outputting related records based on the column name and value provided.

  Examples:
  
  
  MySQL:
  
  ssleuth -r DBI:Mysql:TESTDB:localhost -u jdoe -p secret -c user_id -e 1234
  
  Oracle:
  
  ssleuth -r DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD -u jdoe -p secret -c user_id -e 1234
  
  Custom:
  
  ssleuth -r DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD -u jdoe -p secret -c user_id -e 1234 -t "select TABLE_NAME from ALL_ALL_TABLES" -n "select column_name FROM all_tab_cols where table_name = '$TABLE_NAME'"

  
  Usage:
  
         ssleuth [options]
         
  where [options] are:
  
          --driver-url, -r <s>:   DBI driver URL. Be sure to add .WORLD after the SID for Oracle. e.g. DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD
                --user, -u <s>:   database username. e.g. jdoe
            --password, -p <s>:   database password. e.g. secret
         --column-name, -c <s>:   column name. e.g. user_id
               --value, -e <s>:   column value. e.g. 1234
   --table-names-query, -t <s>:   SQL query to use to get full listing of tables. e.g. "select column_name FROM all_tab_cols where table_name = '$TABLE_NAME'"
  --column-names-query, -n <s>:   SQL query to use to get column names for a table. Substitute $TABLE_NAME for the table name. e.g. "select column_name FROM all_tab_cols where table_name =
                                  '$TABLE_NAME'"
        --generate-inserts, -i:   output as insert statements
       --maximum-depth, -s <i>:   maximum depth (default: 5)
   --maximum-row-count, -m <i>:   maximum row count. Does not attempt to follow path if more than this many rows are found from a query. However, you can still get more than one row in result
                                  summaries, via multiple queries (default: 1000)
                   --debug, -d:   outputs debugging information
                 --version, -v:   Print version and exit
                    --help, -h:   Show this message

== Examples

MySQL:

  ssleuth -r DBI:Mysql:TESTDB:localhost -u jdoe -p secret -c user_id -e 1234

Oracle:

  ssleuth -r DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD -u jdoe -p secret -c user_id -e 1234

Custom:

  ssleuth -r DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD -u jdoe -p secret -c user_id -e 1234 -t "select TABLE_NAME from ALL_ALL_TABLES" -n "select column_name FROM all_tab_cols where table_name = '$TABLE_NAME'"

== Sample Output

Example of normal output:
<commit_msg>added quiet switch to README<commit_after>= Schema Sleuth

A ruby script (for *nix) to search a database for related records based given a column name and value and recursively follows and outputs related rows. Attempts to avoid excessive recursion via related record row limit default of 1000 (otherwise can be slow and may run out of memory), a default max related table depth level of 5, etc.

== Installation

Install Git and Ruby.

Install RBI and Trollop:

  gem install dbi
  gem install trollop

Clone this project.

  cd ~
  git clone http://github.com/garysweaver/schema-sleuth.git

Then add the following to your .bash_profile, or whatever you want:

  #schema-sleuth
  export PATH=$PATH:~/schema-sleuth

Restart Terminal.app or whatever, and then test by doing:

  ssleuth -h
	
== Usage

  Connects to and spiders a database, outputting related records based on the column name and value provided.

  Examples:
  
  
  MySQL:
  
  ssleuth -r DBI:Mysql:TESTDB:localhost -u jdoe -p secret -c user_id -e 1234
  
  Oracle:
  
  ssleuth -r DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD -u jdoe -p secret -c user_id -e 1234
  
  Custom:
  
  ssleuth -r DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD -u jdoe -p secret -c user_id -e 1234 -t "select TABLE_NAME from ALL_ALL_TABLES" -n "select column_name FROM all_tab_cols where table_name = '$TABLE_NAME'"

  
  Usage:
  
         ssleuth [options]
         
  where [options] are:
  
          --driver-url, -r <s>:   DBI driver URL. Be sure to add .WORLD after the SID for Oracle. e.g. DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD
                --user, -u <s>:   database username. e.g. jdoe
            --password, -p <s>:   database password. e.g. <PASSWORD>
         --column-name, -c <s>:   column name. e.g. user_id
               --value, -e <s>:   column value. e.g. 1234
   --table-names-query, -t <s>:   SQL query to use to get full listing of tables. e.g. "select column_name FROM all_tab_cols where table_name = '$TABLE_NAME'"
  --column-names-query, -n <s>:   SQL query to use to get column names for a table. Substitute $TABLE_NAME for the table name. e.g. "select column_name FROM all_tab_cols where table_name =
                                  '$TABLE_NAME'"
        --generate-inserts, -i:   output as insert statements
       --maximum-depth, -s <i>:   maximum depth (default: 5)
   --maximum-row-count, -m <i>:   maximum row count. Does not attempt to follow path if more than this many rows are found from a query. However, you can still get more than one row in result
                                  summaries, via multiple queries (default: 1000)
                   --quiet, -q:   quiet. does not output progress indicator
                   --debug, -d:   outputs debugging information
                 --version, -v:   Print version and exit
                    --help, -h:   Show this message

== Examples

MySQL:

  ssleuth -r DBI:Mysql:TESTDB:localhost -u jdoe -p secret -c user_id -e 1234

Oracle:

  ssleuth -r DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD -u jdoe -p secret -c user_id -e 1234

Custom:

  ssleuth -r DBI:OCI8://db.acme.org:1234/ACMETEST.WORLD -u jdoe -p secret -c user_id -e 1234 -t "select TABLE_NAME from ALL_ALL_TABLES" -n "select column_name FROM all_tab_cols where table_name = '$TABLE_NAME'"

== Sample Output

Example of normal output:

{'fraction_non_alphanumeric': 0.0938496583143508, 'fraction_numerical': 0.019134396355353075, 'mean_word_length': 2.5144076840981855, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '592294', 'n_tokens_mistral': 2303, 'n_tokens_neox': 2132, 'n_words': 818}