将一个或多个数据库与主数据库同步 - 外键
我使用 Google Gears 是为了能够离线使用应用程序（我知道 Gears 已被弃用）。我面临的问题是与服务器上的数据库同步。
具体问题是主键或更准确地说是外键。当将信息发送到服务器时，我可以轻松忽略主键并生成新的主键。但那我怎么知道这些关系是什么。
我心里有一个解决方案，我打赌我需要为每个客户保存所有的 pk。将多个客户端与一个服务器数据库同步的最佳方法是什么？
编辑：
我一直在思考，我猜顺序主键不是最好的解决方案，但是还有什么其他可能性呢？由于可能发生碰撞，基于时间似乎不正确。
我想到了 GUID，这是一个选择吗？看起来在 javascript 中生成 GUID 并不那么容易。
我可以用自然键或复合键做一些事情。当我思考时，这看起来是最好的解决方案。我可以预料到会有什么问题吗？
I'm using Google Gears to be able to use an application offline (I know Gears is deprecated). The problem I am facing is the synchronization with the database on the server.
The specific problem is the primary keys or more exactly, the foreign keys. When sending the information to the server, I could easily ignore the primary keys, and generate new ones. But then how would I know what the relations are.
I had one sollution in mind, bet the I would need to save all the pk for every client. What is the best way to synchronize multiple client with one server db.
Edit:
I've been thinking about it, and I guess seqential primary keys are not the best solution, but what other possibilities are there? Time based doesn't seem right because of collisions which could happen.
A GUID comes to mind, is that an option? It looks like generating a GUID in javascript is not that easy.
I can do something with natural keys or composite keys. As I'm thinking about it, that looks like the best solution. Can I expect any problems with that?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这不是一个完整的答案，但至少可以为您提供一些想法...
您提出的问题（以及您试图解决的问题） > 并非特定于 Google Gears，并且对于其他解决方案仍然有效，例如 HTML 5 或基于系统或 Flash/Air。
几个月前的最后一次 ZendCon 上有一个关于该主题的演示 - 幻灯片可以在 Slideshare 上找到： 规划与浏览器本地数据库的同步
浏览这些幻灯片，您会看到关于可能想到的几种可能性的注释（有些确实出现在您的脑海中，或者在其他答案中）：
当然，对于其中每一个，都有优点......和缺点 - 我不会复制粘贴它们：看一下幻灯片；- )
现在，根据您的情况，哪种解决方案是最好的？实际上，很难说——而且你越早考虑同步，它可能就越好/越容易：当应用程序仍处于设计阶段时，向应用程序添加内容要简单得多 ^^
首先，确定是否：
那么，您要同步什么？
还有一个我们通常不会想到的具体问题 - 直到它发生：特别是如果您的同步过程可能需要一些时间（如果您有大量数据，如果您不经常同步，. ..)，如果同步在尚未完成时停止怎么办？
例如，如果：
在大多数情况下，半同步数据可能不太好......
因此，您也必须找到解决该问题的方法：在大多数情况下，同步必须是原子的！
This is not quite a full answer, but might at least provide you with some ideas...
The question you're asking (and the problem you're trying to address) is not specific to Google Gears, and will remains valid with other solutions, like HTML 5 or systems based or Flash/Air.
There's been a presentation about that subject given during the last ZendCon a few month ago -- and the slides are available on slideshare : Planning for Synchronization with Browser-Local Databases
Going through thoses slides, you'll see notes about a couple of possibilities that might come to mind (some did actually come to your mind, or in other answers) :
Of course, for each one of those, there are advantages... and drawbacks -- I will not copy-paste them : take a look at the slides ;-)
Now, in your situation, which solution will be the best ? Hard to say, actually -- and the sooner your think about synchronisation, the better/easier it'll probably be : adding stuff into an application is so much simpler when that application is still in its design stage ^^
First, it might be interesting to determine whether :
Then, what are you going to synchronise ?
There is also a specific problem to which we don't generally think -- until it happens : especially if your synchronisation process can take some time (if you have a lot of data, if you don't synchronise often, ...), what if the synchronisation is stopped when it's not finished yet ?
For instance, what if :
Having half-synchronised data might not be that good, in most situations...
So, you have to find a solution to that problem, too : in most cases, the synchronisation has to be atomic !
我想出了以下解决方案：
每个客户端从服务器获取一个唯一的 ID。在任何引用主键的地方，我都使用带有客户端 ID 和自动增量字段的复合键。
这样，组合是唯一的，并且易于实现。剩下的唯一一件事就是确保每个客户都有一个唯一的 ID。
我刚刚发现一个缺点：SQLite 不支持复合主键的自动增量，所以我必须自己处理 id。
I've came up with the following solution:
Every client gets a unique id from the server. Everywhere a primary key is referenced, I use a composite key with the client id and an auto increment field.
This way, the combination is unique, and it's easy to implement. The only thing left is making sure every client does get a unique id.
I just found out one drawback: SQLite doesn't support autoincrement on composite primary keys, so I would have to handle the id's myself.
我会使用与您最新答案类似的设置。但是，为了解决自动增量问题，我将在主数据库中使用单个自动增量代理键，然后存储客户端主键和客户端 ID。这样，您就不会在此过程中丢失或更改任何数据，并且还可以跟踪数据最初源自哪个客户端。
请务必在您的 Client Pk、Client Id 上设置唯一索引，以启用任何子表的引用完整性。
I would use a similar setup to your latest answer. However, to get around your auto-increment issue, I would use a single auto-increment surrogate key in your master database and then store the client primary key and your client id as well. That way you are not losing or changing any data in the process and you are also tracking which client the data was originally sourced from.
Be sure to also set up a unique index on your Client Pk, Client Id to enable referential integrity from any child tables.
客户端在断开连接时可以创建的对象数量是否有合理的限制？
我看到的一种可能性是创建一种“本地序列”。
当您的客户端连接到中央服务器时，它会获得一个数字 ID，例如 7 位数字（服务器将其生成为序列）。
实际的 PK 创建为如下字符串：895051|000094 或 895051|005694，其中第一部分是从服务器发送的 7 位数字，第二部分是由客户端管理的“本地”序列。
一旦与中央同步，您就可以获得新的 7 位数字并重新启动本地序列。总而言之，这与您的提议并没有太大不同。它只是使实际的 PK 完全独立于客户端身份。
另一个好处是，如果您遇到客户端从未连接到服务器的情况，它可以在本地使用 000000|000094，需要来自服务器的新号码并更新其一侧的密钥，然后再发送回服务器进行同步（如果你有很多 FK 约束，这会很棘手，并且不可行）。
Is there a reasonable limit to how many objects the client can create while disconnected?
One possibilty I can see is to create a sort of "local sequence".
When your client connects to the central server, it gets a numeric ID, say a 7 digit number (the server generates it as a sequence).
The actual PKs are created as strings like this: 895051|000094 or 895051|005694 where the first part is the 7 digit number sent from the server, and the second part is a "local" sequence managed by the client.
As soon as you synch with the central, you can get a new 7 digit number and restart your local sequence. This is not too different from what you were proposing, all in all. It just makes the actual PK completely independant from the client identity.
Another bonus is that if you have a scenario where the client has never connected to the server, it can use 000000|000094 locally, require a new number from the server and update the keys on its side before sending back to the server for synch (this is tricky if you have lots of FK constraints though, and could not be feasible).