install.php 15.6 KB
Newer Older
mattl's avatar
mattl committed
1 2
<?php

3
/* GNUkebox -- a free software server for recording your listening habits
mattl's avatar
mattl committed
4

5
   Copyright (C) 2009, 2015 Free Software Foundation, Inc
mattl's avatar
mattl committed
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

   This program is free software: you can redistribute it and/or modify
   it under the terms of the GNU Affero 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 Affero General Public License for more details.

   You should have received a copy of the GNU Affero General Public License
   along with this program.  If not, see <http://www.gnu.org/licenses/>.

*/

22
require_once(__DIR__ . '/vendor/autoload.php');
23
require_once('version.php');
24
require_once('utils/get_absolute_url.php');
25

26 27
if (file_exists('config.php')) {
	die('A configuration file already exists. Please delete <i>config.php</i> if you wish to reinstall.');
28 29 30 31 32 33
}

if (isset($_POST['install'])) {

	//Get the database connection string
	$dbms = $_POST['dbms'];
34
	if ($dbms == 'sqlite') {
35
		$filename = urlencode($_POST['filename']);
36
		$connect_string = 'sqlite://' . $filename;
37
	} else {
38
		$connect_string = $dbms . '://' . $_POST['username'] . ':' . $_POST['password'] . '@' . $_POST['hostname'] . ':' . $_POST['port'] . '/' . $_POST['dbname'];
39
	}
40 41

	$adodb_connect_string = str_replace('pgsql:', 'postgres:', $connect_string);
42

43 44
	try {
		$adodb =& NewADOConnection($adodb_connect_string);
45
	} catch (Exception $e) {
46 47
		var_dump($e);
		adodb_backtrace($e->gettrace());
clint's avatar
clint committed
48
		die("Database connection failure\n");
49 50 51
	}

	//Create tables
52

53
	$stage_one_queries = array(
54
		'CREATE TABLE Places(
jurgbohn's avatar
jurgbohn committed
55
		location_uri VARCHAR(255) unique,
56 57
		latitude FLOAT,
		longitude FLOAT,
58
		country CHAR(2))',
59

60
		'CREATE TABLE Countries (
tobyink's avatar
tobyink committed
61 62
		country varchar(2) PRIMARY KEY,
		country_name varchar(200),
63
		wikipedia_en varchar(120));',
tobyink's avatar
tobyink committed
64

65
		'CREATE TABLE Users (
clint's avatar
clint committed
66
		uniqueid SERIAL PRIMARY KEY,
67
		username VARCHAR(64) unique,
68 69 70
		password VARCHAR(32) NOT NULL,
		email VARCHAR(255),
		fullname VARCHAR(255),
71 72 73
		bio TEXT,
		homepage VARCHAR(255),
		location VARCHAR(255),
74
		userlevel INTEGER DEFAULT 0,
clint's avatar
clint committed
75
		anticommercial INTEGER DEFAULT 0,
76 77
		webid_uri VARCHAR(255),
		avatar_uri VARCHAR(255),
78
		openid_uri VARCHAR(100),
79
		active INTEGER DEFAULT 0,
mattl's avatar
mattl committed
80
		public_export INTEGER DEFAULT 0,
tobyink's avatar
tobyink committed
81 82
		location_uri VARCHAR(255) REFERENCES Places(location_uri),
		laconica_profile VARCHAR(255),
83
		created INTEGER DEFAULT 0,
84
		modified INTEGER DEFAULT 0,
85 86
		journal_rss VARCHAR(255),
		receive_emails INTEGER DEFAULT 1)',
87

88
		'CREATE TABLE Groups (
89 90
		id SERIAL PRIMARY KEY,
		groupname VARCHAR(64),
clint's avatar
clint committed
91
		owner INTEGER REFERENCES Users(uniqueid),
92 93 94
		fullname VARCHAR(255),
		bio TEXT,
		homepage VARCHAR(255),
95
		created INTEGER NOT NULL,
