Re: [sqlite] Nested transactions

On Jul 25, 2009, at 2:26 AM, Pavel Ivanov wrote: > First of all in case you've mentioned it will be not nested > transaction but one transaction including all deletes and it will be > committed when select statement will be finished. This was true for a long time. But as of version 3.6.5,

Re: [sqlite] a system for arbitrarily tagging rows in a table

On Fri, Jul 24, 2009 at 09:20:29PM -0500, P Kishor scratched on the wall: > I am trying to develop a "tagging" system, whereby each row in a table > can be tagged with arbitrary number of tags. This smells of a Relational division problem. If you're dealing with tags you might want to have a

Re: [sqlite] a system for arbitrarily tagging rows in a table

On 25 Jul 2009, at 3:20am, P Kishor wrote: > I am trying to develop a "tagging" system, whereby each row in a table > can be tagged with arbitrary number of tags. As an alternative for using a table for tags, consider using a long string instead. The default value for this column would be

Re: [sqlite] Reporting on summary data

On 25/07/2009 11:59 AM, David Bicking wrote: > On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote: >> An accounting system where the sign of the amount is detached and has to >> be obtained from another column is tedious and error-prone; obtaining it >> from TWO columns is "interesting"; >

[sqlite] a system for arbitrarily tagging rows in a table

I am trying to develop a "tagging" system, whereby each row in a table can be tagged with arbitrary number of tags. TABLE foo (f_id INTEGER PRIMARY KEY, f_name TEXT); TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT); TABLE foo_tag (f_id INTEGER, t_id INTEGER); foo 1, one 2, two 3, three 4,

Re: [sqlite] Reporting on summary data

On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote: > On 25/07/2009 6:17 AM, David Bicking wrote: > > That works. Thanks! > > It struck me that Pavel's revised query didn't mention the d2 column at > all, only d1: > Thanks for the additional info. In real life, I added WHERE d1='X' OR D2='X'

Re: [sqlite] Fastest way to insert lots of data

Try to add to your SQLite test these steps: 3.5) Execute statement BEGIN (you can do it via prepared statement). 5.5) Say every 10 000 records execute COMMIT and then BEGIN again. Or if you indeed want to measure maximum write speed then skip this step at all. And your words that pragma

Re: [sqlite] Reporting on summary data

On 25/07/2009 6:17 AM, David Bicking wrote: > That works. Thanks! It struck me that Pavel's revised query didn't mention the d2 column at all, only d1: >> sum(case when d1='X' then 1 else -1 end) as act_sum, >> sum(case when d1='X' then amt else -amt end) as net ... backtracking, it seems that

[sqlite] Fastest way to insert lots of data

What kind of performance can I expect to get from sqlite inserting many many rows of blob data into a database? I know this depends on many factors, but I'm a little puzzled because I have written some tests that allow me to test various ways of inserting data and as far as I can tell I've

Re: [sqlite] Reporting on summary data

