Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode
I posted this a while ago: http://sqlite.1065341.n5.nabble.com/Loading-Options-from-the-command-line-binary-td12230.html It's not very obvious. John wrote: Hi, I have several different computers running an AppleScript that queries and writes to a SQLite3 database located in a shared folder on the network. Occasionally a "database is locked" error is produced. Is there a way of sending a .timeout command as if I was working from the shell, in Command-Line Mode? I understand I can write an error handler which will accomplish the same thing but I am trying to avoid that option. property databaseFolder : POSIX path of (path to public folder as text) & "Databases/" property databaseName : "myDatabase" property databasePath : quoted form of (databaseFolder & databaseName astext) property table1 : "Main" set xxx to do shell script "sqlite3 " & databasePath & " \"select * from "& table1 & "; \"" Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using dot commands with the command line binary
I finally figured out how to load multiple "dot commands" or settings from the command line tool. (Some users only have the default binary to rely on, ya know.) Since I have never found this information on the web before, I thought I would post it here to share the information. To load options before doing the query, they have to be separated from the query by a hard return, in the quoted string itself. So: > sqlite3 -header -column tmp.db ".width 5 30; select * from data;" doesn't really change the column widths. But, doing this does: > echo ".width 5 30\n select * from data;" | sqlite3 -header -column tmp.db To load multiple options, you have to put them on different lines, with the query on the last line: > echo ".width 5 30\n.timeout 15000\n select * from data;" | sqlite3 -header -column tmp.db Some systems I have tried this on /required /only a single hard return after the last option, with a semicolon between the multiple settings. Others required the hard returns without any semicolons. Experiment on your own platform/binary version. I don't know which versions/flavors this works on, but it works for me finally. Note: Your version of echo has to support turning '\n' into a hard return. Not all do. If not, you can do something like a perl -e to do it as well. All the examples on websites show applying the dot commands from a sqlite> prompt. Doing the hard returns within the string makes it look like a user typing the commands in, with the hard returns. If anyone could fix the parser in the sqlite3 source code, I'm sure many novices would greatly appreciate it. The above trick has eluded me for several months of working with sqlite3 from the command line, and is still cumbersome, although usable now. One other trick is to put the options in a .sqliterc file in your home directory, one dot command per line. This works, but is not really feasible in an environment where it will run on multiple hosts and you don't control other accounts that will run it. Also, you can't pick and choose which ones are loaded. They are global settings at that point. I hope this helps someone out there looking for this, as well as possibly getting the parser fixed to make it easier to use in the future. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Loading Options from the command line binary
I finally figured out how to load multiple "dot commands", pragma settings, or settings from the command line tool. (Some users only have the default binary to rely on, ya know.) Since I have never found this information on the web before, I thought I would post it here to share the information. To load options before doing the query, they have to be separated from the query by a hard return, in the quoted string itself. So: > sqlite3 -header -column tmp.db ".width 5 30; select * from data;" doesn't really change the column widths. But, doing this does: > echo ".width 5 30\n select * from data;" | sqlite3 -header -column tmp.db To load multiple options, you have to put them on different lines, with the query on the last line: > echo ".width 5 30\n.timeout 15000\n select * from data;" | sqlite3 -header -column tmp.db Some systems I have tried this on /required /only a single hard return after the last option, with a semicolon between the multiple settings. Others required the hard returns without any semicolons. Experiment on your own platform/binary version. I don't know which versions/flavors this works on, but it works for me finally. Note: Your version of echo has to support turning '\n' into a hard return. Not all do. If not, you can use a perl -e (or similar) to do it as well. All the examples on websites show applying the dot commands from a sqlite> prompt. Doing the hard returns within the string makes it look like a user typing the commands in, with the hard returns. If anyone could fix the parser in the sqlite3 source code, I'm sure many novices would greatly appreciate it. The above trick has eluded me for several months of working with sqlite3 from the command line, and is still cumbersome, although usable now. One other option is to put the dot commands in a .sqliterc file in your home directory, one dot command per line. This works, but is not really feasible in an environment where it will run on multiple hosts and you don't control other accounts that will run it. Also, you can't pick and choose which ones are loaded. They are global settings at that point. I hope this helps someone out there looking for this, as well as possibly gets the parser fixed to make it easier to use in the future. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 code from the website
So, can anyone provide a working example of how this can work? Or, look at the code I have to figure out what I have wrong? I'm still not getting anything to work. To recap: Overall, I'd like to be able to set up 2 *sqlite3* FTS databases. One that holds quotes, with ranked search output, as the above example is trying to do. The other one is to hold entire books, with some minimal structure, like 'Book / Volume or Section / Chapter / text' so that I could do a query to show a list of all the books in the db, then when one is selected, it would show all chapters in a clickable list on the left side of a web page, with the text from a selected chapter on the right. Or be able to search, and have the results ranked, with the output like this: book / Chapter: Hit 1's paragraph text Book / Chapter: Hit 2's paragraph text I want the search results to be limited to either a specific chapter, a specific book, or over the entire database, via select boxes in the book/chapter list displayed on the left of a web page. Any help with the database side of this would be greatly appreciated. Thanks, Ryan Original Message Subject: Re: [sqlite] FTS4 code from the website From: Dan Kennedy <danielk1...@gmail.com> Date: 7/1/2011 7:33 AM > On 06/30/2011 08:55 PM, Ryan Henrie wrote: >> Even if I use the stock example from the web page, with only 2 columns, >> and the exact schema from the example, I get the same result. >> >> I'm wondering if it is a bug in the example code on the website (ie the >> source code has moved on, invalidating an example based on old code), or >> it's just something in turning their c code example into a full >> extension that I didn't do right. >> >>From the extension's source code: >> >> nCol = aMatchinfo[1]; >> if( nVal!=(*1+nCol*) ) goto wrong_number_args; >> >> So, it should scale with the number of columns. (I would hope it's not >> hardcoded to a set number of columns!) > The page is a bit deceptive. The key phrase relating to the C code > example is: > > "Instead of a single weight, it allows a weight to be externally > assigned to each column of each document." > > Making the C code function incompatible with the SQL example above > it. The C code function would work with the example in its header > comment: > > CREATE VIRTUAL TABLE documents USING fts3(title, content); > > SELECT docid FROM documents > WHERE documents MATCH > ORDER BY rank(matchinfo(documents), 1.0, 0.5) DESC; > > It wouldn't be real hard to adapt the C code so that it accepted > a single weight argument like the hypothetical function in the > SQL example above it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS4 code from the website
Even if I use the stock example from the web page, with only 2 columns, and the exact schema from the example, I get the same result. I'm wondering if it is a bug in the example code on the website (ie the source code has moved on, invalidating an example based on old code), or it's just something in turning their c code example into a full extension that I didn't do right. From the extension's source code: nCol = aMatchinfo[1]; if( nVal!=(*1+nCol*) ) goto wrong_number_args; So, it should scale with the number of columns. (I would hope it's not hardcoded to a set number of columns!) Original Message Subject: Re: [sqlite] FTS4 code from the website From: Dan Kennedy <danielk1...@gmail.com> Date: 6/29/2011 11:25 PM > On 06/30/2011 10:31 AM, Ryan Henrie wrote: >> Reference Page: http://www.sqlite.org/fts3.html#appendix_a >> >> At the bottom of the page, there is a sample c file to calculate the >> rank, and a FTS query to use it. I can't get it to work. >> >> You can see my files here: >> >> http://coldmist.homeip.net/quotes_sql_test.txt >> http://coldmist.homeip.net/rank.c.txt >> >>> gcc -shared -fPIC -I/opt/include -o rank.so rank.c >>> rm test.sql; sqlite3 test.sql> >> The C file compiles without errors or warnings on my x86 Linux machine >> (and I verified one plugin I found compiled and worked fine, just to >> remove build issues as a cause), but when I execute the import, it >> complains with this: >> >> Error: near line 16: wrong number of arguments to function myrank() > Looks like myrank() is supposed to be passed 5 arguments in this > case. The return value of matchinfo() and a weight for each column. > Your table has 4 columns, hence 5 arguments. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS4 code from the website
Reference Page: http://www.sqlite.org/fts3.html#appendix_a At the bottom of the page, there is a sample c file to calculate the rank, and a FTS query to use it. I can't get it to work. You can see my files here: http://coldmist.homeip.net/quotes_sql_test.txt http://coldmist.homeip.net/rank.c.txt > gcc -shared -fPIC -I/opt/include -o rank.so rank.c > rm test.sql; sqlite3 test.sql http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
