Module: N::PsqlBackend

Defined in:
lib/n/db/psql.rb

Overview

PsqlBackend

Implement the Db backend using the PostgreSQL RDBMS.

Constant Summary collapse

TYPEMAP =

map between Ruby and SQL types

{
	Integer => "integer",
	Fixnum => "integer",
	Float => "float",
	String => "text",
	Time => "timestamp",
	Date => "date",
	TrueClass => "boolean",
	Object => "bytea",
	Array => "bytea",
	Hash => "bytea"
}

Instance Method Summary collapse

Instance Method Details

#calc_fields(rows, klass) ⇒ Object

Calculate the fields map for the given class



220
221
222
223
224
225
226
227
 
# File 'lib/n/db/psql.rb', line 220

def calc_fields(rows, klass)
	# gmosx SOS, FIXME, INVESTIGATE: no need for second safe hash ????
	fields = $db.fields[klass] = {}
	for field in rows.fields
		fields[field] = rows.fieldnum(field)
	end
	N::Managed.eval_db_read_row(klass)
end

#close ⇒ Object

Close the connection to the database



132
133
134
 
# File 'lib/n/db/psql.rb', line 132

def close()
	@rdb.close
end

#create_schema ⇒ Object

Create the sequence that generates the unified space id oids. You MUST call this method on newly created databases.



140
141
142
 
# File 'lib/n/db/psql.rb', line 140

def create_schema()
	safe_query("CREATE SEQUENCE oids_seq")
end

#create_table(klass) ⇒ Object

Create a table for an entity.



166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
 
# File 'lib/n/db/psql.rb', line 166

def create_table(klass)
	fields = []
	klass.__props.each { |p|
		field = "#{p.symbol}"
		if p.sql
			field << " #{p.sql}"
		else
			field << " #{TYPEMAP[p.klass]}"
		end
		
		fields << field 
	}
	
	sql = "CREATE TABLE #{klass::DBTABLE} (#{fields.join(', ')}"
	
	# Create table constrains
	
	if klass.__meta and constrains = klass.__meta[:sql_constrain]
		sql << ", #{constrains.join(', ')}"
	end
	
	sql << ") WITHOUT OIDS;"
	
	# Create indices
	
	if klass.__meta
		for data in klass.__meta[:sql_index]
			idx, pre_sql, post_sql = *data
			idxname = idx.gsub(/ /, "").gsub(/,/, "_").gsub(/\(.*\)/, "")
			sql << " CREATE #{pre_sql} INDEX #{klass::DBTABLE}_#{idxname}_idx #{post_sql} ON #{klass::DBTABLE} (#{idx});"  
		end
	end

	safe_query(sql)
	$log.info "Created table #{klass::DBTABLE}!"

	# create the sequence for this table. Even if the table
	# uses the oids_seq, attempt to create it. This makes
	# the system more fault tolerant.
	safe_query("CREATE SEQUENCE #{klass::DBSEQ}")
	$log.info "Created sequence #{klass::DBSEQ}!"
end

#deserialize_all(rows, klass, join_fields = nil) ⇒ Object

If the connection is in deserialize mode, deserialize all rows.



263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
 
# File 'lib/n/db/psql.rb', line 263

def deserialize_all(rows, klass, join_fields = nil)	
	return nil unless rows

	calc_fields(rows, klass) unless $db.fields[klass]
	
	if @deserialize		
		entities = []
	
		for tuple in (0...rows.num_tuples)
			entity = klass.new()
			entity.__db_read_row(rows, tuple)
			
			get_join_fields(rows, tuple, entity, join_fields) if join_fields
			
			entities << entity
		end
		
		rows.clear()
		return entities
	end
	
	return rows
end

#deserialize_one(rows, klass, join_fields = nil) ⇒ Object

If the connection is in deserialize mode, deserialize one row.



241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
 
# File 'lib/n/db/psql.rb', line 241

def deserialize_one(rows, klass, join_fields = nil)	
	return nil unless rows
	
	calc_fields(rows, klass) unless $db.fields[klass]
	
	if @deserialize
		# gmosx: Enities should have no params constructor SOS.
		#
		entity = klass.new()
		entity.__db_read_row(rows, 0)
		
		get_join_fields(rows, 0, entity, join_fields) if join_fields
		
		rows.clear()
		return entity
	end

	return rows[0]
end

