Loop SQLite query for many databases in one directory to sort positive hits (UNIX)

Question

I have a directory with lots and lots of SQLite database files.

I need to run an sqlite3 query to establish if any database in my directory contains a specific table. Database filenames go like 001.db, 002.db, etc. If a database contains the table I require, the file is copied to a different directory.

My initial thoughts are similar to this:

while [ sqlite3 $* "SELECT * FROM table1" != "Error: no such table: table1" ]`

do

cp $* newdir/

done

or something similar, and I need some help with correct syntaxis please...

in "$*" I meant a database file currently being processed inside the directory, not sure if it is correct...

score 2 · Accepted Answer · edited May 23 '17 at 11:49

First, you don't need to do a select in a table to see if it exists. You could run something like:

SELECT * FROM dbname.sqlite_master WHERE type='table';

or adapt it to do a count:

SELECT count(*) FROM dbname.sqlite_master WHERE type='table' and name='table_name';

Using the sqlite command line, you should get a "1" or a "0". You can use that in figuring out if the DB actually has your desired table or not and act accordingly. The script could look something like:

for i in `ls *.db`; do
    HAS_TABLE=`sqlite "${i}" "select count(*) ....;"
    if [[ ${HAS_TABLE} == "1" ]]; then
      cp ${i} some/other/dir/${i}
    fi
done

I'm writing this from memory, so the syntax of the if/condition may be off a bit (you might be ok without the quotes around the 1 value).

HTH,

Thank you. I'm mainly concerned with performing a loop over the command to copy databases with positive hits to a new directory. — Sazzy, Feb 14 '13 at 15:35

Loop SQLite query for many databases in one directory to sort positive hits (UNIX)

1 Answers1