Re: [sqlite] table backup

[Hick Gunter]

You could write a virtual table extension that performs pre-image (in the 
xUpdate method) or after-image (in the xCommit method) logging of write 
transactions to a CSV file or even an SQL Logfile.

The biggest challenge would be writing the xBestIndex function to properly 
return an optimal execution plan. Parsing the result of "explain query plan" 
may or may not be an option.

It also depends on the SQL statements used for updates. If you only use "UPDATE 
 SET  WHERE " then it would be relatively 
simple.

Gunter

-Ursprüngliche Nachricht-
Von: dd [mailto:durga.d...@gmail.com]
Gesendet: Montag, 10. Dezember 2012 06:45
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] table backup

Sometimes, sqlite databse corrupts. So, I want to take online backup of
specific table. Not entire database. Any best solution?


On Sun, Dec 9, 2012 at 11:29 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/12/12 10:35, dd wrote:
> > i mean, for every write operation in a table.
>
> You should specify some of your other constraints and exactly what it is
> you are trying to achieve (*not* how you are trying to achieve it).
>
> The solutions range from implementing undo by using triggers to save old
> values, through using a virtual table that backs up data somewhere else.
>
> The SQLite backup API only works on the whole database.
>
> Roger
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlDE5poACgkQmOOfHg372QQI8gCeP6GfezJUWQ7M3pdfvvgc2FCp
> J1QAoMSM/tbpxlicrrDpTbgjHAsWln1d
> =mPMA
> -END PGP SIGNATURE-
> ___
> 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


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table backup

[dd]

Sometimes, sqlite databse corrupts. So, I want to take online backup of
specific table. Not entire database. Any best solution?


On Sun, Dec 9, 2012 at 11:29 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 09/12/12 10:35, dd wrote:
> > i mean, for every write operation in a table.
>
> You should specify some of your other constraints and exactly what it is
> you are trying to achieve (*not* how you are trying to achieve it).
>
> The solutions range from implementing undo by using triggers to save old
> values, through using a virtual table that backs up data somewhere else.
>
> The SQLite backup API only works on the whole database.
>
> Roger
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAlDE5poACgkQmOOfHg372QQI8gCeP6GfezJUWQ7M3pdfvvgc2FCp
> J1QAoMSM/tbpxlicrrDpTbgjHAsWln1d
> =mPMA
> -END PGP SIGNATURE-
> ___
> 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] table backup

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/12/12 10:35, dd wrote:
> i mean, for every write operation in a table.

You should specify some of your other constraints and exactly what it is
you are trying to achieve (*not* how you are trying to achieve it).

The solutions range from implementing undo by using triggers to save old
values, through using a virtual table that backs up data somewhere else.

The SQLite backup API only works on the whole database.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAlDE5poACgkQmOOfHg372QQI8gCeP6GfezJUWQ7M3pdfvvgc2FCp
J1QAoMSM/tbpxlicrrDpTbgjHAsWln1d
=mPMA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table backup

[dd]

i mean, for every write operation in a table.


On Sun, Dec 9, 2012 at 11:55 PM, dd  wrote:

> Hi All,
>
>   What is the best way to do online backup for a particular table for
> every write operation in a sqlite db. Not entire database.
>
> Best Regards,
> d
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] table backup

[dd]

Hi All,

  What is the best way to do online backup for a particular table for every
write operation in a sqlite db. Not entire database.

Best Regards,
d
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

[noel.frankinet]

Le 9/12/2012 13:40, Gilles Ganault a écrit :

On Sun, 9 Dec 2012 11:40:15 +, Simon Slavin
 wrote:

If you think you can make money by providing a basic app, write one and market 
it.

People don't want one.  If they have already paid for and learned to use Excel, 
they'll use that.
If they haven't, they'll use anything that's free.  They don't care whether the 
data engine is SQLite or anything else.

Yup.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


-
Aucun virus trouve dans ce message.
Analyse effectuee par AVG - www.avg.fr
Version: 2013.0.2793 / Base de donnees virale: 2634/5938 - Date: 05/12/2012




I, for one, think that its a good idea, and some people would pay money 
for it. I would for instance.


Best regards
Noël
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk image malformed

[dd]

I do Integrity check every often.


On Sun, Dec 9, 2012 at 5:07 PM, Simon Slavin  wrote:

