1
    2
    3
    4
    5
    6
    7
    8
    9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   36
   37
   38
   39
   40
   41
   42
   43
   44
   45
   46
   47
   48
   49
   50
   51
   52
   53
   54
   55
   56
   57
   58
   59
   60
   61
   62
   63
   64
   65
   66
   67
   68
   69
   70
   71
   72
   73
   74
   75
   76
   77
   78
   79
   80
   81
   82
   83
   84
   85
   86
   87
   88
   89
   90
   91
   92
   93
   94
   95
   96
   97
   98
   99
  100
  101
  102
  103
  104
  105
  106
  107
  108
  109
  110
  111
  112
  113
  114
  115
  116
  117
  118
  119
  120
  121
  122
  123
  124
  125
  126
  127
  128
  129
  130
  131
  132
  133
  134
  135
  136
  137
  138
  139
  140
  141
  142
  143
  144
  145
  146
  147
  148
  149
  150
  151
  152
  153
  154
  155
  156
  157
  158
  159
  160


content / test / data / simple_database.html [blame]

<html>
<script>

// Open a Web SQL database.
var g_db = null;
if (typeof window.openDatabase == "undefined") {
  document.write("Error: Web SQL databases are not supported.");
}
try {
  g_db = openDatabase("test", "1.0", "test database", 1024 * 1024);
} catch(err) {
  document.write("Error: cannot open database.");
}

// Creates a table named "table1" with one text column named "data".
async function createTable() {
  if (!g_db)
    return;
  try {
    await new Promise((resolve, reject) => {
      g_db.transaction(
        function(tx) {
          tx.executeSql("CREATE TABLE table1 (data TEXT)");
        },
        reject, resolve);
    });
    return "done";
  } catch (error) {
    return error;
  }
}

// Inserts a record into the database.
async function insertRecord(text) {
  try {
    await new Promise((resolve, reject) => {
      g_db.transaction(
      function(tx) {
        tx.executeSql("INSERT INTO table1 VALUES (?)", [text]);
      }, reject, resolve);
    });
    return "done";
  } catch (error) {
    return error;
  }
}


// Updates a record at the given index with the given text. The indices are
// 0-based and are ordered from oldest record, to newest record.
async function updateRecord(index, text) {
  var didUpdate = false;
  try {
    const rowId = await findId(index);
    return new Promise((resolve, reject) => {
      g_db.transaction(
        function(tx) {
          tx.executeSql(
              "UPDATE table1 SET data=? WHERE ROWID=?",
              [text, rowId],
              function(tx, result) {
                if (result.rowsAffected == 1)
                  didUpdate = true;
                else if (result.rowsAffected == 0)
                  reject("could not update index: " + index);
                else
                  reject("multiple rows with index: " + index);
              });
        },
        function(error) {
          reject("update error: " + error);
        },
        function() {
          if (didUpdate)
            resolve("done");
        });
    });
  } catch (error) {
    return error;
  }
}

// Deletes a record at the given index.
async function deleteRecord(index) {
  try {
    const rowId = await findId(index);
    await new Promise((resolve, reject) => {
      g_db.transaction(
        function(tx) {
          tx.executeSql("DELETE FROM table1 WHERE ROWID=?", [rowId]);
        },
        function(error) {
          reject("delete error: " + error);
        }, resolve);
      });
    return "done";
  } catch (error) {
    return error;
  }
}

// Gets all the records in the database, ordered by their age.
async function getRecords() {
  try {
    const result = await new Promise((resolve, reject) => {
      g_db.readTransaction(function(tx) {
      tx.executeSql(
          "SELECT data FROM table1 ORDER BY ROWID",
          [],
          function(tx, result) {
            resolve(result);
          },
          function(tx, error) {
            reject("getRecords error: " + error);
          });
      });
    });
    let items = "";
    for (var i = 0; i < result.rows.length; i++) {
      if (items != "")
        items += ", ";
      items += result.rows.item(i).data;
    }
    return items;
  } catch (error) {
    return error;
  }
}

// Helper function that finds the ID for a record based on a given index.
async function findId(index) {
  return new Promise((resolve, reject) => {
    g_db.readTransaction(function(tx) {
      // |ROWID| is a special sqlite column. It is unique and is incremented
      // automatically when a new record is created.
      // |LIMIT| is a nonstandard clause supported by sqlite that lets us pick
      // rows from the database by index. E.g., LIMIT 2,10 will give us 10 records
      // starting at offset 2.
      tx.executeSql(
        "SELECT ROWID AS id FROM table1 ORDER BY ROWID LIMIT ?,1",
        [index],
        function(tx, result) {
          if (result.rows.length >= 1)
            resolve(result.rows.item(0).id);
          else
            reject("could not find row with index: " + index);
        },
        function(tx, error) {
          reject("findId error: " + error);
        });
    });
  });
}

</script>

<body>
This page is used for testing Web SQL databases.
</body>
</html>