Re: [sqlite] any command to find last rowid in a table

On Mon, Feb 8, 2010 at 9:31 AM, Vasanta wrote: > Can I use this function call in C code to return last rowid to be inserted?. On Fri, Feb 5, 2010 at 2:50 PM, Petite Abeille wrote: > Help Vampires: A Spotter’s Guide > http://slash7.com/2006/12/22/vampires/ Nice read. - Robert ___

Re: [sqlite] parallelizing an update

On Tue, Feb 2, 2010 at 11:20 AM, Nicolas Williams wrote: > On Tue, Feb 02, 2010 at 09:23:36AM +0100, Sylvain Pointeau wrote: >> I would be very interested to see some benchmark, just to see. > > Feel free to write the relevant program, schema, SQL statements and run > benchmarks against it.  W

Re: [sqlite] parallelizing an update

On Tue, Feb 2, 2010 at 3:13 PM, John Elrick wrote: > Robert Citek wrote: >> Are there some white papers or examples of how to do updates in >> parallel using sqlite? > > I could be misunderstanding your requirements, but this sounds a little > like Map Reduce: > >

Re: [sqlite] parallelizing an update

7;select rowid, item from foo;' | > while read rowid item ; do >  status=$(long_running_process "${item}" ) >  echo "update foo set status=${status} where rowid=${rowid} ;" >> update.sql > done > > echo "commit transaction" >> update.

[sqlite] parallelizing an update

Are there some white papers or examples of how to do updates in parallel using sqlite? I have a large dataset in sqlite that I need to process outside of sqlite and then update the sqlite database. The process looks something like this: sqlite3 -separator $'\t' sample.db 'select rowid, item from

Re: [sqlite] fastest way to get the min/max

On Wed, Jan 20, 2010 at 1:54 PM, Pavel Ivanov wrote: >> Why the difference in search time between searching individually and >> searching together? > > Apparently SQLite is not smart enough to optimize the search for both > min and max to make double entrance to the index - first from the > beginn

Re: [sqlite] fastest way to get the min/max

On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov wrote: > I thought at the first moment that Pavel's suggestion on using extra index > is very "expensive" in terms of megabytes on the disk, but despite this > drawback it's actually more robust. For my own curiosity I created a table with random text d

Re: [sqlite] fastest way to get the min/max

On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov wrote: >> One of many ways would be to precompute the min/max into a separate >> table and then query that table whenever you need the min/max. > > I thought at the first moment that Pavel's suggestion on using extra index > is very "expensive" in terms

Re: [sqlite] fastest way to get the min/max

> On Wed, Jan 20, 2010 at 6:24 PM, hi wrote: >> For my application I am storing about "1770" rows into sqlite table, >> and when taking 'min' or 'max' it takes about ~7 to 10 seconds. >> >> Can you please suggest effective ways to get min/max values. How are you currently getting the min/max

Re: [sqlite] way to get a list with column names

Nothing with just SQL alone, although you can get close: http://www.sqlite.org/faq.html#q7 You could use a command pipeline, but that only works if the table has at least one record: $ sqlite3 -separator ", " -header sample.db 'select * from sqlite_master limit 1; ' | head -1 type, name, tbl_nam

Re: [sqlite] graphs and sql

On Sun, Jan 10, 2010 at 8:06 PM, Dan Bishop wrote: > Robert Citek wrote: >> Does anyone have any recommendations for books or other resources that >> deal with working with graphs (i.e. vertexes and edges) using sql? >> > I don't think that SQL is the best language

Re: [sqlite] graphs and sql

On Sat, Jan 9, 2010 at 5:44 PM, Jay A. Kreibich wrote: > On Sat, Jan 09, 2010 at 03:41:24PM -0500, Robert Citek scratched on the wall: >> Does anyone have any recommendations for books or other resources that >> deal with working with graphs (i.e. vertexes and edges) using sql? &

[sqlite] graphs and sql

Hello all, Does anyone have any recommendations for books or other resources that deal with working with graphs (i.e. vertexes and edges) using sql? For example, if I could store a graph in a sqlite database, I'd like to query the database to know if the graph contains a Eulerian path[1]. [1] ht

Re: [sqlite] temp directory?

