On Mon, Sep 2, 2013 at 4:47 PM, Michael Foss <foss.m...@gmail.com> wrote:
> Hello, > > I am using SQLite on an embedded system that the user will power off at any > time (i.e. there is never a clean shut down). The system contains sensors > and uses a C program with the SQLite library. The sensor data is saved to > an in-memory database every second. Every 10 seconds, the program saves any > new data to a database on disk, a USB flash drive. > > Because of the nature of the system usage, it is likely that SQLite will be > in the middle of a transaction when the system loses power. This leaves > behind a hot journal file with the "-journal" extension. When the system > starts up, I would like to browse through all the files in the USB disk and > find "-journal" files so that the program can roll back the final > transaction that occurred when the system was powered off. I was under the > impression (http://www.sqlite.org/tempfiles.html) that all I needed to do > was open the base file (without the "-journal" extension) with > sqlite3_open_v2() and then close the database in order to clean up the hot > journal. However, this does not appear to work the way I expected it to. > The "-journal" file remains. > sqlite3_open() and sqlite3_open_v2() defer the actual opening of the database file until you do something with the database. This is to give you the opportunity to issue PRAGMA statements that might influence the opening process. Running a very simple statement like SELECT 1 FROM sqlite_master LIMIT 1; should be sufficient to get SQLite to actually open the database file and rollback the hot journal. > > Pasted below is the function (clean_tmp_files()) that I created to help me > with this task. Given a directory as an argument, the function finds all > the "-journal" files and tries to open and close the underlying database. > You can treat the log_debug() and log_verbose() functions as if they are > printf(). These are just part of my program's logging mechanism. I am using > SQLite version 3.7.16.2. > > #include <glob.h> > #include <stdio.h> > #include "sqlite3.h" > > /** Returns true if the file specified by filename exists. */ > static bool file_exists(char *filename); > > /** Clean up the temporary database files stored in dir. These files > * are usually residual after an unclean shutdown, which happens all > * the time on embedded systems. */ > static void clean_tmp_files(const char *dir); > > <... other code in my .c file ...> > > static void clean_tmp_files(const char *dir) > { > char match_str[1024]; > char base_db_str[1024]; > glob_t globber; > > snprintf(match_str, 1024, "%s/%s", dir, "*-journal"); > > if (glob(match_str, 0, NULL, &globber) == 0) > { > unsigned int i; > for (i=0; i<globber.gl_pathc; i++) > { > log_debug("Cleaning up database temporary file %s\n", > globber.gl_pathv[i]); > > snprintf(base_db_str, strlen(globber.gl_pathv[i]) - > strlen("-journal") + 1, globber.gl_pathv[i]); > log_verbose("Want to clean it up by opening and closing %s\n", > base_db_str); > > if (file_exists(base_db_str)) > { > sqlite3 *db; > int err; > > log_debug("Opening and closing %s\n", base_db_str); > err = sqlite3_open_v2(base_db_str, &db, > SQLITE_OPEN_READWRITE, NULL); > if (err < 0) > { > log_error("%s Sqlite open error: %s\n", > __FUNCTION__, sqlite3_errstr(err)); > } > sqlite3_close(db); > } > else > { > log_warn("No base db file exists for %s. Deleting the > temporary file outright.\n", > globber.gl_pathv[i]); > remove(globber.gl_pathv[i]); > } > } > } > globfree(&globber); > } > > static bool file_exists(char *filename) > { > bool ret = false; > FILE *test = fopen(filename, "r"); > if (test != NULL) > { > ret = true; > fclose(test); > } > > return ret; > } > > I appreciate any help that you can offer. And kudos to the developers for > this tool--it is amazing!! > > Thank you, > Mike Foss > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
