Commits

Mathias Panzenböck committed 855c419

WIP: port away from SQLite because it's no longer supported by notejitsu

missing: search

Comments (0)

Files changed (2)

 var mime    = require('express/node_modules/mime');
 var async   = require('async');
 var cron    = require('cron');
+var unorm   = require('unorm');
+
+console._time = console.time;
+console.time = function (msg) {
+	this.log("%s...", msg);
+	this._time(msg);
+};
 
 if (!fs.exists) fs.exists = path.exists;
 
 	return ys;
 }
 
+function collectSlices (xs) {
+	var ys = new Array(xs.length);
+	for (var i = 0; i < ys.length; ++ i) {
+		var x = xs[i];
+		var y = ys[i] = {};
+
+		for (var j = 1; j < arguments.length; ++ j) {
+			var key = arguments[j];
+			y[key] = x[key];
+		}
+	}
+	return ys;
+}
+
+function slice (obj) {
+	var sliced = {};
+	for (var i = 1; i < arguments.length; ++ i) {
+		var key = arguments[i];
+		sliced[key] = obj[key];
+	}
+	return sliced;
+}
+
 function sqlEscape (s) {
 	return "'"+s.replace(/'/g,"''")+"'";
 }
 	});
 }
 
-function exec (qs, args, cb) {
-	if (arguments.length < 3) {
-		cb = args;
-		args = [];
-	}
-	else if (!args) {
-		args = [];
-	}
+function fulltextSearch (records, words) {
+	return records.filter(function (record) {
+		var vector = record.vector;
 
-	return db.exec(buildQS(qs,args), cb);
-}
+		for (var i = 0; i < words.length; ++ i) {
+			if (vector.indexOf(words[i]) < 0) {
+				return false;
+			}
+		}
 
-function fetch (qs, args, cb) {
-	if (arguments.length < 3) {
-		cb = args;
-		args = [];
-	}
-	else if (!args) {
-		args = [];
-	}
-
-	return db.exec(buildQS(qs,args), function (err, results) {
-		if (err) return cb(err);
-		return cb(null,results[0]);
+		return true;
 	});
 }
 
 	});
 });
 