>
> On 9 Dec 2012, at 12:44pm, dd  wrote:
>
> > Can I read the database when sqlite throws disk io or image malformed or
> > other critical errors first time. I cannot simulate these issues on my
> > machine. So, I need your suggestion.
>
> You should not continue to work with the database in your own application
> on customer computers.
>
> I would recommend that you copy the database to your own computer and open
> it with the sqlite3 shell tool:
>
> 
>
> you will find a copy on every Mac in '/usr/bin/sqlite3'.
>
> You can immediately run
>
> 
>
> and find out whether the error means that the file itself is corrupt.  If
> the file fails the integrity check then the file is corrupt.  if it
> doesn't, it means that something went wrong (software or hardware) on your
> customer's computer but that this didn't cause anything bad to be written
> to the database file.
>
> Simon.
> ___
> 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] Subject: Re: Simple SQLite-based spreadsheet?

[Michael Black]

You're finding out that "simple" and "complete" are frequently mutually
exclusiveespecially when defined by you.
Chances are that if what you want doesn't exist there's a good reason for
it.either not practical or not useful or doable by other means already.
To make a powerful, idiot proof system is quite difficult.

By the time you get to DB functionality you will lose most users.

Do you perhaps want a web-based version?
http://www.debianhelp.co.uk/sqliteweb.htm

Generally speaking database and spreadsheet functionality are not similar
enough to combine.
Why do you think Excel and Access are two separate products?  Just like all
other office suites?
They narrow their comp ability problems by importing/exporting to each
other.

The only concept they have in common is rows/columns/tables -- and
spreadsheets didn't have tables at first (i.e. tabs).  But the spreadsheet
rows/columns is not really db rows/columns.  The spreadsheet version is
transposable for example making the row/column definition very loose.

P.S. I've changed my sqlite mail subscription to my private email now.
Found out that Microsquish Exchange doesn't put the REFERENCES tag back in
replies.  So that was breaking the topic threading. 

Michael Black


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gilles Ganault
Sent: Sunday, December 09, 2012 5:14 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

On Sun, 09 Dec 2012 00:04:40 +0100, Olaf Schmidt
 wrote:
>If no such special Formatting is needed, then the
>term "DataGrid" is the more common one, since
>"real SpreadSheet functionality" is usually associated
>with the extended requirements (at individual cell-level)
>I've listed above.

Thanks for the input. Indeed, a datagrid looks more like what I had in
mind, since people using Excel just to build lists probably don't need
that much control.

OTOH, whoever writes that application could always provide two
version: Basic (datagrid) and Pro (spreadsheet).

SQLite being such a great tool, I just find it sad/odd that no one has
come up with a datagrid/spreadsheet for non-techies that saves data in
an SQLite DB. Currently, it's either Excel for most people although
it's not a DB, or Access for the few (or Libre/OpenOffice + ODBC/JDBC,
which is just as hard or harder than Access).

Thank you.

___
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] Subject: Re: Simple SQLite-based spreadsheet?

[Olaf Schmidt]

Am 09.12.2012 12:40, schrieb Simon Slavin:


On 9 Dec 2012, at 11:13am, Gilles Ganault  wrote:


OTOH, whoever writes that application could always provide two
version: Basic (datagrid) and Pro (spreadsheet).


If you think you can make money by providing a basic app, write one and market 
it.

People don't want one.  If they have already paid for and learned to use Excel,
they'll use that.  If they haven't, they'll use anything that's free.
They don't care whether the data engine is SQLite or anything else.



Yep - and for those who want "Excel-like Editing-Comfort
for simple Data-Lists" - there's always the option,
to work with Excel or OpenOffice-Calc on *.csv-Files
(*.csv usually opens without problems in a SpreadSheet-
 Application).

@Gilles
The only thing remaining for a decent workflow, which in
the end is based on SQLite-storage, would then be a small
batch-program or -script, which ensures the SQLite-To-CSV
conversion (the sqlite-commandline-tool could do that) -
accompanied by a second batch-file which ensures the
back-conversion from CSV to SQLite-DBTable (and maybe
also already the direct upload to a given WebServer).

Along with a tool or script which is "Watching for Folder-Changes",
one could automate that entirely, including a potential
WebServer-Synchronizing.

Olaf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk image malformed

[Simon Slavin]

On 9 Dec 2012, at 12:44pm, dd  wrote:

> Can I read the database when sqlite throws disk io or image malformed or
> other critical errors first time. I cannot simulate these issues on my
> machine. So, I need your suggestion.

You should not continue to work with the database in your own application on 
customer computers.

I would recommend that you copy the database to your own computer and open it 
with the sqlite3 shell tool:



you will find a copy on every Mac in '/usr/bin/sqlite3'.

You can immediately run 



