11 Replies Latest reply on Nov 21, 2010 5:50 AM by tux-

    How to update an existing SQLite .db file?

    tux-

      Hi all,

       

      I am developing a small todo application in AIR. I make use of a .db SQLite file to store data and stuff.

       

      Now when my application is updated, the .db file will be overwritten. Is there a way to avoid this when it already exists?

        • 1. Re: How to update an existing SQLite .db file?
          commadelimited Level 2

          It's a good idea to always check for the existence of a database before trying to use it. The File class has a built in method called ifExists. The way I approach this is to first check for existence, then do one thing, or the other based on the result:

           

          var dbFile = air.File.applicationDirectory.resolvePath('yoursqlite.db');

           

          function checkForDB () {

               if (!dbFile.exists) {

                    alert('database does not exist, add a new db');

               } else {

                    alert('database exists, update rather than replace');

               }

          }

          • 2. Re: How to update an existing SQLite .db file?
            tux- Level 1

            I see, but when I pack the .air file, the .db will be included anyway, right? So when I start the app for the first time, it already has the new database.

            Or should I create it dynamically then? (If so: How could I create a .db file from within AIR?)

             

            Sorry, I'm rather new to this....

            • 3. Re: How to update an existing SQLite .db file?
              commadelimited Level 2

              Don't worry about it. We all started new at some point. Here's what I do with my AIR apps.

               

              1) Package a prestructured db file with the application. The DB should include any data required for the app to run on it's first time. Generally this would include all of your tables, just empty.

              2) In your startup functionality, use the function I provided previously. That function will run EVERY time the application loads, not just the first time. Let's flesh out the method with some additional functionality though;

               

              var dbFile = air.File.applicationDirectory.resolvePath('yoursqlite.db');

               

               

              function checkForDB () {

                   if (!dbFile.exists) {

                        var tmpDB = air.File.applicationDirectory.resolvePath('yoursqlite.db');

                        tmpDB.copyTo(dbFile, true);

                   } else {

                        // if the db DOES exist, then let's query for the history

                        // and fire it back up the event chain

                        this.getHistory();

                   }

              }

               

              The extra lines check for the empty db you've included in your app, and COPY it to the AppStorageDirectory. So essentially if the db exists in the AppStorageDirectory then you''ll know that the app has been run at least once and you can assume that certain records exist.

               

              If you'd like to see a JS AIR application, check out my Shrinkadoo project. Download and install it, then browse to the Program Files, or Applications, directory on your computer and check out the source. Feel free to poke around and gather whatever knowledge you're looking for.

               

              http://www.andymatthews.net/code/Shrinkadoo/

              • 4. Re: How to update an existing SQLite .db file?
                tux- Level 1

                Ah, OK, so basically the .db in the application directory itself is more like a dummy then?

                 

                Now I am on Windows 7 without UAC, so "application directory" is the folder where I put the .db file anyway... so I can't check for its existance because it "exists" when I install TinyTodo. Probably the "tmpDB" should be the "empty" db file then?

                • 5. Re: How to update an existing SQLite .db file?
                  tux- Level 1

                  This:

                   

                          if (!dbFile.exists) {
                              var tmpDB = air.File.applicationDirectory.resolvePath('dummy.db');
                              tmpDB.copyTo(dbFile, true);
                          }

                   

                  ... leads to a "fileWriteResource error"...so something's weird...?

                  • 6. Re: How to update an existing SQLite .db file?
                    Hasan Otuome Level 1

                    You can avoid write errors on the application directory (read-only) by using

                    an absolute path instead (ie, app:/path/to/dummy.db)...

                     

                    P E A C E

                     

                    Hasan

                    • 7. Re: How to update an existing SQLite .db file?
                      tux- Level 1

                      Usage of app:/dummy.db still gives me the fileWrite error...

                      • 8. Re: How to update an existing SQLite .db file?
                        commadelimited Level 2

                        Not really a dummy file. It's the original file that gets copied into the appStorage directory.

                         

                        The install directory, appDirectory, should contain the src db. This db gets copied into appStorageDirectory the first time the app runs. If you had sufficient permissions to install the app, you shouldn't get perm errors running it.

                        • 9. Re: How to update an existing SQLite .db file?
                          Hasan Otuome Level 1

                          Sorry for not being clearer. Writing to applicationDirectory @ runtime is

                          basically a no-no so you should instead write to

                          applicationStorageDirectory.

                           

                          Reading from applicationDirectory @ runtime is fine but writing can be

                          problematic so best-practice is to use applicationStorageDirectory

                          instead...

                           

                          e.g.

                          var dbFile =

                          air.File.applicationStorageDirectory.resolvePath('yoursqlite.db');

                           

                           

                          function checkForDB () {

                                if (!dbFile.exists) {

                                     var tmpDB = air.File.applicationStorageDirectory

                          .resolvePath('yoursqlite.db');

                                     tmpDB.copyTo(dbFile, true);

                               } else {

                                     // if the db DOES exist, then let's query for the history

                                     // and fire it back up the event chain

                                     this.getHistory();

                               }

                          }

                           

                           

                           

                          P E A C E

                           

                          Hasan

                          • 10. Re: How to update an existing SQLite .db file?
                            commadelimited Level 2

                            Oops. That was my fault. S'what I get for copying and pasting code. The dbFile reference should point to applicationStorageDirectory. Thanks Hasan,

                            • 11. Re: How to update an existing SQLite .db file?
                              tux- Level 1

                              Sounds reasonable. Anyway, with applicationStorageDirectory the error is still there.