· 8 years ago · Jun 15, 2018, 01:40 AM
1<?php
2
3/**
4* Create New Tables
5*
6*/
7// stores overall forum analytics for specific time periods.
8self::$db->query_write("
9 CREATE TABLE IF NOT EXISTS `" . TABLE_PREFIX . "dbtech_vbanalytics_tracking` (
10 `logid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
11 `visits` VARCHAR( 255 ) NOT NULL, # how many landings (also counts how many unique sessions i.e visits)
12 `bounces` INT( 10 ) UNSIGNED NOT NULL, # how many bounces
13 `pageviews` INT( 10 ) UNSIGNED NOT NULL, # how many pageviews
14 `total_time` INT( 10 ) UNSIGNED NOT NULL, # total time spent on site
15 `dateline` INT( 10 ) UNSIGNED NOT NULL,
16 PRIMARY KEY ( `logid` ),
17 KEY (`dateline`)
18 ) ENGINE = MYISAM ;
19");
20self::report('Created Table', 'dbtech_vbanalytics_analytics');
21
22self::$db->query_write("
23 CREATE TABLE IF NOT EXISTS `" . TABLE_PREFIX . "dbtech_vbanalytics_tracking_forums` (
24 `logid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
25 `forumid` INT( 10 ) UNSIGNED NOT NULL ,
26 `replycount` INT( 10 ) UNSIGNED NOT NULL ,
27 `threadcount` INT( 10 ) UNSIGNED NOT NULL ,
28 `visits` VARCHAR( 255 ) NOT NULL,
29 `bounces` INT( 10 ) UNSIGNED NOT NULL,
30 `pageviews` INT( 10 ) UNSIGNED NOT NULL,
31 `landings` INT( 10 ) UNSIGNED NOT NULL,
32 `dateline` INT( 10 ) UNSIGNED NOT NULL,
33 PRIMARY KEY ( `logid` ),
34 KEY (`dateline`)
35 ) ENGINE = MYISAM ;
36");
37self::report('Created Table', 'dbtech_vbanalytics_tracking_forums');
38
39// stores older tracking and user tracking data
40self::$db->query_write("
41 CREATE TABLE IF NOT EXISTS `" . TABLE_PREFIX . "dbtech_vbanalytics_tracking_scripts` (
42 `logid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
43 `this_script` VARCHAR( 255 ) NOT NULL,
44 `visits` VARCHAR( 255 ) NOT NULL,
45 `bounces` INT( 10 ) UNSIGNED NOT NULL,
46 `pageviews` INT( 10 ) UNSIGNED NOT NULL,
47 `landings` INT( 10 ) UNSIGNED NOT NULL,
48 `dateline` INT( 10 ) UNSIGNED NOT NULL,
49 PRIMARY KEY ( `logid` ),
50 KEY (`dateline`)
51 ) ENGINE = MYISAM ;
52");
53self::report('Created Table', 'dbtech_vbanalytics_tracking_scripts');
54
55self::$db->query_write("
56 CREATE TABLE IF NOT EXISTS `" . TABLE_PREFIX . "dbtech_vbanalytics_scripts` (
57 `this_script` VARCHAR( 255 ) NOT NULL,
58 `visits` INT( 10 ) UNSIGNED NOT NULL DEFAULT 1,
59 `bounces` INT( 10 ) UNSIGNED NOT NULL DEFAULT 0,
60 `pageviews` INT( 10 ) UNSIGNED NOT NULL DEFAULT 1,
61 `landings` INT( 10 ) UNSIGNED NOT NULL DEFAULT 0,
62 PRIMARY KEY ( `this_script` )
63 ) ENGINE = MYISAM ;
64");
65self::report('Created Table', 'dbtech_vbanalytics_scripts');
66
67self::$db->query_write("
68 CREATE TABLE IF NOT EXISTS `" . TABLE_PREFIX . "dbtech_vbanalytics_sessions` (
69 `sessionid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
70 `landingpage` VARCHAR( 255 ) NOT NULL, # this_script
71 `pageviews` INT( 10 ) UNSIGNED NOT NULL,
72 `firstactivity` INT( 10 ) UNSIGNED NOT NULL,
73 `lastactivity` INT( 10 ) UNSIGNED NOT NULL,
74 `is_guest` TINYINT( 1 ) UNSIGNED NOT NULL,
75 PRIMARY KEY ( `sessionid` ),
76 KEY `user_dateline` (`firstactivity`,`lastactivity`)
77 ) ENGINE = MYISAM ;
78");
79self::report('Created Table', 'dbtech_vbanalytics_sessions');
80
81self::$db->query_write("
82 CREATE TABLE IF NOT EXISTS `" . TABLE_PREFIX . "dbtech_vbanalytics_search_terms` (
83 `search_term` VARCHAR( 255 ) DEFAULT '',
84 `searches` INT( 10 ) NOT NULL DEFAULT 1, # how many times this term has been searched
85 PRIMARY KEY ( `search_term` )
86 ) ENGINE = MYISAM ;
87");
88self::report('Created Table', 'dbtech_vbanalytics_search_terms');
89
90// stores overall search data
91self::$db->query_write("
92 CREATE TABLE IF NOT EXISTS `" . TABLE_PREFIX . "dbtech_vbanalytics_searches` (
93 `searchid` INT( 10 ) UNSIGNED NOT NULL, # Latest Searchid for the term
94 `search_term` VARCHAR( 255 ) DEFAULT '',
95 `link_clicked` VARCHAR( 255 ) DEFAULT '',
96 `dateline` INT( 10 ) UNSIGNED NOT NULL,
97 PRIMARY KEY ( `searchid` ),
98 KEY (`dateline`)
99 ) ENGINE = MYISAM ;
100");
101self::report('Created Table', 'dbtech_vbanalytics_searches');
102
103/**
104* Alter Existing Tables
105*
106*/
107if (self::$db_alter->fetch_table_info('administrator'))
108{
109 self::$db_alter->add_field(array(
110 'name' => 'dbtech_vbanalyticsadminperms',
111 'type' => 'int',
112 'length' => '10',
113 'attributes' => 'unsigned',
114 'null' => false, // True = NULL, false = NOT NULL
115 'default' => '0'
116 ));
117 self::report('Altered Table', 'administrator');
118}
119
120if (self::$db_alter->fetch_table_info('usergroup'))
121{
122 self::$db_alter->add_field(array(
123 'name' => 'dbtech_vbanalyticspermissions',
124 'type' => 'int',
125 'length' => '10',
126 'attributes' => 'unsigned',
127 'null' => false, // True = NULL, false = NOT NULL
128 'default' => '0'
129 ));
130 self::report('Altered Table', 'usergroup');
131}
132
133if (self::$db_alter->fetch_table_info('forum'))
134{
135 self::$db_alter->add_field(array(
136 'name' => 'dbtech_vbanalytics_firstpost',
137 'type' => 'int',
138 'length' => '10',
139 'attributes' => 'unsigned',
140 'null' => false, // True = NULL, false = NOT NULL
141 'default' => '0'
142 ));
143
144 // tracks total views of all the forums threads
145 self::$db_alter->add_field(array(
146 'name' => 'dbtech_vbanalytics_totalviews',
147 'type' => 'int',
148 'length' => '10',
149 'attributes' => 'unsigned',
150 'null' => false, // True = NULL, false = NOT NULL
151 'default' => '0'
152 ));
153
154 self::$db_alter->add_field(array(
155 'name' => 'dbtech_vbanalytics_visits',
156 'type' => 'int',
157 'length' => '10',
158 'attributes' => 'unsigned',
159 'null' => false, // True = NULL, false = NOT NULL
160 'default' => '0'
161 ));
162
163 self::$db_alter->add_field(array(
164 'name' => 'dbtech_vbanalytics_bounces',
165 'type' => 'int',
166 'length' => '10',
167 'attributes' => 'unsigned',
168 'null' => false, // True = NULL, false = NOT NULL
169 'default' => '0'
170 ));
171
172 // tracks actual forumid views (forumdisplay)
173 self::$db_alter->add_field(array(
174 'name' => 'dbtech_vbanalytics_pageviews',
175 'type' => 'int',
176 'length' => '10',
177 'attributes' => 'unsigned',
178 'null' => false, // True = NULL, false = NOT NULL
179 'default' => '0'
180 ));
181
182 self::$db_alter->add_field(array(
183 'name' => 'dbtech_vbanalytics_landings',
184 'type' => 'int',
185 'length' => '10',
186 'attributes' => 'unsigned',
187 'null' => false, // True = NULL, false = NOT NULL
188 'default' => '0'
189 ));
190 self::report('Altered Table', 'forum');
191}
192
193if (self::$db_alter->fetch_table_info('session'))
194{
195 self::$db_alter->add_field(array(
196 'name' => 'dbtech_vbanalytics_first_location',
197 'type' => 'varchar',
198 'length' => '100',
199 'null' => false // True = NULL, false = NOT NULL
200 ));
201
202 self::$db_alter->add_field(array(
203 'name' => 'dbtech_vbanalytics_first_activity',
204 'type' => 'int',
205 'length' => '10',
206 'attributes' => 'unsigned',
207 'null' => false // True = NULL, false = NOT NULL
208 ));
209
210 self::$db_alter->add_field(array(
211 'name' => 'dbtech_vbanalytics_pageviews',
212 'type' => 'int',
213 'length' => '10',
214 'attributes' => 'unsigned',
215 'null' => false, // True = NULL, false = NOT NULL
216 'default' => '1'
217 ));
218
219 // will track what unqiue forums and this_script the session has visited
220 self::$db_alter->add_field(array(
221 'name' => 'dbtech_vbanalytics_visits',
222 'type' => 'mediumtext',
223 'null' => true, // True = NULL, false = NOT NULL
224 'default' => NULL
225 ));
226 self::report('Altered Table', 'session');
227}
228
229define('CP_REDIRECT', 'vbanalytics.php?do=finalise&version=100');
230define('DISABLE_PRODUCT_REDIRECT', true);