Hello.
I'm developing a system for massive point cloud rendering and use SQLite in
order to store nodes of a spatial subdivision tree of the space where the
points lie in, aka Octree. For this, I have a very simple table with just a
key, which is an integer identifier of the node, aka Morton Code, and a
blob, which is the node itself. The system uses the table to store nodes
whenever a main memory budget is reached, in order to deal with point
clouds of arbitrary size.
My problem is that even if I can correctly insert the nodes into the table
(have checked that by querying the database after insertion), it seems that
they are deleted moments after, for an unknown reason. The database
requirements for the project are peak and correct performance in a
multithreaded environment, without any care about rollbacks, since all
transactions are done by the system itself and by now the database is
meaningless after system exit. To manage database connections I have the
SQLiteManager class and the database setup is the following one:
template< typename Point, typename MortonCode, typename OctreeNode >
void SQLiteManager< Point, MortonCode, OctreeNode >::init( const
string& dbFileName, const bool deleteDbFileFlag )
{
if( deleteDbFileFlag )
{
deleteFile( dbFileName );
}
checkReturnCode(
sqlite3_open_v2( dbFileName.c_str(), &m_db,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX,
NULL ),
SQLITE_OK
);
sqlite3_exec( m_db, "PRAGMA synchronous = OFF", NULL, NULL, NULL );
sqlite3_exec( m_db, "PRAGMA journal_mode = OFF", NULL, NULL, NULL );
sqlite3_busy_handler( m_db,
[]( void*, int )
{
// Always try again to make the database access
return 1;
},
NULL
);
createTables();
createStmts();
}
In addition to init(), there is an API to inserted nodes and to create
transactions. The serialization and statement step, reset and return code
checking are already proven correct by automated tests .
template< typename Point, typename MortonCode, typename OctreeNode >
void SQLiteManager< Point, MortonCode, OctreeNode >::insertNode( const
MortonCode& morton, const OctreeNode& node )
{
byte* serialization;
size_t blobSize = node.serialize( &serialization );
checkReturnCode( sqlite3_bind_int64( m_nodeInsertion, 1,
morton.getBits() ), SQLITE_OK );
checkReturnCode( sqlite3_bind_blob( m_nodeInsertion, 2,
serialization, blobSize, SQLITE_STATIC ), SQLITE_OK );
safeStep( m_nodeInsertion );
safeReset( m_nodeInsertion );
delete[] serialization;
}
template< typename Point, typename MortonCode, typename OctreeNode >
void SQLiteManager< Point, MortonCode, OctreeNode >::beginTransaction()
{
safeStep( m_beginTransaction );
safeReset( m_beginTransaction );
}
template< typename Point, typename MortonCode, typename OctreeNode >
void SQLiteManager< Point, MortonCode, OctreeNode >::endTransaction()
{
safeStep( m_endTransaction );
safeReset( m_endTransaction );
}
The statements creation is done by this other method:
template< typename Point, typename MortonCode, typename OctreeNode >
void SQLiteManager< Point, MortonCode, OctreeNode >::createStmts()
{
safePrepare( "INSERT OR REPLACE INTO Nodes VALUES ( ?, ? );",
&m_nodeInsertion );
safePrepare( "SELECT Node FROM Nodes WHERE Morton = ?;",
&m_nodeQuery );
safePrepare( "SELECT Node FROM Nodes WHERE Morton BETWEEN ? AND
?;", &m_nodeIntervalQuery );
safePrepare( "SELECT * FROM Nodes;", &m_allIdNodesQuery );
safePrepare( "SELECT Morton, Node FROM Nodes WHERE Morton BETWEEN ?
AND ?;", &m_nodeIntervalIdQuery );
safePrepare( "DELETE FROM Nodes WHERE Morton BETWEEN ? AND ?;",
&m_nodeIntervalDeletion );
safePrepare( "BEGIN TRANSACTION", &m_beginTransaction );
safePrepare( "END TRANSACTION", &m_endTransaction );
}
This API is used by the HierarchyCreator class, which have an array of
SQLiteManager objects, each one used by a thread in the creation process.
// BEGIN PARALLEL WORKLIST PROCESSING.
#pragma omp parallel for
for( int i = 0; i < dispatchedThreads; ++i )
{
...
if( isReleasing )
{
m_dbs[ i ].beginTransaction();
}
... (Child nodes are processed, creating its parent
inner as result) ...
releaseSiblings( inner.child(),
omp_get_thread_num(), m_octreeDim );
if( isReleasing )
{
m_dbs[ i ].endTransaction();
}
}
releaseSiblings() provides proper node persistence before deleting them
from main memory, as can be seen in the next snipet:
template< typename Morton, typename Point >
inline void HierarchyCreator< Morton, Point >
::releaseSiblings( NodeArray& siblings, const int threadIdx, const
OctreeDim& dim )
{
Sql& sql = m_dbs[ threadIdx ];
for( int i = 0; i < siblings.size(); ++i )
{
Node& sibling = siblings[ i ];
NodeArray& children = sibling.child();
if( !children.empty() )
{
releaseSiblings( children, threadIdx, OctreeDim( dim,
dim.m_nodeLvl + 1 ) );
}
Morton siblingMorton = dim.calcMorton( sibling );
// Persisting node
sql.insertNode( siblingMorton, sibling );
}
siblings.clear();
}
After adding some debug instructions in the code above, I could see that
released nodes are in fact being inserted into the database, but after a
few more iterations of the hierarchy construction algorithm, they were
disappearing for a reason that I couldn't understand yet. The only
SQLiteManager API used by the hierarchy construction code are the ones
shown above, without any drop or delete statement being issued in the
process. I suspect that It can be something related with SQLite cache, but
I couldn't find any clues about the real reason.
--
Thanks in advance for your time and sugestions,
Vin?cius da Silva,
Ph. D. Student.
PESC <http://www.cos.ufrj.br> - UFRJ <http://www.ufrj.br>