-var db;
+var collection = null;
 var updateRunning = false;
 var currentHash = null;
 var currentDate = null;
 
 var ACTIONS = {
 	'index': function (req, res, done) {
-		async.parallel({
-			artists: function (done) {
-				exec('select artist, albumname, launchdate from albums',
-				function (err, rows) {
-					if (err) return done(err);
-					var artists = {};
-					for (var i = 0; i < rows.length; ++ i) {
-						var album = rows[i];
-						var albums = album.artist in artists ?
-							artists[album.artist] :
-							(artists[album.artist] = {});
-						albums[album.albumname] = album.launchdate;
-					}
-					done(null, artists);
-				});
-			},
+		var artists = {};
+		var genres  = {};
 
-			genres: function (done) {
-				exec('select genre, albumname from genres',
-				function (err, rows) {
-					if (err) return done(err);
-					var genres = {};
-					for (var i = 0; i < rows.length; ++ i) {
-						var album = rows[i];
-						var albums = album.genre in genres ?
-							genres[album.genre] :
-							(genres[album.genre] = []);
-						albums.push(album.albumname);
-					}
-					done(null,genres);
-				});
+		var cArtists = collection.SortedArtists;
+		for (var i = 0; i < cArtists.length; ++ i) {
+			var artist  = cArtists[i];
+			var albums  = artists[artist.artist] = {};
+			var aAlbums = artist.albums;
+
+			for (var j = 0; j < aAlbums.length; ++ j) {
+				var album = aAlbums[j];
+				albums[album.albumname] = album.launchdate;
 			}
-		}, done);
+		}
+
+		var cGenres = collection.SortedGenres;
+		for (var i = 0; i < cGenres.length; ++ i) {
+			var genre   = cGenres[i];
+			var gAlbums = genre.albums;
+			var albums  = genres[genre.genre] = new Array(gAlbums.length);
+			
+			for (var j = 0; j < gAlbums.length; ++ j) {
+				albums[j] = gAlbums[j].albumname;
+			}
+		}
+
+		done(null, {artists: artists, genres: genres});
 	},
 	'album': function (req, res, done) {
 		var albumname = req.param('name');
-		var sql = 'select albumname, artist, also, description, sku, '+
-			'launchdate, itunes from albums where ';
-		var args;
+		var sku = req.param('sku');
+		var cAlbum = albumname === undefined ?
+			sku === undefined ? null :
+			collection.getAlbumBySku(sku) :
+			collection.getAlbum(albumname);
+		var album;
 
-		if (albumname === undefined) {
-			sql += 'sku = ? limit 1';
-			args = [req.param('sku')];
+		if (cAlbum) {
+			album = slice(cAlbum, 'albumname', 'description', 'sku', 'launchdate', 'itunes');
+			album.artist = cAlbum.artist.artist;
+			album.also   = collect(cAlbum.relatedAlbums,'albumname');
+			album.songs  = collectSlices(cAlbum.songs,'number','desc','duration','mp3');
 		}
 		else {
-			sql += 'albumname = ? limit 1';
-			args = [albumname];
+			album = null;
 		}
 
-		fetch(sql, args,
-			function (err, album) {
-				if (err) return done(err);
-				if (!album) return done(null,null);
-				var also = album.also;
-				if (also) {
-					also = also.trim().split(/\s+/);
-					exec('select albumname from albums where sku in ('+nargs(also)+')',also,
-					function (err,rows) {
-						if (err) return done(err);
-						album.also = collect(rows,'albumname');
-						songs();
-					});
-				}
-				else {
-					album.also = [];
-					songs();
-				}
-
-				function songs () {
-					exec('select number, desc, duration, mp3 from songs where albumname = ? order by number',
-						[album.albumname],
-						function (err, rows) {
-							if (err) return done(err);
-							album.songs = rows;
-							done(null,album);
-						});
-				}
-			});
+		done(null,album);
 	},
 	'song': function (req, res, done) {
 		var mp3 = req.param('mp3');
-		var sql = 'select songs.albumname as albumname, number, desc, duration, mp3 from songs ';
-		var args;
+		var cSong = null;
 
 		if (mp3 === undefined) {
 			var albumname = req.param('albumname');
-			if (albumname === undefined) {
-				sql += ' inner join albums on albums.albumname = songs.albumname where sku = ? and number = ? limit 1';
-				args = [req.param('sku'),parseInt(req.param('number'),10)];
+			var sku = req.param('sku');
+			var number = parseInt(req.param('number'),10);
+
+			if (!isNaN(number)) {
+				var album = albumname !== undefined ?
+					album = collection.getAlbum(albumname) :
+					sku !== undefined ?
+					album = collection.getAlbumBySku(sku) :
+					null;
+
+				if (album) {
+					cSong = album.songs[number]||null;
+				}
 			}
-			else {
-				sql += ' where albumname = ? and number = ? limit 1';
-				args = [albumname,parseInt(req.param('number'),10)];
-			}
+			
 		}
 		else {
-			sql += ' where mp3 = ? limit 1';
-			args = [mp3];
+			cSong = collection.getSong(mp3);
 		}
 
-		fetch(sql, args,
-			function (err, song) {
-				done(err,song||null);
-			});
+		done(null,cSong);
 	},
 	'artist': function (req, res, done) {
-		fetch(
-			'select artist, description, homepage, city, state, '+
-			'country, bio, bandphoto from artists where artist = ? limit 1',
-			[req.param('name')],
-			function (err, artist) {
-				if (err) return done(err);
-				done(null,artist||null);
-			});
+		var name = req.param('name');
+		var artist = name === undefined ? null : collection.getArtist(name);
+
+		if (artist) {
+			done(null,slice(artist,
+				'artist', 'description', 'homepage', 'city', 'state',
+				'country', 'bio', 'bandphoto'));
+		}
+		else {
+			done(null,null);
+		}
 	},
 	'search': function (req, res, done) {
 		var query = req.param('q','');
 		if (order !== 'name' && order !== 'date') {
 			return done("Illegal order value: "+order);
 		}
-		query = uniq(query.trim().split(/\s+/).filter(function (word) { return word.length > 1; }));
+		query = uniq(unorm.nfkc(query).toLowerCase().split(/\s+/).filter(function (word) { return word.length > 1; }));
 		if (query.length === 0) {
 			return done(null,null);
 		}
 		return FINDER[mode](req, res, query, order, done);
 	},
 	'countries': function (req, res, done) {
-		exec('select country, count(artist) as artists from artists group by country',
-		function (err, rows) {
-			if (err) return done(err);
-			var countries = {};
-			for (var i = 0; i < rows.length; ++ i) {
-				var country = rows[i];
-				countries[country.country] = country.artists;
-			}
-			done(null, countries);
-		});
+		var cCountries = collection.SortedCountries;
+		var countries = {};
+		for (var i = 0; i < cCountries.length; ++ i) {
+			var country = cCountries[i];
+			countries[country.country] = country.artists.length;
+		}
+		done(null, countries);
 	},
 	'country': function (req, res, done) {
-		var country = req.param('name');
-		exec('select artist, description from artists where country = ?', country,
-		function (err, rows) {
-			if (err) return done(err);
-			var artists = {};
-			for (var i = 0; i < rows.length; ++ i) {
-				var artist = rows[i];
+		var name = req.param('name');
+		var country = name === undefined ? null : collection.getCountry(name);
+		var artists = {};
+
+		if (country) {
+			var cArtists = country.artists;
+			for (var i = 0; i < cArtists.length; ++ i) {
+				var artist = cArtists[i];
 				artists[artist.artist] = artist.description;
 			}
-			done(null, artists);
-		});
+		}
+
+		done(null, artists);
 	}
 };
 
+// TODO
 var FINDER = {
 	'artists': function (req, res, query, order, done) {
 		var q = buildQuery(['artists.artist','artists.bio'],query);
 
 function action (handler) {
 	return function (req, res) {
-		if (!db) {
-			return send(updateRunning ?
-				"Database update active. Please try again later." :
-				"Database is down.");
+		if (!collection) {
+			return send("Database is down.");
 		}
 
 		var fmt = req.param('format','json').trim().toLowerCase();
 	if (updateRunning) {
 		return end(new Error('There is already an update running.'));
 	}
-	
+
 	updateRunning = true;
 	log('Checking for updates...');
-	
+
 	var changed = "";
 	fs.readFile('db/changed.txt', "utf8", function (err, data) {
 		if (!err) {
 						else {
 							log('Creating directory "db"...');
 							fs.mkdir("db", function (err) {
-								if (err) return onerror(err);
+								if (err) return end(err);
 								download();
 							});
 						}
 					log('Already up to date.');
 					end();
 				}
-			}).on('error', onerror);
-		}).on('error', onerror);
+			}).on('error', end);
+		}).on('error', end);
 	});
 	
 	function download () {
 						log('Downloading: 100%');
 					}
 
-					if (db) {
-						db.close(function () {
-							db = null;
-							unzip();
-						});
-					}
-					else {
-						unzip();
-					}
+					unzip();
 				}).on("error", transfererror);
 			}).on("error", transfererror);
