/ db-scripts / 1_db.sql.tpl
1_db.sql.tpl
1 # Database tables 2 3 # Copyright © 2019 – Katana Cryptographic Ltd. All Rights Reserved. 4 5 6 # Naming conventions 7 # 1. Table names are lowercase plural 8 # 2. Join table names are snake_case plural 9 # 3. Column names have a table prefix 10 # 4. Foreign key names match primary key of foreign table 11 12 13 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 14 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 15 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 16 /*!40101 SET NAMES utf8 */; 17 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; 18 /*!40103 SET TIME_ZONE='+00:00' */; 19 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 20 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 21 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 22 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 23 24 -- 25 -- Table structure for table `addresses` 26 -- 27 28 /*!40101 SET @saved_cs_client = @@character_set_client */; 29 /*!40101 SET character_set_client = utf8 */; 30 CREATE TABLE IF NOT EXISTS `addresses` ( 31 `addrID` int(10) unsigned NOT NULL AUTO_INCREMENT, 32 `addrAddress` varchar(74) DEFAULT NULL, 33 PRIMARY KEY (`addrID`), 34 UNIQUE KEY `addrAddress` (`addrAddress`) 35 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; 36 /*!40101 SET character_set_client = @saved_cs_client */; 37 38 -- 39 -- Table structure for table `banned_addresses` 40 -- 41 42 /*!40101 SET @saved_cs_client = @@character_set_client */; 43 /*!40101 SET character_set_client = utf8 */; 44 CREATE TABLE IF NOT EXISTS `banned_addresses` ( 45 `bannedAddressId` int(11) NOT NULL AUTO_INCREMENT, 46 `addrAddress` varchar(74) NOT NULL, 47 PRIMARY KEY (`bannedAddressId`), 48 UNIQUE KEY `banned_addresses_addresses` (`addrAddress`) 49 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; 50 /*!40101 SET character_set_client = @saved_cs_client */; 51 52 -- 53 -- Table structure for table `blocks` 54 -- 55 56 /*!40101 SET @saved_cs_client = @@character_set_client */; 57 /*!40101 SET character_set_client = utf8 */; 58 CREATE TABLE IF NOT EXISTS `blocks` ( 59 `blockID` int(10) unsigned NOT NULL AUTO_INCREMENT, 60 `blockHash` char(64) NOT NULL DEFAULT '', 61 `blockParent` int(10) unsigned DEFAULT NULL, 62 `blockHeight` int(10) unsigned NOT NULL DEFAULT '0', 63 `blockTime` int(10) unsigned NOT NULL DEFAULT '0', 64 PRIMARY KEY (`blockID`), 65 UNIQUE KEY `blockHash` (`blockHash`), 66 KEY `blockParent` (`blockParent`), 67 KEY `blockHeight` (`blockHeight`), 68 CONSTRAINT `blocks_ibfk_1` FOREIGN KEY (`blockParent`) REFERENCES `blocks` (`blockID`) ON DELETE SET NULL ON UPDATE NO ACTION 69 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; 70 /*!40101 SET character_set_client = @saved_cs_client */; 71 72 -- 73 -- Table structure for table `hd` 74 -- 75 76 /*!40101 SET @saved_cs_client = @@character_set_client */; 77 /*!40101 SET character_set_client = utf8 */; 78 CREATE TABLE IF NOT EXISTS `hd` ( 79 `hdID` int(10) unsigned NOT NULL AUTO_INCREMENT, 80 `hdXpub` char(112) DEFAULT NULL, 81 `hdCreated` int(10) unsigned NOT NULL DEFAULT '0', 82 `hdType` smallint(5) unsigned NOT NULL DEFAULT '0', 83 PRIMARY KEY (`hdID`), 84 UNIQUE KEY `hdXpub` (`hdXpub`), 85 KEY `hdCreated` (`hdCreated`) 86 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; 87 /*!40101 SET character_set_client = @saved_cs_client */; 88 89 -- 90 -- Table structure for table `hd_addresses` 91 -- 92 93 /*!40101 SET @saved_cs_client = @@character_set_client */; 94 /*!40101 SET character_set_client = utf8 */; 95 CREATE TABLE IF NOT EXISTS `hd_addresses` ( 96 `hdAddrID` int(10) unsigned NOT NULL AUTO_INCREMENT, 97 `hdID` int(10) unsigned NOT NULL DEFAULT '0', 98 `addrID` int(10) unsigned NOT NULL DEFAULT '0', 99 `hdAddrChain` smallint(5) unsigned NOT NULL DEFAULT '0', 100 `hdAddrIndex` int(10) unsigned NOT NULL DEFAULT '0', 101 PRIMARY KEY (`hdAddrID`), 102 UNIQUE KEY `hdID_2` (`hdID`,`addrID`), 103 KEY `hdID` (`hdID`), 104 KEY `addrID` (`addrID`), 105 CONSTRAINT `hd_addresses_ibfk_1` FOREIGN KEY (`hdID`) REFERENCES `hd` (`hdID`) ON DELETE CASCADE ON UPDATE CASCADE, 106 CONSTRAINT `hd_addresses_ibfk_2` FOREIGN KEY (`addrID`) REFERENCES `addresses` (`addrID`) ON DELETE CASCADE ON UPDATE CASCADE 107 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; 108 /*!40101 SET character_set_client = @saved_cs_client */; 109 110 -- 111 -- Table structure for table `inputs` 112 -- 113 114 /*!40101 SET @saved_cs_client = @@character_set_client */; 115 /*!40101 SET character_set_client = utf8 */; 116 CREATE TABLE IF NOT EXISTS `inputs` ( 117 `inID` int(10) unsigned NOT NULL AUTO_INCREMENT, 118 `outID` int(10) unsigned NOT NULL DEFAULT '0', 119 `txnID` int(10) unsigned NOT NULL DEFAULT '0', 120 `inIndex` int(10) unsigned NOT NULL DEFAULT '0', 121 `inSequence` int(10) unsigned NOT NULL DEFAULT '0', 122 PRIMARY KEY (`inID`), 123 UNIQUE KEY `txnID_2` (`txnID`,`inIndex`), 124 KEY `outID` (`outID`), 125 KEY `txnID` (`txnID`), 126 CONSTRAINT `inputs_ibfk_1` FOREIGN KEY (`txnID`) REFERENCES `transactions` (`txnID`) ON DELETE CASCADE ON UPDATE CASCADE, 127 CONSTRAINT `inputs_ibfk_2` FOREIGN KEY (`outID`) REFERENCES `outputs` (`outID`) ON DELETE CASCADE ON UPDATE CASCADE 128 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; 129 /*!40101 SET character_set_client = @saved_cs_client */; 130 131 -- 132 -- Table structure for table `outputs` 133 -- 134 135 /*!40101 SET @saved_cs_client = @@character_set_client */; 136 /*!40101 SET character_set_client = utf8 */; 137 CREATE TABLE IF NOT EXISTS `outputs` ( 138 `outID` int(10) unsigned NOT NULL AUTO_INCREMENT, 139 `txnID` int(10) unsigned NOT NULL DEFAULT '0', 140 `addrID` int(10) unsigned NOT NULL DEFAULT '0', 141 `outIndex` int(10) unsigned NOT NULL DEFAULT '0', 142 `outAmount` bigint(20) unsigned NOT NULL DEFAULT '0', 143 `outScript` varchar(20000) NOT NULL DEFAULT '', 144 PRIMARY KEY (`outID`), 145 UNIQUE KEY `txnID_2` (`txnID`,`addrID`,`outIndex`), 146 KEY `txnID` (`txnID`), 147 KEY `addrID` (`addrID`), 148 CONSTRAINT `outputs_ibfk_1` FOREIGN KEY (`txnID`) REFERENCES `transactions` (`txnID`) ON DELETE CASCADE ON UPDATE CASCADE, 149 CONSTRAINT `outputs_ibfk_2` FOREIGN KEY (`addrID`) REFERENCES `addresses` (`addrID`) ON DELETE CASCADE ON UPDATE CASCADE 150 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; 151 /*!40101 SET character_set_client = @saved_cs_client */; 152 153 -- 154 -- Table structure for table `transactions` 155 -- 156 157 /*!40101 SET @saved_cs_client = @@character_set_client */; 158 /*!40101 SET character_set_client = utf8 */; 159 CREATE TABLE IF NOT EXISTS `transactions` ( 160 `txnID` int(10) unsigned NOT NULL AUTO_INCREMENT, 161 `txnTxid` char(64) DEFAULT NULL, 162 `txnCreated` int(10) unsigned NOT NULL DEFAULT '0', 163 `txnVersion` int(10) unsigned NOT NULL DEFAULT '0', 164 `txnLocktime` int(10) unsigned NOT NULL DEFAULT '0', 165 `blockID` int(10) unsigned DEFAULT NULL, 166 PRIMARY KEY (`txnID`), 167 UNIQUE KEY `txnTxid` (`txnTxid`), 168 KEY `txnCreated` (`txnCreated`), 169 KEY `blockID` (`blockID`), 170 CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`blockID`) REFERENCES `blocks` (`blockID`) ON DELETE SET NULL ON UPDATE NO ACTION 171 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; 172 /*!40101 SET character_set_client = @saved_cs_client */; 173 174 -- 175 -- Table structure for table `scheduled_transactions` 176 -- 177 178 /*!40101 SET @saved_cs_client = @@character_set_client */; 179 /*!40101 SET character_set_client = utf8 */; 180 CREATE TABLE IF NOT EXISTS `scheduled_transactions` ( 181 `schID` int(10) unsigned NOT NULL AUTO_INCREMENT, 182 `schTxid` char(64) NOT NULL DEFAULT '', 183 `schCreated` int(10) unsigned NOT NULL DEFAULT '0', 184 `schRaw` varchar(50000) NOT NULL DEFAULT '', 185 `schParentID` int(10) unsigned DEFAULT NULL, 186 `schParentTxid` char(64) DEFAULT '', 187 `schDelay` int(10) unsigned NOT NULL DEFAULT '0', 188 `schTrigger` int(10) unsigned NOT NULL DEFAULT '0', 189 PRIMARY KEY (`schID`), 190 UNIQUE KEY `schTxid` (`schTxid`), 191 KEY `schParentID` (`schParentID`), 192 CONSTRAINT `scheduled_transactions_ibfk_1` FOREIGN KEY (`schParentID`) REFERENCES `scheduled_transactions` (`schID`) ON DELETE SET NULL ON UPDATE NO ACTION 193 ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1; 194 /*!40101 SET character_set_client = @saved_cs_client */; 195 196 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; 197 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 198 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 199 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 200 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 201 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; 202 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; 203 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;