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 v18 = MultiDBState{ 9 Version: 18, 10 DBStates: DBStates{ 11 storagenodedb.DeprecatedInfoDBName: &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 -- certificate table for storing uplink/satellite certificates 25 CREATE TABLE certificate ( 26 cert_id INTEGER 27 ); 28 29 -- table for storing piece meta info 30 CREATE TABLE pieceinfo_ ( 31 satellite_id BLOB NOT NULL, 32 piece_id BLOB NOT NULL, 33 piece_size BIGINT NOT NULL, 34 piece_expiration TIMESTAMP, 35 36 order_limit BLOB NOT NULL, 37 uplink_piece_hash BLOB NOT NULL, 38 uplink_cert_id INTEGER NOT NULL, 39 40 deletion_failed_at TIMESTAMP, 41 piece_creation TIMESTAMP NOT NULL, 42 43 FOREIGN KEY(uplink_cert_id) REFERENCES certificate(cert_id) 44 ); 45 -- primary key by satellite id and piece id 46 CREATE UNIQUE INDEX pk_pieceinfo_ ON pieceinfo_(satellite_id, piece_id); 47 -- fast queries for expiration for pieces that have one 48 CREATE INDEX idx_pieceinfo__expiration ON pieceinfo_(piece_expiration) WHERE piece_expiration IS NOT NULL; 49 50 -- table for storing bandwidth usage 51 CREATE TABLE bandwidth_usage ( 52 satellite_id BLOB NOT NULL, 53 action INTEGER NOT NULL, 54 amount BIGINT NOT NULL, 55 created_at TIMESTAMP NOT NULL 56 ); 57 CREATE INDEX idx_bandwidth_usage_satellite ON bandwidth_usage(satellite_id); 58 CREATE INDEX idx_bandwidth_usage_created ON bandwidth_usage(created_at); 59 60 -- table for storing all unsent orders 61 CREATE TABLE unsent_order ( 62 satellite_id BLOB NOT NULL, 63 serial_number BLOB NOT NULL, 64 65 order_limit_serialized BLOB NOT NULL, 66 order_serialized BLOB NOT NULL, 67 order_limit_expiration TIMESTAMP NOT NULL, 68 69 uplink_cert_id INTEGER NOT NULL, 70 71 FOREIGN KEY(uplink_cert_id) REFERENCES certificate(cert_id) 72 ); 73 CREATE UNIQUE INDEX idx_orders ON unsent_order(satellite_id, serial_number); 74 75 -- table for storing all sent orders 76 CREATE TABLE order_archive_ ( 77 satellite_id BLOB NOT NULL, 78 serial_number BLOB NOT NULL, 79 80 order_limit_serialized BLOB NOT NULL, 81 order_serialized BLOB NOT NULL, 82 83 uplink_cert_id INTEGER NOT NULL, 84 85 status INTEGER NOT NULL, 86 archived_at TIMESTAMP NOT NULL, 87 88 FOREIGN KEY(uplink_cert_id) REFERENCES certificate(cert_id) 89 ); 90 91 CREATE TABLE bandwidth_usage_rollups ( 92 interval_start TIMESTAMP NOT NULL, 93 satellite_id BLOB NOT NULL, 94 action INTEGER NOT NULL, 95 amount BIGINT NOT NULL, 96 PRIMARY KEY ( interval_start, satellite_id, action ) 97 ); 98 99 -- table to hold expiration data (and only expirations. no other pieceinfo) 100 CREATE TABLE piece_expirations ( 101 satellite_id BLOB NOT NULL, 102 piece_id BLOB NOT NULL, 103 piece_expiration TIMESTAMP NOT NULL, -- date when it can be deleted 104 deletion_failed_at TIMESTAMP, 105 PRIMARY KEY ( satellite_id, piece_id ) 106 ); 107 CREATE INDEX idx_piece_expirations_piece_expiration ON piece_expirations(piece_expiration); 108 CREATE INDEX idx_piece_expirations_deletion_failed_at ON piece_expirations(deletion_failed_at); 109 110 -- tables to store nodestats cache 111 CREATE TABLE reputation ( 112 satellite_id BLOB NOT NULL, 113 uptime_success_count INTEGER NOT NULL, 114 uptime_total_count INTEGER NOT NULL, 115 uptime_reputation_alpha REAL NOT NULL, 116 uptime_reputation_beta REAL NOT NULL, 117 uptime_reputation_score REAL NOT NULL, 118 audit_success_count INTEGER NOT NULL, 119 audit_total_count INTEGER NOT NULL, 120 audit_reputation_alpha REAL NOT NULL, 121 audit_reputation_beta REAL NOT NULL, 122 audit_reputation_score REAL NOT NULL, 123 updated_at TIMESTAMP NOT NULL, 124 PRIMARY KEY (satellite_id) 125 ); 126 127 CREATE TABLE storage_usage ( 128 satellite_id BLOB NOT NULL, 129 at_rest_total REAL NOT NUll, 130 timestamp TIMESTAMP NOT NULL, 131 PRIMARY KEY (satellite_id, timestamp) 132 ); 133 134 CREATE TABLE piece_space_used ( 135 total INTEGER NOT NULL, 136 satellite_id BLOB 137 ); 138 CREATE UNIQUE INDEX idx_piece_space_used_satellite_id ON piece_space_used(satellite_id); 139 140 INSERT INTO unsent_order VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',X'1eddef484b4c03f01332279032796972',X'0a101eddef484b4c03f0133227903279697212202b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf410001a201968996e7ef170a402fdfd88b6753df792c063c07c555905ffac9cd3cbd1c00022200ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac30002a20d00cf14f3c68b56321ace04902dec0484eb6f9098b22b31c6b3f82db249f191630643802420c08dfeb88e50510a8c1a5b9034a0c08dfeb88e50510a8c1a5b9035246304402204df59dc6f5d1bb7217105efbc9b3604d19189af37a81efbf16258e5d7db5549e02203bb4ead16e6e7f10f658558c22b59c3339911841e8dbaae6e2dea821f7326894',X'0a101eddef484b4c03f0133227903279697210321a47304502206d4c106ddec88140414bac5979c95bdea7de2e0ecc5be766e08f7d5ea36641a7022100e932ff858f15885ffa52d07e260c2c25d3861810ea6157956c1793ad0c906284','2019-04-01 16:01:35.9254586+00:00',1); 141 142 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',0,0,'2019-04-01 18:51:24.1074772+00:00'); 143 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',0,0,'2019-04-01 20:51:24.1074772+00:00'); 144 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',1,1,'2019-04-01 18:51:24.1074772+00:00'); 145 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',1,1,'2019-04-01 20:51:24.1074772+00:00'); 146 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',2,2,'2019-04-01 18:51:24.1074772+00:00'); 147 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',2,2,'2019-04-01 20:51:24.1074772+00:00'); 148 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',3,3,'2019-04-01 18:51:24.1074772+00:00'); 149 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',3,3,'2019-04-01 20:51:24.1074772+00:00'); 150 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',4,4,'2019-04-01 18:51:24.1074772+00:00'); 151 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',4,4,'2019-04-01 20:51:24.1074772+00:00'); 152 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',5,5,'2019-04-01 18:51:24.1074772+00:00'); 153 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',5,5,'2019-04-01 20:51:24.1074772+00:00'); 154 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',6,6,'2019-04-01 18:51:24.1074772+00:00'); 155 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',6,6,'2019-04-01 20:51:24.1074772+00:00'); 156 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',1,1,'2019-04-01 18:51:24.1074772+00:00'); 157 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',1,1,'2019-04-01 20:51:24.1074772+00:00'); 158 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',2,2,'2019-04-01 18:51:24.1074772+00:00'); 159 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',2,2,'2019-04-01 20:51:24.1074772+00:00'); 160 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',3,3,'2019-04-01 18:51:24.1074772+00:00'); 161 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',3,3,'2019-04-01 20:51:24.1074772+00:00'); 162 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',4,4,'2019-04-01 18:51:24.1074772+00:00'); 163 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',4,4,'2019-04-01 20:51:24.1074772+00:00'); 164 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',5,5,'2019-04-01 18:51:24.1074772+00:00'); 165 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',5,5,'2019-04-01 20:51:24.1074772+00:00'); 166 INSERT INTO bandwidth_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',6,6,'2019-04-01 18:51:24.1074772+00:00'); 167 INSERT INTO bandwidth_usage VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',6,6,'2019-04-01 20:51:24.1074772+00:00'); 168 169 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',0,0); 170 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',0,0); 171 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',1,1); 172 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',1,1); 173 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',2,2); 174 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',2,2); 175 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',3,3); 176 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',3,3); 177 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',4,4); 178 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',4,4); 179 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',5,5); 180 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',5,5); 181 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 18:00:00+00:00',X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',6,6); 182 INSERT INTO bandwidth_usage_rollups VALUES('2019-07-12 20:00:00+00:00',X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',6,6); 183 184 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'); 185 186 INSERT INTO storage_usage VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',5.0,'2019-07-19 20:00:00+00:00'); 187 188 INSERT INTO pieceinfo_ VALUES(X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000',X'd5e757fd8d207d1c46583fb58330f803dc961b71147308ff75ff1e72a0df6b0b',1000,'2019-05-09 00:00:00.000000+00:00', X'', X'0a20d5e757fd8d207d1c46583fb58330f803dc961b71147308ff75ff1e72a0df6b0b120501020304051a47304502201c16d76ecd9b208f7ad9f1edf66ce73dce50da6bde6bbd7d278415099a727421022100ca730450e7f6506c2647516f6e20d0641e47c8270f58dde2bb07d1f5a3a45673',1,NULL,'epoch'); 189 INSERT INTO pieceinfo_ VALUES(X'2b3a5863a41f25408a8f5348839d7a1361dbd886d75786bb139a8ca0bdf41000',X'd5e757fd8d207d1c46583fb58330f803dc961b71147308ff75ff1e72a0df6b0b',337,'2019-05-09 00:00:00.000000+00:00', X'', X'0a20d5e757fd8d207d1c46583fb58330f803dc961b71147308ff75ff1e72a0df6b0b120501020304051a483046022100e623cf4705046e2c04d5b42d5edbecb81f000459713ad460c691b3361817adbf022100993da2a5298bb88de6c35b2e54009d1bf306cda5d441c228aa9eaf981ceb0f3d',2,NULL,'epoch'); 190 191 INSERT INTO piece_space_used (total) VALUES (1337); 192 193 INSERT INTO piece_space_used (total, satellite_id) VALUES (1337, X'0ed28abb2813e184a1e98b0f6605c4911ea468c7e8433eb583e0fca7ceac3000'); 194 `, 195 }, 196 }, 197} 198