PHP dbexec函数代码示例

 /**
  * Called when migrating up
  */
 public function up()
 {
     //remove the photo plugin
     dbexec("DELETE FROM #__plugins WHERE `element` = 'access' AND `folder` = 'subscriptions' ");
     //remove the dangling transaction records
     dbexec("DELETE FROM #__subscriptions_transactions WHERE user_id NOT IN (SELECT id FROM #__users)");
 }

 /**
  * 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()
 {
     if (!dbexists('SHOW TABLES LIKE "#__edges"')) {
         dbexec('RENAME TABLE #__anahita_edges TO #__edges');
     }
     dbexec('DELETE FROM #__edges WHERE node_a_id = node_b_id');
 }

 /**
  * Called when migrating up.
  */
 public function up()
 {
     //delete open social table
     dbexec('DROP TABLE #__opensocial_profiles');
     //delete legacy com_cache and com_opensocial
     dbexec('DELETE FROM #__components WHERE `option` = \'com_cache\' OR `option` = \'com_opensocial\' ');
     //some clean up
     dbexec('DELETE FROM #__components WHERE name = "" ');
     //fix broken menu link
     dbexec('UPDATE #__components SET `admin_menu_link` = \'option=com_mailer\' WHERE `option` = \'com_mailer\' ');
     //Delete legacy plugins
     dbexec('DELETE FROM #__plugins WHERE `element` IN ( \'ptag\', \'syntax\', \'opensocial\', \'mtupgrade\', \'usertype\' ) ');
     //remove anahita from nodes and edges table names
     if (!dbexists('SHOW TABLES LIKE "#__nodes"')) {
         dbexec('RENAME TABLE #__anahita_nodes TO #__nodes');
     }
     //add pinned field to the nodes table
     if (!dbexists('SHOW COLUMNS FROM #__nodes LIKE "pinned"')) {
         dbexec('ALTER TABLE #__nodes ADD `pinned` TINYINT(1) NOT NULL DEFAULT \'0\' AFTER `enabled`');
     }
     //add github gist plugin
     dbexec("INSERT INTO `#__plugins` (`name`, `element`, `folder`, `access`, `ordering`, `published`, `iscore`, `client_id`, `checked_out`, `checked_out_time`, `params`) VALUES ('Content Filter - Medium', 'medium', 'contentfilter', 0, 1, 1, 0, 0, 0, '0000-00-00 00:00:00', '')");
     //remove the photo plugin
     dbexec("DELETE FROM #__plugins WHERE `element` = 'photo' ");
     //remove the migrator_migrations table if exists
     dbexec('DROP TABLE #__migrator_migrations');
     //some of the plugins are core plugins
     dbexec("UPDATE jos_plugins SET iscore = 1 WHERE element IN ('joomla', 'gist', 'medium', 'link', 'video', 's3')");
 }

 /**
  * 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 = '{$username}' where id = {$person['id']}");
         dbexec("update jos_users set username = '{$username}' where id = {$person['userid']}");
     }
 }

 /**
  * Called when migrating up
  */
 public function up()
 {
     dbexec('ALTER TABLE #__anahita_nodes
             ADD `cover_filename` VARCHAR(255) NULL AFTER `filesize`,
             ADD `cover_filesize` INT(11) NULL AFTER `cover_filename`,
             ADD `cover_mimetype` VARCHAR(100) NULL AFTER `cover_filesize`');
 }

 /**
  * Called when migrating up
  */
 public function up()
 {
     //remove some legacy plugin records
     dbexec('DELETE FROM #__plugins WHERE folder = \'system\' AND element IN (\'sef\',\'debug\',\'logger\',\'missioncontrol\', \'mtupgrade\', \'tagmeta\')');
     dbexec('DROP TABLE IF EXISTS #__menu');
     dbexec('DROP TABLE IF EXISTS #__menu_types');
     dbexec('DELETE FROM #__modules WHERE `module` IN (\'mod_menu\',\'mod_viewer\') AND `client_id` = 0 ');
 }

 /**
  * 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()
 {
     //delete a legacy record
     dbexec('DELETE FROM #__components WHERE `option` = \'com_content\' ');
     //add the mention tag contentfilter
     dbexec('INSERT INTO #__plugins (`name`,`element`,`folder`,`iscore`,`published`) VALUES (\'Mention\', \'mention\',\'contentfilter\',1,1)');
     //changing the hashtag class name to tag
     dbexec('UPDATE #__anahita_edges SET `type` = REPLACE(`type`, \'ComHashtagsDomainEntityAssociation,com:hashtags.domain.entity.association\', \'ComTagsDomainEntityTag,ComHashtagsDomainEntityTag,com:hashtags.domain.entity.tag\') WHERE type LIKE \'%com:hashtags.domain.entity%\' ');
 }

 /**
  * Called when migrating up
  */
 public function up()
 {
     //add your migration here
     dbexec("ALTER TABLE #__users ENGINE=InnoDB");
     dbexec("ALTER TABLE #__templates_menu ENGINE=InnoDB");
     dbexec("ALTER TABLE #__plugins ENGINE=InnoDB");
     dbexec("ALTER TABLE #__nodes ENGINE=InnoDB");
     dbexec("ALTER TABLE #__components ENGINE=InnoDB");
 }

 /**
  * Called when migrating up
  */
 public function up()
 {
     //Create new fields for com_locations
     dbexec("ALTER TABLE `#__nodes` ADD `geo_address` VARCHAR(255) DEFAULT NULL AFTER `geo_longitude`");
     dbexec("ALTER TABLE `#__nodes` ADD `geo_city` VARCHAR(50) DEFAULT NULL AFTER `geo_address`");
     dbexec("ALTER TABLE `#__nodes` ADD `geo_state_province` VARCHAR(50) DEFAULT NULL AFTER `geo_city`");
     dbexec("ALTER TABLE `#__nodes` ADD `geo_country` VARCHAR(30) DEFAULT NULL AFTER `geo_state_province`");
     dbexec("ALTER TABLE `#__nodes` ADD `geo_postalcode` VARCHAR(15) DEFAULT NULL AFTER `geo_country`");
     //add extension records
     dbexec("INSERT INTO `#__components` (`name`, `link`, `admin_menu_link`, `admin_menu_alt`, `admin_menu_img`, `option`,`iscore`, `enabled`) VALUES ('Locations', 'option=com_locations', 'option=com_locations', 'Locations', 'js/ThemeOffice/component.png', 'com_locations', 1, 1)");
 }

 /**
  * 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");
 }

function TestDB()
{
    $ret = array();
    $query = "SELECT 1 AS demo";
    $result = dbexec($query);
    if (db()->affected_rows) {
        //$ret = $result->fetch_all(MYSQLI_ASSOC);
        while ($row = $result->fetch_assoc()) {
            $ret[] = $row;
        }
    }
    return $ret;
}

 /**
  * Called when migrating up
  */
 public function up()
 {
     dbexec('DROP TABLE IF EXISTS #__invites_invitations');
     $query = 'CREATE TABLE IF NOT EXISTS `#__invites_tokens` (
      `id` bigint(11) unsigned NOT NULL auto_increment,
      `inviter_id` bigint(11) unsigned NOT NULL,
      `service` VARCHAR(20),
      `token` VARCHAR(150),
      `used` TINYINT(20) unsigned default 0,
       PRIMARY KEY  (`id`),
       KEY `inviter_id` (`inviter_id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0';
     dbexec($query);
     //add your migration here
 }