将数组存储在另一个表中引用的SQLite中 [英] Store array in SQLite that is referenced in another table

问题描述

我试图在SQLite数据库中存储数据的数组（或向量），但是我试图找到一个体面的方法来解决这个问题。我在StackOverflow上发现了一些其他的帖子，我似乎再也找不到了，其中提到的将数据存储在如下表格中：

 （
 id整数NOT NULL，
位置整数NOT NULL，$ b $整数NOT NULL，$ b $整数NOT NULL，
 PRIMARY KEY（id，position）
）;因此，要存储单个数组的所有数据，您可以将每个项目插入相同的ID下，只需要增加位置。因此，例如插入一个三值的数组，它会是这样的：
 
 $ p $ INSERT INTO array_of_points VALUES（0，0，1 ，1）; 
 INSERT INTO array_of_points VALUES（0，1，2，2）; 
 INSERT INTO array_of_points VALUES（0，2，3，3）; 
  

然后检索值，您将选择具有相同ID的所有东西，并按位置排序： / p>

  SELECT x，y FROM array_of_points WHERE id = 0 ORDER BY position; 
  

这个功能非常好，功能非常好，但是现在我遇到了一个问题，不知道如何在不同的表中引用一个数组。例如，我想做类似以下的事情：

$ p $ $ $ $ $ $ $ $ NULL，
array_id integer NOT NULL，
PRIMARY KEY（id），
FOREIGN KEY（array_id）REFERENCES array_of_points（id）
）;

这会创建表格，但是一旦您尝试对其执行查询，外键约束抛出一个错误，因为它必须引用 array_of_points id 和位置 c $ c>表，因为它们是组合主键的一部分。

我现在唯一的解决方法是从 foo 表，但这不是一个好的解决方案，因为它意味着它现在可以保存任何值，即使它实际上并不映射到 array_of_points 表。

有什么办法解决这个问题？或者也许有其他的方式来存储数据，这样才有可能？

正如仅供参考，请不要建议我存储在某种逗号/分号/任何分隔列表，因为这是一个更糟糕的选择，我不会考虑。也不可能处理一些将要存储在数据库中的更复杂的对象。

是这个模式无法处理的一个特殊情况：不可能存储大小为零的数组。
在实践中这可能不是一个问题，但它表明数据库没有完全标准化。

外键总是引用一个单个< /父母记录。
因此，缺少的是每个数组都有单个记录的表。
实现这个会产生一个像这样的模式：
$ b $ $ $ $ $ $ $ codeREATE TABLE array

ID整数主键
- 没有其他属性
）;
CREATE TABLE array_points
（
array_id integer REFERENCES array（id），
position integer，
x，y，[...]，
PRIMARY KEY （array_id，position）
）WITHOUT ROWID; - 见http://www.sqlite.org/withoutrowid.html
CREATE TABLE foo
（
[...]，
array_id integer REFERENCES array（id）
）;

额外的表格需要更多的努力来管理，但现在您可以通过自动增量生成数组ID 。

I'm trying to store arrays (or vectors) of data in a SQLite database but I'm having problems trying to find a decent way to do so. I found some other post on StackOverflow, that I can't seem to find anymore, which mentioned storing the data in a table like the following:

CREATE TABLE array_of_points
(
    id integer NOT NULL,
    position integer NOT NULL,
    x integer NOT NULL,
    y integer NOT NULL,
    PRIMARY KEY (id, position)
);

So to store all the data for a single array you would insert each item under the same ID and just increment the position. So for example to insert an array with three values it would be something like:

INSERT INTO array_of_points VALUES (0, 0, 1, 1);
INSERT INTO array_of_points VALUES (0, 1, 2, 2);
INSERT INTO array_of_points VALUES (0, 2, 3, 3);

And then to retrieve the values you would select everything with the same ID and order by the position:

SELECT x,y FROM array_of_points WHERE id = 0 ORDER BY position;

This is all great and works wonderfully, but I'm now running into a problem where I don't know how to reference an array in a different table. For example I want to do something like the following:

CREATE TABLE foo
(
    id integer NOT NULL,
    array_id integer NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (array_id) REFERENCES array_of_points (id)
);

This will create the table just fine but once you try to execute a query on it the foreign key constraint throws an error since it must reference both the id and position of the array_of_points table since they are part of a composite primary key.

The only solution I currently have is to just remove the foreign key from the foo table, but that is not a good solution since it means it can now hold any value even if it doesn't actually map to an array in the array_of_points table.

Is there any way to work around this problem? Or maybe there's some other way to store the data so that this is possible?

Just as an FYI, please do not suggest I store the data in some sort of comma/semi-colon/whatever delimited list because that is an even worse option that I am not going to consider. It is also not possible to do with some of the more complex objects that are going to be stored in the database.

解决方案

There is one special case that this schema cannot handle: it is not possible to store an array of size zero. This might not be a concern in practice, but it shows that the database is not fully normalized.

A foreign key always references a single parent record. Therefore, what is missing is a table that has a single record for each array. Implementing this would result in a schema like this:

CREATE TABLE array
(
    id integer PRIMARY KEY
    -- no other properties
);
CREATE TABLE array_points
(
    array_id integer REFERENCES array(id),
    position integer,
    x, y, [...],
    PRIMARY KEY (array_id, position)
) WITHOUT ROWID; -- see http://www.sqlite.org/withoutrowid.html
CREATE TABLE foo
(
    [...],
    array_id integer REFERENCES array(id)
);

The additional table requires more effort to manage, but now you have the ability to generate array IDs through autoincrementing.

这篇关于将数组存储在另一个表中引用的SQLite中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！