Class: N::DbConnection

Inherits:
Object
  • Object
show all
Includes:
DbUtils
Defined in:
lib/n/db/connection.rb

Overview

DbConnection

A Connection to the Database.

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from DbUtils

escape, escape_bytes, parse_sql_date, parse_sql_timestamp, read_prop, sql_date, sql_table, sql_timestamp, unescape_bytes, write_prop

Constructor Details

#initialize(config) ⇒ DbConnection

Initializate a connection to the database.



49
50
51
52
53
 
# File 'lib/n/db/connection.rb', line 49

def initialize(config)
	super
	@deserialize = true
	$log.debug "Created connection."
end

Instance Attribute Details

#deserialize ⇒ Object

If set to true, the select methods deserialize the rows to create entities.



45
46
47
 
# File 'lib/n/db/connection.rb', line 45

def deserialize
  @deserialize
end

#rdb ⇒ Object (readonly)

the actual connection to the database.



41
42
43
 
# File 'lib/n/db/connection.rb', line 41

def rdb
  @rdb
end

Instance Method Details

#child(entity, klass, extrasql = nil) ⇒ Object

child

Return one children of the parent



209
210
211
212
213
214
215
216
217
218
219
 
# File 'lib/n/db/connection.rb', line 209

def child(entity, klass, extrasql = nil)		
	pid = entity.to_i()
	
	sql = "SELECT * FROM #{klass::DBTABLE}"
	sql << " WHERE pid=#{pid}" if pid > 0
	sql << " #{extrasql}" if extrasql

	rows = safe_query(sql)		
	
	return deserialize_one(rows, klass)
end

#children(entity, klass, extrasql = nil) ⇒ Object

children

entity = entity or oid. klass = the class of the children to return. extrasql = extra sql for limit/order etc.

Design:

If the calculated pid is 0 returns all entities of the given class. It works like the older collect method. This make sense, the root of all objects has an oid = 0. INVESTIGATE: is this a security problem?

only_oids is not needed use the limit modifiers instead. if you need oids, use specialized sql. You can use the extrasql parameter to change the where clause too.



238
239
240
241
242
243
244
245
246
247
248
 
# File 'lib/n/db/connection.rb', line 238

def children(entity, klass, extrasql = nil)		
	pid = entity.to_i()
	
	sql = "SELECT * FROM #{klass::DBTABLE}"
	sql << " WHERE pid=#{pid}" if pid > 0
	sql << " #{extrasql}" if extrasql

	rows = safe_query(sql)		
	
	return deserialize_all(rows, klass)
end

#close ⇒ Object

Close the connection to the database.



57
58
59
60
 
# File 'lib/n/db/connection.rb', line 57

def close()
	super
	$log.debug "Closed connection."
end

#commit ⇒ Object 



274
275
276
 
# File 'lib/n/db/connection.rb', line 274

def commit
	exec_clear("COMMIT")
end

#count(sql) ⇒ Object

Count the entities returned by the sql statement.



350
351
352
353
354
355
356
 
# File 'lib/n/db/connection.rb', line 350

def count(sql)
	if rows = safe_query(sql)
		return rows[0][0].to_i()
	end 
	
	return 0
end

#count_children(entity, klass, extasql = nil) ⇒ Object

count_children

Use extrasql to change the where clause.



254
255
256
257
258
259
260
261
262
263
264
265
 
# File 'lib/n/db/connection.rb', line 254

def count_children(entity, klass, extasql = nil)
	pid = entity.to_i()
	
	sql = "SELECT COUNT(pid) FROM #{klass::DBTABLE}"
	sql << " WHERE pid=#{pid}" if pid > 0

	if rows = safe_query(sql)
		return rows[0][0].to_i()
	end 
	
	return 0
end

#delete(entity, klass = nil, cascade = true) ⇒ Object Also known as: delete!

Delete an entity from the database. Allways perform a deep delete.

DONT use ::DBTABLE (allow classes as table names).

Input:

