finql 0.12.0

A quantitative finance toolbox
Documentation 
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
131
132
133
134
135
136
137
138
139
140

///! Implementation of PostgreSQL data handler
use sqlx::postgres::{PgPoolOptions, Postgres};

pub mod asset_handler;
pub mod object_handler;
pub mod quote_handler;
pub mod transaction_handler;

/// Struct to handle connections to postgres databases
pub struct PostgresDB {
    /// pool is made public to allow extending this struct outside of the library
    pub pool: sqlx::Pool<Postgres>,
}

impl PostgresDB {
    pub async fn new(connection_string: &str) -> Result<PostgresDB, sqlx::Error> {
        let pool = PgPoolOptions::new()
            .max_connections(5)
            .connect(connection_string)
            .await?;
        Ok(PostgresDB { pool })
    }

    /// Clean database by dropping all tables and than run init
    pub async fn clean(&self) -> Result<(), sqlx::Error> {
        sqlx::query!("DROP TABLE IF EXISTS transactions")
            .execute(&self.pool)
            .await?;
        sqlx::query!("DROP TABLE IF EXISTS quotes")
            .execute(&self.pool)
            .await?;
        sqlx::query!("DROP TABLE IF EXISTS ticker")
            .execute(&self.pool)
            .await?;
        sqlx::query!("DROP TYPE IF EXISTS market_data_source")
            .execute(&self.pool)
            .await?;
        sqlx::query!("DROP TABLE IF EXISTS currencies")
            .execute(&self.pool)
            .await?;
        sqlx::query!("DROP TABLE IF EXISTS stocks")
            .execute(&self.pool)
            .await?;
        sqlx::query!("DROP TABLE IF EXISTS assets")
            .execute(&self.pool)
            .await?;
        self.init().await
    }

    /// Initialize new database by creating table
    pub async fn init(&self) -> Result<(), sqlx::Error> {
        sqlx::query!(
            "CREATE TABLE IF NOT EXISTS assets (
                id SERIAL PRIMARY KEY,
                asset_class VARCHAR(20) NOT NULL
            )"
        )
        .execute(&self.pool)
        .await?;
        sqlx::query!(
            "CREATE TABLE IF NOT EXISTS currencies (
                    id INTEGER PRIMARY KEY,
                    iso_code CHAR(3) NOT NULL UNIQUE,
                    rounding_digits INT NOT NULL,
                    FOREIGN KEY(id) REFERENCES assets(id)
                )"
        )
        .execute(&self.pool)
        .await?;
        sqlx::query!(
            "CREATE TABLE IF NOT EXISTS stocks (
                  id INTEGER PRIMARY KEY,
                  name TEXT NOT NULL UNIQUE,
                  wkn CHAR(6) UNIQUE,
                  isin CHAR(12) UNIQUE,
                  note TEXT,
                  FOREIGN KEY(id) REFERENCES assets(id)
                )"
        )
        .execute(&self.pool)
        .await?;
        sqlx::query!(
            "CREATE TABLE IF NOT EXISTS transactions (
                id SERIAL PRIMARY KEY,
                trans_type TEXT NOT NULL,
                asset_id INTEGER,
                cash_amount FLOAT8 NOT NULL,
                cash_currency_id INT NOT NULL,
                cash_date DATE NOT NULL,
                related_trans INTEGER,
                position FLOAT8,
                note TEXT,
                FOREIGN KEY(asset_id) REFERENCES assets(id),
                FOREIGN KEY(cash_currency_id) REFERENCES currencies(id),
                FOREIGN KEY(related_trans) REFERENCES transactions(id)
            )"
        )
        .execute(&self.pool)
        .await?;
        sqlx::query!(
            "CREATE TABLE IF NOT EXISTS ticker (
                id SERIAL PRIMARY KEY,
                name TEXT NOT NULL,
                asset_id INTEGER NOT NULL,
                source TEXT NOT NULL,
                priority INTEGER NOT NULL,
                currency_id INT NOT NULL,
                factor FLOAT8 NOT NULL DEFAULT 1.0,
                tz TEXT,
                cal TEXT,
                FOREIGN KEY(asset_id) REFERENCES assets(id),
                FOREIGN KEY(currency_id) REFERENCES currencies(id)
            )"
        )
        .execute(&self.pool)
        .await?;
        sqlx::query!(
            "CREATE TABLE IF NOT EXISTS quotes (
                id SERIAL PRIMARY KEY,
                ticker_id INTEGER NOT NULL,
                price FLOAT8 NOT NULL,
                time TIMESTAMP WITH TIME ZONE NOT NULL,
                volume FLOAT8,
                FOREIGN KEY(ticker_id) REFERENCES ticker(id) 
            )"
        )
        .execute(&self.pool)
        .await?;

        sqlx::query!(
            "CREATE TABLE IF NOT EXISTS objects (
            id TEXT PRIMARY KEY,
            object JSON NOT NULL)"
        )
        .execute(&self.pool)
        .await?;

        Ok(())
    }
}