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
|
SQL
===
This package contains a generic SQL library that consists of two parts:
- A generic interface to databases
- A syntax extension to Basic Storm that allows type-safe interaction with the database
Currently, three databases are supported: SQLite, MariaDB, and MySQL.
An SQLite connection can be established in memory or in a file as follows:
- `SQLite db();`
- `SQLite db(cwdUrl/"database.db");`
A statement is then prepared and executed as follows:
- `var statement = db.prepare("SQL QUERY ?;");`
- `statement.bind(x, "string");`
- `statement.execute();`
There are then a number of methods in the Statement class that allows examining the result. In
particular it is possible to use the Statement as an iterator for queries that produce table
results.
It is also possible to use the language extension in Basic Storm:
- `Str x = "c";`
- `WITH db: INSERT INTO table VALUES ("a", "b", x);`
The above does not allow Storm to type-check the queries. For that to work, it is necessary to
declare a database first:
```bs
use sql;
DATABASE MyDB {
TABLE test (
a INTEGER PRIMARY KEY,
b TEXT
);
INDEX ON test(b);
}
```
It is worth noting that columns are NOT NULL by default. Use ALLOW NULL if null are desired.
Similarly, if a single integer-type column is marked as `PRIMARY KEY`, then it will be marked as
autoincrement by default.
The table may then be queried as follows:
```bsstmt:use=sql
MyDB connection(db);
for (row in WITH c: SELECT * FROM test) {
print(row.a # " " # b);
}
```
Data Types
----------
The following data types are supported:
- `TEXT`
Specifies text, either of unlimited size, or with a specified maximum length. If `TEXT` appears
without a size, it generally maps to the `TEXT` datatype in databases. If specified with a size
(e.g. `TEXT(10)`) it maps to `VARCHAR` or a similar datatype.
- `INTEGER`
Specifies an integer type. The SQL plugin uses an optional size to specify different sizes of
integers (rather than separate types). In particular, `INTEGER` without a size refers to a type
that is at least 4 bytes wide. For smaller types, use `INTEGER(2)` for a 2-byte integer, and
`INTEGER(8)` for an 8 byte integer. Some databases (e.g. MariaDB and MySQL) support `INTEGER(1)`
and `INTEGER(3)` as well. These types are just converted to the next larger type on databases that
don't support them, so they can be used there as well.
- `REAL`
Specifies a floating-point type. Similarly to integers, `REAL(8)` can be used for a
double-precision type.
Connections
-----------
When connecting to a server-based database, it is necessary to specify how to connect to the
database. The SQL library uses the type `Host` to represent different connection methods. It can be
created as follows:
- `Host:local()`
Use the default connection method to connect to a local database server. The exact semantics
varies depending on the database. The benefit of this method is that it is typically does not
require authentication since it can rely on the identity of the current system user instead.
- `Host:local(Str)`
Connect to localhost using a custom name. On UNIX, the string is often the name of a UNIX domain
socket.
- `Host:socket(Address)`
Connect to another database on another machine, reached via the specified `Address`. Note that
addresses can be created from the `core:net:toAddress` function or the `core:net:lookupAddress`
function. See examples below.
For example, to connect to a MariaDB database on localhost, one can create a connection as
illustrated below. The two parameters that are `null` are the username and the password. All of
`MariaDB`, `MySQL`, and `PostgreSQL` allows omitting both, which causes them to use the username of
the current system user and an empty password.
```bsstmt
MariaDB connection(Host:local(), null, null, "database");
```
For remote connections, ask the database to use a socket instead. For this, either use
`core:net:toAddress` to parse an IPv4 or IPv6 address as follows:
```bsstmt
PostgreSQL connection(Host:socket(core:net:toAddress("192.168.0.1")), "user", "password", "database");
```
Or, use `core:net:lookupAddress` to resolve a textual address:
```bsstmt
MySQL connection(Host:socket(core:net:lookupAddress("localhost")), "user", "password", "database");
```
|
