sqlite3的列类型 [英] sqlite3 columns types
问题描述
我开始有将数据从 XML
转换为分贝
新项目。 XML的
都有自己的格式,不能用于填充分贝
。
我选择使用 sqlite的
,因为它是一个嵌入式平台,我需要一个轻量级的库。
我的列类型挣扎。
我写了下面的SQL语句:
静态INT回调(无效* NOTUSED,INT ARGC,字符** argv的,字符** azColName)
{
INT I;
对于(i = 0; I< ARGC,我++){
的printf(%S =%S \\ N,azColName [I]的argv [I]的argv [Ⅰ]:NULL);
}
的printf(\\ n);
返回0;
}静态布尔exec_sql(sqlite3的*分贝,字符* SQL,布尔use_callback)
{
INT RC;
字符* zErrMsg = 0; //执行SQL语句
如果(use_callback ==真)
{
RC = sqlite3_exec(DB,SQL,回调,0,&安培; zErrMsg);
}
其他
{
RC = sqlite3_exec(DB,SQL,NULL,0,&安培; zErrMsg);
} 如果(RC!= SQLITE_OK)
{
fprintf中(标准错误,SQL:%s的失败!\\ n错误:%S \\ n,SQL,zErrMsg);
sqlite3_free(zErrMsg);
返回false;
}
返回true;
}INT主(INT ARGC,CHAR *的argv [])
{
xmlDoc中*文档;
*的xmlNode root_element;
FILE * FW;
INT I,srcidx,next_start; sqlite3的*分贝;
字符* zErrMsg = 0;
INT RC;
字符* SQL; RC = sqlite3_open(Configuration.db,&安培; DB); 如果(RC)
{
XML2DB_DEBUG_ERR(无法打开数据库:%S \\ n,sqlite3_errmsg(DB)); }
其他
{
XML2DB_DEBUG_INFO(打开的数据库成功的\\ n);
} SQL =CREATE TABLE表2(\\
ID INT NOT NULL PRIMARY KEY,\\
TABLE_1_ID INT FOREGN键引用表1(ID)NOT NULL,\\
COLUMN_1 INT [512] NOT NULL,\\
COLUMN_2文本NOT NULL,\\
COLUMN_3布尔默认为false);; //执行SQL语句
exec_sql(DB,SQL,FALSE); SQL =INSERT INTO模型(ID,TABLE_1_ID,COLUMN_1,COLUMN_2,COLUMN_3)\\
值(1,1,{1,2,3,4,5}','布拉布拉',TRUE);; //执行SQL语句
exec_sql(DB,SQL,真正的); SQL =SELECT * FROM表2;; //执行SQL语句
exec_sql(DB,SQL,真正的); sqlite3_close(DB); 返回0;
}
这code效果很好, ,但现在我发现的sqlite3
不支持布尔
和阵列
的数据类型。的
我写的,效果很好,因为一个 SELECT
和 DBVisualizer中
可显示数据并没有给我的错误。
所以,问题是:
- 为什么SQL被执行时以及即使数据类型,不支持并插入值不正确?的
- *有一个特点,可以使始终检查插入的值以及格式化? *
SQLite的使用动态类型
要检查数据类型,使用 CHECK约束的:
CREATE TABLE表2(
ID INTEGER PRIMARY KEY,
TABLE_1_ID INT外键引用表1(ID)NOT NULL,
COLUMN_1 INT [512] NOT NULL
COLUMN_2文本NOT NULL CHECK(typeof运算(COLUMN_2)=文本),
COLUMN_3布尔值默认值0 CHECK(COLUMN_3 IN(0,1))
);
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
.
I choose to use sqlite
, because it is an embedded platform and I need a lightweight library.
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\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
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!!!\nError: %s\n", 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\n", sqlite3_errmsg(db));
}
else
{
XML2DB_DEBUG_INFO("Opened database successfully\n");
}
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;
}
That code works well, but now I found out that sqlite3
does not support boolean
and array
datatypes.
I wrote, "it works well", because of a SELECT
and DBVisualizer
can display data and do not give me errors.
So, the questions are:
- Why sql are executes well even if datatype are not supported and inserted values are not correct?
- *Is there a feature that can be enable to always check that values of insert are well formatted? *
SQLite uses dynamic typing.
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))
);
这篇关于sqlite3的列类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!