170 lines
4.6 KiB
Markdown
170 lines
4.6 KiB
Markdown
<!-- 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/
|