Inheritance diagram for LayerTable:

Collaboration diagram for LayerTable:

Public Member Functions
	LayerTable (const MetadataRecord &meta, sqlite3 *db)
sqlite3_int64	getTableSize (sqlite3 *db)
int	getNbEntry (sqlite3 *db)
void	checkAndPurgeIfNeeded (sqlite3 *db, unsigned int maxSize)
bool	store (const ImageRecord &rec, sqlite3 *db)
bool	updateAccessTime (const TileKey &key, int newTimestamp, sqlite3 *db)
bool	updateAccessTimePool (const std::string &keyStr, int newTimestamp, sqlite3 *db)
bool	load (const TileKey &key, ImageRecord &output, sqlite3 *db)
void	displayStats ()
bool	purge (int utcTimeStamp, int maxToRemove, sqlite3 *db)
bool	initialize (sqlite3 *db)
Public Attributes
std::string	_selectSQL
std::string	_insertSQL
std::string	_updateTimeSQL
std::string	_updateTimePoolSQL
std::string	_purgeSelect
std::string	_purgeSQL
std::string	_purgeLimitSQL
MetadataRecord	_meta
std::string	_tableName
osg::ref_ptr< osgDB::ReaderWriter >	_rw
osg::ref_ptr < osgDB::ReaderWriter::Options >	_rwOptions
osg::Timer_t	_statsStartTimer
osg::Timer_t	_statsLastCheck
int	_statsLoaded
int	_statsStored
int	_statsDeleted

Detailed Description

Database table that holds one record per cached imagery tile in a layer. The layer name is the table name.

Definition at line 361 of file Sqlite3Cache.cpp.

Constructor & Destructor Documentation

LayerTable::LayerTable	(	const MetadataRecord &	meta,
		sqlite3 *	db
	)		`[inline]`

Definition at line 363 of file Sqlite3Cache.cpp.

        : _meta(meta)
    {        
        _tableName = "layer_" + _meta._layerName;
        // create the table and load the processors.
        if ( ! initialize( db ) )
        {
            return;
        }

        // initialize the SELECT statement for fetching records
        std::stringstream buf;
#ifdef SPLIT_DB_FILE
        buf << "SELECT created,accessed,size FROM \"" << tableName << "\" WHERE key = ?";
#else
        buf << "SELECT created,accessed,data FROM \"" << _tableName << "\" WHERE key = ?";
#endif
        _selectSQL = buf.str();

        // initialize the UPDATE statement for updating the timestamp of an accessed record
        buf.str("");
        buf << "UPDATE \"" << _tableName << "\" SET accessed = ? "
            << "WHERE key = ?";
        _updateTimeSQL = buf.str();

        buf.str("");
        buf << "UPDATE \"" << _tableName << "\" SET accessed = ? "
            << "WHERE key in ( ? )";
        _updateTimePoolSQL = buf.str();
        
        // initialize the INSERT statement for writing records.
        buf.str("");
        buf << "INSERT OR REPLACE INTO \"" << _tableName << "\" "
#ifdef SPLIT_DB_FILE
            << "(key,created,accessed,size) VALUES (?,?,?,?)";
#else
            << "(key,created,accessed,data) VALUES (?,?,?,?)";
#endif
        _insertSQL = buf.str();

        // initialize the DELETE statements for purging old records.
        buf.str("");
        buf << "DELETE FROM \"" << _tableName << "\" "
            << "INDEXED BY \"" << _tableName << "_lruindex\" "
            << "WHERE accessed < ?";
        _purgeSQL = buf.str();
        
        buf.str("");
        buf << "DELETE FROM \""  << _tableName << "\" WHERE key in (SELECT key FROM \"" << _tableName << "\" WHERE \"accessed\" < ? limit ?)";
        _purgeLimitSQL = buf.str();          

        buf.str("");
        buf << "SELECT key FROM \"" << _tableName << "\" WHERE \"accessed\" < ? limit ?";
        _purgeSelect = buf.str();

        _statsLoaded = 0;
        _statsStored = 0;
        _statsDeleted = 0;
    }

Member Function Documentation

void LayerTable::checkAndPurgeIfNeeded	(	sqlite3 *	db,
		unsigned int	maxSize
	)		`[inline]`