and find out whether the error means that the file itself is corrupt.  If the 
file fails the integrity check then the file is corrupt.  if it doesn't, it 
means that something went wrong (software or hardware) on your customer's 
computer but that this didn't cause anything bad to be written to the database 
file.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk image malformed

[Kees Nuyt]

On Sun, 9 Dec 2012 16:44:13 +0400, dd  wrote:

> Can I read the database when sqlite throws disk io or image malformed or
> other critical errors first time. I cannot simulate these issues on my
> machine. So, I need your suggestion.

Here are a few suggestions to simulate (or rather, to force) corruption:
http://sqlite.org/lockingv3.html#how_to_corrupt

You may need http://sqlite.org/fileformat2.html for more insight.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk image malformed

[dd]

Can I read the database when sqlite throws disk io or image malformed or
other critical errors first time. I cannot simulate these issues on my
machine. So, I need your suggestion.


On Sun, Dec 9, 2012 at 4:40 PM, dd  wrote:

> Thank you Simon Slavin.
>
>
> On Sun, Dec 9, 2012 at 4:27 PM, Simon Slavin  wrote:
>
>>
>> On 9 Dec 2012, at 12:10pm, dd  wrote:
>>
>> > I have code in C++. Right now, I got this issue on Mac.
>>
>> Oh.  Then you don't have to worry about Windows codepages.  All your text
>> is already in Unicode.
>>
>> >>> you still have faulty hardware or software
>> >
>> > faulty software means? (OS or sqlite version)
>>
>> The software you wrote.  Your C++ code.  You may be writing to memory
>> that SQLite is using.  You may not be allocating or disposing of memory
>> correctly.  The C programming language is very bad at allowing you to do
>> all sorts of dangerous things.  Every C programmer makes mistakes like this
>> from time to time.
>>
>> Make sure that you are checking the return values of all your calls to
>> the SQLite API, reporting any unexpected values and immediately halting
>> your application.  That is the easiest way to detect problems as soon as
>> they happen.  If you don't do this then your application may continue to
>> use the database, writing corrupted memory to the database file.
>>
>> Simon.
>> ___
>> 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] Subject: Re: Simple SQLite-based spreadsheet?

[Gilles Ganault]

On Sun, 9 Dec 2012 11:40:15 +, Simon Slavin
 wrote:
>If you think you can make money by providing a basic app, write one and market 
>it.
>
>People don't want one.  If they have already paid for and learned to use 
>Excel, they'll use that.
> If they haven't, they'll use anything that's free.  They don't care whether 
> the data engine is SQLite or anything else.

Yup.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk image malformed

[dd]

Thank you Simon Slavin.


On Sun, Dec 9, 2012 at 4:27 PM, Simon Slavin  wrote:

>
> On 9 Dec 2012, at 12:10pm, dd  wrote:
>
> > I have code in C++. Right now, I got this issue on Mac.
>
> Oh.  Then you don't have to worry about Windows codepages.  All your text
> is already in Unicode.
>
> >>> you still have faulty hardware or software
> >
> > faulty software means? (OS or sqlite version)
>
> The software you wrote.  Your C++ code.  You may be writing to memory that
> SQLite is using.  You may not be allocating or disposing of memory
> correctly.  The C programming language is very bad at allowing you to do
> all sorts of dangerous things.  Every C programmer makes mistakes like this
> from time to time.
>
> Make sure that you are checking the return values of all your calls to the
> SQLite API, reporting any unexpected values and immediately halting your
> application.  That is the easiest way to detect problems as soon as they
> happen.  If you don't do this then your application may continue to use the
> database, writing corrupted memory to the database file.
>
> Simon.
> ___
> 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] disk image malformed

[Simon Slavin]

On 9 Dec 2012, at 12:10pm, dd  wrote:

> I have code in C++. Right now, I got this issue on Mac.

Oh.  Then you don't have to worry about Windows codepages.  All your text is 
already in Unicode.

>>> you still have faulty hardware or software
> 
> faulty software means? (OS or sqlite version)

The software you wrote.  Your C++ code.  You may be writing to memory that 
SQLite is using.  You may not be allocating or disposing of memory correctly.  
The C programming language is very bad at allowing you to do all sorts of 
dangerous things.  Every C programmer makes mistakes like this from time to 
time.

Make sure that you are checking the return values of all your calls to the 
SQLite API, reporting any unexpected values and immediately halting your 
application.  That is the easiest way to detect problems as soon as they 
happen.  If you don't do this then your application may continue to use the 
database, writing corrupted memory to the database file.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk image malformed

[dd]

I have code in C++. Right now, I got this issue on Mac.

