Mercurial > hg > dbutils / file comparison

comparison: test/main.c

test/main.c

changeset 7
c98ff52cd806
parent 5
f0c66b2139ea
child 8
bd08116b8af4
equal deleted inserted replaced
6:d6981b56ab30 7:c98ff52cd806
48 "address_id integer primary key autoincrement, " 48 "address_id integer primary key autoincrement, "
49 "street text, " 49 "street text, "
50 "zip text, " 50 "zip text, "
51 "city text);"; 51 "city text);";
52 52
53 const char *sql_create_table_role =
54 "create table if not exists Role ("
55 "role_id integer primary key autoincrement, "
56 "person_id integer, "
57 "name text);";
58
53 59
54 const char *sql_check_table = 60 const char *sql_check_table =
55 "select person_id from Person limit 1;"; 61 "select person_id from Person limit 1;";
56
57
58 62
59 const char *sql_create_test_data1 = 63 const char *sql_create_test_data1 =
60 "insert into address (street, zip, city) " 64 "insert into address (street, zip, city) "
61 "values " 65 "values "
62 "('street 1', '12343', 'city 17'), " 66 "('street 1', '12343', 'city 17'), "
63 "('street 2', '23456', 'city 18');"; 67 "('street 2', '23456', 'city 18');";
64 68
65 const char *sql_create_test_data2 = 69 const char *sql_create_test_data2 =
66 "insert into person (name, email, age, iscustomer, hash, address_id) " 70 "insert into person (name, email, age, iscustomer, hash, address_id) "
67 "values " 71 "values "
68 "('alice', 'alice@example.com', 30, 1, 123456789, (select address_id from address where street = 'street 1')), " 72 "('alice', 'alice@example.com', 30, 0, 123456789, (select address_id from address where street = 'street 1')), "
69 "('bob', 'bob@example.com', 25, 0, 987654321, (select address_id from address where street = 'street 2'));"; 73 "('bob', 'bob@example.com', 25, 1, 987654321, (select address_id from address where street = 'street 2'));";
74
75 const char *sql_create_test_data3 =
76 "insert into role (person_id, name) "
77 "values "
78 "(1, 'finance'), "
79 "(1, 'dev'), "
80 "(1, 'manager'), "
81 "(2, 'extern');";
70 82
71 typedef struct Address { 83 typedef struct Address {
72 int64_t address_id; 84 int64_t address_id;
73 85
74 cxmutstr street; 86 cxmutstr street;
84 int age; 96 int age;
85 bool iscustomer; 97 bool iscustomer;
86 uint64_t hash; 98 uint64_t hash;
87 99
88 Address *address; 100 Address *address;
101
102 CxList *roles;
89 } Person; 103 } Person;
90 104
105 typedef struct Role {
106 int64_t role_id;
107 int64_t person_id;
108 cxmutstr name;
109 } Role;
91 110
92 111
93 static int create_test_data(sqlite3 *db); 112 static int create_test_data(sqlite3 *db);
94 113
95 int main(int argc, char **argv) { 114 int main(int argc, char **argv) {
99 118
100 DBUClass *address = dbuRegisterClass(ctx, "address", Address, address_id); 119 DBUClass *address = dbuRegisterClass(ctx, "address", Address, address_id);
101 dbuClassAdd(address, Address, street); 120 dbuClassAdd(address, Address, street);
102 dbuClassAdd(address, Address, zip); 121 dbuClassAdd(address, Address, zip);
103 dbuClassAdd(address, Address, city); 122 dbuClassAdd(address, Address, city);
123
124 DBUClass *role = dbuRegisterClass(ctx, "role", Role, role_id);
104 125
105 DBUClass *person = dbuRegisterClass(ctx, "person", Person, person_id); 126 DBUClass *person = dbuRegisterClass(ctx, "person", Person, person_id);
106 dbuClassAdd(person, Person, name); 127 dbuClassAdd(person, Person, name);
107 dbuClassAdd(person, Person, email); 128 dbuClassAdd(person, Person, email);
108 dbuClassAdd(person, Person, age); 129 dbuClassAdd(person, Person, age);
109 dbuClassAdd(person, Person, iscustomer); 130 dbuClassAdd(person, Person, iscustomer);
110 dbuClassAdd(person, Person, hash); 131 dbuClassAdd(person, Person, hash);
111 dbuClassAddObj(person, "address_id", offsetof(Person, address), address); 132 dbuClassAddObj(person, "address_id", offsetof(Person, address), address);
112 133 dbuClassAddCxLinkedList(person, "person_id", offsetof(Person, roles), role);
134
135 dbuClassAddForeignKey(role, Role, person_id, person);
136 dbuClassAdd(role, Role, name);
113 137
114 // Open or create the database 138 // Open or create the database
115 sqlite3 *db; 139 sqlite3 *db;
116 int rc = sqlite3_open("test.db", &db); 140 int rc = sqlite3_open("test.db", &db);
117 if(rc != SQLITE_OK) { 141 if(rc != SQLITE_OK) {
126 150
127 DBUConnection *conn = dbuSQLiteConnectionFromDB(db, true); 151 DBUConnection *conn = dbuSQLiteConnectionFromDB(db, true);
128 DBUQuery *query = conn->createQuery(conn, NULL); 152 DBUQuery *query = conn->createQuery(conn, NULL);
129 dbuQuerySetSQL(query, "select p.*, a.address_id as [__address__address_id], a.street, a.zip, a.city from Person p inner join Address a on p.address_id = a.address_id;"); 153 dbuQuerySetSQL(query, "select p.*, a.address_id as [__address__address_id], a.street, a.zip, a.city from Person p inner join Address a on p.address_id = a.address_id;");
130 154
155 DBUQuery *roleQuery = conn->createQuery(conn, NULL);
156 dbuQuerySetSQL(roleQuery, "select * from role;");
157
131 DBUObjectBuilder *builder = dbuObjectBuilder(person, query, cxDefaultAllocator); 158 DBUObjectBuilder *builder = dbuObjectBuilder(person, query, cxDefaultAllocator);
159 dbuObjectBuilderAddAdditionalQuery(builder, role, roleQuery);
132 CxList *persons = dbuObjectBuilderGetList(builder); 160 CxList *persons = dbuObjectBuilderGetList(builder);
133 if(persons) { 161 if(persons) {
134 CxIterator i = cxListIterator(persons); 162 CxIterator i = cxListIterator(persons);
135 cx_foreach(Person *, p, i) { 163 cx_foreach(Person *, p, i) {
136 printf("{ person_id = %" PRId64 ", name = \"%s\", email = \"%s\", age = %d, iscustomer = %s, hash = %" PRIu64 " }\n", 164 printf("{ person_id = %" PRId64 ", name = \"%s\", email = \"%s\", age = %d, iscustomer = %s, hash = %" PRIu64 " }\n",
145 return 0; 173 return 0;
146 } 174 }
147 175
148 static int create_test_data(sqlite3 *db) { 176 static int create_test_data(sqlite3 *db) {
149 char *err_msg = NULL; 177 char *err_msg = NULL;
150 int rc = sqlite3_exec(db, sql_create_table_person, 0, 0, &err_msg); 178 sqlite3_stmt *stmt;
179 int rc = sqlite3_prepare_v2(db, sql_check_table, -1, &stmt, 0);
180 if(rc == SQLITE_OK) {
181 return 0;
182 }
183
184 rc = sqlite3_exec(db, sql_create_table_person, 0, 0, &err_msg);
151 if(rc != SQLITE_OK) { 185 if(rc != SQLITE_OK) {
152 fprintf(stderr, "SQLite error: %s\n", err_msg); 186 fprintf(stderr, "SQLite error: %s\n", err_msg);
153 sqlite3_free(err_msg); 187 sqlite3_free(err_msg);
154 sqlite3_close(db); 188 sqlite3_close(db);
155 return 1; 189 return 1;
161 sqlite3_free(err_msg); 195 sqlite3_free(err_msg);
162 sqlite3_close(db); 196 sqlite3_close(db);
163 return 1; 197 return 1;
164 } 198 }
165 199
166 200 rc = sqlite3_exec(db, sql_create_table_role, 0, 0, &err_msg);
167 sqlite3_stmt *stmt; 201 if(rc != SQLITE_OK) {
168 rc = sqlite3_prepare_v2(db, sql_check_table, -1, &stmt, 0); 202 fprintf(stderr, "SQLite error: %s\n", err_msg);
169 if(rc != SQLITE_OK) { 203 sqlite3_free(err_msg);
170 fprintf(stderr, "SQLite error: %s\n", sqlite3_errmsg(db)); 204 sqlite3_close(db);
171 sqlite3_close(db); 205 return 1;
172 return 1;
173 }
174
175 int exists = 0;
176 if(sqlite3_step(stmt) == SQLITE_ROW) {
177 exists = 1;
178 }
179 sqlite3_finalize(stmt);
180
181 if(exists) {
182 return 0;
183 } 206 }
184 207
185 rc = sqlite3_exec(db, sql_create_test_data1, 0, 0, &err_msg); 208 rc = sqlite3_exec(db, sql_create_test_data1, 0, 0, &err_msg);
186 if(rc != SQLITE_OK) { 209 if(rc != SQLITE_OK) {
187 fprintf(stderr, "SQLite error: %s\n", err_msg); 210 fprintf(stderr, "SQLite error: %s\n", err_msg);
189 sqlite3_close(db); 212 sqlite3_close(db);
190 return 1; 213 return 1;
191 } 214 }
192 215
193 rc = sqlite3_exec(db, sql_create_test_data2, 0, 0, &err_msg); 216 rc = sqlite3_exec(db, sql_create_test_data2, 0, 0, &err_msg);
217 if(rc != SQLITE_OK) {
218 fprintf(stderr, "SQLite error: %s\n", err_msg);
219 sqlite3_free(err_msg);
220 sqlite3_close(db);
221 return 1;
222 }
223
224 rc = sqlite3_exec(db, sql_create_test_data3, 0, 0, &err_msg);
194 if(rc != SQLITE_OK) { 225 if(rc != SQLITE_OK) {
195 fprintf(stderr, "SQLite error: %s\n", err_msg); 226 fprintf(stderr, "SQLite error: %s\n", err_msg);
196 sqlite3_free(err_msg); 227 sqlite3_free(err_msg);
197 sqlite3_close(db); 228 sqlite3_close(db);
198 return 1; 229 return 1;

Mercurial Repository: dbutils

mercurial