Insert an array of tables into one table SQLite C/C++

Question

I made my own database format, and it sadly required too much memory and the size of it got horrendous and upkeep was horrible.

So I'm looking for a way to store an array of a struct that's in an object into a table.

I'm guessing I need to use a blob, but all other options are welcome. An easy way to implement a blob would be helpful as well.

I've attached my saving code and related structures(Updated from my horrible post earlier)

#include "stdafx.h"
#include <string>
#include <stdio.h>
#include <vector>
#include "sqlite3.h"
using namespace std;
struct PriceEntry{

    float cardPrice;
    string PriceDate;
    int Edition;
    int Rarity;
};
struct cardEntry{
    string cardName;
    long pesize;
    long gsize;
    vector<PriceEntry> cardPrices;
    float vThreshold;
    int fav;
};


vector<cardEntry> Cards;

void FillCards(){
  int i=0;
  int j=0;
  char z[32]={0};
  for(j=0;j<3;j++){
     cardEntry tmpStruct;
     sprintf(z, "Card Name: %d" , i);
     tmpStruct.cardName=z;
     tmpStruct.vThreshold=1.00;
     tmpStruct.gsize=0;
     tmpStruct.fav=1;
            for(i=0;i<3;i++){
                PriceEntry ss;
                ss.cardPrice=i+1;
                ss.Edition=i;
                ss.Rarity=i-1;

                sprintf(z,"This is struct %d", i);

                ss.PriceDate=z;
                tmpStruct.cardPrices.push_back(ss);
            }

            tmpStruct.pesize=tmpStruct.cardPrices.size();
            Cards.push_back(tmpStruct);
  }
}
int SaveCards(){
      // Create an int variable for storing the return code for each call
    int retval;
    int CardCounter=0;
    int PriceEntries=0;
    char tmpQuery[256]={0};

    int q_cnt = 5,q_size = 256;


    sqlite3_stmt *stmt;


    sqlite3 *handle;


    retval = sqlite3_open("sampledb.sqlite3",&handle);

    if(retval)
    {
        printf("Database connection failed\n");
        return -1;
    }
    printf("Connection successful\n");

    //char create_table[100] = "CREATE TABLE IF NOT EXISTS users (uname TEXT PRIMARY KEY,pass TEXT NOT NULL,activated INTEGER)";
    char create_table[] = "CREATE TABLE IF NOT EXISTS Cards (CardName TEXT, PriceNum NUMERIC, Threshold NUMERIC, Fav NUMERIC);";


    retval = sqlite3_exec(handle,create_table,0,0,0);
    printf( "could not prepare statemnt: %s\n", sqlite3_errmsg(handle) );
    for(CardCounter=0;CardCounter<Cards.size();CardCounter++){
        char Query[512]={0};

        for(PriceEntries=0;PriceEntries<Cards[CardCounter].cardPrices.size();PriceEntries++){

              //Here is where I need to find out the process of storing the vector of PriceEntry for Cards then I can modify this loop to process the data

       }

            sprintf(Query,"INSERT INTO Cards VALUES('%s',  %d, %f, %d)",            
            Cards[CardCounter].cardName.c_str(),
            Cards[CardCounter].pesize,
            Cards[CardCounter].vThreshold,
            Cards[CardCounter].fav); //My insert command  

        retval = sqlite3_exec(handle,Query,0,0,0);
        if(retval){

            printf( "Could not prepare statement: %s\n", sqlite3_errmsg(handle) );

        }
    }
    // Insert first row and second row

    sqlite3_close(handle);
    return 0;
}

I tried googling but my results didn't suffice.

Answer 1

This is a simple serialization and deserialization system. The class PriceEntry has been extended with serialization support (very simply). Now all you have to do is serialize a PriceEntry (or a set of them) to binary data and store it in a blob column. Later on, you get the blob data and from that deserialize a new PriceEntry with the same values. An example of how it is used is given at the bottom. Enjoy.

#include <iostream>
#include <vector>
#include <string>
#include <cstring> // for memcpy

using std::vector;
using std::string;

// deserialization archive
struct iarchive
{
    explicit iarchive(vector<unsigned char> data)
    : _data(data)
    , _cursor(0)
    {}

    void read(float& v)          { read_var(v); }
    void read(int& v)            { read_var(v); }
    void read(size_t& v)         { read_var(v); }
    void read(string& v)         { read_string(v); }