96
		modified INTEGER,
tobyink's avatar
tobyink committed
97
		avatar_uri VARCHAR(255),
98
		grouptype INTEGER)',
99

100
		'CREATE TABLE Group_Members (
101
		grp INTEGER REFERENCES Groups(id),
clint's avatar
clint committed
102
		member INTEGER REFERENCES Users(uniqueid),
103
		joined INTEGER NOT NULL,
104
		PRIMARY KEY (grp, member))',
105

106
		# TODO: REMOVE
107
		'CREATE TABLE AccountActivation(
108
		username VARCHAR(64),
109
		authcode VARCHAR(32),
110
		expires INTEGER)',
111

112
		'CREATE TABLE Auth (
113
		token VARCHAR(32) PRIMARY KEY,
114
		sk VARCHAR(32),
clint's avatar
clint committed
115
		expires INTEGER,
116
		username VARCHAR(64) REFERENCES Users(username))',
117

118
		'CREATE TABLE Artist(
119
		id SERIAL PRIMARY KEY,
120
		name VARCHAR(255) unique,
121
		mbid VARCHAR(36),
122
		imbid INTEGER,
clint's avatar
clint committed
123 124
		streamable INTEGER,
		bio_published INTEGER,
125 126 127 128
		bio_content TEXT,
		bio_summary TEXT,
		image_small VARCHAR(255),
		image_medium VARCHAR(255),
jurgbohn's avatar
jurgbohn committed
129
		image_large VARCHAR(255),
tobyink's avatar
tobyink committed
130
		homepage VARCHAR(255),
131
		hashtag VARCHAR(255),
132
		origin VARCHAR(255) REFERENCES Places(location_uri),
133
		flattr_uid VARCHAR(255))',
134

135
		'CREATE TABLE Album(
136
		id SERIAL PRIMARY KEY,
137
		name VARCHAR(255),
138 139
		artist_name VARCHAR(255) REFERENCES Artist(name),
		mbid VARCHAR(36),
140 141
		image VARCHAR(255),
		artwork_license VARCHAR(255),
clint's avatar
clint committed
142
		releasedate INTEGER,
143
		albumurl VARCHAR(255),
144
		downloadurl VARCHAR(255))',
145

146
		'CREATE TABLE Similar_Artist(
147 148
		name_a VARCHAR(255) REFERENCES Artist(name),
		name_b VARCHAR(255) REFERENCES Artist(name),
149
		PRIMARY KEY(name_a, name_b))'
150
	);
151

152
	$stage_two_queries_mysql = array(
153
		'CREATE TABLE Track(
154 155 156 157 158 159 160 161 162 163
		id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		name VARCHAR(255),
		artist_name VARCHAR(255) REFERENCES Artist(name),
		album_name VARCHAR(255),
		mbid VARCHAR(36),
		duration INTEGER,
		streamable INTEGER DEFAULT 0,
		license VARCHAR(255),
		downloadurl VARCHAR(255),
		streamurl VARCHAR(255),
164
		otherid VARCHAR(16))'
165 166 167
	);

	$stage_two_queries_other = array(
168 169
		'CREATE SEQUENCE track_id_seq;',
		'CREATE TABLE Track(
170
		id INTEGER NOT NULL DEFAULT nextval(\'track_id_seq\'::regclass) PRIMARY KEY,
171 172 173 174 175 176 177 178 179
		name VARCHAR(255),
		artist_name VARCHAR(255) REFERENCES Artist(name),
		album_name VARCHAR(255),
		mbid VARCHAR(36),
		duration INTEGER,
		streamable INTEGER DEFAULT 0,
		license VARCHAR(255),
		downloadurl VARCHAR(255),
		streamurl VARCHAR(255),
180
		otherid VARCHAR(16))'
181 182 183
	);

	$stage_three_queries = array(
184
		'CREATE TABLE Scrobbles(
185
		userid INTEGER REFERENCES Users(uniqueid),
186 187
		track VARCHAR(255),
		album VARCHAR(255),
elleo's avatar
elleo committed
188
		artist VARCHAR(255) REFERENCES Artist(name),
clint's avatar
clint committed
189
		time INTEGER,
190
		mbid VARCHAR(36),
191 192
		source VARCHAR(6),
		rating CHAR(1),
clint's avatar
clint committed
193
		length INTEGER,
194
		stid INTEGER)',
