1 | import os |
2 | import db.sqlite |
3 | import db.mysql |
4 | import db.pg |
5 | |
6 | // The goal of this example, is to show how you can connect to |
7 | // several different databases in the same program, and use both |
8 | // the ORM and the native connection wrapper, that each DB driver |
9 | // provides, if you need to execute more complex SQL queries. |
10 | // |
11 | // You can use environment variables to pass your local DB connection |
12 | // settings, without editing the code, like this: |
13 | // |
14 | // MUSER='myuser' MPASS='abc' MDATABASE='vtestdb' PGUSER='postgres' PGPASS='password' PGDATABASE='postgres' ./v -g run examples/database/orm.v |
15 | // |
16 | // WARNING: this example will drop and re-create any tables named: |
17 | // * modules |
18 | // * User |
19 | // * Parent |
20 | // * Child |
21 | // in the passed databases, so it is better to use empty DBs for it. |
22 | |
23 | const ( |
24 | mysql_host = os.getenv_opt('MHOST') or { 'localhost' } |
25 | mysql_port = os.getenv_opt('MPORT') or { '3306' }.u32() |
26 | mysql_user = os.getenv_opt('MUSER') or { 'myuser' } |
27 | mysql_pass = os.getenv_opt('MPASS') or { 'abc' } |
28 | mysql_db = os.getenv_opt('MDATABASE') or { 'test' } |
29 | ) |
30 | |
31 | const ( |
32 | pg_host = os.getenv_opt('PGHOST') or { 'localhost' } |
33 | pg_user = os.getenv_opt('PGUSER') or { 'test' } |
34 | pg_pass = os.getenv_opt('PGPASS') or { 'abc' } |
35 | pg_db = os.getenv_opt('PGDATABASE') or { 'test' } |
36 | ) |
37 | |
38 | [table: 'modules'] |
39 | struct Module { |
40 | id int [primary; sql: serial] |
41 | name string |
42 | nr_downloads int [sql: u64] |
43 | creator User |
44 | } |
45 | |
46 | struct User { |
47 | id int [primary; sql: serial] |
48 | age u32 [unique: 'user'] |
49 | name string [sql: 'username'; sql_type: 'VARCHAR(200)'; unique] |
50 | is_customer bool [sql: 'abc'; unique: 'user'] |
51 | skipped_string string [skip] |
52 | } |
53 | |
54 | struct Parent { |
55 | id int [primary; sql: serial] |
56 | name string |
57 | children []Child [fkey: 'parent_id'] |
58 | } |
59 | |
60 | struct Child { |
61 | id int [primary; sql: serial] |
62 | parent_id int |
63 | name string |
64 | } |
65 | |
66 | fn sqlite3_array() ! { |
67 | eprintln('------------ ${@METHOD} -----------------') |
68 | mut db := sqlite.connect(':memory:')! |
69 | defer { |
70 | sql db { |
71 | drop table Parent |
72 | drop table Child |
73 | } or {} |
74 | db.close() or {} |
75 | } |
76 | |
77 | sql db { |
78 | create table Parent |
79 | }! |
80 | sql db { |
81 | create table Child |
82 | }! |
83 | par := Parent{ |
84 | name: 'test' |
85 | children: [ |
86 | Child{ |
87 | name: 'abc' |
88 | }, |
89 | Child{ |
90 | name: 'def' |
91 | }, |
92 | ] |
93 | } |
94 | sql db { |
95 | insert par into Parent |
96 | }! |
97 | parent := sql db { |
98 | select from Parent where id == 1 |
99 | }! |
100 | eprintln(parent) |
101 | } |
102 | |
103 | fn msql_array() ! { |
104 | eprintln('------------ ${@METHOD} -----------------') |
105 | mut db := mysql.connect( |
106 | host: mysql_host |
107 | port: mysql_port |
108 | username: mysql_user |
109 | password: mysql_pass |
110 | dbname: mysql_db |
111 | )! |
112 | defer { |
113 | sql db { |
114 | drop table Parent |
115 | } or {} |
116 | db.close() |
117 | } |
118 | |
119 | db.query('drop table if exists Parent')! |
120 | db.query('drop table if exists Child')! |
121 | sql db { |
122 | create table Parent |
123 | create table Child |
124 | }! |
125 | par := Parent{ |
126 | name: 'test' |
127 | children: [ |
128 | Child{ |
129 | name: 'abc' |
130 | }, |
131 | Child{ |
132 | name: 'def' |
133 | }, |
134 | ] |
135 | } |
136 | sql db { |
137 | insert par into Parent |
138 | }! |
139 | parent := sql db { |
140 | select from Parent where id == 1 |
141 | }! |
142 | eprintln(parent) |
143 | } |
144 | |
145 | fn psql_array() ! { |
146 | eprintln('------------ ${@METHOD} -----------------') |
147 | mut db := pg.connect(host: pg_host, user: pg_user, password: pg_pass, dbname: pg_db)! |
148 | defer { |
149 | db.exec_one('drop table if exists "Parent", "Child"') or { eprintln(err) } |
150 | db.close() |
151 | } |
152 | db.exec_one('drop table if exists "Parent", "Child"') or { eprintln(err) } |
153 | |
154 | sql db { |
155 | create table Parent |
156 | create table Child |
157 | }! |
158 | par := Parent{ |
159 | name: 'test' |
160 | children: [ |
161 | Child{ |
162 | name: 'abc' |
163 | }, |
164 | Child{ |
165 | name: 'def' |
166 | }, |
167 | ] |
168 | } |
169 | sql db { |
170 | insert par into Parent |
171 | }! |
172 | parent := sql db { |
173 | select from Parent where id == 1 |
174 | }! |
175 | eprintln(parent) |
176 | } |
177 | |
178 | fn sqlite3() ! { |
179 | eprintln('------------ ${@METHOD} -----------------') |
180 | mut db := sqlite.connect(':memory:')! |
181 | defer { |
182 | sql db { |
183 | drop table Module |
184 | drop table User |
185 | } or {} |
186 | db.close() or {} |
187 | } |
188 | |
189 | sql db { |
190 | create table Module |
191 | }! |
192 | sql db { |
193 | create table User |
194 | }! |
195 | mod := Module{ |
196 | name: 'test' |
197 | nr_downloads: 10 |
198 | creator: User{ |
199 | age: 21 |
200 | name: 'VUser' |
201 | is_customer: true |
202 | } |
203 | } |
204 | sql db { |
205 | insert mod into Module |
206 | }! |
207 | modul := sql db { |
208 | select from Module where id == 1 |
209 | }! |
210 | eprintln(modul) |
211 | } |
212 | |
213 | fn msql() ! { |
214 | eprintln('------------ ${@METHOD} -----------------') |
215 | mut conn := mysql.connect( |
216 | host: mysql_host |
217 | port: mysql_port |
218 | username: mysql_user |
219 | password: mysql_pass |
220 | dbname: mysql_db |
221 | )! |
222 | defer { |
223 | conn.query('DROP TABLE IF EXISTS Module') or { eprintln(err) } |
224 | conn.query('DROP TABLE IF EXISTS User') or { eprintln(err) } |
225 | conn.close() |
226 | } |
227 | conn.query('DROP TABLE IF EXISTS Module') or { eprintln(err) } |
228 | conn.query('DROP TABLE IF EXISTS User') or { eprintln(err) } |
229 | |
230 | sql conn { |
231 | create table Module |
232 | }! |
233 | sql conn { |
234 | create table User |
235 | }! |
236 | mod := Module{ |
237 | name: 'test' |
238 | nr_downloads: 10 |
239 | creator: User{ |
240 | age: 21 |
241 | name: 'VUser' |
242 | is_customer: true |
243 | } |
244 | } |
245 | sql conn { |
246 | insert mod into Module |
247 | }! |
248 | m := sql conn { |
249 | select from Module where id == 1 |
250 | }! |
251 | eprintln(m) |
252 | } |
253 | |
254 | fn psql() ! { |
255 | eprintln('------------ ${@METHOD} -----------------') |
256 | mut db := pg.connect(host: pg_host, user: pg_user, password: pg_pass, dbname: pg_db)! |
257 | defer { |
258 | db.exec_one('drop table if exists "modules", "User"') or { eprintln(err) } |
259 | db.close() |
260 | } |
261 | db.exec_one('drop table if exists "modules", "User"') or { eprintln(err) } |
262 | sql db { |
263 | create table Module |
264 | create table User |
265 | }! |
266 | mod := Module{ |
267 | name: 'test' |
268 | nr_downloads: 10 |
269 | creator: User{ |
270 | age: 21 |
271 | name: 'VUser' |
272 | is_customer: true |
273 | } |
274 | } |
275 | sql db { |
276 | insert mod into Module |
277 | }! |
278 | modul := sql db { |
279 | select from Module where id == 1 |
280 | }! |
281 | sql db { |
282 | drop table Module |
283 | }! |
284 | eprintln(modul) |
285 | } |
286 | |
287 | fn main() { |
288 | eprintln('------------ ${@METHOD} -----------------') |
289 | sqlite3_array()! |
290 | msql_array()! |
291 | psql_array()! |
292 | |
293 | sqlite3()! |
294 | msql()! |
295 | psql()! |
296 | } |