Re: [sqlite] Backing up data by date

2004-04-23 Thread Christian Smith
On Thu, 22 Apr 2004, Lloyd thomas wrote:

>Thanks Christian,
>Although that makes sense I can find nowhere on the
>net which explains how to dump the data within a transaction.
>Do you have an example.

It's just like any other select, except bounded by begin and commit
statements, using seperate sqlite_exec calls before and after the dump:

  /* Begin transaction, for data snapshot */
  sqlite_exec( db, "BEGIN TRANSACTION;", ... );

  /* Get data from various tables */
  sqlite_exec( db, "SELECT ", ... );
  sqlite_exec( db, "SELECT ", ... );

  /* Transaction finished, though there is nothing to actually commit */
  sqlite_exec( db, "COMMIT TRANSACTION;", ... );

Of course, if you're dumping a single table or view, or using a single
select statement across multiple tables, then the begin/commit is not
necassary.


>
>Lloyd
>- Original Message -
>From: "Christian Smith" <[EMAIL PROTECTED]>
>To: "Lloyd thomas" <[EMAIL PROTECTED]>
>Cc: <[EMAIL PROTECTED]>
>Sent: Thursday, April 22, 2004 4:21 PM
>Subject: Re: [sqlite] Backing up data by date
>
>
>> On Wed, 21 Apr 2004, Lloyd thomas wrote:
>>
>> >Forgive my ignorance, I have yet to use a transaction and therefore can
>you
>> >give me an example.
>>
>> Transactions are simply SQL statements bounded by a begin/commit pair, or
>> begin/rollback if the transaction is to be aborted:
>>
>> begin transaction;
>> [do something]
>> commit transaction;
>>
>> If [do something] is a single select, then you don't need the
>> begin/commit. You need the begin/commit if you have multiple selects, to
>> prevent the database changing underneath you in between select statements.
>>
>> You can play with transactions simply from the sqlite command line tool.
>>
>> Any decent SQL guide or web page should explain transactions.
>>
>> >
>> >Lloyd
>> >- Original Message -
>> >From: "Christian Smith" <[EMAIL PROTECTED]>
>> >To: "Lloyd thomas" <[EMAIL PROTECTED]>
>> >Cc: <[EMAIL PROTECTED]>
>> >Sent: Wednesday, April 21, 2004 3:27 PM
>> >Subject: Re: [sqlite] Backing up data by date
>> >
>> >
>> >> On Tue, 20 Apr 2004, Lloyd thomas wrote:
>> >>
>> >> >Which is the best way to backup rows which meet a certain date
>criteria?
>> >> >ie WHERE data is <= '2003-11-20'.
>> >> >Would I need to select and save the data to a temporary table and then
>> >DUMP the temp table.
>> >> >
>> >>
>> >> Just begin a transaction to get a snapshot of the database, then dump
>the
>> >> data within the transaction. No temporary table needed. Will lock the
>> >> database, but it is the only way to ensure a consistent view unless you
>> >> are saving data from a single table or view, in which case you won't
>need
>> >> the transaction.
>> >>
>> >> Christian
>> >>
>> >> --
>> >> /"\
>> >> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
>> >>  X   - AGAINST MS ATTACHMENTS
>> >> / \
>> >>
>> >> -
>> >> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> >> For additional commands, e-mail: [EMAIL PROTECTED]
>> >>
>> >>
>> >
>> >
>> >
>> >-
>> >To unsubscribe, e-mail: [EMAIL PROTECTED]
>> >For additional commands, e-mail: [EMAIL PROTECTED]
>> >
>>
>> --
>> /"\
>> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
>>  X   - AGAINST MS ATTACHMENTS
>> / \
>>
>> -
>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>
>
>
>-
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] Backing up data by date

2004-04-22 Thread Christian Smith
On Wed, 21 Apr 2004, Lloyd thomas wrote:

>Forgive my ignorance, I have yet to use a transaction and therefore can you
>give me an example.

Transactions are simply SQL statements bounded by a begin/commit pair, or
begin/rollback if the transaction is to be aborted:

begin transaction;
[do something]
commit transaction;

If [do something] is a single select, then you don't need the
begin/commit. You need the begin/commit if you have multiple selects, to
prevent the database changing underneath you in between select statements.

You can play with transactions simply from the sqlite command line tool.

Any decent SQL guide or web page should explain transactions.

>
>Lloyd
>- Original Message -
>From: "Christian Smith" <[EMAIL PROTECTED]>
>To: "Lloyd thomas" <[EMAIL PROTECTED]>
>Cc: <[EMAIL PROTECTED]>
>Sent: Wednesday, April 21, 2004 3:27 PM
>Subject: Re: [sqlite] Backing up data by date
>
>
>> On Tue, 20 Apr 2004, Lloyd thomas wrote:
>>
>> >Which is the best way to backup rows which meet a certain date criteria?
>> >ie WHERE data is <= '2003-11-20'.
>> >Would I need to select and save the data to a temporary table and then
>DUMP the temp table.
>> >
>>
>> Just begin a transaction to get a snapshot of the database, then dump the
>> data within the transaction. No temporary table needed. Will lock the
>> database, but it is the only way to ensure a consistent view unless you
>> are saving data from a single table or view, in which case you won't need
>> the transaction.
>>
>> Christian
>>
>> --
>> /"\
>> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
>>  X   - AGAINST MS ATTACHMENTS
>> / \
>>
>> -
>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>
>
>
>-
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] Backing up data by date

2004-04-21 Thread Lloyd thomas
Forgive my ignorance, I have yet to use a transaction and therefore can you
give me an example.

Lloyd
- Original Message - 
From: "Christian Smith" <[EMAIL PROTECTED]>
To: "Lloyd thomas" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, April 21, 2004 3:27 PM
Subject: Re: [sqlite] Backing up data by date


> On Tue, 20 Apr 2004, Lloyd thomas wrote:
>
> >Which is the best way to backup rows which meet a certain date criteria?
> >ie WHERE data is <= '2003-11-20'.
> >Would I need to select and save the data to a temporary table and then
DUMP the temp table.
> >
>
> Just begin a transaction to get a snapshot of the database, then dump the
> data within the transaction. No temporary table needed. Will lock the
> database, but it is the only way to ensure a consistent view unless you
> are saving data from a single table or view, in which case you won't need
> the transaction.
>
> Christian
>
> -- 
> /"\
> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
>  X   - AGAINST MS ATTACHMENTS
> / \
>
> -
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



Re: [sqlite] Backing up data by date

2004-04-21 Thread Christian Smith
On Tue, 20 Apr 2004, Lloyd thomas wrote:

>Which is the best way to backup rows which meet a certain date criteria?
>ie WHERE data is <= '2003-11-20'.
>Would I need to select and save the data to a temporary table and then DUMP the temp 
>table.
>

Just begin a transaction to get a snapshot of the database, then dump the
data within the transaction. No temporary table needed. Will lock the
database, but it is the only way to ensure a consistent view unless you
are saving data from a single table or view, in which case you won't need
the transaction.

Christian

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]