Class: Bitbot::Database
- Inherits:
-
Object
- Object
- Bitbot::Database
- Defined in:
- lib/bitbot/database.rb
Instance Method Summary collapse
-
#create_transaction_from_deposit(user_id, amount, transaction_id) ⇒ Object
Adds a transaction with a deposit.
- #create_transaction_from_tip(from_user_id, to_user_id, amount, message) ⇒ Object
-
#create_transaction_from_withdrawal(user_id, amount, fee, address) ⇒ Object
Adds a transaction for a withdrawal.
- #get_balance_for_user_id(user_id) ⇒ Object
-
#get_incoming_transaction_ids ⇒ Object
Returns an array of all the Bitcoin transaction ids for deposits.
- #get_or_create_user_id_for_username(username) ⇒ Object
- #get_tipping_stats(from = nil) ⇒ Object
- #get_transactions_for_user_id(user_id) ⇒ Object
- #get_username_for_user_id(user_id) ⇒ Object
-
#initialize(path) ⇒ Database
constructor
A new instance of Database.
- #upgrade_schema ⇒ Object
Constructor Details
#initialize(path) ⇒ Database
Returns a new instance of Database.
7 8 9 10 |
# File 'lib/bitbot/database.rb', line 7 def initialize(path) @db = SQLite3::Database.new path @db.execute('PRAGMA foreign_keys = ON') end |
Instance Method Details
#create_transaction_from_deposit(user_id, amount, transaction_id) ⇒ Object
Adds a transaction with a deposit. Returns true if the row was added, and false if the insert failed for some reason (like if the transaction_id already exists).
102 103 104 105 106 |
# File 'lib/bitbot/database.rb', line 102 def create_transaction_from_deposit(user_id, amount, transaction_id) @db.execute("insert into transactions (created_at, amount, incoming_transaction, user_id) values (?, ?, ?, ?)", [ Time.now.to_i, amount, transaction_id, user_id ]) return @db.changes == 1 end |
#create_transaction_from_tip(from_user_id, to_user_id, amount, message) ⇒ Object
124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 |
# File 'lib/bitbot/database.rb', line 124 def create_transaction_from_tip(from_user_id, to_user_id, amount, ) @db.transaction(:exclusive) do # verify current balance current_balance = self.get_balance_for_user_id(from_user_id) if current_balance < amount raise InsufficientFundsError, "Insufficient funds; Current balance: #{current_balance} for amount #{amount}" end now = Time.now.to_i @db.execute("insert into transactions (created_at, user_id, amount, note, other_user_id) values (?, ?, ?, ?, ?)", [ now, from_user_id, (0 - amount), , to_user_id ]) @db.execute("insert into transactions (created_at, user_id, amount, note, other_user_id) values (?, ?, ?, ?, ?)", [ now, to_user_id, amount, , from_user_id ]) end true end |
#create_transaction_from_withdrawal(user_id, amount, fee, address) ⇒ Object
Adds a transaction for a withdrawal.
109 110 111 112 113 114 115 116 117 118 119 120 121 122 |
# File 'lib/bitbot/database.rb', line 109 def create_transaction_from_withdrawal(user_id, amount, fee, address) @db.transaction(:exclusive) do # verify current balance current_balance = self.get_balance_for_user_id(user_id) if current_balance < (amount + fee) raise InsufficientFundsError, "Insufficient funds; Current balance: #{current_balance} for amount #{amount} + fee #{fee}" end @db.execute("insert into transactions (created_at, user_id, amount, withdrawal_address) values (?, ?, ?, ?)", [ Time.now.to_i, user_id, (0 - (amount + fee)), address ]) end true end |
#get_balance_for_user_id(user_id) ⇒ Object
49 50 51 |
# File 'lib/bitbot/database.rb', line 49 def get_balance_for_user_id(user_id) @db.get_first_value("select coalesce(sum(amount), 0) from transactions where user_id = ?", [ user_id ]) end |
#get_incoming_transaction_ids ⇒ Object
Returns an array of all the Bitcoin transaction ids for deposits
90 91 92 93 94 95 96 97 |
# File 'lib/bitbot/database.rb', line 90 def get_incoming_transaction_ids transaction_ids = [] @db.execute("select incoming_transaction from transactions where incoming_transaction is not null") do |row| transaction_ids << row[0] end transaction_ids end |
#get_or_create_user_id_for_username(username) ⇒ Object
35 36 37 38 39 40 41 42 43 |
# File 'lib/bitbot/database.rb', line 35 def get_or_create_user_id_for_username(username) user_id = @db.get_first_value("select id from users where username = ?", [ username ]) unless user_id @db.execute("insert into users(created_at, username) values (?, ?)", [ Time.now.to_i, username ]) user_id = @db.get_first_value("select last_insert_rowid()") end user_id end |
#get_tipping_stats(from = nil) ⇒ Object
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 |
# File 'lib/bitbot/database.rb', line 70 def get_tipping_stats(from = nil) # If no from is specified, use midnight of today. if from.nil? # TODO: this is hardcoding an offset of -0700 - fix that to make # the timezone configurable now = Time.now - 60 * 60 * 7 from = Time.local(now.year, now.month, now.day) end from = from.to_i stats = {} stats[:total_tipped] = @db.get_first_value("select coalesce(sum(amount), 0) from transactions t where t.other_user_id is not null and t.user_id <> t.other_user_id and t.amount < 0 and t.created_at > ?", [ from ]) stats[:total_tips] = @db.get_first_value("select count(*) from transactions t where t.other_user_id is not null and t.user_id <> t.other_user_id and t.amount < 0 and t.created_at > ?", [ from ]) stats[:tippers] = @db.execute("select * from (select username, sum(amount) total from transactions t, users u where t.user_id = u.id and other_user_id is not null and amount < 0 and user_id <> other_user_id and t.created_at >= ? group by username) foo order by total asc", [ from ]) stats[:tippees] = @db.execute("select * from (select username, sum(amount) total from transactions t, users u where t.user_id = u.id and amount > 0 and user_id <> other_user_id and t.created_at >= ? group by username) foo order by total desc", [ from ]) stats end |
#get_transactions_for_user_id(user_id) ⇒ Object
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
# File 'lib/bitbot/database.rb', line 53 def get_transactions_for_user_id(user_id) result = [] @db.execute("select t.id, t.created_at, t.amount, t.note, t.withdrawal_address, t.incoming_transaction, t.other_user_id, u.username from transactions t left outer join users u on t.other_user_id = u.id where user_id = ? order by t.created_at desc", [ user_id ]) do |row| result << { :id => row[0], :created_at => row[1], :amount => row[2], :note => row[3], :withdrawal_address => row[4], :incoming_transaction => row[5], :other_user_id => row[6], :other_username => row[7] } end result end |
#get_username_for_user_id(user_id) ⇒ Object
45 46 47 |
# File 'lib/bitbot/database.rb', line 45 def get_username_for_user_id(user_id) @db.get_first_value("select username from users where id = ?", [ user_id ]) end |
#upgrade_schema ⇒ Object
12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
# File 'lib/bitbot/database.rb', line 12 def upgrade_schema @db.execute(<<-ENDSQL) create table if not exists users ( id integer primary key autoincrement, created_at integer not null, username text not null unique ) ENDSQL @db.execute(<<-ENDSQL) create table if not exists transactions ( id integer primary key autoincrement, created_at integer not null, amount numeric not null, note text, withdrawal_address text, incoming_transaction text unique, user_id int references users(id) not null, other_user_id int references users(id) ) ENDSQL end |