Definition at line 477 of file Sqlite3Cache.cpp.

    {
        int size = getTableSize(db);
        OE_DEBUG << _meta._layerName <<  std::dec << " : "  << size/1024/1024 << " MB" << std::endl;
        if (size < 0 || size < 1.2 * maxSize)
            return;
            
        ::time_t t = ::time(0L);
        int nbElements = getNbEntry(db);
        float averageSize = size * 1.0 / nbElements;
        float diffSize = size - maxSize;
        int maxElementToRemove = static_cast<int>(ceil(diffSize/averageSize));
        OE_DEBUG << _meta._layerName <<  " try to remove " << std::dec << maxElementToRemove << " / " <<  nbElements << " to save place" << std::endl;
        purge(t, maxElementToRemove, db);
    }

Here is the call graph for this function:

Here is the caller graph for this function:

void LayerTable::displayStats ( ) [inline]

Definition at line 749 of file Sqlite3Cache.cpp.

    {
        osg::Timer_t t = osg::Timer::instance()->tick();
        if (osg::Timer::instance()->delta_s( _statsLastCheck, t) > 10.0) {
            double d = osg::Timer::instance()->delta_s(_statsStartTimer, t);
            OE_DEBUG << _meta._layerName << " time " << d << " stored " << std::dec << _statsStored << " rate " << _statsStored * 1.0 / d << std::endl;
            OE_DEBUG << _meta._layerName << " time " << d << " loaded " << std::dec  << _statsLoaded << " rate " << _statsLoaded * 1.0 / d << std::endl;
            OE_DEBUG << _meta._layerName << " time " << d << " deleted " << std::dec  << _statsDeleted << " rate " << _statsDeleted * 1.0 / d << std::endl;
            _statsLastCheck = t;
        }
    }

int LayerTable::getNbEntry ( sqlite3 * db ) [inline]

Definition at line 453 of file Sqlite3Cache.cpp.

                                {
        std::string query = "select count(*) from \"" + _tableName + "\";";
        sqlite3_stmt* select = 0L;
        int rc = sqlite3_prepare_v2( db, query.c_str(), query.length(), &select, 0L );
        if ( rc != SQLITE_OK )
        {
            OE_WARN << LC << "Failed to prepare SQL: " << query << "; " << sqlite3_errmsg(db) << std::endl;
            return -1;
        }

        rc = sqlite3_step( select );
        if ( rc != SQLITE_ROW)
        {
            OE_WARN << LC << "SQL QUERY failed for " << query << ": " 
                << sqlite3_errmsg( db ) //<< "; tries=" << (1000-tries)
                << ", rc = " << rc << std::endl;
            sqlite3_finalize( select );
            return -1;
        }
        int nbItems = sqlite3_column_int(select, 0);
        sqlite3_finalize( select );
        return nbItems;
    }

Here is the caller graph for this function:

sqlite3_int64 LayerTable::getTableSize ( sqlite3 * db ) [inline]

Definition at line 424 of file Sqlite3Cache.cpp.

    {
#ifdef SPLIT_DB_FILE
        std::string query = "select sum(size) from \"" + _tableName + "\";";
#else
        std::string query = "select sum(length(data)) from \"" + _tableName + "\";";
#endif
        sqlite3_stmt* select = 0L;
        int rc = sqlite3_prepare_v2( db, query.c_str(), query.length(), &select, 0L );
        if ( rc != SQLITE_OK )
        {
            OE_WARN << LC << "Failed to prepare SQL: " << query << "; " << sqlite3_errmsg(db) << std::endl;
            return -1;
        }

        rc = sqlite3_step( select );
        if ( rc != SQLITE_ROW)
        {
            OE_WARN << LC << "SQL QUERY failed for " << query << ": " 
                << sqlite3_errmsg( db ) //<< "; tries=" << (1000-tries)
                << ", rc = " << rc << std::endl;
            sqlite3_finalize( select );
            return -1;
        }
        sqlite3_int64 size = sqlite3_column_int(select, 0);
        sqlite3_finalize( select );
        return size;
    }

Here is the caller graph for this function:

bool LayerTable::initialize ( sqlite3 * db ) [inline]

Initializes the layer by creating the layer's table (if necessary), loading the appropriate reader-writer for the image data, and initializing the compressor if necessary.

