Class: HSqlDatabaseManager

Inherits:
Object
  • Object
show all
Defined in:
lib/hsqlmanager/hsqldatabasemanager.rb

Direct Known Subclasses

HPgSqlDatabaseManager

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(host, port, dbname, user, password) ⇒ HSqlDatabaseManager

Returns a new instance of HSqlDatabaseManager.



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
# 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

Instance Attribute Details

#connection ⇒ Object (readonly)

Returns the value of attribute connection.



8
9
10
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 8

def connection
  @connection
end

#resultTable ⇒ Object (readonly)

Returns the value of attribute resultTable.



8
9
10
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 8

def resultTable
  @resultTable
end

Instance Method Details

#closeConnection ⇒ Object 



76
77
78
79
80
81
82
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 76

def closeConnection()

  @connection.close() if(@connection != nil)
  @connection = nil
  self.close()

end

#columnCount ⇒ Object 



165
166
167
168
169
170
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 165

def columnCount()

  return 0 if(self.rowCount() == 0)
  return @resultTable[0].count()

end

#dataByFieldIndex(row, fieldIndex) ⇒ Object 



129
130
131
132
133
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 129

def dataByFieldIndex(row, fieldIndex)

  return self.dataByFieldName(row, @resultTable.keys[fieldIndex])

end

#dataByFieldName(row, fieldName) ⇒ Object 



122
123
124
125
126
127
 
# 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) ⇒ Object 



281
282
283
284
285
 
# 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) ⇒ Object 



305
306
307
308
309
310
311
312
 
# 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 ⇒ Object 



85
86
87
88
89
90
91
92
93
94
95
 
# 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) ⇒ Object 



37
38
39
40
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 37

def from(from)
  @from = from
  return self
end

#insertOrUpdateIfExist(tableName, where, values = nil) ⇒ Object 



239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
 
# 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) ⇒ Object 



257
258
259
260
261
262
263
264
265
266
267
268
 
# 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) ⇒ Object 



197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
 
# 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) ⇒ Object 



49
50
51
52
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 49

def limit(limit)
  @limit = limit
  return self
end

#offset(offset) ⇒ Object 



53
54
55
56
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 53

def offset(offset)
  @offset = offset
  return self
end

#openConnection ⇒ Object 



69
70
71
72
73
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 69

def openConnection()

  @connection = self.connect() if(@connection == nil)

end

#orderBy(orderBy) ⇒ Object 



45
46
47
48
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 45

def orderBy(orderBy)
  @orderBy = orderBy
  return self
end

#parentChildValueOf(parentTableName, childTableName, parentTableId, fieldName, key = nil) ⇒ Object 



314
315
316
317
318
319
320
321
322
 
# 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) ⇒ Object 



324
325
326
327
328
329
330
 
# 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 ⇒ Object 



58
59
60
61
62
63
64
65
66
 
# 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) ⇒ Object

quota tutti i valori tranne quelli che iniziano con # quindi se nella mia query devo richiamare una funzione basta farla precedere dal cancelletto



183
184
185
186
187
188
189
190
191
192
193
194
 
# 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) ⇒ Object 



173
174
175
176
177
178
 
# 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 ⇒ Object 



159
160
161
162
163
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 159

def rowCount()

  return @resultTable.count()

end

#run(queryStr = self.queryStr) ⇒ Object 



97
98
99
100
101
102
 
# 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) ⇒ Object

def setQuery(queryStr)

  return self.run(queryStr)
end



108
109
110
111
112
113
114
115
116
117
118
119
120
 
# 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) ⇒ Object

posso passare anche un array di campi



32
33
34
35
36
 
# 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) ⇒ Object 



287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
 
# 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 ⇒ Object 



332
333
334
335
336
337
338
339
340
341
342
343
 
# 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 ⇒ Object

Se si vuole efficienza conviene non usare la seguente funzione ma le precedenti



137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
 
# 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) ⇒ Object 



270
271
272
273
274
275
276
277
278
279
 
# 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) ⇒ Object 



220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# 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) ⇒ Object 



41
42
43
44
 
# File 'lib/hsqlmanager/hsqldatabasemanager.rb', line 41

def where(where)
  @where = where
  return self
end