· 8 years ago · Feb 07, 2018, 10:28 PM
1-- --------------------------------------------------------
2-- Host: 127.0.0.1
3-- Server version: 5.5.36 - MySQL Community Server (GPL)
4-- Server OS: Win64
5-- HeidiSQL Version: 8.3.0.4694
6-- --------------------------------------------------------
7
8/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
9/*!40101 SET NAMES utf8 */;
10/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
11/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
12
13-- Dumping database structure for steemw
14CREATE DATABASE IF NOT EXISTS `steemw` /*!40100 DEFAULT CHARACTER SET utf8 */;
15USE `steemw`;
16
17
18-- Dumping structure for table steemw.accounts
19CREATE TABLE IF NOT EXISTS `accounts` (
20 `name` varchar(50) NOT NULL,
21 `post_count` int(11) NOT NULL,
22 `balance` decimal(32,3) NOT NULL,
23 `sbd_balance` decimal(32,3) NOT NULL,
24 `vesting_shares` decimal(64,6) NOT NULL,
25 `posting_rewards` int(11) NOT NULL,
26 `curation_rewards` int(11) NOT NULL,
27 `reputation` bigint(20) DEFAULT NULL,
28 `vesting_withdraw_rate` decimal(32,6) DEFAULT NULL,
29 `next_vesting_withdrawal` datetime DEFAULT NULL,
30 `followers` int(11) NOT NULL DEFAULT '0',
31 `following` int(11) NOT NULL DEFAULT '0',
32 `last_active` datetime DEFAULT NULL,
33 `updatedOn` datetime NOT NULL,
34 `moreUpdatedOn` datetime DEFAULT NULL,
35 `json_metadata` text,
36 PRIMARY KEY (`name`)
37) ENGINE=InnoDB DEFAULT CHARSET=utf8;
38
39-- Data exporting was unselected.
40
41
42-- Dumping structure for procedure steemw.dailyhistory
43DELIMITER //
44CREATE DEFINER=`root`@`%` PROCEDURE `dailyhistory`()
45 COMMENT 'stores daily snapshots of the data'
46BEGIN
47 SET @real_price := (SELECT real_price FROM globals);
48 SET @steem_price_usd := (SELECT steem_price_usd FROM globals);
49 SET @sbd_price_usd := (SELECT sbd_price_usd FROM globals);
50 SET @total_vesting_shares := (SELECT total_vesting_shares FROM globals);
51 SET @total_vesting_fund_steem := (SELECT total_vesting_fund_steem FROM globals);
52
53 INSERT INTO history (date, name, post_count, balance, sbd_balance, posting_rewards, curation_rewards, reputation, steem_power, estimated_value, followers, following)
54 (SELECT CURDATE(), name, post_count, balance, sbd_balance, posting_rewards, curation_rewards, reputation,
55 @total_vesting_fund_steem*vesting_shares/@total_vesting_shares as steempower,
56 @steem_price_usd*(balance+@total_vesting_fund_steem*vesting_shares/@total_vesting_shares)+@sbd_price_usd*sbd_balance as estimated_value,
57 followers, following
58 FROM accounts);
59
60 INSERT INTO sumhistory (
61 SELECT date, SUM(post_count) as post_count, SUM(posting_rewards) as posting_rewards, SUM(curation_rewards) as curation_rewards,
62 SUM(balance) as balance, SUM(steem_power) as steem_power, SUM(sbd_balance) as sbd_balance, SUM(estimated_value) as estimated_value, SUM(reputation) as reputation,
63 SUM(followers) as follows
64 FROM history
65 WHERE date = CURDATE()
66 );
67END//
68DELIMITER ;
69
70
71-- Dumping structure for table steemw.globals
72CREATE TABLE IF NOT EXISTS `globals` (
73 `total_vesting_shares` decimal(64,6) DEFAULT NULL,
74 `total_vesting_fund_steem` decimal(32,3) DEFAULT NULL,
75 `real_price` decimal(32,5) DEFAULT NULL,
76 `accounts_tracked` int(11) DEFAULT NULL,
77 `steem_price_usd` decimal(32,5) DEFAULT NULL,
78 `sbd_price_usd` decimal(32,5) DEFAULT NULL
79) ENGINE=InnoDB DEFAULT CHARSET=utf8;
80
81-- Data exporting was unselected.
82
83
84-- Dumping structure for table steemw.history
85CREATE TABLE IF NOT EXISTS `history` (
86 `date` date NOT NULL,
87 `name` varchar(50) NOT NULL,
88 `post_count` int(11) NOT NULL,
89 `posting_rewards` int(11) NOT NULL,
90 `curation_rewards` int(11) NOT NULL,
91 `balance` decimal(32,3) NOT NULL,
92 `steem_power` decimal(32,3) NOT NULL,
93 `sbd_balance` decimal(32,3) NOT NULL,
94 `estimated_value` int(11) NOT NULL,
95 `reputation` bigint(20) NOT NULL DEFAULT '0',
96 `followers` int(11) NOT NULL DEFAULT '0',
97 `following` int(11) NOT NULL DEFAULT '0',
98 KEY `name` (`name`),
99 KEY `date` (`date`)
100) ENGINE=InnoDB DEFAULT CHARSET=utf8;
101
102-- Data exporting was unselected.
103
104
105-- Dumping structure for table steemw.historytest
106CREATE TABLE IF NOT EXISTS `historytest` (
107 `date` date NOT NULL,
108 `name` varchar(50) NOT NULL,
109 `post_count` int(11) NOT NULL,
110 `posting_rewards` int(11) NOT NULL,
111 `curation_rewards` int(11) NOT NULL,
112 `balance` decimal(32,3) NOT NULL,
113 `steem_power` decimal(32,3) NOT NULL,
114 `sbd_balance` decimal(32,3) NOT NULL,
115 `estimated_value` int(11) NOT NULL,
116 `reputation` bigint(20) NOT NULL DEFAULT '0',
117 `followers` int(11) NOT NULL DEFAULT '0',
118 `following` int(11) NOT NULL DEFAULT '0',
119 KEY `name` (`name`),
120 KEY `date` (`date`)
121) ENGINE=InnoDB DEFAULT CHARSET=utf8;
122
123-- Data exporting was unselected.
124
125
126-- Dumping structure for table steemw.search
127CREATE TABLE IF NOT EXISTS `search` (
128 `name` varchar(50) DEFAULT NULL
129) ENGINE=InnoDB DEFAULT CHARSET=utf8;
130
131-- Data exporting was unselected.
132
133
134-- Dumping structure for table steemw.sumhistory
135CREATE TABLE IF NOT EXISTS `sumhistory` (
136 `date` date NOT NULL,
137 `post_count` int(11) NOT NULL,
138 `posting_rewards` bigint(20) NOT NULL,
139 `curation_rewards` bigint(20) NOT NULL,
140 `balance` decimal(32,3) NOT NULL,
141 `steem_power` decimal(32,3) NOT NULL,
142 `sbd_balance` decimal(32,3) NOT NULL,
143 `estimated_value` int(11) NOT NULL,
144 `reputation` bigint(20) NOT NULL DEFAULT '0',
145 `follows` bigint(20) NOT NULL DEFAULT '0',
146 PRIMARY KEY (`date`)
147) ENGINE=InnoDB DEFAULT CHARSET=utf8;
148
149-- Data exporting was unselected.
150/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
151/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
152/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;