elleo's avatar
elleo committed
195

196
		'CREATE TABLE Scrobble_Sessions(
197
		userid INTEGER REFERENCES Users(uniqueid),
clint's avatar
clint committed
198
		sessionid VARCHAR(32) PRIMARY KEY,
clint's avatar
clint committed
199
		client CHAR(3),
200
		api_key VARCHAR(32),
201
		expires INTEGER)',
elleo's avatar
elleo committed
202

203
		'CREATE TABLE Now_Playing(
204
		sessionid VARCHAR(32) PRIMARY KEY REFERENCES Scrobble_Sessions(sessionid) ON DELETE CASCADE,
205
		track VARCHAR(255),
206
		artist VARCHAR(255),
207
		album VARCHAR(255),
208
		mbid VARCHAR(36),
209
		expires INTEGER)',
elleo's avatar
elleo committed
210

211
		# TODO: Delete
212
		'CREATE TABLE Invitation_Request(
213
		email VARCHAR(255) PRIMARY KEY,
214
		time INTEGER)',
215

216
		'CREATE TABLE Invitations(
elleo's avatar
elleo committed
217 218 219
		inviter VARCHAR(64) REFERENCES Users(username),
		invitee VARCHAR(64) REFERENCES Users(username),
		code VARCHAR(32),
220
		PRIMARY KEY(inviter, invitee, code))',
elleo's avatar
elleo committed
221

222
		'CREATE TABLE ClientCodes(
223 224 225
		code CHAR(3),
		name VARCHAR(32),
		url VARCHAR(256),
226
		free CHAR(1),
227
		PRIMARY KEY(code))',
228

229
		'CREATE TABLE Tags(
230 231
		tag VARCHAR(64),
		artist VARCHAR(255) REFERENCES Artist(name),
232
		album VARCHAR(255),
233 234
		track VARCHAR(255),
		userid INTEGER REFERENCES Users(uniqueid),
235
		UNIQUE(tag, artist, album, track, userid))',
clint's avatar
clint committed
236

237
		'CREATE TABLE Manages(
238 239
		userid INTEGER REFERENCES Users(uniqueid),
		artist VARCHAR(255) REFERENCES Artist(name),
240
		authorised INTEGER)',
241

242
		'CREATE TABLE Error(
243 244 245
		id SERIAL PRIMARY KEY,
		msg TEXT,
		data TEXT,
246
		time INTEGER)',
247

248 249 250 251 252 253
		'CREATE TABLE Recovery_Request(
			username VARCHAR(64),
			email VARCHAR(255),
			code VARCHAR(32),
			expires INTEGER,
			PRIMARY KEY(username))',
clint's avatar
clint committed
254

255
		'CREATE TABLE Radio_Sessions(
clint's avatar
clint committed
256 257 258 259
			username VARCHAR(64),
			session VARCHAR(32),
			url VARCHAR(255),
			expires INTEGER NOT NULL DEFAULT 0,
260
			PRIMARY KEY(session))',
261 262

		//Table for delete profile requests
263
		'CREATE TABLE Delete_Request (
264 265 266
		code VARCHAR(300),
		expires INTEGER,
		username VARCHAR(64) REFERENCES Users(username),
267
		PRIMARY KEY(code))',
268

269
		'CREATE TABLE Scrobble_Track(
270 271 272 273 274
		id SERIAL PRIMARY KEY,
		artist VARCHAR(255) NOT NULL,
		album VARCHAR(255),
		name VARCHAR(255) NOT NULL,
		mbid VARCHAR(36),
