合并数据库，然后将照片和文档分开，就像一个文件一样? [英] Merge a database and seperate photos and documents into something that acts like a single file?

问题描述

我们有一个数据库应用程序，用于维护遮荫树种群的清单数据(位置/GIS数据，站点特征，树属性等)，并通过集成的服务请求和工作单系统管理其维护. UI和数据提供程序层是vb.net 2010，我们当前正在使用MS Access 2007作为后端.

数据库通常是中小型的(即15个主要数据表，通常每个表有5,000-15,000行.较不常见的是，每个数据库中有几万个或更多的行.几乎总是使用单用户PC或小型工作组来连接)共享的数据库文件).

现在，我们想添加将照片和文档(主要是MS Office + .pdf)绑定到单个树记录的功能，并相信我们的某些用户将使用此功能，以最大限度地提高Access的2G大小限制，或至少将其推到效率低下.

我已经阅读了关于将照片和文档存储在数据库而不是文件系统中的永恒争论，并得出结论，就我们而言，由于种种原因，它们都不是最佳选择.主要是:

1.对于单文件数据存储，我们有非常强烈的偏好(如果不是必需的话)-用户经常将数据文件复制到数位板以供现场使用，并在一天结束时将其复制回桌面...我们的应用允许用户为他们管理的每个树种群创建和使用单独的数据库文件，就像MS Word将创建并使用多个文档一样...我们使用文件关联-双击我们的一个数据库文件将启动我们的应用程序并建立连接参数.多文件/多文件夹数据存储将破坏或混淆这些功能，并留下太多丢失或损坏数据的风险.

2.我熟悉的大多数单文件数据库都有一个强加的大小限制，对于我们的某些用户(访问，SQl Server CE)来说可能证明太小，不支持通过网络的连接(SQL Server CE) ，据称存在某些性能问题，可能会影响我们的处境等.诸如VistaDB之类的其他问题可能会起作用，但很难证明比我们在此特定项目上已经拥有的第三方成本更高的第三方成本.

因此，我开始怀疑是否有一种方法可以兼顾两者-将数据库文件(即我们现有的MS Access文件)和目录(用于存储照片和文档)嵌入到单个文件中，或者像单个文件.另一种选择是将多个数据库文件包装到一个文件中，并保持每个文件相对较小.我还没有做太多实验，但是潜在的解决方案可能包括:

1.结构化存储/复合文件(尚不清楚我是否可以在结构化存储中实际连接到数据库文件，或者是否必须先将其流式传输到新文件)

2.使用未压缩的Zip文件(再次，我不清楚是否可以直接访问位于zip文件中的数据库，或者每次我想打开连接时都需要解压缩该文件)

3.诸如TrueCrypt之类的东西，它在文件内创建虚拟磁盘并将其安装为真实磁盘"(包含文件的可访问性似乎很容易，但是TrueCrypt似乎是作为独立的应用程序而不是组件来提供的，我认为不喜欢我们的每个文件在用户计算机上显示为驱动器的想法……也许那里有类似的东西?).

因此，经过冗长的解释之后，我的问题是-是否有人在这条路上徘徊和/或有任何想法可提供?

非常感谢.

Paul

Hi,

We have a database application that maintains inventory data for shade tree populations (location / GIS data, site characteristics, tree attributes, etc.) and manages their maintenance via an integrated service request and work order system. The UI and data provider layer are vb.net 2010 and we''re currently using MS Access 2007 as the backend.

The databases are generally small to medium size (i.e. 15 main datatables that typically will have 5,000-15,000 rows each. Less commonly, there will be a few 100k or more in each. Use is almost always single-user PC or small workgroup connecting to a shared database file).

Now, we want to add the ability to tie photos and documents (mostly MS Office + .pdf) to individual tree records and believe that some of our users will use this feature to the point of maxing-out Access'' 2G size limit, or at least pushing it to inefficiency.

I''ve read-up on the timeless debate over storing photos and documents in the database versus the filesystem and concluded that, in our case, neither is optimal for several reasons. Primarily:

1. We have a very strong preference, if not a requirement, for a single-file data store - Users frequently copy a data file to pen tablets for field use and back to the desktop at the end of the day... Our application allows users to create and use a seperate database file for each tree population they manage, just like MS Word will create and use multiple documents... We use a file association - double-clicking one of our database files launches our app and establishes the connection parameters. A multi-file / multifolder data store would break or confuse these features and leaves too much risk of data being lost or corrupted.

2. Most of the single-file databases I am familiar with have an imposed size limit that might prove to be too low for some of our users (Access, SQl Server CE), do not support connections over a network (SQL Server CE), are alleged to have certain performance issues that may affect our situation, etc. Others like VistaDB might work, but it''s difficult to justify more third-party costs than we already have on this particular project.