>>you still have faulty hardware or software

faulty software means? (OS or sqlite version)




On Sun, Dec 9, 2012 at 3:37 PM, Simon Slavin  wrote:

>
> On 9 Dec 2012, at 7:34am, dd  wrote:
>
> > I have 10 databases. One database got disk io error, later it's used by
> > application without proper handling, so it's corrupted.
> >
> > After some days, one more database also corrupted without io error.
> Sqlite
> > returned error code 11. It's fully corrupted. Are these corruptions
> depend
> > on other database integrity?
>
> Corruption may spread from one database to another if you have a corrupt
> database open at the same time as an uncorrupt database.  It is rare but it
> can happen.  If you are no longer opening corrupt database files and are
> still getting more corruption you still have faulty hardware or software.
>
> > I am using this database for "schengen countries" customers. Can sqlite
> > handle these characters(special)?
>
> SQLite handles text in UTF-8 or UTF-16 format.  If you are storing data in
> a database relying on other Windows codepages then you will get strange
> results.  If you are allowing non-Roman characters and rely on your user's
> codepage when handling text, use a routine like 'WideCharToMultiByte':
>
> 
>
> to convert the data to UTF-8 or UTF-16 before storing it in the SQLite
> database.  Actually it's easier to convert your text to Unicode immediately
> you receive it from your user.  You do not have to use the above routine, I
> provide it just as an example.
>
> I can't help more without knowing what OS and programming language you're
> using but maybe someone else can.
>
> Simon.
> ___
> 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] Subject: Re: Simple SQLite-based spreadsheet?

[Simon Slavin]

On 9 Dec 2012, at 11:13am, Gilles Ganault  wrote:

> OTOH, whoever writes that application could always provide two
> version: Basic (datagrid) and Pro (spreadsheet).

If you think you can make money by providing a basic app, write one and market 
it.

People don't want one.  If they have already paid for and learned to use Excel, 
they'll use that.  If they haven't, they'll use anything that's free.  They 
don't care whether the data engine is SQLite or anything else.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] disk image malformed

[Simon Slavin]

On 9 Dec 2012, at 7:34am, dd  wrote:

> I have 10 databases. One database got disk io error, later it's used by
> application without proper handling, so it's corrupted.
> 
> After some days, one more database also corrupted without io error. Sqlite
> returned error code 11. It's fully corrupted. Are these corruptions depend
> on other database integrity?

Corruption may spread from one database to another if you have a corrupt 
database open at the same time as an uncorrupt database.  It is rare but it can 
happen.  If you are no longer opening corrupt database files and are still 
getting more corruption you still have faulty hardware or software.

> I am using this database for "schengen countries" customers. Can sqlite
> handle these characters(special)?

SQLite handles text in UTF-8 or UTF-16 format.  If you are storing data in a 
database relying on other Windows codepages then you will get strange results.  
If you are allowing non-Roman characters and rely on your user's codepage when 
handling text, use a routine like 'WideCharToMultiByte':



to convert the data to UTF-8 or UTF-16 before storing it in the SQLite 
database.  Actually it's easier to convert your text to Unicode immediately you 
receive it from your user.  You do not have to use the above routine, I provide 
it just as an example.

I can't help more without knowing what OS and programming language you're using 
but maybe someone else can.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

[Gilles Ganault]

On Sun, 09 Dec 2012 00:04:40 +0100, Olaf Schmidt
 wrote:
>If no such special Formatting is needed, then the
>term "DataGrid" is the more common one, since
>"real SpreadSheet functionality" is usually associated
>with the extended requirements (at individual cell-level)
>I've listed above.

Thanks for the input. Indeed, a datagrid looks more like what I had in
mind, since people using Excel just to build lists probably don't need
that much control.

OTOH, whoever writes that application could always provide two
version: Basic (datagrid) and Pro (spreadsheet).

SQLite being such a great tool, I just find it sad/odd that no one has
come up with a datagrid/spreadsheet for non-techies that saves data in
an SQLite DB. Currently, it's either Excel for most people although
it's not a DB, or Access for the few (or Libre/OpenOffice + ODBC/JDBC,
which is just as hard or harder than Access).

Thank you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subject: Re: Simple SQLite-based spreadsheet?

[Gilles Ganault]

On Sun, 09 Dec 2012 00:49:03 +0100, Jean-Christophe Deschamps
 wrote:
>What are those $20 suppose to pay for? 

Licenses by users interested in buying that utility optimized to build
lists and sort data much better than in Excel (which isn't meant for
that, but people use it anyway since they don't know any better).

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users