[odb-users] Runtime create_schema failure
Ciccio Pasticcio
dodungocorporation at gmail.com
Wed Jul 18 06:25:34 EDT 2018
Hi,
I'm working on an embedded application that use ODB 2.3.0 in combination
with a sqlite database.
This application has different tables, divided into different schemas.
Everythings works well, except one schema with one table:
Decoupling class (isolate ID from the actual object) (file "Status.h")
#pragma db model version(1, 6)
#ifdef PERSISTENCE
#pragma db table("Status") // <-- Not sure about this line! IMO it's
useless
#endif
class Status : public StatusEntity
{
public:
......
unsigned long id;
};
#pragma db object( Status )
#pragma db member(Status::id ) id auto
---------------------------------------
The actual object (file "StatusEntity.h")
#pragma db object( StatusEntity ) abstract
class StatusEntity
{
public:
.....
SystemStatus system { SS_UNKNOWN };
UserStatus user { US_UNKNOWN };
boost::posix_time::ptime timestamp {
boost::posix_time::microsec_clock::universal_time() };
int odometer { -1 };
int distance { -1 };
int missionOdometer { -1 };
int progressIdC2D { 0 };
int progressIdD2C { -1 };
ChargingStatus charging { CS_UNKNOWN };
};
---------------------------------------
Status class is compiled with the following commands:
odb -D PERSISTENCE $INCLUDE_DIRS -o $DESTINATION_DIR -d sqlite --std c++11
--changelog-dir $CHANGELOG_DIR --generate-query --schema-name status
--generate-schema --profile boost *StatusEntity.h*
odb -D PERSISTENCE $INCLUDE_DIRS -o $DESTINATION_DIR -d sqlite --std c++11
--changelog-dir $CHANGELOG_DIR --generate-query --schema-name status
--generate-schema --schema-format separate --schema-format sql --profile
boost --hxx-prologue "#include \"StatusEntity.h\"" *Status.h*
NB: there are around 15 more classes that are *used in the same way.*
When the application starts, it checks for the db:
std::vector<std::string> schemas{......, "status", ......};
for ( auto name : schemas )
{
if ( !isSchemaPresent( name ) )
{
setSQLitePragmas();
createSchema( name );
}
else
{
migrate( name );
}
}
When is *status *turn, the *create_schema *fails: (I added some logs at ODB
level)
2018-07-18 09:45:32,283 [INFO ] CCS-DB : Verifying the schema
presence for [status]
2018-07-18 09:45:32,287 [INFO ] CCS-DBT : EXECUTE: SELECT "version",
"migration" FROM "schema_version" WHERE "name" = ?
2018-07-18 09:45:32,292 [INFO ] CCS-DB : Schema for [status] is not
present.
2018-07-18 09:45:32,293 [INFO ] CCS-DBT : EXECUTE: BEGIN
2018-07-18 09:45:32,294 [INFO ] CCS-DBT : EXECUTE: PRAGMA
foreign_keys = ON
2018-07-18 09:45:32,295 [INFO ] CCS-DBT : EXECUTE: PRAGMA
auto_vacuum = 1
2018-07-18 09:45:32,301 [INFO ] CCS-DBT : EXECUTE: COMMIT
2018-07-18 09:45:32,309 [INFO ] CCS-DB : Creating database schema
for [status]
2018-07-18 09:45:32,311 [INFO ] CCS-DBT : EXECUTE: BEGIN
2018-07-18 09:45:32,312 [DEBUG] CCS-libodb : create_schema() + Name:
status Drop: 1
2018-07-18 09:45:32,313 [DEBUG] CCS-libodb : drop_schema() + name:
status
2018-07-18 09:45:32,314 [WARN ] CCS-odb : create_schema() db: 0,
pass: 1, drop: 1
2018-07-18 09:45:32,315 [INFO ] CCS-DBT : EXECUTE: DROP TABLE IF
EXISTS "Status"
2018-07-18 09:45:32,316 [INFO ] CCS-DBT : EXECUTE: CREATE TABLE IF
NOT EXISTS "schema_version" (#012 "name" TEXT NOT NULL PRIMARY KEY,#012
"version" INTEGER NOT NULL,#012 "migration" INTEGER NOT NULL)
2018-07-18 09:45:32,317 [INFO ] CCS-DBT : EXECUTE: DELETE FROM
"schema_version"#012 WHERE "name" = 'status'
2018-07-18 09:45:32,318 [WARN ] CCS-odb : create_schema() db: 0,
pass: 2, drop: 1
2018-07-18 09:45:32,319 [INFO ] CCS-DBT : EXECUTE: DROP TABLE IF
EXISTS "Status"
2018-07-18 09:45:32,321 [INFO ] CCS-DBT : EXECUTE: CREATE TABLE IF
NOT EXISTS "schema_version" (#012 "name" TEXT NOT NULL PRIMARY KEY,#012
"version" INTEGER NOT NULL,#012 "migration" INTEGER NOT NULL)
2018-07-18 09:45:32,322 [INFO ] CCS-DBT : EXECUTE: DELETE FROM
"schema_version"#012 WHERE "name" = 'status'
2018-07-18 09:45:32,323 [DEBUG] CCS-libodb : drop_schema() -
2018-07-18 09:45:32,323 [DEBUG] CCS-libodb : Pass: 1, Calling function
in pos 0
2018-07-18 09:45:32,325 [INFO ] CCS-DBT :* EXECUTE: CREATE TABLE
"Status" (#012 "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,#012
"value" INTEGER NOT NULL,#012 "description" TEXT NOT NULL)*
2018-07-18 09:45:32,328 [DEBUG] CCS-libodb : Done: 0
2018-07-18 09:45:32,329 [DEBUG] CCS-libodb : Pass: 1, Calling function
in pos 1
2018-07-18 09:45:32,330 [WARN ] CCS-odb : create_schema() db: 0,
pass: 1, drop: 0
2018-07-18 09:45:32,332 [WARN ] CCS-odb : Exec: CREATE TABLE
"Status" (#012 "system" INTEGER NOT NULL,#012 "user" INTEGER NOT
NULL,#012 "timestamp" TEXT NULL,#012 "odometer" INTEGER NOT NULL,#012
"distance" INTEGER NOT NULL,#012 "missionOdometer" INTEGER NOT NULL,#012
"progressIdC2D" INTEGER NOT NULL,#012 "progressIdD2C" INTEGER NOT
NULL,#012 "charging" INTEGER NOT NULL,#012 "id" INTEGER NOT NULL PRIMARY
KEY AUTOINCREMENT)
2018-07-18 09:45:32,334 [ERROR] CCS-odb : What: 1: table "Status"
already exists
2018-07-18 09:45:32,336 [INFO ] CCS-DBT : EXECUTE: ROLLBACK
2018-07-18 09:45:32,337 [ERROR] CCS-DB : Database error: [1: table
"Status" already exists]
To clarify:
- *DB *is the thread that manages the interaction with the database
- *DBT *is a custom tracer attached to ODB
- *libodb *is a log at ODB library level
- *odb *is a log inside the code generated by ODB
The logs are pretty clear to me, except the *bold *line:
2018-07-18 09:45:32,325 [INFO ] CCS-DBT : EXECUTE: CREATE TABLE
"Status" (#012 "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,#012
"value" INTEGER NOT NULL,#012 "description" TEXT NOT NULL)
There is no way I figured from where this statement come from. I don't even
know this kind of struct (id, value, description). Actually the table is
created and then, the actual table struct fail throwing the exception
"Table already exists"
2018-07-18 09:45:32,332 [WARN ] CCS-odb : Exec: CREATE TABLE "Status"
(#012 "system" INTEGER NOT NULL,#012 "user" INTEGER NOT NULL,#012
"timestamp" TEXT NULL,#012 "odometer" INTEGER NOT NULL,#012 "distance"
INTEGER NOT NULL,#012 "missionOdometer" INTEGER NOT NULL,#012
"progressIdC2D" INTEGER NOT NULL,#012 "progressIdD2C" INTEGER NOT
NULL,#012 "charging" INTEGER NOT NULL,#012 "id" INTEGER NOT NULL PRIMARY
KEY AUTOINCREMENT)
2018-07-18 09:45:32,334 [ERROR] CCS-odb : What: 1: table "Status"
already exists
and then it rolls back.
If i use a different schema name (ie "statust") everythings works fine. But
for legacy reason (and personal curiosity) I'd like to know why this
happens.
Thanks in advance,
Gabriele
More information about the odb-users
mailing list