<!-- README.md -->
<!-- version: 1.0 -->

<!-- markdown-toc start - Don't edit this section. Run M-x markdown-toc-generate-toc again -->
<!-- markdown-toc end -->

# advotracker - database handling

`advotracker` make use of [Diesel][^1] to handle all SQL access functionality.
Diesel requires to be compiled with Rust version 1.24 or later.

The actual version is tested with the `sqlite3` backend. All following documentation
will reference to this constellation.

# Diesel

First of all, add the required dependencies to the projects Cargo.toml

```bash
[dependencies]
diesel = { version = "1.4.4", features = ["sqlite" "chrono"] }
dotenv = "0.15.0"
```

## CLI helper

Diesel provides a separate CLI tool to help manage the project.
This CLI should be installed on your system. I you don't have that
already available on you system, go and install it like this:

```bash
cargo install diesel_cli
cargo install diesel_cli --no-default-features --features sqlite3
```

## Database Setup

We do uses `sqlite` for now. Use your distro package for sqlite3.

Now direct diesel to the correct database instance. We can create an
environment variable `DATABASE_URL`, that will point to the instance.
For the test setup, we take advantage of an .env file:

```.env
DATABASE_URL=sqlite://localhost/advotracker
#DATABASE_URL=postgres://username:password@localhost/advotracker
```

- **Instantiate a new database**

In the beginning, there is .... nothing!
We will create an empty database to get started:

```bash
$ sqlite3 <absolute_path_to>/advotracker.sqlite3>
sqlite3 advotracker.sqlite3
```

- **Test availability**

Sqlite will drop us in the CLI shell. Try to get available
databases, which should response a `main` pointing to the
absolute path of the created database.

```bash

SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> .databases
main: /<the_absolute_path>/advotracker.sqlite3
.quit
```

All fine.

## Diesel Setup

When managing the database schema, probably it will evolve over the time.
Diesel takes that into account and supports a bundles called `Migrations`.
They allow us to define states, that can be applied (up.sql) or reverted (down.sql).
Applying and immediately reverting a migration is a good test, to make sure you
that the used migration leaves your database schema in the requested state.

- **Instantiate a migration**

The following command will crate the basic `advotracker` structure.

```bash
diesel setup
diesel migration generate advotracker
```

You can review the generated structure in the subfolder `migrations`. It
holds a timestamp based subfolder, named `<timestamp-advotracker`, that provides
to empty files. You'll see output that looks something like this:

```bash
Creating migrations/20200625133000_advotracker/up.sql
Creating migrations/20200625133000_advotracker/down.sql
```

- **Update the SQL-files**

Both files have to be updated to meet our target structure. `up.sql` will
create tables, beside their constraints, indices and views. `down.sql` work
delete the database schema providing a virgin state.
If you leave this scripts empty, diesel can't generate the target `schema` file,
that is used to provide the API consumed by you rust code.
You are free to edit and apply this in a manual fashion, but this is not
recommended.

- **Migration run**

We can apply the new migration like this:

```bash
diesel migration run
```

Using the the `redo` command will prove the expected functionality: Run down and
up scrips in a sequence. This will drop the given schema and create a new one using the
adopted files in the migration path.

```bash
diesel migration run
```
- **Test our generated structure**

As a basic success test, we the CLI interface should respond with a list of indices,
if we call a query. Go ahead an run:

```bash
$ sqlite3 <abolute_path_to>/advotracker.sqlite3>
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> .index
claims_ix_id_user
harms_ix_number_harm
sqlite_autoindex___diesel_schema_migrations_1
sqlite_autoindex_roles_1
sqlite_autoindex_user_roles_1
user_roles_ix_id_role
user_roles_ix_id_user
users_ix_email
.quit
```

You are done. The database is ready to be accessed for the
advotracker rust code.

## Reference

Any further information can be obtained from [Diesel's project page][^1].

---

[Logo-CC_BY]: https://i.creativecommons.org/l/by/4.0/88x31.png "Creative Common Logo"
[License-CC_BY]: https://creativecommons.org/licenses/by/4.0/legalcode "Creative Common License"
This work is licensed under a [Creative Common License 4.0][License-CC_BY]

![Creative Common Logo][Logo-CC_BY]

© 2020 Ralf Zerres, Networkx GmbH

---

Footnotes

[^1]: Diesel Guides:  https://diesel.rs/guides/getting-started/