Class: Bitbot::Database

Inherits:
Object
  • Object
show all
Defined in:
lib/bitbot/database.rb

Instance Method Summary collapse

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, message)
  @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), message, to_user_id ])
    @db.execute("insert into transactions (created_at, user_id, amount, note, other_user_id)
                values (?, ?, ?, ?, ?)", [ now, to_user_id, amount, message, 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