That works. Thanks! > From: Pavel Ivanov > Oops, sorry! I misunderstood what you > need. Try this: > > select id, > case when act_sum = 1 then 'NEW' > when act_sum = 0 then 'CHANGE' > else 'DROP' > end as Action, > net > from > ( > select id, > sum(case when d1='X' then 1

Re: [sqlite] Reporting on summary data

Oops, sorry! I misunderstood what you need. Try this: select id, case when act_sum = 1 then 'NEW' when act_sum = 0 then 'CHANGE' else 'DROP' end as Action, net from ( select id, sum(case when d1='X' then 1 else -1 end) as act_sum, sum(case when d1='X' then amt else -amt end) as net from Source

Re: [sqlite] Reporting on summary data

> From: Pavel Ivanov > > Just do the same approach with CASE you've already used: > > Select ID, > CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW' >     WHEN d1 IS NULL AND d2='X' THEN 'DROP' >     ELSE 'CHANGE' END AS Action, > Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) -

Re: [sqlite] Nested transactions

First of all in case you've mentioned it will be not nested transaction but one transaction including all deletes and it will be committed when select statement will be finished. As a second note: it's pretty bad idea to change table which is used in not yet finished select statement. In SQLite

[sqlite] Nested transactions

Hello, just a short question: With the current sqlite version, is it possible to have nested transactions, e.g. exec select ... from table while (fetch row) { if (criteria) exec delete from t where ... } which means execute some 'select', fetching the

Re: [sqlite] Reporting on summary data

> And how can I calculate the value for Action? Just do the same approach with CASE you've already used: Select ID, CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW' WHEN d1 IS NULL AND d2='X' THEN 'DROP' ELSE 'CHANGE' END AS Action, Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN

[sqlite] Reporting on summary data

I am trying to create a report. The source table can have one or two records for each given ID. If there is a record with D1='X' but no record with D2='X', then it is a "NEW" action. If there are both D1 and D2 record, it is a "CHANGE" and the Net amount is the D1 amount less the D2 amount. If

Re: [sqlite] SQLite: Porting to another Operating system.

On Jul 24, 2009, at 8:44 AM, D. Richard Hipp wrote: > SQLite database files are cross-platform. All you have to do is copy > the file to the new machine. There is no separate "external format". > The same database file format work on all platforms. Just make sure that if you are moving to a

Re: [sqlite] SQLite: Porting to another Operating system.

Re: "I can't see how you get to that page." I found the link from page: http://www.sqlite.org/features.html And perhaps it's linked from others as well. Re: "I would expect to reorganise the physical database on a regular basis..." Once again, I'd look at the VACUUM command, though

Re: [sqlite] SQLite: Porting to another Operating system.

On Fri, 24 Jul 2009, CityDev wrote: > I'm only familiar with DB2, Access Jet and Focus. In each case I would > expect to reorganise the physical database on a regular basis - maybe > daily or weekly. What's the best way of doing that with SQLite? Vacuum. Rich -- Richard B. Shepard, Ph.D.

Re: [sqlite] Installing SQLite

On Fri, 24 Jul 2009, Simon Slavin wrote: > SQLite has enough fans that people are happy to build binaries for > major platforms. I have a SlackBuild script that I've used since sqlite-3.3.1 on June 15, 2006. It builds a Slackware package (*.tgz) on my standard system (currently -12.2). In the

Re: [sqlite] SQLite: Porting to another Operating system.

Thanks Donald. I can't see how you get to that page off the documentation menu but now I can go direct. I'm only familiar with DB2, Access Jet and Focus. In each case I would expect to reorganise the physical database on a regular basis - maybe daily or weekly. What's the best way of doing that

Re: [sqlite] SQLite: Porting to another Operating system.

On 24 Jul 2009, at 2:09pm, CityDev wrote: > All you have to do is copy > > That's handy - I didn't realise that. However I suggest it's good > practice > to dump and reload in these kinds of situations. I don't yet know > how SQLite > works but I suspect a reload will get the physical data

Re: [sqlite] Installing SQLite

On 24 Jul 2009, at 2:51pm, Jay A. Kreibich wrote: > As an experienced user of SQLite, I guess I'd rather have the > developers working on great new database features, rather than build > scripts. SQLite has enough fans that people are happy to build binaries for major platforms. If the

Re: [sqlite] SQLite: Porting to another Operating system.

Hi, CityDev, Regarding: All you have to do is copy That's handy - I didn't realise that. However I suggest it's good practice to dump and reload in these kinds of situations. I don't yet know how SQLite works but I suspect a reload will get the physical data into a better shape and clear out

Re: [sqlite] An index question

On 25/07/2009 2:14 AM, Jon Dixon wrote: > In the description of the "Create Index" statement, it says: > > "Every time the database is opened, > all CREATE INDEX statements > are read from the sqlite_master table and used to regenerate > SQLite's internal representation of the index layout." > >

Re: [sqlite] An index question

On Jul 24, 2009, at 12:14 PM, Jon Dixon wrote: > In the description of the "Create Index" statement, it says: > > "Every time the database is opened, > all CREATE INDEX statements > are read from the sqlite_master table and used to regenerate > SQLite's internal representation of the index

[sqlite] An index question

In the description of the "Create Index" statement, it says: "Every time the database is opened, all CREATE INDEX statements are read from the sqlite_master table and used to regenerate SQLite's internal representation of the index layout." Does this mean that all of the indices are regenerated

[sqlite] Ability to convert Access to SQLite

sqlite-users-requ...@sqlite.org wrote: > -- > > Message: 5 > Date: Wed, 22 Jul 2009 09:34:48 -0700 (PDT) > From: scabral > Subject: [sqlite] Ability to convert Access to SQLite > To: sqlite-users@sqlite.org > Message-ID:

Re: [sqlite] Installing SQLite

On Fri, Jul 24, 2009 at 02:19:36AM +0700, Dan scratched on the wall: > > On Jul 23, 2009, at 10:50 PM, Jay A. Kreibich wrote: > > > On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched on > > the wall: > > > > The amalgimation works well enough if what you want is mostly > >

Re: [sqlite] SQLite: Porting to another Operating system.

D. Richard Hipp wrote: > On Jul 24, 2009, at 8:37 AM, CityDev wrote: > > >> I'm new to SQLite. I would assume you would dump the tables to an >> external >> format and then load them into the new database. I can't however see >> where >> the documentation is for this kind of database

Re: [sqlite] SQLite: Porting to another Operating system.

All you have to do is copy That's handy - I didn't realise that. However I suggest it's good practice to dump and reload in these kinds of situations. I don't yet know how SQLite works but I suspect a reload will get the physical data into a better shape and clear out deleted items etc. Do

Re: [sqlite] SQLite: Porting to another Operating system.

On Jul 24, 2009, at 8:37 AM, CityDev wrote: > > I'm new to SQLite. I would assume you would dump the tables to an > external > format and then load them into the new database. I can't however see > where > the documentation is for this kind of database management function. > Anyone > know

Re: [sqlite] SQLite: Porting to another Operating system.

I'm new to SQLite. I would assume you would dump the tables to an external format and then load them into the new database. I can't however see where the documentation is for this kind of database management function. Anyone know where I should look, or do you have to download the SQLite3

Re: [sqlite] SQLite OLEDB provider for Linked Server

Thanks for response, but already tried that... Their activation does not work either... Will not activate for SQLite but does for MySQL... -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of CityDev Sent: Friday, July 24, 2009

Re: [sqlite] Installing SQLite

Hi All; I'm a newbie trying to get up to speed on SQL and SQLite and first- thanks for the product, and thanks for the group access and the chance to extensively learn. I agree with the following and would volunteer to document my learning experience. I used this thread as an opportunity to

[sqlite] SQLite: Porting to another Operating system.

Hi, I am using SQLite amalgamation package and linking the compiled sqlite3.o along with my application. The application runs on x86 and MIPS (BIG Endian) processors having Linux has its Operating system. We now have plans to port the application to a different Operating system. Is

Re: [sqlite] SQLite OLEDB provider for Linked Server

It looks like you get an activation code from their site, which can either be temporary or you can pay for a proper one. -- View this message in context: http://www.nabble.com/SQLite-OLEDB-provider-for-Linked-Server-tp24635046p24640083.html Sent from the SQLite mailing list archive at