[SOLVED] Bash script: How to assign variable to an sqlite3 command with variable embedded?

ninja6o4 · 03-12-2013, 05:43 PM

Having trouble with a particular line here. I'm working on a script that grabs a list of files on an FTP server, and compares it to a list kept in an sqlite3 db so that it doesn't try to download the same file again.

Code:

  files=( $( cat list.txt ) )
  for file in "${files[@]}"
    do echo "Checking if item ${files[$count]} was already downloaded.."
	exists=$(sqlite3 sync.db "select count(*) from rememberedFiles where filename="?${files[$count]}"")
	if [ $exists > 0 ]
	then
	  echo "It exists!"
	else
	  echo "It doesn't exist!"
    fi	  
    ((count++))
  done

The contents of ${files[0]} is a folder: JT/
When I run the script, I get the error msg: Error: near "JT": syntax error

I know the / is the problem, but how do I correct this so that it knows to search for that folder name in the sqlite3 table?

EDIT: Thank you to everyone to contributed, I have fixed my code and (hopefully) cleaned it up a bit. The indenting is quite far because of the nesting I've done in the overall script. The final working code is in my final post here: http://www.linuxquestions.org/questi...9/#post4913830

rigor · 03-12-2013, 08:34 PM

ninja6o4,

With bash, there are various things you can do to find out what's happening, when things seem confusing.

One is to use the command:

Code:

set -x

to turn on "debugging output".

When you have a situation where a command is to be executed, often you can put "echo " before the command. That way the echo command is often able to output the result of how bash interprets what you've done.

ninja6o4 · 03-13-2013, 08:55 AM

Thanks for the tip. Here is the output:

Code:

++ sqlite3 sync.db 'select count(*) from rememberedFiles where filename=?JT/'
Error: near "JT": syntax error

I tried with/without quotation ("), and multiples ("") and (""") but nothing seems to let me encapsulate that filename with quotes. What am I missing here?

Edit: I found some documentation from the python code that I am basing this script on:

Code:

files=os.listdir(u""+remote_Dir) 
# If you call os.listdir() with a UTF-8 string the result will be an array of UTF-8 strings instead of ASCII. Needed for passing UTF-8 into sqlite3 for filenames with special characters.

Is this something I have to consider when trying to do the same thing, but from a text file?

rigor · 03-13-2013, 02:15 PM

ninja6o4,

First, realize that in general, with Unix-like systems, including Linux, commands that are intended to be given arguments then run to completion, often without User interaction, are generally set up so that the format of an error message is:

Code:

program_name:  error message

So for example, if from a shell command line, I issue a sleep command, and instead of providing to the sleep command, the expected numeric argument, the number of seconds, I provide some silly argument, like the word OK in all caps, I get this output from the sleep command:

Code:

