1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
#include <format>
#include <memory>
#include <string>
#include <expected>
#include <mw/database.hpp>
#include <mw/error.hpp>
#include <mw/utils.hpp>
#include "data.hpp"
// id, owner_id, parent_id, name, url, desc, visibility, time.
#define LINK_TUPLE_TYPES int64_t,int64_t,int64_t,std::string,std::string,std::string,int,int64_t
namespace
{
using LinkTuple = std::tuple<LINK_TUPLE_TYPES>;
LinkItem linkFromTuple(const LinkTuple& t)
{
LinkItem l;
l.id = std::get<0>(t);
l.owner_id = std::get<1>(t);
int64_t p = std::get<2>(t);
if(p == 0)
{
l.parent_id = std::nullopt;
}
else
{
l.parent_id = p;
}
l.name = std::get<3>(t);
l.url = std::get<4>(t);
l.description = std::get<5>(t);
l.visibility = static_cast<LinkItem::Visibility>(std::get<6>(t));
l.time = mw::secondsToTime(std::get<7>(t));
return l;
}
} // namespace
mw::E<std::unique_ptr<DataSourceSQLite>>
DataSourceSQLite::fromFile(const std::string& db_file)
{
auto data_source = std::make_unique<DataSourceSQLite>();
ASSIGN_OR_RETURN(data_source->db, mw::SQLite::connectFile(db_file));
DO_OR_RETURN(data_source->db->execute("PRAGMA foreign_keys = ON;"));
// Perform schema upgrade here.
//
// data_source->upgradeSchema1To2();
// Update this line when schema updates.
DO_OR_RETURN(data_source->setSchemaVersion(1));
DO_OR_RETURN(data_source->db->execute(
"CREATE TABLE IF NOT EXISTS Users "
"(id INTEGER PRIMARY KEY, openid_uid TEXT, name TEXT);"));
DO_OR_RETURN(data_source->db->execute(
"CREATE TABLE IF NOT EXISTS LinkItems "
"(id INTEGER PRIMARY KEY, owner_id INTEGER NOT NULL,"
" parent_id INTEGER,"
" name TEXT NOT NULL, url TEXT, description TEXT,"
" visibility INTEGER NOT NULL, time INTEGER NOT NULL,"
" FOREIGN KEY(owner_id) REFERENCES Users(id),"
" FOREIGN KEY(parent_id) REFERENCES LinkItems(id));"));
return data_source;
}
mw::E<std::unique_ptr<DataSourceSQLite>> DataSourceSQLite::newFromMemory()
{
return fromFile(":memory:");
}
mw::E<int64_t> DataSourceSQLite::getSchemaVersion() const
{
return db->evalToValue<int64_t>("PRAGMA user_version;");
}
mw::E<void> DataSourceSQLite::setSchemaVersion(int64_t v) const
{
return db->execute(std::format("PRAGMA user_version = {};", v));
}
mw::E<std::vector<LinkItem>>
DataSourceSQLite::items(std::optional<int64_t> parent)
{
std::vector<LinkTuple> links;
if(!parent.has_value())
{
// Querying root-level links.
ASSIGN_OR_RETURN(links, (db->eval<LINK_TUPLE_TYPES>(
"SELECT id, owner_id, parent_id, name, url, description, visibility,"
" time from LinkItems WHERE parent_id IS NULL;")));
}
else
{
ASSIGN_OR_RETURN(mw::SQLiteStatement stat, db->statementFromStr(
"SELECT id, owner_id, parent_id, name, url, description, visibility,"
" time from LinkItems WHERE parent_id = ?;"));
DO_OR_RETURN(stat.bind<int64_t>(*parent));
ASSIGN_OR_RETURN(links, (db->eval<LINK_TUPLE_TYPES>(std::move(stat))));
}
std::vector<LinkItem> result;
for(const LinkTuple& t : links)
{
result.push_back(linkFromTuple(t));
}
return result;
}
mw::E<int64_t> DataSourceSQLite::addLink(LinkItem&& link)
{
ASSIGN_OR_RETURN(mw::SQLiteStatement sql, db->statementFromStr(
"INSERT INTO LinkItems (id, owner_id, parent_id, name, url,"
" description, visibility, time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"));
if(link.parent_id.has_value())
{
DO_OR_RETURN(sql.bind(
std::nullopt, link.owner_id, *link.parent_id, link.name, link.url,
link.description, static_cast<int>(link.visibility),
mw::timeToSeconds(mw::Clock::now())));
}
else
{
DO_OR_RETURN(sql.bind(
std::nullopt, link.owner_id, std::nullopt, link.name, link.url,
link.description, static_cast<int>(link.visibility),
mw::timeToSeconds(mw::Clock::now())));
}
DO_OR_RETURN(db->execute(std::move(sql)));
return db->lastInsertRowID();
}
|
