CREATE TABLE logs ( id INTEGER UNSIGNED PRIMARY KEY auto_increment, site SMALLINT UNSIGNED NOT NULL, marqueur MEDIUMINT UNSIGNED NULL, visitor_id INTEGER UNSIGNED NOT NULL, page_id MEDIUMINT UNSIGNED NULL, referrer_id INT UNSIGNED NULL, load_time FLOAT UNSIGNED NULL, time DATETIME NOT NULL ); CREATE INDEX marqueur ON logs (marqueur); CREATE INDEX site ON logs (site); CREATE INDEX visitor_id ON logs (visitor_id); CREATE TABLE visitors ( id INTEGER UNSIGNED PRIMARY KEY auto_increment, site INTEGER UNSIGNED NOT NULL, ip VARCHAR(40) NOT NULL, host VARCHAR(100) NULL, country CHAR(2) NOT NULL, browser MEDIUMINT UNSIGNED NULL, browser_version VARCHAR(10) NULL, browser_engine VARCHAR(10) NULL, browser_os TINYINT UNSIGNED NULL, screen_w SMALLINT UNSIGNED NULL, screen_h SMALLINT UNSIGNED NULL, firstseen DATETIME NOT NULL, lastseen DATETIME NOT NULL, is_new ENUM('yes', 'no') NULL, uniqueHash VARCHAR(40) NOT NULL ); CREATE INDEX site ON visitors (site); CREATE INDEX uniqueHash ON visitors (uniqueHash); CREATE INDEX ip ON visitors (ip); CREATE INDEX country ON visitors (country); CREATE INDEX browser ON visitors (browser); CREATE INDEX browser_version ON visitors (browser_version); CREATE INDEX browser_engine ON visitors (browser_engine); CREATE INDEX browser_os ON visitors (browser_os); CREATE INDEX lastseen ON visitors (lastseen); CREATE INDEX firstseen ON visitors (firstseen); CREATE INDEX screen_w ON visitors (screen_w); CREATE INDEX screen_h ON visitors (screen_h); CREATE TABLE robots ( id MEDIUMINT UNSIGNED PRIMARY KEY auto_increment, useragent VARCHAR(200) NOT NULL ) AUTO_INCREMENT=1001; CREATE INDEX useragent ON robots (useragent); CREATE TABLE visitor_langs ( lang CHAR(2) NOT NULL, visitor_id INT UNSIGNED NOT NULL, priority TINYINT UNSIGNED NOT NULL, PRIMARY KEY (lang, visitor_id) ); #CREATE PRIMARY KEY lang ON visitor_langs (lang, visitor_id); CREATE TABLE visitor_plugins ( plugin_id TINYINT UNSIGNED NOT NULL, visitor_id INT UNSIGNED NOT NULL, PRIMARY KEY (plugin_id, visitor_id) ); #CREATE PRIMARY KEY plugin ON visitor_plugins (plugin_id, visitor_id); CREATE TABLE visitor_referrers ( id INT UNSIGNED PRIMARY KEY auto_increment, source ENUM('search', 'external', 'internal') NOT NULL DEFAULT 'external', content VARCHAR(150) NOT NULL ); CREATE UNIQUE INDEX from_source ON visitor_referrers (source, content); CREATE TABLE visitor_pages ( id INT UNSIGNED PRIMARY KEY auto_increment, page VARCHAR(100) NOT NULL, uri VARCHAR(100) NOT NULL ); CREATE UNIQUE INDEX page_unique ON visitor_pages (page, uri); CREATE TABLE sites ( id SMALLINT UNSIGNED PRIMARY KEY auto_increment, name VARCHAR(100) NOT NULL, url VARCHAR(250) NOT NULL ); CREATE TABLE marqueurs ( id MEDIUMINT UNSIGNED PRIMARY KEY auto_increment, site SMALLINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL ); CREATE INDEX site ON marqueurs (site);