Hey Jay,
I could never get work.py to spit anything out into the miner_data database, tried multiple versions of p2pool (including freicoin version, and the build just before stratum was introduced) and multiple ways to write the SQL insert code. update_blocks / grab_data updates this table, but without difficulty/ontime data. Your new fork does this, however. I've fiddled with the code adding the SQL Insert to get it to populate miner_data in the old database to run the old front-end. I need a small amount of help to figure out where the code should be placed in database.py, I just put it everywhere an execute insert was located.
class P2PminingData:
def __init__(self):
try:
self.workDB = mysql.connector.connect(user=configure.db_username,password=configure.db_password,host=configure.db_location,database=configure.db_name)
self.autocommit = False
self.workDBcursor = self.workDB.cursor()
except mysql.connector.Error as err:
print(err)
self.bitcoin = bitcoin.Bitcoin(configure.args)
def add_shares(self,user,difficulty,on_time):
try:
[b] self.workDBcursor.execute("INSERT INTO miner_data (id,address,hashrate,timestamp,difficulty,ontime) VALUES (NULL, %s, %s, UNIX_TIMESTAMP(), %s, %s)",(user[:36],difficulty * on_time,difficulty,on_time))[/b]
self.workDBcursor.execute("""INSERT INTO live_shares (id,userid,shares) VALUES (NULL, %s , %s ) ON DUPLICATE KEY UPDATE shares=shares + %s""", (user[:36], difficulty * on_time, difficulty * on_time) )
self.workDB.commit()
except mysql.connector.Error as err:
self.workDB.rollback()
print(err)
def check_for_shift_completion(self):
try:
self.workDBcursor.execute("SELECT sum(shares) AS share_total FROM live_shares")
returned = self.workDBcursor.fetchone()
if (0 if returned[0] is None else int(returned[0])) > configure.shares_per_shift:
self.workDBcursor.execute("SELECT * FROM live_shares")
returned = self.workDBcursor.fetchall()
self.workDBcursor.execute("INSERT INTO shifts (id, timestamp, shiftpay, lastblockheight, confirmed) VALUES (NULL, UNIX_TIMESTAMP(), '0', '0', FALSE)")
shift_id = self.workDBcursor.lastrowid
for row in returned:
self.workDBcursor.execute("INSERT INTO shift_data (id,userid,shares,shiftid) VALUES (NULL, %s, %s, %s)", (row[1],row[2],shift_id))
self.workDBcursor.execute("UPDATE live_shares SET shares = '0'")
[b]self.workDBcursor.execute("INSERT INTO miner_data (id,address,hashrate,timestamp,difficulty,ontime) VALUES (NULL, %s, %s, UNIX_TIMESTAMP(), %s, %s)",(user[:36],difficulty * on_time,difficulty,on_time))[/b]
self.workDB.commit()
except mysql.connector.Error as err:
self.workDB.rollback()
print(err)
def record_p2pool_share(self,user,difficulty,on_time):
try:
[b]self.workDBcursor.execute("INSERT INTO miner_data (id,address,hashrate,timestamp,difficulty,ontime) VALUES (NULL, %s, %s, UNIX_TIMESTAMP(), %s, %s)",(user[:36],difficulty * on_time,difficulty,on_time))[/b]
self.workDBcursor.execute("INSERT INTO p2pool_shares (id,userid,share_hash,on_time,timestamp) VALUES (NULL, %s, %s, %s, UNIX_TIMESTAMP())",(user[:36],share_hash,on_time))
self.workDB.commit()
except mysql.connector.Error as err:
self.workDB.rollback()
print(err)
def record_block_from_miner(self,user,block_hash,on_time):
try:
[b]self.workDBcursor.execute("INSERT INTO miner_data (id,address,hashrate,timestamp,difficulty,ontime) VALUES (NULL, %s, %s, UNIX_TIMESTAMP(), %s, %s)",(user[:36],difficulty * on_time,difficulty,on_time))[/b]
self.workDBcursor.execute("INSERT INTO found_blocks (id,userid,block_hash,on_time,timestamp) VALUES (NULL, %s, %s, %s, UNIX_TIMESTAMP())",(user[:36],block_hash,on_time))
self.workDB.commit()
except mysql.connector.Error as err:
self.workDB.rollback()
print(err)
Also, I've modified the SQL database creation from what was posted earlier with a few fixes, using some settings from Jay's new database creation script. I think I've got all references to the database host / username / password / name referenced back to cred.php in the original. I'll post that when confirmed working.
CREATE TABLE `pool_blocks` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`type` varchar(64) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`confirmations` int(11) NOT NULL,
`blk_num` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `miner_data` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`address` varchar(48) NOT NULL,
`hashrate` varchar(64) NOT NULL,
`timestamp` int(11) NOT NULL,
`difficulty` bigint(20) NOT NULL,
`ontime` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `miner_hist` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`address` varchar(48) NOT NULL,
`hashrate` varchar(64) NOT NULL,
`timestamp` int(11) NOT NULL,
`difficulty` bigint(20) NOT NULL,
`ontime` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `pay_address` (
`address` varchar(48) NOT NULL,
`paddress` varchar(48) NOT NULL,
PRIMARY KEY (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `payouts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address` varchar(48) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`paddress` varchar(48) NOT NULL,
`paid` varchar(36) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `pool_data` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`hashrate` varchar(64) NOT NULL,
`shares` bigint(20) NOT NULL,
`stale_doa` bigint(20) NOT NULL,
`stale_orphan` bigint(20) NOT NULL,
`p2pool_hashrate` bigint(20) NOT NULL,
`p2pool_stale_rate` bigint(20) NOT NULL,
`block_value` decimal(16,8) NOT NULL,
`peers_out` int(11) NOT NULL,
`peers_in` int(11) NOT NULL,
`current_payout` decimal(16,8) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `signtime` (
`address` varchar(48) NOT NULL,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `lminer_data` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`address` varchar(48) NOT NULL,
`hashrate` varchar(64) NOT NULL,
`timestamp` int(11) NOT NULL,
`difficulty` bigint(20) NOT NULL,
`ontime` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `lminer_hist` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`address` varchar(48) NOT NULL,
`hashrate` varchar(64) NOT NULL,
`timestamp` int(11) NOT NULL,
`difficulty` bigint(20) NOT NULL,
`ontime` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `lpayouts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address` varchar(48) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`paddress` varchar(48) NOT NULL,
`paid` varchar(36) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `lpool_blocks` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`type` varchar(64) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`confirmations` int(11) NOT NULL,
`blk_num` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `lpool_data` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`hashrate` varchar(64) NOT NULL,
`shares` bigint(20) NOT NULL,
`stale_doa` bigint(20) NOT NULL,
`stale_orphan` bigint(20) NOT NULL,
`p2pool_hashrate` bigint(20) NOT NULL,
`p2pool_stale_rate` bigint(20) NOT NULL,
`block_value` decimal(16,8) NOT NULL,
`peers_out` int(11) NOT NULL,
`peers_in` int(11) NOT NULL,
`current_payout` decimal(16,8) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `namecoin_blocks` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`type` varchar(64) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`confirmations` int(11) NOT NULL,
`blk_num` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `namecoin_payouts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address` varchar(48) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`paddress` varchar(48) NOT NULL,
`paid` varchar(36) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `namecoin_reg` (
`BTC_address` varchar(36) NOT NULL,
`NMC_address` varchar(36) NOT NULL,
PRIMARY KEY (`BTC_address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `devcoin_blocks` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`type` varchar(64) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`confirmations` int(11) NOT NULL,
`blk_num` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `devcoin_payouts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address` varchar(48) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`paddress` varchar(48) NOT NULL,
`paid` varchar(36) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `devcoin_reg` (
`BTC_address` varchar(36) NOT NULL,
`NMC_address` varchar(36) NOT NULL,
PRIMARY KEY (`BTC_address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `i0coin_blocks` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`type` varchar(64) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`confirmations` int(11) NOT NULL,
`blk_num` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `i0coin_payouts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address` varchar(48) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`paddress` varchar(48) NOT NULL,
`paid` varchar(36) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `i0coin_reg` (
`BTC_address` varchar(36) NOT NULL,
`NMC_address` varchar(36) NOT NULL,
PRIMARY KEY (`BTC_address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `ixcoin_blocks` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`type` varchar(64) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`confirmations` int(11) NOT NULL,
`blk_num` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `ixcoin_payouts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address` varchar(48) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`paddress` varchar(48) NOT NULL,
`paid` varchar(36) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `ixcoin_reg` (
`BTC_address` varchar(36) NOT NULL,
`NMC_address` varchar(36) NOT NULL,
PRIMARY KEY (`BTC_address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `coiledcoin_blocks` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`type` varchar(64) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`confirmations` int(11) NOT NULL,
`blk_num` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `coiledcoin_payouts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address` varchar(48) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`paddress` varchar(48) NOT NULL,
`paid` varchar(36) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `coiledcoin_reg` (
`BTC_address` varchar(36) NOT NULL,
`NMC_address` varchar(36) NOT NULL,
PRIMARY KEY (`BTC_address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `groupcoin_blocks` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`type` varchar(64) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`confirmations` int(11) NOT NULL,
`blk_num` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `groupcoin_payouts` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`timestamp` int(11) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`address` varchar(48) NOT NULL,
`amount` decimal(16,8) NOT NULL,
`txid` varchar(64) NOT NULL DEFAULT 'NONE',
`paddress` varchar(48) NOT NULL,
`paid` varchar(36) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE `groupcoin_reg` (
`BTC_address` varchar(36) NOT NULL,
`NMC_address` varchar(36) NOT NULL,
PRIMARY KEY (`BTC_address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Thanks for the help!