On Thu, Jan 7, 2010 at 12:54 PM, Kris Groves wrote: > So it seems as if TMPDIR will work in two different OSes. What would be an easy test to verify if setting TMPDIR works or not? I did this, which shows that TMPDIR is indeed being used, but I think this test is a bit cumbersome: $ sqlite3 /de

Re: [sqlite] temp directory?

On Thu, Jan 7, 2010 at 10:42 AM, Jay A. Kreibich wrote: > On Thu, Jan 07, 2010 at 10:35:21AM -0500, Robert Citek scratched on the wall: >> You mention a temp environment variable.  I've googled through the >> sqlite.org site and haven't found any mention of an envir

Re: [sqlite] temp directory?

You mention a temp environment variable. I've googled through the sqlite.org site and haven't found any mention of an environment variable. What environment variable can I set to change the default value for the temporary directory? Regards, - Robert On Fri, Nov 20, 2009 at 5:04 AM, Kris Groves

Re: [sqlite] long time to sort list of random integers

On Sat, Dec 19, 2009 at 10:18 AM, Simon Slavin wrote: > On 19 Dec 2009, at 9:27am, Robert Citek wrote: > >> Does anyone have any pointers on how I can speed up a sqlite3 query >> that sorts a list of random integers? > > What are  you doing that sorts them? An "ord

Re: [sqlite] long time to sort list of random integers

On Sat, Dec 19, 2009 at 12:46 PM, Roger Binns wrote: > Robert Citek wrote: >> and much longer than >> piping the list of random integers into the sort command. > > A considerable amount of time in your test script is actually spent in print > calls to pipes. I don'

[sqlite] long time to sort list of random integers

Does anyone have any pointers on how I can speed up a sqlite3 query that sorts a list of random integers? I've noticed that sqlite3 takes a long time to sort random integers, much longer than sorting a series of integers and much longer than piping the list of random integers into the sort command

Re: [sqlite] How to find out which row had been modified by an UPDATE statement?

On Thu, Dec 17, 2009 at 12:05 PM, Martin Kirsche wrote: > is it possible in SQLite to find out which row had been modified by an > UPDATE statement? Not automatically. You would have to track it yourself with some code like this: sqlite> create temporary table update_list as select rowid as "id

Re: [sqlite] How to find out which row had been modified by an UPDATE statement?

On Thu, Dec 17, 2009 at 1:43 PM, P Kishor wrote: > On Thu, Dec 17, 2009 at 11:05 AM, Martin Kirsche > wrote: >> Hi, >> is it possible in SQLite to find out which row had been modified by an >> UPDATE statement? > > just SELECT with the same params that you used in your UPDATE. Unless the params

Re: [sqlite] speed up a sorted union all query

On Thu, Dec 17, 2009 at 1:32 PM, Igor Tandetnik wrote: > Robert Citek > wrote: >> How can I speed up the sorting of a union all query? > > Basically, you can't. An index can't be used for this. Consider changing your > requirements, or your design, to make su

Re: [sqlite] speed up a sorted union all query

On Thu, Dec 17, 2009 at 11:20 AM, Robert Citek wrote: > How can I speed up the sorting of a union all query? BTW, here is a method for creating a sample dataset: $ echo {1..1000}$'\t'{1..200}$'\t'1 | tr ' ' '\n' | sqlite3 -init <(echo ' create

[sqlite] speed up a sorted union all query

Hello all, How can I speed up the sorting of a union all query? Here are two queries which effectively create the same output. However the first one pipes the output from the union all to the shell's sort command whereas the second one used an order by clause. In addition, the second query explod

[sqlite] creating teams with random players

Hello all, I have a toy problem which is analogous to a real problem I am working on. The toy problem is, given a set of teams each with a roster of 100 possible players, create a set of teams each with 6 players chosen randomly from the roster of 100 players. I can model the sets in sqlite3 and

Re: [sqlite] Passing all columns as arguments to a function?

On Thu, Sep 10, 2009 at 8:37 AM, Jean-Denis Muys wrote: > Is it possible to pass all columns of a table as arguments to a function in > a implicit way? As Igor wrote: no. > I tried this but it did not work: > > Create table studentMarks (French, English, Maths, Physics); > Insert into studentMar

Re: [sqlite] Importing data into SQLite - text files are not really portable

Yes, one big, long line. As for displaying, depending on the program you use, \r may get displayed as ^M. For example: $ echo -ne '\r\n' | cat -A ^M$ $ echo -ne '\r\n' | od -An -abcx cr nl 015 012 \r \n 0a0d Regards, - Robert > -Original Message- > From: sqlite-users-boun...@s

Re: [sqlite] Importing data into SQLite

Just a guess, but you may be running into issues with the end-of-line character as they are different under linux (\n), Mac (\r), and Windows/DOS (\r\n). Linux has a tool to convert Windows/DOS end-of-lines to linux-style called dos2unix. There may be one for Mac, too, but I'm not sure. If not,

Re: [sqlite] Importing data into SQLite

Sounds good. Let us know how things go. P.S. one thing to try may be to use dos2unix to convert any text files created in the Windows/DOS world to unix-format text files. Regards, - Robert On Mon, Sep 7, 2009 at 7:22 PM, Kavita Raghunathan wrote: > The difference between what you did and what I

Re: [sqlite] Importing data into SQLite

On Sun, Sep 6, 2009 at 9:32 PM, Kavita Raghunathan wrote: > Timothy and all, > When I try to import a .csv, I get a segmentation fault: > 1) First I set .seperator to , > 2) Then I type .import > 3) I see "Segmentation fault" > > Any ideas ? Here's an example of how it worked for me. $ cat data

