We are no longer offering accounts on this server. Consider https://gitlab.freedesktop.org/ as a place to host projects.

install.php 13.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 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/>.

*/

clint's avatar
clint committed
22 23
require_once('adodb/adodb-exceptions.inc.php');
require_once('adodb/adodb.inc.php');
24
require_once('version.php');
25
require_once('utils/get_absolute_url.php');
26 27

if(file_exists("config.php")) {
clint's avatar
clint committed
28
	die("A configuration file already exists. Please delete <i>config.php</i> if you wish to reinstall.");
29 30 31 32 33 34 35 36
}

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

	//Get the database connection string
	$dbms = $_POST['dbms'];
	if($dbms == "sqlite") {
		$filename = $_POST['filename'];
37
		$connect_string = "sqlite:///" . $filename;
38 39 40
	} else {
		$connect_string = $dbms . "://" . $_POST['username'] . ":" . $_POST['password'] . "@" . $_POST['hostname'] . ":" . $_POST['port'] . "/" . $_POST['dbname'];
	}
41 42
	
	$adodb_connect_string = str_replace('pgsql:', 'postgres:', $connect_string );
43

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

	//Create tables
53

clint's avatar
clint committed
54
	$adodb->Execute("CREATE TABLE Places(
jurgbohn's avatar
jurgbohn committed
55
		location_uri VARCHAR(255) unique,
56 57
		latitude FLOAT,
		longitude FLOAT,
clint's avatar
clint committed
58
		country CHAR(2))");
59

clint's avatar
clint committed
60
	$adodb->Execute("CREATE TABLE Countries (
tobyink's avatar
tobyink committed
61 62
		country varchar(2) PRIMARY KEY,
		country_name varchar(200),
elleo's avatar
elleo committed
63
		wikipedia_en varchar(120));");
tobyink's avatar
tobyink committed
64

clint's avatar
clint committed
65
	$adodb->Execute("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,
tobyink's avatar
tobyink committed
84
		journal_rss VARCHAR(255))");
85

clint's avatar
clint committed
86
	$adodb->Execute("CREATE TABLE Groups (
87 88
		id SERIAL PRIMARY KEY,
		groupname VARCHAR(64),
clint's avatar
clint committed
89
		owner INTEGER REFERENCES Users(uniqueid),
tobyink's avatar
tobyink committed
90 91 92
		fullname VARCHAR(255),
		bio TEXT,
		homepage VARCHAR(255),
elleo's avatar
elleo committed
93
		created INTEGER NOT NULL,
tobyink's avatar
tobyink committed
94
		modified INTEGER,
tobyink's avatar
tobyink committed
95
		avatar_uri VARCHAR(255),
tobyink's avatar
tobyink committed
96 97
		grouptype INTEGER)");

clint's avatar
clint committed
98
	$adodb->Execute("CREATE TABLE Group_Members (
99
		grp INTEGER REFERENCES Groups(id),
clint's avatar
clint committed
100
		member INTEGER REFERENCES Users(uniqueid),
101
		joined INTEGER NOT NULL,
clint's avatar
clint committed
102
		PRIMARY KEY (grp, member))");
tobyink's avatar
tobyink committed
103

clint's avatar
clint committed
104
	$adodb->Execute("CREATE TABLE AccountActivation(
jurgbohn's avatar
jurgbohn committed
105
		username VARCHAR(64),
106 107
		authcode VARCHAR(32),
		expires INTEGER)");
jurgbohn's avatar
jurgbohn committed
108

clint's avatar
clint committed
109
	$adodb->Execute("CREATE TABLE Auth (
110
		token VARCHAR(32) PRIMARY KEY,
elleo's avatar
elleo committed
111
		sk VARCHAR(32),
clint's avatar
clint committed
112
		expires INTEGER,
elleo's avatar
elleo committed
113
		username VARCHAR(64) REFERENCES Users(username))");
114

clint's avatar
clint committed
115
	$adodb->Execute("CREATE TABLE Artist(
116
		id SERIAL PRIMARY KEY,
117
		name VARCHAR(255) unique,
118
		mbid VARCHAR(36),
clint's avatar
clint committed
119 120
		streamable INTEGER,
		bio_published INTEGER,
121 122 123 124
		bio_content TEXT,
		bio_summary TEXT,
		image_small VARCHAR(255),
		image_medium VARCHAR(255),
jurgbohn's avatar
jurgbohn committed
125
		image_large VARCHAR(255),
tobyink's avatar
tobyink committed
126 127
		homepage VARCHAR(255),
		origin VARCHAR(255) REFERENCES Places(location_uri))");
128