Definition at line 843 of file Sqlite3Cache.cpp.

    {
        // first create the table if it does not already exist:
        std::stringstream buf;
        buf << "CREATE TABLE IF NOT EXISTS \"" << _tableName << "\" ("
            << "key char(64) PRIMARY KEY UNIQUE, "
            << "created int, "
            << "accessed int, "
#ifdef SPLIT_DB_FILE
            << "size int )";
#else
            << "data blob )";
#endif
        std::string sql = buf.str();

        OE_DEBUG << LC << "SQL = " << sql << std::endl;

        char* errMsg = 0L;
        int rc = sqlite3_exec( db, sql.c_str(), 0L, 0L, &errMsg );
        if ( rc != SQLITE_OK )
        {
            OE_WARN << LC << "Creating layer \"" << _meta._layerName << "\": " << errMsg << std::endl;
            sqlite3_free( errMsg );
            return false;
        }

        // create an index on the time-last-accessed column
        buf.str("");
        buf << "CREATE INDEX IF NOT EXISTS \"" 
            << _tableName << "_lruindex\" "
            << "ON \"" << _tableName << "\" (accessed)";
        sql = buf.str();

        OE_DEBUG << LC << "SQL = " << sql << std::endl;

        rc = sqlite3_exec( db, sql.c_str(), 0L, 0L, &errMsg );
        if ( rc != SQLITE_OK )
        {
            OE_WARN << LC << "Creating index for layer \"" << _meta._layerName << "\": " << errMsg << std::endl;
            sqlite3_free( errMsg );
            //return false;
        }

        // next load the appropriate ReaderWriter:

#if OSG_MIN_VERSION_REQUIRED(2,9,5)
        _rw = osgDB::Registry::instance()->getReaderWriterForMimeType( _meta._format );
        if ( !_rw.valid() )
#endif
            _rw = osgDB::Registry::instance()->getReaderWriterForExtension( _meta._format );
        if ( !_rw.valid() )
        {
            OE_WARN << LC << "Creating layer: Cannot initialize ReaderWriter for format \"" 
                << _meta._format << "\"" << std::endl;
            return false;
        }

        if ( !_meta._compressor.empty() )
            _rwOptions = new osgDB::ReaderWriter::Options( "Compressor=" + _meta._compressor );

        _statsLastCheck = _statsStartTimer = osg::Timer::instance()->tick();
        return true;
    }

bool LayerTable::load	(	const TileKey &	key,
		ImageRecord &	output,
		sqlite3 *	db
	)		`[inline]`

Definition at line 690 of file Sqlite3Cache.cpp.

    {
        displayStats();
        int imageBufLen = 0;
        
        sqlite3_stmt* select = 0L;
        int rc = sqlite3_prepare_v2( db, _selectSQL.c_str(), _selectSQL.length(), &select, 0L );
        if ( rc != SQLITE_OK )
        {
            OE_WARN << LC << "Failed to prepare SQL: " << _selectSQL << "; " << sqlite3_errmsg(db) << std::endl;
            return false;
        }

        std::string keyStr = key.str();
        sqlite3_bind_text( select, 1, keyStr.c_str(), keyStr.length(), SQLITE_STATIC );

        rc = sqlite3_step( select );
        if ( rc != SQLITE_ROW ) // == SQLITE_DONE ) // SQLITE_DONE means "no more rows"
        {
            // cache miss
            OE_DEBUG << LC << "Cache MISS on tile " << key.str() << std::endl;
            sqlite3_finalize(select);
            return false;
        }

        // copy the timestamps:
        output._created  = sqlite3_column_int( select, 0 );
        output._accessed = sqlite3_column_int( select, 1 );

#ifdef SPLIT_DB_FILE
        std::string fname(keyStr);
        osgDB::ReaderWriter::ReadResult rr = _rw->readImage( _meta._layerName + "_" +fname+".osgb" );
#else
        // the pointer returned from _blob gets freed internally by sqlite, supposedly
        const char* data = (const char*)sqlite3_column_blob( select, 2 );
        imageBufLen = sqlite3_column_bytes( select, 2 );

        // deserialize the image from the buffer:
        std::string imageString( data, imageBufLen );
        std::stringstream imageBufStream( imageString );
        osgDB::ReaderWriter::ReadResult rr = _rw->readImage( imageBufStream );
#endif
        if ( rr.error() )
        {
            OE_WARN << LC << "Failed to read image from database: " << rr.message() << std::endl;
        }
        else
        {
            output._image = rr.takeImage();
            output._key = key;
            OE_DEBUG << LC << "Cache HIT on tile " << key.str() << std::endl;
        }

        sqlite3_finalize(select);

        _statsLoaded++;
        return output._image.valid();
    }

