db.rb 4.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165
  1. #!/usr/bin/ruby
  2. # Copyright (c) 2006, 2007 Peter Palfrader
  3. #
  4. # Permission is hereby granted, free of charge, to any person obtaining a copy
  5. # of this software and associated documentation files (the "Software"), to deal
  6. # in the Software without restriction, including without limitation the rights
  7. # to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
  8. # copies of the Software, and to permit persons to whom the Software is
  9. # furnished to do so, subject to the following conditions:
  10. #
  11. # The above copyright notice and this permission notice shall be included in
  12. # all copies or substantial portions of the Software.
  13. #
  14. # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  15. # IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  16. # FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
  17. # AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  18. # LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  19. # OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
  20. # SOFTWARE.
  21. require "dbi"
  22. class WeaselDbQueryHandle
  23. def initialize(sth)
  24. @sth = sth
  25. end
  26. def next()
  27. row = @sth.fetch_hash
  28. if row
  29. return row
  30. else
  31. @sth.finish
  32. return nil
  33. end
  34. end
  35. end
  36. class Db
  37. def initialize(database, user, password)
  38. @dbh = DBI.connect("dbi:Pg:#{database}:localhost", user, password);
  39. @dbh['AutoCommit'] = false
  40. @transaction = false
  41. @pre_initial_transaction=true
  42. end
  43. def do(query,*args)
  44. @dbh.do(query,*args)
  45. end
  46. def transaction_begin()
  47. @dbh.do("BEGIN") unless @pre_initial_transaction
  48. @transaction = true
  49. @pre_initial_transaction=false
  50. end
  51. def transaction_commit()
  52. @dbh.do("COMMIT")
  53. @transaction = false
  54. end
  55. def transaction_rollback()
  56. @dbh.do("ROLLBACK")
  57. end
  58. def get_primarykey_name(table);
  59. #return 'ref';
  60. return table+'_id';
  61. end
  62. def update(table, values, keys)
  63. cols = []
  64. vals = []
  65. values.each_pair{ |k,v|
  66. cols << "#{k}=?"
  67. vals << v
  68. }
  69. wheres = []
  70. keys.each_pair{ |k,v|
  71. wheres << "#{k}=?"
  72. vals << v
  73. }
  74. throw "update value set empty" unless cols.size > 0
  75. throw "where clause empty" unless wheres.size > 0
  76. query = "UPDATE #{table} SET #{cols.join(',')} WHERE #{wheres.join(' AND ')}"
  77. transaction_begin unless transaction_before=@transaction
  78. r = @dbh.do(query, *vals)
  79. transaction_commit unless transaction_before
  80. return r
  81. end
  82. def update_row(table, values)
  83. pk_name = get_primarykey_name(table);
  84. throw "Ref not defined" unless values[pk_name]
  85. return update(table, values.clone.delete_if{|k,v| k == pk_name}, { pk_name => values[pk_name] });
  86. end
  87. def insert(table, values)
  88. cols = values.keys
  89. vals = values.values
  90. qmarks = values.values.collect{ '?' }
  91. query = "INSERT INTO #{table} (#{cols.join(',')}) VALUES (#{qmarks.join(',')})"
  92. transaction_begin unless transaction_before=@transaction
  93. @dbh.do(query, *vals)
  94. transaction_commit unless transaction_before
  95. end
  96. def insert_row(table, values)
  97. pk_name = get_primarykey_name(table);
  98. if values[pk_name]
  99. insert(table, values)
  100. else
  101. transaction_begin unless transaction_before=@transaction
  102. row = query_row("SELECT nextval(pg_get_serial_sequence('#{table}', '#{pk_name}')) AS newref");
  103. throw "No newref?" unless row['newref']
  104. values[pk_name] = row['newref']
  105. insert(table, values);
  106. transaction_commit unless transaction_before
  107. end
  108. end
  109. def delete_row(table, ref)
  110. pk_name = get_primarykey_name(table);
  111. query = "DELETE FROM #{table} WHERE #{pk_name}=?"
  112. transaction_begin unless transaction_before=@transaction
  113. @dbh.do(query, ref)
  114. transaction_commit unless transaction_before
  115. end
  116. def query(query, *params)
  117. sth = @dbh.execute(query, *params)
  118. while row = sth.fetch_hash
  119. yield row
  120. end
  121. sth.finish
  122. end
  123. # nil if no results
  124. # hash if one match
  125. # throw otherwise
  126. def query_row(query, *params)
  127. sth = @dbh.execute(query, *params)
  128. row = sth.fetch_hash
  129. if row == nil
  130. sth.finish
  131. return nil
  132. elsif sth.fetch_hash != nil
  133. sth.finish
  134. throw "More than one result when querying for #{query}"
  135. else
  136. sth.finish
  137. return row
  138. end
  139. end
  140. def query_all(query, *params)
  141. sth = @dbh.execute(query, *params)
  142. rows = sth.fetch_all
  143. return nil if rows.size == 0
  144. return rows
  145. end
  146. def query2(query, *params)
  147. sth = @dbh.execute(query, *params)
  148. return WeaselDbQueryHandle.new(sth)
  149. end
  150. end