VGMdb
Go Back   VGMdb Forums > Discussion > Miscellaneous Discussion
Register FAQ Calendar Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old Nov 1, 2009, 10:19 AM
dma's Avatar
dma dma is offline
 
Join Date: Sep 2007
Location: France
Posts: 99
Lightbulb VGMdb Tool : Display your collection as a table (Greasemonkey script)

Hi all,

I talked with Secret Squirrel a while ago about a different way to display collections, say more spreadsheet like.
But, most certainly due to others priorities and all, it seems that no one at VGMdb would have had time to work on this.

So i took a look at the "Greasemonkey" Firefox extension, which offers a scripting system to modify pages on websites, so that you can change the way these are displayed and even add features.
And it turned out that i could actually get what i wanted using this system.

After a week-end of nasty javascript/xpath/jquery coding (and then some times over various week-end to add or fix things in the latest years ), here is the result.

What does it do ?
The collections are now displayed as a table with "catalog number", "album title" and "parent folders path" columns.
The table is filterable (enter text in field at the top of the table, it filters over the three columns) and sortable (use SHIFT key for multi-column sort selection).
You can export the table data to CSV (including album page URL and album type).

To install the script :
- You first need to install the Greasemonkey extension on Firefox or Tampermonkey on Chrome/etc... (Tampermonkey compatibility only tested on Chrome).
- Then add a new script in the Greasemonkey/Tampermonkey menu, copy/paste the content of the code box bellow over all existing text of that script window, and save (ctrl+S).
- Head over to your collection page (or anyone else's), wait for complete page loading and processing, it will now be displayed as a table.

Changes history:

0.9 2020/08/23
- fixing original list action buttons removal

0.8 2018/12/06
- fixing parsing of private albums

0.7 2018/05/20
- more explicit export filename

0.6 2018/05/20
- add date and time to CSV export filename

0.5 2018/05/20
- add CSV export feature (including album page URL and album type)

0.4 2018/04/21
- handling secure VGMdb URL
- fixing jquery-tablesorter-filter version for a working one
- only filtering cat and title columns (not much point in filtering the folder column after all)

0.3 2010/03/12
- process corrected according to the new collection page structure

Code:
// ==UserScript==
// @name           VGMdb Collection
// @version        0.9
// @author         Mathieu Stempell (Dma-Sc) dma-sc@gmx.fr
// @namespace      http://dma-sc.atari.org/vgmdb_collection
// @description    Display VGMdb collection as a sortable and filterable table
// @include        /^https?://vgmdb\.net/db/collection\.php\?do=view.*$/
// @grant          none
// @require        https://raw.githubusercontent.com/jbritten/jquery-tablesorter-filter/62109591bc1d7189045d7645a313b35801fe5c16/jquery-1-3-2.js
// @require        https://raw.githubusercontent.com/jbritten/jquery-tablesorter-filter/62109591bc1d7189045d7645a313b35801fe5c16/tablesorter.js
// @require        https://raw.githubusercontent.com/jbritten/jquery-tablesorter-filter/62109591bc1d7189045d7645a313b35801fe5c16/tablesorter_filter.js
// ==/UserScript==

// changelog:
// 0.9 2020/08/23
// - fixing original list action buttons removal
// 0.8 2018/12/06
// - fixing parsing of private albums
// 0.7 2018/05/20
// - more explicit export filename
// 0.6 2018/05/20
// - add date and time to CSV export filename
// 0.5 2018/05/20
// - add CSV export feature (including album page and album type)
// 0.4 2018/04/21
// - handling secure VGMdb URL
// - fixing jquery-tablesorter-filter version for a working one
// - only filtering cat and title columns (not much point in filtering the folder column after all)
// 0.3 2010/03/12
// - process corrected according to the new collection page structure

function addGlobalStyle(css) {
	var head, style;
	head = document.getElementsByTagName('head')[0];
	if (!head) { return; }
	style = document.createElement('style');
	style.type = 'text/css';
	style.innerHTML = css;
	head.appendChild(style);
}

function evaluteXPath(query, baseNode, unique) {
	var returnValue;
	returnValue = document.evaluate(query,
	baseNode,
	null,
	XPathResult.ORDERED_NODE_SNAPSHOT_TYPE,
	null);
	
	if (unique) {
		
		// if multiple results, returning first result with real text value
		if (returnValue.snapshotLength > 1) {
			for (var returnValueIt = 0; returnValueIt < returnValue.snapshotLength; returnValueIt++) {
				var currentReturnValueTest = jQuery.trim(returnValue.snapshotItem(returnValueIt).data);
				if (currentReturnValueTest != null && currentReturnValueTest != ""
						&& currentReturnValueTest != "/" // if title display is set to original or romanized, a slash character is present in the result
						) {
					return returnValue.snapshotItem(returnValueIt);
				}
			}
		}
		return returnValue.snapshotItem(0);
	}
	return returnValue;
}

function evaluteXPathMultipleReturn(query, baseNode) {
	return evaluteXPath(query, baseNode, false);
}

function evaluteXPathSingleReturn(query, baseNode) {
	return evaluteXPath(query, baseNode, true);
}

function appendDataCell(tableRow, data) {
	
	var tableRowCell = document.createElement("td");
	tableRowCell.setAttribute("class", "smallfont");
	tableRowCell.innerHTML=data;
	tableRow.appendChild(tableRowCell);
}

function appendHeaderCell(tableRow, title) {
	
	var tableRowHeader = document.createElement("th");
	tableRowHeader.innerHTML=title;
	tableRow.appendChild(tableRowHeader);
}

function padOnTwoDigits(val) {
	return (val < 10) ? ("0" + val) : val;
}

// Original credit for this export function goes to https://stackoverflow.com/a/16203218
function exportTableToCSV($table, filename) {
	
	var $rows = $table.find('tr:has(td)'),

	tmpColDelim = String.fromCharCode(11), // vertical tab character
	tmpRowDelim = String.fromCharCode(0), // null character
	colDelim = '";"',
	rowDelim = '"\r\n"',
	header = 'cat' + colDelim + 'title' + colDelim + 'url' + colDelim + 'type' + colDelim + 'folder' + rowDelim;

	csv = '"' + header + $rows.map(function(i, row) {
		var $row = $(row),
		$cols = $row.find('td');

		return $cols.map(function(j, col) {
			var $col = $(col),
			text = $col.text();
			
			// Add album page link URL and album type (extracted from title class) as additional column
			if (j == 1) {
				$col.find('a.albumtitle').each(function (k, element) {
					
					text = text + tmpColDelim + $(element).attr('href');
					
					var type = '';
					$.each($(element).attr('class').split(/\s+/), function (l, albumClass) {
						if (/^album-/.test(albumClass)) {
							type = albumClass.split("album-").join("");
						}
					});
					text = text + tmpColDelim + type;
				});
			}

			return text.replace(/"/g, '""'); // escape double quotes

		}).get().join(tmpColDelim);

	}).get().join(tmpRowDelim)
	.split(tmpRowDelim).join(rowDelim)
	.split(tmpColDelim).join(colDelim) + '"';

	if (window.Blob && window.URL) {
	// HTML5 Blob file export where available
	var blob = new Blob([csv], {
		type: 'text/csv;charset=utf-8'
	});
	var csvUrl = URL.createObjectURL(blob);

	$(this)
		.attr({
		'download': filename,
		'href': csvUrl
		});
	} else {
	// Data URI file export as a fallback
	var csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);

	$(this)
		.attr({
		'download': filename,
		'href': csvData,
		'target': '_blank'
		});
	}
}

	(function() {

		if(unsafeWindow.console){
			var GM_log = unsafeWindow.console.log;
		}

		addGlobalStyle("table.tablesorter thead tr th { background-repeat: no-repeat; background-position: 3px 3px; }");
		addGlobalStyle("table.tablesorter thead tr th.headerSortUp { background-image: url(../forums/images/buttons/sortdesc.gif); }");
		addGlobalStyle("table.tablesorter thead tr th.headerSortDown { background-image: url(../forums/images/buttons/sortasc.gif); }");
		addGlobalStyle("table.tablesorter thead tr th.header { cursor: pointer; min-width: 60px; width:auto !important; _width: 60px; /* IE6 hack */ border: 2px solid #3C405C; }");
		addGlobalStyle("table.tablesorter tbody tr td { border-bottom: 1px solid #3C405C; }");
		addGlobalStyle("table.tablesorter { border-left: 2px solid #3C405C; border-right: 2px solid #3C405C; padding: 0px; }");

		var searchMainContentNode = "/html/body/div/div/div/div/ul[@id='mycollection']/..";
		var searchCollectionNode = "./ul[@id='mycollection']";
		var searchButtonsNode = "./div/a[text() = 'Expand All']/..";
		var searchAlbums = ".//li/a/..|.//li/b/a/../..";
		var searchAlbumPage = "./descendant::a[contains(@class,'albumtitle')]/@href";
		var searchAlbumTitle = "./descendant::a/span[contains(@style,'inline')]//text()";
		var searchAlbumTitleColor = "./descendant::a[contains(@class,'albumtitle')]/@class";
		var searchAlbumRef = "./descendant::span[contains(@class,'catalog')]/text()";
		var searchAlbumFolders = "ancestor::li[@class='submenu']/b/text()|ancestor::li[@class='submenu']/text()";
	
		var currentAlbum;
		var currentAlbumTitle;
		var currentAlbumTitleColor;
		var currentAlbumRef;
		var currentAlbumPage;
		var currentAlbumId;
		var currentAlbumFolders;
		var currentAlbumFoldersIt;
		var currentAlbumFolder;
		var currentAlbumFolderSub;
		var currentAlbumRow;
		var currentAlbumRowCell;
		var gotFolders=false;
	
		var mainContentNode = evaluteXPathSingleReturn(searchMainContentNode, document);
		
		var albumsTable = document.createElement("table");
		albumsTable.setAttribute("id", "collectionTable");
		albumsTable.setAttribute("class", "tablesorter");
		albumsTable.setAttribute("border", 0);
		albumsTable.setAttribute("cellspacing", 2);
		albumsTable.setAttribute("style", "padding-top: 6px");
	
		var albumsTableHeader = document.createElement("thead");
		albumsTable.appendChild(albumsTableHeader);
		
		var albumsTableHeaderRow = document.createElement("tr");
		appendHeaderCell(albumsTableHeaderRow, "cat");
		appendHeaderCell(albumsTableHeaderRow, "title");
		albumsTableHeader.appendChild(albumsTableHeaderRow);
	
		var albumsTableBody = document.createElement("tbody");
		albumsTable.appendChild(albumsTableBody);
	
		var collectionNode = evaluteXPathSingleReturn(searchCollectionNode, mainContentNode);
			
		var allAlbums = evaluteXPathMultipleReturn(searchAlbums, collectionNode);
			
		for (var allAlbumsIt = 0; allAlbumsIt < allAlbums.snapshotLength; allAlbumsIt++) {
	
			currentAlbum = allAlbums.snapshotItem(allAlbumsIt);
			currentAlbumTitle = evaluteXPathSingleReturn(searchAlbumTitle, currentAlbum).data;
			currentAlbumTitleColor = evaluteXPathSingleReturn(searchAlbumTitleColor, currentAlbum).value;
			currentAlbumRef = evaluteXPathSingleReturn(searchAlbumRef, currentAlbum).data;
			currentAlbumPage = evaluteXPathSingleReturn(searchAlbumPage, currentAlbum).value;
			currentAlbumId = currentAlbumPage.substring(currentAlbumPage.lastIndexOf('/')+1);
			
			// handling parent folders search not contained in any dedicated element (like a "div" or a "span" node)
			currentAlbumFolders = evaluteXPathMultipleReturn(searchAlbumFolders, currentAlbum);
			currentAlbumFolder="";
			for (currentAlbumFoldersIt = 0; currentAlbumFoldersIt < currentAlbumFolders.snapshotLength; currentAlbumFoldersIt++) {
				currentAlbumFolderSub = jQuery.trim(currentAlbumFolders.snapshotItem(currentAlbumFoldersIt).data);
				// if folders aren't in a "b" node (private), many empty or space filled texts are returned along the interesting text value, filtering those
			if (currentAlbumFolderSub != null && currentAlbumFolderSub != "") {
				gotFolders=true;
				currentAlbumFolder = currentAlbumFolder+"/"+currentAlbumFolderSub;
			}
		}
		
		currentAlbumRow = document.createElement("tr");
		appendDataCell(currentAlbumRow, "<span class='time'>"+currentAlbumRef+"</span>");
		appendDataCell(currentAlbumRow, "<a href='"+currentAlbumPage+"' class='"+currentAlbumTitleColor+"'>"+currentAlbumTitle+"</a>");
		appendDataCell(currentAlbumRow, "<span class='time' style='color: silver'>"+currentAlbumFolder+"</span>");
		albumsTableBody.appendChild(currentAlbumRow);
	}

	if (gotFolders) {
		appendHeaderCell(albumsTableHeaderRow, "folder");
	}

	mainContentNode.appendChild(albumsTable);
	mainContentNode.removeChild(collectionNode);

	var buttonsNode = evaluteXPathSingleReturn(searchButtonsNode, mainContentNode);
	mainContentNode.removeChild(buttonsNode);

	$(".smallfont.time").eq(0).append(" - Filter: <input name='filter' id='filter-box' value='' maxlength='30' size='30' type='text' class='bginput smallfont'/><input id='filter-clear-button' type='submit' value='Clear' class='button'/> (use SHIFT key for multi-column sorting) - <a href='#' id='export-link'>Export to CSV</a>");
	$("#collectionTable").tablesorter()
	.tablesorterFilter({filterContainer: $("#filter-box"), filterClearContainer: $("#filter-clear-button"), filterColumns: [0, 1], filterCaseSensitive: false});

	// Export element must be a hyperlink
	$("#export-link").click(function(event) {
		var d = new Date();
		var date = d.getFullYear() + '_' + padOnTwoDigits(d.getMonth() + 1) + '_' + padOnTwoDigits(d.getDate());
		var time = padOnTwoDigits(d.getHours()) + '_' + padOnTwoDigits(d.getMinutes()) + '_' + padOnTwoDigits(d.getSeconds());
		exportTableToCSV.apply(this, [$('#collectionTable'), 'vgmdb-collection-' + date + '-' + time + '.csv']);
		// No "event.preventDefault()" or "return false" to behave like a hyperlink
	});
}());

Last edited by dma; Aug 23, 2020 at 07:18 AM.
Reply With Quote
  #2  
Old Nov 2, 2009, 04:56 AM
Secret Squirrel's Avatar
Secret Squirrel Secret Squirrel is offline
VGMdb Administrator
 
Join Date: May 2007
Location: Cleveland, OH
Posts: 6,208
Default

Do you have a screen shot of what the result looks like?

Also, thanks for sharing this. I'm thinking we ought to put up a page on the site for various third-party scripts that people have contributed.

Also, kudos on OpenOffice. I had a system HD crash on Friday, so I bought a new drive and reinstalled everything. This time, I decided not to put Office on here, but instead will use OpenOffice. The only thing that might trip me up is Powerpoint, but I can log into my work computer to use tht.
Reply With Quote
  #3  
Old Nov 2, 2009, 05:29 AM
dma's Avatar
dma dma is offline
 
Join Date: Sep 2007
Location: France
Posts: 99
Default

Attached here is a screenshot of the resulting table.

And yes the OpenOffice presentation editor isn't as intuitive as Powerpoint, also it's not very pratical to work on documents with the latest Microsoft Office and their OpenOffice equivalents.
Attached Images
File Type: png vgmdb_collection_table.png (8.9 KB, 85 views)
Reply With Quote
  #4  
Old Mar 12, 2010, 02:39 AM
dma's Avatar
dma dma is offline
 
Join Date: Sep 2007
Location: France
Posts: 99
Default

If anyone else but me uses this, i corrected it according to the new collection page structure (see first message).
Reply With Quote
  #5  
Old Mar 12, 2010, 05:46 AM
Lowe's Avatar
Lowe Lowe is offline
Member
 
Join Date: Jan 2008
Posts: 72
Default

This is great. Thanks for sharing.
Reply With Quote
  #6  
Old Mar 12, 2010, 07:27 AM
Nisto's Avatar
Nisto Nisto is offline
VGMdb Staff
 
Join Date: Sep 2009
Posts: 1,187
Default

Thanks for this and thanks for bumping the thread, dma I've missed out on this!
Reply With Quote
  #7  
Old Apr 21, 2018, 07:06 AM
dma's Avatar
dma dma is offline
 
Join Date: Sep 2007
Location: France
Posts: 99
Default

Yo, a new working version has been uploaded (see first post).
Reply With Quote
  #8  
Old May 20, 2018, 09:39 AM
dma's Avatar
dma dma is offline
 
Join Date: Sep 2007
Location: France
Posts: 99
Default

New version again, offering CSV export.

[edit] ...now with date and time in a globally more explicit export filename.

Last edited by dma; May 20, 2018 at 10:41 AM.
Reply With Quote
  #9  
Old May 22, 2018, 05:09 AM
dma's Avatar
dma dma is offline
 
Join Date: Sep 2007
Location: France
Posts: 99
Default

Added a note about Chrome compatibility in the first message.
Reply With Quote
  #10  
Old Aug 13, 2018, 10:38 PM
ilef's Avatar
ilef ilef is offline
Senior Member
 
Join Date: Apr 2008
Location: Italy
Posts: 333
Default

Thanks for this script.

I'm using Mozilla Firefox, v52.9.0|32 bit ("esr" = Extended Support Release) and Greasemonkey 4.1.

I didn't get a prompt to install the script, upon dragging it over the browser window, but doing so showed me the script's codeā€¦ So it was only a matter of copy->paste content in a new user script.

I'm mainly posting this to report that the script seemingly stops to work on my end, when I set all my albums to "private" (table is still displayed if I only leave folders private).
At any rate, I like the looks of it, so I thank you once again for writing this script.

Last edited by ilef; Aug 14, 2018 at 03:01 AM. Reason: Extremely late notice of a missing word, sorry.
Reply With Quote
  #11  
Old Dec 6, 2018, 11:51 AM
dma's Avatar
dma dma is offline
 
Join Date: Sep 2007
Location: France
Posts: 99
Default

Hey ilef,

Sorry for not getting back to you earlier, but i actually missed your message all that time.

OK to analyze the exact problem, the most simple thing would be to provide me a copy of your generated page, in the situation you described, with the script deactivated.
So that i could see what stops the script to process it correctly.

If you don't want to, for privacy concerns, i'll try to found out what's wrong another way, but it'll take more time.

I hope to hear from you soon then.
Reply With Quote
  #12  
Old Dec 6, 2018, 02:58 PM
dma's Avatar
dma dma is offline
 
Join Date: Sep 2007
Location: France
Posts: 99
Default

OK, thanks to ilef providing his generated collection page content, the problem could fixed.
The script just wasn't handling private albums (as i never used it myself, just setting folders containing albums, to private ).
Updated script in first post (v0.8).

Also indeed, Greasemonkey doesn't automatically install a script by drag'n drop anymore, adjusted instructions then.

So thanks to ilef again for report and data.

Last edited by dma; Dec 6, 2018 at 03:02 PM.
Reply With Quote
  #13  
Old Dec 6, 2018, 11:25 PM
ilef's Avatar
ilef ilef is offline
Senior Member
 
Join Date: Apr 2008
Location: Italy
Posts: 333
Default

Thanks to you for fixing this display issue, dma. Everything works perfectly!
Reply With Quote
  #14  
Old Aug 23, 2020, 07:12 AM
dma's Avatar
dma dma is offline
 
Join Date: Sep 2007
Location: France
Posts: 99
Default

Found out that it wasn't working anymore.
Now updated and working again.
Reply With Quote
Reply

Tags
browser, collection, tool

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Display Thread Vert1 Miscellaneous Discussion 0 Aug 11, 2018 03:47 PM
Display Name Despatche Questions and Comments 2 May 16, 2011 05:21 PM
Album Art Downloader - VGMdb Source Script? the_miker Questions and Comments 10 Mar 19, 2011 10:56 AM
Advanced collection display suggestion dma Questions and Comments 0 Jan 9, 2008 12:55 PM