-		}).on('error', onerror);
+		}).on('error', end);
 
 		function transfererror (err) {
 			ostream.end();
-			onerror(err);
+			end(err);
 		}
 	}
 
 		var ostream = fs.createWriteStream(DB_FILE);
 		ostream.on("open", function () {
 			fs.createReadStream(DB_FILE+".gz").pipe(zlib.createGunzip()).
-			on("error", onerror).
+			on("error", end).
 			on("end", function () {
 				currentHash = changed;
 				fs.writeFile('db/changed.txt', changed+" "+new Date().toUTCString()+"\n", "utf8", function (err) {
-					if (err) return onerror(err);
-					mkindices();
+					if (err) return end(err);
+
+					sqlite3.open(DB_FILE, {}, function (err, db) {
+						if (err) return end(err);
+
+						new Collection().load(db, function (err, coll) {
+							if (err) return end(err);
+
+							db.close(function (err) {
+								if (err) console.error(String(err));
+								collection = coll;
+								updateRunning = false;
+								end();
+							});
+						});
+					});
 				});
 			}).pipe(ostream);
 		});
 	}
 
-	function mkindices () {
-		function mkindex(table,columns,callback) {
-			var sql = "create index "+table+"_"+columns.join("_")+" on "+table+" ("+columns.join(", ")+")";
-			log(sql);
-			db.exec(sql, callback);
-		}
-
-		log("Create Indices...");
-		sqlite3.open(DB_FILE, {}, function (err, _db) {
-			if (err) {
-				db = null;
-				return onerror(err);
-			}
-			else {
-				db = _db;
-			}
-
-			sync([
-				['albums',  [['artist'],['albumname'],['sku']]],
-				['artists', [['artist'],['country']]],
-				['genres',  [['albumname'],['genre']]],
-				['songs',   [['albumname'],['number']]],
-				['songs',   [['mp3']]]],
-				function (item, next) {
-					var table = item[0];
-					var indices = item[1];
-					sync(indices, function (index, next) {
-						mkindex(table,index,next);
-					}, next);
-				},
-				function () {
-					log("Done.");
-					end();
-				});
-		});
-	}
-
-	function onerror (err) {
-		updateRunning = false;
-		if (!db) {
-			fs.exists(DB_FILE, function (exists) {
-				if (exists) {
-					sqlite3.open(DB_FILE, {}, function (dberr, _db) {
-						if (dberr) {
-							db = null;
-							console.error("Update DB: error opening db:", String(dberr));
-						}
-						else {
-							db = _db;
-						}
-						end(err);
-					});
-				}
-				else {
-					end(err);
-				}
-			});
-		}
-		else {
-			end(err);
-		}
-	}
-
 	function end (err) {
 		if (err) console.error("Update DB:",String(err));
 		updateRunning = false;
 	}
 }
 
