Is there any way to store an array of integers in one column of table? I want o/p like this:
有没有办法在一列表中存储整数数组?我想这样的o / p:
ident | value | count
----------------+------------------------------------------------------------------------------------------------------------------------+-------
563 | [0:10]={"(0,0)","(1,100)","(2,200)","(3,300)","(4,400)","(5,500)"} | 6
This I have already acheieved through postgres but i want same o/p from sqlite also. Here column value store an array. I tried it through BLOB but it is not working. Somebody told me about serialized way but i am not sure how to do that.
我已经通过postgres实现了这个,但我也希望从sqlite获得相同的o / p。这里列值存储一个数组。我通过BLOB尝试了它,但它无法正常工作。有人告诉我关于序列化方式,但我不知道该怎么做。
4 个解决方案
#1
19
SQLite3 does not support arrays directly. See here the type it supports. Basically, it only does Ints, Floats and Text.
SQLite3不直接支持数组。在这里看到它支持的类型。基本上,它只有Ints,Floats和Text。
To accomplish what you need, you have to use a custom encoding, or use an FK, i.e. create another table, where each item in the array is stored as a row.
要完成您的需要,您必须使用自定义编码,或使用FK,即创建另一个表,其中数组中的每个项目都存储为一行。
#2
10
Sorry to Necro, just came across the problem myself and found a solution.
对不起Necro,我自己遇到了问题并找到了解决方案。
As stated already SQLite has no support for arrays, so you can't store them as such. Try this, I had the same problem;
如前所述,SQLite不支持数组,因此您无法存储它们。试试这个,我有同样的问题;
Instead of storing array elements individually, you can store them as a large string and use a string function or Regular expression to parse them back into their types. An C# example
您可以将它们存储为大字符串,并使用字符串函数或正则表达式将它们解析回其类型,而不是单独存储数组元素。一个C#的例子
int[] myArray = new int[] {8,4,345,378,34456,7};
string Arraystring = myArray[0].ToString();
for(int i = 1; i < myArray.Length; i++) {
Arraystring += "," + myArray[i].ToString();
}
This will turn the array into a single string, now we take the string and insert it into the table as a string, when you read the string use this code to get the array back. Another C# example
这会将数组转换为单个字符串,现在我们将字符串作为字符串插入表中,当您读取字符串时,使用此代码将数组返回。另一个C#例子
string value; //assign this via Reader
string[] tokens = values.Split(',');
int[] myItems = Array.ConvertAll<string, int>(tokens, int.Parse);
this will only work with single dimensional arrays, multi-dimensional can get tricky when it comes to parsing the strings.
这只适用于单维数组,在解析字符串时多维可能会变得棘手。
#3
2
This is one way of serializing and deserializing data:
这是序列化和反序列化数据的一种方法:
#include <string>
#include <vector>
#include <sstream>
#include <iostream>
std::vector<std::string> deserialize_array(std::string const &csv)
{
std::istringstream parse(csv);
std::vector<std::string> ret;
for(std::string token; std::getline(parse, token, ','); ret.push_back(token));
return ret;
}
std::string serialize_array(std::string* array_ptr, std::size_t N)
{
std::ostringstream cat;
for(std::size_t index= 0; index< N; ++ index)
cat<< array_ptr[index]<< ',';
std::string ret= cat.str();
return ret.substr(0, ret.size()-1);
}
int main()
{
std::string data= "1,2,3";
std::cout<< "Data: "<< data<< std::endl;
std::vector<std::string> deserialized= deserialize_array(data);
std::string serialized= serialize_array(deserialized.data(), deserialized.size());
std::cout<< "Serialized + Deserialized: "<< serialized<< std::endl;
}
Instead of spending time parsing parentheses and extra commas, you can serialize as csv and read two by two when processing the deserialized data.
您可以在处理反序列化数据时将序列化为csv并逐两读取,而不是花时间解析括号和额外逗号。
#4
1
This is what I envision, though it may be incorrect:
这是我想象的,虽然它可能是不正确的:
<table>
<citation>
<citation ID>
<citation content>
<citation publication date>
CREATE TABLE citation
(
citation_ID INTEGER PRIMARY KEY AUTOINCREMENT,
citation VARCHAR(255)
published datetime
)
<table>
<source doc>
<source doc ID>
<source doc content>
CREATE TABLE source
(
source_ID INTEGER PRIMARY KEY AUTOINCREMENT,
source VARCHAR(5000)
)
<citation_to_source table> //table in question
<relationship>
<relationship ID>
<citation ID>
<source doc ID>
CREATE TABLE citation_to_source //table in question
(
relationship_id INTEGER,
citation_ID INTEGER,
source_ID INTEGER,
FOREIGN KEY(citation_ID) REFERENCES citation(citation_ID)
FOREIGN KEY(source_ID) REFERENCES source(source_ID)
)
Output format:
<content>
<relationship ID>
<unique source document content>
<enumerate citation IDs>
#1
19
SQLite3 does not support arrays directly. See here the type it supports. Basically, it only does Ints, Floats and Text.
SQLite3不直接支持数组。在这里看到它支持的类型。基本上,它只有Ints,Floats和Text。
To accomplish what you need, you have to use a custom encoding, or use an FK, i.e. create another table, where each item in the array is stored as a row.
要完成您的需要,您必须使用自定义编码,或使用FK,即创建另一个表,其中数组中的每个项目都存储为一行。
#2
10
Sorry to Necro, just came across the problem myself and found a solution.
对不起Necro,我自己遇到了问题并找到了解决方案。
As stated already SQLite has no support for arrays, so you can't store them as such. Try this, I had the same problem;
如前所述,SQLite不支持数组,因此您无法存储它们。试试这个,我有同样的问题;
Instead of storing array elements individually, you can store them as a large string and use a string function or Regular expression to parse them back into their types. An C# example
您可以将它们存储为大字符串,并使用字符串函数或正则表达式将它们解析回其类型,而不是单独存储数组元素。一个C#的例子
int[] myArray = new int[] {8,4,345,378,34456,7};
string Arraystring = myArray[0].ToString();
for(int i = 1; i < myArray.Length; i++) {
Arraystring += "," + myArray[i].ToString();
}
This will turn the array into a single string, now we take the string and insert it into the table as a string, when you read the string use this code to get the array back. Another C# example
这会将数组转换为单个字符串,现在我们将字符串作为字符串插入表中,当您读取字符串时,使用此代码将数组返回。另一个C#例子
string value; //assign this via Reader
string[] tokens = values.Split(',');
int[] myItems = Array.ConvertAll<string, int>(tokens, int.Parse);
this will only work with single dimensional arrays, multi-dimensional can get tricky when it comes to parsing the strings.
这只适用于单维数组,在解析字符串时多维可能会变得棘手。
#3
2
This is one way of serializing and deserializing data:
这是序列化和反序列化数据的一种方法:
#include <string>
#include <vector>
#include <sstream>
#include <iostream>
std::vector<std::string> deserialize_array(std::string const &csv)
{
std::istringstream parse(csv);
std::vector<std::string> ret;
for(std::string token; std::getline(parse, token, ','); ret.push_back(token));
return ret;
}
std::string serialize_array(std::string* array_ptr, std::size_t N)
{
std::ostringstream cat;
for(std::size_t index= 0; index< N; ++ index)
cat<< array_ptr[index]<< ',';
std::string ret= cat.str();
return ret.substr(0, ret.size()-1);
}
int main()
{
std::string data= "1,2,3";
std::cout<< "Data: "<< data<< std::endl;
std::vector<std::string> deserialized= deserialize_array(data);
std::string serialized= serialize_array(deserialized.data(), deserialized.size());
std::cout<< "Serialized + Deserialized: "<< serialized<< std::endl;
}
Instead of spending time parsing parentheses and extra commas, you can serialize as csv and read two by two when processing the deserialized data.
您可以在处理反序列化数据时将序列化为csv并逐两读取,而不是花时间解析括号和额外逗号。
#4
1
This is what I envision, though it may be incorrect:
这是我想象的,虽然它可能是不正确的:
<table>
<citation>
<citation ID>
<citation content>
<citation publication date>
CREATE TABLE citation
(
citation_ID INTEGER PRIMARY KEY AUTOINCREMENT,
citation VARCHAR(255)
published datetime
)
<table>
<source doc>
<source doc ID>
<source doc content>
CREATE TABLE source
(
source_ID INTEGER PRIMARY KEY AUTOINCREMENT,
source VARCHAR(5000)
)
<citation_to_source table> //table in question
<relationship>
<relationship ID>
<citation ID>
<source doc ID>
CREATE TABLE citation_to_source //table in question
(
relationship_id INTEGER,
citation_ID INTEGER,
source_ID INTEGER,
FOREIGN KEY(citation_ID) REFERENCES citation(citation_ID)
FOREIGN KEY(source_ID) REFERENCES source(source_ID)
)
Output format:
<content>
<relationship ID>
<unique source document content>
<enumerate citation IDs>