clint's avatar
clint committed
129
	$adodb->Execute("CREATE TABLE Album(
130
		id SERIAL PRIMARY KEY,
131
		name VARCHAR(255),
132 133
		artist_name VARCHAR(255) REFERENCES Artist(name),
		mbid VARCHAR(36),
134 135
		image VARCHAR(255),
		artwork_license VARCHAR(255),
clint's avatar
clint committed
136
		releasedate INTEGER,
137
		albumurl VARCHAR(255),
clint's avatar
clint committed
138
		downloadurl VARCHAR(255))");
139 140

	// Table for registering similar artists
clint's avatar
clint committed
141
	$adodb->Execute("CREATE TABLE Similar_Artist(
142 143
		name_a VARCHAR(255) REFERENCES Artist(name),
		name_b VARCHAR(255) REFERENCES Artist(name),
clint's avatar
clint committed
144
		PRIMARY KEY(name_a, name_b))");
145

clint's avatar
clint committed
146 147
	if ( strtolower(substr($dbms,0,5)) == 'mysql'  ) {
		$adodb->Execute("CREATE TABLE Track(
tobyink's avatar
tobyink committed
148
			id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
tobyink's avatar
tobyink committed
149
			name VARCHAR(255),
150 151
			artist_name VARCHAR(255) REFERENCES Artist(name),
			album_name VARCHAR(255),
tobyink's avatar
tobyink committed
152 153
			mbid VARCHAR(36),
			duration INTEGER,
154
			streamable INTEGER DEFAULT 0,
tobyink's avatar
tobyink committed
155 156 157 158 159
			license VARCHAR(255),
			downloadurl VARCHAR(255),
			streamurl VARCHAR(255),
			otherid VARCHAR(16))");
	} else {
clint's avatar
clint committed
160 161
		$adodb->Execute("CREATE SEQUENCE track_id_seq;");
		$adodb->Execute("CREATE TABLE Track(
tobyink's avatar
tobyink committed
162 163
			id INTEGER NOT NULL DEFAULT nextval('track_id_seq'::regclass) PRIMARY KEY,
			name VARCHAR(255),
164 165
			artist_name VARCHAR(255) REFERENCES Artist(name),
			album_name VARCHAR(255),
tobyink's avatar
tobyink committed
166 167
			mbid VARCHAR(36),
			duration INTEGER,
168
			streamable INTEGER DEFAULT 0,
tobyink's avatar
tobyink committed
169 170 171 172 173
			license VARCHAR(255),
			downloadurl VARCHAR(255),
			streamurl VARCHAR(255),
			otherid VARCHAR(16))");
	}
clint's avatar
clint committed
174

clint's avatar
clint committed
175
	$adodb->Execute("CREATE TABLE Scrobbles(
176
		userid INTEGER REFERENCES Users(uniqueid),
177 178
		track VARCHAR(255),
		album VARCHAR(255),
elleo's avatar
elleo committed
179
		artist VARCHAR(255) REFERENCES Artist(name),
clint's avatar
clint committed
180
		time INTEGER,
181
		mbid VARCHAR(36),
clint's avatar
clint committed
182 183
		source VARCHAR(6),
		rating CHAR(1),
clint's avatar
clint committed
184 185
		length INTEGER,
		stid INTEGER)");
elleo's avatar
elleo committed
186

clint's avatar
clint committed
187
	$adodb->Execute("CREATE TABLE Scrobble_Sessions(
188
		userid INTEGER REFERENCES Users(uniqueid),
clint's avatar
clint committed
189
		sessionid VARCHAR(32) PRIMARY KEY,
clint's avatar
clint committed
190
		client CHAR(3),
clint's avatar
clint committed
191
		expires INTEGER)");
elleo's avatar
elleo committed
192

clint's avatar
clint committed
193
	$adodb->Execute("CREATE TABLE Now_Playing(
194
		sessionid VARCHAR(32) PRIMARY KEY REFERENCES Scrobble_Sessions(sessionid) ON DELETE CASCADE,
195
		track VARCHAR(255),
196
		artist VARCHAR(255),
197
		album VARCHAR(255),
198
		mbid VARCHAR(36),
clint's avatar
clint committed
199
		expires INTEGER)");
elleo's avatar
elleo committed
200

clint's avatar
clint committed
201
	$adodb->Execute("CREATE TABLE Invitation_Request(
202
		email VARCHAR(255) PRIMARY KEY,
clint's avatar
clint committed
203
		time INTEGER)");
204

clint's avatar
clint committed
205
	$adodb->Execute("CREATE TABLE Invitations(
elleo's avatar
elleo committed
206 207 208
		inviter VARCHAR(64) REFERENCES Users(username),
		invitee VARCHAR(64) REFERENCES Users(username),
		code VARCHAR(32),
clint's avatar
clint committed
209
		PRIMARY KEY(inviter, invitee, code))");
