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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
|
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<!-- $Id: database.htm,v 1.12 2004/12/03 20:48:19 jfontain Exp $ -->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Author" content="Jean-Luc Fontaine">
<meta name="Description" content="reference manual for moodss and moomps database">
<title>moodss database</title>
</head>
<body bgcolor="#FFFFFF" style="font-family: helvetica, verdana, arial">
<center><h1>Database for moodss and moomps</h1></center>
<h3>Contents:</h3>
<ul>
<li><a href="#about">1. About this document</a>
<li><a href="#introduction">2. Introduction</a><ul>
<li><a href="#64bits">2.1. 64 bits support</a>
</ul>
<li><a href="#tables">3. Tables</a><ul>
<li><a href="#instances">3.1. Instances</a>
<li><a href="#options">3.2. Options</a>
<li><a href="#entries">3.3. Entries</a>
<li><a href="#history">3.4. History</a>
<li><a href="#data">3.5. Data</a>
</ul>
<li><a href="#installation">4. Installation</a><ul>
<li><a href="#configuration">4.1 Configuration</a>
<li><a href="#choice">4.2 Choice</a>
<li><a href="#initialization">4.3 Initialization</a>
<li><a href="#upgrade">4.4 Upgrade</a><ul>
<li><a href="#upgrade23">4.4.1. from moomps before 2.3</a>
<li><a href="#upgrade30">4.4.2. from moomps before 3.0</a>
</ul>
</ul>
<li><a href="#faq">5. Frequently Asked Questions</a>
</ul>
<h3><a name="about"></a>1. About this document</h3>
<p>This document contains general and reference information to help the database manager and the user understand the database used as a history storage medium by both the moodss and moomps applications.
<h3><a name="introduction"></a>2. Introduction</h3>
<p><i><b>Important</b>: if you have created a database using a moomps daemon prior to version 2.3 or version 3.0, please follow the instructions in the <a href="#upgrade">upgrade</a> section.</i>
<p>The <a href="moodss.htm">moodss</a> GUI and <a href="moomps.htm">moomps</a> daemon have the capability of not only monitoring in real time data cells belonging to loaded modules, but also to keep track of the history over time of any data cells, by storing their values in a database.
<p>Any number of data cells can be monitored over time, using a SQL database as storage mean. The cells values are stored in the database, which can then be used from, for example, any spreadsheet that allows external data sources, such as OpenOffice, ... or from a Web server via PHP, Perl, Tcl, ... or any language capable of database access. Using those tools, it becomes possible to create history graphs, presentations, ... using the <i>moodss</i> database as data source.
<br>It is also possible to browse the database and make graphs, piecharts, ... directly from the moodss graphical user interface.
<p>At this time, the MySQL (native driver and ODBC), SQLite (file based SQL library with no server needed), PostgreSQL (ODBC only), Oracle (ODBC only) and DB2 (ODBC only, <i>yet untested</i>) databases are supported, while other databases that also support ODBC should work (also see <a href="#installation">installation</a>).
<p><b>Notes</b>:<ul>
<li>The SQLite database library is supported in mono-writing-user/multi-reading-user mode only, as multiple users writing to the same database has not been tested. In most cases, SQLite is used in single user mode anyway.
<li>The Oracle database support was added some time after the database schema was already defined and in use. Consequently, it was not possible to change some table column names that conflict with some Oracle reserved names (there are many, unfortunately...). It was then decided to prefix all column names with the letter <b>c</b>, only in the Oracle database case, of course. Additionally, some column types had to be changed (DATETIME to DATE, TEXT to VARCHAR2(4000).
<li><i>Please report any experience with other databases access via ODBC, either to <a href="mailto:jfontain@free.fr">jfontain@free.fr</a> or <a href="news:comp.lang.tcl">comp.lang.tcl</a>.</i>
</ul>
<h4><a name="64bits"></a>2.1. 64 bits support</h4>
<p>Starting with moodss version 18.0 and moomps version 3.0, modules with internal row numbers of 64 bits length are supported, if running under Tcl/Tk version 8.4 or above.
<br>This implies that, depending on the database type, a couple or columns must have their type changed. In the general case:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
history.row: INTEGER -> BIGINT
data.row: INTEGER -> BIGINT
</pre></td><tr></table>
<p>Note that the above change is only needed if you archive data from 64 bit modules (see <a href="modules.htm">documentation</a> for a detailed list), but is strongly advised if you are running under Tcl/Tk 8.4 or are planning to in the near future (also strongly advised as Tcl/Tk 8.3 support is gradually dropped in modules and finally the core).
<p>Moodss or moomps automatically take care of creating the <i>history</i> and <i>data</i> tables with the right type for the <i>row</i> column, depending on the Tcl/Tk version being used. Also, whenever an incompatibility is encountered (for example, attempting to archive 64 bit module data in a 32 bit database), an error message is generated and the operation aborted, in order to remove any chance of corrupting an existing database.
<p>Since row numbers in modules are unsigned, and most databases do not support unsigned integers, the core (moodss or moomps) transforms huge row numbers into negative numbers when writing data to the database and performs the reverse operation when reading data from the database:<ul>
<li>a row greater or equal to 2<sup><small>63</small></sup> is written as (row - 2<sup><small>64</small></sup>)
<li>a negative row is read as (2<sup><small>64</small></sup> - row)
</ul>
<p>Please refer to the <a href="#history">history</a> and <a href="#data">data</a> tables sections for specific database information on 64 bits support.
<h3><a name="tables"></a>3. Tables</h3>
<p>The database structure is quite simple, with all the history data (cells and their values over time) in one table. It was designed to allow the moodss GUI application to be used not only as a real-time monitoring graphical tool, but also as a data history browser. Experience has shown that the database can be very easily used by other applications, such as PHP to make graphical dashboards accessible through a web browser.
<p>The different tables (described in more details later) are:<ul>
<li><b>instances</b>: the different instances of modules <i>(for example created by moomps from configuration files data)</i>.
<li><b>options</b>: the options of the modules instances.
<li><b>entries</b>: the data table columns of the instantiated modules
<li><b>history</b>: the monitored data cells values versus time
<li><b>data</b>: miscellaneous data related to the monitored data cells
</ul>
<center><pre><img src="diagram.gif" alt="relations diagram"></pre></center>
<p><i><b>Note</b>: the chosen database structure and its table definitions may appear minimalist to some experts, but it simply reflects the SQL code that works across all supported and tested databases.</i>
<h4><a name="instances"></a>3.1. Instances</h4>
<p>The instances table holds data related to the modules loaded by the application and which contain data cells that the user chose to monitor over a long period of time. For example, when such a module is first loaded by the core, a new entry is created in the instances table.
<p>Columns:<ul>
<li><b>number</b>: the table primary key, a unique, internally generated, instance number. Each new instance is assigned a new number.
<li><b>start</b>: when the last instance of the module of specified major version number was started. Consists of both the date and the time, down to the second precision (format: usually <i>YYYY-MM-DD HH:MM:SS</i>).
<li><b>module</b>: the module name.
<li><b>identifier</b>: the module identifier, as defined by the module code, defaults to the module name <i>(for example, the snmp module can set its identifier to snmp(1.2.3.4) when monitoring the IP address 1.2.3.4, the identifier appearing as title of the data tables displayed by the moodss GUI)</i>.
<li><b>major</b>: the module major version number (i.e. <i>1</i> if the module version is <i>1.2.3</i>).
<li><b>minor</b>: the module minor version number when the last instance of the module was recorded (i.e. <i>2</i> if the module version is <i>1.2.3</i>), <i>0</i> in the rare cases where the module version consists only of a single number.
</ul>
<p>A module instance is uniquely defined from the module name, its major version and its options (also see <a href="#options">options table</a>).
<p><b>Important note</b>: module programmers must insure that the module internal data table structure and static content does not change between minor versions (i.e. between <i>1.2</i> and <i>1.3</i>, but such changes between <i>1.3</i> and <i>2.0</i> are allowed, in which case a new entry in the instances table is created) <i>(this has changed from moomps version 2.3, since it has been found that new instances, thus new data histories were created too often when modules code was being improved, which often changed the minor number)</i>.
<p>SQL creation statement:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
CREATE TABLE instances (
number INTEGER NOT NULL PRIMARY KEY,
start DATETIME NOT NULL,
module VARCHAR(255) NOT NULL,
identifier VARCHAR(255),
major INTEGER NOT NULL,
minor INTEGER NOT NULL
)
</pre></td><tr></table>
<p><i>Sample extract:</i>
<table border="1" bgcolor="#DFDFDF">
<tr><th>number</th><th>start</th><th>module</th><th>identifier</th><th>major</th><th>minor</th></tr>
<tr><td>7</td><td>2002-09-14 21:52:37</td><td>random</td><td>random</td><td>1</td><td>47</td></tr>
<tr><td>13</td><td>2002-09-14 22:33:26</td><td>snmp</td><td>snmp(1.2.3.4)</td><td>2</td><td>12</td></tr>
</table>
<p><b>Notes</b>:<ul>
<li>in the Oracle database case, columns are named: <b>cnumber</b>, <b>cstart</b>, <b>cmodule</b>, <b>cidentifier</b>, <b>cmajor</b>, <b>cminor</b>.
<li>for the SQLite library, the <b>start</b> column is of INTEGER type and counted in seconds (UNIX time), as date and time types are not supported.
</ul>
<h4><a name="options"></a>3.2. Options</h4>
<p>The options table holds the options of the modules instances.
<p>Columns:<ul>
<li><b>instance</b>: the module instance number, corresponding to a unique module <a href="#instances">instance</a>.
<li><b>name</b>: the option name, the switch name (i.e.: <i>-r</i>, <i>--remote</i>), as can be found from the module help.
<li><b>value</b>: the actual option value, used to initialize the module instance at load time. The value can be void if the related option takes no argument, being of the boolean type (i.e. <i>--asynchronous</i> in the <i>random</i> module).
</ul>
<p>SQL creation statement:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
CREATE TABLE options (
instance INTEGER NOT NULL REFERENCES instances,
name VARCHAR(255) NOT NULL,
value VARCHAR(255)
)
</pre></td><tr></table>
<p><i>Sample extract:</i>
<table border="1" bgcolor="#DFDFDF">
<tr><th>instance</th><th>name</th><th>value</th></tr>
<tr><td>7</td><td>-a</td><td> </td></tr>
<tr><td>13</td><td>-a</td><td>1.2.3.4</td></tr>
<tr><td>13</td><td>--trim</td><td>sys</td></tr>
<tr><td>13</td><td>-i</td><td>sysUpTime,sysDescr,sysObjectID,sysContact,sysName,sysLocation</td></tr>
<tr><td>15</td><td>--password</td><td>********</td></tr>
</table>
<p><b>Note</b>: password options values (determined from their option name: see moodss <a href="moodss.htm#moduledevelopment">module development</a> section for what defines a password option name) are not stored as readable values, but with all the characters replaced by a <b>*</b> character, as seen in the table extract above.
<p><b>Note</b>: in the Oracle database case, columns are named: <b>cinstance</b>, <b>cname</b>, <b>cvalue</b>.
<h4><a name="entries"></a>3.3. Entries</h4>
<p>This is a copy of the internal module data table description. It is updated every time data recording is started for the module instance. The term <i>entry</i> is used in place of <i>column</i>, which is a reserved word in SQL.
<br><i><b>Note</b>: this table is mostly of use by the moodss database feature, which is meant to display history data in a similar fashion as the regular, real-time modules.</i>
<p>Columns:<ul>
<li><b>instance</b>: the module instance number, corresponding to a unique module <a href="#instances">instance</a>.
<li><b>number</b>: the internal data column entry index, as defined in the module implementation.
<li><b>indexed</b>: whether the column entry is part of the internal module data table key.
<li><b>label</b>: the column entry label (as displayed in the column headers of the module table(s) in moodss).
<li><b>type</b>: the column entry data type, as defined in the module implementation (<i>ascii</i>, <i>integer</i>, ...).
<li><b>message</b>: the column entry help message (as displayed with a widget tip (balloon) on the column headers of the module table(s) in moodss).
<li><b>anchor</b>: the column entry displayed data anchoring (optional, as defined in the module implementation).
</ul>
<p>SQL creation statement:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
CREATE TABLE entries (
instance INTEGER NOT NULL REFERENCES instances,
number INTEGER NOT NULL,
indexed INTEGER NOT NULL,
label VARCHAR(255) NOT NULL,
type VARCHAR(16) NOT NULL,
message TEXT NOT NULL,
anchor VARCHAR(16),
UNIQUE(instance, number)
)
</pre></td><tr></table>
<p><i>Sample extract:</i>
<table border="1" bgcolor="#DFDFDF">
<tr><th>instance</th><th>number</th><th>indexed</th><th>label</th><th>type</th><th>message</th><th>anchor</th></tr>
<tr><td>7</td><td>0</td><td>1</td><td>name</td><td>ascii</td><td>user name</td><td> </td></tr>
<tr><td>7</td><td>1</td><td>0</td><td>cpu</td><td>real</td><td>cpu usage in percent</td><td> </td></tr>
<tr><td>7</td><td>2</td><td>0</td><td>disk</td><td>integer</td><td>disk usage in megabytes</td><td> </td></tr>
<tr><td>7</td><td>3</td><td>0</td><td>memory</td><td>integer</td><td>memory usage in kilobytes</td><td> </td></tr>
<tr><td>7</td><td>4</td><td>1</td><td>command</td><td>dictionary</td><td>command name</td><td>left</td></tr>
</table>
<p><b>Note</b>: in the Oracle database case, columns are named: <b>cinstance</b>, <b>cnumber</b>, <b>cindexed</b>, <b>clabel</b>, <b>ctype</b>, <b>cmessage</b>, <b>canchor</b>.
<h4><a name="history"></a>3.4. History</h4>
<p>The table with the data cells values over time, the actual history table. Can grow quite large.
<p>Columns:<ul>
<li><b>instant</b>: when the data cell sample was recorded. Consists of both the date and the time, down to the second precision (format: usually <i>YYYY-MM-DD HH:MM:SS</i>).
<li><b>instance</b>: the module instance number, corresponding to a unique module <a href="#instances">instance</a>.
<li><b>row</b>: the data cell row in the module internal data table.
<li><b>entry</b>: the data cell column in the module internal data table (as in the <a href="#entries">entries table</a>).
<li><b>value</b>: the actual value of the data cell at the time the data sample was recorded (the data type can be determined from the <a href="#entries">entries table</a>).
</ul>
<p>SQL creation statement:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
CREATE TABLE history (
instant DATETIME NOT NULL,
instance INTEGER NOT NULL REFERENCES instances,
row BIGINT NOT NULL,
entry INTEGER NOT NULL,
value VARCHAR(255)
)
</pre></td><tr></table>
<p><i>Sample extract:</i>
<table border="1" bgcolor="#DFDFDF">
<tr><th>instant</th><th>instance</th><th>row</th><th>entry</th><th>value</th></tr>
<tr><td>2002-09-14 21:52:39</td><td>7</td><td>3</td><td>2</td><td>593</td></tr>
<tr><td>2002-09-14 21:52:39</td><td>7</td><td>9</td><td>1</td><td>25.0</td></tr>
<tr><td>2002-09-14 22:33:28</td><td>7</td><td>3</td><td>2</td><td>545</td></tr>
<tr><td>2002-09-14 22:33:28</td><td>7</td><td>9</td><td>1</td><td>24.8</td></tr>
<tr><td>2002-09-14 22:33:36</td><td>7</td><td>3</td><td>2</td><td>512</td></tr>
<tr><td>2002-09-14 22:33:36</td><td>7</td><td>9</td><td>1</td><td>14.6</td></tr>
</table>
<p>Note: starting with moomps version 2.3, the following index is created when the history table is created, to significantly improve performance. If the database tables were created by a moomps prior to version 2.3, use the following SQL statement:
<p>SQL creation statement:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
CREATE INDEX cell ON history (instance, row, entry)
</pre></td><tr></table>
<p><b>Notes</b>:<ul>
<li>in the Oracle database case, columns are named: <b>cinstant</b>, <b>cinstance</b>, <b>crow</b>, <b>centry</b>, <b>cvalue</b>, and the <b>row</b> column type is INTEGER (as it supports 64 bits values).
<li>for the SQLite library, the <b>instant</b> column if of INTEGER type and counted in seconds (UNIX time), as date and time types are not supported.
</ul>
<h4><a name="data"></a>3.5. Data</h4>
<p>This is where one can determine which data cells were or are monitored from the moomps daemon or the moodss GUI. Also contains some extra miscellaneous information.
<p>Columns:<ul>
<li><b>instance</b>: the module instance number, corresponding to a unique module <a href="#instances">instance</a>.
<li><b>row</b>: the data cell row in the module internal data table.
<li><b>entry</b>: the data cell column in the module internal data table (as in the <a href="#entries">entries table</a>).
<li><b>label</b>: the string used to label the cell, as would be used, for example, in a moodss viewer next to the cell value, when monitored in real time.
<li><b>comment</b>: an optional string, which may have been input by the user, from the moodss database interface, when choosing the cell as a target for history monitoring.
</ul>
<p>SQL creation statement:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
CREATE TABLE data (
instance INTEGER NOT NULL REFERENCES instances,
row BIGINT NOT NULL,
entry INTEGER NOT NULL,
label VARCHAR(255) NOT NULL,
comment VARCHAR(255),
UNIQUE(instance, row, entry)
)
</pre></td><tr></table>
<p><i>Sample extract:</i>
<table border="1" bgcolor="#DFDFDF">
<tr><th>instance</th><th>row</th><th>entry</th><th>label</th><th>comment</th></tr>
<tr><td>7</td><td>3</td><td>2</td><td>random: Robert top disk</td><td>lab workstation</td></tr>
<tr><td>7</td><td>9</td><td>1</td><td>random: Laura cc cpu</td><td>in cluster</td></tr>
<tr><td>13</td><td>0</td><td>1</td><td>snmp: UpTime</td><td> </td></tr>
</table>
<p><b>Note</b>: in the Oracle database case, columns are named: <b>cinstance</b>, <b>crow</b>, <b>centry</b>, <b>clabel</b>, <b>ccomment</b>, and the <b>row</b> column type is INTEGER (as it supports 64 bits values).
<h3><a name="installation"></a>4. Installation</h3>
<h4><a name="configuration"></a>4.1. Configuration</h4>
<p>If you want storage of data cell values in a history database, set the following options in the moodss preferences dialog box, database section:
<!-- the following list is the same as in the moodss HTML documentation -->
<ul>
<li><b>file</b>: the single file holding complete data accessed by the SQLite library (that is the only parameter needed by the SQLite library). By default, it is <i>moodss.dat</i> in the home directory of the current user.
<li><b>dsn</b>: Data Source Name, if specified, means that database access is done via ODBC, which requires the <i>tclodbc</i> package to be installed. If not specified, native MySQL database access is used, which requires the <i>mysqltcl</i> package library to be installed.
<li><b>host</b>: the database server host name, or IP address <i>(not allowed in ODBC mode)</i>. Use <i>localhost</i> for socket based access (also see MySQL documentation).
<li><b>password</b>: the password used to connect to the database (also see <i>user</i>). <i><b>Note</b>: it is stored in clear, so only allowing access to the moomps <b>preferences</b> file from the <b>root</b> account is highly recommended.</i>
<li><b>port</b>: the port number corresponding to the database service <i>(not allowed in ODBC mode)</i>.
<li><b>user</b>: the user name used to connect to the database (also see <i>password</i>).
<li><b>database</b>: the MySQL database name for the MySQL native driver <i>(<b>moodss</b> by default, not allowed in ODBC mode)</i>.
<li><b>SQL trace</b>: when set, all SQL statements and queries are either printed on the terminal where moodss was launched, or in the system log, for the moomps daemon (<i>off by default, available on UNIX platforms only</i>).
</ul>
<h4><a name="choice"></a>4.2. Choice</h4>
<p>The moomps daemon or moodss GUI do not permanently write to the database (but obviously it depends on the number of the modules loaded and on the poll times), so the performance on writes may not seem that important (transactions are not used since all write operations are atomic), but in some cases, it is important that the database achieves write operations in the shortest possible time.
<br>For instance, problems can occur for example, when the moomps daemon has a lot of modules loaded, such as <i>snmp</i>, which may time out due to delays incurred by database insertions in the history table. That is why the INSERT DELAYED statement is internally used when MySQL is the chosen database, in order to have write operations as fast as possible.
<p>Reading from the database happens when for example, creating graphs of data cell values over time, from a spreadsheet, or browsing history data from the moodss GUI, and since the history table can grow quite large (tens of millions of recorded samples have been observed on production systems), database performance on reads is not to be neglected. For the same reason, storage space is to be taken into consideration.
<p>As a concrete example, a bi-processor PC with pentium IIIs at 1.25 GHz, 1 gigabyte of memory and 7200 rpm SCSI disks has been adequate for a MySQL database with more than 10 million records.
<h4><a name="initialization"></a>4.3. Initialization</h4>
<p>Before starting moomps or moodss, the only thing to do is to manually create a database named <b>moodss</b> (or possibly some other name defined either in the ODBC configuration, or in the moodss preferences for the native MySQL driver), no matter what the database type that you have chosen (except SQLite, of course, which works with a single data file). Here are a few examples of the commands required to initialize the database:
<ul>
<li>MySQL:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
$ mysqladmin create moodss
</pre></td><tr></table>
or
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
$ mysql
mysql> create database moodss;
</pre></td><tr></table>
<li>PostgreSQL:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
$ createdb moodss
</pre></td><tr></table>
<li>SQLite: the database file, once chosen, is automatically created by the library
<li>Oracle: <i>to be specified</i>
<li>DB2: <i>to be specified</i>
</ul>
<p>Moomps or moodss, when started and if any data cells history is to be recorded, will then automatically create the database tables if they do not yet exist (make sure the database user has enough privileges to create tables). An informational message is logged when that operation has succeeded.
<p><b>Example of a MySQL database server with ODBC installation on a local Red Hat Linux system</b>:
<p>Install the <i>mysql</i> and <i>mysql-server</i> rpms (you may also use instead the latest <i>MySQL-server</i>, <i>MySQL-client</i> and <i>MySQL-shared</i> rpms from <a href="http://www.mysql.com">www.mysql.com</a>) and the <i>unixODBC</i> rpm.
<br>Install the <i>MyODBC</i> rpm from your Red Hat distribution.
<p>In <i>/etc/odbcinst.ini</i>, insert:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc.so
FileUsage = 1
</pre></td><tr></table>
<p>and in <i>/etc/odbc.ini</i>:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
[moodss]
Driver = MySQL
Database = moodss
</pre></td><tr></table>
<p>finally in moodss preferences, thresholds and database sections:
<table bgcolor="#DFDFDF" border="0">
<tr><td><b>From address</b>:</td><td>jfontain</td></tr>
<tr><td><b>Outgoing mail SMTP servers</b>:</td><td>127.0.0.1</td></tr>
<tr><td><b>ODBC DSN</b>:</td><td>moodss</td></tr>
<tr><td><b>user</b>:</td><td>jdoe</td></tr>
<tr><td><b>password</b>:</td><td>xxxxxx</td></tr>
</table>
<p>(assuming <i>jdoe</i> MySQL user exists, has proper privileges and <i>xxxxxx</i> as password).
<h4><a name="upgrade"></a>4.4. Upgrade</h4>
<h5><a name="upgrade23"></a>4.4.1. from moomps before 2.3</h5>
<p><i><b>Important</b>: if you have created a database using a moomps daemon prior to version 2.3, please follow the following simple instructions to convert your existing database so that it can be used by the latest moomps and moodss applications. If you have never created a moodss database or used moomps prior to its version 2.3, you can completely ignore this section.</i>
<p>There has been a change in the tables structure at the moodss 17.0 and moomps 2.3 release. The <i>entries</i> table was affected the most, but backward compatibility has been maintained, provided you alter some tables by following the instructions below.
<p><i><b>Important</b>: make sure to backup your existing database before making the following alterations, just in case.</i>
<hr>
<p>In the <i>instances</i> table, the <i>start</i> column type has been changed from TIMESTAMP to DATETIME, since in MySQL, manually updating any row would result in the <i>start</i> cell being set to the current time. It is advised to manually change the type before being caught one day by this behavior (<i>note: can be done while the database is being used without problems</i>).
<br>The <i>identifier</i> column was also added, to record the name set by the module code (which appears as the title of the module tables in the moodss GUI).
<p><b>instances</b> table modification:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
CREATE TABLE instances (
number INTEGER NOT NULL PRIMARY KEY,
start <strike>TIMESTAMP</strike> DATETIME NOT NULL,
module VARCHAR(255) NOT NULL,
identifier VARCHAR(255),
major INTEGER NOT NULL,
minor INTEGER NOT NULL
)
</pre></td><tr></table>
<p>To convert an <i>instances</i> table created by a moomps daemon prior to version 2.3, it suffices to insert an <i>identifier</i> column and change the <i>start</i> column TIMESTAMP type, as the following statements (examples for MySQL) show:
<p><table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
ALTER TABLE instances MODIFY start DATETIME NOT NULL;
ALTER TABLE instances ADD COLUMN identifier VARCHAR(255) AFTER module;
</pre></td><tr></table>
<p>Note: with some databases (such as PostgreSQL), it may be necessary to insert the following row to enforce consistency:
<p><table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
INSERT INTO instances VALUES (0, NOW(), '', NULL, 0, 0);
</pre></td><tr></table>
<hr>
<p>In the <i>entries</i> table, the <i>module</i>, <i>major</i> and <i>minor</i> columns were replaced by the <i>instance</i> column, since it was wrongly assumed that the internal data columns of a module depended solely on its version, assumption put to pieces by modules (such as <i>snmp</i>) which can dynamically generate completely different internal data columns depending on the module options.
<p><b>entries</b> table modifications:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
CREATE TABLE entries (
instance INTEGER NOT NULL REFERENCES instances,
<strike>module VARCHAR(255) NOT NULL,</strike>
<strike>major INTEGER NOT NULL,</strike>
<strike>minor INTEGER NOT NULL,</strike>
number INTEGER NOT NULL,
indexed INTEGER NOT NULL,
label VARCHAR(255) NOT NULL,
type VARCHAR(16) NOT NULL,
message TEXT NOT NULL,
anchor VARCHAR(<strike>255</strike> 16),
UNIQUE(<strike>module, major, minor</strike> instance, number)
)
</pre></td><tr></table>
<p>Use the following statements (examples for MySQL) to alter the <i>entries</i> table (<i>note: do not remove the <i>module</i>, <i>major</i> and <i>minor</i> columns, but simply allow them to accept NULL values, so that the database remains consistent, the moodss and moomps applications being able to handle both old and new instance entries</i>):
<p><table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
ALTER TABLE entries ADD COLUMN instance INTEGER NOT NULL REFERENCES instances FIRST;
ALTER TABLE entries MODIFY module VARCHAR(255);
ALTER TABLE entries MODIFY major INTEGER;
ALTER TABLE entries MODIFY minor INTEGER;
</pre></td><tr></table>
<p>Finally consistency needs to be enforced (<i>note: the second statement may not work in which case it is necessary to set the instances numbers in the instance column manually by matching with the instances table number column</i>):
<p><table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
ALTER TABLE entries DROP PRIMARY KEY;
ALTER TABLE entries ADD UNIQUE (instance, number);
</pre></td><tr></table>
<hr>
<p>In the <i>history</i> table, the <i>instant</i> column type has been changed from TIMESTAMP to DATETIME, since in MySQL, manually updating any row would result in the <i>instant</i> cell being set to the current time. It is advised to manually change the type before being caught one day by this behavior (<i>note: can be done while the database is being used without problems</i>).
<p><b>history</b> table modification:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
CREATE TABLE history (
instant <strike>TIMESTAMP</strike> DATETIME NOT NULL,
instance INTEGER NOT NULL REFERENCES instances,
row INTEGER NOT NULL,
entry INTEGER NOT NULL,
value VARCHAR(255)
)
</pre></td><tr></table>
<p>Use the following statement (example for MySQL) to alter the <i>history</i> table:
<p><table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
ALTER TABLE history MODIFY instant DATETIME NOT NULL;
</pre></td><tr></table>
<h5><a name="upgrade30"></a>4.4.2. from moomps before 3.0</h5>
<p>Starting with moodss version 18.0 and moomps version 3.0, modules with internal row numbers of 64 bits length are supported, if running under Tcl/Tk version 8.4 or above.
<br>If you were using Tcl/Tk version 8.3 and upgrading to 8.4, and you will be archiving data from 64 bits capable modules (see <a href="modules.htm">documentation</a>), then you need to change the type of 2 columns in the database. If you are not sure that you will be using 64 bits modules, then just go ahead and upgrade the database, as this is a safe operation.
<p>If you are using:<ul>
<li>SQLite: no changes required as this is a typeless database, and it can therefore handle 64 bits numbers from the start.
<li>Oracle: the INTEGER type can handle 64 bits integers so no changes are required.
<li>MySQL: follow the instructions below.
<li>PostgresQL: follow the instructions below.
<li>other ODBC databases: please contact me at <a href="mailto:jfontain@free.fr">jfontain@free.fr</a> or ask in <a href="news:comp.lang.tcl">comp.lang.tcl</a>.
</ul>
<p>The <i>history</i> and <i>data</i> tables need to be altered (make a backup before executing the following command just in case), as the following example for MySQL shows:
<table bgcolor="#DFDFDF" width="100%"><tr><td><pre>
ALTER TABLE history MODIFY row BIGINT;
ALTER TABLE data MODIFY row BIGINT;
</pre></td><tr></table>
<h3><a name="faq"></a>5. Frequently Asked Questions</h3>
<h4>Q: A module major version number has changed: what can I do to recuperate the old history data?</h4>
<p><b>A</b>: Here is a summary of the situation using an example:<ul>
<li>you have accumulated data history for cells belonging to the module <i>foo</i> version <i>1.3.4</i> (major version <i>1</i>)
<li>the module implementation has been greatly enhanced, but with no change to the module data columns (type, ordering, ...), which still justified a major version number increase (<i>foo</i> module version is now <i>2.0</i>)
<li>new history data has been created using the new <i>foo</i> module
</ul>
<p>The major version change has resulted in a new instance (say number <i>21</i>) being created for the foo module in the database (assuming the module parameters have not changed), so the old history data (created using <i>foo</i> version <i>1.3.4</i> and saved under database instance <i>6</i>) is still valid but not accessed using the same instance number: is it possible to integrate the old and the new data?
<p>What needs to be done is to change the old data instance number (<i>6</i>) to the new instance number (<i>21</i>) for all recordings in the <i>history</i> table, then remove all references to the instance number <i>6</i> in the other tables (although that step is not strictly necessary: that would just leave a database instance (<i>6</i>) with no data, which moodss can handle).
<p>Here is an example of the necessary steps:<ul>
<li><b>backup the <i>history</i> table just in case</b>
<li>UPDATE TABLE history SET instance = 21 where instance = 6
<li>DELETE FROM data WHERE instance = 6 <i>(optional along with the following)</i>
<li>DELETE FROM entries WHERE instance = 6
<li>DELETE FROM options WHERE instance = 6
<li>DELETE FROM instances WHERE number = 6
</ul>
</body>
</html>
|