entity = Entity or oid to delete. klass = klass of entity (can be nil if an entity is passed)



164
165
166
167
168
169
170
171
172
173
174
175
 
# File 'lib/n/db/connection.rb', line 164

def delete(entity, klass = nil, cascade = true)
	if entity.is_a?(Fixnum)
		oid = entity
		entity = klass.new
		entity.oid = oid
	end
	
	transaction {
		entity.__db_pre_delete(self)
		exec_clear("DELETE FROM #{DbUtils.sql_table(entity.class)} WHERE oid=#{entity.oid}")
	}
end

#delete_descendants(pid, pclass) ⇒ Object Also known as: delete_descendants!

Recursively delete all descendants of this entity. Operates in a transaction.



181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
 
# File 'lib/n/db/connection.rb', line 181

def delete_descendants(pid, pclass)
	return unless pclass.respond_to?(:__descendants_classes)
	
	for dclass in pclass.__descendants_classes
		if dclass.include?(N::ParentClass)
			rs = exec("SELECT oid FROM #{dclass::DBTABLE} WHERE pid=#{pid} AND pclass='#{pclass}'")
			exec_clear("DELETE FROM #{dclass::DBTABLE} where pid=#{pid} AND pclass='#{pclass}'")
		else
			rs = exec("SELECT oid FROM #{dclass::DBTABLE} WHERE pid=#{pid}")
			exec_clear("DELETE FROM #{dclass::DBTABLE} where pid=#{pid}")			
		end
		
		for tuple in (0...rs.num_tuples)
			delete_descendants(rs.getvalue(tuple, 0), dclass)
		end
		
		rs.clear()
	end	
end

#exec(sql) ⇒ Object

Useful in transactions



298
299
300
 
# File 'lib/n/db/connection.rb', line 298

def exec(sql)
	@rdb.exec(sql)
end

#exec_and_clear(sql) ⇒ Object Also known as: exec_clear

Useful in transactions Exec an sql command and clear the resultset.



305
306
307
308
 
# File 'lib/n/db/connection.rb', line 305

def exec_and_clear(sql)
	rows = @rdb.exec(sql)
	rows.clear()
end

#get(oid, klass) ⇒ Object

Get an entity from the database.

COOL: klass can be the class to fetch or the DBTABLE of the class. thanks to duck typing it works correctly!

Input: oid = the entity oid, OR the entity name.



115
116
117
118
119
120
121
 
# File 'lib/n/db/connection.rb', line 115

def get(oid, klass)
	if oid.to_i > 0 # a valid Fixnum ?
		get_by_oid(oid, klass)
	else
		get_by_name(oid, klass)
	end
end

#get_all(klass, extrasql = nil) ⇒ Object

Get all entities of the given klass.

COOL: klass can be the class to fetch or the DBTABLE of the class. thanks to duck typing it works correctly!

Used to be called ‘collect’ in an earlier version.



150
151
152
153
 
# File 'lib/n/db/connection.rb', line 150

def get_all(klass, extrasql = nil)
	rows = safe_query("SELECT * FROM #{DbUtils.sql_table(klass)} #{extrasql}")
	return deserialize_all(rows, klass)
end

#get_by_name(name, klass) ⇒ Object

Get an entity by name.

COOL: klass can be the class to fetch or the DBTABLE of the class. thanks to duck typing it works correctly!



138
139
140
141
 
# File 'lib/n/db/connection.rb', line 138

def get_by_name(name, klass)
	rows = safe_query("SELECT * FROM #{DbUtils.sql_table(klass)} WHERE name='#{name}'")
	return deserialize_one(rows, klass)
end

#get_by_oid(oid, klass) ⇒ Object

Get an entity by oid.

COOL: klass can be the class to fetch or the DBTABLE of the class. thanks to duck typing it works correctly!



128
129
130
131
 
# File 'lib/n/db/connection.rb', line 128

