Re: [Perfparse-devel] Using indexes with mysql

[Flo G. <fl...@bi...>]

Hi again,

to show that handling date datatypes in different RDBMS is not easy 
portable, here some syntax examples (copied from sqlzoo.net):

Formating a date


MySQL:

SELECT DATE_FORMAT(wk,'%d/%m/%Y'), song
FROM totp
WHERE singer='Tom Jones'



Postges (this is ANSI i think):

SELECT CAST(EXTRACT(DAY FROM wk) AS VARCHAR(2))
        || '/' ||
        CAST(EXTRACT(MONTH FROM wk) AS VARCHAR(2))
        || '/' ||
        CAST(EXTRACT(YEAR FROM wk) AS VARCHAR(4)),
        song
  FROM totp
  WHERE singer='Tom Jones'



Oracle:

SELECT TO_CHAR(wk, 'DD/MM/YYYY'), song
FROM totp
WHERE singer='Tom Jones'

MSSQL:
SELECT CONVERT(VARCHAR(10), wk, 103), song
   FROM totp
  WHERE singer='Tom Jones'



DB2:

SELECT CHAR(wk, eur),
        song
  FROM totp
  WHERE singer='Tom Jones'



The conversion of unix timestamps to a textual representation can be 
easily done using the functions in <time.h> or equivalent functions in 
php.
Basically you're right. Dates work in all RDBMS the same. Only the textual 
representation is different. As far as i remember, Oracle does not print 
hours, minutes and seconds by default. And the representation may depend 
on initialization Parameters (NLS_ parameters). So one may get 12/24/2004 
while another one sees 24.12.2004 13:45:32 MESZ or even 24 Dezember 2004 
13:45:32 MESZ.

You can check this at sqlzoo.net.

Do a

SELECT wk, song
FROM totp
WHERE singer='Tom Jones'

using the different RDBMS, you get the default date representations:

Mysql and Postgres:
1987-04-30

Oracle:
30-APR-87


Finding time intervals with timestamps is easy too. One week is 60*60*24*7 
seconds. So my idea was to make all textual representation and conversion 
in C/PHP which ist portable.

To make all portable to other RDBMS there is some more work to do. I made 
tikiwiki (tikiwiki.org) work with mysql, postgres, oracle and sybase. It 
was really hard. Some examples:
- Oracle does not understand mysql's join syntax
- Mysql is case insensitive by default
- in php you get the result of a query as a associative array. The array 
keys are lower case on most systems, upper case at Oracle.
- To circumvent the above problem, one should quote the table- and column 
names. These quotes are backquotes in mysql, double quotes in Oracle and 
others
- sybase only supports identifiers (table names, column names) until a 
maximum length of (i think) 26 characters (don't ask me why!)
- some systems have a maximum varchar length of 250 characters.
- sybase is picky if you try to insert a integer value like this:
   insert into bla values('12')
   mysql and others do a cast. for them it's ok.
- many functions (CONCAT ...) are different

...

So you see, it's really hard to use ANSI SQL :-) But perfparse does not 
use to many of the special cases, so i think it will be easier to make it 
db independent.


Greetings,

Flo Gleixner


On Fri, 29 Oct 2004, Viljo Marrandi wrote:

> Hello,
>
> Actually, the first thing I thought about perfparse data schema, was that why 
> store hostnames in varchar for every value nagios gives us. Therefore I like 
> Flo's idea about putting hosts, metric names and service_descriptions to 
> different tables and simply joining them with _bin table. And also putting 
> raw data to same table would save lots of space and unneeded duplicate 
> values. I guess that would speed up things greatly.
>
> I'm not sure about storing times in unix timestamp format. I know mysql 
> supports this, don't know about Oracle, but it's not supported by PostgreSQL, 
> for which I indend to write module sometime (if somebody doesn't beat me on 
> this). By 'supported' I mean that postgres doesn't have TO_UNIXTIME and 
> FROM_UNIXTIME functions (or however they were called).
>
> So searching would be really weird, because first I had to convert unix 
> timestamp to date and then compare dates to find one week interval or 
> whatever. But then again, I might be wrong too :)
>
> Rgds,
> Viljo
>
>> What i would do to save storage:
>> 
>> create table hosts_names (
>>    host_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
>>    host_name VARCHAR(75) NOT NULL
>> )
>> 
>> and replace host_name column in perfdata_bin with the host_id. Do joins to 
>> get the textual host_name in queries. The same could be done for 
>> service_description and metric, since there is a limited number of 
>> different values.
>> 
>> Another proposal would be to use a integer to store timestamps. I'd use 
>> unix timestamps (seconds since 1.1.1970) to store dates, since - in my 
>> experience - the handling of dates in different databases (mysql, oracle, 
>> ...) is not very portable.
>> 
>> Last i'd propose to store the raw data in the bin table, since i really 
>> don't know why this is stored in a extra table. But probably there is a 
>> reason i didn't realize.
>> 
>> Finally i get:
>> 
>> TABLE perfdata_bin (
>>    host_id INTEGER NOT NULL,
>>    service_id INTEGER NOT NULL,
>>    metric_id INTEGER NOT NULL,
>>    ctime INTEGER NOT NULL,
>>    PRIMARY KEY (host_id,service_id,metric_id,ctime),
>>    value DOUBLE,
>>    state TINY INT,
>>    warn DOUBLE,
>>    critical DOUBLE,
>>    max DOUBLE,
>>    min DOUBLE,
>>    txt_data VARCHAR(255)
>> )
>> 
>> And tables for host_name, service_description and metric.
>> 
>> Note that this table has almost only fixed sized small columns.
>> 
>> What do you think?
>
>
> -------------------------------------------------------
> This Newsletter Sponsored by: Macrovision For reliable Linux application 
> installations, use the industry's leading
> setup authoring tool, InstallShield X. Learn more and evaluate today. 
> http://clk.atdmt.com/MSI/go/ins0030000001msi/direct/01/
> _______________________________________________
> Perfparse-devel mailing list
> Per...@li...
> https://lists.sourceforge.net/lists/listinfo/perfparse-devel
>