更好的SQLite的损坏检测 [英] Better SQLite corruption detection
问题描述
首先，一些背景：
我的机器人应用具有数据库表有很多的四柱行。它发送请求到服务器和服务器只响应当所有这四个值是有效。几个成千上万的用户报告说，一些不为他们工作（因为一段时间，他们没有得到结果从服务器） - 我试图找出什么是造成问题的原因和事实证明，唯一可能的原因是这不是被检测数据库损坏。
My Android app has DB table with a lot of four-column rows. It sends requests to the server and server responds only when all of these four values are "valid". A few of the thousands users reported that something doesn't work for them (since awhile they are not getting the results from the server) - I was trying to figure out what's causing the problem and it turned out that the only possible cause is the DB corruption that's not being detected.
在ACRA的日志我已经得到了与SQL错误的一些消息，但这些有关应用程序不能够打开，因为它被损坏的文件。这给了我一些线索，但是我仍然不认为这是问题。所以，我创建了改变数据库文件和检查的SQLite将如何处理与随机字节一个非常简单的Python脚本：
In ACRA logs I've got some messages with SQL errors, but these were about application not being able to open the file because of it being corrupt. That gave me some clue, but I was still not convinced that this is the issue. So, I created a very simple Python script which changes random bytes in the DB file and checks how SQLite will deal with that:
import random
import array
import sqlite3
db = array.array('B')
db.fromstring(open('db').read())
ta = [x for x in sqlite3.connect('db').execute('SELECT * FROM table ORDER BY _id')]
results = [0,0,0,0]
tries = 1000
for i in xrange(0,tries):
work = db[:]
while work == db:
for j in xrange(0,random.randint(1,5)):
work[random.randint(1,len(db))-1] = random.randint(0,255)
work.tofile(open('outdb','w'))
try:
c = sqlite3.connect('outdb')
results[0] += 1
for r in c.execute('PRAGMA integrity_check;'):
results[1] += 1 if (r[0] == 'ok') else 0
except:
continue
try:
results[3] += 1 if [x for x in c.execute('SELECT * FROM table ORDER BY _id')] != ta else 0
results[2] += 1
except:
c.close()
continue
print 'Results for '+str(tries)+' tests:'
print 'Creating connection failed '+str(tries-results[0])+ ' times'
print 'Integrity check failed '+str(results[0]-results[1])+ ' times'
print 'Running a SELECT * query failed '+str(results[1]-results[2])+ ' times'
print 'Data was succesfully altered '+str(results[3])+ ' times'
结果表明，用这种方式编辑的表数据是完全有可能：
The results showed that "editing" table data in this way is entirely possible:
Results for 1000 tests:
Creating connection failed 0 times
Integrity check failed 503 times
Running a SELECT * query failed 289 times
Data was succesfully altered 193 times
这是一般有趣的是，运行一个查询失败了一半，也未被发现通过完整性校验的修改，但最有趣的事情对我来说是的东西可能掉在我的数据库中随机字节渲染的一部分，我的应用程序无用我的用户。
It's generally interesting to see that running a query failed for half of the modifications that went undetected by integrity check, but the most interesting thing for me is that something may swap random bytes in my DB rendering my application useless for a part of my users.
我读过有关腐败对SQLite的网站，并在计算器可能的原因，我知道，如强制关闭应用程序可能会做伤害到数据库。我只是想知道是否有可能实现快速和更强大的数据库完整性检查。
I've read about possible causes of corruption on SQLite website and also on StackOverflow, I know that e.g. forcing application to close may do a harm to the DB. I'd just like to know if it's possible to implement a fast and more robust DB integrity check.
我是从整个表的一列读取数据在启动时（对于自动完成），所以我想计算的所有值的一些散的 - 我认为这会工作相当不错，因为一些哈希函数的设计只是做完整性检查，但也许有一个更简单，更快，更好的解决方案 - 我这样问你，如果你知道任何
I'm reading the data from a one column of the whole table at startup (for autocompletion), so I thought of calculating some hash from the all values - I think this would work quite good, since some hash function are designed just for doing integrity checks, but maybe there's a simpler, faster and better solution - I'm thus asking you, if you know any.
推荐答案
我不知道像这样的任何SQLite的功能，所以我会说，计算哈希值是最简单的解决方案，来看看在<一个HREF =http://developer.android.com/reference/java/security/MessageDigest.html相对=nofollow> 消息摘要 类的开始。
I don't know of any SQLite feature like this, so I'd say that calculating a hash is the simplest solution, take a look at the MessageDigest class for a start.
这篇关于更好的SQLite的损坏检测的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
