Extending UNIX grep to databases -

Extending UNIX grep to databases

Figure 952. UNIX grep sample

> 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.

The grep command among with its myriad of options allows searching text files for the occurrence of strings or patterns. This can be extended both to RDBMS and non-SQL databases like Mongodb.

The database query problem however is more complex than simply query lines of text files. A practically useful CLI must address:

Figure 953. Database grep requirements

Connection profile handling

Search level specification:

Whole database
Table(s)

Table column(s)
Recordsets

Output formatting, limiting/filtering and paging

Figure 954. Connection profile handling

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 955. Search level specification

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 956. Property based searches

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. Depending on the corresponding search level we distinguish: Database Search all tables for compatible integer columns. Return either a list of all tables containing at least one occurrence of 237 or the data records themselves depending on an output formatting rule,
❷
❸
❹
❺

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

Project ideas

Extending UNIX grep to databases