function contests_cache($job, $step)
{
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$limit = 50;
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
if ($step['success'] == 0) {
$total = $fw->db5->exec("SELECT COUNT(*) as found FROM `{$new}contests`;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
}
$dataIn = $fw->db5->exec("SELECT \r\n\t\t\t\t\t\t\t\tCon.conid,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT S.sid,',',S.title,',',U.uid,',',U.nickname ORDER BY charname ASC SEPARATOR '||') AS storyblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT Chara.charid,',',Chara.charname ORDER BY charname ASC SEPARATOR '||') AS characterblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT C.cid,',',C.category ORDER BY category ASC SEPARATOR '||' ) as categoryblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT T.tid,',',T.label,',',TG.description ORDER BY TG.order,TG.tgid,T.label ASC SEPARATOR '||') AS tagblock\r\n\t\t\t\t\t\t\t\t\tFROM \r\n\t\t\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\t\t\tSELECT Con1.conid\r\n\t\t\t\t\t\t\t\t\t\t\tFROM `{$new}contests`Con1\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE Con1.cache_tags IS NULL\r\n\t\t\t\t\t\t\t\t\t\t\tLIMIT 0,{$limit}\r\n\t\t\t\t\t\t\t\t\t) AS Con\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}contest_relations`rC ON ( rC.conid = Con.conid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories`S ON ( S.sid = rC.relid and rC.type='ST' )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories_authors`rSA ON ( rSA.sid = S.sid )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}users`U ON ( U.uid = rSA.aid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tags`T ON ( T.tid = rC.relid AND rC.type = 'T' )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tag_groups`TG ON ( TG.tgid = T.tgid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}characters`Chara ON ( Chara.charid = rC.relid AND rC.type = 'CH' )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}categories`C ON ( C.cid = rC.relid AND rC.type = 'CA' )\r\n\t\t\t\t\t\t\t\t\tGROUP BY Con.conid;");
if (0 < ($count = sizeof($dataIn))) {
foreach ($dataIn as $item) {
$fw->db5->exec("UPDATE `{$new}contests` SET \r\n\t\t\t\t\t\t`cache_stories`\t\t= :storyblock,\r\n\t\t\t\t\t\t`cache_tags`\t\t= :tagblock,\r\n\t\t\t\t\t\t`cache_characters`\t= :characterblock,\r\n\t\t\t\t\t\t`cache_categories`\t= :categoryblock\r\n\t\t\t\t\tWHERE conid = {$item['conid']} ;", [':storyblock' => json_encode(upgradetools::cleanResult($item['storyblock'])), ':tagblock' => json_encode(upgradetools::cleanResult($item['tagblock'])), ':characterblock' => json_encode(upgradetools::cleanResult($item['characterblock'])), ':categoryblock' => json_encode(upgradetools::cleanResult($item['categoryblock']))]);
}
$tracking->items = $tracking->items + $count;
$tracking->save();
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
function series_cache($job, $step)
{
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$limit = 20;
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
if ($step['success'] == 0) {
$total = $fw->db5->exec("SELECT COUNT(*) as found FROM `{$new}series`;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
}
$dataIn = $fw->db5->exec("SELECT \r\n\t\t\t\t\t\t\t\tSERIES.seriesid, \r\n\t\t\t\t\t\t\t\tSERIES.tagblock, \r\n\t\t\t\t\t\t\t\tSERIES.characterblock, \r\n\t\t\t\t\t\t\t\tSERIES.authorblock, \r\n\t\t\t\t\t\t\t\tSERIES.categoryblock, \r\n\t\t\t\t\t\t\t\tCONCAT(rating,'||',max_rating_id) as max_rating\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\tSELECT \r\n\t\t\t\t\t\t\tSer.seriesid,\r\n\t\t\t\t\t\t\tMAX(Ra.rid) as max_rating_id,\r\n\t\t\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT U.uid,',',U.nickname ORDER BY nickname ASC SEPARATOR '||' ) as authorblock,\r\n\t\t\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT Chara.charid,',',Chara.charname ORDER BY charname ASC SEPARATOR '||') AS characterblock,\r\n\t\t\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT C.cid,',',C.category ORDER BY category ASC SEPARATOR '||' ) as categoryblock,\r\n\t\t\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT T.tid,',',T.label,',',TG.description ORDER BY TG.order,TG.tgid,T.label ASC SEPARATOR '||') AS tagblock\r\n\t\t\t\t\t\t\t\t\tFROM \r\n\t\t\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\t\t\tSELECT Ser1.seriesid\r\n\t\t\t\t\t\t\t\t\t\t\tFROM `{$new}series`Ser1\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE Ser1.cache_authors IS NULL\r\n\t\t\t\t\t\t\t\t\t\t\tLIMIT 0,{$limit}\r\n\t\t\t\t\t\t\t\t\t) AS Ser\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}series_stories`TrS ON ( Ser.seriesid = TrS.seriesid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories`S ON ( TrS.sid = S.sid )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}ratings`Ra ON ( Ra.rid = S.ratingid )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories_tags`rST ON ( rST.sid = S.sid )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tags`T ON ( T.tid = rST.tid AND rST.character = 0 )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tag_groups`TG ON ( TG.tgid = T.tgid )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}characters`Chara ON ( Chara.charid = rST.tid AND rST.character = 1 )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories_categories`rSC ON ( rSC.sid = S.sid )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}categories`C ON ( rSC.cid = C.cid )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}stories_authors`rSA ON ( rSA.sid = S.sid )\r\n\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}users` U ON ( rSA.aid = U.uid )\r\n\t\t\t\t\t\t\t\t\tGROUP BY Ser.seriesid\r\n\t\t\t\t\t\t\t) AS SERIES\r\n\t\t\t\t\t\t\tLEFT JOIN `{$new}ratings`R ON (R.rid = max_rating_id);");
if (0 < ($count = sizeof($dataIn))) {
foreach ($dataIn as $item) {
$fw->db5->exec("UPDATE `{$new}series` SET \r\n\t\t\t\t\t\t`cache_authors`\t\t= :authorblock,\r\n\t\t\t\t\t\t`cache_tags`\t\t= :tagblock,\r\n\t\t\t\t\t\t`cache_characters`\t= :characterblock,\r\n\t\t\t\t\t\t`cache_categories`\t= :categoryblock,\r\n\t\t\t\t\t\t`max_rating`\t\t= :max_rating\r\n\t\t\t\t\tWHERE seriesid = {$item['seriesid']} ;", [':authorblock' => json_encode(upgradetools::cleanResult($item['authorblock'])), ':tagblock' => json_encode(upgradetools::cleanResult($item['tagblock'])), ':characterblock' => json_encode(upgradetools::cleanResult($item['characterblock'])), ':categoryblock' => json_encode(upgradetools::cleanResult($item['categoryblock'])), ':max_rating' => json_encode(explode(",", $item['max_rating']))]);
}
$tracking->items = $tracking->items + $count;
$tracking->save();
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
function chapters_copy($job, $step)
{
// Chapters copy is a 1-pass module, doing the entire chapter relocation
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$old = "{$fw['installerCFG.db3.dbname']}`.`{$fw['installerCFG.db3.prefix']}fanfiction_";
$limit = 100;
$report = [];
$source = $fw->get('installerCFG.data.store');
// "files" or "mysql"
$target = $fw->get('installerCFG.chapters');
// "filebase" or "database"
// Initialize
if ($step['success'] == 0) {
// drop an existing chapter DB file
if (file_exists(realpath('..') . '/data/chapters.sq3')) {
unlink(realpath('..') . '/data/chapters.sq3');
}
// if we need the filebase storage, initialize it now
if ($target = "filebase") {
$fw->dbsqlite = new DB\SQL('sqlite:' . realpath('..') . '/data/chapters.sq3');
$fw->dbsqlite->begin();
$fw->dbsqlite->exec("DROP TABLE IF EXISTS 'chapters'");
$fw->dbsqlite->exec("CREATE TABLE IF NOT EXISTS 'chapters' ('chapid' INTEGER PRIMARY KEY NOT NULL, 'sid' INTEGER, 'chaptertext' BLOB);");
// inorder might be obsolete
//$fw->dbsqlite->exec ( "CREATE TABLE IF NOT EXISTS 'chapters' ('chapid' INTEGER PRIMARY KEY NOT NULL, 'sid' INTEGER, 'inorder' INTEGER,'chaptertext' BLOB);" );
$fw->dbsqlite->commit();
unset($fw->dbsqlite);
} else {
}
// Count total chapters and take note
$total = $fw->db3->exec("SELECT COUNT(*) as found FROM `{$old}chapters`;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
}
$dataIn = $fw->db3->exec("SELECT COUNT(reviewid) as reviewsNew, Ch.uid as folder, Ch.chapid as chapter, Ch.*\r\n\t\t\t\t\t\t\t\tFROM `{$old}chapters`Ch \r\n\t\t\t\t\t\t\t\tLEFT JOIN `{$old}reviews`R ON ( Ch.chapid = R.chapid AND R.type='ST' )\r\n\t\t\t\t\t\t\t\tGROUP BY Ch.chapid\r\n\t\t\t\t\t\t\t\tORDER BY chapid ASC LIMIT {$step['items']},{$limit};");
if (0 < ($count = sizeof($dataIn))) {
$newchapter = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'chapters');
if ($target = "filebase") {
$fw->dbsqlite = new DB\SQL('sqlite:' . realpath('..') . '/data/chapters.sq3');
$newchapterText = new DB\SQL\Mapper($fw->dbsqlite, 'chapters');
}
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
foreach ($dataIn as $chapterIn) {
// Get chapter text, from file or DB
if ($source == "files") {
$s = upgradetools::getChapterFile($chapterIn);
if ($s[0]) {
$chaptertext = mb_convert_encoding($s[1], "UTF-8", mb_detect_encoding($s[1], 'UTF-8, ISO-8859-1'));
} else {
// report error
}
} elseif ($source == "mysql") {
$chaptertext = $chapterIn['storytext'];
}
if ($target == "filebase") {
// No text in Database
$newchapter->chaptertext = NULL;
// Store data in the filebase storage
$newchapterText->chapid = $chapterIn['chapter'];
$newchapterText->sid = $chapterIn['sid'];
//$newchapterText->inorder = $chapterIn['inorder'];
$newchapterText->chaptertext = $chaptertext;
$newchapterText->save();
$newchapterText->reset();
} else {
$newchapter->chaptertext = $chaptertext;
}
$newchapter->chapid = $chapterIn['chapter'];
$newchapter->sid = $chapterIn['sid'];
$newchapter->title = $chapterIn['title'];
$newchapter->inorder = $chapterIn['inorder'];
$newchapter->notes = $chapterIn['notes'];
$newchapter->endnotes = $chapterIn['endnotes'];
$newchapter->validated = $chapterIn['validated'];
$newchapter->wordcount = count(preg_split("/\\p{L}[\\p{L}\\p{Mn}\\p{Pd}'\\x{2019}]{1,}/u", $chaptertext));
$newchapter->rating = $chapterIn['rating'];
$newchapter->reviews = $chapterIn['reviewsNew'];
$newchapter->count = $chapterIn['count'];
$newchapter->save();
$newchapter->reset();
$tracking->items = $tracking->items + 1;
$tracking->save();
}
//
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
function recommendations_cache($job, $step)
{
$fw = \Base::instance();
$new = "{$fw['installerCFG.db5.dbname']}`.`{$fw['installerCFG.db5.prefix']}";
$limit = 50;
$tracking = new DB\SQL\Mapper($fw->db5, $fw->get('installerCFG.db5.prefix') . 'convert');
$tracking->load(['id = ?', $step['id']]);
if ($step['success'] == 0) {
$total = $fw->db5->exec("SELECT COUNT(*) as found FROM `{$new}recommendations`;")[0]['found'];
$fw->db5->exec("UPDATE `{$new}convert`SET `success` = 1, `total` = :total WHERE `id` = :id ", [':total' => $total, ':id' => $step['id']]);
}
$dataIn = $fw->db5->exec("SELECT SELECT_OUTER.recid,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT tid,',',tag,',',description ORDER BY `order`,tgid,tag ASC SEPARATOR '||') AS tagblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT charid,',',charname ORDER BY charname ASC SEPARATOR '||') AS characterblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT cid,',',category ORDER BY category ASC SEPARATOR '||' ) as categoryblock,\r\n\t\t\t\t\t\t\t\tGROUP_CONCAT(DISTINCT ratingid,',',rating_name,',',rating_image SEPARATOR '||' ) as rating\r\n\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\t\tSELECT R.recid,\r\n\t\t\t\t\t\t\t\t\t\tR.ratingid, Ra.rating as rating_name, IF(Ra.rating_image,Ra.rating_image,'') as rating_image,\r\n\t\t\t\t\t\t\t\t\t\tCat.cid, Cat.category,\r\n\t\t\t\t\t\t\t\t\t\tTG.description,TG.order,TG.tgid,T.label as tag,T.tid,\r\n\t\t\t\t\t\t\t\t\t\tCh.charid, Ch.charname\r\n\t\t\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\t\t\t\tSELECT R1.*\r\n\t\t\t\t\t\t\t\t\t\t\tFROM `{$new}recommendations` R1\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE R1.cache_tags IS NULL\r\n\t\t\t\t\t\t\t\t\t\t\tLIMIT 0,{$limit}\r\n\t\t\t\t\t\t\t\t\t\t) AS R\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}ratings` Ra ON ( Ra.rid = R.ratingid )\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}recommendation_relations`rRT ON ( rRT.recid = R.recid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tags` T ON ( T.tid = rRT.relid AND rRT.type='T' )\r\n\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}tag_groups` TG ON ( TG.tgid = T.tgid )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}characters` Ch ON ( Ch.charid = rRT.relid AND rRT.type = 'CH' )\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `{$new}categories` Cat ON ( Cat.cid = rRT.relid AND rRT.type = 'CA' )\r\n\t\t\t\t\t\t\t\t)AS SELECT_OUTER\r\n\t\t\t\t\t\t\t\tGROUP BY recid ORDER BY recid ASC;");
if (0 < ($count = sizeof($dataIn))) {
foreach ($dataIn as $item) {
$fw->db5->exec("UPDATE `{$new}recommendations` SET \r\n\t\t\t\t\t`cache_tags`\t\t= :tagblock,\r\n\t\t\t\t\t`cache_characters`\t= :characterblock,\r\n\t\t\t\t\t`cache_categories`\t= :categoryblock,\r\n\t\t\t\t\t`cache_rating`\t\t= :rating\r\n\t\t\t\tWHERE recid = {$item['recid']} ;", [':tagblock' => json_encode(upgradetools::cleanResult($item['tagblock'])), ':characterblock' => json_encode(upgradetools::cleanResult($item['characterblock'])), ':categoryblock' => json_encode(upgradetools::cleanResult($item['categoryblock'])), ':rating' => json_encode(explode(",", $item['rating']))]);
}
$tracking->items = $tracking->items + $count;
$tracking->save();
}
if ($count == 0 or $count < $limit) {
// There was either nothing to be done, or there are no elements left for the next run
$tracking->success = 2;
$tracking->save();
}
}
function steps()
{
if (null !== $this->fw->get('resume')) {
$this->fw->set('content', Template::instance()->render('resume.htm'));
return TRUE;
}
// See if the DB connection has been set up and checked, if not force to config
if (empty($this->fw['installerCFG.test'])) {
$this->fw->reroute('@config');
}
// $this->fw->get('PARAMS.step')
$options = array(\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_PERSISTENT => TRUE, \PDO::MYSQL_ATTR_COMPRESS => TRUE);
try {
$this->fw->db3 = new \DB\SQL($this->fw['installerCFG.db3.dsn'], $this->fw['installerCFG.db3.user'], $this->fw['installerCFG.db3.pass'], $options);
} catch (PDOException $e) {
$this->error($e->getMessage());
return FALSE;
}
try {
$this->fw->db5 = new \DB\SQL($this->fw['installerCFG.db5.dsn'], $this->fw['installerCFG.db5.user'], $this->fw['installerCFG.db5.pass'], $options);
} catch (PDOException $e) {
$this->error($e->getMessage());
return FALSE;
}
switch ($this->fw->get('PARAMS.step')) {
case 0:
$this->fw->set('content', upgradetools::sitedata());
break;
case 1:
$this->fw->set('content', upgradetools::optional());
break;
case 2:
$this->fw->set('content', upgradetools::newTables());
break;
case 3:
$this->fw->set('content', upgradetools::processJobs());
break;
/*
case 4:
$this->fw->set('content', upgradetools::workJobs() );
break;
case 5:
$this->fw->set('content', upgradetools::buildCache() );
break;
case 6:
$this->fw->set('content', upgradetools::processChapters() );
break;
*/
/*
case 4:
$this->fw->set('content', upgradetools::workJobs() );
break;
case 5:
$this->fw->set('content', upgradetools::buildCache() );
break;
case 6:
$this->fw->set('content', upgradetools::processChapters() );
break;
*/
case 4:
$this->fw->set('content', upgradetools::buildConfig());
break;
case 5:
$this->fw->set('content', upgradetools::moveFiles());
break;
default:
$this->fw->reroute('@steps(@step=0)');
break;
}
}
