iamjwc/gist:259602

## gistfile1.htm
<?xml version="1.0" encoding="iso-8859-1"?>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
  <style>
    div {
      height: 1000px;
    }

    body {
      background-color: #eaeaea;
      font-family: "verdana";
    }

    table {
      position: fixed;
      top: 0em;
      left: 0em;
      right: 0;
      bottom: 0;
      font-size: 10px;
      width: 100%;

      -webkit-user-select: none;
    }

    thead th {
      background: -webkit-gradient(linear, left top, left bottom, from(#fcfcfc), to(#dfdfdf));
      border: 1px solid #afafaf;
      border-left-width: 0px;
      border-right-width: 0px;
      font-weight: normal;
      font-size: 10px;
    }

    th a {
      display: block;
    }

    tr.odd {
      background-color: #fff;
    }

    th {
      width: 20%;
    }

  </style>
  <script>


    db = openDatabase("TableTest", "1.0", "Table test example", 200000);
    db.transaction(function(tx) {
      tx.executeSql("SELECT COUNT(*) AS count FROM songs", [], function(tx, result) {
        totalRowCount = Number(result.rows.item(0)["count"]);
        previousCacheMaxSize = Math.floor(totalRowCount * 0.1);
        nextCacheMaxSize = Math.floor(totalRowCount * 0.1);

        // Seems to be the magic number to make scrolling by 1 line
        // at a time possible.
        document.getElementById("blank").style.height = totalRowCount * 4;
      })
    });

    function loaded()
    {
      db.transaction(function(tx) {
        tx.executeSql("SELECT COUNT(*) FROM songs", [], function() {}, function(tx, error) {
          tx.executeSql("CREATE TABLE songs (id INTEGER PRIMARY KEY, artist TEXT, album TEXT, song TEXT, track INTEGER)", [], function(result) {
            console.log("created database");
            tx.executeSql("CREATE INDEX IF NOT EXISTS songs_artist ON songs (artist)");
            console.log("created index 1");
            tx.executeSql("CREATE INDEX IF NOT EXISTS songs_album  ON songs (album)");
            console.log("created index 2");
            tx.executeSql("CREATE INDEX IF NOT EXISTS songs_song   ON songs (song)");
            console.log("created index 3");
            tx.executeSql("CREATE INDEX IF NOT EXISTS songs_track  ON songs (track)");
            console.log("created index 4");

            var rand = function(min,max) { return Math.floor(Math.random() * (max - min)) + min + 1; }
            var numArtists = 100;
            var numAlbums  = 10;
            var numSongs   = 10;

            for(var artist = 0; artist < numArtists; ++artist) {
              for(var album = 0; album < numAlbums; ++album) {
                for(var song = 0; song < numSongs; ++song) {
                  tx.executeSql("INSERT INTO songs (artist, album, song, track) values (?,?,?,?)", ["Artist " + (artist + 1), "Album " + (album + 1), "Song " + (song + 1), (song + 1)]);
                }
              }
            }
            console.log("creating songs");

          });
        });
      });
    }
    loaded();

    function resultToRows(result) {
      var rows = [];

      for (var i = 0; i < result.rows.length; ++i) {
        var item = result.rows.item(i);
        rows.push({
          id: item['id'],
          artist: item['artist'],
          album: item['album'],
          song: item['song'],
          track: Number(item['track'])
        });
      }

      return rows;
    }

    function insertIntoStartOfCache(rows, offset, count) {
    }

    function insertIntoEndOfCache(rows, offset, count) {
      var i = offset - firstRowDisplayed();

      var allowedNumberOfRowsPastCacheStart = numberOfDisplayedRows() + rowCacheNextMaxSize;

      for(; rows.length && (rowCache.length - firstRowDisplayed()) <= allowedNumberOfRowsPastCacheStart; ++i) {
        // Get the first row from the newly fetched rows;
        var row = rows.pop();

        // Add that row to the cache
        rowCache.push(row);

        // If the empty row is on the screen, then populate it.
        if(rowBeingDisplayed(i)) {
          populateEmptyRow(i, row);
        }
      }
    }

    function isRowEmpty(i) {
      var tr = document.getElementById(rowId(i));
      return tr && tr.getElementsByClassName("loading").length != 0;
    }

    function populateEmptyRow(i, row) {
      console.log('populateEmptyRow(' + i + ')');
      document.getElementById(rowId(i)).innerHTML = trFromResult(i, row);
    }

    order_on = "artist";
    order_by = {
      artist:         "ORDER BY artist ASC, album ASC, track ASC, song ASC",
      artist_reverse: "ORDER BY artist DESC, album ASC, track ASC, song ASC",
      album:          "ORDER BY album ASC, artist ASC, track ASC, song ASC",
      album_reverse:  "ORDER BY album DESC, artist ASC, track ASC, song ASC",
      song:           "ORDER BY song ASC, artist ASC, album ASC, track ASC",
      song_reverse:   "ORDER BY song DESC, artist ASC, album ASC, track ASC",
      track:          "ORDER BY track ASC, artist ASC, album ASC, song ASC",
      track_reverse:  "ORDER BY track DESC, artist ASC, album ASC, song ASC"
    }

    function addRowsToCache(offset, count) {
      var sql = "SELECT * FROM songs " + order_by[order_on] + " LIMIT " + offset + ", " + count;
      console.log(sql);


      db.transaction(function(tx) {
        tx.executeSql(sql, [], function(tx, result) {
          // Dunno why we need to reverse the rows. I thought they were
          // in order
          var rows = resultToRows(result);

          for(var j = 0, i = offset, n = offset + count; i < n; ++i, ++j) {
            var row = rows[j];
            rowCache[i] = row;
            if(isRowEmpty(i)) {
              populateEmptyRow(i, row);
            }
          }
        });
      });
    }

    function rowId(i) {
      return "row_" + i;
    }


    function updateCache() {
      var firstRowInDisplay = firstRowDisplayed();
      var lastRowInDisplay  = firstRowInDisplay + numberOfDisplayedRows();
      var firstRowOfCache   = Math.max(firstRowInDisplay - previousCacheMaxSize, 0);
      var lastRowOfCache    = Math.min(lastRowInDisplay  + nextCacheMaxSize, numberOfTotalRows());

      var indexesNotInCache = [];
      for(var i = 0, n = numberOfTotalRows(); i < n; ++i) {
        // If a row is in the cache that should not be there,
        // remove it.
        if(i in rowCache && (i < firstRowOfCache || i > lastRowOfCache)) {
          delete rowCache[i];
        }
      }

      // Overwrite entire cache
      addRowsToCache(firstRowOfCache, lastRowOfCache - firstRowOfCache);
    }

    // Pop N elements
    function removeElementsFromEndOfCache(n) {
      for(; n; --n) { rowCache.pop(); }
    }

    function buildRows(offset, limit) {
      var rows = [];

      for(var i = offset, n = offset + limit; i < n; ++i) {
        rows.push(buildRow(i))
      }

      return rows.join("");
    }


    function trFromResult(i, row) {
      return "<td>" + [i, row['artist'], row['album'], row['song'], row['track']].join("</td><td>") + "</td>";
    }

    function buildRow(i) {
      var tr = "<tr id='" + rowId(i) + "' class='row " + ((i % 2) ? "odd" : "even") + "'>"
      if(inCache(i)) {
        var row = fromCache(i);
        return tr + trFromResult(i, row) + "</tr>";
      } else {
        return tr + "<td colspan='6' class='loading'>Loading</td></tr>";
      }
    }

    rowCache = {};

    previousCacheMaxSize = 200;
    nextCacheMaxSize     = 200;

    function inCache(i) {
      return i in rowCache;
    }
    function fromCache(i) {
      return rowCache[i];
    }

    totalRowCount = 10000;
    function numberOfTotalRows() {
      return totalRowCount;
    }

    function titleBarHeight() {
      return document.getElementById("thead").clientHeight;
    }

    function rowHeight() {
      return document.getElementsByClassName("row")[0].clientHeight;
    }


    function numberOfDisplayedRows() {
      var windowHeight = window.innerHeight;
      var remainingHeight = windowHeight - titleBarHeight();
      var numberOfRowsInRemainingHeight = remainingHeight / rowHeight();

      return Math.floor(numberOfRowsInRemainingHeight);
    }

    function firstRowDisplayed() {
      var scrollPositionPercentage = Math.abs(window.scrollY / (document.height - window.innerHeight));

      return Math.floor((numberOfTotalRows() - numberOfDisplayedRows()) * scrollPositionPercentage);
    }

    function rowBeingDisplayed(i) {
      return i >= firstRowDisplayed() && i < (firstRowDisplayed() + numberOfDisplayedRows())
    }

    window.addEventListener("load", function() {
      invalidate();

      var types = ["artist", "album", "track", "song"];
      for(var i in types) {
        document.getElementById(types[i] + "_sort").addEventListener("click", clickHandler(types[i]), false);
      }

    }, false);

    window.addEventListener('resize', function() {
      invalidate();
    }, false);

    function clickHandler(name) {
      return function() {
        if(order_on != name) {
          order_on = name;
        } else {
          order_on = name + "_reverse";
        }

        rowCache = {};
        invalidate();
      };
    }


    window.addEventListener("scroll", function(e) {
      invalidate();
    }, false);


    function invalidate() {
      updateCache();

      var offset = firstRowDisplayed();
      var limit  = numberOfDisplayedRows();
      document.getElementById("tbody").innerHTML = buildRows(offset, limit);
    }


  </script>
</head>
<body>

<div id="blank" class="blank"></div>
<table id="t" cellpadding="0" cellspacing="0">
  <thead id="thead">
    <tr>
      <th>pos</th>
      <th><a href="#" id="artist_sort">Artist</a></th>
      <th><a href="#" id="album_sort">Album</a></th>
      <th><a href="#" id="song_sort">Song</a></th>
      <th><a href="#" id="track_sort">Track #</a></th>
    </tr>
  </thead>
  <tbody id="tbody">
    <tr class="row">
      <td colspan="4">Loading</td>
    </tr>
  </tbody>
</table>

</body>
</html>
	<?xml version="1.0" encoding="iso-8859-1"?>
	<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
	<head>
	<style>
	div {
	height: 1000px;
	}

	body {
	background-color: #eaeaea;
	font-family: "verdana";
	}

	table {
	position: fixed;
	top: 0em;
	left: 0em;
	right: 0;
	bottom: 0;
	font-size: 10px;
	width: 100%;

	-webkit-user-select: none;
	}

	thead th {
	background: -webkit-gradient(linear, left top, left bottom, from(#fcfcfc), to(#dfdfdf));
	border: 1px solid #afafaf;
	border-left-width: 0px;
	border-right-width: 0px;
	font-weight: normal;
	font-size: 10px;
	}

	th a {
	display: block;
	}

	tr.odd {
	background-color: #fff;
	}

	th {
	width: 20%;
	}

	</style>
	<script>


	db = openDatabase("TableTest", "1.0", "Table test example", 200000);
	db.transaction(function(tx) {
	tx.executeSql("SELECT COUNT(*) AS count FROM songs", [], function(tx, result) {
	totalRowCount = Number(result.rows.item(0)["count"]);
	previousCacheMaxSize = Math.floor(totalRowCount * 0.1);
	nextCacheMaxSize = Math.floor(totalRowCount * 0.1);

	// Seems to be the magic number to make scrolling by 1 line
	// at a time possible.
	document.getElementById("blank").style.height = totalRowCount * 4;
	})
	});

	function loaded()
	{
	db.transaction(function(tx) {
	tx.executeSql("SELECT COUNT(*) FROM songs", [], function() {}, function(tx, error) {
	tx.executeSql("CREATE TABLE songs (id INTEGER PRIMARY KEY, artist TEXT, album TEXT, song TEXT, track INTEGER)", [], function(result) {
	console.log("created database");
	tx.executeSql("CREATE INDEX IF NOT EXISTS songs_artist ON songs (artist)");
	console.log("created index 1");
	tx.executeSql("CREATE INDEX IF NOT EXISTS songs_album ON songs (album)");
	console.log("created index 2");
	tx.executeSql("CREATE INDEX IF NOT EXISTS songs_song ON songs (song)");
	console.log("created index 3");
	tx.executeSql("CREATE INDEX IF NOT EXISTS songs_track ON songs (track)");
	console.log("created index 4");

	var rand = function(min,max) { return Math.floor(Math.random() * (max - min)) + min + 1; }
	var numArtists = 100;
	var numAlbums = 10;
	var numSongs = 10;

	for(var artist = 0; artist < numArtists; ++artist) {
	for(var album = 0; album < numAlbums; ++album) {
	for(var song = 0; song < numSongs; ++song) {
	tx.executeSql("INSERT INTO songs (artist, album, song, track) values (?,?,?,?)", ["Artist " + (artist + 1), "Album " + (album + 1), "Song " + (song + 1), (song + 1)]);
	}
	}
	}
	console.log("creating songs");

	});
	});
	});
	}
	loaded();

	function resultToRows(result) {
	var rows = [];

	for (var i = 0; i < result.rows.length; ++i) {
	var item = result.rows.item(i);
	rows.push({
	id: item['id'],
	artist: item['artist'],
	album: item['album'],
	song: item['song'],
	track: Number(item['track'])
	});
	}

	return rows;
	}

	function insertIntoStartOfCache(rows, offset, count) {
	}

	function insertIntoEndOfCache(rows, offset, count) {
	var i = offset - firstRowDisplayed();

	var allowedNumberOfRowsPastCacheStart = numberOfDisplayedRows() + rowCacheNextMaxSize;

	for(; rows.length && (rowCache.length - firstRowDisplayed()) <= allowedNumberOfRowsPastCacheStart; ++i) {
	// Get the first row from the newly fetched rows;
	var row = rows.pop();

	// Add that row to the cache
	rowCache.push(row);

	// If the empty row is on the screen, then populate it.
	if(rowBeingDisplayed(i)) {
	populateEmptyRow(i, row);
	}
	}
	}

	function isRowEmpty(i) {
	var tr = document.getElementById(rowId(i));
	return tr && tr.getElementsByClassName("loading").length != 0;
	}

	function populateEmptyRow(i, row) {
	console.log('populateEmptyRow(' + i + ')');
	document.getElementById(rowId(i)).innerHTML = trFromResult(i, row);
	}

	order_on = "artist";
	order_by = {
	artist: "ORDER BY artist ASC, album ASC, track ASC, song ASC",
	artist_reverse: "ORDER BY artist DESC, album ASC, track ASC, song ASC",
	album: "ORDER BY album ASC, artist ASC, track ASC, song ASC",
	album_reverse: "ORDER BY album DESC, artist ASC, track ASC, song ASC",
	song: "ORDER BY song ASC, artist ASC, album ASC, track ASC",
	song_reverse: "ORDER BY song DESC, artist ASC, album ASC, track ASC",
	track: "ORDER BY track ASC, artist ASC, album ASC, song ASC",
	track_reverse: "ORDER BY track DESC, artist ASC, album ASC, song ASC"
	}

	function addRowsToCache(offset, count) {
	var sql = "SELECT * FROM songs " + order_by[order_on] + " LIMIT " + offset + ", " + count;
	console.log(sql);


	db.transaction(function(tx) {
	tx.executeSql(sql, [], function(tx, result) {
	// Dunno why we need to reverse the rows. I thought they were
	// in order
	var rows = resultToRows(result);

	for(var j = 0, i = offset, n = offset + count; i < n; ++i, ++j) {
	var row = rows[j];
	rowCache[i] = row;
	if(isRowEmpty(i)) {
	populateEmptyRow(i, row);
	}
	}
	});
	});
	}

	function rowId(i) {
	return "row_" + i;
	}


	function updateCache() {
	var firstRowInDisplay = firstRowDisplayed();
	var lastRowInDisplay = firstRowInDisplay + numberOfDisplayedRows();
	var firstRowOfCache = Math.max(firstRowInDisplay - previousCacheMaxSize, 0);
	var lastRowOfCache = Math.min(lastRowInDisplay + nextCacheMaxSize, numberOfTotalRows());

	var indexesNotInCache = [];
	for(var i = 0, n = numberOfTotalRows(); i < n; ++i) {
	// If a row is in the cache that should not be there,
	// remove it.
	if(i in rowCache && (i < firstRowOfCache \|\| i > lastRowOfCache)) {
	delete rowCache[i];
	}
	}

	// Overwrite entire cache
	addRowsToCache(firstRowOfCache, lastRowOfCache - firstRowOfCache);
	}

	// Pop N elements
	function removeElementsFromEndOfCache(n) {
	for(; n; --n) { rowCache.pop(); }
	}

	function buildRows(offset, limit) {
	var rows = [];

	for(var i = offset, n = offset + limit; i < n; ++i) {
	rows.push(buildRow(i))
	}

	return rows.join("");
	}


	function trFromResult(i, row) {
	return "<td>" + [i, row['artist'], row['album'], row['song'], row['track']].join("</td><td>") + "</td>";
	}

	function buildRow(i) {
	var tr = "<tr id='" + rowId(i) + "' class='row " + ((i % 2) ? "odd" : "even") + "'>"
	if(inCache(i)) {
	var row = fromCache(i);
	return tr + trFromResult(i, row) + "</tr>";
	} else {
	return tr + "<td colspan='6' class='loading'>Loading</td></tr>";
	}
	}

	rowCache = {};

	previousCacheMaxSize = 200;
	nextCacheMaxSize = 200;

	function inCache(i) {
	return i in rowCache;
	}
	function fromCache(i) {
	return rowCache[i];
	}

	totalRowCount = 10000;
	function numberOfTotalRows() {
	return totalRowCount;
	}

	function titleBarHeight() {
	return document.getElementById("thead").clientHeight;
	}

	function rowHeight() {
	return document.getElementsByClassName("row")[0].clientHeight;
	}


	function numberOfDisplayedRows() {
	var windowHeight = window.innerHeight;
	var remainingHeight = windowHeight - titleBarHeight();
	var numberOfRowsInRemainingHeight = remainingHeight / rowHeight();

	return Math.floor(numberOfRowsInRemainingHeight);
	}

	function firstRowDisplayed() {
	var scrollPositionPercentage = Math.abs(window.scrollY / (document.height - window.innerHeight));

	return Math.floor((numberOfTotalRows() - numberOfDisplayedRows()) * scrollPositionPercentage);
	}

	function rowBeingDisplayed(i) {
	return i >= firstRowDisplayed() && i < (firstRowDisplayed() + numberOfDisplayedRows())
	}

	window.addEventListener("load", function() {
	invalidate();

	var types = ["artist", "album", "track", "song"];
	for(var i in types) {
	document.getElementById(types[i] + "_sort").addEventListener("click", clickHandler(types[i]), false);
	}

	}, false);

	window.addEventListener('resize', function() {
	invalidate();
	}, false);

	function clickHandler(name) {
	return function() {
	if(order_on != name) {
	order_on = name;
	} else {
	order_on = name + "_reverse";
	}

	rowCache = {};
	invalidate();
	};
	}


	window.addEventListener("scroll", function(e) {
	invalidate();
	}, false);



	function invalidate() {
	updateCache();

	var offset = firstRowDisplayed();
	var limit = numberOfDisplayedRows();
	document.getElementById("tbody").innerHTML = buildRows(offset, limit);
	}


	</script>
	</head>
	<body>

	<div id="blank" class="blank"></div>
	<table id="t" cellpadding="0" cellspacing="0">
	<thead id="thead">
	<tr>
	<th>pos</th>
	<th><a href="#" id="artist_sort">Artist</a></th>
	<th><a href="#" id="album_sort">Album</a></th>
	<th><a href="#" id="song_sort">Song</a></th>
	<th><a href="#" id="track_sort">Track #</a></th>
	</tr>
	</thead>
	<tbody id="tbody">
	<tr class="row">
	<td colspan="4">Loading</td>
	</tr>
	</tbody>
	</table>

	</body>
	</html>