#drop_schema ⇒ Object

Drop the oid sequence



146
147
148
 
# File 'lib/n/db/psql.rb', line 146

def drop_schema()
	safe_query("DROP SEQUENCE oids_seq")
end

#drop_table(klass) ⇒ Object

Drop the entity table



211
212
213
214
215
216
 
# File 'lib/n/db/psql.rb', line 211

def drop_table(klass)
	safe_query("DROP TABLE #{klass::DBTABLE}")
	if klass.include?(N::Sequenced)
		safe_query("DROP SEQUENCE #{klass::DBSEQ};")
	end
end

#execute_statement ⇒ Object Also known as: xstatement

NOT IMPLEMENTED



159
160
161
 
# File 'lib/n/db/psql.rb', line 159

def execute_statement()
	self.select("EXECUTE")
end

#get_join_fields(rows, tuple, entity, join_fields) ⇒ Object

Grab the join fields returned by an sql join query, and attach them to the entity.



232
233
234
235
236
237
 
# File 'lib/n/db/psql.rb', line 232

def get_join_fields(rows, tuple, entity, join_fields)
	entity.join_fields = {}
	for f in join_fields
		entity.join_fields[f] = rows.getvalue(tuple, $db.fields[entity.class][f.to_s])
	end
end

#initialize(config) ⇒ Object

Initialize a connection to the database



125
126
127
128
 
# File 'lib/n/db/psql.rb', line 125

def initialize(config)
	@rdb = PGconn.connect(nil, nil, nil, nil, config[:database], 
			config[:user], config[:password])
end

#next_oid(klass) ⇒ Object

Get the next oid in the sequence for this klass



346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
 
# File 'lib/n/db/psql.rb', line 346

def next_oid(klass)
	retries = 0
	begin
		res = @rdb.exec("SELECT nextval('#{klass::DBSEQ}')")
		oid = res.getvalue(0, 0).to_i()
		res.clear()
		return oid
	rescue => ex
		# Any idea how to better test this?
		if ex.to_s =~ /relation .* not exist/
			$log.info "next_oid: #{ex}"
			# table does not exist, create it!
			create_table(klass)
			# gmosx: only allow ONE retry to avoid loops here!
			retries += 1 
			retry if retries <= 1
		else
			$log.error "DB Error: #{ex}, #next_oid"
			$log.debug "#{caller[0]} : #{caller[1]} : #{caller[2]}"
			return nil
		end
	end
end

#prepare_statement ⇒ Object Also known as: pstatement

NOT IMPLEMENTED



152
153
154
 
# File 'lib/n/db/psql.rb', line 152

def prepare_statement()
	@rdb.query("PREPARE")
end

#retry_query(sql, klass = nil) ⇒ Object

Execute an sql query. If the entity table is missing, create it and retry.

exec() is used instead of query because it is faster and we also need fields()

FIXME: is the result cleared?



296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
 
# File 'lib/n/db/psql.rb', line 296

def retry_query(sql, klass = nil)
	$log.debug sql if $DBG
	retries = 0
	begin
		rows = @rdb.exec(sql)
		if rows && (rows.num_tuples > 0) 
			return rows
		else
			return nil
		end
	rescue => ex
		# Any idea how to better test this?
		if ex.to_s =~ /relation .* not exist/
			$log.info "RETRY_QUERY"
			# table does not exist, create it!
			create_table(klass)
			# gmosx: only allow ONE retry to avoid loops here!
			retries += 1 
			retry if retries <= 1
		else
			$log.error "RETRY_QUERY: surpressing db error: #{ex}, [#{sql}]"
#				$log.debug "#{caller[0]} : #{caller[1]} : #{caller[2]}"
			return nil
		end
	end
end

#safe_query(sql) ⇒ Object

Execute an sql query and catch the errors.

exec() is used instead of query because it is faster and we also need fields()



328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
 
# File 'lib/n/db/psql.rb', line 328

def safe_query(sql)	
	$log.debug sql if $DBG
	begin
		rows = @rdb.exec(sql)
		if rows && (rows.num_tuples > 0) 
			return rows
		else
			return nil
		end
	rescue => ex
			$log.error "SAFE_QUERY: surpressing db error #{ex}, [#{sql}]"
#				$log.debug "#{caller[0]} : #{caller[1]} : #{caller[2]}"
		return nil
	end
end