SteemWhales db

schema.sql

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               5.5.36 - MySQL Community Server (GPL)
-- Server OS:                    Win64
-- HeidiSQL Version:             8.3.0.4694
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for steemw
CREATE DATABASE IF NOT EXISTS `steemw` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `steemw`;


-- Dumping structure for table steemw.accounts
CREATE TABLE IF NOT EXISTS `accounts` (
  `name` varchar(50) NOT NULL,
  `post_count` int(11) NOT NULL,
  `balance` decimal(32,3) NOT NULL,
  `sbd_balance` decimal(32,3) NOT NULL,
  `vesting_shares` decimal(64,6) NOT NULL,
  `posting_rewards` int(11) NOT NULL,
  `curation_rewards` int(11) NOT NULL,
  `reputation` bigint(20) DEFAULT NULL,
  `vesting_withdraw_rate` decimal(32,6) DEFAULT NULL,
  `next_vesting_withdrawal` datetime DEFAULT NULL,
  `followers` int(11) NOT NULL DEFAULT '0',
  `following` int(11) NOT NULL DEFAULT '0',
  `last_active` datetime DEFAULT NULL,
  `updatedOn` datetime NOT NULL,
  `moreUpdatedOn` datetime DEFAULT NULL,
  `json_metadata` text,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.


-- Dumping structure for procedure steemw.dailyhistory
DELIMITER //
CREATE DEFINER=`root`@`%` PROCEDURE `dailyhistory`()
    COMMENT 'stores daily snapshots of the data'
BEGIN
	SET @real_price := (SELECT real_price FROM globals);
	SET @steem_price_usd := (SELECT steem_price_usd FROM globals);
	SET @sbd_price_usd := (SELECT sbd_price_usd FROM globals);
	SET @total_vesting_shares := (SELECT total_vesting_shares FROM globals);
	SET @total_vesting_fund_steem := (SELECT total_vesting_fund_steem FROM globals);

	INSERT INTO history (date, name, post_count, balance, sbd_balance, posting_rewards, curation_rewards, reputation, steem_power, estimated_value, followers, following)
		(SELECT CURDATE(), name, post_count, balance, sbd_balance, posting_rewards, curation_rewards, reputation, 
		@total_vesting_fund_steem*vesting_shares/@total_vesting_shares as steempower,
		@steem_price_usd*(balance+@total_vesting_fund_steem*vesting_shares/@total_vesting_shares)+@sbd_price_usd*sbd_balance as estimated_value,
		followers, following
		FROM accounts);
	
	INSERT INTO sumhistory (
		SELECT date, SUM(post_count) as post_count, SUM(posting_rewards) as posting_rewards, SUM(curation_rewards) as curation_rewards, 
		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,
		SUM(followers) as follows
		FROM history
		WHERE date = CURDATE()
	);
END//
DELIMITER ;


-- Dumping structure for table steemw.globals
CREATE TABLE IF NOT EXISTS `globals` (
  `total_vesting_shares` decimal(64,6) DEFAULT NULL,
  `total_vesting_fund_steem` decimal(32,3) DEFAULT NULL,
  `real_price` decimal(32,5) DEFAULT NULL,
  `accounts_tracked` int(11) DEFAULT NULL,
  `steem_price_usd` decimal(32,5) DEFAULT NULL,
  `sbd_price_usd` decimal(32,5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.


-- Dumping structure for table steemw.history
CREATE TABLE IF NOT EXISTS `history` (
  `date` date NOT NULL,
  `name` varchar(50) NOT NULL,
  `post_count` int(11) NOT NULL,
  `posting_rewards` int(11) NOT NULL,
  `curation_rewards` int(11) NOT NULL,
  `balance` decimal(32,3) NOT NULL,
  `steem_power` decimal(32,3) NOT NULL,
  `sbd_balance` decimal(32,3) NOT NULL,
  `estimated_value` int(11) NOT NULL,
  `reputation` bigint(20) NOT NULL DEFAULT '0',
  `followers` int(11) NOT NULL DEFAULT '0',
  `following` int(11) NOT NULL DEFAULT '0',
  KEY `name` (`name`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.


-- Dumping structure for table steemw.historytest
CREATE TABLE IF NOT EXISTS `historytest` (
  `date` date NOT NULL,
  `name` varchar(50) NOT NULL,
  `post_count` int(11) NOT NULL,
  `posting_rewards` int(11) NOT NULL,
  `curation_rewards` int(11) NOT NULL,
  `balance` decimal(32,3) NOT NULL,
  `steem_power` decimal(32,3) NOT NULL,
  `sbd_balance` decimal(32,3) NOT NULL,
  `estimated_value` int(11) NOT NULL,
  `reputation` bigint(20) NOT NULL DEFAULT '0',
  `followers` int(11) NOT NULL DEFAULT '0',
  `following` int(11) NOT NULL DEFAULT '0',
  KEY `name` (`name`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.


-- Dumping structure for table steemw.search
CREATE TABLE IF NOT EXISTS `search` (
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.


-- Dumping structure for table steemw.sumhistory
CREATE TABLE IF NOT EXISTS `sumhistory` (
  `date` date NOT NULL,
  `post_count` int(11) NOT NULL,
  `posting_rewards` bigint(20) NOT NULL,
  `curation_rewards` bigint(20) NOT NULL,
  `balance` decimal(32,3) NOT NULL,
  `steem_power` decimal(32,3) NOT NULL,
  `sbd_balance` decimal(32,3) NOT NULL,
  `estimated_value` int(11) NOT NULL,
  `reputation` bigint(20) NOT NULL DEFAULT '0',
  `follows` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;