275
		track INTEGER NOT NULL)',
276

277
		'CREATE VIEW Free_Scrobbles AS
278 279 280 281
		SELECT s.userid, s.track, s.artist, s.time, s.mbid, s.album, s.source, s.rating, s.length
		FROM Scrobbles s
		JOIN Scrobble_Track st ON s.stid = st.id
		JOIN Track t ON st.track = t.id
282
		WHERE t.streamable = 1',
283

284
		'CREATE TABLE Banned_Tracks (
285 286
		userid INTEGER REFERENCES Users(uniqueid) ON DELETE CASCADE,
		track varchar(255),
287 288
		artist varchar(255),
		time INTEGER,
289
		UNIQUE(userid, track, artist))',
290

291
		'CREATE TABLE Loved_Tracks (
292 293
		userid INTEGER REFERENCES Users(uniqueid) ON DELETE CASCADE,
		track varchar(255),
294 295
		artist varchar(255),
		time varchar(255),
296
		UNIQUE(userid, track, artist))',
297

298 299
		'CREATE TABLE Service_Connections (
		userid INTEGER REFERENCES Users(uniqueid) ON DELETE CASCADE,
300 301
		webservice_url VARCHAR(255),
		remote_key VARCHAR(255),
302
		remote_username VARCHAR(255),
303
		forward INTEGER DEFAULT 1)',
304

305
		'CREATE TABLE User_Relationships (
306 307
		uid1 INTEGER REFERENCES Users(uniqueid) ON DELETE CASCADE,
		uid2 INTEGER REFERENCES Users(uniqueid) ON DELETE CASCADE,
308
		established INTEGER NOT NULL,
309 310 311
		PRIMARY KEY (uid1, uid2))',

		'CREATE TABLE Relationship_Flags (
312
		flag VARCHAR(12),
313
		PRIMARY KEY (flag))',
314

315
		'CREATE TABLE User_Relationship_Flags (
316 317
		uid1 INTEGER,
		uid2 INTEGER,
318
		flag VARCHAR(12) REFERENCES Relationship_Flags(flag),
319
		PRIMARY KEY (uid1, uid2, flag),
320 321
		FOREIGN KEY (uid1, uid2) REFERENCES User_Relationships (uid1, uid2))',

322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337
		'INSERT INTO Relationship_Flags VALUES (\'contact\')',
		'INSERT INTO Relationship_Flags VALUES (\'acquaintance\')',
		'INSERT INTO Relationship_Flags VALUES (\'friend\')',
		'INSERT INTO Relationship_Flags VALUES (\'met\')',
		'INSERT INTO Relationship_Flags VALUES (\'co-worker\')',
		'INSERT INTO Relationship_Flags VALUES (\'colleague\')',
		'INSERT INTO Relationship_Flags VALUES (\'co-resident\')',
		'INSERT INTO Relationship_Flags VALUES (\'neighbor\')',
		'INSERT INTO Relationship_Flags VALUES (\'child\')',
		'INSERT INTO Relationship_Flags VALUES (\'parent\')',
		'INSERT INTO Relationship_Flags VALUES (\'sibling\')',
		'INSERT INTO Relationship_Flags VALUES (\'spouse\')',
		'INSERT INTO Relationship_Flags VALUES (\'kin\')',
		'INSERT INTO Relationship_Flags VALUES (\'muse\')',
		'INSERT INTO Relationship_Flags VALUES (\'crush\')',
		'INSERT INTO Relationship_Flags VALUES (\'date\')',
338 339 340 341 342
		'INSERT INTO Relationship_Flags VALUES (\'sweetheart\')',

		'CREATE TABLE User_Stats (
			userid INTEGER REFERENCES Users(uniqueid) ON DELETE CASCADE,
			scrobble_count INTEGER NOT NULL,
343 344 345 346
			PRIMARY KEY (userid))',

		'CREATE TABLE Domain_Blacklist (
			domain TEXT,
347
			expires INTEGER)'
348 349
	);

