bool Create(RCString path) override {
m_db.Create(path);
m_db.ExecuteNonQuery("PRAGMA page_size=8192");
m_db.ExecuteNonQuery(
"CREATE TABLE blocks (id INTEGER PRIMARY KEY"
", hash UNIQUE"
", data"
", txhashes);"
"CREATE TABLE txes (id INTEGER PRIMARY KEY"
", blockid INTEGER"
", data"
", ins"
", coins);"
);
if (Eng().Mode == EngMode::BlockExplorer)
m_db.ExecuteNonQuery("CREATE TABLE pubkeys (id INTEGER PRIMARY KEY"
", data"
", txhashes);");
else
m_db.ExecuteNonQuery("CREATE TABLE pubkeys (id INTEGER PRIMARY KEY"
", data);");
SetUserVersion(m_db);
SetPragmas();
return true;
}
void UpgradeTo(const Version& ver) override {
CoinEng& eng = Eng();
Version dbver = CheckUserVersion();
if (dbver < ver) {
TRC(0, "Upgrading Database " << eng.GetDbFilePath() << " from version " << dbver << " to " << ver);
TransactionScope dbtx(m_db);
eng.UpgradeDb(ver);
SetUserVersion(m_db, ver);
}
}
void UsdxDatabase::Initialize()
{
// Add table cUS_Statistics_Info
// needed in the conversion from 1.01 to 1.1
if (!TableExists(cUS_Statistics_Info))
{
sLog.Info("Database::Init", "Outdated song database found - missing table %s", cUS_Statistics_Info);
FormattedExec("CREATE TABLE IF NOT EXISTS [%s] ([ResetTime] INTEGER);", cUS_Statistics_Info);
// insert creation timestamp
time_t now = time(nullptr);
FormattedExec(
"INSERT INTO [%s] ([ResetTime]) VALUES(" I64FMTD ");",
cUS_Statistics_Info, now);
}
// convert data from 1.01 to 1.1
// part #1 - prearrangement
bool finalizeConversion = false;
Sint32 version = GetUserVersion();
if (version == 0 && TableExists(cUS_Scores))
{
// rename old tables - to be able to insert new table structures
FormattedExec("ALTER TABLE %s RENAME TO us_scores_101;", cUS_Scores);
FormattedExec("ALTER TABLE %s RENAME TO us_songs_101;", cUS_Songs);
finalizeConversion = true; // means: conversion has to be done!
}
// Set version number after creation
if (version == 0)
SetUserVersion(cDBVersion);
// SQLite does not handle VARCHAR(n) or INT(n) as expected.
// Texts do not have a restricted length, no matter which type is used,
// so use the native TEXT type. INT(n) is always INTEGER.
// In addition, SQLiteTable3 will fail if other types than the native SQLite
// types are used (especially FieldAsInteger). Also take care to write the
// types in upper-case letters although SQLite does not care about this -
// SQLiteTable3 is very sensitive in this regard.
FormattedExec(
"CREATE TABLE IF NOT EXISTS [%s] ("
"[SongID] INTEGER NOT NULL, "
"[Difficulty] INTEGER NOT NULL, "
"[Player] TEXT NOT NULL, "
"[Score] INTEGER NOT NULL, "
"[Date] INTEGER NULL"
");", cUS_Scores);
FormattedExec(
"CREATE TABLE IF NOT EXISTS [%s] ("
"[ID] INTEGER PRIMARY KEY, "
"[Artist] TEXT NOT NULL, "
"[Title] TEXT NOT NULL, "
"[TimesPlayed] INTEGER NOT NULL, "
"[Rating] INTEGER NULL"
");", cUS_Songs);
// Add Date column to cUS_Scores
if (!ColumnExists(cUS_Scores, "Date"))
{
sLog.Info("Database::Init", "Adding column [Date] to %s", cUS_Scores);
FormattedExec("ALTER TABLE %s ADD COLUMN [Date] INTEGER NULL", cUS_Scores);
}
// Add Rating column to cUS_Songs
// Just for users of nightly builds and developers!
if (!ColumnExists(cUS_Songs, "Rating"))
{
sLog.Info("Database::Init", "Adding column [Rating] to %s", cUS_Songs);
FormattedExec("ALTER TABLE %s ADD COLUMN [Rating] INTEGER NULL", cUS_Songs);
}
// convert data from previous versions
// part #2 - accomplishment
if (finalizeConversion)
{
// convert data from 1.01 to 1.1
if (TableExists("us_scores_101"))
ConvertFrom101To110();
}
}
