Lets say that I have an app that lets you browse through a listing of cars found in a Sqlite database. When you click on a car in the listing, it'll open up a view with the description of the car and a photo of the car.
可以说我有一个应用程序,可以让你浏览在Sqlite数据库中找到的汽车列表。当您点击列表中的汽车时,它会打开一个包含汽车描述和汽车照片的视图。
My question is: should I keep the photo in the database as a binary data column in the row for this specific car, or should I have the photo somewhere in the resources directory? Which is better to do? Are there any limitations of Sqlite in terms of how big a binary data column can be?
我的问题是:我应该将照片作为特定汽车行中的二进制数据列保存在数据库中,还是应该将照片放在资源目录中的某个位置?哪个更好?就二进制数据列的大小而言,Sqlite是否有任何限制?
The database will be pretty much be read only and bundled with the app (so the user wouldn't be inserting any cars and their photos).
该数据库几乎只能被读取并与应用程序捆绑在一起(因此用户不会插入任何汽车及其照片)。
2 个解决方案
#1
4
This is a decision which is discussed quite a lot. And in my opinion, it's a matter of personal taste. Pretty much like vim/emacs, windows/linux kind of debates. Not that heated though.
这是一个经过深思熟虑的决定。在我看来,这是个人品味的问题。非常像vim / emacs,windows / linux之类的辩论。虽然没那么激烈。
Both sides have their advantages and disadvantages. When you store them in a database, you don't need to worry much about filename and location. Management is also easier (you delete the row(s) containing the BLOBs, and that's it). But the files are also harder to access and you may need to write wrapper code in one way or the other (f.ex. some of those "download.php" links).
双方各有利弊。将它们存储在数据库中时,您不必担心文件名和位置。管理也更容易(您删除包含BLOB的行,就是这样)。但是文件也很难访问,你可能需要以某种方式编写包装代码(例如,某些“download.php”链接)。
On the other hand, if the binary data is stored as a separate file, management is more complicated (You need to open the proper file from the disk by constructing the filename first). On large data sets you may run into filesystem bottlenecks when the number of files in one directory grows very large (but this can be prevented by creating subdirs easily). But then, if the data is stored as files, replacing them becomes so much easier. Other people can also access it without the need to know the internals (imagine for example a user who takes fun in customizing his/her UI).
另一方面,如果二进制数据存储为单独的文件,则管理更复杂(您需要首先通过构造文件名从磁盘打开正确的文件)。在大型数据集上,当一个目录中的文件数量变得非常大时,您可能会遇到文件系统瓶颈(但这可以通过轻松创建子目录来防止)。但是,如果数据存储为文件,则替换它们会变得更加容易。其他人也可以在不需要知道内部的情况下访问它(例如,想象一下在定制他/她的UI时玩乐的用户)。
I'm certain that there are other points to be raised, but I don't want to write too much now...
我确定还有其他要点,但我现在不想写太多......
I would say: Think about what operations you would like to do with the photos (and the limitations of both storage methods), and from there take an informed decision. There's not much that can go wrong.
我想说:想想你想对照片做什么操作(以及两种存储方法的局限性),并从那里做出明智的决定。没有太多可能出错的地方。
TX-Log and FS-Journal
On further investigation I found some more info:
经过进一步调查,我发现了一些更多信息
- SQLite uses a Transaction Log
- Android uses YAFFS on the system mount points and VFAT on the SD-Card. Both are (to the best of my knowlege) unjournaled.
SQLite使用事务日志
Android在系统安装点上使用YAFFS,在SD卡上使用VFAT。两者都是(据我所知),没有记录。
I don't know the exact implementation of SQLite's TX-Log, but it is to be expected that each INSERT/UPDATE operation will perform two writes on the disk. I may be mistaken (it largely depends on the implementation of transactions), but frankly, I couldn't be bothered to skim through the SQLite source code. It feels to me like we start splitting hairs here (premature optimization anyone?)...
我不知道SQLite的TX-Log的确切实现,但是可以预期每个INSERT / UPDATE操作将在磁盘上执行两次写操作。我可能弄错了(这在很大程度上取决于交易的实施),但坦率地说,我不能轻易浏览SQLite源代码。我感觉就像我们在这里开始分裂(过早优化任何人?)......
As both file systems (YAFFS and VFAT) are not journalled, you have no additional "hidden" write operations.
由于两个文件系统(YAFFS和VFAT)都没有记录,因此您没有其他“隐藏”写入操作。
These two points speak in favour of the file system.
这两点支持文件系统。
Note that this info is to be taken with a grain of salt. I only skimmed over the Google results of YAFFS journaling and sqlite transaction log. I may have missed some details.
请注意,此信息应与一粒盐。我只浏览了YAFFS日记和sqlite事务日志的Google结果。我可能错过了一些细节。
#2
0
The default MAX SQLite size for a BLOB or String is 231-1 bytes. That value also applies to the maximum bytes to be stored in a ROW.
BLOB或String的默认MAX SQLite大小为231-1个字节。该值也适用于要存储在ROW中的最大字节数。
As to which method is better, I do not know. What I would do in your case is test both methods and monitor the memory usage and its effect on battery life. Maybe you'll find the filesystem method has a clear advantage over the other in that area and the ease of storing it in SQLite is not worth it for your use case, or maybe you'll find the opposite.
至于哪种方法更好,我不知道。在您的情况下,我会做的是测试两种方法并监控内存使用情况及其对电池寿命的影响。也许你会发现文件系统方法在该领域中具有明显的优势,并且在SQLite中存储它的便利性对于你的用例来说是不值得的,或者你可能会发现相反的情况。
#1
4
This is a decision which is discussed quite a lot. And in my opinion, it's a matter of personal taste. Pretty much like vim/emacs, windows/linux kind of debates. Not that heated though.
这是一个经过深思熟虑的决定。在我看来,这是个人品味的问题。非常像vim / emacs,windows / linux之类的辩论。虽然没那么激烈。
Both sides have their advantages and disadvantages. When you store them in a database, you don't need to worry much about filename and location. Management is also easier (you delete the row(s) containing the BLOBs, and that's it). But the files are also harder to access and you may need to write wrapper code in one way or the other (f.ex. some of those "download.php" links).
双方各有利弊。将它们存储在数据库中时,您不必担心文件名和位置。管理也更容易(您删除包含BLOB的行,就是这样)。但是文件也很难访问,你可能需要以某种方式编写包装代码(例如,某些“download.php”链接)。
On the other hand, if the binary data is stored as a separate file, management is more complicated (You need to open the proper file from the disk by constructing the filename first). On large data sets you may run into filesystem bottlenecks when the number of files in one directory grows very large (but this can be prevented by creating subdirs easily). But then, if the data is stored as files, replacing them becomes so much easier. Other people can also access it without the need to know the internals (imagine for example a user who takes fun in customizing his/her UI).
另一方面,如果二进制数据存储为单独的文件,则管理更复杂(您需要首先通过构造文件名从磁盘打开正确的文件)。在大型数据集上,当一个目录中的文件数量变得非常大时,您可能会遇到文件系统瓶颈(但这可以通过轻松创建子目录来防止)。但是,如果数据存储为文件,则替换它们会变得更加容易。其他人也可以在不需要知道内部的情况下访问它(例如,想象一下在定制他/她的UI时玩乐的用户)。
I'm certain that there are other points to be raised, but I don't want to write too much now...
我确定还有其他要点,但我现在不想写太多......
I would say: Think about what operations you would like to do with the photos (and the limitations of both storage methods), and from there take an informed decision. There's not much that can go wrong.
我想说:想想你想对照片做什么操作(以及两种存储方法的局限性),并从那里做出明智的决定。没有太多可能出错的地方。
TX-Log and FS-Journal
On further investigation I found some more info:
经过进一步调查,我发现了一些更多信息
- SQLite uses a Transaction Log
- Android uses YAFFS on the system mount points and VFAT on the SD-Card. Both are (to the best of my knowlege) unjournaled.
SQLite使用事务日志
Android在系统安装点上使用YAFFS,在SD卡上使用VFAT。两者都是(据我所知),没有记录。
I don't know the exact implementation of SQLite's TX-Log, but it is to be expected that each INSERT/UPDATE operation will perform two writes on the disk. I may be mistaken (it largely depends on the implementation of transactions), but frankly, I couldn't be bothered to skim through the SQLite source code. It feels to me like we start splitting hairs here (premature optimization anyone?)...
我不知道SQLite的TX-Log的确切实现,但是可以预期每个INSERT / UPDATE操作将在磁盘上执行两次写操作。我可能弄错了(这在很大程度上取决于交易的实施),但坦率地说,我不能轻易浏览SQLite源代码。我感觉就像我们在这里开始分裂(过早优化任何人?)......
As both file systems (YAFFS and VFAT) are not journalled, you have no additional "hidden" write operations.
由于两个文件系统(YAFFS和VFAT)都没有记录,因此您没有其他“隐藏”写入操作。
These two points speak in favour of the file system.
这两点支持文件系统。
Note that this info is to be taken with a grain of salt. I only skimmed over the Google results of YAFFS journaling and sqlite transaction log. I may have missed some details.
请注意,此信息应与一粒盐。我只浏览了YAFFS日记和sqlite事务日志的Google结果。我可能错过了一些细节。
#2
0
The default MAX SQLite size for a BLOB or String is 231-1 bytes. That value also applies to the maximum bytes to be stored in a ROW.
BLOB或String的默认MAX SQLite大小为231-1个字节。该值也适用于要存储在ROW中的最大字节数。
As to which method is better, I do not know. What I would do in your case is test both methods and monitor the memory usage and its effect on battery life. Maybe you'll find the filesystem method has a clear advantage over the other in that area and the ease of storing it in SQLite is not worth it for your use case, or maybe you'll find the opposite.
至于哪种方法更好,我不知道。在您的情况下,我会做的是测试两种方法并监控内存使用情况及其对电池寿命的影响。也许你会发现文件系统方法在该领域中具有明显的优势,并且在SQLite中存储它的便利性对于你的用例来说是不值得的,或者你可能会发现相反的情况。