Here is the call graph for this function:

Here is the caller graph for this function:

bool LayerTable::purge	(	int	utcTimeStamp,
		int	maxToRemove,
		sqlite3 *	db
	)		`[inline]`

Definition at line 761 of file Sqlite3Cache.cpp.

    {
        if ( maxToRemove < 0 )
            return false;

        sqlite3_stmt* purge = 0L;
        
        int rc;
        {
#ifdef SPLIT_DB_FILE
            {
                std::vector<std::string> deleteFiles;
                sqlite3_stmt* selectPurge = 0L;
                rc = sqlite3_prepare_v2( db, _purgeSelect.c_str(), _purgeSelect.length(), &selectPurge, 0L);
                if ( rc != SQLITE_OK )
                {
                    OE_WARN << LC << "Failed to prepare SQL: " << _purgeSelect << "; " << sqlite3_errmsg(db) << std::endl;
                    return false;
                }
                sqlite3_bind_int( selectPurge, 2, maxToRemove );
                sqlite3_bind_int( selectPurge, 1, utcTimeStamp );

                rc = sqlite3_step( selectPurge );
                if ( rc != SQLITE_ROW && rc != SQLITE_DONE)
                {
                    OE_WARN << LC << "SQL QUERY failed for " << _purgeSelect << ": " 
                            << sqlite3_errmsg( db ) //<< "; tries=" << (1000-tries)
                            << ", rc = " << rc << std::endl;
                    sqlite3_finalize( selectPurge );
                    return false;
                }
                while (rc == SQLITE_ROW) {
                    std::string f((const char*)sqlite3_column_text( selectPurge, 0 ));
                    deleteFiles.push_back(f);
                    rc = sqlite3_step( selectPurge );
                }
                if (rc != SQLITE_DONE) {
                    OE_WARN << LC << "SQL QUERY failed for " << _purgeSelect << ": " 
                            << sqlite3_errmsg( db ) //<< "; tries=" << (1000-tries)
                            << ", rc = " << rc << std::endl;
                    sqlite3_finalize( selectPurge );
                    return false;
                }
                sqlite3_finalize( selectPurge );
                while (!deleteFiles.empty()) {
                    std::string fname = _meta._layerName + "_" + deleteFiles.back() +".osgb";
                    int run = unlink(fname.c_str());
                    if (run) {
                        OE_WARN << "Error while removing file " << fname << std::endl;
                    }
                    deleteFiles.pop_back();
                }
            }
#endif
            rc = sqlite3_prepare_v2( db, _purgeLimitSQL.c_str(), _purgeLimitSQL.length(), &purge, 0L );
            if ( rc != SQLITE_OK )
            {
                OE_WARN << LC << "Failed to prepare SQL: " << _purgeLimitSQL << "; " << sqlite3_errmsg(db) << std::endl;
                return false;
            }
            sqlite3_bind_int( purge, 2, maxToRemove );
        }

        sqlite3_bind_int( purge, 1, utcTimeStamp );

        rc = sqlite3_step( purge );
        if ( rc != SQLITE_DONE )
        {
            // cache miss
            OE_DEBUG << LC << "Error purging records from \"" << _meta._layerName << "\"; " << sqlite3_errmsg(db) << std::endl;
            sqlite3_finalize(purge);
            return false;
        }

        sqlite3_finalize(purge);
        _statsDeleted += maxToRemove;
        return true;
    }

Here is the call graph for this function:

Here is the caller graph for this function:

bool LayerTable::store	(	const ImageRecord &	rec,
		sqlite3 *	db
	)		`[inline]`