350
	foreach ($stage_one_queries as $query) {
351 352
		try {
			$adodb->Execute($query);
353 354
		} catch (Exception $e) {
			die('Database Error: ' . $adodb->ErrorMsg());
355 356 357
		}
	}

358 359
	if (strtolower(substr($dbms, 0, 5)) == 'mysql') {
		foreach ($stage_two_queries_mysql as $query) {
360 361
			try {
				$adodb->Execute($query);
362 363
			} catch (Exception $e) {
				die('Database Error: ' . $adodb->ErrorMsg());
364 365 366
			}
		}
	} else {
367
		foreach ($stage_two_queries_other as $query) {
368 369
			try {
				$adodb->Execute($query);
370 371
			} catch (Exception $e) {
				die('Database Error: ' . $adodb->ErrorMsg());
372
			}
373 374 375
		}
	}

376
	foreach ($stage_three_queries as $query) {
377 378
		try {
			$adodb->Execute($query);
379 380
		} catch (Exception $e) {
			die('Database Error: ' . $adodb->ErrorMsg());
381 382
		}
	}
383

384
	$adodb->Execute("CREATE INDEX scrobbles_time_idx ON Scrobbles(time)");
385
	$adodb->Execute("CREATE INDEX scrobbles_userid_time_idx ON Scrobbles(userid, time)");
386 387
	$adodb->Execute("CREATE INDEX scrobbles_track_idx on Scrobbles(track)");
	$adodb->Execute("CREATE INDEX scrobble_track_name_idx ON Scrobble_Track(name)");
388
	$adodb->Execute("CREATE INDEX track_streamable_idx on Track(streamable);");
389 390 391
	$adodb->Execute("CREATE INDEX track_name_idx ON Track(name)");
	$adodb->Execute("CREATE INDEX album_name_idx ON Album(name)");
	$adodb->Execute("CREATE INDEX artist_name_idx ON Artist(name)");
392

