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