contrib/auto-naming/db.rb

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