3 # Copyright (c) 2006, 2007 Peter Palfrader
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:
12 # The above copyright notice and this permission notice shall be included in
13 # all copies or substantial portions of the Software.
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
25 class WeaselDbQueryHandle
42 def initialize(host, database, user, password)
43 @dbh = DBI.connect("dbi:Pg:#{database}:#{host}", user, password);
44 @dbh['AutoCommit'] = false
46 @pre_initial_transaction=true
52 def transaction_begin()
53 @dbh.do("BEGIN") unless @pre_initial_transaction
55 @pre_initial_transaction=false
57 def transaction_commit()
61 def transaction_rollback()
64 def get_primarykey_name(table);
69 def update(table, values, keys)
72 values.each_pair{ |k,v|
83 throw "update value set empty" unless cols.size > 0
84 throw "where clause empty" unless wheres.size > 0
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
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] });
98 def insert(table, values)
101 qmarks = values.values.collect{ '?' }
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
109 def insert_row(table, values)
110 pk_name = get_primarykey_name(table);
112 insert(table, values)
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
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
127 transaction_commit unless transaction_before
129 def query(query, *params)
130 sth = @dbh.execute(query, *params)
131 while row = sth.fetch_hash
139 def query_row(query, *params)
140 sth = @dbh.execute(query, *params)
146 elsif sth.fetch_hash != nil
148 throw "More than one result when querying for #{query}"
154 def query_all(query, *params)
155 sth = @dbh.execute(query, *params)
158 return nil if rows.size == 0
161 def query2(query, *params)
162 sth = @dbh.execute(query, *params)
163 return WeaselDbQueryHandle.new(sth)