def get_by_oid(oid, klass)
	rows = safe_query("SELECT * FROM #{DbUtils.sql_table(klass)} WHERE oid=#{oid}")
	return deserialize_one(rows, klass)
end

#insert(obj) ⇒ Object

Force insertion of managed object. Typically used for NON-entities.



82
83
84
 
# File 'lib/n/db/connection.rb', line 82

def insert(obj)
	obj.__db_insert(self)
end

#match(sql) ⇒ Object Also known as: match?

Match a query, return true if a resultset was found



360
361
362
363
 
# File 'lib/n/db/connection.rb', line 360

def match(sql)
	res = safe_query(sql)
	return true if res and (res.num_tuples > 0)
end

#put(entity) ⇒ Object Also known as: <<

Put an entity to the database. Insert if this is a new entity or update if this is an existing entity.



68
69
70
71
72
73
74
75
76
 
# File 'lib/n/db/connection.rb', line 68

def put(entity)
	if entity.oid
		# entity allready inserted, update!
		entity.__db_update(self)
	else
		# this is a new entity, insert!
		entity.__db_insert(self)
	end
end

#query(sql) ⇒ Object Also known as: sql

Perform a standard SQL query to the database. Returns the result rows.



332
333
334
 
# File 'lib/n/db/connection.rb', line 332

def query(sql)
	return safe_query(sql)
end

#query_one(sql) ⇒ Object

Perform a standard SQL query to the database. Used for queries that return one row (typically with values) ie COUNT, etc.



340
341
342
343
344
345
346
 
# File 'lib/n/db/connection.rb', line 340

def query_one(sql)
	if rows = safe_query(sql)
		return rows[0]
	else
		return nil
	end
end

#rollback ⇒ Object 



278
279
280
 
# File 'lib/n/db/connection.rb', line 278

def rollback
	exec_clear("ROLLBACK")
end

#select(sql, klass, join_fields = nil) ⇒ Object

Perform a standard SQL query to the database. Deserializes the results.



317
318
319
320
 
# File 'lib/n/db/connection.rb', line 317

def select(sql, klass, join_fields = nil)
	rows = safe_query(sql)
	return deserialize_all(rows, klass, join_fields)
end

#select_one(sql, klass, join_fields = nil) ⇒ Object

Optimized for one result.



324
325
326
327
 
# File 'lib/n/db/connection.rb', line 324

def select_one(sql, klass, join_fields = nil)
	rows = safe_query(sql)
	return deserialize_one(rows, klass, join_fields)
end

#start ⇒ Object

Transaction methods.



270
271
272
 
# File 'lib/n/db/connection.rb', line 270

def start
	exec_clear("START TRANSACTION")
end

#transaction(&block) ⇒ Object

Transaction helper



284
285
286
287
288
289
290
291
292
293
294
 
# File 'lib/n/db/connection.rb', line 284

def transaction(&block)
	begin
		exec_clear("START TRANSACTION")
		yield 
		exec_clear("COMMIT")
	rescue => ex
		$log.error "DB Error: ERROR IN TRANSACTION"
		$log.error "DB Error: #{ex}, #{caller[0]} : #{caller[1]} : #{caller[2]}"
		exec_clear("ROLLBACK")
	end
end

#update(obj) ⇒ Object

Force update of managed object. Typically used for relations.



89
90
91
 
# File 'lib/n/db/connection.rb', line 89

def update(obj)
	obj.__db_update(self)
end

#update_properties(update_sql, ent_or_oid, klass = nil) ⇒ Object Also known as: pupdate

Update only specific fields of the entity

Input: sql = the sql code to updated the properties.



98
99
100
101
102
103
104
 
# File 'lib/n/db/connection.rb', line 98

def update_properties(update_sql, ent_or_oid, klass = nil)
	oid = ent_or_oid.to_i()
	klass = ent_or_oid.class unless klass
	
	sql = "UPDATE #{klass::DBTABLE} SET #{update_sql} WHERE oid=#{oid}"
	retry_query(sql, klass)
end