Extending UNIX grep to databases

Figure 980. UNIX grep sample Slide presentation 
> grep --color ❶ -i ❷ fraction App.java
package de.hdm_stuttgart.mi.sd1.fraction;
 * Playing with fraction objects.
    final Fraction threeSeven = 
        new Fraction(3, 7);
    final Fraction

❶

Colorize matched text in output

❷

Ignore case when matching text.

Figure 981. Similarities and differences Slide presentation

  • Database queries are more complex than matching text.

  • Support for Nosql databases, e.g. Mongodb.

  • Customer demands:

    • Restrict input record sets.

    • Filter / page output.


Figure 982. Database grep requirements Slide presentation

  • Connection profile handling

  • Search level specification:

    • Whole database

    • Table(s)

    • Table column(s)

    • Recordsets

  • Output formatting, limiting/filtering and paging


Figure 983. Connection profile handling Slide presentation
Command File ~/.dbgrep/Profiles/postgresTest.cfg 
dbgrep --profile postgresTest ...
 
host=myserver.companynet.com
port=5432
database=main_test_data
user=testuser
password=secret
driver=Driver/postgresql-42.6.0.jar

Figure 984. Search level specification Slide presentation 
dbgrep ... ❶
dbgrep ... --table User --table Stocks ❷...
dbgrep ... --column User.userId ❸ ...
dbgrep ... --table Stocks --column User.userId ... ❹

❶

Search whole database i.e. all tables (and views?).

❷

Search all compatible columns from tables User and Stocks.

❸

Search column userId from table User.

❹

Combined search of ❷ and ❸.


Figure 985. Property based searches Slide presentation 
dbgrep ... --equal 237 ❶
dbgrep ... --greater 4.43 ❷
dbgrep ... --like 'Smit%' ❸
dbgrep ... --like 'Smit%' --and --greater 4 ❹
dbgrep ... --range [-3:17] ❺

❶

Search for integer values equal to 237. Return either of:

  • All records from all tables containing an integer type value of 237.

  • A list of all tables among with column names of integer type containing at least one value of 237 but not the records themselves. This resembles the grep -l ... aka --files-with-matches argument.

❷

Search for numeric values being greater than 4.43.

❸

Texts starting with Smit.

❹

Conjunction: Records containing text starting with Smit and at least one numerical value being greater than 4.

❺

Search for integer values between and including -3 and 17.


A tool might be implemented either as a single command or as a terminal based application keeping state information.

Prev  Up  Next
Project ideas  Home  Database copy tool