393
	if(strtolower(substr($dbms, 0, 5)) == 'pgsql') {
394
		// MySQL doesn't support the use of lower() to create case-insensitive indexes
395 396 397 398 399 400
		$adodb->Execute("CREATE INDEX album_lower_artistname_idx ON Album(lower(artist_name))");
		$adodb->Execute("CREATE INDEX track_lower_artist_idx ON Track(lower(artist_name))");
		$adodb->Execute("CREATE INDEX track_lower_name_idx ON Track(lower(name))");
		$adodb->Execute("CREATE INDEX scrobbles_lower_artist_idx on Scrobbles(lower(artist))");
		$adodb->Execute("CREATE INDEX scrobbles_lower_track_idx on Scrobbles(lower(track))");
		$adodb->Execute("CREATE INDEX groups_lower_groupname_idx ON Groups(lower(groupname))");
401 402

		// PostgreSQL stored functions
403
		$adodb->Execute("CREATE OR REPLACE LANGUAGE plpgsql;");
404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437
		$adodb->Execute("CREATE FUNCTION update_user_stats_scrobble_count() RETURNS TRIGGER AS $$
			DECLARE s_count int;
			BEGIN
				UPDATE User_Stats SET scrobble_count = scrobble_count + 1 WHERE userid = NEW.userid;
				IF found THEN
					RETURN NULL;
				END IF;
				BEGIN
					-- userid not in User_Stats table, get current scrobble count from Scrobbles
					-- and insert userid into User_Stats
					SELECT COUNT(userid) into s_count FROM Scrobbles WHERE userid = NEW.userid;
					INSERT INTO User_Stats(userid, scrobble_count) VALUES(NEW.userid, s_count);
					RETURN NULL;
				END;
			END;
			$$ LANGUAGE plpgsql;");
		$adodb->Execute("CREATE TRIGGER update_user_stats_scrobble_count
			AFTER INSERT ON Scrobbles
			FOR EACH ROW EXECUTE PROCEDURE update_user_stats_scrobble_count();");

	} elseif (substr($dbms, 0, 5) == 'mysql') {
		$adodb->Execute("CREATE PROCEDURE update_user_stats_scrobble_count(uid INT)
			main: BEGIN
				DECLARE s_count INT;
				UPDATE User_Stats SET scrobble_count = (scrobble_count + 1) WHERE userid = uid;
				IF ROW_COUNT() > 0 THEN
					LEAVE main;
				END IF;
				SELECT COUNT(userid) INTO s_count FROM Scrobbles WHERE userid = uid;
				INSERT INTO User_Stats(userid, scrobble_count) VALUES(uid, s_count);
			END main;");
		$adodb->Execute("CREATE TRIGGER update_user_stats_scrobble_count
			AFTER INSERT ON Scrobbles
			FOR EACH ROW CALL update_user_stats_scrobble_count(NEW.userid);");
438
	}
439

440
	$adodb->Close();
441

442
	$submissions_server = $_POST['submissions'];
443
	$install_path = dirname(__FILE__) . '/';
444

445
	//Write out the configuration
446
	$config = "<?php\n \$config_version = " . $version .";\n \$connect_string = '" . $connect_string . "';\n \$submissions_server = '" . $submissions_server . "';\n \$install_path = '" . $install_path . "';\n \$adodb_connect_string = '" . $adodb_connect_string . "';\n\n require_once(__DIR__ . '/vendor/autoload.php');\n";
447

448
	$conf_file = fopen('config.php', 'w');
449 450 451
	$result = fwrite($conf_file, $config);
	fclose($conf_file);

452 453 454
	if (!$result) {
		$print_config = str_replace('<', '&lt;', $config);
		die('Unable to write to file \'<i>config.php</i>\'. Please create this file and copy the following in to it: <br /><pre>' . $print_config . '</pre>');
455
	}
456

457
	die('Configuration completed successfully!');
458 459 460 461 462
}

?>
<html>
	<head>
mattl's avatar
mattl committed
463
		<title>GNUkebox Installer</title>
464 465 466 467 468 469 470 471 472 473 474 475 476 477
		<script type='text/javascript'>
			function showSqlite() {
				document.getElementById("sqlite").style.visibility = "visible";
				document.getElementById("networkdbms").style.visibility = "hidden";
			}

			function showNetworkDBMS() {
				document.getElementById("sqlite").style.visibility = "hidden";
				document.getElementById("networkdbms").style.visibility = "visible";
			}
		</script>
	</head>

	<body onload="showSqlite()">
mattl's avatar
mattl committed
478
		<h1>GNUkebox Installer</h1>
479
		<form method="post">
480
			<h2>Database</h2>
481
			Database Management System: <br />
482
			<input type="radio" name="dbms" value="sqlite" onclick='showSqlite()' checked>SQLite (use an absolute path)</input><br />
483 484 485 486 487 488 489 490 491 492 493 494 495 496
			<input type="radio" name="dbms" value="mysql" onclick='showNetworkDBMS()'>MySQL</input><br />
			<input type="radio" name="dbms" value="pgsql" onclick='showNetworkDBMS()'>PostgreSQL</input><br />
			<br />
			<div id="sqlite">
				Filename: <input type="text" name="filename" /><br />
			</div>
			<div id="networkdbms">
				Hostname: <input type="text" name="hostname" /><br />
				Port: <input type="text" name="port" /><br />
				Database: <input type="text" name="dbname" /><br />
				Username: <input type="text" name="username" /><br />
				Password: <input type="password" name="password" /><br />
			</div>
			<br />
497
			<h2>Servers</h2>
498
			Submissions Server URL: <input type="text" name="submissions" value="<?php echo getAbsoluteURL(); ?>" /><br />
499
			<br />
500 501
			<input type="submit" value="Install" name="install" />
		</form>
502 503
		<br />
		<div align="center"><a href="http://docs.jurg.no/gnufm_install.txt">Help</a></div>
504 505 506 507
	</body>
</html>