class HSqlDatabaseManager
databaseManager.select(“id, name”).from(“cookbook”).where(“id > 10”).orderBy(“id”).limit(5).offset(5).run().show()
Attributes
connection[R]
resultTable[R]
Public Class Methods
new(host, port, dbname, user, password)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 10 def initialize(host, port, dbname, user, password) @host = host @port = port @dbname = dbname @user = user @password = password @connection = nil @select = nil @from = nil @where = nil @orderBy = nil @limit = nil @offset = nil @resultTable = nil end
Public Instance Methods
closeConnection()
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 76 def closeConnection() @connection.close() if(@connection != nil) @connection = nil self.close() end
columnCount()
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 165 def columnCount() return 0 if(self.rowCount() == 0) return @resultTable[0].count() end
dataByFieldIndex(row, fieldIndex)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 129 def dataByFieldIndex(row, fieldIndex) return self.dataByFieldName(row, @resultTable.keys[fieldIndex]) end
dataByFieldName(row, fieldName)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 122 def dataByFieldName(row, fieldName) return nil if(@resultTable.count() == 0) return @resultTable[row][fieldName] end
deleteCacheBranch(tableName, dialogViewLevel)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 281 def deleteCacheBranch(tableName, dialogViewLevel) self.run("delete from cache_table where table_name = '#{tableName}' and view_level >= #{dialogViewLevel}").resultTable end
extractFromHash(value, key = nil)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 305 def extractFromHash(value, key = nil) return nil unless(value) value = eval(value) if(value[0] == '{' && value[-1] == '}') # E' un hash table return value[key] if(value && key) return value end
fieldNameList()
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 85 def fieldNameList() fieldList = HList.new() for i in (0..@resultTable.nfields() - 1) fieldList.insertTail(HRecord.new(@resultTable.fname(i))) end return fieldList end
from(from)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 37 def from(from) @from = from return self end
insertOrUpdateIfExist(tableName, where, values = nil)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 239 def insertOrUpdateIfExist(tableName, where, values = nil) resultTable = self.run("select id from #{tableName} where #{where} ").resultTable if(resultTable.count == 0) insertQuery = "insert into #{tableName} #{self.insertValues(values)} returning id" p "================= insertOrUpdateIfExist ================== ", insertQuery newId = self.run(insertQuery).resultTable[0]["id"] return newId else p values updateQuery = "update #{tableName} set #{self.updateValues(values)} where #{where}" p updateQuery self.run(updateQuery) if(values) return resultTable[0]["id"] end end
insertOrUpdateIfExistCached(tableName, dialogViewLevel, id, values)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 257 def insertOrUpdateIfExistCached(tableName, dialogViewLevel, id, values) value = {} value[:view_level] = dialogViewLevel value[:table_name] = tableName value[:field_id] = id value[:field_value] = values value[:state] = "none" return self.insertOrUpdateIfExist("cache_table", "table_name = '#{tableName}' and field_id = #{id}", value) end
insertValues(args)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 197 def insertValues(args) return "(id) values(default)" unless(args) args = self.quote(args) keys = args.keys result = "(" i = -1 for i in (0..args.count - 2) do result += "#{keys[i]}, " end result += "#{keys[i + 1]}) values(" i = -1 for i in (0..args.count - 2) do result += "#{args[keys[i]]}, " end result += "#{args[keys[i + 1]]})" return result end
limit(limit)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 49 def limit(limit) @limit = limit return self end
offset(offset)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 53 def offset(offset) @offset = offset return self end
openConnection()
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 69 def openConnection() @connection = self.connect() if(@connection == nil) end
orderBy(orderBy)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 45 def orderBy(orderBy) @orderBy = orderBy return self end
parentChildValueOf(parentTableName, childTableName, parentTableId, fieldName, key = nil)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 314 def parentChildValueOf(parentTableName, childTableName, parentTableId, fieldName, key = nil) return nil if(parentTableId == -1) selectQuery = "select field_value from #{childTableName}_table where field_name = '#{fieldName}' and #{parentTableName}_id = #{parentTableId}" value = self.run(selectQuery).dataByFieldName(0, "field_value") return extractFromHash(value, key) end
parentChildValueOfByUsername(parentTableName, childTableName, username, fieldName, key = nil)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 324 def parentChildValueOfByUsername(parentTableName, childTableName, username, fieldName, key = nil) selectQuery = "select id from #{parentTableName}_table where username = '#{username}'" parentTableId = self.run(selectQuery).dataByFieldName(0, "id") return self.parentChildValueOf(parentTableName, childTableName, parentTableId, fieldName, key) end
queryStr()
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 58 def queryStr() where = "where #{@where}" if(@where) orderBy = "order by #{@orderBy}" if(@orderBy) limit = "limit #{@limit}" if(@limit) offset = "offset #{@offset}" if(@offset) return "select #{@select} from #{@from} #{where} #{orderBy} #{limit} #{offset}" end
quote(args)
click to toggle source
quota tutti i valori tranne quelli che iniziano con # quindi se nella mia query devo richiamare una funzione basta farla precedere dal cancelletto
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 183 def quote(args) return nil unless(args) result = Hash.new() args.each do |key, value| result[key] = self.quoteValue(args[key]) end return result end
quoteValue(value)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 173 def quoteValue(value) return nil unless(value) return (value[0] != '#') ? "'#{value}'" : value[1, value.size - 1] end
rowCount()
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 159 def rowCount() return @resultTable.count() end
run(queryStr = self.queryStr)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 97 def run(queryStr = self.queryStr) @resultTable = @connection.query(queryStr) return self end
runWithPaging(pageSize = "all", page = 0, queryStr = self.queryStr)
click to toggle source
def setQuery(queryStr)
return self.run(queryStr)
end
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 108 def runWithPaging(pageSize = "all", page = 0, queryStr = self.queryStr) limit = "limit #{pageSize}" offset = "offset #{page} * #{pageSize}" if(pageSize == "all") limit = offset = "" end queryStr += " #{limit} #{offset}" return self.run(queryStr) end
select(select)
click to toggle source
posso passare anche un array di campi
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 32 def select(select) select = select.join(',') if(select.class == Array) @select = select return self end
setParentChildTableValues(parentTableName, childTableName, where, parentValues, childValues)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 287 def setParentChildTableValues(parentTableName, childTableName, where, parentValues, childValues) parentTableId = self.insertOrUpdateIfExist("#{parentTableName}_table", where, parentValues) i = 0 record = Hash.new() childValues.each do |key, value| childRecord = {"#{parentTableName}_id" => parentTableId, :field_name => key, :field_value => value, :ordering => i} recordWhere = "#{parentTableName}_id = #{parentTableId} and field_name = '#{key}'" self.insertOrUpdateIfExist("#{childTableName}_table", recordWhere, childRecord) i += 1 end end
show()
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 332 def show() self.fieldNameList().show() puts @resultTable.each do |row| row.each do |key, value| print "%-15s" % row[key] end puts end end
toSqlTable()
click to toggle source
Se si vuole efficienza conviene non usare la seguente funzione ma le precedenti
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 137 def toSqlTable() sqlTable = HSqlTable.new() fieldNameList = self.fieldNameList() fieldNameList.eachWithIndex do |fieldName, i| sqlTable.setFieldName(i, fieldName.value(), "true"); end @resultTable.each_with_index do |row, i| j = 0 row.each do |key, value| sqlTable.setDataByFieldIndex(i, j, HRecord.new(value)) j += 1 end end return sqlTable end
unloadCache(tableName, dialogViewLevel, id)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 270 def unloadCache(tableName, dialogViewLevel, id) p "unloadCache" resultTable = self.run("select field_id, field_value from cache_table where table_name = '#{tableName}'").resultTable resultTable.each do |row| self.insertOrUpdateIfExist(tableName, "id = #{row['field_id']}", eval(row['field_value'])) end self.run("delete from cache_table where table_name = '#{tableName}'").resultTable end
updateValues(args)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 220 def updateValues(args) return "" unless(args) args = self.quote(args) keys = args.keys result = "" i = -1 for i in (0..args.count - 2) do result += "#{keys[i]} = #{args[keys[i]]}, " end result += "#{keys[i + 1]} = #{args[keys[i + 1]]}" return result end
where(where)
click to toggle source
# File lib/hsqlmanager/hsqldatabasemanager.rb, line 41 def where(where) @where = where return self end