[sqlite] How to Online Backup In-Memory DB with SQL?
SQLite Online Backup API such as sqlite3_backup***, it's very convenient to backup In-Memory DB to disk. If sqlite also provide SQL to do this, it will bring more convenience for some scenarios, such as pysqlite in python. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 question
您好: 我们使用的SQLITE3 数据库版本为3.6.23.1版本,在红帽编译器下运行,运行的硬件系统为LINUX(2.6版本),现在出现了一个问题,长时间运行后会出现查询数据库返回The database disk image is malformed, 请问这是什么原因造成的.如何解决,使用最新版本3.7.2是否会避免出现这个问题? Dear Sir: I am writing to enquire about a bug we found. Now the SQLITE3 database we use is version 3.6.23.1, which running in the red hat compiler and the hardware system is Linux(version 2.6).We have a problem that for select database(SQL),return The database disk image is malformed ,How to solve my trouble? Is use version 3.7.2 ? Do you have any idea about this? I would like get support for SQLite ? Wish you happiness ! Yours sincerely, Lizhe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Online Backup In-Memory DB with SQL?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/29/2010 11:50 PM, zhangzhenggui wrote: If sqlite also provide SQL to do this, it will bring more convenience for some scenarios, The problem is that the backup process can return busy/locked errors. With C code you can then decide what to do (eg sleep, retry, give up after however many retries etc). That is hard to express in SQL. such as pysqlite in python. APSW has had the functionality since SQLite introduced it. pysqlite had it added to the development code this week. http://groups.google.com/group/python-sqlite Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzLy+YACgkQmOOfHg372QQ42wCfVvYGpTqOzCf9ugBXzDceHwGm DgsAoNf5UGeHAmAsAnYwE8ExCEqS5ajx =GLvB -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Slow SELECT Statements in Large Database file
All, I am having some problems with a new database that I am trying to setup. This database is a large file (about 8.7 GB without indexing). The problem I am having is that SELECT statements are extremely slow. The goal is to get the database file up and running for an embedded application (we have tons of storage space so the size is not a problem). Here is the schema layout: CREATE table Dted (dtedkey INTEGER PRIMARY KEY, dted_lat INTEGER, dted_lon INTEGER, dted_alt FLOAT); We lookup dted_alt based on dted_lat and dted_lon. Here is our SELECT statement: SELECT dted_alt FROM Dted WHERE (dted_lat=%d AND dted_lon=%d); The numbers fed to dted_lat and dted_lon are typically on the order of 37 and -111. What can we do to speed up our SELECT statements? Minutes is unacceptable for our application. We were hoping we could run somewhere on the order of 500 queries per second and get valid results back. I am not an SQL expert, but I was reading about indexes that that it is best to have a specific index per SELECT. Since we only have one, this is the index I am creating now (it has been creating this index on my machine for the past 10 minutes now): CREATE INDEX idx_ll ON Dted(dted_lat,dted_lon); Is that a good index for my SELECT? Will it speed up the accesses? Any thoughts? Thanks! -- Jonathan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fw: sqlite3 support
- Original Message - From: lizhe To: d...@hwaci.com Cc: us...@sqlite.org Sent: Friday, October 29, 2010 2:41 PM Subject: sqlite3 support 您好: 我们使用的SQLITE3 数据库版本为3.6.23.1版本,在红帽编译器下运行,运行的硬件系统为LINUX(2.6版本),现在出现了一个问题,长时间运行后会出现查询数据库返回The database disk image is malformed, 请问这是什么原因造成的.如何解决,使用最新版本3.7.2是否会避免出现这个问题? Dear Sir: I am writing to enquire about a bug we found. Now the SQLITE3 database we use is version 3.6.23.1, which running in the red hat compiler and the hardware system is Linux(version 2.6).We have a problem that for select database(SQL),return The database disk image is malformed ,How to solve my trouble? Is use version 3.7.2 ? Do you have any idea about this? I would like get support for SQLite ? Wish you happiness ! Yours sincerely, Lizhe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 question
Hello Lizhe, in order for the members in this list to help you, please provide more details. First, the most probable cause for this error is that the database file is in fact corrupted in some way or is not a sqlite database file. What step/steps leads to corruption? What sqlite function returns the error? does the error occur always, or only with a certain statement? Can you access the file with the sqlite command line utility? How was the file created? Do you have a file which is ok? Martin Am 30.10.2010 09:21, schrieb lizhe: 您好: 我们使用的SQLITE3 数据库版本为3.6.23.1版本,在红帽编译器下运行,运行的硬件系统为LINUX(2.6版本),现在出现了一个问题,长时间运行后会出现查询数据库返回The database disk image is malformed, 请问这是什么原因造成的.如何解决,使用最新版本3.7.2是否会避免出现这个问题? Dear Sir: I am writing to enquire about a bug we found. Now the SQLITE3 database we use is version 3.6.23.1, which running in the red hat compiler and the hardware system is Linux(version 2.6).We have a problem that for select database(SQL),return The database disk image is malformed ,How to solve my trouble? Is use version 3.7.2 ? Do you have any idea about this? I would like get support for SQLite ? Wish you happiness ! Yours sincerely, Lizhe ___ 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] Loss of Binary Data in Dump File
Please post how you manage to get PHP to use Sqlite3 without PDO as I've been wrestling with that for a while now. Any time I use Sqlite commands, it creates the DB as SQLite2, not 3. On Thu, Oct 28, 2010 at 3:28 AM, Art Age Software artag...@gmail.comwrote: With Roger's help I have gotten to the bottom of this issue, and I wanted to post the resolution to the list in case anyone else bumps into it. There is a bug in PHP versions prior to PHP 5.3 that makes it impossible to reliably create/store BLOB columns. My code ends up creating TEXT data when run under any version of PHP in the 5.2.x branch. When run under PHP 5.3.x, BLOB columns are correctly created. So, the only options are to either use PHP 5.3 or later, or to ditch PDO and use PHP's native Sqlite3 interface directly. Thanks. Sam ___ 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] Loss of Binary Data in Dump File
On 30 Oct 2010, at 10:29pm, Stephen Chrzanowski wrote: Please post how you manage to get PHP to use Sqlite3 without PDO There is yet another SQLite interface: http://php.net/manual/en/book.sqlite3.php It's object-oriented, so of you don't already use OO in PHP you're going to have to learn how. I find it very amusing that SQLite is so useful to PHP users that there are three different APIs to do it. Yes, I know one's obsolete, but it's still included in the default compilation configuration. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loss of Binary Data in Dump File
Thank you. I'll investigate some code change options. On Sat, Oct 30, 2010 at 5:33 PM, Simon Slavin slav...@bigfraud.org wrote: On 30 Oct 2010, at 10:29pm, Stephen Chrzanowski wrote: Please post how you manage to get PHP to use Sqlite3 without PDO There is yet another SQLite interface: http://php.net/manual/en/book.sqlite3.php It's object-oriented, so of you don't already use OO in PHP you're going to have to learn how. I find it very amusing that SQLite is so useful to PHP users that there are three different APIs to do it. Yes, I know one's obsolete, but it's still included in the default compilation configuration. 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] sqlite3 question
Also what's your journal_mode and synchronous setting? ( http://www.sqlite.org/pragma.html#pragma_synchronous) Depending on how these are set, if your application terminates early, or system crashes, you will likely corrupt your DB. 2010/10/30 Martin Engelschalk engelsch...@codeswift.com Hello Lizhe, in order for the members in this list to help you, please provide more details. First, the most probable cause for this error is that the database file is in fact corrupted in some way or is not a sqlite database file. What step/steps leads to corruption? What sqlite function returns the error? does the error occur always, or only with a certain statement? Can you access the file with the sqlite command line utility? How was the file created? Do you have a file which is ok? Martin Am 30.10.2010 09:21, schrieb lizhe: 您好: 我们使用的SQLITE3 数据库版本为3.6.23.1版本,在红帽编译器下运行,运行的硬件系统为LINUX(2.6版本),现在出现了一个问题,长时间运行后会出现查询数据库返回The database disk image is malformed, 请问这是什么原因造成的.如何解决,使用最新版本3.7.2是否会避免出现这个问题? Dear Sir: I am writing to enquire about a bug we found. Now the SQLITE3 database we use is version 3.6.23.1, which running in the red hat compiler and the hardware system is Linux(version 2.6).We have a problem that for select database(SQL),return The database disk image is malformed ,How to solve my trouble? Is use version 3.7.2 ? Do you have any idea about this? I would like get support for SQLite ? Wish you happiness ! Yours sincerely, Lizhe ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT OR IGNORE with rtree virtual tables
Hi, It seems that the OR IGNORE clause is not working with an RTREE virtual table. The documentation (http://sqlite.org/rtree.html, section 3.2) implies that this should work, but is not absolutely clear on the point of conflict handling. I've tried this with both 3.6.19 and 3.7.3 with the same result. In the shell: SQLite version 3.7.3 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE VIRTUAL TABLE a_rt USING rtree( _id, min_x, max_x, min_y, max_y ); sqlite INSERT OR IGNORE INTO a_rt ( _id, min_x, max_x, min_y, max_y ) VALUES( 2, 3, 4, 5, 6 ); sqlite INSERT OR IGNORE INTO a_rt ( _id, min_x, max_x, min_y, max_y ) VALUES( 2, 3, 4, 5, 6 ); Error: constraint failed sqlite Is this a bug, or operating as designed? Thanks, Peter Kolbus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