    vector<unsigned char> data() { return _data; }

private:

    template <typename T>
    void read_var(T& v)
    {
      // todo: check that the cursor will not be past-the-end after the operation

      // read the binary data
      std::memcpy(reinterpret_cast<void*>(&v), reinterpret_cast<const void*>(&_data[_cursor]), sizeof(T));

      // advance the cursor
      _cursor += sizeof(T);
    }

    inline

    void
    read_string(string& v)
    {  
      // get the array size
      size_t sz;
      read_var(sz);

      // get alignment padding
      size_t padding = sz % 4;
      if (padding == 1) padding = 3;
      else if (padding == 3) padding = 1;

      // todo: check that the cursor will not be past-the-end after the operation

      // resize the string
      v.resize(sz);

      // read the binary data
      std::memcpy(reinterpret_cast<void*>(&v[0]), reinterpret_cast<const void*>(&_data[_cursor]), sz);

      // advance the cursor
      _cursor += sz + padding;
    }

    vector<unsigned char> _data;    // archive data
    size_t _cursor;                 // current position in the data
};


// serialization archive
struct oarchive
{
    void write(float v)          { write_var(v); }
    void write(int v)            { write_var(v); }
    void write(size_t v)         { write_var(v); }
    void write(const string& v)  { write_string(v); }

    vector<unsigned char> data() { return _data; }

private:

    template <typename T>
    void write_var(const T& v)
    {
      // record the current data size
      size_t s(_data.size());

      // enlarge the data
      _data.resize(s + sizeof(T));

      // store the binary data
      std::memcpy(reinterpret_cast<void*>(&_data[s]), reinterpret_cast<const void*>(&v), sizeof(T));
    }

    void write_string(const string& v)
    {
      // write the string size
      write(v.size());

      // get alignment padding
      size_t padding = v.size() % 4;
      if (padding == 1) padding = 3;
      else if (padding == 3) padding = 1;

      // record the data size
      size_t s(_data.size());

      // enlarge the data
      _data.resize(s + v.size() + padding);

      // store the binary data
      std::memcpy(reinterpret_cast<void*>(&_data[s]), reinterpret_cast<const void*>(&v[0]), v.size());
    }

    vector<unsigned char> _data;     /// archive data
};



struct PriceEntry
{
    PriceEntry()
    {}

    PriceEntry(iarchive& in) // <<< deserialization support
    {
        in.read(cardPrice);
        in.read(PriceDate);
        in.read(Edition);
        in.read(Rarity);
    }

    void save(oarchive& out) const // <<< serialization support
    {
        out.write(cardPrice);
        out.write(PriceDate);
        out.write(Edition);
        out.write(Rarity);
    }

    float cardPrice;
    string PriceDate;
    int Edition;
    int Rarity;
};



int main()
{    
    // create a PriceEntry
    PriceEntry x;
    x.cardPrice = 1;
    x.PriceDate = "hi";
    x.Edition = 3;
    x.Rarity = 0;

    // serialize it
    oarchive out;   
    x.save(out);

    // create a deserializer archive, from serialized data
    iarchive in(out.data());

    // deserialize a PriceEntry
    PriceEntry y(in);

    std::cout << y.cardPrice << std::endl;
    std::cout << y.PriceDate << std::endl;
    std::cout << y.Edition << std::endl;
    std::cout << y.Rarity << std::endl;
}

Answer 2

You have two types here: Cards and PriceEntries. And for each Card there can be many PriceEntries.

You can store Cards in one table, one Card per row. But you're puzzled about how to store the PriceEntries, right?

What you'd normally do here is have a second table for PriceEntries, keyed off a unique column (or columns) of the Cards table. I guess the CardName is unique to each card? Let's go with that. So your PriceEntry table would have a column CardName, followed by columns of PriceEntry information. You'll have a row for each PriceEntry, even if there are duplicates in the CardName column.

The PriceEntry table might look like:

CardName  | Some PE value  | Some other PE value
Ace       | 1 | 1
Ace       | 1 | 5
2         | 2 | 3

and so on. So when you want to find the array of PriceEntries for a card, you'd do

select * from PriceEntry where CardName = 'Ace'

And from the example data above you'd get back 2 rows, which you could shove into an array (if you wanted to).

No need for BLOBs!