Repository navigation
Expand file tree
/
Copy pathinsert.cpp
More file actions
134 lines (117 loc) · 3.89 KB
/
Copy pathinsert.cpp
File metadata and controls
134 lines (117 loc) · 3.89 KB
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
133
134
/**
* This code is an implementation of this article https://www.tutorialspoint.com/sqlite/sqlite_insert_query.htm
* with C++ specific features like creating object and inserting within
* single line and separately, inserting a subclass object and inserting a vector.
*/
#include <sqlite_orm/sqlite_orm.h>
#include <string>
#include <iostream>
using std::cout;
using std::endl;
struct Employee {
int id;
std::string name;
int age;
std::string address;
double salary;
};
struct DetailedEmployee : public Employee {
std::string birthDate;
};
int main(int, char**) {
using namespace sqlite_orm;
auto storage = make_storage("insert.sqlite",
make_table("COMPANY",
make_column("ID", &Employee::id, primary_key()),
make_column("NAME", &Employee::name),
make_column("AGE", &Employee::age),
make_column("ADDRESS", &Employee::address),
make_column("SALARY", &Employee::salary)));
storage.sync_schema();
storage.remove_all<Employee>();
Employee paul{
-1,
"Paul",
32,
"California",
20000.00,
};
// insert returns inserted id
paul.id = storage.insert(paul);
storage.insert(Employee{
-1,
"Allen",
25,
"Texas",
15000.00,
});
DetailedEmployee teddy;
teddy.name = "Teddy";
teddy.age = 23;
teddy.address = "Norway";
teddy.salary = 20000.00;
// to insert subclass object as a superclass you have to specify type explicitly
teddy.id = storage.insert<Employee>(teddy);
std::vector<Employee> otherEmployees;
otherEmployees.push_back(Employee{
-1,
"Mark",
25,
"Rich-Mond",
65000.00,
});
otherEmployees.push_back(Employee{
-1,
"David",
27,
"Texas",
85000.00,
});
otherEmployees.push_back(Employee{
-1,
"Kim",
22,
"South-Hall",
45000.00,
});
// transaction is optional. It is used here to optimize sqlite usage - every insert opens
// and closes database. So triple insert will open and close the db three times.
// Transaction openes and closes the db only once.
storage.transaction([&] {
for (auto& employee: otherEmployees) {
storage.insert(employee);
}
return true; // commit
});
Employee james{
-1,
"James",
24,
"Houston",
10000.00,
};
james.id = storage.insert(james);
cout << "---------------------" << endl;
for (auto& employee: storage.iterate<Employee>()) {
cout << storage.dump(employee) << endl;
}
#if SQLITE_VERSION_NUMBER >= 3024000
// INSERT INTO COMPANY(ID, NAME, AGE, ADDRESS, SALARY)
// VALUES (3, 'Sofia', 26, 'Madrid', 15000.0)
// (4, 'Doja', 26, 'LA', 25000.0)
// ON CONFLICT(ID) DO UPDATE SET NAME = excluded.NAME,
// AGE = excluded.AGE,
// ADDRESS = excluded.ADDRESS,
// SALARY = excluded.SALARY
storage.insert(
into<Employee>(),
columns(&Employee::id, &Employee::name, &Employee::age, &Employee::address, &Employee::salary),
values(std::make_tuple(3, "Sofia", 26, "Madrid", 15000.0), std::make_tuple(4, "Doja", 26, "LA", 25000.0)),
on_conflict(&Employee::id)
.do_update(set(c(&Employee::name) = excluded(&Employee::name),
c(&Employee::age) = excluded(&Employee::age),
c(&Employee::address) = excluded(&Employee::address),
c(&Employee::salary) = excluded(&Employee::salary))));
#endif
return 0;
}