elleo's avatar
elleo committed
210

clint's avatar
clint committed
211
	$adodb->Execute("CREATE TABLE ClientCodes(
212 213 214
		code CHAR(3),
		name VARCHAR(32),
		url VARCHAR(256),
clint's avatar
clint committed
215
		free CHAR(1),
clint's avatar
clint committed
216
		PRIMARY KEY(code))");
217

clint's avatar
clint committed
218
	$adodb->Execute("CREATE TABLE Tags(
219
		userid INTEGER REFERENCES Users(uniqueid),
220 221
		tag VARCHAR(64),
		artist VARCHAR(255) REFERENCES Artist(name),
222
		album VARCHAR(255),
clint's avatar
clint committed
223
		track VARCHAR(255))");
clint's avatar
clint committed
224

clint's avatar
clint committed
225
	$adodb->Execute("CREATE TABLE Error(
226 227
			id SERIAL PRIMARY KEY,
			msg TEXT,
elleo's avatar
elleo committed
228 229
		    data TEXT,
		    time INTEGER)");
clint's avatar
clint committed
230
	$adodb->Execute("CREATE TABLE Recovery_Request(
231 232 233
		    username VARCHAR(64),
		    email VARCHAR(255),
		    code VARCHAR(32),
clint's avatar
clint committed
234
		    expires INTEGER,
clint's avatar
clint committed
235 236
		    PRIMARY KEY(username))");

clint's avatar
clint committed
237
	$adodb->Execute("CREATE TABLE Radio_Sessions(
clint's avatar
clint committed
238 239 240 241
			username VARCHAR(64),
			session VARCHAR(32),
			url VARCHAR(255),
			expires INTEGER NOT NULL DEFAULT 0,
242
			PRIMARY KEY(session))");
clint's avatar
clint committed
243 244

	//Table for delete profile requests
clint's avatar
clint committed
245
	$adodb->Execute("CREATE TABLE Delete_Request (
clint's avatar
clint committed
246 247
			code VARCHAR(300),
			expires INTEGER,
clint's avatar
clint committed
248
			username VARCHAR(64) REFERENCES Users(username),
idflood's avatar
idflood committed
249
			PRIMARY KEY(code))");
250

clint's avatar
clint committed
251
	$adodb->Execute("CREATE TABLE Scrobble_Track(
clint's avatar
clint committed
252 253 254 255 256 257 258
			id SERIAL PRIMARY KEY,
			artist VARCHAR(255) NOT NULL,
			album VARCHAR(255),
			name VARCHAR(255) NOT NULL,
			mbid VARCHAR(36),
			track INTEGER NOT NULL)");

