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

//! Migration module for cardano transactions store
//!
use crate::database::SqlMigration;

/// Get all the migrations required by this version of the software.
/// There shall be one migration per database version. There could be several
/// statements per migration.
pub fn get_migrations() -> Vec<SqlMigration> {
    vec![
        // Migration 1
        // Add the `cardano_tx` table.
        SqlMigration::new(
            1,
            r#"
create table cardano_tx (
    transaction_hash        text      not null,
    block_number            integer   not null,
    immutable_file_number   integer   not null,
    primary key (transaction_hash)
);

create unique index cardano_tx_immutable_file_number_index on cardano_tx(immutable_file_number);
"#,
        ),
        // Migration 2
        // Fix the `cardano_tx` table index on immutable_file number, incorrectly marked as unique.
        SqlMigration::new(
            2,
            r#"
-- remove all data from the cardano tx table since a lot of transactions where missing for each
-- block and we rely on their insert order.
delete from cardano_tx;

drop index cardano_tx_immutable_file_number_index;
create index cardano_tx_immutable_file_number_index on cardano_tx(immutable_file_number);

vacuum;
"#,
        ),
        // Migration 3
        // Add `slot_number` and `block_hash` columns to `cardano_tx`.
        SqlMigration::new(
            3,
            r#"
-- remove all data from the cardano tx table since the new columns are mandatory
delete from cardano_tx;

alter table cardano_tx add column slot_number integer not null;
alter table cardano_tx add column block_hash text not null;

vacuum;
        "#,
        ),
        // Migration 4
        // Add index on `block_number` column of `cardano_tx` table
        SqlMigration::new(
            4,
            r#"
create index block_number_index on cardano_tx(block_number);
"#,
        ),
        // Migration 5
        // Add `block_range_root` table
        SqlMigration::new(
            5,
            r#"
create table block_range_root (
    start         integer   not null,
    end           integer   not null,
    merkle_root   text      not null,
    primary key (start, end)
);
"#,
        ),
        // Migration 6
        // Add composite index on `block_number/transaction_hash` column of `cardano_tx` table
        // Truncate `block_range_root` table after changing the order of retrieval of the transactions
        SqlMigration::new(
            6,
            r#"
create index block_number_transaction_hash_index on cardano_tx(block_number, transaction_hash);

-- remove all data from the block_range_root table since the order used to create them has changed
delete from block_range_root;

vacuum;
"#,
        ),
        // Migration 7
        // Enable full `auto_vacuum` on the database to prevent the database from growing
        // indefinitely since data is often deleted with chain rollbacks or, only on signers,
        // transactions pruning.
        SqlMigration::new(
            7,
            r#"
-- 'pragma auto_vacuum = full' can't be applied to an existing database, so we need to recreate
-- the database by using 'vacuum'.
pragma auto_vacuum = full;
vacuum;
"#,
        ),
        // Migration 8
        // Remove Immutable File Number in Cardano Transaction
        SqlMigration::new(
            8,
            r#"
drop index cardano_tx_immutable_file_number_index;
alter table cardano_tx drop column immutable_file_number;
 "#,
        ),
        // Migration 9
        // Truncate Cardano transaction related tables to avoid data inconsistency
        // with previous versions of the nodes.
        SqlMigration::new(
            9,
            r#"
delete from cardano_tx;
delete from block_range_root;
vacuum;
 "#,
        ),
    ]
}