Hi, I am trying to get sqlite working on a freertos/fatfs based STM32 embedded system. I started from the 3.24.0 amalgamation an did the steps in https://www.sqlite.org/custombuild.html. The VFS is based on the https://www.sqlite.org/src/doc/trunk/src/test_demovfs.c code (though I did implement the truncate method).
Compilation flags are: SQLITE_MUTEX_APPDEF=1 SQLITE_OS_OTHER=1 SQLITE_OMIT_WAL=1 SQLITE_TEMP_STORE=3 SQLITE_ENABLE_8_3_NAMES=2 I am running into the problem that when I try to create a table using sql ="CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" ;/* Create SQL statement */ rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);/* Execute SQL statement */ (full sample code below) I get a SQLITE_NOTADB. Searching the mailing list archive there was a similar post a few years ago (https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg96723.html), but in that case it was a problem with the journal file it seems (https://stackoverflow.com/questions/35738578/sqlite-porting-on-freertos-with-stm32). In my case demoOpen is called only once for the main database and that one is in read/write mode so I am encountering a different problem it seems. Looking with a debugger the stack trace when it goes wrong is: sqlite3_exec sqlite3_step sqlite3Step sqlite3VdbeExec sqlite3BtreeBeginTrans lockBtree He reads a page from the file in sqlite3PagerSharedLock, but the file is still 0 so the page is all zeros. pPage1->aData points to a buffer with all 0's sqlite3PagerPagecount(pBt->pPager, &nPageFile); //sets nPageFile to 1 if( nPage==0 || memcmp(24+(u8*)pPage1->aData, 92+(u8*)pPage1->aData,4)!=0 ){ nPage = nPageFile; } // changes nPage from 0 to 1 if( (pBt->db->flags & SQLITE_ResetDatabase)!=0 ){ nPage = 0; } // Since pBt->db->flags = 0x00048060 and SQLITE_ResetDatabase = 0x02000000 , this keeps nPage as 1 So it triggers the SQLITE_NOTADB case if( nPage>0 ) { u32 pageSize; u32 usableSize; u8 *page1 = pPage1->aData; rc = SQLITE_NOTADB; /* EVIDENCE-OF: R-43737-39999 Every valid SQLite database file begins ** with the following 16 bytes (in hex): 53 51 4c 69 74 65 20 66 6f 72 6d ** 61 74 20 33 00. */ if( memcmp(page1, zMagicHeader, 16)!=0 ) { goto page1_init_failed; } So I have been trying to figure out how the magic header should end up in the database file. I does not seem to happen when the database opened in the sqlite3_open_v2 call. That just opens/creates the file in RW mode, but does not yet write any data to it (behaviour on a windows PC seems to same so I guess this is OK) Writing the magic header seems to be done in a function static int newDatabase(BtShared *pBt) And I am assuming this function should be called from sqlite3BtreeBeginTrans in a part of the code that looks like: pBt->btsFlags &= ~BTS_INITIALLY_EMPTY; if( pBt->nPage==0 ) pBt->btsFlags |= BTS_INITIALLY_EMPTY; do { /* Call lockBtree() until either pBt->pPage1 is populated or ** lockBtree() returns something other than SQLITE_OK. lockBtree() ** may return SQLITE_OK but leave pBt->pPage1 set to 0 if after ** reading page 1 it discovers that the page-size of the database ** file is not pBt->pageSize. In this case lockBtree() will update ** pBt->pageSize to the page-size of the file on disk. */ while( pBt->pPage1==0 && SQLITE_OK==(rc = lockBtree(pBt)) ); if( rc==SQLITE_OK && wrflag ){ if( (pBt->btsFlags & BTS_READ_ONLY)!=0 ){ rc = SQLITE_READONLY; }else{ rc = sqlite3PagerBegin(pBt->pPager,wrflag>1,sqlite3TempInMemory(p->db)); if( rc==SQLITE_OK ){ rc = newDatabase(pBt); // <---------------------------------------------------------------------------------------------------HERE } } } if( rc!=SQLITE_OK ){ unlockBtreeIfUnused(pBt); } }while( (rc&0xFF)==SQLITE_BUSY && pBt->inTransaction==TRANS_NONE && btreeInvokeBusyHandler(pBt) ); but if I analyse code execution this statement is never executed in the cases I end up in sqlite3BtreeBeginTrans [1] First time I am there is with stack trace: sqlite3_exec sqlite3_prepare_v2 sqlite3LockAndPrepare sqlite3Prepare sqlite3RunParser sqlite3Parser yy_reduce sqlite3StartTable sqlite3ReadSchema sqlite3Init sqlite3InitOne sqlite3BtreeBeginTrans But in sqlite3InitOne, it is called with sqlite3BtreeBeginTrans(pDb->pBt, 0); so wrflag is 0, so the newDatabase part is not executed. [2] I get there a second time with stack trace: sqlite3_exec sqlite3_prepare_v2 sqlite3LockAndPrepare sqlite3Prepare sqlite3RunParser sqlite3Parser yy_reduce sqlite3StartTable sqlite3ReadSchema sqlite3Init sqlite3InitOne sqlite3_exec sqlite3_step sqlite3Step sqlite3VdbeExec sqlite3BtreeBeginTrans(pBt, pOp->p2); //pOp->p2 = 0 again (pOp->opcode = 0x02) So since pOp->p2 is 0, again wrflag is 0, so the newDatabase part is not executed. And since if( p->inTrans==TRANS_WRITE || (p->inTrans==TRANS_READ && !wrflag) ){ goto trans_begun; } is true (p->inTrans = 0x1 (= TRANS_READ)) we make the jump over it already in the beginning. [3] Then the 3th time is the one where I get the error. sqlite3_exec sqlite3_step sqlite3Step sqlite3VdbeExec sqlite3BtreeBeginTrans The call is sqlite3BtreeBeginTrans(pBt, pOp->p2); with pOp->p2 = 1, pOp->opcode = 0x02 But now we get the error already in the lockBtree part of while( pBt->pPage1==0 && SQLITE_OK==(rc = lockBtree(pBt)) ); So again newDatabase will not be called because that happens only afterwards. The code I am using to test is the same as in the original thread I already refered to: { sqlite3 *db = NULL; char *zErrMsg = 0; int rc; char *sql; l_nIntermediateResult = e_RETURNVALUE_Success; if ( e_RETURNVALUE_Success == l_nIntermediateResult) { //mmm this is also done by sqlite3_os_init but it does not harm to do it twice i think rc = sqlite3_vfs_register(sqlite3_demovfs(), 1); /* Register a VFS */ if(rc != SQLITE_OK) { LOGGER_LOG_FATAL(LOG_CLASS, "Could not register sqlite vfs: %d", rc); l_nIntermediateResult = e_RETURNVALUE_Failure; } } if ( e_RETURNVALUE_Success == l_nIntermediateResult) { rc = sqlite3_open_v2("testsql.db", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE , "demo" );/* Create a SQL table */ if( rc ) { LOGGER_LOG_FATAL(LOG_CLASS, "Could not create table: %d", rc); l_nIntermediateResult = e_RETURNVALUE_Failure; } } #if 0 // I disabled disabling the journal but it makes no difference if ( e_RETURNVALUE_Success == l_nIntermediateResult) { sql = "PRAGMA journal_mode=OFF";/* Create SQL statement */ rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);/* Execute SQL statement */ if( rc != SQLITE_OK ) { sqlite3_free(zErrMsg); LOGGER_LOG_FATAL(LOG_CLASS, "Could not execute journal mode: %d", rc); l_nIntermediateResult = e_RETURNVALUE_Failure; } } #endif if ( e_RETURNVALUE_Success == l_nIntermediateResult) { sql ="CREATE TABLE Cars(Id INT, Name TEXT, Price INT);" ;/* Create SQL statement */ rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);/* Execute SQL statement */ if( rc != SQLITE_OK ) { sqlite3_free(zErrMsg); LOGGER_LOG_FATAL(LOG_CLASS, "Could not execute create table: %d", rc); l_nIntermediateResult = e_RETURNVALUE_Failure; } } if ( e_RETURNVALUE_Success == l_nIntermediateResult) { sql = "INSERT INTO Cars VALUES(1, 'Audi', 52642);"/* Create SQL statement */ "INSERT INTO Cars VALUES(2, 'Skoda', 9000);"; rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);/* Execute SQL statement */ if( rc != SQLITE_OK ) { sqlite3_free(zErrMsg); LOGGER_LOG_FATAL(LOG_CLASS, "Could not execute insert: %d", rc); l_nIntermediateResult = e_RETURNVALUE_Failure; } } if ( e_RETURNVALUE_Success == l_nIntermediateResult ) { sql = "SELECT * from Cars";/* Create SQL statement */ const char* data = "Callback function called"; rc = sqlite3_exec(db, sql, lclSqlCallback, (void *)data, &zErrMsg);/* Execute SQL statement */ if( rc != SQLITE_OK ) { sqlite3_free(zErrMsg); LOGGER_LOG_FATAL(LOG_CLASS, "Could not execute select: %d", rc); l_nIntermediateResult = e_RETURNVALUE_Failure; } } if ( e_RETURNVALUE_Success == l_nIntermediateResult ) { sqlite3_close(db); } } Any suggestions about what could be going wrong (or when this magic header should be written ) Thank you Bram Peeters _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users