Check if DB exists and dropping DB in SQLlite IOS

I'm currently using phonegap to create and ios app.

While getting familiar to the sql javascript interactions I seem to have created 10 versions of the same named database file.

I'm currently using the following creation code (from the phonegap wiki)

var mydb=false;
// initialise the database
initDB = function() {
  try { 
    if (!window.openDatabase) { 
      alert('not supported'); 
    } else { 
      var shortName = 'phonegap'; 
      var version = '1.0'; 
      var displayName = 'PhoneGap Test Database'; 
      var maxSize = 65536; // in bytes 
      mydb = openDatabase(shortName, version, displayName, maxSize); 
     }
  } catch(e) { 
    // Error handling code goes here. 
    if (e == INVALID_STATE_ERR) { 
      // Version number mismatch. 
      alert("Invalid database version."); 
    } else { 
      alert("Unknown error "+e+"."); 
    } 
    return; 
  } 
}
// db error handler - prevents the rest of the transaction going ahead on failure
    errorHandler = function (transaction, error) { 
      // returns true to rollback the transaction
    return true;  
          } 
// null db data handler
    nullDataHandler = function (transaction, results) { }

my problem is that I'm unsure how to check if the database exists before creating it or how to create it only once per device?

and secondly how can i drop all these databases that have been created.

transaction.executeSql('DROP DATABASE phonegap;');

does not seem to drop anything.

Thanks

回答1:

Please try following code. it is not creating multiple database files, just cross verify by visiting location -

/Users/{username}/Library/Application Support/iPhone Simulator/4.3/Applications/{3D5CD3CC-C35B-41B3-BF99-F1E4B048FFFF}/Library/WebKit/Databases/file__0

This is sqlite3 example which cover create, insert, delete and drop queries on Table.

<!DOCTYPE html>
<html>
  <body style="font: 75% Lucida Grande, Trebuchet MS">
    <div id="content"></div>
    <p id="log" style="color: gray"></p>
    <script>
      document.getElementById('content').innerHTML = 
        '<h4>Simple to do list</h4>'+
        '<ul id="results"></ul><div>Handle Database in Phonegap</div>'+
        '<button onclick="newRecord()">new record</button>'+
        '<button onclick="createTable()">create table</button>' +
        '<button onclick="dropTable()">drop table</button>';
      var db;
      var log = document.getElementById('log');
      db = openDatabase("DBTest", "1.0", "HTML5 Database API example", 200000);
      showRecords();
      document.getElementById('results').addEventListener('click', function(e) { e.preventDefault(); }, false);
      function onError(tx, error) {
        log.innerHTML += '<p>' + error.message + '</p>';
      }
      // select all records and display them
      function showRecords() {
        document.getElementById('results').innerHTML = '';
        db.transaction(function(tx) {
          tx.executeSql("SELECT * FROM Table1Test", [], function(tx, result) {
            for (var i = 0, item = null; i < result.rows.length; i++) {
              item = result.rows.item(i);
              document.getElementById('results').innerHTML += 
                  '<li><span contenteditable="true" onkeyup="updateRecord('+item['id']+', this)">'+
                  item['id']+' '+item['text'] + '</span> <a href="#" onclick="deleteRecord('+item['id']+')">x</a></li>';
            }
          });
        });
      }
      function createTable() {
        db.transaction(function(tx) {
          tx.executeSql("CREATE TABLE Table1Test (id REAL UNIQUE, text TEXT)", [],
              function(tx) { log.innerHTML = 'Table1Test created' },
              onError);
        });
      }
      // add record with random values
      function newRecord() {
        var num = Math.round(Math.random() * 10000); // random data
        db.transaction(function(tx) {
          tx.executeSql("INSERT INTO Table1Test (id, text) VALUES (?, ?)", [num, 'Record:'],
              function(tx, result) {
                log.innerHTML = 'record added';
                showRecords();
              }, 
              onError);
        });
      }
      function updateRecord(id, textEl) {
        db.transaction(function(tx) {
          tx.executeSql("UPDATE Table1Test SET text = ? WHERE id = ?", [textEl.innerHTML, id], null, onError);
        });
      }
      function deleteRecord(id) {
        db.transaction(function(tx) {
          tx.executeSql("DELETE FROM Table1Test WHERE id=?", [id],
              function(tx, result) { showRecords() }, 
              onError);
        });
      }
      // delete table from db
      function dropTable() {
        db.transaction(function(tx) {
          tx.executeSql("DROP TABLE Table1Test", [],
              function(tx) { showRecords() }, 
              onError);
        });
      }
     </script>
  </body>
</html>

And about Droping Database... Does not seem meaningful for an embedded database engine like SQLite.
To create a new database, just do sqlite_open().
To drop a database, simply delete the file.

thanks,
Mayur

回答2:

Manually deleting the SQLite database from the Library worked for me. Thanks for the precious tip.