[odb-users] FK relationships

[Ronnie Chowdhury]

Hi

I’m trying to achieve this simple structure but cannot get my head around this.

A table with 

CREATE TABLE Asset (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, asset TEXT NOT NULL UNIQUE PRIMARY KEY);

A timeseries set of tables that have a foreign key into the asset

CREATE TABLE TSData (asset INTEGER NOT NULL PRIMARY KEY, CONSTRAINT asset_fk FOREIGN KEY (asset) REFERENCES Asset (id) DEFERRABLE INITIALLY DEFERRED);

CREATE TABLE TSData_close (object_id INTEGER NOT NULL, "index" INTEGER NOT NULL, value REAL, CONSTRAINT object_id_fk FOREIGN KEY (object_id) REFERENCES TSData (asset) ON DELETE CASCADE);
(repeat similar for the remaining columns for timeseries)

The closest I’ve got is

struct TSData;
#pragma db object 
struct Asset
{
public:
	Asset() = default;
	explicit Asset(const std::string& name) : m_asset(name) {}
	const std::string& asset() const { return m_asset; }
	friend class odb::access;

#pragma db id auto
	int id;
	std::string m_asset;
#pragma db value_not_null inverse(asset_)
	std::vector<std::shared_ptr<TSData>> m_ts; // One asset, references array of timeseries
};
#pragma db object
struct TSData
{
	TSData() = default;
#pragma db id auto 
	int asset_id;
	std::vector<int> ts;
	std::vector<float> m_close;
	std::vector<float> m_open;
	std::vector<float> m_high;
	std::vector<float> m_low;

#pragma db not_null 
	std::shared_ptr<Asset> asset_;
};

But this seems to not save anything in the TSData tables, and the Asset name is not unique ( I can repeated put in multiple names with the same text). 

I tried a few combinations of composite keys but not getting anywhere.

Removing the reference to Asset from TSData, and saving timeseries on it’s own seems to work fine.

What am I missing here please?

Ronnie