aboutsummaryrefslogtreecommitdiff
path: root/sql/cheetah.sql
blob: 654775156bf64d0ee239195e35a86464d47aff76 (plain)
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
--
--  Cheetah News SQL
--  Copyright (C) 2005, 2006, 2007, 2008, 2010 Wojciech Polak.
--
--  This program is free software; you can redistribute it and/or modify it
--  under the terms of the GNU General Public License as published by the
--  Free Software Foundation; either version 3 of the License, or (at your
--  option) any later version.
--
--  This program is distributed in the hope that it will be useful,
--  but WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--  GNU General Public License for more details.
--
--  You should have received a copy of the GNU General Public License along
--  with this program.  If not, see <http://www.gnu.org/licenses/>.
--

-- DROP TABLE IF EXISTS user;
CREATE TABLE user (
  id int(11) NOT NULL auto_increment,
  email varchar(128) NOT NULL default '',
  pass varchar(255) NOT NULL default '',
  active enum('yes','no','suspended') NOT NULL default 'yes',
  lastLog datetime default NULL,
  lastAccess datetime default NULL,
  lastUC datetime default NULL,
  logCount int(11) NOT NULL default '0',
  failogCount tinyint(2) unsigned NOT NULL default '0',
  lang varchar(7) default NULL,
  showActive tinyint(1) NOT NULL default '1',
  oldestFirst tinyint(1) NOT NULL default '0',
  refresh int(4) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY email (email),
  KEY active (active),
  KEY lastAccess (lastAccess)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS openid;
CREATE TABLE openid (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL REFERENCES user.id,
  identity varchar(255) NOT NULL default '',
  PRIMARY KEY (id),
  KEY userid (userid),
  UNIQUE KEY identity (identity)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS feed;
CREATE TABLE feed (
  id int(11) NOT NULL auto_increment,
  hash varchar(64) NOT NULL default '',
  url varchar(255) NOT NULL default '',
  description varchar(255) NOT NULL default '',
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS feedaddqueue;
CREATE TABLE feedaddqueue (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL REFERENCES user.id,
  url varchar(255) NOT NULL default '',
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS folder;
CREATE TABLE folder (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL REFERENCES user.id,
  fname varchar(64) NOT NULL default '',
  pri int(11) NOT NULL default '1',
  PRIMARY KEY (id),
  KEY userid (userid)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS subscription;
CREATE TABLE subscription (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL REFERENCES user.id,
  feedid int(11) NOT NULL REFERENCES feed.id,
  pri int(11) NOT NULL default '1',
  latest tinyint NOT NULL default '7',
  expand tinyint NOT NULL default '0',
  folder int(11) NOT NULL default '0',
  active tinyint(1) NOT NULL default '0',
  description varchar(255) NOT NULL default '',
  PRIMARY KEY (id),
  KEY userid (userid),
  KEY feedid (feedid)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS cache;
CREATE TABLE cache (
  feedid int(11) NOT NULL REFERENCES feed.id,
  eTag varchar(255),
  lastModified varchar(64),
  lastAccessed datetime,
  xml mediumblob,
  PRIMARY KEY (feedid)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS forgotpassword;
CREATE TABLE forgotpassword (
  id int(11) NOT NULL auto_increment,
  email varchar(255) NOT NULL default '',
  hash varchar(255) NOT NULL,
  date datetime NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (hash)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS registration;
CREATE TABLE registration (
  id int(11) NOT NULL auto_increment,
  email varchar(255) NOT NULL default '',
  pass varchar(255) NOT NULL default '',
  hash varchar(255) NOT NULL,
  openid_identity varchar(255),
  rdate datetime NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (hash)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS ntag;
CREATE TABLE ntag (
  id int(11) NOT NULL auto_increment,
  tag varchar(255) NOT NULL default '',
  PRIMARY KEY (id),
  UNIQUE KEY (tag)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS note;
CREATE TABLE note (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL REFERENCES user.id,
  public enum('yes','no') NOT NULL default 'no',
  color varchar(32) NOT NULL default '',
  date datetime NOT NULL,
  mstamp timestamp,
  title varchar(64),
  note text not null default '',
  PRIMARY KEY (id),
  KEY userid (userid)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS jntag;
CREATE TABLE jntag (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL REFERENCES user.id,
  noteid int(11) NOT NULL REFERENCES note.id,
  tagid int(11) NOT NULL REFERENCES ntag.id,
  PRIMARY KEY (id)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS weather;
CREATE TABLE weather (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL REFERENCES user.id,
  service enum('yweather') NOT NULL DEFAULT 'yweather',
  code varchar(64) NOT NULL DEFAULT '',
  unit enum('C', 'F') NOT NULL DEFAULT 'C',
  description varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY userid (userid)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS weathercache;
CREATE TABLE weathercache (
  code varchar(64) NOT NULL REFERENCES weather.code,
  lastModified varchar(64),
  lastAccessed datetime,
  xml mediumblob,
  PRIMARY KEY (code)
) ENGINE=InnoDB CHARSET=utf8;

-- DROP TABLE IF EXISTS marker;
CREATE TABLE marker (
  id int(11) NOT NULL auto_increment,
  userid int(11) NOT NULL REFERENCES user.id,
  mstamp timestamp,
  markers text not null default '',
  PRIMARY KEY (id),
  KEY userid (userid)
) ENGINE=InnoDB CHARSET=utf8;

Return to:

Send suggestions and report system problems to the System administrator.