Re: [sqlite] Using several .commands from a Script
On Thu, 10 Oct 2013 16:27:21 +0200 Stephan Beal wrote: > > (echo .separator "||"; echo .import path tbl) | sqlite3 dbfile > > > > Alternately, most Unix shells allow: > > echo -e ".separator '[||'\n.import ..." > > the -e enables the conventional set of backslash escapes. We're OT here, but I'm not so sure "most" shells support "echo -e". Standard, simpler, and easier to remember is printf: $ printf '.separator "||"\n.import path tbl\n' | sqlite3 dbfile or just $ echo '.import path tbl' | sqlite3 -separator '||' dbfile --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performing Multiple SQL Statements Within A Transactions Using TCL
On 10/10/2013 2:14 PM, Tilsley, Jerry M. wrote:
All,
Does anybody have any examples of wrapping multiple SQL insert/update
statements in a transaction using the TCL API?
Sure:
db transaction {
db eval {create table if not exists s2011.tclcode(procname text,
version text, tcl text, comments text, unique(procname, version))}
db eval {delete from s2011.tclcode}
foreach table $tablelist {
set shorttablename [lindex [split $table .] 1]
set tc [db eval "select tcl, comments from $table limit 1"]
set t [lindex $tc 0]
set c [lindex $tc 1]
db eval "insert into s2011.tclcode(procname, version, tcl,
comments) values(:shorttablename,:::GEB::defaultversion,:t,:c)"
}
}
HTH,
Gerry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting or replacing in the same db based on one key
On Thu, 10 Oct 2013 12:29:21 -0400 "jose isaias cabrera" wrote: > > INSERT INTO SimplePrices > > SELECT cust || '1', class, slang, tlang, TransferCost, Price > > FROM SimplePrices WHERE cust = 'XEROX'; > > > > Kudos for the primary key declaration, btw. :-) > > Thanks for the kudos and the support. So, are you saying that this > is also true? > > INSERT INTO SimplePrices > SELECT 'XEROX1', class, slang, tlang, TransferCost, Price > FROM SimplePrices WHERE cust = 'XEROX'; Yes, that works too. All that matters is that the values produced by SELECT meet the requirements of the columns they're being INSERTed into. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performing Multiple SQL Statements Within A Transactions Using TCL
All, Does anybody have any examples of wrapping multiple SQL insert/update statements in a transaction using the TCL API? Thanks, Jerry Tilsley, CIS Lvl 2 St. Claire Regional Medical Center Sr Systems Analyst jerry.tils...@st-claire.org Disclaimer This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of St. Claire Regional Medical Center. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing or copying of the email is strictly prohibited. If you received this email in error please notify the St. Claire Regional Helpdesk by telephone at 606-783-6565. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LIMIT expression in subquery cannot reference outer query tables
When using LIMIT in a subquery it seems the expression cannot access the outer query's tables. Other complex expressions are supported in LIMIT, so I was curious if this is a defined limitation or something else? Example: sqlite> create table test (f int); sqlite> insert into test values (1); sqlite> insert into test values (2); sqlite> insert into test values (3); sqlite> insert into test values (4); sqlite> insert into test values (5); sqlite> select avg(f) from test; 3.0 sqlite> select * from test limit (select avg(f) from test); 1 2 3 sqlite> select *, (select count(*) from test t1 limit t2.f) from test t2; Error: no such column: t2.f sqlite> select *, (select count(*) from test t1 where t1.f <= t2.f) from test t2; 1|1 2|2 3|3 4|4 5|5 sqlite> So the average is intentionally a value that losslessly converts to an integer. I can use a subquery inside the LIMIT clause without issue. However, when I use limit inside a subquery that correlates to the outer query, I get no such column. A similar subquery with WHERE instead of LIMIT works fine. Thanks, Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Temporary Tables
On 10/10/2013 1:16 PM, John wrote: Do you need to/should you drop temporary tables when you are done with them? You may. If you don't, the temp db and all tables in it will be deleted when you close the connection. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Temporary Tables
Do you need to/should you drop temporary tables when you are done with them? Thanks. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting or replacing in the same db based on one key
"James K. Lowden" wrote... On Wed, 9 Oct 2013 17:00:36 -0400 "jose isaias cabrera" wrote: CREATE TABLE SimplePrices ( cust TEXT, class TEXT, slang TEXT, tlang TEXT, TransferCost, Price, PRIMARY KEY (cust, class, slang, tlang)); and I have lots of data data. What I would like to do is to be able to copy records to the same table. So, imagine that you have a set of records where cust='XEROX' and you would like to copy all of those records to a new cust say, 'XEROX1'. INSERT INTO SimplePrices SELECT cust || '1', class, slang, tlang, TransferCost, Price FROM SimplePrices WHERE cust = 'XEROX'; Kudos for the primary key declaration, btw. :-) Thanks for the kudos and the support. So, are you saying that this is also true? INSERT INTO SimplePrices SELECT 'XEROX1', class, slang, tlang, TransferCost, Price FROM SimplePrices WHERE cust = 'XEROX'; I may need to also have a total different value for cust. Thanks again. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using several .commands from a Script
(echo .separator "||"; echo .import path tbl) | sqlite3 dbfile
worked perfectly. Thank you Clemens, Stephan and Simon for the ideas. I
learned a bunch of new things today.
John
On Thu, Oct 10, 2013 at 10:18 AM, Clemens Ladisch wrote:
> John wrote:
> > I am having problems creating a "do shell script" command which includes
> > both the import command and the insert command. The reason they need to
> be
> > combined is that the shells instance ends with the command.
> >
> > do shell script ("sqlite3 " & databasePath & " .separator \"||\" ;
> .import path/to/csv Main")
>
> Dot commands are not SQL commands; they must be separated by newlines.
>
> If you cannot generate newline characters, try to execute something
> like this:
>
> (echo .separator "||"; echo .import path tbl) | sqlite3 dbfile
>
>
> Regards,
> Clemens
> ___
> 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
Re: [sqlite] Using several .commands from a Script
On 10 Oct 2013, at 3:02pm, John wrote:
> Combining .commands does NOT work:
>
> do shell script ("sqlite3 " & databasePath & " .separator \"||\" ; .import
> path/to/csv Main")
> -
>
> Is my syntax wrong or can .commands not be included in a script?
You cannot combine commands in that way and call it that way. They'd work
separately but not together.
Instead, use AppleScript commands to write a text file. Put all the commands
you want in the text file including the one to set the separator and the one to
read the .csv file.
Then start sqlite3 and tell it to execute that text file. Something like
do shell script ("sqlite3 " & databasePath & " < commands.txt")
But actually you don't need to write two separate files: the .csv and the
command file. It might be better to instead make one file with proper import
SQL commands in, and just tell sqlite3 to run that:
do shell script ("sqlite3 " & databasePath & " '.read importcommands.txt'")
that can have whatever sqlite3 commands you like in, including ones to create
the table or delete any rows which already exist in it.
Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using several .commands from a Script
On Thu, Oct 10, 2013 at 4:18 PM, Clemens Ladisch wrote: > If you cannot generate newline characters, try to execute something > like this: > > (echo .separator "||"; echo .import path tbl) | sqlite3 dbfile > Alternately, most Unix shells allow: echo -e ".separator '[||'\n.import ..." the -e enables the conventional set of backslash escapes. [stephan@host:~]$ echo -e ".separator ||\n.import foo bar" .separator || .import foo bar -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using several .commands from a Script
John wrote:
> I am having problems creating a "do shell script" command which includes
> both the import command and the insert command. The reason they need to be
> combined is that the shells instance ends with the command.
>
> do shell script ("sqlite3 " & databasePath & " .separator \"||\" ; .import
> path/to/csv Main")
Dot commands are not SQL commands; they must be separated by newlines.
If you cannot generate newline characters, try to execute something
like this:
(echo .separator "||"; echo .import path tbl) | sqlite3 dbfile
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using several .commands from a Script
I have a fairly long AppleScript that I creates a csv file which then needs
to be imported into a database. I am trying to use the .import command from
AppleScript's "do shell script" command to import a csv file into a
temporary table. I then need to import the temporary table into the Main
table which includes a id integer primary key.
I am having problems creating a "do shell script" command which includes
both the import command and the insert command. The reason they need to be
combined is that the shells instance ends with the command.
For example:
do shell script "cd ~/Desktop"
do shell script "pwd"
returns / because it is a separate instance of shell
Usually you can separate commands in a "do shell script" with semicolins.
However, this does not seems to work with the .commands.
This is an example of how I would create a table:
-
property databaseFolder : POSIX path of (path to documents folder as text)
& "Databases/"
property databaseName : "MyDatabase"
property databasePath : quoted form of (databaseFolder & databaseName astext)
property table1 : "Main"
set sqlDataFile to POSIX path of (path to desktop as text) & "sql_data"
do shell script ("mkdir -p " & quoted form of databaseFolder & " ;
sqlite3 "& databasePath & "
\"create table if not exists " & table1 & "(id integer primary key, color,
taste); \"")
-
This is an example of how a single .command can be called:
do shell script ("sqlite3 " & databasePath & " .show")
-
Combining .commands does NOT work:
do shell script ("sqlite3 " & databasePath & " .separator \"||\" ; .import
path/to/csv Main")
-
Is my syntax wrong or can .commands not be included in a script? If there
is a better way of doing this ... maybe a bash script (which can then be
called from AppleScript) ?
Thanks for the time.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using an sqlite db as a mutex?
On Sat, Sep 28, 2013 at 10:09 AM, Stephan Beal wrote:
> On Thu, Sep 26, 2013 at 5:31 PM, Dan Kennedy wrote:
>
>> It does. Both open a write transaction on the database. In the\
>> parent posts case either would work.
>>
>
> Follow-up: i implemented this yesterday and it seems to do the job nicely.
> Actually triggering the race condition was always basically impossible with
> our infrastructure, so i can't really _confirm_ that it's all doing the
> right thing, but it seems to be just fine (or at least doesn't break
> anything). It basically looks like:
>
Follow-up 2, since i coincidentally have that code opened... maybe this
will help someone else...
/* Part of gf_db_mutex(). */
$_DB_MUTEX_KLUDGE = NULL;
/**
Various bugs in MySQL's table locking mechanism make it useless
for most of our purposes, so for cases where we must ensure that
the db remains locked for the duration of a single request, we use
a separate sqlite3 db whose _sole_ purpose is to act as a mutex.
Calling this function will either block until the mutex is acquired or
will throw an exception on error (e.g. it times out while waiting).
This is currently (201310) only used for the 1-to-1 meeting
calculations,
to avoid a 1-in-a-million case which could lead to double bookings
of the same time slot (in which case the 2nd one would overwrite the
first).
*/
function gf_db_mutex(){
global $_DB_MUTEX_KLUDGE;
/*
Note that we use only one sqlite3 db for all sites, but that is just
a simplification. We "should" use a different one for each site.
However, we only use this mutex for the 1-to-1 meeting tools and
those don't get enough traffic that this will ever make a
difference.
*/
if(!$_DB_MUTEX_KLUDGE){
$fn = gf_get_common_file_path('mutex.sqlite3');
$_DB_MUTEX_KLUDGE = new PDO('sqlite:'.$fn, NULL, NULL,
array(PDO::ATTR_ERRMODE =>
PDO::ERRMODE_EXCEPTION)
);
$_DB_MUTEX_KLUDGE->exec("BEGIN EXCLUSIVE")
/* Per a discussion the sqlite mailing list, BEGIN EXCLUSIVE
is all we need to acquire/hold the lock. */;
}
return $_DB_MUTEX_KLUDGE;
}
/**
Explicitly unlocks the mutex acquired by gf_db_mutex(). Not normally
needed - the lock is released when the underlying PDO instance is
freed when PHP shuts down.
*/
function gf_db_mutex_free(){
global $_DB_MUTEX_KLUDGE;
if($_DB_MUTEX_KLUDGE){
$_DB_MUTEX_KLUDGE->exec("ROLLBACK");
$_DB_MUTEX_KLUDGE = NULL;
}
}
that said, PHP's built-in flock() would probably be a better solution, but
i won't get paid to reimplement it :/.
--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS
On 09/10/2013 9:53 PM, Richard Hipp wrote: On Wed, Oct 9, 2013 at 9:49 PM, James K. Lowden wrote: It's difficult to do portably because you have to account for every combination of standard C library and integer size Remember that SQLite does not use the standard library printf() function. It has its own. (See http://www.sqlite.org/src/artifact/da9119eb?ln=163) And the SQLite version assumes that %lld means 64-bit integer and %d means 32-bit integer. Beautiful! Proves yet again that sometimes the best solution is to "cheat" (change the rules). Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS
On 10.10.2013 03:53, Richard Hipp wrote: I think that http://www.sqlite.org/src/info/e97d7d3044 fixes this issue. Works well for me. Please correct me if I've missed something. You committed to the "row-size-est" branch. I guess this will be merged into "trunk" for 3.8.1? Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