+function Collection () {}
+
+Collection.prototype = {
+	hasGenre: function (genre) {
+		return Object.prototype.hasOwnProperty.call(this.Genres,genre);
+	},
+	hasAlbum: function (albumname) {
+		return Object.prototype.hasOwnProperty.call(this.Albums,albumname);
+	},
+	hasAlbumBySku: function (sku) {
+		return Object.prototype.hasOwnProperty.call(this.AlbumsBySku,sku);
+	},
+	hasArtist: function (artist) {
+		return Object.prototype.hasOwnProperty.call(this.Artists,artist);
+	},
+	hasSong: function (mp3) {
+		return Object.prototype.hasOwnProperty.call(this.Songs,mp3);
+	},
+	hasCountry: function (country) {
+		return Object.prototype.hasOwnProperty.call(this.Countries,country);
+	},
+	getGenre: function (genre) {
+		return this.hasGenre(genre) ? this.Genres[genre] : null;
+	},
+	getAlbum: function (albumname) {
+		return this.hasAlbum(albumname) ? this.Albums[albumname] : null;
+	},
+	getAlbumBySku: function (sku) {
+		return this.hasAlbumBySku(sku) ? this.AlbumsBySku[sku] : null;
+	},
+	getArtist: function (artist) {
+		return this.hasArtist(artist) ? this.Artists[artist] : null;
+	},
+	getSong: function (mp3) {
+		return this.hasSong(mp3) ? this.Songs[mp3] : null;
+	},
+	getCountry: function (country) {
+		return this.hasCountry(country) ? this.Countries[country] : null;
+	},
+	load: function (db, done) {
+		var C = this;
+
+		C.Artists     = {};
+		C.Albums      = {};
+		C.AlbumsBySku = {};
+		C.Genres      = {};
+		C.Countries   = {};
+		C.Songs       = {};
+
+		var strings = {};
+		var hasString = Object.prototype.hasOwnProperty.bind(strings);
+	
+//		async.parallel([
+		async.series([
+
+			// load artists
+			function (done) {
+				db.exec(
+					'select artist, description, homepage, city, state, country, bio, bandphoto '+
+					'from artists order by artist', function (err, artists) {
+					if (err) return done(err);
+
+					console.time("loading artists");
+					C.SortedArtists   = artists;
+					C.SortedCountries = [];
+
+					for (var i = 0; i < artists.length; ++ i) {
+						var artist = internFields(artists[i],'artist','homepage','city','state','country');
+
+						if (artist.country) {
+							var country;
+							if (C.hasCountry(artist.country)) {
+								country = C.Countries[artist.country];
+							}
+							else {
+								country = C.Countries[artist.country] = {
+									country: artist.country,
+									artists: []
+								};
+
+								C.SortedCountries.push(country);
+							}
+
+							country.artists.push(artist);
+						}
+
+						artist.albums = [];
+						addVector(artist, ['artist','bio','description']);
+
+						C.Artists[artist.artist] = artist;
+					}
+
+					C.SortedCountries.sort(function (lhs,rhs) {
+						lhs = lhs.country;
+						rhs = rhs.country;
+						return lhs < rhs ? -1 : rhs < lhs ? 1 : 0;
+					});
+
+					console.timeEnd("loading artists");
+
+					done(null);
+				});
+			},
+
+			// load albums
+			function (done) {
+				db.exec(
+					'select artist, albumname, also, description, sku, launchdate, upc, itunes '+
+					'from albums order by albumname', function (err, albums) {
+					if (err) return done(err);
+
+					console.time("loading albums");
+					C.SortedAlbums = albums;
+					for (var i = 0; i < albums.length; ++ i) {
+						var album = albums[i];
+						var also  = (album.also||'').trim();
+
+						album.launchdate = Number(album.launchdate);
+						album.also  = also ? also.split(/\s+/).map(intern) : [];
+						album.songs = [];
+
+						addVector(album, ['albumname', 'artist', 'description']);
+
+						C.AlbumsBySku[album.sku] = C.Albums[album.albumname] = internFields(album,'artist','albumname','sku');
+					}
+
+					console.timeEnd("loading albums");
+
+					done(null);
+				});
+			},
+
+			// load genres
+			function (done) {
+				db.exec(
+					'select genre, albumname from genres order by genre, albumname', function (err, albums_to_genres) {
+					if (err) return done(err);
+
+					console.time("loading genres");
+					var genres = C.SortedGenres = [];
+					for (var i = 0; i < albums_to_genres.length; ++ i) {
+						var album_to_genre = internFields(albums_to_genres[i],'genre','albumname');
+						var genre;
+						if (C.hasGenre(album_to_genre.genre)) {
+							genre = C.Genres[album_to_genre.genre];
+						}
+						else {
+							genre = C.Genres[album_to_genre.genre] = {
+								genre:  album_to_genre.genre,
+								albums: []
+							};
+							
+							addVector(genre, ['genre']);
+
+							genres.push(genre);
+						}
+						genre.albums.push(album_to_genre.albumname);
+					}
+
+					console.timeEnd("loading genres");
+
+					done(null);
+				});
+			},
+
+			// load songs
+			function (done) {
+				db.exec(
+					'select albumname, number, duration, mp3, desc from songs order by albumname, number', function (err, songs) {
+					if (err) return done(err);
+
+					console.time("loading songs");
+					C.SortedSongs = songs;
+					for (var i = 0; i < songs.length; ++ i) {
+						var song = songs[i];
+						song.number   = Number(song.number);
+						song.duration = Number(song.duration);
+						song = internFields(song,'albumname','mp3','desc');
+						C.Songs[song.mp3] = song;
+					}
+
+					console.timeEnd("loading songs");
+
+					done(null);
+				});
+			}
+
+		], function (err) {
+			if (err) return done(err);
+
+			var i;
+
+			// build references of songs
+			console.time("build references of songs");
+			for (i = 0; i < C.SortedSongs.length; ++ i) {
+				var song = C.SortedSongs[i];
+				var album = C.Albums[song.albumname];
+				album.songs.push(song);
+				song.album = album;
+				addVector(song, ['albumname','desc'], [album.artist]);
+				delete song.albumname;
+			}
+			console.timeEnd("build references of songs");
+
+			// build references of albums
+			console.time("build references of albums");
+			for (i = 0; i < C.SortedAlbums.length; ++ i) {
+				var album = C.SortedAlbums[i];
+				var artist = C.Artists[album.artist];
+				album.relatedAlbums = album.also.map(function (sku) {
+					return C.AlbumsBySku[sku];
+				});
+				artist.albums.push(album);
+				album.artist = artist;
+			}
+			console.timeEnd("build references of albums");
+
+			// build references of genres
+			console.time("build references of genres");
+			for (i = 0; i < C.SortedGenres.length; ++ i) {
+				var genre = C.SortedGenres[i];
+				genre.albums = genre.albums.map(function (albumname) {
+					return C.Albums[albumname];
+				});
+			}
+			console.timeEnd("build references of genres");
+
+			done(null, C);
+		});
+
+		function addVector (record,fields,values) {
+			var vector = {};
+			var sources = fields.map(function (field) { return record[field]; });
+			if (values) sources.push.apply(sources,values);
+
+			for (var i = 0; i < sources.length; ++ i) {
+				var source = (sources[i]||'').trim();
+				if (source) {
+					source = unorm.nfkc(source).toLowerCase().split(/\s+/g);
+					for (var j = 0; j < source.length; ++ j) {
+						vector[source[j]] = true;
+					}
+				}
+			}
+
+			record.vector = Object.keys(vector).sort().join(' ');
+		}
+
+		function intern (s) {
+			if (hasString(s)) {
+				return strings[s];
+			}
+			else {
+				return (strings[s] = s);
+			}
+		}
+
+		function internFields (obj) {
+			for (var i = 1; i < arguments.length; ++ i) {
+				var key = arguments[i];
+				obj[key] = intern(obj[key]);
+			}
+			return obj;
+		}
+	}
+};
+
 app.on('error', function (e) {
 	console.error(String(e));
-	dbshutdown();
 });
 
 updateDB(function (err) {
 	if (err) throw err;
-	if (!db) {
-		sqlite3.open(DB_FILE, {}, function (err, _db) {
+	if (!collection) {
+		sqlite3.open(DB_FILE, {}, function (err, db) {
 			if (err) throw err;
-			db = _db;
+
+			console.time("loading collection");
+			new Collection().load(db, function (err, coll) {
+				if (err) throw err;
+
+				db.close(function (err) {
+					if (err) console.error(String(err));
+					collection = coll;
+					console.timeEnd("loading collection");
+				});
+			});
 		});
 	}
 });
 	this.on('close', function () {
 		updateJob.stop();
 		server = null;
-		dbshutdown();
 		console.log("\nserver shutdown");
 	}).on('connection', function (sock) {
 		var id = sock.remoteAddress+':'+sock.remotePort;
 		});
 	}
 }
-
-function dbshutdown () {
-	if (db) {
-		db.close(function () {
-			db = null;
-		});
-	}
-}
     "connect": "2.x",
     "express": "2.x",
     "async": ">= 0.x",
-    "cron": ">= 0.x"
+    "cron": ">= 0.x",
+    "unorm": ">= 1.1.2"
   },
   "license": "GPLv2",
   "engines": {