如何检测sqlite3是否创建了数据库文件? [英] How do I detect if sqlite3 created a database file?

问题描述

我正在编写一个使用sqlite3数据库文件存储其数据的程序.如果我使用

I'm writing a program that uses a sqlite3 database file to store its data. If I open a database file with

sqlite3_open_v2(filename, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL)

如果数据库文件不存在，则会创建该文件.如何在打开数据库文件之前先确定它是否存在? sqlite3 shell使用如下代码:

the database file is created if it does not exist. How can I find out if the database file existed prior to opening it? The sqlite3 shell uses code like this:

  /* Go ahead and open the database file if it already exists.  If the
  ** file does not exist, delay opening it.  This prevents empty database
  ** files from being created if a user mistypes the database name argument
  ** to the sqlite command-line tool.
  */
  if( access(data.zDbFilename, 0)==0 ){
    open_db(&data, 0);
  }

但是，当在access调用之后和open_db调用之前(检查时间与使用时间)由另一个进程创建数据库文件时，此代码具有竞争条件.

This code however has a race condition when the database file is created by another process after the access call and before the open_db call (time-of-check vs. time-of-use).

另一个答案(我现在找不到)建议检查application_id和user_version字段.如果它们为零，则仅创建一个数据库.我研究了这种方法，发现许多应用程序实际上都不用在新创建的数据库中设置这些字段，因此这种方法充其量是模糊的，我认为它不能解决我的问题.

Another answer (that I can't find right now) suggests to check the application_id and user_version fields. If they are zero, a database was just created. I researched this approach and found that numerous applications actually don't bother to set these fields in newly created databases, so this approach is fuzzy at best and I don't think it solves my problem.

是否有一种方法可以在打开数据库之前查找不存在这种竞争条件的数据库?如果我只能找出sqlite3是否初始化了数据库文件(例如，用sqlite3标头填充了截断的文件)，那也是可以接受的.

Is there a method to find out if the database existed prior to opening it that does not involve a race condition like this? It is also acceptable if I can merely find out if the database file was initialized (as in, a truncated file was populated with a sqlite3 header) by sqlite3.

拥有这样一个例程的目的是能够找出我是否需要创建数据库中需要的所有表.我不想意外地覆盖由其他应用程序放置在该文件中的另一个文件.

The purpose of having such a routine is to be able to find out if I need to create all the tables I need in the database. I don't want to accidentally overwrite another file placed there by a different application.

可以在下面的bash脚本中找到问题的简化说明.它模拟了两个应用程序.它们都以类似的方式工作:

A simplified illustration of the problem can be found in the following bash script. It simulates two applications. They both work in similar ways:

1. 创建或打开数据库test.db
2. 如果该数据库以前不存在，请创建一个表test并在其中写入一行.对于第一个应用程序，该值为1，对于第二个应用程序，该值为2.
3. 打印数据库的内容.

1. Create or open a database test.db
2. If the database did not exist before, create a table test and write a single row into it. The value is 1 for the first application and 2 for the second.
3. Print the contents of the database.

这是代码:

#!/bin/sh

# sleeps are inserted to make the race conditions easier to trigger

application() {
    echo Checking if database exists...
    [ ! -f test.db ]
    status=$?
    sleep 2

    if (exit $status)
    then
        echo Database not found, making tables
        sqlite3 test.db "CREATE TABLE IF NOT EXISTS test (a);"
        sleep 2
        echo Writing initial record into database
        sqlite3 test.db "INSERT INTO test VALUES ($1);"
        sleep 2
    else
        echo Database found, checking if it belongs to me
    fi

    echo Printing content of database
    sqlite3 test.db "SELECT * FROM test;"
}

rm -f test.db
echo First test: app1 and app1 race
application 1 & (sleep 1 ; application 1)

rm -f test.db
echo Second test: app2 and app1 race
application 2 & (sleep 1 ; application 1)

我的目标是确保永远不会发生以下情况:

My goal is to ensure that the following cases can never happen:

• 应用程序的一个实例打开数据库文件，得出结论认为该文件没有初始化，并且初始化了该文件(通过创建表并写入初始记录)，即使该文件已经包含来自同一应用程序的不同实例或来自其他应用程序的数据.一个不同的应用程序.
• 写入属于另一个应用程序的数据库文件.

如果正确编程了application，则每次运行只会在之前未初始化的情况下初始化数据库.因此，您将看到的唯一输出是仅包含行1的数据库或仅包含行2的数据库.

If application was programmed correctly, each run would only initialize the database if it wasn't initialized before. Thus, the only output you would see is either a database containing only the row 1 or a database that contains only the row 2.

推荐答案

不可能将新创建的数据库与之前创建的空数据库区分开.

It is not possible to differentiate a newly-created database from an empty database that was created earlier.

但是，一个空数据库是唯一出现此问题的数据库. 通过检查sqlite_master表是否不为空，可以检测到其他任何数据库.在事务内部执行此操作并创建表，并且没有竞争条件.

However, an empty database is the only one with this problem. Any other database can be detected by checking if the sqlite_master table is not empty. Do this and your table creation inside a transaction, and there is no race condition.

如果您第一次写入数据库(即实际创建文件的时间)是在设置 application_id ，那么您就会知道具有另一个ID的任何文件都不属于您. (注册的应用程序ID是唯一的.)

If your first write to the database (which is when the file is actually created) is setting the application_id, then you know that any file with another ID is not yours. (Registered application IDs are unique.)

这篇关于如何检测sqlite3是否创建了数据库文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！