Definition at line 493 of file Sqlite3Cache.cpp.

    {
        displayStats();

        sqlite3_stmt* insert = 0L;
        int rc = sqlite3_prepare_v2( db, _insertSQL.c_str(), _insertSQL.length(), &insert, 0L );
        if ( rc != SQLITE_OK )
        {
            OE_WARN 
                << LC << "Error preparing SQL: " 
                << sqlite3_errmsg( db )
                << "(SQL: " << _insertSQL << ")"
                << std::endl;
            return false;
        }

        // bind the key string:
        std::string keyStr = rec._key.str();
        sqlite3_bind_text( insert, 1, keyStr.c_str(), keyStr.length(), SQLITE_STATIC );
        sqlite3_bind_int(  insert, 2, rec._created );
        sqlite3_bind_int(  insert, 3, rec._accessed );

        // serialize the image:
#ifdef SPLIT_DB_FILE
        std::stringstream outStream;
        _rw->writeImage( *rec._image.get(), outStream, _rwOptions.get() );
        std::string outBuf = outStream.str();
        std::string fname = _meta._layerName + "_" + keyStr+".osgb";
        {
            std::ofstream file(fname.c_str(), std::ios::out | std::ios::binary);
            if (file.is_open()) {
                file.write(outBuf.c_str(), outBuf.length());
            }
        }
        sqlite3_bind_int( insert, 4, outBuf.length() );
#else
        std::stringstream outStream;
        _rw->writeImage( *rec._image.get(), outStream, _rwOptions.get() );
        std::string outBuf = outStream.str();
        sqlite3_bind_blob( insert, 4, outBuf.c_str(), outBuf.length(), SQLITE_STATIC );
#endif

        // write to the database:
        rc = sqlite3_step( insert );

        if ( rc != SQLITE_DONE )
        {
            OE_WARN << LC << "SQL INSERT failed for key " << rec._key.str() << ": " 
                << sqlite3_errmsg( db ) //<< "; tries=" << (1000-tries)
                << ", rc = " << rc << std::endl;
            sqlite3_finalize( insert );
            return false;
        }
        else
        {
            OE_DEBUG << LC << "cache INSERT tile " << rec._key.str() << std::endl;
            sqlite3_finalize( insert );
            _statsStored++;
            return true;
        }
    }

Here is the call graph for this function:

Here is the caller graph for this function:

bool LayerTable::updateAccessTime	(	const TileKey &	key,
		int	newTimestamp,
		sqlite3 *	db
	)		`[inline]`

Definition at line 640 of file Sqlite3Cache.cpp.

    { 
        sqlite3_stmt* update = 0L;
        int rc = sqlite3_prepare_v2( db, _updateTimeSQL.c_str(), _updateTimeSQL.length(), &update, 0L );
        if ( rc != SQLITE_OK )
        {
            OE_WARN << LC << "Failed to prepare SQL " << _updateTimeSQL << "; " << sqlite3_errmsg(db) << std::endl;
            return false;
        }

        bool success = true;
        sqlite3_bind_int( update, 1, newTimestamp );
        std::string keyStr = key.str();
        sqlite3_bind_text( update, 2, keyStr.c_str(), keyStr.length(), SQLITE_STATIC );
        rc = sqlite3_step( update );
        if ( rc != SQLITE_DONE )
        {
            OE_WARN << LC << "Failed to update timestamp for " << key.str() << " on layer " << _meta._layerName << " rc = " << rc << std::endl;
            success = false;
        }

        sqlite3_finalize( update );
        return success;
    }

Here is the call graph for this function:

Here is the caller graph for this function:

bool LayerTable::updateAccessTimePool	(	const std::string &	keyStr,
		int	newTimestamp,
		sqlite3 *	db
	)		`[inline]`

Definition at line 665 of file Sqlite3Cache.cpp.

    {
        //OE_WARN << LC << "update access times " << _meta._layerName << " " << keyStr << std::endl;
        sqlite3_stmt* update = 0L;
        int rc = sqlite3_prepare_v2( db, _updateTimePoolSQL.c_str(), _updateTimePoolSQL.length(), &update, 0L );
        if ( rc != SQLITE_OK )
        {
            OE_WARN << LC << "Failed to prepare SQL " << _updateTimePoolSQL << "; " << sqlite3_errmsg(db) << std::endl;
            return false;
        }

        bool success = true;
        sqlite3_bind_int( update, 1, newTimestamp );
        sqlite3_bind_text( update, 2, keyStr.c_str(), keyStr.length(), SQLITE_STATIC );
        rc = sqlite3_step( update );
        if ( rc != SQLITE_DONE )
        {
            OE_WARN << LC << "Failed to update timestamp for " << keyStr << " on layer " << _meta._layerName << " rc = " << rc << std::endl;
            success = false;
        }

        sqlite3_finalize( update );
        return success;
    }