e97cafc3bae3d2029d41ad5f89f5623ee95d1506
[public/netxms.git] / src / server / tools / nxdbmgr / upgrade.cpp
1 /*
2 ** nxdbmgr - NetXMS database manager
3 ** Copyright (C) 2004 Victor Kirhenshtein
4 **
5 ** This program is free software; you can redistribute it and/or modify
6 ** it under the terms of the GNU General Public License as published by
7 ** the Free Software Foundation; either version 2 of the License, or
8 ** (at your option) any later version.
9 **
10 ** This program is distributed in the hope that it will be useful,
11 ** but WITHOUT ANY WARRANTY; without even the implied warranty of
12 ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 ** GNU General Public License for more details.
14 **
15 ** You should have received a copy of the GNU General Public License
16 ** along with this program; if not, write to the Free Software
17 ** Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
18 **
19 ** $module: upgrade.cpp
20 **
21 **/
22
23 #include "nxdbmgr.h"
24
25
26 //
27 // Create table
28 //
29
30 static BOOL CreateTable(TCHAR *pszQuery)
31 {
32 TCHAR *pszBuffer;
33 BOOL bResult;
34
35 pszBuffer = (TCHAR *)malloc(_tcslen(pszQuery) * sizeof(TCHAR) + 256);
36 _tcscpy(pszBuffer, pszQuery);
37 TranslateStr(pszBuffer, _T("$SQL:TEXT"), g_pszSqlType[g_iSyntax][SQL_TYPE_TEXT]);
38 TranslateStr(pszBuffer, _T("$SQL:INT64"), g_pszSqlType[g_iSyntax][SQL_TYPE_INT64]);
39 if (g_iSyntax == DB_SYNTAX_MYSQL)
40 _tcscat(pszBuffer, _T(" type=InnoDB"));
41 bResult = SQLQuery(pszBuffer);
42 free(pszBuffer);
43 return bResult;
44 }
45
46
47 //
48 // Create configuration parameter if it doesn't exist
49 //
50
51 static BOOL CreateConfigParam(TCHAR *pszName, TCHAR *pszValue, int iVisible, int iNeedRestart)
52 {
53 TCHAR szQuery[1024], *pszEscValue;
54 DB_RESULT hResult;
55 BOOL bVarExist = FALSE, bResult = TRUE;
56
57 // Check for variable existence
58 _stprintf(szQuery, _T("SELECT var_value FROM config WHERE var_name='%s'"), pszName);
59 hResult = DBSelect(g_hCoreDB, szQuery);
60 if (hResult != 0)
61 {
62 if (DBGetNumRows(hResult) > 0)
63 bVarExist = TRUE;
64 DBFreeResult(hResult);
65 }
66
67 if (!bVarExist)
68 {
69 pszEscValue = EncodeSQLString(pszValue);
70 _stprintf(szQuery, _T("INSERT INTO config (var_name,var_value,is_visible,"
71 "need_server_restart) VALUES ('%s','%s',%d,%d)"),
72 pszName, pszEscValue, iVisible, iNeedRestart);
73 free(pszEscValue);
74 bResult = SQLQuery(szQuery);
75 }
76 return bResult;
77 }
78
79
80 //
81 // Upgrade from V39 to V40
82 //
83
84 static BOOL H_UpgradeFromV39(void)
85 {
86 static TCHAR m_szBatch[] =
87 "ALTER TABLE users ADD grace_logins integer\n"
88 "UPDATE users SET grace_logins=5\n"
89 "<END>";
90
91 if (!SQLBatch(m_szBatch))
92 if (!g_bIgnoreErrors)
93 return FALSE;
94
95 if (!SQLQuery(_T("UPDATE config SET var_value='40' WHERE var_name='DBFormatVersion'")))
96 if (!g_bIgnoreErrors)
97 return FALSE;
98
99 return TRUE;
100 }
101
102
103 //
104 // Upgrade from V38 to V39
105 //
106
107 static BOOL H_UpgradeFromV38(void)
108 {
109 static TCHAR m_szBatch[] =
110 "INSERT INTO maps (map_id,map_name,description,root_object_id) "
111 "VALUES (1,'Default','Default network map',1)\n"
112 "INSERT INTO map_access_lists (map_id,user_id,access_rights) VALUES (1,-2147483648,1)\n"
113 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) "
114 "VALUES (33,'SYS_SCRIPT_ERROR',2,1,"
115 "'Script (%1) execution error: %2',"
116 "'Generated when server encounters NXSL script execution error.#0D#0A"
117 "Parameters:#0D#0A"
118 " 1) Script name#0D#0A"
119 " 2) Error text#0D#0A"
120 " 3) DCI ID if script is DCI transformation script, or 0 otherwise')\n"
121 "<END>";
122
123 if (!CreateTable(_T("CREATE TABLE maps ("
124 "map_id integer not null,"
125 "map_name varchar(255) not null,"
126 "description $SQL:TEXT not null,"
127 "root_object_id integer not null,"
128 "PRIMARY KEY(map_id))")))
129 if (!g_bIgnoreErrors)
130 return FALSE;
131
132 if (!CreateTable(_T("CREATE TABLE map_access_lists ("
133 "map_id integer not null,"
134 "user_id integer not null,"
135 "access_rights integer not null,"
136 "PRIMARY KEY(map_id,user_id))")))
137 if (!g_bIgnoreErrors)
138 return FALSE;
139
140 if (!CreateTable(_T("CREATE TABLE submaps ("
141 "map_id integer not null,"
142 "submap_id integer not null,"
143 "attributes integer not null,"
144 "PRIMARY KEY(map_id,submap_id))")))
145 if (!g_bIgnoreErrors)
146 return FALSE;
147
148 if (!CreateTable(_T("CREATE TABLE submap_object_positions ("
149 "map_id integer not null,"
150 "submap_id integer not null,"
151 "object_id integer not null,"
152 "x integer not null,"
153 "y integer not null,"
154 "PRIMARY KEY(map_id,submap_id,object_id))")))
155 if (!g_bIgnoreErrors)
156 return FALSE;
157
158 if (!CreateTable(_T("CREATE TABLE submap_links ("
159 "map_id integer not null,"
160 "submap_id integer not null,"
161 "object_id1 integer not null,"
162 "object_id2 integer not null,"
163 "link_type integer not null,"
164 "PRIMARY KEY(map_id,submap_id,object_id1,object_id2))")))
165 if (!g_bIgnoreErrors)
166 return FALSE;
167
168 if (!SQLBatch(m_szBatch))
169 if (!g_bIgnoreErrors)
170 return FALSE;
171
172 if (!CreateConfigParam(_T("LockTimeout"), _T("60000"), 1, 1))
173 if (!g_bIgnoreErrors)
174 return FALSE;
175
176 if (!CreateConfigParam(_T("DisableVacuum"), _T("0"), 1, 0))
177 if (!g_bIgnoreErrors)
178 return FALSE;
179
180 if (!SQLQuery(_T("UPDATE config SET var_value='39' WHERE var_name='DBFormatVersion'")))
181 if (!g_bIgnoreErrors)
182 return FALSE;
183
184 return TRUE;
185 }
186
187
188 //
189 // Upgrade from V37 to V38
190 //
191
192 static BOOL H_UpgradeFromV37(void)
193 {
194 static TCHAR m_szBatch[] =
195 "CREATE INDEX idx_event_log_event_timestamp ON event_log(event_timestamp)\n"
196 "CREATE INDEX idx_syslog_msg_timestamp ON syslog(msg_timestamp)\n"
197 "CREATE INDEX idx_snmp_trap_log_trap_timestamp ON snmp_trap_log(trap_timestamp)\n"
198 "<END>";
199
200 if (!CreateTable(_T("CREATE TABLE snmp_trap_log ("
201 "trap_id $SQL:INT64 not null,"
202 "trap_timestamp integer not null,"
203 "ip_addr varchar(15) not null,"
204 "object_id integer not null,"
205 "trap_oid varchar(255) not null,"
206 "trap_varlist $SQL:TEXT not null,"
207 "PRIMARY KEY(trap_id))")))
208
209 if (!g_bIgnoreErrors)
210 return FALSE;
211
212 if (!SQLBatch(m_szBatch))
213 if (!g_bIgnoreErrors)
214 return FALSE;
215
216 if (!CreateConfigParam(_T("LogAllSNMPTraps"), _T("0"), 1, 1))
217 if (!g_bIgnoreErrors)
218 return FALSE;
219
220 if (!SQLQuery(_T("UPDATE config SET var_value='38' WHERE var_name='DBFormatVersion'")))
221 if (!g_bIgnoreErrors)
222 return FALSE;
223
224 return TRUE;
225 }
226
227
228 //
229 // Upgrade from V36 to V37
230 //
231
232 static BOOL H_UpgradeFromV36(void)
233 {
234 static TCHAR m_szBatch[] =
235 "DROP TABLE new_nodes\n"
236 "DELETE FROM config WHERE var_name='NewNodePollingInterval'\n"
237 "INSERT INTO script_library (script_id,script_name,script_code) "
238 "VALUES (1,'Filter::SNMP','sub main()#0D#0A{#0D#0A return $1->isSNMP;#0D#0A}#0D#0A')\n"
239 "INSERT INTO script_library (script_id,script_name,script_code) "
240 "VALUES (2,'Filter::Agent','sub main()#0D#0A{#0D#0A return $1->isAgent;#0D#0A}#0D#0A')\n"
241 "INSERT INTO script_library (script_id,script_name,script_code) "
242 "VALUES (3,'Filter::AgentOrSNMP','sub main()#0D#0A{#0D#0A return $1->isAgent || $1->isSNMP;#0D#0A}#0D#0A')\n"
243 "INSERT INTO script_library (script_id,script_name,script_code) "
244 "VALUES (4,'DCI::SampleTransform','sub dci_transform()#0D#0A{#0D#0A return $1 + 1;#0D#0A}#0D#0A')\n"
245 "<END>";
246
247 if (!CreateTable(_T("CREATE TABLE script_library ("
248 "script_id integer not null,"
249 "script_name varchar(63) not null,"
250 "script_code $SQL:TEXT not null,"
251 "PRIMARY KEY(script_id))")))
252 if (!g_bIgnoreErrors)
253 return FALSE;
254
255 if (!SQLBatch(m_szBatch))
256 if (!g_bIgnoreErrors)
257 return FALSE;
258
259 if (!CreateConfigParam(_T("DefaultCommunityString"), _T("public"), 1, 0))
260 if (!g_bIgnoreErrors)
261 return FALSE;
262
263 if (!CreateConfigParam(_T("DiscoveryFilter"), _T("none"), 1, 0))
264 if (!g_bIgnoreErrors)
265 return FALSE;
266
267 if (!SQLQuery(_T("UPDATE config SET var_value='37' WHERE var_name='DBFormatVersion'")))
268 if (!g_bIgnoreErrors)
269 return FALSE;
270
271 return TRUE;
272 }
273
274
275 //
276 // Upgrade from V35 to V36
277 //
278
279 static BOOL H_UpgradeFromV35(void)
280 {
281 static TCHAR m_szBatch[] =
282 "ALTER TABLE nodes ADD proxy_node integer\n"
283 "UPDATE nodes SET proxy_node=0\n"
284 "ALTER TABLE object_tools ADD matching_oid varchar(255)\n"
285 "UPDATE object_tools SET matching_oid='#00'\n"
286 "<END>";
287
288 if (!SQLBatch(m_szBatch))
289 if (!g_bIgnoreErrors)
290 return FALSE;
291
292 if (!CreateConfigParam(_T("CapabilityExpirationTime"), _T("604800"), 1, 0))
293 if (!g_bIgnoreErrors)
294 return FALSE;
295
296 if (!SQLQuery(_T("UPDATE config SET var_value='36' WHERE var_name='DBFormatVersion'")))
297 if (!g_bIgnoreErrors)
298 return FALSE;
299
300 return TRUE;
301 }
302
303
304 //
305 // Upgrade from V34 to V35
306 //
307
308 static BOOL H_UpgradeFromV34(void)
309 {
310 static TCHAR m_szBatch[] =
311 "ALTER TABLE object_properties DROP COLUMN status_alg\n"
312 "ALTER TABLE object_properties ADD status_calc_alg integer\n"
313 "ALTER TABLE object_properties ADD status_prop_alg integer\n"
314 "ALTER TABLE object_properties ADD status_fixed_val integer\n"
315 "ALTER TABLE object_properties ADD status_shift integer\n"
316 "ALTER TABLE object_properties ADD status_translation varchar(8)\n"
317 "ALTER TABLE object_properties ADD status_single_threshold integer\n"
318 "ALTER TABLE object_properties ADD status_thresholds varchar(8)\n"
319 "UPDATE object_properties SET status_calc_alg=0,status_prop_alg=0,"
320 "status_fixed_val=0,status_shift=0,status_translation='01020304',"
321 "status_single_threshold=75,status_thresholds='503C2814'\n"
322 "DELETE FROM config WHERE var_name='StatusCalculationAlgorithm'\n"
323 "<END>";
324
325 if (!SQLBatch(m_szBatch))
326 if (!g_bIgnoreErrors)
327 return FALSE;
328
329 if (!CreateConfigParam(_T("StatusCalculationAlgorithm"), _T("1"), 1, 1))
330 if (!g_bIgnoreErrors)
331 return FALSE;
332
333 if (!CreateConfigParam(_T("StatusPropagationAlgorithm"), _T("1"), 1, 1))
334 if (!g_bIgnoreErrors)
335 return FALSE;
336
337 if (!CreateConfigParam(_T("FixedStatusValue"), _T("0"), 1, 1))
338 if (!g_bIgnoreErrors)
339 return FALSE;
340
341 if (!CreateConfigParam(_T("StatusShift"), _T("0"), 1, 1))
342 if (!g_bIgnoreErrors)
343 return FALSE;
344
345 if (!CreateConfigParam(_T("StatusTranslation"), _T("01020304"), 1, 1))
346 if (!g_bIgnoreErrors)
347 return FALSE;
348
349 if (!CreateConfigParam(_T("StatusSingleThreshold"), _T("75"), 1, 1))
350 if (!g_bIgnoreErrors)
351 return FALSE;
352
353 if (!CreateConfigParam(_T("StatusThresholds"), _T("503C2814"), 1, 1))
354 if (!g_bIgnoreErrors)
355 return FALSE;
356
357 if (!SQLQuery(_T("UPDATE config SET var_value='35' WHERE var_name='DBFormatVersion'")))
358 if (!g_bIgnoreErrors)
359 return FALSE;
360
361 return TRUE;
362 }
363
364
365 //
366 // Upgrade from V33 to V34
367 //
368
369 static BOOL H_UpgradeFromV33(void)
370 {
371 static TCHAR m_szBatch[] =
372 "ALTER TABLE items ADD adv_schedule integer\n"
373 "UPDATE items SET adv_schedule=0\n"
374 "<END>";
375
376 if (!SQLBatch(m_szBatch))
377 if (!g_bIgnoreErrors)
378 return FALSE;
379
380 if (!CreateTable(_T("CREATE TABLE dci_schedules ("
381 "item_id integer not null,"
382 "schedule varchar(255) not null)")))
383 if (!g_bIgnoreErrors)
384 return FALSE;
385
386 if (!CreateTable(_T("CREATE TABLE syslog ("
387 "msg_id $SQL:INT64 not null,"
388 "msg_timestamp integer not null,"
389 "facility integer not null,"
390 "severity integer not null,"
391 "source_object_id integer not null,"
392 "hostname varchar(127) not null,"
393 "msg_tag varchar(32) not null,"
394 "msg_text $SQL:TEXT not null,"
395 "PRIMARY KEY(msg_id))")))
396 if (!g_bIgnoreErrors)
397 return FALSE;
398
399 if (!CreateConfigParam(_T("IcmpPingSize"), _T("46"), 1, 1))
400 if (!g_bIgnoreErrors)
401 return FALSE;
402
403 if (!CreateConfigParam(_T("SMSDrvConfig"), _T(""), 1, 1))
404 if (!g_bIgnoreErrors)
405 return FALSE;
406
407 if (!CreateConfigParam(_T("EnableSyslogDaemon"), _T("0"), 1, 1))
408 if (!g_bIgnoreErrors)
409 return FALSE;
410
411 if (!CreateConfigParam(_T("SyslogListenPort"), _T("514"), 1, 1))
412 if (!g_bIgnoreErrors)
413 return FALSE;
414
415 if (!CreateConfigParam(_T("SyslogRetentionTime"), _T("5184000"), 1, 0))
416 if (!g_bIgnoreErrors)
417 return FALSE;
418
419 if (!SQLQuery(_T("UPDATE config SET var_value='34' WHERE var_name='DBFormatVersion'")))
420 if (!g_bIgnoreErrors)
421 return FALSE;
422
423 return TRUE;
424 }
425
426
427 //
428 // Upgrade from V32 to V33
429 //
430
431 static BOOL H_UpgradeFromV32(void)
432 {
433 static TCHAR m_szBatch[] =
434 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
435 "VALUES (5,'&Info->&Switch forwarding database (FDB)',2 ,'Forwarding database',0,'Show switch forwarding database')\n"
436 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
437 "VALUES (5,0,'MAC Address','.1.3.6.1.2.1.17.4.3.1.1',4 ,0)\n"
438 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
439 "VALUES (5,1,'Port','.1.3.6.1.2.1.17.4.3.1.2',1 ,0)\n"
440 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
441 "VALUES (6,'&Connect->Open &web browser',4 ,'http://%OBJECT_IP_ADDR%',0,'Open embedded web browser to node')\n"
442 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
443 "VALUES (7,'&Connect->Open &web browser (HTTPS)',4 ,'https://%OBJECT_IP_ADDR%',0,'Open embedded web browser to node using HTTPS')\n"
444 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
445 "VALUES (8,'&Info->&Agent->&Subagent list',3 ,'Subagent List#7FAgent.SubAgentList#7F^(.*) (.*) (.*) (.*)',0,'Show list of loaded subagents')\n"
446 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
447 "VALUES (8,0,'Name','',0 ,1)\n"
448 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
449 "VALUES (8,1,'Version','',0 ,2)\n"
450 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
451 "VALUES (8,2,'File','',0 ,4)\n"
452 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
453 "VALUES (8,3,'Module handle','',0 ,3)\n"
454 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
455 "VALUES (9,'&Info->&Agent->Supported &parameters',3 ,'Supported parameters#7FAgent.SupportedParameters#7F^(.*)',0,'Show list of parameters supported by agent')\n"
456 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
457 "VALUES (9,0,'Parameter','',0 ,1)\n"
458 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
459 "VALUES (10,'&Info->&Agent->Supported &enums',3 ,'Supported enums#7FAgent.SupportedEnums#7F^(.*)',0,'Show list of enums supported by agent')\n"
460 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
461 "VALUES (10,0,'Parameter','',0 ,1)\n"
462 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
463 "VALUES (11,'&Info->&Agent->Supported &actions',3 ,'Supported actions#7FAgent.ActionList#7F^(.*) (.*) #22(.*)#22.*',0,'Show list of actions supported by agent')\n"
464 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
465 "VALUES (11,0,'Name','',0 ,1)\n"
466 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
467 "VALUES (11,1,'Type','',0 ,2)\n"
468 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
469 "VALUES (11,2,'Data','',0 ,3)\n"
470 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
471 "VALUES (12,'&Info->&Agent->Configured &ICMP targets',3 ,'Configured ICMP targets#7FICMP.TargetList#7F^(.*) (.*) (.*) (.*)',0,'Show list of actions supported by agent')\n"
472 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
473 "VALUES (12,0,'IP Address','',0 ,1)\n"
474 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
475 "VALUES (12,1,'Name','',0 ,4)\n"
476 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
477 "VALUES (12,2,'Last RTT','',0 ,2)\n"
478 "INSERT INTO object_tools_table_columns (tool_id,col_number,col_name,col_oid,col_format,col_substr) "
479 "VALUES (12,4,'Average RTT','',0 ,3)\n"
480 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (5,-2147483648)\n"
481 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (6,-2147483648)\n"
482 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (7,-2147483648)\n"
483 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (8,-2147483648)\n"
484 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (9,-2147483648)\n"
485 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (10,-2147483648)\n"
486 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (11,-2147483648)\n"
487 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (12,-2147483648)\n"
488 "<END>";
489
490 if (!CreateTable(_T("CREATE TABLE object_tools_table_columns ("
491 "tool_id integer not null,"
492 "col_number integer not null,"
493 "col_name varchar(255),"
494 "col_oid varchar(255),"
495 "col_format integer,"
496 "col_substr integer,"
497 "PRIMARY KEY(tool_id,col_number))")))
498 if (!g_bIgnoreErrors)
499 return FALSE;
500
501 if (!SQLBatch(m_szBatch))
502 if (!g_bIgnoreErrors)
503 return FALSE;
504
505 if (!SQLQuery(_T("UPDATE config SET var_value='33' WHERE var_name='DBFormatVersion'")))
506 if (!g_bIgnoreErrors)
507 return FALSE;
508
509 return TRUE;
510 }
511
512
513 //
514 // Upgrade from V31 to V32
515 //
516
517 static BOOL H_UpgradeFromV31(void)
518 {
519 static TCHAR m_szBatch[] =
520 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
521 "VALUES (1,'&Shutdown system',1 ,'System.Shutdown',0,'Shutdown target node via NetXMS agent')\n"
522 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
523 "VALUES (2,'&Restart system',1 ,'System.Restart',0,'Restart target node via NetXMS agent')\n"
524 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
525 "VALUES (3,'&Wakeup node',0 ,'wakeup',0,'Wakeup node using Wake-On-LAN magic packet')\n"
526 "INSERT INTO object_tools (tool_id,tool_name,tool_type,tool_data,flags,description) "
527 "VALUES (4,'Restart &agent',1 ,'Agent.Restart',0,'Restart NetXMS agent on target node')\n"
528 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (1,-2147483648)\n"
529 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (2,-2147483648)\n"
530 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (3,-2147483648)\n"
531 "INSERT INTO object_tools_acl (tool_id,user_id) VALUES (4,-2147483648)\n"
532 "<END>";
533
534 if (!CreateTable(_T("CREATE TABLE object_tools ("
535 "tool_id integer not null,"
536 "tool_name varchar(255) not null,"
537 "tool_type integer not null,"
538 "tool_data $SQL:TEXT,"
539 "description varchar(255),"
540 "flags integer not null,"
541 "PRIMARY KEY(tool_id))")))
542 if (!g_bIgnoreErrors)
543 return FALSE;
544
545 if (!CreateTable(_T("CREATE TABLE object_tools_acl ("
546 "tool_id integer not null,"
547 "user_id integer not null,"
548 "PRIMARY KEY(tool_id,user_id))")))
549 if (!g_bIgnoreErrors)
550 return FALSE;
551
552 if (!SQLBatch(m_szBatch))
553 if (!g_bIgnoreErrors)
554 return FALSE;
555
556 if (!SQLQuery(_T("UPDATE config SET var_value='32' WHERE var_name='DBFormatVersion'")))
557 if (!g_bIgnoreErrors)
558 return FALSE;
559
560 return TRUE;
561 }
562
563
564 //
565 // Upgrade from V30 to V31
566 //
567
568 static BOOL H_UpgradeFromV30(void)
569 {
570 static TCHAR m_szBatch[] =
571 "INSERT INTO default_images (object_class,image_id) "
572 "VALUES (12, 14)\n"
573 "INSERT INTO images (image_id,name,file_name_png,file_hash_png,"
574 "file_name_ico,file_hash_ico) VALUES (14,'Obj.VPNConnector',"
575 "'vpnc.png','<invalid_hash>','vpnc.ico','<invalid_hash>')\n"
576 "<END>";
577
578 if (!SQLBatch(m_szBatch))
579 if (!g_bIgnoreErrors)
580 return FALSE;
581
582 if (!CreateTable(_T("CREATE TABLE vpn_connectors ("
583 "id integer not null,"
584 "node_id integer not null,"
585 "peer_gateway integer not null,"
586 "PRIMARY KEY(id))")))
587 if (!g_bIgnoreErrors)
588 return FALSE;
589
590 if (!CreateTable(_T("CREATE TABLE vpn_connector_networks ("
591 "vpn_id integer not null,"
592 "network_type integer not null,"
593 "ip_addr varchar(15) not null,"
594 "ip_netmask varchar(15) not null,"
595 "PRIMARY KEY(vpn_id,ip_addr))")))
596 if (!g_bIgnoreErrors)
597 return FALSE;
598
599 if (!CreateConfigParam(_T("NumberOfRoutingTablePollers"), _T("5"), 1, 1))
600 if (!g_bIgnoreErrors)
601 return FALSE;
602
603 if (!CreateConfigParam(_T("RoutingTableUpdateInterval"), _T("300"), 1, 1))
604 if (!g_bIgnoreErrors)
605 return FALSE;
606
607 if (!SQLQuery(_T("UPDATE config SET var_value='31' WHERE var_name='DBFormatVersion'")))
608 if (!g_bIgnoreErrors)
609 return FALSE;
610
611 return TRUE;
612 }
613
614
615 //
616 // Upgrade from V29 to V30
617 //
618
619 static BOOL H_UpgradeFromV29(void)
620 {
621 static TCHAR m_szBatch[] =
622 "ALTER TABLE object_properties ADD status_alg integer\n"
623 "UPDATE object_properties SET status_alg=-1\n"
624 "<END>";
625
626 if (!SQLBatch(m_szBatch))
627 if (!g_bIgnoreErrors)
628 return FALSE;
629
630 if (!CreateConfigParam(_T("StatusCalculationAlgorithm"), _T("0"), 1, 1))
631 if (!g_bIgnoreErrors)
632 return FALSE;
633
634 if (!CreateConfigParam(_T("EnableMultipleDBConnections"), _T("1"), 1, 1))
635 if (!g_bIgnoreErrors)
636 return FALSE;
637
638 if (!CreateConfigParam(_T("NumberOfDatabaseWriters"), _T("1"), 1, 1))
639 if (!g_bIgnoreErrors)
640 return FALSE;
641
642 if (!CreateConfigParam(_T("DefaultEncryptionPolicy"), _T("1"), 1, 1))
643 if (!g_bIgnoreErrors)
644 return FALSE;
645
646 if (!CreateConfigParam(_T("AllowedCiphers"), _T("15"), 1, 1))
647 if (!g_bIgnoreErrors)
648 return FALSE;
649
650 if (!CreateConfigParam(_T("KeepAliveInterval"), _T("60"), 1, 1))
651 if (!g_bIgnoreErrors)
652 return FALSE;
653
654 if (!SQLQuery(_T("UPDATE config SET var_value='30' WHERE var_name='DBFormatVersion'")))
655 if (!g_bIgnoreErrors)
656 return FALSE;
657
658 return TRUE;
659 }
660
661
662 //
663 // Upgrade from V28 to V29
664 //
665
666 static BOOL H_UpgradeFromV28(void)
667 {
668 static TCHAR m_szBatch[] =
669 "ALTER TABLE nodes ADD zone_guid integer\n"
670 "ALTER TABLE subnets ADD zone_guid integer\n"
671 "UPDATE nodes SET zone_guid=0\n"
672 "UPDATE subnets SET zone_guid=0\n"
673 "INSERT INTO default_images (object_class,image_id) VALUES (6,13)\n"
674 "INSERT INTO images (image_id,name,file_name_png,file_hash_png,"
675 "file_name_ico,file_hash_ico) VALUES (13,'Obj.Zone','zone.png',"
676 "'<invalid_hash>','zone.ico','<invalid_hash>')\n"
677 "<END>";
678
679 if (!CreateTable(_T("CREATE TABLE zones ("
680 "id integer not null,"
681 "zone_guid integer not null,"
682 "zone_type integer not null,"
683 "controller_ip varchar(15) not null,"
684 "description $SQL:TEXT,"
685 "PRIMARY KEY(id))")))
686 if (!g_bIgnoreErrors)
687 return FALSE;
688
689 if (!CreateTable(_T("CREATE TABLE zone_ip_addr_list ("
690 "zone_id integer not null,"
691 "ip_addr varchar(15) not null,"
692 "PRIMARY KEY(zone_id,ip_addr))")))
693 if (!g_bIgnoreErrors)
694 return FALSE;
695
696 if (!SQLBatch(m_szBatch))
697 if (!g_bIgnoreErrors)
698 return FALSE;
699
700 if (!CreateConfigParam(_T("EnableZoning"), _T("0"), 1, 1))
701 if (!g_bIgnoreErrors)
702 return FALSE;
703
704 if (!SQLQuery(_T("UPDATE config SET var_value='29' WHERE var_name='DBFormatVersion'")))
705 if (!g_bIgnoreErrors)
706 return FALSE;
707
708 return TRUE;
709 }
710
711
712 //
713 // Upgrade from V27 to V28
714 //
715
716 static BOOL H_UpgradeFromV27(void)
717 {
718 static TCHAR m_szBatch[] =
719 "ALTER TABLE users ADD system_access integer\n"
720 "UPDATE users SET system_access=access\n"
721 "ALTER TABLE users DROP COLUMN access\n"
722 "ALTER TABLE user_groups ADD system_access integer\n"
723 "UPDATE user_groups SET system_access=access\n"
724 "ALTER TABLE user_groups DROP COLUMN access\n"
725 "<END>";
726
727 if (!SQLBatch(m_szBatch))
728 if (!g_bIgnoreErrors)
729 return FALSE;
730
731 if (!SQLQuery(_T("UPDATE config SET var_value='28' WHERE var_name='DBFormatVersion'")))
732 if (!g_bIgnoreErrors)
733 return FALSE;
734
735 return TRUE;
736 }
737
738
739 //
740 // Move object data from class-specific tables to object_prioperties table
741 //
742
743 static BOOL MoveObjectData(DWORD dwId, BOOL bInheritRights)
744 {
745 DB_RESULT hResult;
746 TCHAR szQuery[1024] ,szName[MAX_OBJECT_NAME];
747 BOOL bRead = FALSE, bIsDeleted, bIsTemplate;
748 DWORD i, dwStatus, dwImageId;
749 static TCHAR *m_pszTableNames[] = { _T("nodes"), _T("interfaces"), _T("subnets"),
750 _T("templates"), _T("network_services"),
751 _T("containers"), NULL };
752
753 // Try to read information from nodes table
754 for(i = 0; (!bRead) && (m_pszTableNames[i] != NULL); i++)
755 {
756 bIsTemplate = !_tcscmp(m_pszTableNames[i], _T("templates"));
757 _sntprintf(szQuery, 1024, _T("SELECT name,is_deleted,image_id%s FROM %s WHERE id=%d"),
758 bIsTemplate ? _T("") : _T(",status"),
759 m_pszTableNames[i], dwId);
760 hResult = SQLSelect(szQuery);
761 if (hResult != NULL)
762 {
763 if (DBGetNumRows(hResult) > 0)
764 {
765 nx_strncpy(szName, DBGetField(hResult, 0, 0), MAX_OBJECT_NAME);
766 bIsDeleted = DBGetFieldLong(hResult, 0, 1) ? TRUE : FALSE;
767 dwImageId = DBGetFieldULong(hResult, 0, 2);
768 dwStatus = bIsTemplate ? STATUS_UNKNOWN : DBGetFieldULong(hResult, 0, 3);
769 bRead = TRUE;
770 }
771 DBFreeResult(hResult);
772 }
773 else
774 {
775 if (!g_bIgnoreErrors)
776 return FALSE;
777 }
778 }
779
780 if (bRead)
781 {
782 _sntprintf(szQuery, 1024, _T("INSERT INTO object_properties (object_id,name,"
783 "status,is_deleted,image_id,inherit_access_rights,"
784 "last_modified) VALUES (%d,'%s',%d,%d,%d,%d,%ld)"),
785 dwId, szName, dwStatus, bIsDeleted, dwImageId, bInheritRights, time(NULL));
786
787 if (!SQLQuery(szQuery))
788 if (!g_bIgnoreErrors)
789 return FALSE;
790 }
791 else
792 {
793 _tprintf(_T("WARNING: object with ID %d presented in access control tables but cannot be found in data tables\n"), dwId);
794 }
795
796 return TRUE;
797 }
798
799
800 //
801 // Upgrade from V26 to V27
802 //
803
804 static BOOL H_UpgradeFromV26(void)
805 {
806 DB_RESULT hResult;
807 DWORD i, dwNumObjects, dwId;
808 static TCHAR m_szBatch[] =
809 "ALTER TABLE nodes DROP COLUMN name\n"
810 "ALTER TABLE nodes DROP COLUMN status\n"
811 "ALTER TABLE nodes DROP COLUMN is_deleted\n"
812 "ALTER TABLE nodes DROP COLUMN image_id\n"
813 "ALTER TABLE interfaces DROP COLUMN name\n"
814 "ALTER TABLE interfaces DROP COLUMN status\n"
815 "ALTER TABLE interfaces DROP COLUMN is_deleted\n"
816 "ALTER TABLE interfaces DROP COLUMN image_id\n"
817 "ALTER TABLE subnets DROP COLUMN name\n"
818 "ALTER TABLE subnets DROP COLUMN status\n"
819 "ALTER TABLE subnets DROP COLUMN is_deleted\n"
820 "ALTER TABLE subnets DROP COLUMN image_id\n"
821 "ALTER TABLE network_services DROP COLUMN name\n"
822 "ALTER TABLE network_services DROP COLUMN status\n"
823 "ALTER TABLE network_services DROP COLUMN is_deleted\n"
824 "ALTER TABLE network_services DROP COLUMN image_id\n"
825 "ALTER TABLE containers DROP COLUMN name\n"
826 "ALTER TABLE containers DROP COLUMN status\n"
827 "ALTER TABLE containers DROP COLUMN is_deleted\n"
828 "ALTER TABLE containers DROP COLUMN image_id\n"
829 "ALTER TABLE templates DROP COLUMN name\n"
830 "ALTER TABLE templates DROP COLUMN is_deleted\n"
831 "ALTER TABLE templates DROP COLUMN image_id\n"
832 "DROP TABLE access_options\n"
833 "DELETE FROM config WHERE var_name='TopologyRootObjectName'\n"
834 "DELETE FROM config WHERE var_name='TopologyRootImageId'\n"
835 "DELETE FROM config WHERE var_name='ServiceRootObjectName'\n"
836 "DELETE FROM config WHERE var_name='ServiceRootImageId'\n"
837 "DELETE FROM config WHERE var_name='TemplateRootObjectName'\n"
838 "DELETE FROM config WHERE var_name='TemplateRootImageId'\n"
839 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) "
840 "VALUES (31,'SYS_SNMP_OK',0,1,'Connectivity with SNMP agent restored',"
841 "'Generated when connectivity with node#27s SNMP agent restored.#0D#0A"
842 "Parameters:#0D#0A No message-specific parameters')\n"
843 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) "
844 "VALUES (32,'SYS_AGENT_OK',0,1,'Connectivity with native agent restored',"
845 "'Generated when connectivity with node#27s native agent restored.#0D#0A"
846 "Parameters:#0D#0A No message-specific parameters')\n"
847 "<END>";
848
849 if (!CreateTable(_T("CREATE TABLE object_properties ("
850 "object_id integer not null,"
851 "name varchar(63) not null,"
852 "status integer not null,"
853 "is_deleted integer not null,"
854 "image_id integer,"
855 "last_modified integer not null,"
856 "inherit_access_rights integer not null,"
857 "PRIMARY KEY(object_id))")))
858 if (!g_bIgnoreErrors)
859 return FALSE;
860
861 if (!CreateTable(_T("CREATE TABLE user_profiles ("
862 "user_id integer not null,"
863 "var_name varchar(255) not null,"
864 "var_value $SQL:TEXT,"
865 "PRIMARY KEY(user_id,var_name))")))
866 if (!g_bIgnoreErrors)
867 return FALSE;
868
869 // Move data from access_options and class-specific tables to object_properties
870 hResult = SQLSelect(_T("SELECT object_id,inherit_rights FROM access_options"));
871 if (hResult != NULL)
872 {
873 dwNumObjects = DBGetNumRows(hResult);
874 for(i = 0; i < dwNumObjects; i++)
875 {
876 dwId = DBGetFieldULong(hResult, i, 0);
877 if (dwId >= 10) // Id below 10 reserved for built-in objects
878 {
879 if (!MoveObjectData(dwId, DBGetFieldLong(hResult, i, 1) ? TRUE : FALSE))
880 {
881 DBFreeResult(hResult);
882 return FALSE;
883 }
884 }
885 else
886 {
887 TCHAR szName[MAX_OBJECT_NAME], szQuery[1024];
888 DWORD dwImageId;
889 BOOL bValidObject = TRUE;
890
891 switch(dwId)
892 {
893 case 1: // Topology Root
894 ConfigReadStr(_T("TopologyRootObjectName"), szName,
895 MAX_OBJECT_NAME, _T("Entire Network"));
896 dwImageId = ConfigReadULong(_T("TopologyRootImageId"), 0);
897 break;
898 case 2: // Service Root
899 ConfigReadStr(_T("ServiceRootObjectName"), szName,
900 MAX_OBJECT_NAME, _T("All Services"));
901 dwImageId = ConfigReadULong(_T("ServiceRootImageId"), 0);
902 break;
903 case 3: // Template Root
904 ConfigReadStr(_T("TemplateRootObjectName"), szName,
905 MAX_OBJECT_NAME, _T("All Services"));
906 dwImageId = ConfigReadULong(_T("TemplateRootImageId"), 0);
907 break;
908 default:
909 bValidObject = FALSE;
910 break;
911 }
912
913 if (bValidObject)
914 {
915 _sntprintf(szQuery, 1024, _T("INSERT INTO object_properties (object_id,name,"
916 "status,is_deleted,image_id,inherit_access_rights,"
917 "last_modified) VALUES (%d,'%s',5,0,%d,%d,%ld)"),
918 dwId, szName, dwImageId,
919 DBGetFieldLong(hResult, i, 1) ? TRUE : FALSE,
920 time(NULL));
921
922 if (!SQLQuery(szQuery))
923 if (!g_bIgnoreErrors)
924 return FALSE;
925 }
926 else
927 {
928 _tprintf(_T("WARNING: Invalid built-in object ID %d\n"), dwId);
929 }
930 }
931 }
932 DBFreeResult(hResult);
933 }
934 else
935 {
936 if (!g_bIgnoreErrors)
937 return FALSE;
938 }
939
940 if (!SQLBatch(m_szBatch))
941 if (!g_bIgnoreErrors)
942 return FALSE;
943
944 if (!SQLQuery(_T("UPDATE config SET var_value='27' WHERE var_name='DBFormatVersion'")))
945 if (!g_bIgnoreErrors)
946 return FALSE;
947
948 return TRUE;
949 }
950
951
952 //
953 // Upgrade from V25 to V26
954 //
955
956 static BOOL H_UpgradeFromV25(void)
957 {
958 DB_RESULT hResult;
959
960 hResult = SQLSelect(_T("SELECT var_value FROM config WHERE var_name='IDataIndexCreationCommand'"));
961 if (hResult != NULL)
962 {
963 if (DBGetNumRows(hResult) > 0)
964 {
965 if (!CreateConfigParam(_T("IDataIndexCreationCommand_0"), DBGetField(hResult, 0, 0), 0, 1))
966 {
967 if (!g_bIgnoreErrors)
968 {
969 DBFreeResult(hResult);
970 return FALSE;
971 }
972 }
973 }
974 DBFreeResult(hResult);
975
976 if (!SQLQuery(_T("DELETE FROM config WHERE var_name='IDataIndexCreationCommand'")))
977 if (!g_bIgnoreErrors)
978 return FALSE;
979 }
980
981 if (!CreateConfigParam(_T("IDataIndexCreationCommand_1"),
982 _T("CREATE INDEX idx_timestamp ON idata_%d(idata_timestamp)"), 0, 1))
983 if (!g_bIgnoreErrors)
984 return FALSE;
985
986 if (!SQLQuery(_T("UPDATE config SET var_value='26' WHERE var_name='DBFormatVersion'")))
987 if (!g_bIgnoreErrors)
988 return FALSE;
989
990 return TRUE;
991 }
992
993
994 //
995 // Upgrade from V24 to V25
996 //
997
998 static BOOL H_UpgradeFromV24(void)
999 {
1000 DB_RESULT hResult;
1001 int i, iNumRows;
1002 DWORD dwNodeId;
1003 TCHAR szQuery[256];
1004
1005 _tprintf("Create indexes on existing IDATA tables? (Y/N) ");
1006 if (GetYesNo())
1007 {
1008 hResult = SQLSelect(_T("SELECT id FROM nodes WHERE is_deleted=0"));
1009 if (hResult != NULL)
1010 {
1011 iNumRows = DBGetNumRows(hResult);
1012 for(i = 0; i < iNumRows; i++)
1013 {
1014 dwNodeId = DBGetFieldULong(hResult, i, 0);
1015 _tprintf(_T("Creating indexes for table \"idata_%d\"...\n"), dwNodeId);
1016 _sntprintf(szQuery, 256, _T("CREATE INDEX idx_timestamp ON idata_%d(idata_timestamp)"), dwNodeId);
1017 if (!SQLQuery(szQuery))
1018 if (!g_bIgnoreErrors)
1019 {
1020 DBFreeResult(hResult);
1021 return FALSE;
1022 }
1023 }
1024 DBFreeResult(hResult);
1025 }
1026 else
1027 {
1028 if (!g_bIgnoreErrors)
1029 return FALSE;
1030 }
1031 }
1032
1033 if (!SQLQuery(_T("UPDATE config SET var_value='25' WHERE var_name='DBFormatVersion'")))
1034 if (!g_bIgnoreErrors)
1035 return FALSE;
1036
1037 return TRUE;
1038 }
1039
1040
1041 //
1042 // Upgrade from V23 to V24
1043 //
1044
1045 static BOOL H_UpgradeFromV23(void)
1046 {
1047 DB_RESULT hResult;
1048 TCHAR szQuery[256];
1049 int i, iNumRows;
1050
1051 if (!CreateTable(_T("CREATE TABLE raw_dci_values ("
1052 " item_id integer not null,"
1053 " raw_value varchar(255),"
1054 " last_poll_time integer,"
1055 " PRIMARY KEY(item_id))")))
1056 if (!g_bIgnoreErrors)
1057 return FALSE;
1058
1059 if (!SQLQuery(_T("CREATE INDEX idx_item_id ON raw_dci_values(item_id)")))
1060 if (!g_bIgnoreErrors)
1061 return FALSE;
1062
1063
1064 // Create empty records in raw_dci_values for all existing DCIs
1065 hResult = SQLSelect(_T("SELECT item_id FROM items"));
1066 if (hResult != NULL)
1067 {
1068 iNumRows = DBGetNumRows(hResult);
1069 for(i = 0; i < iNumRows; i++)
1070 {
1071 _stprintf(szQuery, _T("INSERT INTO raw_dci_values (item_id,"
1072 "raw_value,last_poll_time) VALUES (%d,'#00',1)"),
1073 DBGetFieldULong(hResult, i, 0));
1074 if (!SQLQuery(szQuery))
1075 if (!g_bIgnoreErrors)
1076 {
1077 DBFreeResult(hResult);
1078 return FALSE;
1079 }
1080 }
1081 DBFreeResult(hResult);
1082 }
1083 else
1084 {
1085 if (!g_bIgnoreErrors)
1086 return FALSE;
1087 }
1088
1089 if (!SQLQuery(_T("UPDATE config SET var_value='24' WHERE var_name='DBFormatVersion'")))
1090 if (!g_bIgnoreErrors)
1091 return FALSE;
1092
1093 return TRUE;
1094 }
1095
1096
1097 //
1098 // Upgrade from V22 to V23
1099 //
1100
1101 static BOOL H_UpgradeFromV22(void)
1102 {
1103 static TCHAR m_szBatch[] =
1104 "ALTER TABLE items ADD template_item_id integer\n"
1105 "UPDATE items SET template_item_id=0\n"
1106 "CREATE INDEX idx_sequence ON thresholds(sequence_number)\n"
1107 "<END>";
1108
1109 if (!SQLBatch(m_szBatch))
1110 if (!g_bIgnoreErrors)
1111 return FALSE;
1112
1113 if (!SQLQuery(_T("UPDATE config SET var_value='23' WHERE var_name='DBFormatVersion'")))
1114 if (!g_bIgnoreErrors)
1115 return FALSE;
1116
1117 return TRUE;
1118 }
1119
1120
1121 //
1122 // Upgrade from V21 to V22
1123 //
1124
1125 static BOOL H_UpgradeFromV21(void)
1126 {
1127 static TCHAR m_szBatch[] =
1128 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) "
1129 "VALUES (4009,'DC_HDD_TEMP_WARNING',1,1,"
1130 "'Temperature of hard disk %6 is above warning level of %3 (current: %4)',"
1131 "'Custom data collection threshold event.#0D#0AParameters:#0D#0A"
1132 " 1) Parameter name#0D#0A 2) Item description#0D#0A"
1133 " 3) Threshold value#0D#0A 4) Actual value#0D#0A"
1134 " 5) Data collection item ID#0D#0A 6) Instance')\n"
1135 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) "
1136 "VALUES (4010,'DC_HDD_TEMP_MAJOR',3,1,"
1137 "'Temperature of hard disk %6 is above %3 (current: %4)',"
1138 "'Custom data collection threshold event.#0D#0AParameters:#0D#0A"
1139 " 1) Parameter name#0D#0A 2) Item description#0D#0A"
1140 " 3) Threshold value#0D#0A 4) Actual value#0D#0A"
1141 " 5) Data collection item ID#0D#0A 6) Instance')\n"
1142 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description) "
1143 "VALUES (4011,'DC_HDD_TEMP_CRITICAL',4,1,"
1144 "'Temperature of hard disk %6 is above critical level of %3 (current: %4)',"
1145 "'Custom data collection threshold event.#0D#0AParameters:#0D#0A"
1146 " 1) Parameter name#0D#0A 2) Item description#0D#0A"
1147 " 3) Threshold value#0D#0A 4) Actual value#0D#0A"
1148 " 5) Data collection item ID#0D#0A 6) Instance')\n"
1149 "<END>";
1150
1151 if (!SQLBatch(m_szBatch))
1152 if (!g_bIgnoreErrors)
1153 return FALSE;
1154
1155 if (!CreateConfigParam(_T("SMSDriver"), _T("<none>"), 1, 1))
1156 if (!g_bIgnoreErrors)
1157 return FALSE;
1158
1159 if (!CreateConfigParam(_T("AgentUpgradeWaitTime"), _T("600"), 1, 0))
1160 if (!g_bIgnoreErrors)
1161 return FALSE;
1162
1163 if (!CreateConfigParam(_T("NumberOfUpgradeThreads"), _T("10"), 1, 0))
1164 if (!g_bIgnoreErrors)
1165 return FALSE;
1166
1167 if (!SQLQuery(_T("UPDATE config SET var_value='22' WHERE var_name='DBFormatVersion'")))
1168 if (!g_bIgnoreErrors)
1169 return FALSE;
1170
1171 return TRUE;
1172 }
1173
1174
1175 //
1176 // Upgrade from V20 to V21
1177 //
1178
1179 static BOOL H_UpgradeFromV20(void)
1180 {
1181 static TCHAR m_szBatch[] =
1182 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description)"
1183 " VALUES (30,'SYS_SMS_FAILURE',1,1,'Unable to send SMS to phone %1',"
1184 "'Generated when server is unable to send SMS.#0D#0A"
1185 " Parameters:#0D#0A 1) Phone number')\n"
1186 "ALTER TABLE nodes ADD node_flags integer\n"
1187 "<END>";
1188 static TCHAR m_szBatch2[] =
1189 "ALTER TABLE nodes DROP COLUMN is_snmp\n"
1190 "ALTER TABLE nodes DROP COLUMN is_agent\n"
1191 "ALTER TABLE nodes DROP COLUMN is_bridge\n"
1192 "ALTER TABLE nodes DROP COLUMN is_router\n"
1193 "ALTER TABLE nodes DROP COLUMN is_local_mgmt\n"
1194 "ALTER TABLE nodes DROP COLUMN is_ospf\n"
1195 "CREATE INDEX idx_item_id ON thresholds(item_id)\n"
1196 "<END>";
1197 static DWORD m_dwFlag[] = { NF_IS_SNMP, NF_IS_NATIVE_AGENT, NF_IS_BRIDGE,
1198 NF_IS_ROUTER, NF_IS_LOCAL_MGMT, NF_IS_OSPF };
1199 DB_RESULT hResult;
1200 int i, j, iNumRows;
1201 DWORD dwFlags, dwNodeId;
1202 TCHAR szQuery[256];
1203
1204 if (!SQLBatch(m_szBatch))
1205 if (!g_bIgnoreErrors)
1206 return FALSE;
1207
1208 // Convert "is_xxx" fields into one "node_flags" field
1209 hResult = SQLSelect(_T("SELECT id,is_snmp,is_agent,is_bridge,is_router,"
1210 "is_local_mgmt,is_ospf FROM nodes"));
1211 if (hResult != NULL)
1212 {
1213 iNumRows = DBGetNumRows(hResult);
1214 for(i = 0; i < iNumRows; i++)
1215 {
1216 dwFlags = 0;
1217 for(j = 1; j <= 6; j++)
1218 if (DBGetFieldLong(hResult, i, j))
1219 dwFlags |= m_dwFlag[j - 1];
1220 _sntprintf(szQuery, 256, _T("UPDATE nodes SET node_flags=%d WHERE id=%d"),
1221 dwFlags, DBGetFieldULong(hResult, i, 0));
1222 if (!SQLQuery(szQuery))
1223 if (!g_bIgnoreErrors)
1224 {
1225 DBFreeResult(hResult);
1226 return FALSE;
1227 }
1228 }
1229 DBFreeResult(hResult);
1230 }
1231 else
1232 {
1233 if (!g_bIgnoreErrors)
1234 return FALSE;
1235 }
1236
1237 if (!SQLBatch(m_szBatch2))
1238 if (!g_bIgnoreErrors)
1239 return FALSE;
1240
1241 _tprintf("Create indexes on existing IDATA tables? (Y/N) ");
1242 if (GetYesNo())
1243 {
1244 hResult = SQLSelect(_T("SELECT id FROM nodes WHERE is_deleted=0"));
1245 if (hResult != NULL)
1246 {
1247 iNumRows = DBGetNumRows(hResult);
1248 for(i = 0; i < iNumRows; i++)
1249 {
1250 dwNodeId = DBGetFieldULong(hResult, i, 0);
1251 _tprintf(_T("Creating indexes for table \"idata_%d\"...\n"), dwNodeId);
1252 _sntprintf(szQuery, 256, _T("CREATE INDEX idx_item_id ON idata_%d(item_id)"), dwNodeId);
1253 if (!SQLQuery(szQuery))
1254 if (!g_bIgnoreErrors)
1255 {
1256 DBFreeResult(hResult);
1257 return FALSE;
1258 }
1259 }
1260 DBFreeResult(hResult);
1261 }
1262 else
1263 {
1264 if (!g_bIgnoreErrors)
1265 return FALSE;
1266 }
1267 }
1268
1269 if (!CreateConfigParam(_T("NumberOfStatusPollers"), _T("10"), 1, 1))
1270 if (!g_bIgnoreErrors)
1271 return FALSE;
1272 if (!CreateConfigParam(_T("NumberOfConfigurationPollers"), _T("4"), 1, 1))
1273 if (!g_bIgnoreErrors)
1274 return FALSE;
1275 if (!CreateConfigParam(_T("IDataIndexCreationCommand"), _T("CREATE INDEX idx_item_id ON idata_%d(item_id)"), 0, 1))
1276 if (!g_bIgnoreErrors)
1277 return FALSE;
1278
1279 if (!SQLQuery(_T("UPDATE config SET var_value='21' WHERE var_name='DBFormatVersion'")))
1280 if (!g_bIgnoreErrors)
1281 return FALSE;
1282
1283 return TRUE;
1284 }
1285
1286
1287 //
1288 // Upgrade from V19 to V20
1289 //
1290
1291 static BOOL H_UpgradeFromV19(void)
1292 {
1293 static TCHAR m_szBatch[] =
1294 "ALTER TABLE nodes ADD poller_node_id integer\n"
1295 "ALTER TABLE nodes ADD is_ospf integer\n"
1296 "UPDATE nodes SET poller_node_id=0,is_ospf=0\n"
1297 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description)"
1298 " VALUES (28,'SYS_NODE_DOWN',4,1,'Node down',"
1299 "'Generated when node is not responding to management server.#0D#0A"
1300 "Parameters:#0D#0A No event-specific parameters')\n"
1301 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description)"
1302 " VALUES (29,'SYS_NODE_UP',0,1,'Node up',"
1303 "'Generated when communication with the node re-established.#0D#0A"
1304 "Parameters:#0D#0A No event-specific parameters')\n"
1305 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description)"
1306 " VALUES (25,'SYS_SERVICE_DOWN',3,1,'Network service #22%1#22 is not responding',"
1307 "'Generated when network service is not responding to management server as "
1308 "expected.#0D#0AParameters:#0D#0A 1) Service name#0D0A"
1309 " 2) Service object ID#0D0A 3) Service type')\n"
1310 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description)"
1311 " VALUES (26,'SYS_SERVICE_UP',0,1,"
1312 "'Network service #22%1#22 returned to operational state',"
1313 "'Generated when network service responds as expected after failure.#0D#0A"
1314 "Parameters:#0D#0A 1) Service name#0D0A"
1315 " 2) Service object ID#0D0A 3) Service type')\n"
1316 "INSERT INTO event_cfg (event_code,event_name,severity,flags,message,description)"
1317 " VALUES (27,'SYS_SERVICE_UNKNOWN',1,1,"
1318 "'Status of network service #22%1#22 is unknown',"
1319 "'Generated when management server is unable to determine state of the network "
1320 "service due to agent or server-to-agent communication failure.#0D#0A"
1321 "Parameters:#0D#0A 1) Service name#0D0A"
1322 " 2) Service object ID#0D0A 3) Service type')\n"
1323 "INSERT INTO images (image_id,name,file_name_png,file_hash_png,file_name_ico,"
1324 "file_hash_ico) VALUES (12,'Obj.NetworkService','network_service.png',"
1325 "'<invalid_hash>','network_service.ico','<invalid_hash>')\n"
1326 "INSERT INTO default_images (object_class,image_id) VALUES (11,12)\n"
1327 "<END>";
1328
1329 if (!SQLBatch(m_szBatch))
1330 if (!g_bIgnoreErrors)
1331 return FALSE;
1332
1333 if (!CreateTable(_T("CREATE TABLE network_services ("
1334 "id integer not null,"
1335 "name varchar(63),"
1336 "status integer,"
1337 "is_deleted integer,"
1338 "node_id integer not null,"
1339 "service_type integer,"
1340 "ip_bind_addr varchar(15),"
1341 "ip_proto integer,"
1342 "ip_port integer,"
1343 "check_request $SQL:TEXT,"
1344 "check_responce $SQL:TEXT,"
1345 "poller_node_id integer not null,"
1346 "image_id integer not null,"
1347 "PRIMARY KEY(id))")))
1348 if (!g_bIgnoreErrors)
1349 return FALSE;
1350
1351 if (!SQLQuery(_T("UPDATE config SET var_value='20' WHERE var_name='DBFormatVersion'")))
1352 if (!g_bIgnoreErrors)
1353 return FALSE;
1354
1355 return TRUE;
1356 }
1357
1358
1359 //
1360 // Upgrade from V18 to V19
1361 //
1362
1363 static BOOL H_UpgradeFromV18(void)
1364 {
1365 static TCHAR m_szBatch[] =
1366 "ALTER TABLE nodes ADD platform_name varchar(63)\n"
1367 "UPDATE nodes SET platform_name=''\n"
1368 "<END>";
1369
1370 if (!SQLBatch(m_szBatch))
1371 if (!g_bIgnoreErrors)
1372 return FALSE;
1373
1374 if (!CreateTable(_T("CREATE TABLE agent_pkg ("
1375 "pkg_id integer not null,"
1376 "pkg_name varchar(63),"
1377 "version varchar(31),"
1378 "platform varchar(63),"
1379 "pkg_file varchar(255),"
1380 "description varchar(255),"
1381 "PRIMARY KEY(pkg_id))")))
1382 if (!g_bIgnoreErrors)
1383 return FALSE;
1384
1385 if (!SQLQuery(_T("UPDATE config SET var_value='19' WHERE var_name='DBFormatVersion'")))
1386 if (!g_bIgnoreErrors)
1387 return FALSE;
1388
1389 return TRUE;
1390 }
1391
1392
1393 //
1394 // Upgrade from V17 to V18
1395 //
1396
1397 static BOOL H_UpgradeFromV17(void)
1398 {
1399 static TCHAR m_szBatch[] =
1400 "ALTER TABLE nodes DROP COLUMN inherit_access_rights\n"
1401 "ALTER TABLE nodes ADD agent_version varchar(63)\n"
1402 "UPDATE nodes SET agent_version='' WHERE is_agent=0\n"
1403 "UPDATE nodes SET agent_version='<unknown>' WHERE is_agent=1\n"
1404 "INSERT INTO event_cfg (event_code,event_name,severity,flags,"
1405 "message,description) SELECT event_id,name,severity,flags,"
1406 "message,description FROM events\n"
1407 "DROP TABLE events\n"
1408 "DROP TABLE event_group_members\n"
1409 "CREATE TABLE event_group_members (group_id integer not null,"
1410 "event_code integer not null, PRIMARY KEY(group_id,event_code))\n"
1411 "ALTER TABLE alarms ADD source_event_code integer\n"
1412 "UPDATE alarms SET source_event_code=source_event_id\n"
1413 "ALTER TABLE alarms DROP COLUMN source_event_id\n"
1414 "ALTER TABLE alarms ADD source_event_id bigint\n"
1415 "UPDATE alarms SET source_event_id=0\n"
1416 "ALTER TABLE snmp_trap_cfg ADD event_code integer not null default 0\n"
1417 "UPDATE snmp_trap_cfg SET event_code=event_id\n"
1418 "ALTER TABLE snmp_trap_cfg DROP COLUMN event_id\n"
1419 "DROP TABLE event_log\n"
1420 "CREATE TABLE event_log (event_id bigint not null,event_code integer,"
1421 "event_timestamp integer,event_source integer,event_severity integer,"
1422 "event_message varchar(255),root_event_id bigint default 0,"
1423 "PRIMARY KEY(event_id))\n"
1424 "<END>";
1425 TCHAR szQuery[4096];
1426 DB_RESULT hResult;
1427
1428 hResult = SQLSelect(_T("SELECT rule_id,event_id FROM policy_event_list"));
1429 if (hResult != NULL)
1430 {
1431 DWORD i, dwNumRows;
1432
1433 if (!SQLQuery(_T("DROP TABLE policy_event_list")))
1434 {
1435 if (!g_bIgnoreErrors)
1436 {
1437 DBFreeResult(hResult);
1438 return FALSE;
1439 }
1440 }
1441
1442 if (!SQLQuery(_T("CREATE TABLE policy_event_list ("
1443 "rule_id integer not null,"
1444 "event_code integer not null,"
1445 "PRIMARY KEY(rule_id,event_code))")))
1446 {
1447 if (!g_bIgnoreErrors)
1448 {
1449 DBFreeResult(hResult);
1450 return FALSE;
1451 }
1452 }
1453
1454 dwNumRows = DBGetNumRows(hResult);
1455 for(i = 0; i < dwNumRows; i++)
1456 {
1457 _sntprintf(szQuery, 4096, _T("INSERT INTO policy_event_list (rule_id,event_code) VALUES (%d,%d)"),
1458 DBGetFieldULong(hResult, i, 0), DBGetFieldULong(hResult, i, 1));
1459 if (!SQLQuery(szQuery))
1460 if (!g_bIgnoreErrors)
1461 {
1462 DBFreeResult(hResult);
1463 return FALSE;
1464 }
1465 }
1466
1467 DBFreeResult(hResult);
1468 }
1469 else
1470 {
1471 if (!g_bIgnoreErrors)
1472 return FALSE;
1473 }
1474
1475 _sntprintf(szQuery, 4096,
1476 _T("CREATE TABLE event_cfg (event_code integer not null,"
1477 "event_name varchar(63) not null,severity integer,flags integer,"
1478 "message varchar(255),description %s,PRIMARY KEY(event_code))"),
1479 g_pszSqlType[g_iSyntax][SQL_TYPE_TEXT]);
1480 if (!SQLQuery(szQuery))
1481 if (!g_bIgnoreErrors)
1482 return FALSE;
1483
1484 if (!SQLBatch(m_szBatch))
1485 if (!g_bIgnoreErrors)
1486 return FALSE;
1487
1488 _sntprintf(szQuery, 4096,
1489 _T("CREATE TABLE modules (module_id integer not null,"
1490 "module_name varchar(63),exec_name varchar(255),"
1491 "module_flags integer not null default 0,description %s,"
1492 "license_key varchar(255),PRIMARY KEY(module_id))"),
1493 g_pszSqlType[g_iSyntax][SQL_TYPE_TEXT]);
1494 if (!SQLQuery(szQuery))
1495 if (!g_bIgnoreErrors)
1496 return FALSE;
1497
1498 if (!SQLQuery(_T("UPDATE config SET var_value='18' WHERE var_name='DBFormatVersion'")))
1499 if (!g_bIgnoreErrors)
1500 return FALSE;
1501
1502 return TRUE;
1503 }
1504
1505
1506 //
1507 // Upgrade from V16 to V17
1508 //
1509
1510 static BOOL H_UpgradeFromV16(void)
1511 {
1512 static TCHAR m_szBatch[] =
1513 "DROP TABLE locks\n"
1514 "CREATE TABLE snmp_trap_cfg (trap_id integer not null,snmp_oid varchar(255) not null,"
1515 "event_id integer not null,description varchar(255),PRIMARY KEY(trap_id))\n"
1516 "CREATE TABLE snmp_trap_pmap (trap_id integer not null,parameter integer not null,"
1517 "snmp_oid varchar(255),description varchar(255),PRIMARY KEY(trap_id,parameter))\n"
1518 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1519 "(500, 'SNMP_UNMATCHED_TRAP', 0, 1, 'SNMP trap received: %1 (Parameters: %2)',"
1520 "'Generated when system receives an SNMP trap without match in trap "
1521 "configuration table#0D#0AParameters:#0D#0A 1) SNMP trap OID#0D#0A"
1522 " 2) Trap parameters')\n"
1523 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1524 "(501, 'SNMP_COLD_START', 0, 1, 'System was cold-started',"
1525 "'Generated when system receives a coldStart SNMP trap#0D#0AParameters:#0D#0A"
1526 " 1) SNMP trap OID')\n"
1527 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1528 "(502, 'SNMP_WARM_START', 0, 1, 'System was warm-started',"
1529 "'Generated when system receives a warmStart SNMP trap#0D#0A"
1530 "Parameters:#0D#0A 1) SNMP trap OID')\n"
1531 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1532 "(503, 'SNMP_LINK_DOWN', 3, 1, 'Link is down',"
1533 "'Generated when system receives a linkDown SNMP trap#0D#0A"
1534 "Parameters:#0D#0A 1) SNMP trap OID#0D#0A 2) Interface index')\n"
1535 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1536 "(504, 'SNMP_LINK_UP', 0, 1, 'Link is up',"
1537 "'Generated when system receives a linkUp SNMP trap#0D#0AParameters:#0D#0A"
1538 " 1) SNMP trap OID#0D#0A 2) Interface index')\n"
1539 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1540 "(505, 'SNMP_AUTH_FAILURE', 1, 1, 'SNMP authentication failure',"
1541 "'Generated when system receives an authenticationFailure SNMP trap#0D#0A"
1542 "Parameters:#0D#0A 1) SNMP trap OID')\n"
1543 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1544 "(506, 'SNMP_EGP_NEIGHBOR_LOSS', 1, 1, 'EGP neighbor loss',"
1545 "'Generated when system receives an egpNeighborLoss SNMP trap#0D#0A"
1546 "Parameters:#0D#0A 1) SNMP trap OID')\n"
1547 "INSERT INTO snmp_trap_cfg (trap_id,snmp_oid,event_id,description) "
1548 "VALUES (1,'.1.3.6.1.6.3.1.1.5.1',501,'Generic coldStart trap')\n"
1549 "INSERT INTO snmp_trap_cfg (trap_id,snmp_oid,event_id,description) "
1550 "VALUES (2,'.1.3.6.1.6.3.1.1.5.2',502,'Generic warmStart trap')\n"
1551 "INSERT INTO snmp_trap_cfg (trap_id,snmp_oid,event_id,description) "
1552 "VALUES (3,'.1.3.6.1.6.3.1.1.5.3',503,'Generic linkDown trap')\n"
1553 "INSERT INTO snmp_trap_cfg (trap_id,snmp_oid,event_id,description) "
1554 "VALUES (4,'.1.3.6.1.6.3.1.1.5.4',504,'Generic linkUp trap')\n"
1555 "INSERT INTO snmp_trap_cfg (trap_id,snmp_oid,event_id,description) "
1556 "VALUES (5,'.1.3.6.1.6.3.1.1.5.5',505,'Generic authenticationFailure trap')\n"
1557 "INSERT INTO snmp_trap_cfg (trap_id,snmp_oid,event_id,description) "
1558 "VALUES (6,'.1.3.6.1.6.3.1.1.5.6',506,'Generic egpNeighborLoss trap')\n"
1559 "INSERT INTO snmp_trap_pmap (trap_id,parameter,snmp_oid,description) "
1560 "VALUES (3,1,'.1.3.6.1.2.1.2.2.1.1','Interface index')\n"
1561 "INSERT INTO snmp_trap_pmap (trap_id,parameter,snmp_oid,description) "
1562 "VALUES (4,1,'.1.3.6.1.2.1.2.2.1.1','Interface index')\n"
1563 "<END>";
1564
1565 if (!SQLBatch(m_szBatch))
1566 if (!g_bIgnoreErrors)
1567 return FALSE;
1568
1569 if (!CreateConfigParam(_T("DBLockStatus"), _T("UNLOCKED"), 0, 1))
1570 if (!g_bIgnoreErrors)
1571 return FALSE;
1572
1573 if (!CreateConfigParam(_T("DBLockInfo"), _T(""), 0, 0))
1574 if (!g_bIgnoreErrors)
1575 return FALSE;
1576
1577 if (!CreateConfigParam(_T("EnableSNMPTraps"), _T("1"), 1, 1))
1578 if (!g_bIgnoreErrors)
1579 return FALSE;
1580
1581 if (!CreateConfigParam(_T("SMSDriver"), _T("<none>"), 1, 1))
1582 if (!g_bIgnoreErrors)
1583 return FALSE;
1584
1585 if (!CreateConfigParam(_T("SMTPServer"), _T("localhost"), 1, 0))
1586 if (!g_bIgnoreErrors)
1587 return FALSE;
1588
1589 if (!CreateConfigParam(_T("SMTPFromAddr"), _T("netxms@localhost"), 1, 0))
1590 if (!g_bIgnoreErrors)
1591 return FALSE;
1592
1593 if (!SQLQuery(_T("UPDATE config SET var_value='17' WHERE var_name='DBFormatVersion'")))
1594 if (!g_bIgnoreErrors)
1595 return FALSE;
1596
1597 return TRUE;
1598 }
1599
1600
1601 //
1602 // Upgrade from V15 to V16
1603 //
1604
1605 static BOOL H_UpgradeFromV15(void)
1606 {
1607 static TCHAR m_szBatch[] =
1608 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1609 "(4005, 'DC_MAILBOX_TOO_LARGE', 1, 1,"
1610 "'Mailbox #22%6#22 exceeds size limit (allowed size: %3; actual size: %4)',"
1611 "'Custom data collection threshold event.#0D#0AParameters:#0D#0A"
1612 " 1) Parameter name#0D#0A 2) Item description#0D#0A"
1613 " 3) Threshold value#0D#0A 4) Actual value#0D#0A"
1614 " 5) Data collection item ID#0D#0A 6) Instance')\n"
1615 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1616 "(4006, 'DC_AGENT_VERSION_CHANGE', 0, 1,"
1617 "'NetXMS agent version was changed from %3 to %4',"
1618 "'Custom data collection threshold event.#0D#0AParameters:#0D#0A"
1619 " 1) Parameter name#0D#0A 2) Item description#0D#0A"
1620 " 3) Threshold value#0D#0A 4) Actual value#0D#0A"
1621 " 5) Data collection item ID#0D#0A 6) Instance')\n"
1622 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1623 "(4007, 'DC_HOSTNAME_CHANGE', 1, 1,"
1624 "'Host name was changed from %3 to %4',"
1625 "'Custom data collection threshold event.#0D#0AParameters:#0D#0A"
1626 " 1) Parameter name#0D#0A 2) Item description#0D#0A"
1627 " 3) Threshold value#0D#0A 4) Actual value#0D#0A"
1628 " 5) Data collection item ID#0D#0A 6) Instance')\n"
1629 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1630 "(4008, 'DC_FILE_CHANGE', 1, 1,"
1631 "'File #22%6#22 was changed',"
1632 "'Custom data collection threshold event.#0D#0AParameters:#0D#0A"
1633 " 1) Parameter name#0D#0A 2) Item description#0D#0A"
1634 " 3) Threshold value#0D#0A 4) Actual value#0D#0A"
1635 " 5) Data collection item ID#0D#0A 6) Instance')\n"
1636 "<END>";
1637
1638 if (!SQLBatch(m_szBatch))
1639 if (!g_bIgnoreErrors)
1640 return FALSE;
1641 if (!SQLQuery(_T("UPDATE config SET var_value='16' WHERE var_name='DBFormatVersion'")))
1642 if (!g_bIgnoreErrors)
1643 return FALSE;
1644 return TRUE;
1645 }
1646
1647
1648 //
1649 // Upgrade from V14 to V15
1650 //
1651
1652 static BOOL H_UpgradeFromV14(void)
1653 {
1654 static TCHAR m_szBatch[] =
1655 "ALTER TABLE items ADD instance varchar(255)\n"
1656 "UPDATE items SET instance=''\n"
1657 "INSERT INTO config (var_name,var_value,is_visible,need_server_restart) VALUES "
1658 "('SMTPServer','localhost',1,0)\n"
1659 "INSERT INTO config (var_name,var_value,is_visible,need_server_restart) VALUES "
1660 "('SMTPFromAddr','netxms@localhost',1,0)\n"
1661 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1662 "(4003, 'DC_AGENT_RESTARTED', 0, 1,"
1663 "'NetXMS agent was restarted within last 5 minutes',"
1664 "'Custom data collection threshold event.#0D#0AParameters:#0D#0A"
1665 " 1) Parameter name#0D#0A 2) Item description#0D#0A"
1666 " 3) Threshold value#0D#0A 4) Actual value#0D#0A"
1667 " 5) Data collection item ID#0D#0A 6) Instance')\n"
1668 "INSERT INTO events (event_id,name,severity,flags,message,description) VALUES "
1669 "(4004, 'DC_SERVICE_NOT_RUNNING', 3, 1,"
1670 "'Service #22%6#22 is not running',"
1671 "'Custom data collection threshold event.#0D#0AParameters:#0D#0A"
1672 " 1) Parameter name#0D#0A 2) Item description#0D#0A"
1673 " 3) Threshold value#0D#0A 4) Actual value#0D#0A"
1674 " 5) Data collection item ID#0D#0A 6) Instance')\n"
1675 "UPDATE events SET "
1676 "description='Generated when threshold value reached "
1677 "for specific data collection item.#0D#0A"
1678 " 1) Parameter name#0D#0A 2) Item description#0D#0A"
1679 " 3) Threshold value#0D#0A 4) Actual value#0D#0A"
1680 " 5) Data collection item ID#0D#0A 6) Instance' WHERE "
1681 "event_id=17 OR (event_id>=4000 AND event_id<5000)\n"
1682 "UPDATE events SET "
1683 "description='Generated when threshold check is rearmed "
1684 "for specific data collection item.#0D#0A"
1685 "Parameters:#0D#0A 1) Parameter name#0D#0A"
1686 " 2) Item description#0D#0A 3) Data collection item ID' "
1687 "WHERE event_id=18\n"
1688 "<END>";
1689
1690 if (!SQLBatch(m_szBatch))
1691 if (!g_bIgnoreErrors)
1692 return FALSE;
1693 if (!SQLQuery(_T("UPDATE config SET var_value='15' WHERE var_name='DBFormatVersion'")))
1694 if (!g_bIgnoreErrors)
1695 return FALSE;
1696 return TRUE;
1697 }
1698
1699
1700 //
1701 // Upgrade map
1702 //
1703
1704 static struct
1705 {
1706 int iVersion;
1707 BOOL (* fpProc)(void);
1708 } m_dbUpgradeMap[] =
1709 {
1710 { 14, H_UpgradeFromV14 },
1711 { 15, H_UpgradeFromV15 },
1712 { 16, H_UpgradeFromV16 },
1713 { 17, H_UpgradeFromV17 },
1714 { 18, H_UpgradeFromV18 },
1715 { 19, H_UpgradeFromV19 },
1716 { 20, H_UpgradeFromV20 },
1717 { 21, H_UpgradeFromV21 },
1718 { 22, H_UpgradeFromV22 },
1719 { 23, H_UpgradeFromV23 },
1720 { 24, H_UpgradeFromV24 },
1721 { 25, H_UpgradeFromV25 },
1722 { 26, H_UpgradeFromV26 },
1723 { 27, H_UpgradeFromV27 },
1724 { 28, H_UpgradeFromV28 },
1725 { 29, H_UpgradeFromV29 },
1726 { 30, H_UpgradeFromV30 },
1727 { 31, H_UpgradeFromV31 },
1728 { 32, H_UpgradeFromV32 },
1729 { 33, H_UpgradeFromV33 },
1730 { 34, H_UpgradeFromV34 },
1731 { 35, H_UpgradeFromV35 },
1732 { 36, H_UpgradeFromV36 },
1733 { 37, H_UpgradeFromV37 },
1734 { 38, H_UpgradeFromV38 },
1735 { 39, H_UpgradeFromV39 },
1736 { 0, NULL }
1737 };
1738
1739
1740 //
1741 // Upgrade database to new version
1742 //
1743
1744 void UpgradeDatabase(void)
1745 {
1746 DB_RESULT hResult;
1747 LONG i, iVersion = 0;
1748 BOOL bLocked = FALSE;
1749
1750 _tprintf(_T("Upgrading database...\n"));
1751
1752 // Get database format version
1753 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBFormatVersion'"));
1754 if (hResult != NULL)
1755 {
1756 if (DBGetNumRows(hResult) > 0)
1757 iVersion = DBGetFieldLong(hResult, 0, 0);
1758 DBFreeResult(hResult);
1759 }
1760 if (iVersion == DB_FORMAT_VERSION)
1761 {
1762 _tprintf(_T("Your database format is up to date\n"));
1763 }
1764 else if (iVersion > DB_FORMAT_VERSION)
1765 {
1766 _tprintf(_T("Your database has format version %d, this tool is compiled for version %d.\n"
1767 "You need to upgrade your server before using this database.\n"),
1768 iVersion, DB_FORMAT_VERSION);
1769 }
1770 else
1771 {
1772 // Check if database is locked
1773 hResult = DBSelect(g_hCoreDB, _T("SELECT var_value FROM config WHERE var_name='DBLockStatus'"));
1774 if (hResult != NULL)
1775 {
1776 if (DBGetNumRows(hResult) > 0)
1777 bLocked = _tcscmp(DBGetField(hResult, 0, 0), _T("UNLOCKED"));
1778 DBFreeResult(hResult);
1779 }
1780 if (!bLocked)
1781 {
1782 // Upgrade database
1783 while(iVersion < DB_FORMAT_VERSION)
1784 {
1785 // Find upgrade procedure
1786 for(i = 0; m_dbUpgradeMap[i].fpProc != NULL; i++)
1787 if (m_dbUpgradeMap[i].iVersion == iVersion)
1788 break;
1789 if (m_dbUpgradeMap[i].fpProc == NULL)
1790 {
1791 _tprintf(_T("Unable to find upgrade procedure for version %d\n"), iVersion);
1792 break;
1793 }
1794 printf("Upgrading from version %d to %d\n", iVersion, iVersion + 1);
1795 SQLQuery(_T("BEGIN"));
1796 if (m_dbUpgradeMap[i].fpProc())
1797 {
1798 SQLQuery(_T("COMMIT"));
1799 iVersion++;
1800 }
1801 else
1802 {
1803 printf("Rolling back last stage due to upgrade errors...\n");
1804 SQLQuery(_T("ROLLBACK"));
1805 break;
1806 }
1807 }
1808
1809 _tprintf(_T("Database upgrade %s\n"), (iVersion == DB_FORMAT_VERSION) ? _T("succeeded") : _T("failed"));
1810 }
1811 else
1812 {
1813 _tprintf(_T("Database is locked\n"));
1814 }
1815 }
1816 }