utilities.md 4.87 KB
Newer Older
1 2 3 4 5 6 7 8 9 10
# Utilities

SOCI provides a portable abstraction for selection of database queries.

## DDL

SOCI supports some basic methods to construct portable DDL queries. That is, instead of writing explicit SQL statement for creating or modifying tables, it is possible to use dedicated SOCI functions, which prepare appropriate DDL statements behind the scenes, thus enabling the user application to create basic database structures in a way that is portable across different database servers. Note that the actual support for these functions depends on the actual backend implementation.

It is possible to create a new table in a single statement:

11 12 13
```cpp
sql.create_table("t1").column("i", soci::dt_integer).column("j", soci::dt_integer);
```
14 15 16 17 18

Above, table "t1" will be created with two columns ("i", "j") of type integer.

It is also possible to build similar statements piece by piece, which is useful if the table structure is computed dynamically:

19 20 21 22 23 24 25 26 27
```cpp
{
    soci::ddl_type ddl = sql.create_table("t2");
    ddl.column("i", soci::dt_integer);
    ddl.column("j", soci::dt_integer);
    ddl.column("k", soci::dt_integer)("not null");
    ddl.primary_key("t2_pk", "j");
}
```
28 29 30 31 32

The actual statement is executed at the end of above block, when the ddl object goes out of scope. The "not null" constraint was added to the definition of column "k" explicitly and in fact any piece of SQL can be inserted this way - with the obvious caveat of having limited portability (the "not null" piece seems to be universaly portable).

Columns can be added to and dropped from already existing tables as well:

33 34 35 36 37 38 39
```cpp
sql.add_column("t1", "k", soci::dt_integer);
// or with constraint:
//sql.add_column("t1", "k", soci::dt_integer)("not null");

sql.drop_column("t1", "i");
```
40 41 42

If needed, precision and scale can be defined with additional integer arguments to functions that create columns:

43 44 45 46
```cpp
sql.add_column("t1", "s", soci::dt_string, precision);
sql.add_column("t1", "d", soci::dt_double, precision, scale);
```
47 48 49

Tables with foreign keys to each other can be also created:

50 51 52 53 54 55 56 57
```cpp
{
    soci::ddl_type ddl = sql.create_table("t3");
    ddl.column("x", soci::dt_integer);
    ddl.column("y", soci::dt_integer);
    ddl.foreign_key("t3_fk", "x", "t2", "j");
}
```
58 59 60

Tables can be dropped, too:

61 62 63 64 65
```cpp
sql.drop_table("t1");
sql.drop_table("t3");
sql.drop_table("t2");
```
66 67 68 69 70

Note that due to the differences in the set of types that are actually supported on the target database server, the type mappings, as well as precision and scales, might be different, even in the way that makes them impossible to portably recover with metadata queries.

In the category of portability utilities, the following functions are also available:

71 72 73
```cpp
sql.empty_blob()
```
74 75 76

the above call returns the string containing expression that represents an empty BLOB value in the given target backend. This expression can be used as part of a bigger SQL statement, for example:

77 78 79
```cpp
sql << "insert into my_table (x) values (" + sql.empty_blob() + ")";
```
80 81 82

and:

83 84 85
```cpp
sql.nvl()
```
86 87 88

the above call returns the string containing the name of the SQL function that implements the NVL or COALESCE operation in the given target backend, for example:

89 90 91
```cpp
sql << "select name, " + sql.nvl() + "(phone, \'UNKNOWN\') from phone_book";
```
92 93 94 95 96 97 98 99 100 101 102 103

Note: `empty_blob` and `nvl` are implemented in Oracle, PostgreSQL and SQLite3 backends; for other backends their behaviour is as for PostgreSQL.

## DML

Only two related functions are currently available in this category:
`get_dummy_from_clause()` can be used to construct select statements that don't
operate on any table in a portable way, as while some databases allow simply
omitting the from clause in this case, others -- e.g. Oracle -- still require
providing some syntactically valid from clause even if it is not used. To use
this function, simply append the result of this function to the statement:

104 105 106 107 108
```cpp
double databasePi;
session << ("select 4*atan(1)" + session.get_dummy_from_clause()),
            into(databasePi);
```
109 110 111 112 113 114 115 116 117 118 119 120 121

If just the name of the dummy table is needed, and not the full clause, you can
use `get_dummy_from_table()` to obtain it.

Notice that both functions require the session to be connected as their result
depends on the database it is connected to.

## Database Metadata

It is possible to portably query the database server to obtain basic metadata information.

In order to get the list of table names in the current schema:

122 123 124 125
```cpp
std::vector<std::string> names(100);
sql.get_table_names(), into(names);
```
126 127 128

alternatively:

129 130 131 132 133 134 135 136 137 138
```cpp
std::string name;
soci::statement st = (sql.prepare_table_names(), into(name));

st.execute();
while (st.fetch())
{
    // ...
}
```
139 140 141

Similarly, to get the description of all columns in the given table:

142 143 144
```cpp
soci::column_info ci;
soci::statement st = (sql.prepare_column_descriptions(table_name), into(ci));
145

146 147 148 149 150 151
st.execute();
while (st.fetch())
{
    // ci fields describe each column in turn
}
```