1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
-- Gemeinschaft 5 module: database class
-- (c) AMOOMA GmbH 2013
--
module(...,package.seeall)
Database = {}
DATABASE_DSN = 'gemeinschaft';
function Database.new(self, arg)
arg = arg or {}
object = arg.object or {}
setmetatable(object, self);
self.__index = self;
self.class = 'database';
self.log = arg.log;
self.conn = nil;
self.ignore_on_update = arg.ignore_on_update or {};
return object;
end
function Database.connect(self)
self.dsn = DATABASE_DSN;
require 'common.configuration_file'
local dsn = common.configuration_file.get('/var/lib/freeswitch/.odbc.ini', self.dsn);
self.database_name = dsn.DATABASE;
self.user_name = dsn.USER;
self.password = dsn.PASSWORD;
self.host_name = dsn.HOST;
self.conn = freeswitch.Dbh(self.dsn, self.user_name, self.password);
self.conn_id = tostring(self.conn);
return self;
end
function Database.connected(self)
return self.conn:connected();
end
function Database.query(self, sql_query, call_function)
if call_function then
return self.conn:query(sql_query, call_function);
else
return self.conn:query(sql_query);
end
end
function Database.query_return_value(self, sql_query)
local result = nil;
self.conn:query(sql_query, function(row)
for key, value in pairs(row) do
result = value;
return result;
end
end)
return result;
end
function Database.query_return_first(self, sql_query)
local result = nil;
self.conn:query(sql_query, function(row)
result = row;
return result;
end);
return result;
end
function Database.query_return_all(self, sql_query)
local result = {};
self.conn:query(sql_query, function(row)
table.insert(result, row);
end);
return result;
end
function Database.last_insert_id(self)
return self:query_return_value('SELECT LAST_INSERT_ID()');
end
function Database.insert_or_update(self, db_table, record, ignore_on_update)
ignore_on_update = ignore_on_update or self.ignore_on_update;
local record_sql_create = {};
local record_sql_update = {};
for key, value in pairs(record) do
if ignore_on_update[key] ~= false then
table.insert(record_sql_update, self:key_value(key, value));
end
table.insert(record_sql_create, self:key_value(key, value));
end
local sql_query = 'INSERT INTO `' .. db_table .. '` SET ' .. table.concat(record_sql_create, ', ') .. ' ON DUPLICATE KEY UPDATE ' .. table.concat(record_sql_update, ', ');
return self:query(sql_query);
end
function Database.key_value(self, key, value)
return self:escape(key, '`') .. ' = ' .. self:escape(value, '"');
end
function Database.escape(self, value, str_quotes)
str_quotes = str_quotes or '';
if type(value) == 'boolean' then
return tostring(value):upper();
elseif type(value) == 'number' then
return tostring(value);
elseif type(value) == 'string' then
return str_quotes .. value:gsub('"', '\\"'):gsub("'", "\\'") .. str_quotes;
elseif type(value) == 'table' and value.raw then
return tostring(value[1]);
else
return 'NULL';
end
end
function Database.release(self)
if self.conn then
self.conn:release();
end
end
|
