database.sql
author Dan
Sat, 15 Nov 2008 15:38:21 -0500
changeset 28 a7e537c0b549
parent 20 e2f6e3af9959
permissions -rw-r--r--
Fix undefined $total in stats-fe
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
0
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
     1
CREATE TABLE snippets(
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
     2
  snippet_id int(12) NOT NULL auto_increment,
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
     3
  snippet_code varchar(32) NOT NULL DEFAULT '',
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
     4
  snippet_text text,
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
     5
  snippet_channels text,
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
     6
  PRIMARY KEY ( snippet_id )
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
     7
);
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
     8
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
     9
CREATE TABLE irclog (
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
    10
  id int(11) NOT NULL auto_increment,
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
    11
  channel varchar(30) default NULL,
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
    12
  day char(10) default NULL,
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
    13
  nick varchar(40) default NULL,
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
    14
  timestamp int(11) default NULL,
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
    15
  line text,
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
    16
  spam tinyint(1) default '0',
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
    17
  PRIMARY KEY  (id)
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
    18
);
d02690a8552c Initial population. EnanoBot v0.1.
Dan
parents:
diff changeset
    19
20
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    20
--
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    21
-- NEW - Late October '08 modifications
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    22
--
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    23
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    24
CREATE TABLE stats_messages (
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    25
  message_id int(21) NOT NULL auto_increment,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    26
  channel varchar(30) NOT NULL DEFAULT '',
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    27
  nick varchar(40) NOT NULL DEFAULT '',
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    28
  time int(11) NOT NULL DEFAULT 0,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    29
  PRIMARY KEY ( message_id )
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    30
);
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    31
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    32
CREATE TABLE stats_anon (
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    33
  nick varchar(40) NOT NULL DEFAULT '',
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    34
  PRIMARY KEY ( nick )
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    35
);
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    36
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    37
CREATE TABLE stats_count_cache (
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    38
  cache_id int(21) NOT NULL auto_increment,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    39
  channel varchar(30) NOT NULL DEFAULT '',
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    40
  time_min int(11) NOT NULL DEFAULT 0,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    41
  time_max int(11) NOT NULL DEFAULT 0,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    42
  message_count int(11) NOT NULL DEFAULT 0,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    43
  PRIMARY KEY ( cache_id )
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    44
);
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    45
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    46
--
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    47
-- ADDED November 15 2008
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    48
--
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    49
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    50
CREATE TABLE ip_log (
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    51
  entry_id int(21) NOT NULL auto_increment,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    52
  nick varchar(40) NOT NULL,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    53
  basenick varchar(40) NOT NULL,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    54
  ip varchar(39) NOT NULL,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    55
  hostname varchar(80) NOT NULL,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    56
  channel varchar(20) NOT NULL,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    57
  time int(12) unsigned NOT NULL DEFAULT 0,
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    58
  PRIMARY KEY ( entry_id )
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    59
);
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    60
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    61
-- Also added Nov. 15 (this DRAMATICALLY speeds things up)
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    62
CREATE INDEX stats_time_idx USING BTREE ON stats_messages (time);
e2f6e3af9959 Stats now uses MySQL backend
Dan
parents: 0
diff changeset
    63