寻找数据库设计相关的方式 [英] look for a database design related manner

问题描述

我正在一个日志分析器系统，它读取tomcat的日志，并通过图表/表在网页中显示它们。
（我知道有一些存在的日志分析器系统，我正在重新创建轮子，但这是我的工作，我的老板想要的。）

我们的tomcat日志按天保存。例如：

  2011-01-01.txt 
 2011-01-02.txt 
。 ..... 
  

以下是我将日志导出到db并读取它们的方式： p>

1数据库结构

$ b b

我有三个表：
1）log_current：保存今天生成的日志。

2）log_past：保存今天生成的日志。 / p>

上面两个表拥有SAME模式。

  ----- + ----------- + ---------- + ---------- + -------- +  - ---- + ---------- + ---------- + -------- + -------------- ------- + --------- + ---------- + ------- + 
 | Id | hostip |用户名| datasend |方法| uri | queryStr |协议|状态|时间|浏览器|平台|参考| 
 + ------- + ----------- + ---------- + ---------- + --- ----- + ----- + ---------- + ---------- + -------- + ------- -------------- + --------- + ---------- + ------- + 
 | 44359 | 127.0.0.1 | -  | 0 | GET | / | | HTTP / 1.1 | 404 | 2011-02-17 08:08:25 |未知|未知| -  | 
 + ------- + ----------- + ---------- + ---------- + --- ----- + ----- + ---------- + ---------- + -------- + ------- -------------- + --------- + ---------- + ------- + 
  

3）log_record：保存log_past的信息，它记录日志已导出到log_past表的天数。

  + ----- + ------------ + 
 | Id |保存日期| 
 + ----- + ------------ + 
 | 127 | 2011-02-15 | 
 | 128 | 2011-02-14 | 
 .................. 
 + ----- + ------------ + 
  

此表显示2011-02-15的日志已导出。

2汇出（至db）

在00:05：

00，检查tomcat日志目录（/ tomcat / logs）以查找所有最近30天的日志文件（当然包括昨天的日志。

检查log_record表要查看是否导出了一天的日志，例如， log_record 中找不到 2011-02-16 ，因此我将阅读2011-02-16。 txt，并将它们导出为log_past。

在昨天的导出日志之后，我启动了今天日志的文件监视器（2011-02-17.txt）

$ b

2）文件监视器

，它将按小时读取文件。

3 tomcat服务器重新启动。

有时我们必须重新启动tomcat，所以一旦tomcat启动，我将删除log_current的所有日志，然后执行 day work 。

4我的问题

1）两个表（log_current和log_past）。

因为如果我把今天的日志保存到log_past，我不能确保所有的日志文件xxxx-xx-xx.txt）导出到db。因为我将在每天的00:05:00进行检查，确保今天之前的日志必须导出。

但是这使得查询日志很困难accros yestersay

例如，查询从2011-02-14 00:00:00到2011-02-15 00:00:00 ，这些日志必须在log_past。

但从2011-02-14 00:00:00到2011-02-17 08:00的 ：00 （假设是2011-02-17 09:00:00 现在）。

此外，我总是认为我的设计表和工作方式（计划导出/读取工作）不完美，所以任何人都可以给一个好建议？

我只需要导出和读取日志，并可以做一个几乎实时的分析，其中实时意味着我必须使图表的日志可见/ table等。

解决方案

首先，IMO不需要2个不同的表 log_current 和 log_past 。您可以插入同一个表中的所有行，例如 logs ，并使用
select *从日志中检索，其中id = log_record where savedDate ='YOUR_DATE'）
这将为您提供特定日期的所有日志。

能够使用上述方式删除表之间的当前和过去的区别，我认为您在这里要求的问题将解决。 ：）

I am working for a log analyzer system,which read the log of tomcat and display them by a chart/table in web page. (I know there are some existed log analyzer system,I am recreating the wheel. But this is my job,my boss want it.)

Our tomcat log are saved by day. For example:

2011-01-01.txt
2011-01-02.txt
......

The following is my manner for export logs to db and read them:

1 The DB structure

I have three tables: 1)log_current:save the logs generated today.

2)log_past:save the logs generated before today.

The above two tables own the SAME schema.

+-------+-----------+----------+----------+--------+-----+----------+----------+--------+---------------------+---------+----------+-------+
| Id    | hostip    | username | datasend | method | uri | queryStr | protocol | status | time                | browser | platform | refer |
+-------+-----------+----------+----------+--------+-----+----------+----------+--------+---------------------+---------+----------+-------+
| 44359 | 127.0.0.1 | -        |        0 | GET    | /   |          | HTTP/1.1 |    404 | 2011-02-17 08:08:25 | Unknown | Unknown  | -     |
+-------+-----------+----------+----------+--------+-----+----------+----------+--------+---------------------+---------+----------+-------+

3)log_record:save the information of log_past,it record the days whose logs have been exported to the log_past table.

+-----+------------+
| Id  | savedDate  |
+-----+------------+
| 127 | 2011-02-15 |
| 128 | 2011-02-14 |
..................
+-----+------------+

The table shows log of 2011-02-15 have been exported.

2 Export(to db)

I have two schedule work.

1) day work.

at 00:05:00,check the tomcat log directory(/tomcat/logs) to find all the latest 30 days log files(of course it include logs of yesterday.

check the log_record table to see if logs of one day is exported,for example,2011-02-16 is not find in the log_record,so I will read the 2011-02-16.txt,and export them to log_past.

After export log of yesterday,I start the file monitor for today's log(2011-02-17.txt) not matter it exist or not.

2)the file monitor

Once the monitor is started,it will read the file hour by hour. Each log it read will be saved in the log_current table.

3 tomcat server restart.

Sometimes we have to restart the tomcat,so once the tomcat is started,I will delete all logs of log_current,then do the day work.

4 My problem

1) two table (log_current and log_past).

Because if I save the today's log to log_past,I can not make sure all the log file(xxxx-xx-xx.txt) are exported to db. Since I will do a check in 00:05:00 every day which make sure that logs before today must be exported.

But this make it difficult to query logs accros yestersay and today.

For example,query from 2011-02-14 00:00:00 to 2011-02-15 00:00:00,these log must be at log_past.

But how about from 2011-02-14 00:00:00 to 2011-02-17 08:00:00 ?(suppose it is 2011-02-17 09:00:00 now).

It is complex to query across tables.

Also,I always think my desing for the table and work manner(schedule work of export/read) are not perfect,so anyone can give a good suggestion?

I just need to export and read log and can do a almost real-time analysis where real-time means I have to make logs of current day visiable by chart/table and etc.

解决方案

First of all, IMO you don't need 2 different tables log_current and log_past. You can insert all the rows in the same table, say logs and retrieve using select * from logs where id = (select id from log_record where savedDate = 'YOUR_DATE') This will give you all the logs of the particular day.

Now, once you are able to remove the current and past distinction between tables using above way, I think the problem you are asking here would be solved. :)

这篇关于寻找数据库设计相关的方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！