How to store array in one column in Sqlite3?

Is there any way to store an array of integers in one column of table? I want o/p like this:

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.

SQLite3 does not support arrays directly. See here the type it supports. Basically, it only does Ints, Floats and 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.

Sorry to Necro, just came across the problem myself and found a solution.

As stated already SQLite has no support for arrays, so you can't store them as such. Try this, I had the same problem;

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

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

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.

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.

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>

How to store array in one column in Sqlite3?

Related

Recent Posts