void TrackDAOSqlite3Impl::ensureDBSchema() {
int rc;
char *zErrMsg = NULL;
std::string sql;
checkDb();
sql.append( "CREATE TABLE IF NOT EXISTS `Tracks` (" );
sql.append( " `TrackId` INTEGER PRIMARY KEY AUTOINCREMENT," );
sql.append( " `Filename` VARCHAR(255) NOT NULL," );
sql.append( " `Checksum` VARCHAR(32) NOT NULL," );
sql.append( " `Name` VARCHAR(255) NOT NULL," );
sql.append( " `Art_Filename` VARCHAR(255) DEFAULT NULL," );
sql.append( " `ArtistId` INTEGER DEFAULT NULL," );
sql.append( " `AlbumArtistId` INTEGER DEFAULT NULL," );
sql.append( " `AlbumId` INTEGER DEFAULT NULL," );
sql.append( " `TrackNumber` INTEGER DEFAULT 0," );
sql.append( " `DiscNumber` INTEGER DEFAULT 0," );
sql.append( " `ReplayGain` FLOAT," );
sql.append( " FOREIGN KEY(`ArtistId`) REFERENCES `Artists`(`ArtistId`) ON DELETE RESTRICT," );
sql.append( " FOREIGN KEY(`AlbumArtistId`) REFERENCES `Artists`(`ArtistId`) ON DELETE RESTRICT," );
sql.append( " FOREIGN KEY(`AlbumId`) REFERENCES `Albums`(`AlbumId`) ON DELETE RESTRICT" );
sql.append( ");" );
rc = sqlite3_exec( db, sql.c_str(), NULL, 0, &zErrMsg );
if( rc != SQLITE_OK ) {
THROW_GEMINI_EXCEPTION( zErrMsg );
sqlite3_free( zErrMsg );
}
}
void PlaylistDAOSqlite3Impl::ensureDBSchema() {
int rc;
char *zErrMsg = NULL;
std::string sql;
checkDb();
sql.append( "CREATE TABLE IF NOT EXISTS `Playlists` (" );
sql.append( " `PlaylistId` INTEGER PRIMARY KEY AUTOINCREMENT," );
sql.append( " `Name` VARCHAR(255) NOT NULL" );
sql.append( ");" );
sql.append( "CREATE TABLE IF NOT EXISTS `PlaylistTracks` (" );
sql.append( " `PlaylistTracksId` INTEGER PRIMARY KEY AUTOINCREMENT," );
sql.append( " `PlaylistId` INTEGER NOT NULL," );
sql.append( " `Order` INTEGER NOT NULL," );
sql.append( " `TrackId` INTEGER NOT NULL," );
sql.append( " FOREIGN KEY(`TrackId`) REFERENCES `Tracks`(`TrackId`) ON DELETE RESTRICT," );
sql.append( " FOREIGN KEY(`PlaylistId`) REFERENCES `Playlists`(`PlaylistId`) ON DELETE RESTRICT" );
sql.append( ");" );
rc = sqlite3_exec( db, sql.c_str(), NULL, 0, &zErrMsg );
if( rc != SQLITE_OK ) {
THROW_GEMINI_EXCEPTION( zErrMsg );
sqlite3_free( zErrMsg );
}
}
CGantnerTime::CGantnerTime(QObject *parent) : QObject(parent)
{
timerCheckDb = new QTimer(this);
connect(timerCheckDb, SIGNAL(timeout()), this, SLOT(checkDb()));
timerCheckDb->start(TIME_DB_CHECKING);
initSAASMode();
}
void TrackDAOSqlite3Impl::insertOrUpdateTrack( Track* track ) {
sqlite3_stmt *pStmt = NULL;
bool rowExists = false;
checkDb();
pStmt = prepare( "SELECT `Checksum` FROM `Tracks` WHERE `Filename` = :name;" );
bindText( pStmt, ":name", track->filename.c_str() );
if( step( pStmt ) == SQLITE_ROW ) {
rowExists = true;
//TODO: Consider checking this file's checksum
//checksum.assign( sqlite3_column_text( pStmt, 0 ) );
}
finalize( pStmt );
if( rowExists) {
pStmt = prepare(
"UPDATE `Tracks` SET "
"`Checksum` = :checksum,"
"`Name` = :name,"
"`Art_Filename` = :art_filename,"
"`ArtistId` = :artist_id,"
"`AlbumArtistId` = :album_artist_id,"
"`AlbumId` = :album_id,"
"`TrackNumber` = :track_number,"
"`DiscNumber` = :disc_number,"
"`ReplayGain` = :replay_gain "
"WHERE `Filename` = :filename;" );
} else {
pStmt = prepare(
"INSERT INTO `Tracks`"
"(`Filename`, `Checksum`, `Name`, `Art_Filename`, `ArtistId`, `AlbumArtistId`, `AlbumId`, `TrackNumber`,`DiscNumber`, `ReplayGain`)"
"values(:filename, :checksum, :name, :art_filename, :artist_id, :album_artist_id, :album_id, :track_number, :disc_number, :replay_gain);" );
}
bindText( pStmt, ":filename", track->filename.c_str() );
bindText( pStmt, ":checksum", "nop" );
bindText( pStmt, ":name", track->name.c_str() );
bindText( pStmt, ":art_filename", track->artFilename.c_str() );
if( track->artist != NULL )
bindLong( pStmt, ":artist_id", track->artist->id );
if( track->album != NULL ) {
bindLong( pStmt, ":album_id", track->album->id );
if( track->album->artist != NULL ) {
bindLong( pStmt, ":album_artist_id", track->album->artist->id );
}
}
bindLong( pStmt, ":track_number", track->trackNumber );
bindLong( pStmt, ":disc_number", track->discNumber );
bindDouble( pStmt, ":replay_gain", track->replayGain );
step( pStmt );
finalize( pStmt );
}
void PlaylistDAOSqlite3Impl::deletePlaylist( Playlist* playlist ) {
sqlite3_stmt* pStmt;
checkDb();
pStmt = prepare( "DELETE FROM `PlaylistTracks` WHERE `PlaylistId` = :playlistid;" );
bindLong( pStmt, ":playlistid", playlist->id );
step( pStmt );
finalize( pStmt );
pStmt = prepare( "DELETE FROM `Playlists` WHERE `PlaylistId` = :playlistid;" );
bindLong( pStmt, ":playlistid", playlist->id );
step( pStmt );
finalize( pStmt );
}
void PlaylistDAOSqlite3Impl::fillPlaylistTrackIds( Playlist* playlist ) {
std::stringstream sql;
checkDb();
sql << "SELECT TrackId FROM `PlaylistTracks`";
sql << " WHERE PlaylistId = :playlistid";
sql << " ORDER BY `Order` ASC;";
sqlite3_stmt* pStmt = prepare( sql.str().c_str() );
bindLong( pStmt, ":playlistid", playlist->id );
while( step( pStmt) == SQLITE_ROW ) {
playlist->track_ids.push_back( sqlite3_column_int64( pStmt, 0 ) );
}
finalize( pStmt );
}
bool SqlDatabase::openReadOnly( const QString& filename )
{
if( isOpen() ) {
return true;
}
if( !openHelper(filename, SQLITE_OPEN_READONLY) ) {
return false;
}
if( !checkDb() ) {
qDebug() << "Consistency check failed in readonly mode, giving up" << filename;
close();
return false;
}
return true;
}
std::vector<long>* TrackDAOSqlite3Impl::getTrackIds( ) {
std::vector<long>* track_ids;
checkDb();
track_ids = new std::vector<long>();
sqlite3_stmt* pStmt = prepare( "SELECT "
"`TrackId` "
" FROM `Tracks`;" );
while( step( pStmt ) == SQLITE_ROW ) {
track_ids->push_back( sqlite3_column_int64( pStmt, 0) );
}
finalize( pStmt );
return track_ids;
}
void SettingsDAOSqlite3Impl::ensureDBSchema() {
int rc;
char *zErrMsg = NULL;
const char* sql =
"CREATE TABLE IF NOT EXISTS `Settings` ("
"`Id` INTEGER PRIMARY KEY AUTOINCREMENT,"
"`Key` VARCHAR(255) UNIQUE NOT NULL,"
"`Value` VARCHAR(255)"
");" ;
checkDb();
rc = sqlite3_exec( db, sql, NULL, 0, &zErrMsg );
if( rc != SQLITE_OK ) {
THROW_GEMINI_EXCEPTION( zErrMsg );
sqlite3_free( zErrMsg );
}
}
bool SqlDatabase::openOrCreateReadWrite( const QString& filename )
{
if( isOpen() ) {
return true;
}
if( !openHelper(filename, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE) ) {
return false;
}
if( !checkDb() ) {
qDebug() << "Consistency check failed, removing broken db" << filename;
close();
QFile::remove(filename);
return openHelper(filename, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);
}
return true;
}
std::vector<long>* TrackDAOSqlite3Impl::getTrackIdsByArtistId( long artist_id ) {
std::vector<long>* track_ids;
checkDb();
track_ids = new std::vector<long>();
sqlite3_stmt* pStmt = prepare( "SELECT "
"`TrackId` "
" FROM `Tracks`"
" WHERE `ArtistId` = :artistid;");
bindLong( pStmt, ":artistid", artist_id );
while( step( pStmt ) == SQLITE_ROW ) {
track_ids->push_back( sqlite3_column_int64( pStmt, 0) );
}
finalize( pStmt );
return track_ids;
}
std::vector<long>* TrackDAOSqlite3Impl::getTrackIdsByAlbumId( long album_id ) {
std::vector<long>* track_ids;
checkDb();
track_ids = new std::vector<long>();
sqlite3_stmt* pStmt = prepare( "SELECT "
"`TrackId`, `DiscNumber`, `TrackNumber` "
" FROM `Tracks`"
" WHERE `AlbumId` = :albumid"
" ORDER BY `DiscNumber` ASC, `TrackNumber` ASC");
bindLong( pStmt, ":albumid", album_id );
while( step( pStmt ) == SQLITE_ROW ) {
track_ids->push_back( sqlite3_column_int64( pStmt, 0) );
}
finalize( pStmt );
return track_ids;
}
bool SqlDatabase::openOrCreateReadWrite(const QString &filename)
{
if (isOpen()) {
return true;
}
if (!openHelper(filename, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)) {
return false;
}
auto checkResult = checkDb();
if (checkResult != CheckDbResult::Ok) {
if (checkResult == CheckDbResult::CantPrepare) {
// When disk space is low, preparing may fail even though the db is fine.
// Typically CANTOPEN or IOERR.
qint64 freeSpace = Utility::freeDiskSpace(QFileInfo(filename).dir().absolutePath());
if (freeSpace != -1 && freeSpace < 1000000) {
qCWarning(lcSql) << "Can't prepare consistency check and disk space is low:" << freeSpace;
close();
return false;
}
// Even when there's enough disk space, it might very well be that the
// file is on a read-only filesystem and can't be opened because of that.
if (_errId == SQLITE_CANTOPEN) {
qCWarning(lcSql) << "Can't open db to prepare consistency check, aborting";
close();
return false;
}
}
qCCritical(lcSql) << "Consistency check failed, removing broken db" << filename;
close();
QFile::remove(filename);
return openHelper(filename, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);
}
return true;
}
Playlist* PlaylistDAOSqlite3Impl::getPlaylist( Playlist* criterion ) {
Playlist* playlist = NULL;
int column = 0;
QueryCriteriaList queryCriteriaList;
std::string* sql;
checkDb();
if( criterion != NULL ) {
if( criterion->id >= 0 ) {
QueryCriteria qc = {"PlaylistId", ":playlistid", QueryCriteria::LONG};
qc.value = &criterion->id;
queryCriteriaList.push_back(qc);
}
if( !criterion->name.empty() ) {
QueryCriteria qc = {"Name", ":name", QueryCriteria::TEXT};
qc.value = criterion->name.c_str();
queryCriteriaList.push_back(qc);
}
}
sql = buildSqlFromQueryCriteria( "Playlists", queryCriteriaList );
sqlite3_stmt* pStmt = prepare( sql->c_str() );
bindVariablesFromQueryCriteria( pStmt, queryCriteriaList );
if( step( pStmt ) == SQLITE_ROW ) {
playlist = new Playlist();
playlist->id = sqlite3_column_int64( pStmt, column++ );
playlist->name.assign( (const char*) sqlite3_column_text( pStmt, column++ ) );
fillPlaylistTrackIds( playlist );
}
finalize( pStmt );
delete sql;
return playlist;
}
Track* TrackDAOSqlite3Impl::getTrack( Track* criterion ) {
Track* track = NULL;
Artist* artist_criterion;
Album* album_criterion;
int column = 0;
QueryCriteriaList queryCriteriaList;
std::string* sql;
checkDb();
if( criterion != NULL ) {
if( criterion->id >= 0 ) {
QueryCriteria qc = {"TrackId", ":trackid", QueryCriteria::LONG};
qc.value = &criterion->id;
queryCriteriaList.push_back(qc);
}
if( !criterion->filename.empty() ) {
QueryCriteria qc = {"Filename", ":filename", QueryCriteria::TEXT};
qc.value = criterion->filename.c_str();
queryCriteriaList.push_back(qc);
}
if( !criterion->name.empty() ) {
QueryCriteria qc = {"Name", ":name", QueryCriteria::TEXT};
qc.value = criterion->name.c_str();
queryCriteriaList.push_back(qc);
}
if( !criterion->artFilename.empty() ) {
QueryCriteria qc = {"Art_Filename", ":artfilename", QueryCriteria::TEXT};
qc.value = criterion->artFilename.c_str();
queryCriteriaList.push_back(qc);
}
if( criterion->artist && criterion->artist->id >= 0 ) {
QueryCriteria qc = {"ArtistId", ":artistid", QueryCriteria::LONG};
qc.value = &criterion->artist->id;
queryCriteriaList.push_back(qc);
}
if( criterion->album && criterion->album->artist && criterion->album->artist->id >= 0 ) {
QueryCriteria qc = {"AlbumArtistId", ":albumartistid", QueryCriteria::LONG};
qc.value = &criterion->album->artist->id;
queryCriteriaList.push_back(qc);
}
if( criterion->album && criterion->album->id >= 0 ) {
QueryCriteria qc = {"AlbumId", ":albumid", QueryCriteria::LONG};
qc.value = &criterion->album->id;
queryCriteriaList.push_back(qc);
}
if( criterion->discNumber > 0 ) {
QueryCriteria qc = {"DiscNumber", ":discnumber", QueryCriteria::INT};
qc.value = &criterion->discNumber;
queryCriteriaList.push_back(qc);
}
if( criterion->trackNumber > 0 ) {
QueryCriteria qc = {"TrackNumber", ":tracknumber", QueryCriteria::INT};
qc.value = &criterion->trackNumber;
queryCriteriaList.push_back(qc);
}
if( criterion->replayGain > -99.0 ) {
QueryCriteria qc = {"ReplayGain", ":replaygain", QueryCriteria::DOUBLE};
qc.value = &criterion->replayGain;
queryCriteriaList.push_back(qc);
}
}
sql = buildSqlFromQueryCriteria( "Tracks", queryCriteriaList );
sqlite3_stmt* pStmt = prepare( sql->c_str() );
bindVariablesFromQueryCriteria( pStmt, queryCriteriaList );
if( step( pStmt ) == SQLITE_ROW ) {
track = new Track();
artist_criterion = new Artist();
album_criterion = new Album();
album_criterion->artist = new Artist();
track->id = sqlite3_column_int64( pStmt, column++ );
track->filename.assign( (const char*) sqlite3_column_text( pStmt, column++ ) );
column++; //track->checksum.assign( (const char*)sqlite3_column_text( pStmt, column++; ) );
track->name.assign( (const char*) sqlite3_column_text( pStmt, column++ ) );
track->artFilename.assign( (const char*) sqlite3_column_text( pStmt, column++ ) );
artist_criterion->id = sqlite3_column_int64( pStmt, column++ );
album_criterion->artist->id = sqlite3_column_int64( pStmt, column++ );
album_criterion->id = sqlite3_column_int64( pStmt, column++ );
track->trackNumber = sqlite3_column_int( pStmt, column++ );
track->discNumber = sqlite3_column_int( pStmt, column++ );
track->replayGain = sqlite3_column_double( pStmt, column++ );
if( artist_criterion->id >= 0 ) {
track->artist = artistDAO->getArtist( artist_criterion );
}
if( album_criterion->id >= 0 ) {
track->album = albumDAO->getAlbum( album_criterion );
}
delete album_criterion->artist;
delete album_criterion;
delete artist_criterion;
}
finalize( pStmt );
delete sql;
return track;
}
int ccDBOpen(char *name)
{
int rv = FALSE;
if (sqlite3_open_v2(name, &dbPointer,SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK && checkDb())
{
#ifdef TEST
printf("checkdb...");
#endif
char *zErrMsg = 0;
int rc = sqlite3_exec(dbPointer, "PRAGMA journal_mode = OFF; PRAGMA synchronous = OFF;PRAGMA temp_store = MEMORY;",
verscallback, 0, &zErrMsg);
if( rc!=SQLITE_OK )
{
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
rv = TRUE;
}
else
{
if (dbPointer)
sqlite3_close(dbPointer);
dbPointer = NULL;
remove(name);
if (sqlite3_open_v2(name, &dbPointer, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL) == SQLITE_OK)
{
#ifdef TEST
printf("createdb...");
#endif
rv = createTables();
}
else
{
#ifdef TEST
printf("open...");
#endif
}
}
if (rv)
sqlite3_busy_timeout(dbPointer, 800);
atexit(unregister);
return rv;
}
