Clovertech

Sqlite database management best practices

› Clovertech Forums › Read Only Archives › Cloverleaf › Cloverleaf › Sqlite database management best practices

  • Creator
    Topic
  • October 2, 2015 at 2:54 pm #54839
    Mike Strout
    Participant

      We are creating a process that writes IN* and GT1 segments for all incoming SIU messages on an incoming scheduling feed. We expect to see a few hundred rows per day added to this DB and we will save these rows for about 60 working days, which means the DB will contain about 12,000 rows.

      I think there are some great arguments for using a Sqlite DB rather than adding the complexity of connecting via ODBC to an MSSql DB. Before I finalize my plans, I would like to hear what people with mature Sqlite solutions do to protect that data from loss or corruption.

      For example, what do you do for backups of the data?

      Do you do anything special permissions-wise on the Sqlite files or directory in which they live?

      Are there any special strategies you employ to prevent corruption?

    • Creator
      Topic
    Viewing 1 reply thread
    • Author
      Replies
      • October 2, 2015 at 3:19 pm #83175
        James Cobane
        Participant

          Hi Mike,

          We have an instance of a simple SQLite database that we use that contains a list of self-pay patients that is updated daily.  We use this to bounce other transactions against to filter for one of our interfaces.  We don’t do anything special in terms of backup; it is within the file system that currently gets backed up, so it gets backed up as part of that.  Other than that, the only thing we do is a monthly prune and delete any records that are older than 90 days.  This isn’t “mission critical”, so we aren’t really doing anything in addition to the daily backup.

          Jim Cobane

          Henry Ford Health

        • October 3, 2015 at 1:43 pm #83176
          Jeff Dinsmore
          Participant

            Like Mike, we rely on daily file system backup to protect our SQLite databases.

            We also run our engines on VMWare, so there’s pretty good protection against hardware failure.

            In my dozen or so years experience with SQLite on Linux and Windows – both virtual and physical – I can recall only one instance of a corrupt DB file.

            Jeff Dinsmore
            Chesapeake Regional Healthcare

        • Author
          Replies
        Viewing 1 reply thread
        • The forum ‘Cloverleaf’ is closed to new topics and replies.