sleep: invalid time interval `OK'

whereas a program that has it's own interactive command environment, won't necessarily output it's name first before its error messages, when you are running within the program's interactive command environment.

So, given that you don't see some command name prefixing the error message you get, it's very likely that the message is coming from sqlite3, not bash.

I'm no sqlite expert, but some years back, some aspects of trying to rearrange Firefox bookmarks a great deal through the Firefox GUI, could take quite a time. I discovered that for the large changes I wanted to make, I could exit Firefox, get into the sqlite3 Firefox databases manually, and easily and quickly make sweeping changes to my Firefox bookmark organization, with SQL.

I realize that the man page for the sqlite3 command, as well as the info document, both use constructs like ?phrase?. But I found I was able to make more sense of the sqlite SQL syntax if I went to the sqlite site, http://www.sqlite.org. To me, some of the SQL syntax, somewhat reminds me of T-SQL ( Transact SQL ) used by the likes of Sybase and Microsoft SQL Server.

Just to give you an example, I went into the directory with the Firefox databases, I entered into the sqlite3 interactive command environment like this:

Code:

sqlite3 extensions.sqlite

listed the tables in the DB:

Code:

sqlite> .tables 
addon              locale             targetApplication
addon_locale       locale_strings     targetPlatform   
sqlite>

I would generally recommend turning sqlite3 table column headers on:

Code:

.headers on

I poked around, got some idea of the structure of the addon table, and tried to get some data from it. This SQL syntax worked:

Code:

sqlite> select sourceUri from addon where sourceUri like "%scrap%";
sourceURI
http://releases.mozilla.org/pub/mozilla.org/addons/427/scrapbook-1.5.4-fx.xpi
sqlite>

Note the sourceURI column name in the output, the use of the % wildcards, and no question marks in the query.

I'm not sure if you have some specific reason for using the ? character, and maybe I'm missing the reason for it, but I'm wondering if you are using the question mark, because it's in the man page and/or info document on sqlite3. If you are looking for a specific value, you might try just using that value. If you need wildcards, I'd suggest going to the sqlite site to make sure what you need.

I hope that helps.

David the H. · 03-15-2013, 07:38 AM

To start with, this isn't recommended.

Code:

files=( $( cat list.txt ) )

This first loads the entire contents of the file into the command line, then uses shell word-splitting to break it up into array elements. If any file happens to have spaces in it, it will be broken up into multiple entries. In addition, any file with a valid globbing pattern will attempt to expand as well.

If you can guarantee that no filename can have embedded newlines in it, then you should use the new mapfile built-in instead. Otherwise you need some kind of a loop and a way to differentiate the delimiter from what's in the filenames.

Code:

mapfile -t files <list.txt

exists=$(sqlite3 sync.db "select count(*) from rememberedFiles where filename="?${files[$count]}"")

Have you determined that the above sqlite3 command does what you want it to do before putting it in the "$(..)" command substitution?

Note that you can't do nested quotes of the same type like this. Quotes act more like toggles than enclosures. The second quotemark acts as a closing quote, and the third one starts a new instance. This means that the text between them is completely unprotected from shell parsing. If you really need to have embedded quotation marks that get passed to the command, backslash-escape them instead.

Code:

exists=$(sqlite3 sync.db "select count(*) from rememberedFiles where filename=\"?${files[$count]}\"")

This works inside double quotes, as do a few other special escape patterns. See the bash man page section on quoting for details.

Moving on:

Code:

[ $exists > 0 ]

This is incorrect in a couple of ways. First, you should never leave quotes off a variable inside single-bracket tests (or just about anywhere else, for that matter), for the same word-splitting reason I mentioned.

http://mywiki.wooledge.org/Arguments
http://mywiki.wooledge.org/WordSplitting
http://mywiki.wooledge.org/Quotes

Second, ">" and its kin are string comparison operators. Inside bracket tests you need to use "-gt" and similar for numerical comparisons.

But finally, when using bash or ksh, it's recommended to use [[..]] for string/file tests, and ((..)) for numerical tests. Avoid using the old [..] test unless you specifically need POSIX-style portability.

http://mywiki.wooledge.org/BashFAQ/031
http://mywiki.wooledge.org/ArithmeticExpression

So the correct option, assuming that "$exists" contains an integer, is:

Code:

(( exists > 0 ))

If you're actually testing for the existence of a string, use "[[..]]" and the -n/-z test options.

Next, a small suggestion:

Code:

((count++))

This line is not wrong, but it is unnecessary. Since the field brackets in arrays also work in an arithmetic context, you could simply use "${files[$count++]}" in the line above it. "variable++" is the "post-increment" operator, meaning that after the value expands and is used, it gets incremented by one, and is thus ready for use when the next iteration comes around.

(In comparsion, "++variable" is the pre-increment operator, meaning that the value increases by one just before it gets used.)

Next, since it appears that one problem may be the "/" at the end of the filename in the array variable, you can always use a parameter substitution of some kind to remove it:

Code:

"${files[$count++]%/}"

The above assumes that there's only a single, optional, slash at the end of the string. You'd have to use a different pattern to remove any others appearing elsewhere.

Finally, I highly suggest that work on cleaning up your formatting. Your complex commands are sloppily mis-aligned and poorly grouped, making it harder to piece together what goes with what. Always line everything that executes at the same level to the same depth, and clearly separate logical sections with blank lines.

Many scripters also feel that it's better to place the "do/then" keywords on the same line as the "for/while/until/if" keywords, as they are not separate commands but are paired with the opening keyword to bracket the test/input string. Putting them together on one line thus helps to better visually separate the outside block from the inside block.

Scripting With Style

If I were to write the above, I'd do it like this:

Code:

#!/bin/bash

infile=list.txt

mapfile -t files <"$infile"

for file in "${files[@]}"; do

    echo "Checking if item ${files[$count]} was already downloaded.."

    exists=$( sqlite3 sync.db "select count(*) from rememberedFiles where filename=\"?${files[count++]%/}\"" )

    if (( exists > 0 )); then
        echo "It exists!"
    else
        echo "It doesn't exist!"
    fi  

done

exit 0

Edit:

I just realized that there's another rather large error, that even I overlooked the first time around.

Code:

for file in "${files[@]}"; do

This loads the variable "$file" with each entry in "${files[@]}" in turn. So there's no need to call the array entries directly inside the loop or worry about incrementing any values at all. Just replace "${files[count]}" with "$file" in the subsequent commands instead.

ninja6o4 · 03-15-2013, 08:12 AM

Wow guys thank you for the very insightful posts. I just got in, so I will review your suggestions and have another visit at my script. I have made some changes since my last post so I corrected a couple errors you have pointed out, but obviously you can tell how new I am to this by my rather undisciplined approach to writing it

I will post an update later with my findings...

EDIT: I've updated OP with my final code, thanks rigor and David for your help. I spent a week bashing my head on this and other parts of my overall script and you guys have helped me finish it off!

I'm sure I will be posting again soon when v2 of my script comes to fruition. But for now.. cheers

chrism01 · 03-18-2013, 02:11 AM

Quote:

EDIT: I've updated OP with my final code,

Please don't do that; it makes a mockery of the responses that follow it.
Always leave the original and post the new version at the bottom of the thread.

ninja6o4 · 03-18-2013, 08:00 AM

Quote:

Originally Posted by chrism01

Please don't do that; it makes a mockery of the responses that follow it.
Always leave the original and post the new version at the bottom of the thread.

Really? I don't see how, I gave credit where it's due, and I figured in the OP it would save someone else the trouble of searching the thread for the solution.

Nevertheless, I practice proper netiquette, so here is the final code in my last post:

Code:

      files=( $( cat ./list.txt ) ) # read folder listing so we know what to check for in each folder
      for file in "${files[@]}"; do 
        sql_value=$(sed 's/'\''/&&/g' <<< "${file%/}") # http://goo.gl/cjeb6
        printf "$sql_value found - "
        exists=$(sqlite3 sync.db "select count(*) from rememberedFiles where filename=\""$sql_value"\"")
        if (( exists > 0 )); then  # if not 0 (it found an entry in the db)
          echo "It exists!"
        else # add it to the db
          echo "It doesn't exist!"
          sqlite3 sync.db "insert into rememberedFiles values (\""$sql_value"\")"
        fi
      done

chrism01 · 03-18-2013, 08:13 PM

Its not a qn of giving credit (which is nice of you to do

), but as people come along they want to see the problem, followed by the Qns and Answers in the order they occurred.
eg the error you say occurred originally won't occur with the fixed code... very confusing, especially for other newbies who may be following along.

No big deal

Macburp · 02-15-2015, 09:55 AM

Sorry to resurrect an old thread, but I've been trying to adapt the code to my own purposes and have hit a roadblock.

I want the script to put sub-directories into the sqlite db rather than files. I've set up the database (dir.db) with one table (zero) and one column (episode, defined as variable character using VARCHAR(10)). This is used in the following script -

Code:

enter #!/bin/sh
set -x
cd ~/scripts/sqlite/remote/
Progs=( * )
for show in "${Progs[@]%*/}"; do
  cd ~/scripts/sqlite/
  exists=$( sqlite3 dir.db "select count(*) from zero where episode=\"?$show%/}\"" )
  if (( exists > 0 )); then
    echo "Show already downloaded"
  else
    cp ~/scripts/sqlite/remote/${show}/ -t ~/scripts/sqlite/home/ -R -v
    sqlite3 dir.db "insert into zero (episode) values ('${show}');"
    fi
done
exit 0

This script runs without error, but doesn't work

. If I put a directory in ~/scripts/sqlite/remote/ which is in dir.db, the directory should return 'Show already downloaded', but instead it copies.

Clearly the problem is here -

Code:

  exists=$( sqlite3 dir.db "select count(*) from zero where episode=\"?$show%/}\"" )
  if (( exists > 0 ));

but I don't know how to diagnose it. Any thoughts on how to fix would be welcome.

rigor · 02-15-2015, 04:43 PM

Hi Macburp!

Just a thought, if plenty of people added to the end of a thread marked SOLVED, that could lead to some very-very long threads that are very time consuming for people to go through. In the future, you might want to start a new thread, mention that it might be related to a different thread, and then refer to the old thread by using it's URL.

As to your concern with your particular script, please consider the following points.

1) If your script worked completely, then we could expect that the patterns you've chosen, for example "%*/" in your for loop, are correct. If they are correct then we could potentially work backwards and determine what type of naming conventions you've used for directories, files, etc., for the shows.

2) Since you've stated that your script is not working correctly, we can't necessary assume that the patterns you've chosen are correct, so we would seem to be left without knowing what type of naming conventions you've used for directories, files, etc., for the shows. Hence we cannot necessarily know if the patterns you've chosen are correct.

3) It appears that plenty of people misinterpret the use of question mark ? characters in the sqlite documentation, which is why I wish the documentation would be changed. It's hard to be sure in this case, whether or not that is a factor with the problem you're having.

If I understand the general nature of your script correctly, if the names of directories/files related to shows are not in your Database, you wish the script to copy them from your remote directory and also insert the names into the DB.

If I'm mistaken about that, please let me know.

Hoping that I'm interpreting correctly what you'd like to accomplish, I've modified a version of your script, and seemingly gotten it functioning correctly. This is what I did:

The details:

I just wanted to get the decision the script needs to make, working correctly.

I also don't have the same directory structure you do. So I commented out the cp command, and just created a directory named remote within directory in which I was working.

I don't know why the cd to ~/scripts/sqlite/ was in the loop; it didn't appear to be needed inside the loop, so I moved it out to be more efficient.

Since I didn't know what the full patterns you were using where intended to match, I used simpler patterns.

The resultant script is one that, if I place simple directory or file names in the remote directory and they are not already in the DB, it will indicate that they would be copied and it will place the names into the DB. If the names are already in the DB, the script just produces the message "Show already downloaded".

As I just wanted to get the main point of failure working, that is the main decision working, I paid no particular attention to handling directory or file names that might contain white space.

If I am correct in my thinking, then you might want to start with the working patterns I've shown you, and modify them to work with whatever the specifics of your situation are.

HTH