Extending UNIX grep to databases -

Extending UNIX grep to databases

Figure 962. 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 963. Database grep requirements

Connection profile handling

Search level specification:

Whole database
Table(s)

Table column(s)
Recordsets

Output formatting, limiting/filtering and paging

Figure 964. 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 965. 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 966. 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. 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

Project ideas

Extending UNIX grep to databases