1// Copyright (C) 2019 Storj Labs, Inc. 2// See LICENSE for copying information. 3 4package testdata 5 6import "storj.io/storj/storagenode/storagenodedb" 7 8var v24 = MultiDBState{ 9 Version: 24, 10 DBStates: DBStates{ 11 storagenodedb.UsedSerialsDBName: &DBState{ 12 SQL: ` 13 -- table for keeping serials that need to be verified against 14 CREATE TABLE used_serial_ ( 15 satellite_id BLOB NOT NULL, 16 serial_number BLOB NOT NULL, 17 expiration TIMESTAMP NOT NULL 18 ); 19 -- primary key on satellite id and serial number 20 CREATE UNIQUE INDEX pk_used_serial_ ON used_serial_(satellite_id, serial_number); 21 -- expiration index to allow fast deletion 22 CREATE INDEX idx_used_serial_ ON used_serial_(expiration); 23 `, 24 }, 25 storagenodedb.StorageUsageDBName: &DBState{ 26 SQL: ` 27 CREATE TABLE storage_usage ( 28 satellite_id BLOB NOT NULL, 29 at_rest_total REAL NOT NUll, 30 interval_start TIMESTAMP NOT NULL, 31 PRIMARY KEY (satellite_id, interval_start) 32 ); 33 INSERT INTO storage_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',5.0,'2019-07-19 20:00:00+00:00'); 34 `, 35 }, 36 storagenodedb.ReputationDBName: &DBState{ 37 SQL: ` 38 -- tables to store nodestats cache 39 CREATE TABLE reputation ( 40 satellite_id BLOB NOT NULL, 41 uptime_success_count INTEGER NOT NULL, 42 uptime_total_count INTEGER NOT NULL, 43 uptime_reputation_alpha REAL NOT NULL, 44 uptime_reputation_beta REAL NOT NULL, 45 uptime_reputation_score REAL NOT NULL, 46 audit_success_count INTEGER NOT NULL, 47 audit_total_count INTEGER NOT NULL, 48 audit_reputation_alpha REAL NOT NULL, 49 audit_reputation_beta REAL NOT NULL, 50 audit_reputation_score REAL NOT NULL, 51 disqualified TIMESTAMP, 52 updated_at TIMESTAMP NOT NULL, 53 PRIMARY KEY (satellite_id) 54 ); 55 INSERT INTO reputation VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',1,1,1.0,1.0,1.0,1,1,1.0,1.0,1.0,'2019-07-19 20:00:00+00:00','2019-08-23 20:00:00+00:00'); 56 `, 57 }, 58 storagenodedb.PieceSpaceUsedDBName: &DBState{ 59 SQL: ` 60 CREATE TABLE piece_space_used ( 61 total INTEGER NOT NULL, 62 satellite_id BLOB 63 ); 64 CREATE UNIQUE INDEX idx_piece_space_used_satellite_id ON piece_space_used(satellite_id); 65 INSERT INTO piece_space_used (total) VALUES (1337); 66 INSERT INTO piece_space_used (total, satellite_id) VALUES (1337, X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000'); 67 `, 68 }, 69 storagenodedb.PieceInfoDBName: &DBState{ 70 SQL: ` 71 -- table for storing piece meta info 72 CREATE TABLE pieceinfo_ ( 73 satellite_id BLOB NOT NULL, 74 piece_id BLOB NOT NULL, 75 piece_size BIGINT NOT NULL, 76 piece_expiration TIMESTAMP, 77 order_limit BLOB NOT NULL, 78 uplink_piece_hash BLOB NOT NULL, 79 uplink_cert_id INTEGER NOT NULL, 80 deletion_failed_at TIMESTAMP, 81 piece_creation TIMESTAMP NOT NULL, 82 FOREIGN KEY(uplink_cert_id) REFERENCES certificate(cert_id) 83 ); 84 -- primary key by satellite id and piece id 85 CREATE UNIQUE INDEX pk_pieceinfo_ ON pieceinfo_(satellite_id, piece_id); 86 -- fast queries for expiration for pieces that have one 87 CREATE INDEX idx_pieceinfo__expiration ON pieceinfo_(piece_expiration) WHERE piece_expiration IS NOT NULL; 88 INSERT INTO pieceinfo_ VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',X'd5e757fd8d207d1c46583fb58330f803dc961b71147308ff75ff1e72a0df6b0b',1000,'2019-05-09 00:00:00.000000+00:00', X'', X'0a20d5e757fd8d207d1c46583fb58330f803dc961b71147308ff75ff1e72a0df6b0b120501020304051a47304502201c16d76ecd9b208f7ad9f1edf66ce73dce50da6bde6bbd7d278415099a727421022100ca730450e7f6506c2647516f6e20d0641e47c8270f58dde2bb07d1f5a3a45673',1,NULL,'epoch'); 89 INSERT INTO pieceinfo_ VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',X'd5e757fd8d207d1c46583fb58330f803dc961b71147308ff75ff1e72a0df6b0b',337,'2019-05-09 00:00:00.000000+00:00', X'', X'0a20d5e757fd8d207d1c46583fb58330f803dc961b71147308ff75ff1e72a0df6b0b120501020304051a483046022100e623cf4705046e2c04d5b42d5edbecb81f000459713ad460c691b3361817adbf022100993da2a5298bb88de6c35b2e54009d1bf306cda5d441c228aa9eaf981ceb0f3d',2,NULL,'epoch'); 90 `, 91 }, 92 storagenodedb.PieceExpirationDBName: &DBState{ 93 SQL: ` 94 -- table to hold expiration data (and only expirations. no other pieceinfo) 95 CREATE TABLE piece_expirations ( 96 satellite_id BLOB NOT NULL, 97 piece_id BLOB NOT NULL, 98 piece_expiration TIMESTAMP NOT NULL, -- date when it can be deleted 99 deletion_failed_at TIMESTAMP, 100 PRIMARY KEY ( satellite_id, piece_id ) 101 ); 102 CREATE INDEX idx_piece_expirations_piece_expiration ON piece_expirations(piece_expiration); 103 CREATE INDEX idx_piece_expirations_deletion_failed_at ON piece_expirations(deletion_failed_at); 104 `, 105 }, 106 storagenodedb.OrdersDBName: &DBState{ 107 SQL: ` 108 -- table for storing all unsent orders 109 CREATE TABLE unsent_order ( 110 satellite_id BLOB NOT NULL, 111 serial_number BLOB NOT NULL, 112 order_limit_serialized BLOB NOT NULL, 113 order_serialized BLOB NOT NULL, 114 order_limit_expiration TIMESTAMP NOT NULL, 115 uplink_cert_id INTEGER NOT NULL, 116 FOREIGN KEY(uplink_cert_id) REFERENCES certificate(cert_id) 117 ); 118 CREATE UNIQUE INDEX idx_orders ON unsent_order(satellite_id, serial_number); 119 -- table for storing all sent orders 120 CREATE TABLE order_archive_ ( 121 satellite_id BLOB NOT NULL, 122 serial_number BLOB NOT NULL, 123 order_limit_serialized BLOB NOT NULL, 124 order_serialized BLOB NOT NULL, 125 uplink_cert_id INTEGER NOT NULL, 126 status INTEGER NOT NULL, 127 archived_at TIMESTAMP NOT NULL, 128 FOREIGN KEY(uplink_cert_id) REFERENCES certificate(cert_id) 129 ); 130 INSERT INTO unsent_order VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',X'1eddef484b4c03f01332279032796972',X'0a101eddef484b4c03f0133227903279697212202b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf410001a201968996e7ef170a402fdfd88b6753df792c063c07c555905ffac9cd3cbd1c00022200ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac30002a20d00cf14f3c68b56321ace04902dec0484eb6f9098b22b31c6b3f82db249f191630643802420c08dfeb88e50510a8c1a5b9034a0c08dfeb88e50510a8c1a5b9035246304402204df59dc6f5d1bb7217105efbc9b3604d19189af37a81efbf16258e5d7db5549e02203bb4ead16e6e7f10f658558c22b59c3339911841e8dbaae6e2dea821f7326894',X'0a101eddef484b4c03f0133227903279697210321a47304502206d4c106ddec88140414bac5979c95bdea7de2e0ecc5be766e08f7d5ea36641a7022100e932ff858f15885ffa52d07e260c2c25d3861810ea6157956c1793ad0c906284','2019-04-01 16:01:35.9254586+00:00',1); 131 `, 132 }, 133 storagenodedb.BandwidthDBName: &DBState{ 134 SQL: ` 135 -- table for storing bandwidth usage 136 CREATE TABLE bandwidth_usage ( 137 satellite_id BLOB NOT NULL, 138 action INTEGER NOT NULL, 139 amount BIGINT NOT NULL, 140 created_at TIMESTAMP NOT NULL 141 ); 142 CREATE INDEX idx_bandwidth_usage_satellite ON bandwidth_usage(satellite_id); 143 CREATE INDEX idx_bandwidth_usage_created ON bandwidth_usage(created_at); 144 CREATE TABLE bandwidth_usage_rollups ( 145 interval_start TIMESTAMP NOT NULL, 146 satellite_id BLOB NOT NULL, 147 action INTEGER NOT NULL, 148 amount BIGINT NOT NULL, 149 PRIMARY KEY ( interval_start, satellite_id, action ) 150 ); 151 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',0,0,'2019-04-01 18:51:24.1074772+00:00'); 152 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',0,0,'2019-04-01 20:51:24.1074772+00:00'); 153 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',1,1,'2019-04-01 18:51:24.1074772+00:00'); 154 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',1,1,'2019-04-01 20:51:24.1074772+00:00'); 155 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',2,2,'2019-04-01 18:51:24.1074772+00:00'); 156 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',2,2,'2019-04-01 20:51:24.1074772+00:00'); 157 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',3,3,'2019-04-01 18:51:24.1074772+00:00'); 158 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',3,3,'2019-04-01 20:51:24.1074772+00:00'); 159 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',4,4,'2019-04-01 18:51:24.1074772+00:00'); 160 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',4,4,'2019-04-01 20:51:24.1074772+00:00'); 161 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',5,5,'2019-04-01 18:51:24.1074772+00:00'); 162 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',5,5,'2019-04-01 20:51:24.1074772+00:00'); 163 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',6,6,'2019-04-01 18:51:24.1074772+00:00'); 164 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',6,6,'2019-04-01 20:51:24.1074772+00:00'); 165 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',1,1,'2019-04-01 18:51:24.1074772+00:00'); 166 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',1,1,'2019-04-01 20:51:24.1074772+00:00'); 167 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',2,2,'2019-04-01 18:51:24.1074772+00:00'); 168 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',2,2,'2019-04-01 20:51:24.1074772+00:00'); 169 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',3,3,'2019-04-01 18:51:24.1074772+00:00'); 170 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',3,3,'2019-04-01 20:51:24.1074772+00:00'); 171 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',4,4,'2019-04-01 18:51:24.1074772+00:00'); 172 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',4,4,'2019-04-01 20:51:24.1074772+00:00'); 173 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',5,5,'2019-04-01 18:51:24.1074772+00:00'); 174 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',5,5,'2019-04-01 20:51:24.1074772+00:00'); 175 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',6,6,'2019-04-01 18:51:24.1074772+00:00'); 176 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',6,6,'2019-04-01 20:51:24.1074772+00:00'); 177 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',0,0); 178 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',0,0); 179 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',1,1); 180 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',1,1); 181 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',2,2); 182 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',2,2); 183 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',3,3); 184 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',3,3); 185 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',4,4); 186 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',4,4); 187 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',5,5); 188 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',5,5); 189 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',6,6); 190 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',6,6); 191 `, 192 }, 193 storagenodedb.SatellitesDBName: &DBState{ 194 SQL: ` 195 CREATE TABLE satellites ( 196 node_id BLOB NOT NULL, 197 address TEXT NOT NUll, 198 added_at TIMESTAMP NOT NULL, 199 status INTEGER NOT NULL, 200 PRIMARY KEY (node_id) 201 ); 202 203 CREATE TABLE satellite_exit_progress ( 204 satellite_id BLOB NOT NULL, 205 initiated_at TIMESTAMP, 206 finished_at TIMESTAMP, 207 starting_disk_usage INTEGER NOT NULL, 208 bytes_deleted INTEGER NOT NULL, 209 completion_receipt BLOB, 210 PRIMARY KEY (satellite_id) 211 ); 212 213 INSERT INTO satellites VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000','127.0.0.1:55516','2019-09-10 20:00:00+00:00', 0); 214 INSERT INTO satellite_exit_progress VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000','2019-09-10 20:00:00+00:00', null, 100, 0, null); 215 `, 216 }, 217 storagenodedb.DeprecatedInfoDBName: &DBState{ 218 SQL: `-- This is intentionally left blank`, 219 }, 220 }, 221} 222