SQLite问题“无法打开数据库文件" [英] SQLite problem "unable to open the database file"
问题描述
我花了很多时间在谷歌上搜索并阅读了有关StackOverflow上有关SQLite的其他讨论，但是我绝对找不到关于我的问题的任何解释，所以这里是:
I come to you after a lot of hours googling and reading other discussions about SQLite on StackOverflow, but I definitely can't find any explanation to my problem, so here it is :
-
上下文:
我正在为iPad开发一个应用程序，它在某些情况下必须处理一些大量"数据.在其中之一中，我必须将.kml文件(Google的用于地理数据的xml)中的点坐标导入到我的数据库中，以便以后在MKMapView中重用它们并比在需要显示特定内容时解析xml更快地加载它们.层.
The context :
I'm developping an application for iPad wich has to deal with some "large" amounts of data, in several occasions. In one of them, I must import points coordinates from a .kml file (Google's xml for geographical data) into my database, in order to reuse them later with a MKMapView and load them faster than by parsing xml when it needs to show a specific layer.
详细信息:
导入很容易:在处理这些文件时，我只关心2个表:
The details :
The import thing is quite easy : when dealing with those files, I'm only concerned with 2 tables :
- 其中一个包含区域定义和详细信息:目前，一个
integer作为ID，一个text用于命名. - 其中一个包含两个
real用于坐标存储，一个integer引用第一个表以了解哪个区域点是其中的一部分.
因此，只要读取我的文件，我首先会为新区域创建一个条目，然后将点插入第二个表中，并在第一个表中创建最后一个区域的ID ...没有什么复杂的！
- One containing zones definitions and details : for the moment, an
integeras an id, and atextfor naming. - One containing two
realfor coordinate storage and anintegerreferencing the first table for knowing which zone point is part of.
So as long as reading my file, I first create an entry for the new zone, and then I insert points into the second table, with ID of the last zone created in the first table...nothing complicated!
但是...
问题:
经过一会儿的运行之后，我从SQLite收到了一条异常消息，并发出了著名的消息无法打开数据库文件"，然后我无法对数据库执行任何其他操作.在区域创建或点插入方法中可能会随机发生这种异常.
The problem :
After running fine a while, I get an exception from SQLite with the famous message "Unable to open the database file", and then it comes I can't do anything more with the database. This exception can randomly occur in the zone creation or the points insertion methods.
我的反思:
考虑到这些文件中的许多点，我怀疑内存或磁盘已饱和，但我的应用程序的其他部分却丢弃了这些点(在我看来).
首先，内存:发生异常时，该应用程序正在使用大约10或12 MB的RAM.它看起来可能非常大，但这是由于在内存中加载了〜10MB .kml文件，因此可以解释.最重要的是，我的应用程序中的MKMapView可以处理地图上方成吨的高分辨率瓷砖图层，因此会导致内存高峰，该高峰可以提供20甚至25MB的内存，而不会导致iPad崩溃.
其次，磁盘:重置数据库并仅填充上述2个表时，发生异常时的db文件大小始终约为2.2或2.5MB，但是当我填充其他表时(我的应用程序的其他部分运行良好！) db文件大约为6或7MB，设备完全没有抱怨.
My reflexions :
Considering the numerous points in those files, I suspected memory or disk saturation but other parts of my app discarded those points (to my mind).
First, memory : it comes that when the exception occurs, the app is using about 10 or 12 MB of RAM. It can seems quite huge, but it's due to the ~10MB .kml file loaded in memory, so it's explainable. And above it all, the MKMapView thing of my app deals with tons of high-res tiles layers above map, and so leads to memory peaks which can afford 20 or even 25MB without making the iPad to crash.
Second, disk : when reseting my database and filling only the 2 tables described above, the db file size when the exception occurs is always about 2.2 or 2.5MB, but when I fill other tables (the other parts of my apps works well!) the db file is about 6 or 7MB, and the device doesn't complain at all.
那又如何?！
CPU烦恼和恐慌?我不这么认为，因为数据库中的其他一些表都以相同的节奏填充而没有问题...并且在模拟器中运行我的应用程序也崩溃了，而核心i7只是在嘲笑这项工作.
SQLite不好用吗?我们去了！在我看来，这是唯一的解决方案！但是我真的不明白这里发生了什么，因为我以与其他应用程序部分相同的方式处理请求，重复我自己，就像处理超级魅力一样！
So what?!
CPU-angryness and panic? I don't think so because some of the other tables of my database are filled at the same rythm without problem... and running my app in simulator crashes too, with a core i7 just laughing at the job.
SQLite bad use? There we go! To my mind, it's the only solution left! But I really can't understand what's going on here because I process my requests the same way I do in other app's parts which - repeating myself - work like a charm!
SQLite详细信息:
我有一个DB类，它是一个单例，用于避免我执行的每个请求创建/释放SqliteConnection对象，并且我处理数据库的所有方法都包含在该类中，以确保我不使用在不知道的情况下连接到其他任何地方.这里是有关此类的方法:
SQLite details :
I have a DB class which is a singleton I use to avoid creating/releasing an SqliteConnection object each request I do, and all my methods dealing with database are contained in this class to be sure I don't play with the connection anywhere else without knowing it. Here are concerned methods of this class :
public void saveZone(ObjZone zone) { //at this point, just creates an entry with a name and let sqlite give it a new id
lock (connection) { //SqliteConnection object
try {
openConnection();
SqliteCommand cmd = connection.CreateCommand();
cmd.CommandText = zone.id == 0 ?
"insert into ZONES (Z_NAME) values (" + format(zone.name) + ") ;" :
"update ZONES set Z_NAME = " + format(zone.name) + " where Z_ID = " + format(zone.id) + " ;";
cmd.ExecuteNonQuery();
if (zone.id == 0) {
cmd.CommandText = "select Z_ID from ZONES where ROWID = last_insert_rowid() ;";
zone.id = uint.Parse(cmd.ExecuteScalar().ToString());
}
cmd.Dispose();
}
catch (Exception e) {
Log.failure("DB.saveZone(" + zone.ToString() + ") : [" + e.GetType().ToString() + "] - " +
e.Message + "\n" + e.StackTrace); //custom Console.WriteLine() method with some formating
throw e;
}
finally {
connection.Close();
}
}
}
public void setPointsForZone(List<CLLocationCoordinate2D> points, uint zone_id) { //registers points for a given zone
lock (connection) {
try {
openConnection();
SqliteCommand cmd = connection.CreateCommand();
cmd.CommandText = "delete from ZONESPOINTS where Z_ID = " + format(zone_id);
cmd.ExecuteNonQuery();
foreach(CLLocationCoordinate2D point in points) {
cmd.CommandText = "insert into ZONESPOINTS values " +
"(" + format(zi_id) + ", " + format(point.Latitude.ToString().Replace(",", ".")) + ", "
+ format(point.Longitude.ToString().Replace(",", ".")) + ");";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
catch (Exception e) {
Log.failure("DB.setPointsForZone(" + zone_id + ") : [" + e.GetType().ToString() + "] - " + e.Message);
throw e;
}
finally {
connection.Close();
}
}
}
并且要尽可能清楚，这是上面两个中引用的一些方法(我使用此自定义openConnection()方法，因为我在大多数表中使用了外键约束，而级联行为未启用默认，但我需要它们.):
And to be as clear as I can, here are some of the methods referenced in the two above (I use this custom openConnection() method because I use foreign keys constraints in most of my tables and cascading behaviours are not enabled by default, but I need them.) :
void openConnection() {
try {
connection.Open();
SqliteCommand cmd = connection.CreateCommand();
cmd.CommandText = "PRAGMA foreign_keys = ON";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch (Exception e) {
Log.failure("DB.openConnection() : [" + e.GetType().ToString() + "] - " + e.Message);
throw e;
}
}
public static string format(object o) {
return "'" + o.ToString().Replace("'", "''") + "'";
}
好吧，对这本小说感到抱歉，我可能已经感谢您阅读所有这些内容，对吗?无论如何，如果我错过了可能有用的事情，请告诉我，我将尽快记录下来.
无论如何，希望有人能为我提供帮助！
(我为我可怜的法国人的英语道歉.)
Well, sorry for the novel, I may already thank you for reading all that stuff, no?! Anyway, if I missed something that could be useful, let me know and I'll document it as soon as possible.
I hope someone will be able to help me, anyway, thank you by advance!
(And my apologies for my poor frenchie's english.)
编辑
我的问题是已解决"！经过几次调试目的变更，没有大的修改也没有成功之后，我将代码放回了发布的状态...现在可以正常工作了.但是，如果有人可以给我解释可能发生的事情，我将不胜感激！似乎SQLite的行为(至少在iPad上使用-从未在其他任何地方使用过)在某些时候可能非常晦涩...:/
EDIT
My problem is "solved"! After a few changes for debugging pourposes, no big modifications, and no success, I put back the code in the state I posted it... and now it works. But I really would appreciate if some someone could give me an explanation of what may have happened! It seems like SQLite behaviour (on iPad at least - never used it anywhere else) can be quite obscure at some times... :/
推荐答案
为此，我不会交叉指责，但我会尝试两件事:
I wouldn't cross my fingers for this but I would try two things:
- 如果可能，请将KML文件预处理到另一个SQLite数据库，然后使用该数据库将数据导入主数据库中(考虑到较低的内存/处理器要求)
- 小批量处理导入的数据.
HTH
您可能已经检查过，但是无论如何:无法打开数据库.
you might have checked this already, but anyway: unable to open database.
这篇关于SQLite问题“无法打开数据库文件"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