Re: [sqlite] SQLite to Access

The ODBC connector for SQLite will allow you to connect Access to SQLite without having to import/export: http://www.ch-werner.de/sqliteodbc/ Regards, - Robert On Thu, Sep 3, 2009 at 2:33 PM, Pighin, Ryan wrote: > Hi All - We have a new utility in our environment using SQLite and I was > wonderi

Re: [sqlite] Trouble running sqlite3 in ubuntu linux 9.04

FWIW, I've been running sqlite3 on Ubuntu 9.04 for a while without issue for several months. $ cat /etc/issue.net Ubuntu 9.04 $ dpkg -l sqlite3 Desired=Unknown/Install/Remove/Purge/Hold | Status=Not/Inst/Cfg-files/Unpacked/Failed-cfg/Half-inst/trig-aWait/Trig-pend |/ Err?=(none)/Hold/Reinst-requi

Re: [sqlite] Rows where people connected in the past 2 weeks?

To expand on things to try: sqlite> select julianday('now'); sqlite> select julianday('2009-08-01'); sqlite> select julianday('now') - julianday('2009-08-01'); And maybe have a look here: http://sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions Regards, - Robert On Tue, Aug 18, 2009 at 1:25 PM,

Re: [sqlite] migrating a pipeline to using sqlite

