betterlinks / includes / Traits / DBTables.php

DBTables.php in BetterLinks – Link Shortener, Link Cloaking, Redirects, Affiliate Link Manager & MCP trunk, at includes/Traits/DBTables.php

270 lines 11.3 KB

Raw Download Zip

1	<?php
2	namespace BetterLinks\Traits;
3	if ( ! defined( 'ABSPATH' ) ) { exit; }
4
5	// phpcs:disable PluginCheck.Security.DirectDB, WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL
6
7	trait DBTables
8	{
9	public function createBetterLinksTable()
10	{
11	$table_name = $this->wpdb->prefix . 'betterlinks';
12	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
13	ID bigint(20) unsigned NOT NULL auto_increment,
14	link_author bigint(20) unsigned NOT NULL default '0',
15	link_date datetime NOT NULL default CURRENT_TIMESTAMP,
16	link_date_gmt datetime NOT NULL default CURRENT_TIMESTAMP,
17	link_title text NOT NULL,
18	link_slug varchar(200) NOT NULL default '',
19	link_note text NOT NULL,
20	link_status varchar(20) NOT NULL default 'publish',
21	nofollow varchar(10),
22	sponsored varchar(10),
23	track_me varchar(10),
24	param_forwarding varchar(10),
25	param_struct varchar(255) default NULL,
26	redirect_type varchar(255) default '307',
27	target_url text default NULL,
28	short_url varchar(255) default NULL,
29	link_order tinyint(11) default 0,
30	link_modified datetime NOT NULL default CURRENT_TIMESTAMP,
31	link_modified_gmt datetime NOT NULL default CURRENT_TIMESTAMP,
32	wildcards boolean NOT NULL default 0,
33	expire text default NULL,
34	dynamic_redirect text default NULL,
35	favorite varchar(255),
36	PRIMARY KEY (ID),
37	KEY link_slug (link_slug(191)),
38	KEY type_status_date (link_status,link_date,ID),
39	KEY link_author (link_author),
40	KEY link_order (link_order)
41	) $this->charset_collate;";
42	dbDelta($sql);
43	}
44
45	public function createBetterTermsTable()
46	{
47	$table_name = $this->wpdb->prefix . 'betterlinks_terms';
48	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
49	ID bigint(20) unsigned NOT NULL auto_increment,
50	term_name text NOT NULL,
51	term_slug varchar(200) NOT NULL default '',
52	term_type varchar(15) NOT NULL,
53	term_order tinyint(11) default 0,
54	PRIMARY KEY (ID),
55	KEY term_slug (term_slug(191)),
56	key term_type (term_type),
57	key term_order (term_order)
58	) $this->charset_collate;";
59	dbDelta($sql);
60	}
61
62	public function createBetterTermsRelationshipsTable()
63	{
64	$table_name = $this->wpdb->prefix . 'betterlinks_terms_relationships';
65	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
66	ID bigint(20) unsigned NOT NULL auto_increment,
67	term_id bigint(20) default 0,
68	link_id bigint(20) default 0,
69	PRIMARY KEY (ID),
70	KEY term_id (term_id),
71	key link_id (link_id)
72	) $this->charset_collate;";
73	dbDelta($sql);
74	}
75
76	public function createBetterClicksTable()
77	{
78	$table_name = $this->wpdb->prefix . 'betterlinks_clicks';
79	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
80	ID bigint(20) unsigned NOT NULL auto_increment,
81	link_id bigint(20) NOT NULL,
82	ip varchar(255) NULL,
83	browser varchar(255) NULL,
84	os varchar(255) NULL,
85	device varchar(20) NULL,
86	brand_name VARCHAR(20) NULL,
87	model VARCHAR(20) NULL,
88	bot_name VARCHAR(20) NULL,
89	browser_type VARCHAR(20) NULL,
90	os_version VARCHAR(20) NULL,
91	browser_version VARCHAR(20) NULL,
92	`language` VARCHAR(10) NULL,
93	`query_params` TEXT NULL,
94	`country_id` int(11) unsigned NULL,
95	referer varchar(255) NULL,
96	host varchar(255) NULL,
97	uri varchar(255) NULL,
98	click_count tinyint(4) NOT NULL default 0,
99	visitor_id varchar(25) NULL,
100	click_order tinyint(11) default 0,
101	created_at datetime NOT NULL default CURRENT_TIMESTAMP,
102	created_at_gmt datetime NOT NULL default CURRENT_TIMESTAMP,
103	rotation_target_url varchar(255) NULL,
104	PRIMARY KEY (ID),
105	KEY ip (ip),
106	key link_id (link_id),
107	key click_order (click_order),
108	KEY idx_country_id (country_id)
109	) $this->charset_collate;";
110	dbDelta($sql);
111	}
112
113	public function createBetterLinkMetaTable()
114	{
115	$table_name = $this->wpdb->prefix . 'betterlinkmeta';
116	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
117	meta_id bigint(20) unsigned NOT NULL auto_increment,
118	link_id bigint(20) unsigned NOT NULL default '0',
119	meta_key varchar(255) NOT NULL default '',
120	meta_value longtext NOT NULL,
121	PRIMARY KEY (meta_id),
122	KEY link_id (link_id),
123	KEY meta_key (meta_key)
124	) $this->charset_collate;";
125	dbDelta($sql);
126	}
127
128	public function createBetterLinkPasswordTable() {
129	$table_name = $this->wpdb->prefix . 'betterlinks_password';
130	$ref_table_name = $this->wpdb->prefix . 'betterlinks';
131	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
132	`id` INT NOT NULL AUTO_INCREMENT,
133	`link_id` bigint(20) unsigned NOT NULL,
134	`password` VARCHAR(255),
135	`status` BOOLEAN,
136	`allow_contact` BOOLEAN default false,
137	PRIMARY KEY (`id`),
138	FOREIGN KEY (`link_id`) REFERENCES $ref_table_name (`ID`)
139	ON DELETE CASCADE
140	) $this->charset_collate;";
141	dbDelta($sql);
142	}
143
144	public function modifyBetterLinksTable() {
145	$table_name = $this->wpdb->prefix . 'betterlinks';
146	$sql = "ALTER TABLE {$table_name}
147	MODIFY target_url text default null,
148	MODIFY link_date datetime NOT NULL default CURRENT_TIMESTAMP,
149	MODIFY link_date_gmt datetime NOT NULL default CURRENT_TIMESTAMP,
150	MODIFY link_modified datetime NOT NULL default CURRENT_TIMESTAMP,
151	MODIFY link_modified_gmt datetime NOT NULL default CURRENT_TIMESTAMP;";
152	$this->wpdb->query($sql);
153	}
154
155	public function modifyBetterLinksClicksTable() {
156	global $wpdb;
157
158	// todo: 👇 if `device` column exists that means we already have added device, brand_name, model, bot_name, browser_type, os_version, browser_version, language column
159	// todo: so, we need to skip the betterlinks_clicks table alterations
160	$check_column_exists_sql = sprintf( 'select `column_name` from information_schema.columns where table_schema="%1$s" and table_name="%2$sbetterlinks_clicks" and column_name="device";', DB_NAME, $wpdb->prefix );
161	$result = $wpdb->query( $check_column_exists_sql );
162
163	if ( ! $result ) {
164	$table_name = $wpdb->prefix . 'betterlinks_clicks';
165	$sql = "ALTER TABLE {$table_name}
166	MODIFY created_at datetime NOT NULL default CURRENT_TIMESTAMP,
167	MODIFY created_at_gmt datetime NOT NULL default CURRENT_TIMESTAMP,
168	ADD COLUMN `device` VARCHAR(20) NULL AFTER `os`,
169	ADD COLUMN `brand_name` VARCHAR(20) NULL AFTER `device`,
170	ADD COLUMN `model` VARCHAR(20) NULL AFTER `brand_name`,
171	ADD COLUMN `bot_name` VARCHAR(20) NULL AFTER `model`,
172	ADD COLUMN `browser_type` VARCHAR(20) NULL AFTER `bot_name`,
173	ADD COLUMN `os_version` VARCHAR(20) NULL AFTER `browser_type`,
174	ADD COLUMN `browser_version` VARCHAR(20) NULL AFTER `os_version`,
175	ADD COLUMN `language` VARCHAR(10) NULL AFTER `browser_version`;";
176
177	$wpdb->query( $sql );
178	}
179	}
180
181	// adding query_params column into betterlinks_clicks table
182	public function modifyBetterLinksClicksTable2() {
183	global $wpdb;
184
185	$check_column_exists_sql = sprintf( 'select `column_name` from information_schema.columns where table_schema="%1$s" and table_name="%2$sbetterlinks_clicks" and column_name="query_params";', DB_NAME, $wpdb->prefix );
186	$result = $wpdb->query( $check_column_exists_sql );
187
188	if ( ! $result ) {
189	$table_name = $wpdb->prefix . 'betterlinks_clicks';
190
191	$sql = "ALTER TABLE {$table_name}
192	ADD COLUMN `query_params` TEXT NULL AFTER `language`;";
193	$wpdb->query( $sql );
194	}
195	}
196
197	// Create countries lookup table for efficient storage and querying
198	public function createBetterLinksCountriesTable() {
199	$table_name = $this->wpdb->prefix . 'betterlinks_countries';
200	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
201	id int(11) unsigned NOT NULL auto_increment,
202	country_code varchar(2) NOT NULL,
203	country_name varchar(100) NOT NULL,
204	created_at datetime NOT NULL default CURRENT_TIMESTAMP,
205	updated_at datetime NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
206	PRIMARY KEY (id),
207	UNIQUE KEY unique_country_code (country_code),
208	KEY idx_country_name (country_name)
209	) $this->charset_collate;";
210	dbDelta($sql);
211	}
212
213	// Add country_id foreign key column to betterlinks_clicks table
214	public function modifyBetterLinksClicksTable4() {
215	global $wpdb;
216
217	$table_name = $wpdb->prefix . 'betterlinks_clicks';
218	$countries_table = $wpdb->prefix . 'betterlinks_countries';
219
220	// Check if country_id column exists
221	$column_exists = $wpdb->get_var( $wpdb->prepare(
222	"SELECT COUNT(*) FROM information_schema.columns
223	WHERE table_schema = %s
224	AND table_name = %s
225	AND column_name = 'country_id'",
226	DB_NAME,
227	$table_name
228	) );
229
230	if ( ! $column_exists ) {
231	$sql = "ALTER TABLE {$table_name}
232	ADD COLUMN `country_id` int(11) unsigned NULL AFTER `query_params`,
233	ADD INDEX `idx_country_id` (`country_id`),
234	ADD CONSTRAINT `fk_clicks_country_id`
235	FOREIGN KEY (`country_id`)
236	REFERENCES {$countries_table}(`id`)
237	ON DELETE SET NULL
238	ON UPDATE CASCADE;";
239
240	$wpdb->query( $sql );
241	}
242	}
243	public function createBetterUserAgentsTable() {
244	$table_name = $this->wpdb->prefix . 'betterlinks_user_agents';
245	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
246	id bigint(20) unsigned NOT NULL auto_increment,
247	user_agent text NOT NULL,
248	created_at datetime NOT NULL default CURRENT_TIMESTAMP,
249	PRIMARY KEY (id),
250	UNIQUE KEY user_agent_hash (user_agent(255))
251	) $this->charset_collate;";
252	dbDelta($sql);
253	}
254
255	public function modifyBetterLinksClicksTableAddUserAgent() {
256	global $wpdb;
257
258	$check_column_exists_sql = sprintf( 'select `column_name` from information_schema.columns where table_schema="%1$s" and table_name="%2$sbetterlinks_clicks" and column_name="user_agent_id";', DB_NAME, $wpdb->prefix );
259	$result = $wpdb->query( $check_column_exists_sql );
260
261	if ( ! $result ) {
262	$table_name = $wpdb->prefix . 'betterlinks_clicks';
263	$sql = "ALTER TABLE {$table_name}
264	ADD COLUMN `user_agent_id` bigint(20) unsigned NULL AFTER `query_params`,
265	ADD INDEX `idx_user_agent_id` (`user_agent_id`);";
266	$wpdb->query( $sql );
267	}
268	}
269	}
270