/** * Called when migrating up. */ public function up() { dbexec('delete from jos_components where `option` IN ("com_installer","com_bazaar","com_tagmeta")'); dbexec('delete from jos_components where `option` IS NULL OR `option` = ""'); dbexec('update jos_components set admin_menu_link ="" where `option` IN ("com_search","com_todos","com_pages","com_html","com_invites")'); ///remove privacy_read_mode. some installation may still have it try { dbexec('alter table jos_anahita_nodes drop column `privacy_read_mode`'); } catch (Exception $e) { } dbexec('DROP TABLE IF EXISTS `jos_core_log_items`'); dbexec('DROP TABLE IF EXISTS `jos_core_log_searches`'); dbexec('DROP TABLE IF EXISTS `jos_stats_agents`'); dbexec('DROP TABLE IF EXISTS `jos_tagmeta`'); dbexec('DROP TABLE IF EXISTS `jos_migration_backlinks`'); dbexec('DROP TABLE IF EXISTS `jos_migrations`'); dbexec("DELETE FROM `jos_modules_menu` WHERE `moduleid` IN (SELECT `id` FROM `jos_modules` WHERE `module` IN ('mod_bazaar','mod_footer','mod_login','mod_rokquicklinks'))"); dbexec("DELETE FROM `jos_modules` WHERE `module` IN ('mod_bazaar','mod_footer','mod_login','mod_rokquicklinks')"); $people = dbfetch('select id,person_username AS username,person_userid AS userid from jos_anahita_nodes where type like "ComActorsDomainEntityActor,ComPeopleDomainEntityPerson,com:people.domain.entity.person" and person_username NOT REGEXP "^[A-Za-z0-9][A-Za-z0-9_-]*$"'); foreach ($people as $person) { $username = $person['username']; $clean = $username = preg_replace('/(\\s|\\.|(@\\w+))+/', '', $username); //add a randome number until the username becomes unique while (dbexists("select id from jos_users where username like '{$username}'")) { $username = $clean . rand(0, 100); } dbexec("update jos_anahita_nodes set person_username = '******' where id = {$person['id']}"); dbexec("update jos_users set username = '******' where id = {$person['userid']}"); } }
/** * Called when migrating up. */ public function up() { $timeThen = microtime(true); dbexec('DELETE FROM #__nodes WHERE `type` LIKE \'%ComBaseDomainEntityComment%\' AND `parent_type` = \'com:todos.domain.entity.milestone\' '); dbexec('DELETE FROM #__nodes WHERE `type` LIKE \'%com:todos.domain.entity.milestone\' '); dbexec('DELETE FROM #__edges WHERE `node_b_type` LIKE \'%com:todos.domain.entity.milestone\' '); dbexec('DROP TABLE #__todos_milestones'); //clearing todolists from the data $todolists = dbfetch('SELECT `id`, `parent_id`, `alias` FROM #__nodes WHERE `type` LIKE \'%com:todos.domain.entity.todolist\' '); foreach ($todolists as $todolist) { $terms = explode('-', $todolist['alias']); foreach ($terms as $index => $value) { if (strlen($value) < 3) { unset($terms[$index]); } } $todos = KService::get('com://site/todos.domain.entity.todo')->getRepository()->getQuery()->disableChain()->where('parent_id = ' . $todolist['id'])->fetchSet(); foreach ($todos as $todo) { foreach ($terms as $term) { if (strlen($term) > 3) { dboutput($term . ', '); $todo->set('parent_id', 0)->set('description', $todo->description . ' #' . trim($term))->addHashtag($term)->save(); } } } } dbexec('DELETE FROM #__nodes WHERE `type` LIKE \'%com:todos.domain.entity.todolist\' '); //clear stories dbexec('DELETE FROM #__nodes WHERE `story_object_type` = \'com:todos.domain.entity.todolist\' OR `story_object_type` = \'com:todos.domain.entity.milestone\' '); dbexec('DROP TABLE #__todos_todolists'); $timeDiff = microtime(true) - $timeThen; dboutput("TIME: ({$timeDiff})" . "\n"); }
/** * Called when migrating up */ public function up() { $timeThen = microtime(true); //converting the old boards as hashtags $boards = dbfetch('SELECT `id`, `alias` FROM #__anahita_nodes WHERE `type` LIKE \'%com:topics.domain.entity.board\' '); foreach ($boards as $board) { $terms = explode('-', $board['alias']); foreach ($terms as $index => $value) { if (strlen($value) < 3) { unset($terms[$index]); } } $topics = KService::get('com://site/topics.domain.entity.topic')->getRepository()->getQuery()->disableChain()->where('parent_id = ' . $board['id'])->fetchSet(); foreach ($topics as $topic) { foreach ($terms as $term) { if (strlen($term) > 3) { dboutput($term . ', '); $topic->set('description', $topic->description . ' #' . trim($term))->addHashtag($term)->save(); } } } } dbexec('UPDATE #__anahita_nodes SET `parent_id` = 0 WHERE `type` LIKE \'%com:topics.domain.entity.topic\''); dbexec('DELETE FROM #__anahita_nodes WHERE `type` LIKE \'%com:topics.domain.entity.board\''); dbexec('DELETE FROM #__anahita_edges WHERE `node_b_type` LIKE \'%com:topics.domain.entity.board\''); dbexec('DROP TABLE #__topics_boards'); $timeDiff = microtime(true) - $timeThen; dboutput("TIME: ({$timeDiff})" . "\n"); }
/** * Called when migrating up */ public function up() { $timeThen = microtime(true); //some legacy cleanup $legacyTables = array('categories', 'content', 'content_frontpage', 'core_log_items', 'migration_backlinks', 'migrations', 'sections', 'stats_agents', 'tagmeta', 'core_log_searches'); foreach ($legacyTables as $legacyTable) { dbexec('DROP TABLE IF EXISTS #__' . $legacyTable); } //delete a legacy record dbexec('DELETE FROM #__components WHERE `option` = \'com_mailto\' '); //add the hashtag contentfilter dbexec('INSERT INTO #__plugins (name,element,folder,iscore) VALUES (\'Hashtag\', \'hashtag\',\'contentfilter\',1)'); //create the fields required for creating hashtag nodes dbexec('ALTER TABLE #__anahita_nodes DROP COLUMN `tag_count`'); dbexec('ALTER TABLE #__anahita_nodes DROP COLUMN `tag_ids`'); //install the hashtag related extensions dbexec('INSERT INTO #__components (`name`,`link`,`option`,`iscore`,`enabled`) VALUES (\'Hashtags\',\'option=com_hashtags\',\'com_hashtags\',1,1)'); $ids = array(); //fetch only the nodes that contain something that resembels a hashtag $query_regexp = 'body REGEXP \'#([^0-9_\\s\\W].{2,})\''; dboutput("\nActors' Hashtags\n"); //extracting hashtag terms from actors $ids = dbfetch('SELECT id FROM #__anahita_nodes WHERE type LIKE \'ComActorsDomainEntityActor%\' AND ' . $query_regexp); foreach ($ids as $id) { $entity = KService::get('com://site/actors.domain.entity.actor')->getRepository()->getQuery()->disableChain()->fetch($id); $hashtagTerms = $this->extractHashtagTerms($entity->description); foreach ($hashtagTerms as $term) { dboutput($term . ', '); $entity->addHashtag($term)->save(); } } dboutput("\nComments' hashtags\n"); //extracting hashtag terms from comments $ids = dbfetch('SELECT id FROM #__anahita_nodes WHERE type LIKE \'ComBaseDomainEntityComment%\' AND ' . $query_regexp); foreach ($ids as $id) { $entity = KService::get('com://site/base.domain.entity.comment')->getRepository()->getQuery()->disableChain()->fetch($id); $hashtagTerms = $this->extractHashtagTerms($entity->body); foreach ($hashtagTerms as $term) { dboutput($term . ', '); $entity->addHashtag($term)->save(); } } dboutput("\nMedia's Hashtags\n"); //extracting hashtag terms from mediums: notes, topics, pages, and todos $query = 'SELECT id FROM #__anahita_nodes WHERE ' . $query_regexp . ' AND ( ' . 'type LIKE \'%com:notes.domain.entity.note\' ' . 'OR type LIKE \'%com:topics.domain.entity.topic\' ' . 'OR type LIKE \'%com:photos.domain.entity.photo\' ' . 'OR type LIKE \'%com:photos.domain.entity.set\' ' . 'OR type LIKE \'%com:pages.domain.entity.page\' ' . 'OR type LIKE \'%com:todos.domain.entity.todo\' ' . ' ) '; $ids = dbfetch($query); foreach ($ids as $id) { $entity = KService::get('com://site/medium.domain.entity.medium')->getRepository()->getQuery()->disableChain()->fetch($id); $hashtagTerms = $this->extractHashtagTerms($entity->description); foreach ($hashtagTerms as $term) { dboutput($term . ', '); $entity->addHashtag($term)->save(); } } dbexec('UPDATE #__plugins SET published = 1 WHERE element = \'hashtag\''); $timeDiff = microtime(true) - $timeThen; dboutput("TIME: ({$timeDiff})" . "\n"); }
/** * Called when migrating up */ public function up() { //looks like these two didn't work in previous migrations dbexec("DROP TABLE #__content_rating"); dbexec("DELETE FROM #__components WHERE `option` IN ('com_media', 'com_menus', 'com_modules')"); //add github gist plugin dbexec("INSERT INTO `#__plugins` (`id`, `name`, `element`, `folder`, `access`, `ordering`, `published`, `iscore`, `client_id`, `checked_out`, `checked_out_time`, `params`) VALUES (49, 'Content Filter - GithubGist', 'gist', 'contentfilter', 0, 0, 0, 0, 0, 0, '0000-00-00 00:00:00', '')"); //remove the syntax plugin dbexec("DELETE FROM #__plugins WHERE `element` IN ('syntax', 'ptag') "); //UTF-8 conversions dbexec("ALTER DATABASE CHARACTER SET utf8"); dbexec("ALTER TABLE #__anahita_edges CHARACTER SET utf8"); dbexec("ALTER TABLE #__anahita_nodes CHARACTER SET utf8"); dbexec("ALTER TABLE #__anahita_nodes CHANGE name name VARBINARY(255)"); dbexec("ALTER TABLE #__anahita_nodes CHANGE name name VARCHAR(255) CHARACTER SET utf8"); dbexec("ALTER TABLE #__anahita_nodes CHANGE alias alias VARBINARY(255)"); dbexec("ALTER TABLE #__anahita_nodes CHANGE alias alias VARCHAR(255) CHARACTER SET utf8"); dbexec("ALTER TABLE #__anahita_nodes CHANGE body body MEDIUMBLOB"); dbexec("ALTER TABLE #__anahita_nodes CHANGE body body MEDIUMTEXT CHARACTER SET utf8"); dbexec("ALTER TABLE #__anahita_nodes CHANGE person_given_name person_given_name VARBINARY(255)"); dbexec("ALTER TABLE #__anahita_nodes CHANGE person_given_name person_given_name VARCHAR(255) CHARACTER SET utf8"); dbexec("ALTER TABLE #__anahita_nodes CHANGE person_family_name person_family_name VARBINARY(255)"); dbexec("ALTER TABLE #__anahita_nodes CHANGE person_family_name person_family_name VARCHAR(255) CHARACTER SET utf8"); dbexec("ALTER TABLE #__migrator_migrations CHARACTER SET utf8"); dbexec("ALTER TABLE #__migrator_versions CHARACTER SET utf8"); dbexec("ALTER TABLE #__opensocial_profiles CHARACTER SET utf8"); dbexec("ALTER TABLE #__opensocial_profiles CHARACTER SET utf8"); //move these to related components dbexec("ALTER TABLE #__invites_tokens CHARACTER SET utf8"); dbexec("ALTER TABLE #__opensocial_profiles CHARACTER SET utf8"); dbexec("ALTER TABLE #__subscriptions_coupons CHARACTER SET utf8"); dbexec("ALTER TABLE #__subscriptions_packages CHARACTER SET utf8"); dbexec("ALTER TABLE #__subscriptions_transactions CHARACTER SET utf8"); dbexec("ALTER TABLE #__subscriptions_vats CHARACTER SET utf8"); dbexec("ALTER TABLE #__todos_todos CHARACTER SET utf8"); dbexec("ALTER TABLE #__topics_topics CHARACTER SET utf8"); dbexec("ALTER TABLE #__users CHARACTER SET utf8"); dbexec("ALTER TABLE #__users CHANGE name name VARBINARY(255)"); dbexec("ALTER TABLE #__users CHANGE name name VARCHAR(255) CHARACTER SET utf8"); //updating comments $timeThen = microtime(true); $db = KService::get('koowa:database.adapter.mysqli'); //change comment formats from html to string $entities = dbfetch('SELECT id, body FROM #__anahita_nodes WHERE type LIKE "ComBaseDomainEntityComment%" '); dboutput("Updating comments. This WILL take a while ...\n"); foreach ($entities as $entity) { $id = $entity['id']; $body = strip_tags($entity['body']); $db->update('anahita_nodes', array('body' => $body), ' WHERE id=' . $id); } dboutput("Comments updated!\n"); $timeDiff = microtime(true) - $timeThen; dboutput("TIME: ({$timeDiff})" . "\n"); }
/** * Called when migrating up. */ public function up() { $timeThen = microtime(true); $db = KService::get('koowa:database.adapter.mysqli'); //change todo formats from html to string $entities = dbfetch('SELECT id, body FROM #__nodes WHERE type LIKE "%com:todos.domain.entity.todo" '); foreach ($entities as $entity) { $id = $entity['id']; $body = strip_tags($entity['body']); $db->update('nodes', array('body' => $body), ' WHERE id=' . $id); } $timeDiff = microtime(true) - $timeThen; dboutput("TIME: ({$timeDiff})" . "\n"); }
/** * Called when migrating up */ public function up() { //fetch duplicate rows $duplicate_rows = dbfetch("SELECT *, COUNT(*) AS count FROM `#__edges` GROUP BY `type`, `node_a_id`, `node_a_type`, `node_b_id`, `node_b_type` HAVING count > 1"); //delete duplicate rows foreach ($duplicate_rows as $row) { dbexec('DELETE FROM `#__edges` WHERE `type`=\'' . $row['type'] . '\' AND `node_a_id`=\'' . $row['node_a_id'] . '\' AND `node_a_type`=\'' . $row['node_a_type'] . '\' AND `node_b_id`=\'' . $row['node_b_id'] . '\' AND `node_b_type`=\'' . $row['node_b_type'] . '\' '); } //add unique constraint to columns dbexec('ALTER TABLE `#__edges` ADD CONSTRAINT `uc_edge` UNIQUE(`type`, `node_a_id`, `node_a_type`, `node_b_id`, `node_b_type`)'); //insert previously duplicate rows once each foreach ($duplicate_rows as $row) { unset($row['id']); unset($row['count']); dbinsert('edges', $row); } }
/** * Called when migrating up */ public function up() { $timeThen = microtime(true); $db = KService::get('koowa:database.adapter.mysqli'); dboutput("Updating Topics. This may take a while ...\n"); //Use p tags instead of inlines for topics $entities = dbfetch('SELECT id, body FROM #__anahita_nodes WHERE type LIKE "%com:topics.domain.entity.topic" '); foreach ($entities as $entity) { $id = $entity['id']; $entity['body'] = strip_tags($entity['body'], '<i><b><h1><h2><h3><h4><ul><ol><li><blockquote><pre>'); $body = preg_replace('/\\n(\\s*\\n)+/', "</p>\n<p>", $entity['body']); $body = '<p>' . $body . '</p>'; $db->update('anahita_nodes', array('body' => $body), ' WHERE id=' . $id); } dboutput("Topics updated!\n"); $timeDiff = microtime(true) - $timeThen; dboutput("TIME: ({$timeDiff})" . "\n"); }
function gettablelocal($pfad, $database, $db, $dbid, $bemerk) { $dbget = dbopen($pfad, $database); $qrydel = "DELETE FROM tbltable WHERE fldid_database=" . $dbid; $db->exec($qrydel); $sql = "SELECT name FROM sqlite_master WHERE type='table'"; $results = dbquery($pfad, $dbget, $sql); $anz = 0; $dbrowarr = array(); while ($row = dbfetch($pfad, $results)) { $menge = array_push($dbrowarr, $row); $anz = $anz + 1; } for ($i = 0; $i < $anz; $i++) { $qryins = "INSERT INTO tbltable (fldid_database,fldbez,fldtyp) VALUES (" . $dbid . ",'" . $dbrowarr[$i]['name'] . "','')"; //echo $qryins."<br>"; $db->exec($qryins); } echo "<div class='alert alert-success'>"; echo $anz . " Tables ausgelesen für " . $bemerk . "."; echo "</div>"; }
<?php include "bootstrapfunc.php"; include "dbtool.php"; include "gettablestrucfunc.php"; bootstraphead(); bootstrapbegin("Get Tables<br>"); $menu = $_GET['menu']; $dbid = $_GET['dbid']; echo "<a href='showtab.php?menu=" . $menu . "&dbid=" . $dbid . "' class='btn btn-primary btn-sm active' role='button'>Zurück</a> "; $db = dbopen('../', '../data/mysqlitesync.db'); $sql = "SELECT * FROM tbldatabase WHERE fldindex=" . $dbid; $results = $db->query($sql); if ($row = dbfetch('../', $results)) { $database = $row['fldbez']; $bemerk = $row['fldbemerk']; $pfad = $row['fldpfad']; $idsel = $row['fldid_select']; //echo $pfad.",".$database."=pfad,database<br>"; } $status = $_POST['status']; if ($status == "empfangen") { $datcnt = $_POST['datcnt']; $bemerk = ""; gettableempfangen($datcnt, $bemerk); } else { if ($idsel == 1) { gettablelocal($pfad, $database, $db, $dbid, $bemerk); } else { echo "<div class='alert alert-warning'>"; echo "Remote-Funktion gestartet.";
/** * Returns whether there at least one row with select query * * @param string $select The Select query * * @return boolean */ function dbexists($select) { return !is_null(dbfetch($select, KDatabase::FETCH_FIELD)); }
function dbcollection($query, $start = 0, $count = 20) { $resrows = array(); $ii = 0; $result = dbquery($query . " limit %d, %d", $start, $count); while ($result && ($row = dbfetch($result, DB_NUM))) { if (count($row) == 1) { // don't keep arrays $row = array_shift($row); } $resrows[$start + $ii] = $row; $ii++; } return $resrows; }
<?php include "dbtool.php"; $pfad = $_GET['pfad']; $database = $_GET['database']; $bemerk = $_GET['bemerk']; $dbid = $_GET['dbid']; //echo $pfad.",".$database."pfad,database<br>"; $dbget = dbopen($pfad, $database); $sql = "SELECT name FROM sqlite_master WHERE type='table'"; $results = dbquery($pfad, $dbget, $sql); $anz = 0; $dbrowarr = array(); while ($row = dbfetch($pfad, $results)) { $menge = array_push($dbrowarr, $row); $anz = $anz + 1; } echo "<div class='alert alert-success'>"; echo $anz . " Tables ausgelesen für " . $bemerk . "."; echo "</div>"; $website = "http://localhost:8080/own/mysqlitesync/classes/gettablestruc.php?menu=table&dbid=" . $dbid; echo "<form class='form-horizontal' method='post' action='" . $website . "'>"; echo "<input type='hidden' name='status' value='empfangen'/>"; for ($i = 0; $i < $anz; $i++) { echo "<input type='hidden' name='dbid" . $i . "' value='" . $dbid . "'/>"; echo "<input type='hidden' name='name" . $i . "' value='" . $dbrowarr[$i]['name'] . "'/>"; } echo "<input type='hidden' name='datcnt' value='" . $anz . "'/>"; echo "<dd><input type='submit' value='Get Table' /></dd>"; echo "</form>";
include "classes/checkupgrade.php"; check_version(); $db = dbopen('', 'data/' . $database); $parentid = $_GET['id']; if ($parentid == "") { $parentid = '0'; } echo "<div>"; echo "<h1 align='center'>" . $headline . "</h1>"; if ($admin == "J") { $sql = "SELECT * FROM tblmenu_liste WHERE fldview='J' AND fldid_parent='" . $parentid . "' ORDER BY fldsort"; } else { $sql = "SELECT * FROM tblmenu_liste WHERE fldview='J' AND fldadmin='N' AND fldid_parent='" . $parentid . "' ORDER BY fldsort"; } $results = dbquery('', $db, $sql); while ($row = dbfetch('', $results)) { if ($row['fldmenu'] == "SUBMENU") { echo "<a href='index.php?id=" . $row['fldindex'] . "&lastid=" . $parentid . "' class='btn btn-default btn-lg btn-block glyphicon " . $row['fldglyphicon'] . "' role='button'> " . $row['fldbez'] . "</a>"; } else { if ($row['fldlink'] != "") { echo "<a href='" . $row['fldlink'] . "?id=" . $parentid . "' class='btn btn-default btn-lg btn-block glyphicon " . $row['fldglyphicon'] . "' role='button'> " . $row['fldbez'] . "</a>"; } else { echo "<a href='classes/showtab.php?menu=" . $row['fldmenu'] . "&id=" . $parentid . "' class='btn btn-default btn-lg btn-block glyphicon " . $row['fldglyphicon'] . "' role='button'> " . $row['fldbez'] . "</a>"; } } } if ($parentid != "0") { echo "<a href='index.php?id=" . $_GET['lastid'] . "' class='btn btn-default btn-lg btn-block glyphicon glyphicon-list' role='button'> zurück</a>"; } echo "</div>"; }