r41 - in phpOpenTracker/trunk: . phpOpenTracker phpOpenTracker/docs phpOpenTracker/docs/sql phpOpenTracker/docs/sql/mysql
Sebastian Bergmann at BerliOS <[email protected]> Mon, 21 Mar 2005 08:59:24 +0100
| Newsgroups | gmane.comp.web.phpopentracker.cvs |
|---|---|
| Message-ID | <[email protected]> |
Author: s_bergmann Date: 2005-03-21 08:58:49 +0100 (Mon, 21 Mar 2005) New Revision: 41 Added: phpOpenTracker/trunk/phpOpenTracker/ phpOpenTracker/trunk/phpOpenTracker/docs/ phpOpenTracker/trunk/phpOpenTracker/docs/sql/ phpOpenTracker/trunk/phpOpenTracker/docs/sql/mysql/ phpOpenTracker/trunk/phpOpenTracker/docs/sql/mysql/schema.sql phpOpenTracker/trunk/phpOpenTracker/docs/sql/mysql/test.sql Log: New database schema. Added: phpOpenTracker/trunk/phpOpenTracker/docs/sql/mysql/schema.sql =================================================================== --- phpOpenTracker/trunk/phpOpenTracker/docs/sql/mysql/schema.sql 2005-02-24 06:11:03 UTC (rev 40) +++ phpOpenTracker/trunk/phpOpenTracker/docs/sql/mysql/schema.sql 2005-03-21 07:58:49 UTC (rev 41) @@ -0,0 +1,73 @@ +# ##### BEGIN LICENSE BLOCK ##### +# Version: Apache License 2.0 +# +# The contents of this file are subject to the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Software distributed under the License is distributed on an "AS IS" basis, +# WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License +# for the specific language governing rights and limitations under the +# License. +# +# The Original Code is the phpOpenTracker Database Schema. +# +# The Initial Developer of the Original Code is Sebastian Bergmann. +# Portions created by the Initial Developer are Copyright (C) 2000-2005 +# the Initial Developer. All Rights Reserved. +# +# Contributor(s): +# +# ##### END LICENSE BLOCK ##### + +DROP TABLE IF EXISTS pot_page_impressions; +DROP TABLE IF EXISTS pot_visits; +DROP TABLE IF EXISTS pot_additional_data; + +# pot_page_impressions is composed of MyISAM Merge Tables +# that are automatically created and managed. +# +# CREATE TABLE pot_page_impressions ( +# visit_id INTEGER UNSIGNED NOT NULL REFERENCES pot_visits(visit_id), +# timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, +# weekday TINYINT UNSIGNED NOT NULL, +# hour TINYINT UNSIGNED NOT NULL, +# document VARCHAR(255) NOT NULL, +# document_url VARCHAR(255) NOT NULL, +# exit_target VARCHAR(255) NOT NULL, +# entry_document BOOLEAN NOT NULL, +# +# KEY visit_id (visit_id), +# KEY timestamp (timestamp), +# KEY document (document(32)), +# KEY exit_target (exit_target(32)) +# ) ENGINE=MYISAM; + +# pot_visits is composed of MyISAM Merge Tables +# that are automatically created and managed. +# +# CREATE TABLE pot_visits ( +# visit_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, +# visitor_id CHAR(36) NOT NULL, +# client_id INTEGER UNSIGNED NOT NULL, +# timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, +# weekday TINYINT UNSIGNED NOT NULL, +# hour TINYINT UNSIGNED NOT NULL, +# returning_visitor BOOLEAN NOT NULL, +# host VARCHAR(255) NOT NULL, +# operating_system VARCHAR(255) NOT NULL, +# user_agent VARCHAR(255) NOT NULL, +# referer VARCHAR(255) NOT NULL, +# +# PRIMARY KEY (visit_id), +# KEY client_time (client_id, timestamp) +# ) ENGINE=MYISAM; + +CREATE TABLE pot_additional_data ( + visit_id INTEGER UNSIGNED NOT NULL REFERENCES pot_visits(visit_id), + data_field VARCHAR(32) NOT NULL, + data_value VARCHAR(255) NOT NULL, + + PRIMARY KEY (visit_id, data_field) +) ENGINE=MYISAM; Added: phpOpenTracker/trunk/phpOpenTracker/docs/sql/mysql/test.sql =================================================================== --- phpOpenTracker/trunk/phpOpenTracker/docs/sql/mysql/test.sql 2005-02-24 06:11:03 UTC (rev 40) +++ phpOpenTracker/trunk/phpOpenTracker/docs/sql/mysql/test.sql 2005-03-21 07:58:49 UTC (rev 41) @@ -0,0 +1,184 @@ +# ##### BEGIN LICENSE BLOCK ##### +# Version: Apache License 2.0 +# +# The contents of this file are subject to the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Software distributed under the License is distributed on an "AS IS" basis, +# WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License +# for the specific language governing rights and limitations under the +# License. +# +# The Original Code is the phpOpenTracker Test Data. +# +# The Initial Developer of the Original Code is Sebastian Bergmann. +# Portions created by the Initial Developer are Copyright (C) 2000-2005 +# the Initial Developer. All Rights Reserved. +# +# Contributor(s): +# +# ##### END LICENSE BLOCK ##### + +DROP TABLE IF EXISTS pot_additional_data; +DROP TABLE IF EXISTS pot_page_impressions; +DROP TABLE IF EXISTS pot_page_impressions_2005; +DROP TABLE IF EXISTS pot_page_impressions_200501; +DROP TABLE IF EXISTS pot_page_impressions_20050101; +DROP TABLE IF EXISTS pot_visits; +DROP TABLE IF EXISTS pot_visits_2005; +DROP TABLE IF EXISTS pot_visits_200501; +DROP TABLE IF EXISTS pot_visits_20050101; + +CREATE TABLE pot_page_impressions_20050101 ( + visit_id INTEGER UNSIGNED NOT NULL REFERENCES pot_visits(visit_id), + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + weekday TINYINT UNSIGNED NOT NULL, + hour TINYINT UNSIGNED NOT NULL, + document VARCHAR(255) NOT NULL, + document_url VARCHAR(255) NOT NULL, + exit_target VARCHAR(255) NOT NULL, + entry_document BOOLEAN NOT NULL, + + KEY visit_id (visit_id), + KEY timestamp (timestamp), + KEY document (document(32)), + KEY exit_target (exit_target(32)) +) ENGINE=MYISAM; + +CREATE TABLE pot_page_impressions_200501 ( + visit_id INTEGER UNSIGNED NOT NULL REFERENCES pot_visits(visit_id), + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + weekday TINYINT UNSIGNED NOT NULL, + hour TINYINT UNSIGNED NOT NULL, + document VARCHAR(255) NOT NULL, + document_url VARCHAR(255) NOT NULL, + exit_target VARCHAR(255) NOT NULL, + entry_document BOOLEAN NOT NULL, + + KEY visit_id (visit_id), + KEY timestamp (timestamp), + KEY document (document(32)), + KEY exit_target (exit_target(32)) +) ENGINE=MERGE UNION=(pot_page_impressions_20050101); + +CREATE TABLE pot_page_impressions_2005 ( + visit_id INTEGER UNSIGNED NOT NULL REFERENCES pot_visits(visit_id), + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + weekday TINYINT UNSIGNED NOT NULL, + hour TINYINT UNSIGNED NOT NULL, + document VARCHAR(255) NOT NULL, + document_url VARCHAR(255) NOT NULL, + exit_target VARCHAR(255) NOT NULL, + entry_document BOOLEAN NOT NULL, + + KEY visit_id (visit_id), + KEY timestamp (timestamp), + KEY document (document(32)), + KEY exit_target (exit_target(32)) +) ENGINE=MERGE UNION=(pot_page_impressions_20050101); + +CREATE TABLE pot_page_impressions ( + visit_id INTEGER UNSIGNED NOT NULL REFERENCES pot_visits(visit_id), + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + weekday TINYINT UNSIGNED NOT NULL, + hour TINYINT UNSIGNED NOT NULL, + document VARCHAR(255) NOT NULL, + document_url VARCHAR(255) NOT NULL, + exit_target VARCHAR(255) NOT NULL, + entry_document BOOLEAN NOT NULL, + + KEY visit_id (visit_id), + KEY timestamp (timestamp), + KEY document (document(32)), + KEY exit_target (exit_target(32)) +) ENGINE=MERGE UNION=(pot_page_impressions_20050101); + +CREATE TABLE pot_visits_20050101 ( + visit_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + visitor_id CHAR(36) NOT NULL, + client_id INTEGER UNSIGNED NOT NULL, + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + weekday TINYINT UNSIGNED NOT NULL, + hour TINYINT UNSIGNED NOT NULL, + returning_visitor BOOLEAN NOT NULL, + host VARCHAR(255) NOT NULL, + operating_system VARCHAR(255) NOT NULL, + user_agent VARCHAR(255) NOT NULL, + referer VARCHAR(255) NOT NULL, + + PRIMARY KEY (visit_id), + KEY client_time (client_id, timestamp) +) ENGINE=MYISAM; + +CREATE TABLE pot_visits_200501 ( + visit_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + visitor_id CHAR(36) NOT NULL, + client_id INTEGER UNSIGNED NOT NULL, + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + weekday TINYINT UNSIGNED NOT NULL, + hour TINYINT UNSIGNED NOT NULL, + returning_visitor BOOLEAN NOT NULL, + host VARCHAR(255) NOT NULL, + operating_system VARCHAR(255) NOT NULL, + user_agent VARCHAR(255) NOT NULL, + referer VARCHAR(255) NOT NULL, + + PRIMARY KEY (visit_id), + KEY client_time (client_id, timestamp) +) ENGINE=MERGE UNION=(pot_visits_20050101); + +CREATE TABLE pot_visits_2005 ( + visit_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + visitor_id CHAR(36) NOT NULL, + client_id INTEGER UNSIGNED NOT NULL, + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + weekday TINYINT UNSIGNED NOT NULL, + hour TINYINT UNSIGNED NOT NULL, + returning_visitor BOOLEAN NOT NULL, + host VARCHAR(255) NOT NULL, + operating_system VARCHAR(255) NOT NULL, + user_agent VARCHAR(255) NOT NULL, + referer VARCHAR(255) NOT NULL, + + PRIMARY KEY (visit_id), + KEY client_time (client_id, timestamp) +) ENGINE=MERGE UNION=(pot_visits_20050101); + +CREATE TABLE pot_visits ( + visit_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, + visitor_id CHAR(36) NOT NULL, + client_id INTEGER UNSIGNED NOT NULL, + timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + weekday TINYINT UNSIGNED NOT NULL, + hour TINYINT UNSIGNED NOT NULL, + returning_visitor BOOLEAN NOT NULL, + host VARCHAR(255) NOT NULL, + operating_system VARCHAR(255) NOT NULL, + user_agent VARCHAR(255) NOT NULL, + referer VARCHAR(255) NOT NULL, + + PRIMARY KEY (visit_id), + KEY client_time (client_id, timestamp) +) ENGINE=MERGE UNION=(pot_visits_20050101); + +CREATE TABLE pot_additional_data ( + visit_id INTEGER UNSIGNED NOT NULL REFERENCES pot_visits(visit_id), + data_field VARCHAR(32) NOT NULL, + data_value VARCHAR(255) NOT NULL, + + PRIMARY KEY (visit_id, data_field) +); + +INSERT INTO pot_visits_20050101 VALUES (1, '3041d34e-c317-1027-813f-196097d66a8f', 1, 0x323030352d30312d32392030383a33323a3536, 6, 8, 0, 'wopr', 'Linux [unknown version]', 'Mozilla Firefox 1.0', 'www.sebastian-bergmann.de'); +INSERT INTO pot_page_impressions_20050101 VALUES (1, 0x323030352d30312d32392030383a33323a3536, 6, 8, 'Document 1', '/document.php?id=1', '', 1); +INSERT INTO pot_page_impressions_20050101 VALUES (1, 0x323030352d30312d32392030383a33333a3030, 6, 8, 'Document 2', '/document.php?id=2', '', 0); +INSERT INTO pot_page_impressions_20050101 VALUES (1, 0x323030352d30312d32392030383a33333a3032, 6, 8, 'Document 3', '/document.php?id=3', 'www.phpopentracker.de', 0); + +INSERT INTO pot_visits_20050101 VALUES (2, '3c3b4fc9-c317-1027-813f-196097d66a8f', 1, 0x323030352d30312d32392030383a33333a3130, 6, 8, 0, 'wopr-mobile', 'Linux [unknown version]', 'Mozilla Firefox 1.0', ''); +INSERT INTO pot_page_impressions_20050101 VALUES (2, 0x323030352d30312d32392030383a33333a3130, 6, 8, 'Document 1', '/document.php?id=1', '', 1); +INSERT INTO pot_page_impressions_20050101 VALUES (2, 0x323030352d30312d32392030383a33333a3132, 6, 8, 'Document 2', '/document.php?id=2', '', 0); + +INSERT INTO pot_visits_20050101 VALUES (3, '3041d34e-c317-1027-813f-196097d66a8f', 1, 0x323030352d30312d32392030383a34323a3132, 6, 8, 1, 'wopr', 'Linux [unknown version]', 'Mozilla Firefox 1.0', ''); +INSERT INTO pot_page_impressions_20050101 VALUES (3, 0x323030352d30312d32392030383a34323a3132, 6, 8, 'Document 1', '/document.php?id=1', '', 1);