void UsdxDatabase::ConvertFrom101To110()
{
if (!ColumnExists("us_scores_101", "Date"))
{
sLog.Info(
"UsdxDatabase::Convert101To110",
"Outdated song database found - begin conversion from V1.01 to V1.1");
// insert old values into new tables
FormattedExec(
"INSERT INTO %s "
"SELECT SongID, Difficulty, Player, Score, 'NULL' FROM us_scores_101;",
cUS_Scores);
}
else
{
sLog.Info(
"UsdxDatabase::Convert101To110",
"Outdated song database found - begin conversion from V1.01 Challenge Mod to V1.1");
// insert old values into new tables
FormattedExec(
"INSERT INTO %s "
"SELECT SongID, Difficulty, Player, Score, Date FROM us_scores_101;",
cUS_Scores);
}
FormattedExec(
"INSERT INTO %s "
"SELECT ID, Artist, Title, TimesPlayed, 'NULL' FROM us_songs_101;",
cUS_Songs);
/*
// now we have to convert all the texts for unicode support:
// player names
TableData := nil;
try
TableData := ScoreDB.GetUniTable(
'SELECT [rowid], [Player] ' +
'FROM [' + cUS_Scores + '];');
// Go through all Entrys
while (not TableData.EOF) do
begin
// Convert name into UTF8 and alter all entrys
DecodeStringUTF8(TableData.FieldByName['Player'], tempUTF8String, encCP1252);
ScoreDB.ExecSQL(
'UPDATE [' + cUS_Scores + '] ' +
'SET [Player] = ? ' +
'WHERE [rowid] = ? ',
[tempUTF8String,
TableData.FieldAsInteger(TableData.FieldIndex['rowid'])]);
TableData.Next;
end; // while
except
on E: Exception do
Log.LogError(E.Message, 'TDataBaseSystem.Convert101To110');
end;
TableData.Free;
// song artist and song title
TableData := nil;
try
TableData := ScoreDB.GetUniTable(
'SELECT [ID], [Artist], [Title] ' +
'FROM [' + cUS_Songs + '];');
// Go through all Entrys
while (not TableData.EOF) do
begin
// Convert Artist into UTF8 and alter all entrys
DecodeStringUTF8(TableData.FieldByName['Artist'], tempUTF8String, encCP1252);
//Log.LogError(TableData.FieldByName['Artist']+' -> '+tempUTF8String+' (encCP1252)');
ScoreDB.ExecSQL(
'UPDATE [' + cUS_Songs + '] ' +
'SET [Artist] = ? ' +
'WHERE [ID] = ?',
[tempUTF8String,
TableData.FieldAsInteger(TableData.FieldIndex['ID'])]);
// Convert Title into UTF8 and alter all entrys
DecodeStringUTF8(TableData.FieldByName['Title'], tempUTF8String, encCP1252);
ScoreDB.ExecSQL(
'UPDATE [' + cUS_Songs + '] ' +
'SET [Title] = ? ' +
'WHERE [ID] = ? ',
[tempUTF8String,
TableData.FieldAsInteger(TableData.FieldIndex['ID'])]);
TableData.Next;
end; // while
except
on E: Exception do
Log.LogError(E.Message, 'TDataBaseSystem.Convert101To110');
end;
*/
// Now drop old tables
Exec("DROP TABLE us_scores_101;");
Exec("DROP TABLE us_songs_101;");
}
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();
}
}
// -----------------------------------------------------------------------
// Invoke yyparse, set hsGlobal structure for each column group.
// -----------------------------------------------------------------------
Lng32 HSFuncParseStmt()
{
HSGlobalsClass *hs_globals = GetHSContext();
Lng32 retcode;
HSColGroupStruct *mgroup = NULL;
void* scanner;
init_scanner (scanner);
HSFuncResetLexer(scanner);
retcode = yyparse(scanner);
HSHandleError(retcode);
destroy_scanner (scanner);
// The parser does not always return immediately following an error, so that
// it can report as many errors as possible. This can result in a nonzero
// return code being overwritten. To detect this case, we check the diagnostics
// area and return with the appropriate sqlcode if necessary.
retcode = hs_globals->getRetcodeFromDiags();
HSHandleError(retcode);
hs_globals->parserError = HSGlobalsClass::ERROR_SEMANTICS;
// We are done here if it is the showstats command
if (hs_globals->optFlags & SHOWSTATS_OPT)
return 0;
// Automatically generate single-column histograms for all multi-column
// histograms requested. This is required to calculate UEC counts for
// multi-column histograms.
// However, do not generate them when the CLEAR option is requested, unless
// ON EVERY COLUMN or ON EVERY KEY is also specified.
if (NOT (hs_globals->optFlags & CLEAR_OPT) ||
hs_globals->optFlags & EVERYCOL_OPT ||
hs_globals->optFlags & EVERYKEY_OPT)
{
mgroup = hs_globals->multiGroup;
while (mgroup != NULL)
{
for (Int32 i=0; i<mgroup->colCount; i++)
{
HSColumnStruct &col = mgroup->colSet[i];
if (NOT ColumnExists(col.colnum))
{
retcode = AddSingleColumn(col.colnum);
HSHandleError(retcode);
}
}
mgroup = mgroup->next;
}
}
// ----------------------------------------------------------------------
// Construct statistics time in the format: YYYY-MM-DD:HH:MM:SS.
// We use GMT time.
// ----------------------------------------------------------------------
time_t t;
time(&t);
char pt[30];
strftime(pt, 30, "%Y-%m-%d:%H:%M:%S", gmtime(&t));
*hs_globals->statstime = pt;
// Also store a numerical timestamp with 10 digits MMDDHHMMSS.
hs_globals->statsTimeInt = (pt[5] - '0') * 1000000000 +
(pt[6] - '0') * 100000000 +
(pt[8] - '0') * 10000000 +
(pt[9] - '0') * 1000000 +
(pt[11] - '0') * 100000 +
(pt[12] - '0') * 10000 +
(pt[14] - '0') * 1000 +
(pt[15] - '0') * 100 +
(pt[17] - '0') * 10 +
(pt[18] - '0');
return 0;
}
Lng32 AddKeyGroups()
{
HSGlobalsClass *hs_globals = GetHSContext();
if (HSGlobalsClass::isHiveCat(hs_globals->objDef->getCatName()))
{
// HSHiveTableDef::getKeyList()/getIndexArray() not yet implemented.
*CmpCommon::diags() << DgSqlCode(-UERR_NO_ONEVERYKEY) << DgString0("hive");
return -1;
}
Lng32 retcode = 0;
Lng32 numColsInGroup = 0;
HSColumnStruct col;
NAString tempColList = "";
NAString tempCol;
NAString autoGroup;
ULng32 numKeys;
ULng32 i, j;
NATable* naTbl = hs_globals->objDef->getNATable();
HSLogMan *LM = HSLogMan::Instance();
// ----------------------------------------------------------
// Generate histograms for KEY
// ----------------------------------------------------------
// The clustering index is included in the list of indices returned by
// NATable::getIndexList(), so we store its pointer so we can skip it
// when the other indexes are processed below.
NAFileSet* clusteringIndex = naTbl->getClusteringIndex();
const NAColumnArray& keyCols = clusteringIndex->getIndexKeyColumns();
Lng32 colPos;
numKeys = keyCols.entries();
if (numKeys == 1) // SINGLE-COLUMN KEY
{
colPos = keyCols[0]->getPosition();
if (LM->LogNeeded())
{
sprintf(LM->msg, "\t\tKEY:\t\t(%s)", hs_globals->objDef->getColName(colPos));
LM->Log(LM->msg);
}
if (ColumnExists(colPos)) // avoid duplicates
{
LM->Log("\t\t** duplicate column group has been ignored.");
}
else // add to single-column group list
{
retcode = AddSingleColumn(colPos);
}
}
else if (numKeys > 1) // MULTI-COLUMN KEY
{
// Create multiple MC group(s) if numkeys > 1. Subset MC groups will
// also be created if numkeys > 2, E.g. If numkeys = 5, then
// MC groups with 5, 4, 3, and 2 columns will be created using
// the key columns. Note that if numkeys is larger than CQD
// USTAT_NUM_MC_GROUPS_FOR_KEYS (default = 5), then the number
// of groups created will be limited by this value. So, e.g. if
// numkeys = 10, then MC groups with 5, 4, 3, and 2 columns will
// be created (that is, 5 groups will be created - incl the single).
ULng32 minMCGroupSz = 2;
ULng32 maxMCGroups = (ULng32)
CmpCommon::getDefaultNumeric(USTAT_NUM_MC_GROUPS_FOR_KEYS);
// Generate no MCs with more cols than specified by the cqd.
if (numKeys > maxMCGroups)
numKeys = maxMCGroups;
// For salted table, generate only the longest MC for the key (subject
// to max cols determined above) unless a cqd is set to gen all MCs of
// allowable sizes.
if (CmpCommon::getDefault(USTAT_ADD_SALTED_KEY_PREFIXES_FOR_MC) == DF_OFF &&
hs_globals->objDef->getColNum("_SALT_", FALSE) >= 0)
minMCGroupSz = numKeys;
while (numKeys >= minMCGroupSz) // Create only MC groups not single cols
{
HSColSet colSet;
autoGroup = "(";
for (j = 0; j < numKeys; j++)
{
colPos = keyCols[j]->getPosition();
col = hs_globals->objDef->getColInfo(colPos);
col.colnum = colPos;
colSet.insert(col);
autoGroup += col.colname->data();
autoGroup += ",";
}
if (LM->LogNeeded())
{
autoGroup.replace(autoGroup.length()-1,1,")"); // replace comma with close parenthesis
sprintf(LM->msg, "\t\tKEY:\t\t%s", autoGroup.data());
LM->Log(LM->msg);
}
if (retcode = AddColumnSet(colSet))
{
HSHandleError(retcode);
}
numKeys--;
}
}
// ----------------------------------------------------------
// Generate histograms for all INDEXES
// ----------------------------------------------------------
const NAFileSetList& indexes = naTbl->getIndexList();
NAFileSet* index;
for (i = 0; i < indexes.entries(); i++ )
{
index = indexes[i];
if (index == clusteringIndex)
continue; // clustering index processed above already
const NAColumnArray& keyCols = index->getIndexKeyColumns();
numKeys = keyCols.entries();
if (numKeys == 1) // SINGLE-COLUMN INDEX
{
colPos = keyCols[0]->getPosition();
if (LM->LogNeeded())
{
sprintf(LM->msg, "\t\tINDEX[%d]\t(%s)", i,
hs_globals->objDef->getColName(colPos));
LM->Log(LM->msg);
}
if (ColumnExists(colPos)) // avoid duplicates
{
LM->Log("\t\t*** duplicate column group has been ignored.");
}
else // add to single-column group list
{
retcode = AddSingleColumn(colPos);
}
}
else // MULTI-COLUMN INDEX
{
// Create multiple MC group(s) if numkeys > 1. Subset MC groups will
// also be created if numkeys > 2, E.g. If numkeys = 5, then
// MC groups with 5, 4, 3, and 2 columns will be created using
// the key columns. Note that if numkeys is larger than CQD
// USTAT_NUM_MC_GROUPS_FOR_KEYS (default = 5), then the number
// of groups created will be limited by this value. So, e.g. if
// numkeys = 10, then MC groups with 10, 9, 8, 7, 6 columns will
// be created (that is, 5 groups will be created).
ULng32 minMCGroupSz = 2;
ULng32 maxMCGroups = (ULng32)
CmpCommon::getDefaultNumeric(USTAT_NUM_MC_GROUPS_FOR_KEYS);
if (numKeys > maxMCGroups)
minMCGroupSz = numKeys - maxMCGroups + 1;
while (numKeys >= minMCGroupSz) // MinMCGroupSz is greater than 1.
{
HSColSet colSet;
tempColList = "";
autoGroup = "(";
for (j = 0; j < numKeys; j++)
{
colPos = keyCols[j]->getPosition();
tempCol = ".";
tempCol += LongToNAString(colPos);
tempCol += ".";
// Eliminate duplicate columns in the index;
// They may have been introduced by appending the key to the specified index.
if (!tempColList.contains(tempCol))
{
col = hs_globals->objDef->getColInfo(colPos);
col.colnum = colPos;
colSet.insert((const struct HSColumnStruct) col);
tempColList += tempCol.data();
numColsInGroup++;
autoGroup += col.colname->data();
autoGroup += ",";
}
}
if (colSet.entries())
{
if (numColsInGroup > 1)
{
if (LM->LogNeeded())
{
autoGroup.replace(autoGroup.length()-1,1,")"); // replace comma with close parenthesis
sprintf(LM->msg, "\t\tINDEX[%d]\t%s", i, autoGroup.data());
LM->Log(LM->msg);
}
if (retcode = AddColumnSet(colSet))
{
HSHandleError(retcode);
}
}
numColsInGroup = 0;
}
numKeys--;
}
}
}
return retcode;
}
// -----------------------------------------------------------------------
// Add the single-column groups from startColumn to endColumn. If these
// parameters are NULL, the function has been called for the ON EVERY COLUMN
// clause, and we will add all single column groups, as well as key groups.
// -----------------------------------------------------------------------
Lng32 AddEveryColumn(const char *startColumn, const char *endColumn)
{
HSGlobalsClass *hs_globals = GetHSContext();
Lng32 colNumber, retcode;
NAString colName;
Lng32 start, upto;
HSLogMan *LM = HSLogMan::Instance();
hs_globals->parserError = HSGlobalsClass::ERROR_SEMANTICS;
// Can't use EVERYCOL_OPT flag for this test, it may have been set on a
// previous call (making this a redundant, or incorrect, request to ustat
// an individual column name). startColumn will always be NULL if this fn
// is called to add all the columns for a table.
if (!startColumn)
{
HS_ASSERT(hs_globals->optFlags & EVERYCOL_OPT);
start = 0;
upto = hs_globals->objDef->getNumCols() - 1;
}
else
{
start = hs_globals->objDef->getColNum(startColumn);
// LCOV_EXCL_START :rfi
if (start < 0)
{
retcode = -1;
HSHandleError(retcode);
}
// LCOV_EXCL_STOP
upto = hs_globals->objDef->getColNum(endColumn);
// LCOV_EXCL_START :rfi
if (upto < 0)
{
retcode = -1;
HSHandleError(retcode);
}
// LCOV_EXCL_STOP
if (start > upto)
{
Lng32 tmp = upto;
upto = start;
start = tmp;
}
}
for (colNumber = start; colNumber <= upto; colNumber++)
{
if (ColumnExists(colNumber)) // avoid duplicates
{
colName = hs_globals->objDef->getColName(colNumber);
if (LM->LogNeeded())
{
sprintf(LM->msg, "\t\t****Duplicate Column group (%s) has been ignored", colName.data());
LM->Log(LM->msg);
}
}
else // add to single-column group list
{
retcode = AddSingleColumn(colNumber);
}
}
if (!startColumn && // ON EVERY COLUMN causes key groups to be added as well
!HSGlobalsClass::isHiveCat(hs_globals->objDef->getCatName())) // No ustat on keys yet for hive tables
{
retcode = AddKeyGroups();
HSHandleError(retcode);
}
hs_globals->parserError = HSGlobalsClass::ERROR_SYNTAX;
return 0;
}
