title: Reading SQL from file and SQLiteOpenHelper
body: <p>I need to include an existing <code>SQLiteDatabase</code> in my Android app and I also want the ability to download and install a new db. I did some research and my first working solution came <a href="http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications" rel="nofollow">from here</a>. I didn't like that solution, for one thing, it assumes that the database will always be in a fixed path and other oddities.</p>
  
  <p>So, rather than putting the existing database file in the assets, I exported the database to a SQL file, read it in, and then in the <code>onCreate()</code> method of my <code>SQLiteOpenHelper</code>, I call a new method  <code>updateDatabase</code> with an open <code>DataInputStream</code> with the file data.
  I ran into a few issues, which I think I solved, but I'm sure I didn't think of all the issues, which are:</p>
  
  <ol>
  <li><p>When <code>SQLiteOpenHelper</code>'s <code>onCreate</code> method is called, the database has been created, it is open, and <code>inTransaction()</code> is <code>true</code>.  As a result, if the imported sql file includes <code>BEGIN TRANSACTION</code>, an exception is thrown and if the sql String contains statements creating <code>'android_metadata'</code> yet another exception.  So, I added a simple search using <code>String.contains()</code> looking for these keywords, and set a <code>boolean</code> <code>doExecute</code>  to <code>false</code> to avoid executing them.  So a question is, is there a better SQL class or method to filter this, or even a better regexp method?</p></li>
  <li><p>Similar issue with having unexpected line breaks in the SQL file.  I read the file with <code>readLine()</code> and to look for line breaks, I simply use <code>String.trim()</code> on the line, then check for <code>endsWith(";")</code>.  This puts some constraints on my input file, like not having multiple statements on a single line.  So, is there a better way to pre-process SQL from a file?  </p></li>
  </ol>
  
  <p>Here's the code I use to create my db after I've gotten a <code>DataInputStream</code> from the <code>assets</code> resource or from a download:</p>
  
  <pre><code>    public boolean updateDatabase(DataInputStream inStream, SQLiteDatabase db, boolean doClear) throws Error {
      String sqlStatement = null;
      boolean result = true;
      boolean inOnCreate = true;
      boolean wasInTransaction;
  
      if(doClear) dropDatabase();
  
      // if called from onCreate() db is open and inTransaction, else getWritableDatabase()
      if(db == null) {
          inOnCreate = false;
          db = this.getWritableDatabase();
      }
  
      wasInTransaction = db.inTransaction();  // see NB below
  
      boolean doExecute;
      try {
          while ((sqlStatement = inStream.readLine()) != null) {
              // trim, so we can look for ';'
              sqlStatement.trim();
              if(!sqlStatement.endsWith(";")) {
                  continue;   // line breaks in file, get whole statement
              }
  
              // NB - my file (exported from SQLite Database Browser starts with "BEGIN TRANSACTION;". 
              // executing this throws SQLiteException: cannot start a transaction within a transaction
              // According to SQLiteDatabase doc for beginTransaction(), "Transactions can be nested"
              // so this is a problem
              // but... possibly it is an "exclusive transaction" ?
              doExecute = true;
              if(wasInTransaction) {
                  // don't execute BEGIN TRANSACTION; or COMMIT;
                  if((sqlStatement.contains("BEGIN" ) || sqlStatement.contains("begin" )) &amp;&amp;
                          (sqlStatement. contains("TRANSACTION") || sqlStatement.contains("transaction" ))) {
                      doExecute = false;
                  }
                  if(sqlStatement.contains("COMMIT") || sqlStatement.contains("commit")) {
                      doExecute = false;
                  }
              }   // inTransaction
              // this statement could be in older databases, but this scheme doesn't need, can't have it
              if(sqlStatement.contains("android_metadata")) {
                  doExecute = false;
              }
              if(doExecute) {
                  try {
                      db.execSQL(sqlStatement);
                  } catch (SQLException e) {
                      throw(new Error("Error executing SQL " + sqlStatement));
                  }   // try/catch
              }   // doExecute
          }   // while()
      } catch (IOException e) {
          result = false; // which won't matter if we throw 
          throw(new Error("Error reading  " + DB_SQL));
      } 
  
      if(!inOnCreate) {
          db.close();
      }
  
      return result;
  }
  </code></pre>