On Tue, Jul 7, 2009 at 10:20 AM, Michal Seliga wrote: > Robert Citek wrote: >> create table foo ( >> col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, >> col_10, col_11, col_12, col_13, col_14, col_15, col_16, col_17, >> col_18, col_19, col_20, col_

Re: [sqlite] The SQL Guide to SQLite

On Sun, Jul 19, 2009 at 11:23 AM, Rick Ratchford wrote: > Anyway, when I've gathered enough info from the book I'll share my thoughts > on it. It will be from the perspective of a person new to SQLite and SQL. Anyone know of a way to preview the index, the table of contest, or some sample chapters

Re: [sqlite] New Book Available

On Thu, Jul 9, 2009 at 8:50 AM, wrote: > On Thu, 9 Jul 2009, Rich Shepard wrote: > >> Rick van der Laans, who wrote the excellent "Introduction to SQL, 4th Ed." >> (and eariler editions, of course) has just had his new book specific to >> SQLite published. It is another resource for those who wa

[sqlite] migrating a pipeline to using sqlite

I have a command pipeline that I would like to transfer to sqlite. However, the performance under sqlite is significantly slower, which makes me think that I am doing something not quite correctly. How can I improve the performance of this task using sqlite? foo.tsv is a tab-delimited file with 2

[sqlite] importing data to sqlite from stdin

On occasion I have had a need to import large amounts of data from standard input via a pipe. Here's an example of how to import from a pipe using sqlite3 on Ubuntu: $ { grep -v '^#'

Re: [sqlite] sequential row numbers from query

That would work. In fact, my current solution, which actually pipes to perl, works pretty well. It's just that I have to then import the data back into the database. So, I'd prefer to do the process entirely in SQL. I was thinking maybe a view, but that didn't work. Apparently, there is no row

[sqlite] sequential row numbers from query

How can I get a query to display sequential row number in a select statement? I have a simple database similar to this: $ sqlite3 db .dump BEGIN TRANSACTION; CREATE TABLE foo (field); INSERT INTO "foo" VALUES('a'); INSERT INTO "foo" VALUES('b'); INSERT INTO "foo" VALUES('c'); COMMIT; $ sqlite3 d

Re: [sqlite] selecting the top 3 in a group

Thanks, Igor. That worked perfectly. Time for me to read up on rowid and the subtleties of subselects. Regards, - Robert On Thu, Jan 8, 2009 at 6:48 AM, Igor Tandetnik wrote: > select div, team from teams t1 where rowid in > (select rowid from teams t2 where t1.div = t2.div > order by wins de

Re: [sqlite] selecting the top 3 in a group

y wins+0 desc limit 3 ;' done I am still curious to know if there is a purely SQL way to do the same. Regards, - Robert On Thu, Jan 8, 2009 at 12:06 AM, Robert Citek wrote: > In pseudocode, I want to do something similar to this: > > for $i in (select div from teams) { > sel

Re: [sqlite] selecting the top 3 in a group

That gets me the best team in the first five divisions. I would like the top three teams within each division. Regards, - Robert On Thu, Jan 8, 2009 at 12:19 AM, aditya siram wrote: > Hi Robert, > SQL has a LIMIT keyword. I have used it to take the top 'x' entries of a > large table , so for ex

[sqlite] selecting the top 3 in a group

How can I construction a SQL query to pick the top three (3) items in a group? I have a list of sports teams which are grouped into divisions, say A, B, C, D, etc. At the end of the season I would like to get a list of the top three teams (those with the most wins) in each division. If I wanted

Re: [sqlite] replacing underscore with a tab

On Tue, Sep 16, 2008 at 3:49 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > But if you insist on doing it in SQL, this should work: > > sqlite3 foobar.db "select replace(id,'_',cast(x'09' as text)) from bar;" That worked: $ sqlite3 foobar.db 'select replace(id,"_",cast(x"09" as text)) from bar;'

[sqlite] replacing underscore with a tab

How can I replace an underscore ("_") in a field with a tab? This works but seems like suck a hack: $ sqlite3 foobar.db 'select replace(id,"_","{tab}") from bar;' | sed -e 's/{tab}/\t/' I was hoping for a char(9) or similar but couldn't find anything in the docs: http://www.sqlite.org/lang_co

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 4:25 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 03:27:20AM -0500, Robert Citek wrote: >> real 3.25 > .. >> real 22.48 > > I'm seeing the second being twice as -fast- as the first one here, still. I don't follow. 22

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 2:35 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote: >> $ sqlite3 -version >> 3.4.2 > > On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell > pipe. Could you tell us m

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 2:23 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 02:15:54AM -0500, Robert Citek wrote: >> Are you sure time ignores everything after the pipe? > > Seems to depend on shell version - when I tested it here it definitely > ignored everythi

Re: [sqlite] db vs shell

On Tue, Jul 29, 2008 at 1:31 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 01:26:54AM -0500, Robert Citek wrote: >> Why the difference in time? > > Your first test is only measuring how long sqlite needs to 'select foo from > bar'; > all the c

[sqlite] db vs shell

Was doing some DB operations and felt they were going slower than they should. So I did this quick test: $ time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc -l 209 real 5.64 user 5.36 sys 1.51 $ time -p sqlite3 sample.db 'select count(distinct foo) from bar ; ' 200

[sqlite] SQL error: disk I/O error

Hello all, I recently got an error while running a long query with sqlite3 on Ubuntu Gutsy 7.10: SQL error: disk I/O error I googled for a solution but didn't find anything relevant. After a bit of sleuthing on my machine, I discovered that I was running out of disk space. I freed up some disk

Re: [sqlite] Value between changes

On Fri, Mar 14, 2008 at 8:07 AM, BandIT <[EMAIL PROTECTED]> wrote: > I would like a query to output the the time between the status codes (sum) > to know how long each status has been active. I am not so very familiar with > SQL and SQLite, and I hope I am posting to the correct forum. Can you