So, I started wondering if there was a way get the best of both worlds - embed a database file (i.e. our existing MS Access file) and a directory (for storing photos and documents) into a single file, or something that acts like a single file. Another option would be to wrap multiple database files into a single file and keep each relatively small. I haven''t experimented too much yet, but potential solutions might include:

1. Structured Storage / Compound File (it''s not clear to me if you can actually connect to a database file while it''s in structured storage or if it has to be streamed out to a new file first)

2. Using an uncompressed Zip file (again, it''s not clear to me if a database residing in a zip file can be accessed directly, or needs to be unzipped each time i want to open a connection)

3. Something like TrueCrypt, which "creates a virtual disk within a file and mounts it as a real disk" (accesibility of the included files seems easy, but it appears that TrueCrypt comes as a stand-alone application versus a component and I don''t love the idea of each of our files appearing as a drive on the user''s machine... maybe there is something similar out there?).

So, after that long-winded explanation, my question is - has anyone else wandered down this path and/or have any ideas to offer?

Thanks so much.

Paul

推荐答案

是的，我濒临想出一条类似的道路.我打算使用SQL Server Embedded，这通常是非常有限的，但是允许我动态创建数据库.根据您的情况，我将以Access格式创建一个空文件数据库.每当我需要添加新数据库时，我都会复制该文件.然后，在我的元数据表中，我既要存储一个ID来查找图像，又要存储该图像所在的数据库的名称.因此，您可以使ID对每个表都是唯一的(使用GUID是这样做的明显方式是，但它会使数据库更大，并按插入顺序删除自动排序)，或者您可以使一对数据库名称和id唯一.

Yes, I am on the verge of wondering down a similar path. I intend to use SQL Server Embedded, which is quite limited in general, but allows me to create databases on the fly. In your situation, I''d create an empty file database, in Access format. Every time I needed to add a new database, I''d make a copy of that file. Then, in my metadata table, I''d store both an id to look up an image, and the name of the database that image is in. So, you could make id''s unique to each table ( using GUIDs is an obvious way of doing this, but it makes your DB bigger and removes an automatic sort in order of insertion ), or you could make just the pair of the DB name and the id unique.

阅读"如何为Windows开发虚拟磁盘"以了解如何创建虚拟磁盘.驱动器.

将主数据库文件(Access，SQL Server CE等)存储为较大文件的一部分.同样在该文件中，包括您的图像等.允许数据库分散在整个文件中.这样，当数据库对于较大文件的一部分来说太大时，您可以增加文件的大小，然后将数据库的一部分放在较大文件的末尾附近.当然，这将需要您具有某种标题(例如，一个主文件表，该表指示较大文件的哪些部分映射到单个文件).

现在，为包含所有其他内容(图片，数据库等)的单个文件加载标头信息.将其用作虚拟硬盘的基础.这样，您基本上可以从真实文件流式传输到虚拟硬盘.您可以将Access/SQL Server CE指向虚拟硬盘中的该数据库.程序启动时，将挂载虚拟驱动器，当程序关闭时，将卸载虚拟磁盘.

当然，您可以将其放在zip文件或其他文件中(如您提到的那样)，但是您将无法执行我解释过的流传输方案...当您的应用程序运行时，您必须解压缩数据库运行，然后在关闭时将其重新压缩，这可能会花费大量时间.

请注意，我还没有尝试过任何方法.这只是完成您要完成的一种方法的高级描述.祝您好运.

Read "How to develop a virtual disk for Windows" to understand how to create a virtual drive.

Store your primary database file (Access, SQL Server CE, whatever) as part of a larger file. Also in that file, include your images and such. Allow the database to be fragmented across the file. That way, when the database grows too large for its portion of the larger file, you can just increase the size of the file and put part of the database near the end of the larger file. This will of course require you to have some sort of header (like a master file table that indicates what parts of the larger file map to individual files).

Now, load up the header information for the single file that contains all the other stuff (pictures, database, etc). Use that as the basis of your virtual hard disk. That way, you can essentially stream from your real file to the virtual hard disk. And you can point Access/SQL Server CE to that database in the virtual hard disk. When your program launches, you mount the virtual drive and when it closes, you unmount the virtual disk.

Of course, you could put it in a zip file or whatever (like you mention), but then you wouldn''t be able to do the streaming scenario I explained... you''d have to unzip the database when your app runs and then rezip it when it closes, which could take considerable time.

Note that I''ve not tried any of this. That''s just a high level description of one way to accomplish what you are trying to accomplish. Good luck.

此外，您可以尝试 SQLLite ，我认为它的限制比2GB(访问)或4GB(SQL Server CE).请参见 SQLLite限制.

Also, you might try SQLLite, which I believe has a higher limit than 2GB (Access) or 4GB (SQL Server CE). See SQLLite Limits.

这篇关于合并数据库，然后将照片和文档分开，就像一个文件一样?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！