CODEWITHSUNDEEP
sqldatabasesafarihtml
Parand
Parand

Reputation: 106350

HTML5 Database administration / introspection?

I'm playing around with html client side storage and wanted to know if there are any facilities for introspection of the database - is there an equivalent of sqlite ".tables" or ".schema" ?

Also, I'm not seeing my tables show up in AppData\Local\Apple Computer\Safari\LocalStorage . Is there another place tables are stored?

Upvotes: 1

Views: 1504

Answers (4)

Tony Brix
Tony Brix

Reputation: 4251

I have created a script to display the contents of the database.

<!DOCTYPE html>
<html>
<head>
<title>sqlite database</title>
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script type="text/javascript">
var db;
function openDB(shortName, version, displayName, maxSize){
 try{
  if(window.openDatabase)
  {
   db = openDatabase(shortName, version, displayName, maxSize);
  }
  else
  {
   alert("Your browser does not have a sqlite database");
  }
 }catch(e){
  alert(e);
 }
}
function executeQuery($query,callback,errorcallback){
 try{
  if(window.openDatabase){
   db.transaction(function(tx){
    tx.executeSql($query,[],function(tx,result){
     if(typeof callback == "function"){
      callback(result);
     }else{
      if(callback != undefined){
       eval(callback+"(result)");
      }
     }
    },function(tx,error){
     if(typeof errorcallback == "function"){
      errorcallback(error);
     }else{
      if(errorcallback != undefined){
       eval(errorcallback+"(error)");
      }
     }
    });
   });
  }
 }catch(e){
  alert(e);
 }
}
$(function(){
  openDB("dbname", "1.0", "Display Name", 2 * 1024 * 1024/*size of db*/);
  executeQuery("SELECT name FROM sqlite_master WHERE type='table'", function(tables)
  {
   var tableNames = new Array();
   for(var i = 1; i < tables.rows.length; i++)//i starts at 1 to skip over the "__WebKitDatabaseInfoTable__" table
   {
    tableNames.push(tables.rows.item(i).name);
   }
   var count = 0;
   for(var i = 0; i < tableNames.length; i++)//if there is no information in the table we cannot get the column names;
   {
    executeQuery("SELECT * FROM " + tableNames[i], function(results){
    var columnNames = new Array();
    var table = "<div class='tableName'>" + tableNames[count++] + "</div><table cellspacing='0' cellspacing='0' class='table'>";
    if(results.rows.length > 0)
    {
     table += "<tr class='headerRow'>";
     for(var column in results.rows.item(0))
     {
      columnNames.push(column);
      table += "<th class='columnName'>" + column + "</th>";
     }
     table += "</tr>"
     for(var i = 0; i < results.rows.length; i++)
     {
      table += "<tr class='tableRow'>";
      for(var columnName in columnNames)
      {
       table += "<td class='tableEntry'>" + results.rows.item(i)[columnNames[columnName]] + "</td>";
      }
      table += "</tr>";
     }
    }
    else
    {
     table += "<tr><td>empty</td></tr>";
    }
    table += "</table>";
    $("body").append(table);
   }, function(error){
    alert(error.message);
   });
  }
 }, function(error){
  alert(error.message);
 });
});
</script>
<style type="text/css">
.tableName{
 font-variant: small-caps;
 font-size: 20px;
 margin-top: 15px;
}
.headerRow{
    background-color: #ff0;
}
.columnName{
 border: 1px solid #000;
 padding: 2px 10px;
}
.tableRow{
    background-color: #AFA;
}
.tableEntry{
 border: 1px solid #000;
 padding: 2px 10px;
}
.table{
 border: 1px solid #000;
}
</style>
</head>
<body>
</body>
</html>

Upvotes: 0

river
river

Reputation: 6272

select * from sqlite_master;

That table has columns for type ('table' or 'index'), name, tbl_name, sql (the equivalent of what .schema outputs)

Upvotes: 0

Great Turtle
Great Turtle

Reputation: 3345

Safari stores the tables in "AppData\Local\Apple Computer\Safari\Databases".

First Databases.db is an sqlite3 database with 2 tables.

Origins tracks what site created the database and maximum storage for that database.

Databases tracks the specific databases and their folder, common and file name

I use sqlite3 command line tool or sometimes SQLite Administrator. Any sqlite3 app will do.

Your database will be in a subfolder like http_exeample_com_0\00000000003.db

Upvotes: 1

J-16 SDiZ
J-16 SDiZ

Reputation: 26910

This is browser specific.

For Safari, you need Safari 4 -- they have a inspection tool(Figure 2-11) for that purpose.

Upvotes: 2

Related Questions