sqlite3 列类型

2022-01-25 00:00:00 sql c embedded sqlite

我正在开始一个新项目，它必须将数据从 XML 转换为 db.XMLs 有自己的格式，不能用来填充 db.

I'm starting a new project that has to convert data from XML to db. XMLs have their own format and cannot be used to fill a db.

我选择使用sqlite，因为它是一个嵌入式平台，我需要一个轻量级的库.

I choose to use sqlite, because it is an embedded platform and I need a lightweight library.

我正在为列类型而苦苦挣扎.我写了下面的sql:

I'm struggling with columns types. I wrote the sql below:

static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
   int i;
   for(i=0; i<argc; i++){
      printf("%s = %s
", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("
");
   return 0;
}

static bool exec_sql (sqlite3 *db, char *sql, bool use_callback)
{
    int rc;
    char *zErrMsg = 0;

    // Execute SQL statement
    if (use_callback == true)
    {
        rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
    }
    else
    {
        rc = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    }

    if( rc != SQLITE_OK )
    {
        fprintf(stderr, "SQL: %s FAIL!!!
Error: %s
", sql, zErrMsg);
        sqlite3_free(zErrMsg);
        return false;
    }
    return true;
}

int main ( int argc, char *argv[] )
{
    xmlDoc *doc;
    xmlNode *root_element;
    FILE *fw;
    int i, srcidx, next_start;

    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;
    char *sql;

    rc = sqlite3_open("Configuration.db", &db);

    if( rc )
    {
        XML2DB_DEBUG_ERR("Can't open database: %s
", sqlite3_errmsg(db));

    }
    else
    {
        XML2DB_DEBUG_INFO("Opened database successfully
");
    }

    sql = "CREATE TABLE Table2("  
         "ID          INT      NOT NULL PRIMARY KEY," 
         "TABLE_1_ID  INT      FOREGN KEY REFERENCES Table1(ID) NOT NULL ,"  
         "COLUMN_1    INT[512] NOT NULL,"
         "COLUMN_2    TEXT     NOT NULL,"
         "COLUMN_3    BOOLEAN  DEFAULT FALSE);";

    // Execute SQL statement
    exec_sql(db, sql, false);

    sql = "INSERT INTO Models (ID, TABLE_1_ID, COLUMN_1, COLUMN_2, COLUMN_3) "
          "VALUES (1, 1, '{1,2,3,4,5}', 'blabla', TRUE); ";

    // Execute SQL statement
    exec_sql(db, sql,true);

    sql = "SELECT * FROM Table2;";

    // Execute SQL statement
    exec_sql(db, sql, true);

    sqlite3_close(db);

    return 0;
}

该代码运行良好，但现在我发现 sqlite3 不支持 boolean 和 array 数据类型.

That code works well, but now I found out that sqlite3 does not support boolean and array datatypes.

我写道，它运行良好"，因为 SELECT 和 DBVisualizer 可以显示数据并且不会给我错误.

I wrote, "it works well", because of a SELECT and DBVisualizer can display data and do not give me errors.

所以，问题是:

  1. 为什么即使数据类型不受支持且插入的值不正确，sql 也能很好地执行?
  2. *是否有可以启用始终检查插入值是否格式正确的功能?*

推荐答案

SQLite 使用 动态类型.

SQLite uses dynamic typing.

要检查数据类型，请使用 CHECK 约束:

To check data types, use CHECK constraints:

CREATE TABLE Table2(
    ID          INTEGER  PRIMARY KEY,
    TABLE_1_ID  INT      FOREIGN KEY REFERENCES Table1(ID) NOT NULL,
    COLUMN_1    INT[512] NOT NULL
    COLUMN_2    TEXT     NOT NULL   CHECK (typeof(COLUMN_2) = 'text'),
    COLUMN_3    BOOLEAN  DEFAULT 0  CHECK (COLUMN_3 IN (0, 1))
);

相关文章