clint's avatar
clint committed
259
	$adodb->Execute("CREATE VIEW Free_Scrobbles AS
Jorgen Bohnsdalen's avatar
Jorgen Bohnsdalen committed
260
			SELECT s.userid, s.track, s.artist, s.time, s.mbid, s.album, s.source, s.rating, s.length
clint's avatar
clint committed
261 262 263 264
				FROM Scrobbles s
				JOIN Scrobble_Track st ON s.stid = st.id
				JOIN Track t ON st.track = t.id
				WHERE t.streamable = 1");
265

266 267 268 269 270 271
	$adodb->Execute("CREATE TABLE Banned_Tracks (
		userid INTEGER REFERENCES Users(uniqueid) ON DELETE CASCADE,
		track varchar(255),
		artist varchar(255), 
		UNIQUE(userid, track, artist))");

272 273 274 275 276 277
	$adodb->Execute("CREATE TABLE Loved_Tracks (
		userid INTEGER REFERENCES Users(uniqueid) ON DELETE CASCADE,
		track varchar(255),
		artist varchar(255), 
		UNIQUE(userid, track, artist))");

clint's avatar
clint committed
278
	$adodb->Execute("CREATE TABLE User_Relationships (
279 280
		uid1 INTEGER REFERENCES Users(uniqueid) ON DELETE CASCADE,
		uid2 INTEGER REFERENCES Users(uniqueid) ON DELETE CASCADE,
281
		established INTEGER NOT NULL,
282
		PRIMARY KEY (uid1, uid2))");
283 284 285 286
	
	$adodb->Execute("CREATE TABLE Relationship_Flags (
		flag VARCHAR(12),
		PRIMARY KEY (flag))");
287

clint's avatar
clint committed
288
	$adodb->Execute("CREATE TABLE User_Relationship_Flags (
289 290
		uid1 INTEGER,
		uid2 INTEGER,
291
		flag VARCHAR(12) REFERENCES Relationship_Flags(flag),
292 293
		PRIMARY KEY (uid1, uid2, flag),
		FOREIGN KEY (uid1, uid2) REFERENCES User_Relationships (uid1, uid2))");
294 295
	
	
clint's avatar
clint committed
296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('contact')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('acquaintance')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('friend')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('met')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('co-worker')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('colleague')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('co-resident')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('neighbor')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('child')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('parent')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('sibling')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('spouse')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('kin')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('muse')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('crush')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('date')");
	$adodb->Execute("INSERT INTO Relationship_Flags VALUES ('sweetheart')");
313

clint's avatar
clint committed
314
// uncomment these to solve performance problems with getRecentScrobbles
clint's avatar
clint committed
315 316 317
// 	$adodb->Execute("CREATE INDEX album_artistname_idx ON Album(artist_name)");
// 	$adodb->Execute("CREATE INDEX scrobbles_artist_idx ON Scrobbles(artist)");
//	$adodb->Execute("CREATE INDEX scrobbles_time_idx ON Scrobbles(time)");
318
//      $adodb->Execute("CREATE INDEX track_artist_idx ON Track(lower(artist_name))");
clint's avatar
clint committed
319 320 321 322
//      $adodb->Execute("CREATE INDEX track_name_idx ON Track(lower(name))");
//      $adodb->Execute("CREATE INDEX track_streamable_idx on Track(streamable);");
//      $adodb->Execute("CREATE INDEX scrobbles_artist_idx on Scrobbles(lower(artist))");
//      $adodb->Execute("CREATE INDEX scrobbles_track_idx on Scrobbles(lower(track))");
323
//      $adodb->Execute("CREATE UNIQE INDEX groups_groupname_idx ON Groups(lower(groupname))");
clint's avatar
clint committed
324

clint's avatar
clint committed
325
// uncomment these if you're using postgresql and want to run the software as www-data
clint's avatar
clint committed
326 327
//	$adodb->Execute("GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE Album, Artist, Auth, Clientcodes, Delete_Request Error, Invitation_Request, Invitations, Now_Playing, Places, Radio_Sessions, Scrobble_Sessions, Scrobbles, Scrobble_Track, Similar_Artist, Tags, Track, Users, User_Relationships, User_Relationship_Flags to \"www-data\"");
//	$adodb->Execute("GRANT SELECT ON Free_Scrobbles, Relationship_Flags to \"www-data\"");
clint's avatar
clint committed
328
//	$adodb->Execute("GRANT SELECT, UPDATE ON users_uniqueid_seq, scrobble_track_id_seq, groups_id_seq, artist_id_seq, album_id_seq to \"www-data\"");
clint's avatar
clint committed
329

330
	// Test user configuration
clint's avatar
clint committed
331
	$adodb->Execute("INSERT INTO Users
Jorgen Bohnsdalen's avatar
Jorgen Bohnsdalen committed
332
		(username, password)
333
		VALUES
Jorgen Bohnsdalen's avatar
Jorgen Bohnsdalen committed
334
		('testuser', '" . md5('password') . "');");
335

clint's avatar
clint committed
336
	$adodb->Close();
337

338
	$submissions_server = $_POST['submissions'];
339
	$install_path = dirname(__FILE__) . "/";
340

341
	//Write out the configuration
342
	$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 . "'; ";
343 344 345 346 347 348

	$conf_file = fopen("config.php", "w");
	$result = fwrite($conf_file, $config);
	fclose($conf_file);

	if(!$result) {
349
		$print_config = str_replace("<", "&lt;", $config);
clint's avatar
clint committed
350
		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>");
351
	}
352

clint's avatar
clint committed
353
	die("Configuration completed successfully!");
354 355 356 357 358
}

?>
<html>
	<head>
mattl's avatar
mattl committed
359
		<title>GNUkebox Installer</title>
360 361 362 363 364 365 366 367 368 369 370 371 372 373
		<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
374
		<h1>GNUkebox Installer</h1>
375
		<form method="post">
376
			<h2>Database</h2>
377
			Database Management System: <br />
378
			<input type="radio" name="dbms" value="sqlite" onclick='showSqlite()' checked>SQLite (use an absolute path)</input><br />
379 380 381 382 383 384 385 386 387 388 389 390 391 392
			<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 />
393
			<h2>Servers</h2>
394
			Submissions Server URL: <input type="text" name="submissions" value="<?php echo getAbsoluteURL(); ?>" /><br />
395
			<br />
396 397 398 399 400 401
			<input type="submit" value="Install" name="install" />
		</form>
	</body>
</html>