-3

I'm using sqlite database for storing my data, a application which keep writing to the database non-stop. (example windows perf data every 5 minutes)

What I need,

  1. get all the data incremental every 5 minutes and move to some log/text file
  2. delete that piece of data which successfully move to log/text file from the sqlite db.

I have all below 3 files generated while I'm writing data to db.

  1. MyData.db
  2. MyData.db-shm
  3. MyData.db-wal

I know may be based of last event insert time, query the data, process and delete, store last time in some place and next time read from that point onward.

I there a way that I can copy the Wal file in a incremental way every 5 minute to skip read/delete operation through C#?

I'm also open to change the database like Maria, etc, if other database provide this kind of solution, etc. Please suggest.

user584018
  • 10,186
  • 15
  • 74
  • 160
  • How to back up a sqlite database that is currently in use: https://www.sqlite.org/backup.html (just blindly copying files is a bad idea) – Shawn Jan 02 '19 at 06:45
  • You'd need the [session extension](https://www.sqlite.org/sessionintro.html). This is probably not available with your C# driver. – CL. Jan 02 '19 at 15:37

1 Answers1

1

Why have a database involved at all? Especially if you remove the data from it minutes after inserting it?

Plan A: Append to a file. Have some daily task to 'cycle' the file.

Plan B: Store all the data 'forever' in MariaDB. Suggest using PARTITIONs to facilitate purging "old" data. For example, with ~32 partitions, you could have daily partitions. Each night DROP the oldest partition and REORGANIZE to get a new partition.

Rick James
  • 135,179
  • 13
  • 127
  • 222