Re: [sqlite] Backup-restore behaviour
On 7 Dec 2010, at 5:49pm, Csom Gyula wrote: > I was just wondering how portable is the backup format... (well according to > the backup API - as far as I see the backup format is nothing but the > database file format). Yes, it's just a copy of the source file. No changes. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backup-restore behaviour
Thanks for your response! We are currently running our app on a 64 bit machine (btw OS is Debian GNU/Linux). I was just wondering how portable is the backup format... (well according to the backup API - as far as I see the backup format is nothing but the database file format). Gyula Feladó: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] ; meghatalmaz: Doug Currie [doug.cur...@gmail.com] Küldve: 2010. december 7. 18:40 Címzett: General Discussion of SQLite Database Tárgy: Re: [sqlite] Backup-restore behaviour On Dec 7, 2010, at 10:49 AM, Csom Gyula wrote: > It clarified the situation, that is backup-restore seems to be the best > choice:) Just one more question. As you put backup-restore is based upon data > pages (that could be binary a format I guess) not on plain SQL/data records. > After all: Is the data page/backup format platform indenpendent? For instance > can I restore a database on Windows from a backup created on a Linux box? If your Linux is on ARM, you should pay attention to the SQLITE_MIXED_ENDIAN_64BIT_FLOAT setting for binary compatibility with Windows (or x86 Linux for that matter). e ___ 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] Backup-restore behaviour
On Dec 7, 2010, at 10:49 AM, Csom Gyula wrote: > It clarified the situation, that is backup-restore seems to be the best > choice:) Just one more question. As you put backup-restore is based upon data > pages (that could be binary a format I guess) not on plain SQL/data records. > After all: Is the data page/backup format platform indenpendent? For instance > can I restore a database on Windows from a backup created on a Linux box? If your Linux is on ARM, you should pay attention to the SQLITE_MIXED_ENDIAN_64BIT_FLOAT setting for binary compatibility with Windows (or x86 Linux for that matter). e ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backup-restore behaviour
Again, thanks for your response:) Cheers, Gyula Feladó: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] ; meghatalmaz: Simon Slavin [slav...@bigfraud.org] Küldve: 2010. december 7. 17:00 Címzett: General Discussion of SQLite Database Tárgy: Re: [sqlite] Backup-restore behaviour On 7 Dec 2010, at 3:49pm, Csom Gyula wrote: > Just one more question. As you put backup-restore is based upon data pages > (that could be binary a format I guess) not on plain SQL/data records. After > all: Is the data page/backup format platform indenpendent? For instance can I > restore a database on Windows from a backup created on a Linux box? You are correct. Although this format is documented, it should be treated as an impenetrable black box in most circumstances. Howver, the file format is identical on all platforms that run SQLite version 3. You can, for example, take any SQLite database that was created on an iPhone, and read it on a Windows computer, or vice versa. There are some minor format differences between different versions of SQLite. For instance, if you use the ALTER TABLE ADD COLUMN command introduced in 3.2.0, versions before that will not be able to read the file. However, changes like these are very rare (it's happen just twice and we're up to 3.7 now) and if you keep to relatively similar version numbers on your two platforms you're unlikely to have problems. 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] Backup-restore behaviour
On 7 Dec 2010, at 3:49pm, Csom Gyula wrote: > Just one more question. As you put backup-restore is based upon data pages > (that could be binary a format I guess) not on plain SQL/data records. After > all: Is the data page/backup format platform indenpendent? For instance can I > restore a database on Windows from a backup created on a Linux box? You are correct. Although this format is documented, it should be treated as an impenetrable black box in most circumstances. Howver, the file format is identical on all platforms that run SQLite version 3. You can, for example, take any SQLite database that was created on an iPhone, and read it on a Windows computer, or vice versa. There are some minor format differences between different versions of SQLite. For instance, if you use the ALTER TABLE ADD COLUMN command introduced in 3.2.0, versions before that will not be able to read the file. However, changes like these are very rare (it's happen just twice and we're up to 3.7 now) and if you keep to relatively similar version numbers on your two platforms you're unlikely to have problems. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backup-restore behaviour
Thanks for your reply! It clarified the situation, that is backup-restore seems to be the best choice:) Just one more question. As you put backup-restore is based upon data pages (that could be binary a format I guess) not on plain SQL/data records. After all: Is the data page/backup format platform indenpendent? For instance can I restore a database on Windows from a backup created on a Linux box? Cheers, Gyula Feladó: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] ; meghatalmaz: Simon Slavin [slav...@bigfraud.org] Küldve: 2010. december 7. 14:48 Címzett: General Discussion of SQLite Database Tárgy: Re: [sqlite] Backup-restore behaviour On 7 Dec 2010, at 1:03pm, Csom Gyula wrote: > Based on the documentation available we've already decided to use > the CLI either .backup-.restore or .dump. Possibly simplest to answer some of it here. The dump/read pair use standard SQL commands like SELECT and INSERT. The normal SQL locking system is used. Since no PRAGMAs are used unless you yourself specify them these commands are by default network-safe, and multi-process safe. In contrast the backup/restore operations use the Backup API as described here: http://www.sqlite.org/backup.html It copies the information as pages, not as individual data elements. It does not lock the source file. The process constantly monitors the source file and if it notices any change it automatically restarts from the beginning. Consequently, if you use this system with a database which is constantly being changed it will never finish an entire backup run. However, this system is also network-safe and multi-process safe. > However we have some questions we couldn't find in the > docs, ie.: how these operations work/effect a running application? Namely: > > [1] .backup online? > Is it safe to run a .backup operation online that is without stopping the > application? > > [2] .backup non blocking? > Is the backup operation a non blocking or a blocking one that is: will it > block normal transactions or > not? > > [3] .restore online? > Can someone run a .restore operation online? The only effect we could see so > far is the following. > After a successful restore the client (Ruby+Sequel) detects the schema change > and fails for the > first time, however it refreshes itself and works for subsequent calls... > > [4] Is the .dump operation like normal SQL operations? Is it safe to run it > online? will it block other > SQL operations? [1] yes [2] non-blocking, restarting [3] yes, see below [4] yes, yes, and yes Supplementary note for both '.read' and '.restore': you shouldn't use either of these while an application is accessing the file. They both start off by deleting all the data in the database -- .restore does it for you and .read requires you to do it. Until they're complete, data will be missing from the file. You wouldn't want to leave an application running while you restored the data it was trying to access. 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] Backup-restore behaviour
On 7 Dec 2010, at 1:03pm, Csom Gyula wrote: > Based on the documentation available we've already decided to use > the CLI either .backup-.restore or .dump. Possibly simplest to answer some of it here. The dump/read pair use standard SQL commands like SELECT and INSERT. The normal SQL locking system is used. Since no PRAGMAs are used unless you yourself specify them these commands are by default network-safe, and multi-process safe. In contrast the backup/restore operations use the Backup API as described here: http://www.sqlite.org/backup.html It copies the information as pages, not as individual data elements. It does not lock the source file. The process constantly monitors the source file and if it notices any change it automatically restarts from the beginning. Consequently, if you use this system with a database which is constantly being changed it will never finish an entire backup run. However, this system is also network-safe and multi-process safe. > However we have some questions we couldn't find in the > docs, ie.: how these operations work/effect a running application? Namely: > > [1] .backup online? > Is it safe to run a .backup operation online that is without stopping the > application? > > [2] .backup non blocking? > Is the backup operation a non blocking or a blocking one that is: will it > block normal transactions or > not? > > [3] .restore online? > Can someone run a .restore operation online? The only effect we could see so > far is the following. > After a successful restore the client (Ruby+Sequel) detects the schema change > and fails for the > first time, however it refreshes itself and works for subsequent calls... > > [4] Is the .dump operation like normal SQL operations? Is it safe to run it > online? will it block other > SQL operations? [1] yes [2] non-blocking, restarting [3] yes, see below [4] yes, yes, and yes Supplementary note for both '.read' and '.restore': you shouldn't use either of these while an application is accessing the file. They both start off by deleting all the data in the database -- .restore does it for you and .read requires you to do it. Until they're complete, data will be missing from the file. You wouldn't want to leave an application running while you restored the data it was trying to access. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Backup-restore behaviour
Hi, we are using SQLite3 as an embeded database in our application and we'd like to select the proper backup-restore mechanism. Based on the documentation available we've already decided to use the CLI either .backup-.restore or .dump. However we have some questions we couldn't find in the docs, ie.: how these operations work/effect a running application? Namely: [1] .backup online? Is it safe to run a .backup operation online that is without stopping the application? [2] .backup non blocking? Is the backup operation a non blocking or a blocking one that is: will it block normal transactions or not? [3] .restore online? Can someone run a .restore operation online? The only effect we could see so far is the following. After a successful restore the client (Ruby+Sequel) detects the schema change and fails for the first time, however it refreshes itself and works for subsequent calls... [4] Is the .dump operation like normal SQL operations? Is it safe to run it online? will it block other SQL operations? Cheers, Gyula ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
