[wp-trac] [WordPress Trac] #15332: dbDelta($query) - do not create view
WordPress Trac
wp-trac at lists.automattic.com
Fri Nov 12 15:03:05 UTC 2010
#15332: dbDelta($query) - do not create view
----------------------------------------------+-----------------------------
Reporter: christian_gnoth | Owner:
Type: feature request | Status: reopened
Priority: normal | Milestone: Awaiting Triage
Component: Database | Version: 3.0.1
Severity: normal | Resolution:
Keywords: reporter-feedback, needs-usecase |
----------------------------------------------+-----------------------------
Comment(by christian_gnoth):
if have the following function:
{{{
function bbnuke_db_delta()
{
global $wpdb;
if ($wpdb->supports_collation())
{
if ( ! empty($wpdb->charset) )
$charset_collate = "DEFAULT CHARACTER SET $wpdb->charset";
if ( ! empty($wpdb->collate) )
$charset_collate .= " COLLATE $wpdb->collate";
}
$query = "CREATE TABLE `" . $wpdb->prefix . "baseballNuke_boxscores` (
`gameID` int(11) NOT NULL default '0',
`v1` int(11) default NULL,
`v2` int(11) default NULL,
`v3` int(11) default NULL,
`v4` int(11) default NULL,
`v5` int(11) default NULL,
`v6` int(11) default NULL,
`v7` int(11) default NULL,
`v8` int(11) default NULL,
`v9` int(11) default NULL,
`h1` int(11) default NULL,
`h2` int(11) default NULL,
`h3` int(11) default NULL,
`h4` int(11) default NULL,
`h5` int(11) default NULL,
`h6` int(11) default NULL,
`h7` int(11) default NULL,
`h8` int(11) default NULL,
`h9` int(11) default NULL,
`vhits` int(11) default NULL,
`vruns` int(11) default NULL,
`verr` int(11) default NULL,
`hhits` int(11) default NULL,
`hruns` int(11) default NULL,
`herr` int(11) default NULL,
`notes` longtext,
PRIMARY KEY (`gameID`)
) ENGINE=MyISAM " . $charset_collate . ";";
dbDelta($query);
$query = "CREATE TABLE `" . $wpdb->prefix . "baseballNuke_locations` (
`fieldname` mediumtext NOT NULL,
`directions` longtext
) ENGINE=MyISAM " . $charset_collate . ";";
dbDelta($query);
$query = "CREATE TABLE `" . $wpdb->prefix . "baseballNuke_players` (
`playerID` int(11) NOT NULL auto_increment,
`teamName` varchar(255) default NULL,
`firstname` varchar(255) default NULL,
`middlename` varchar(255) default NULL,
`lastname` varchar(255) default NULL,
`positions` mediumtext,
`bats` tinytext,
`throws` tinytext,
`height` int(11) default NULL,
`weight` int(11) default NULL,
`address` varchar(255) default NULL,
`city` varchar(255) default NULL,
`state` tinytext,
`zip` int(11) default NULL,
`homePhone` varchar(255) default NULL,
`workPhone` varchar(255) default NULL,
`cellphone` varchar(255) default NULL,
`jerseyNum` int(10) unsigned default NULL,
`picLocation` varchar(255) default NULL,
`season` varchar(20) NOT NULL default '',
`profile` text,
`bdate` date default NULL,
`email` varchar(255) default NULL,
PRIMARY KEY (`playerID`,`season`)
) ENGINE=MyISAM AUTO_INCREMENT=141 " . $charset_collate . ";";
dbDelta($query);
$query = "CREATE TABLE `" . $wpdb->prefix . "baseballNuke_schedule` (
`gameID` int(11) NOT NULL auto_increment,
`visitingTeam` varchar(255) default NULL,
`homeTeam` varchar(255) default NULL,
`gameDate` date default NULL,
`field` mediumtext,
`umpire` varchar(255) default NULL,
`homeScore` int(11) default NULL,
`visitScore` int(11) default NULL,
`gameTime` time default NULL,
`notes` varchar(255) default NULL,
`type` text,
`season` varchar(20) default NULL,
PRIMARY KEY (`gameID`)
) ENGINE=MyISAM AUTO_INCREMENT=748 " . $charset_collate . ";";
dbDelta($query);
$query = "CREATE TABLE `" . $wpdb->prefix . "baseballNuke_settings` (
`defaultTeam` varchar(255) default NULL,
`defaultSeason` varchar(4) default NULL,
`displayMenu` char(1) default '1',
`ID` tinyint(4) NOT NULL auto_increment,
`version` varchar(20) NOT NULL default '',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=2 " . $charset_collate . ";";
dbDelta($query);
$query = "CREATE TABLE `" . $wpdb->prefix . "baseballNuke_stats` (
`gameID` int(11) NOT NULL default '0',
`playerID` int(11) NOT NULL default '0',
`battOrd` int(11) default NULL,
`pitchOrd` int(11) default NULL,
`baAB` int(11) default NULL,
`ba1b` int(11) default NULL,
`ba2b` int(11) default NULL,
`ba3b` int(11) default NULL,
`baHR` int(11) default NULL,
`baRBI` int(11) default NULL,
`baBB` int(11) default NULL,
`baK` int(11) default NULL,
`baSB` int(11) default NULL,
`piWin` int(11) default NULL,
`piLose` int(11) default NULL,
`piSave` int(11) default NULL,
`piIP` float(3,2) default NULL,
`piHits` int(11) default NULL,
`piRuns` int(11) default NULL,
`piER` int(11) default NULL,
`piWalks` int(11) default NULL,
`piSO` int(11) default NULL,
`baRuns` int(11) default NULL,
`baRE` int(11) default '0',
`baFC` int(11) default '0',
`baHP` int(11) NOT NULL default '0',
`baLOB` int(11) NOT NULL default '0',
`fiPO` int(11) NOT NULL default '0',
`fiA` int(11) NOT NULL default '0',
`fiE` int(11) NOT NULL default '0'
) ENGINE=MyISAM " . $charset_collate . ";";
dbDelta($query);
$query = "CREATE TABLE `" . $wpdb->prefix . "baseballNuke_teams` (
`teamname` varchar(255) NOT NULL default '',
`wins` int(11) default NULL,
`losses` int(11) default NULL,
`winPer` float default NULL,
`season` varchar(20) NOT NULL default '',
PRIMARY KEY (`teamname`,`season`)
) ENGINE=MyISAM " . $charset_collate . ";";
dbDelta($query);
$wpdb->query = "DROP VIEW `" . $wpdb->prefix . "baseballNuke_batTotals`
";
$query = "CREATE VIEW `" . $wpdb->prefix . "baseballNuke_batTotals` AS
select `" . $wpdb->prefix . "baseballNuke_players`.`playerID` AS
`playerID`,`" . $wpdb->prefix . "baseballNuke_players`.`lastname` AS
`lastname`,`" . $wpdb->prefix . "baseballNuke_players`.`firstname` AS
`firstname`,`" . $wpdb->prefix . "baseballNuke_players`.`middlename` AS
`middlename`,`" . $wpdb->prefix . "baseballNuke_players`.`jerseyNum` AS
`jerseyNum`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`baRuns`) AS
`baTotRuns`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`baAB`) AS
`baTotAB`,sum((((`" . $wpdb->prefix . "baseballNuke_stats`.`ba1b` + `" .
$wpdb->prefix . "baseballNuke_stats`.`ba2b`) + `" . $wpdb->prefix .
"baseballNuke_stats`.`ba3b`) + `" . $wpdb->prefix .
"baseballNuke_stats`.`baHR`)) AS `baTotH`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`ba1b`) AS `baTot1b`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`ba2b`) AS `baTot2b`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`ba3b`) AS `baTot3b`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`baHR`) AS `baTotHR`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`baRE`) AS `baTotRE`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`baFC`) AS `baTotFC`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`baHP`) AS `baTotHP`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`baRBI`) AS `baTotRBI`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`baBB`) AS `baTotBB`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`baK`) AS `baTotK`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`baLOB`) AS `baTotLOB`,sum(`" . $wpdb->prefix .
"baseballNuke_stats`.`baSB`) AS `baTotSB` from ((`" . $wpdb->prefix .
"baseballNuke_players` join `" . $wpdb->prefix . "baseballNuke_stats`)
join `" . $wpdb->prefix . "baseballNuke_schedule`) where ((`" .
$wpdb->prefix . "baseballNuke_players`.`playerID` = `" . $wpdb->prefix .
"baseballNuke_stats`.`playerID`) and (year(`" . $wpdb->prefix .
"baseballNuke_schedule`.`gameDate`) = year(now())) and (`" . $wpdb->prefix
. "baseballNuke_stats`.`gameID` = `" . $wpdb->prefix .
"baseballNuke_schedule`.`gameID`) and (`" . $wpdb->prefix .
"baseballNuke_players`.`season` = year(now()))) group by `" .
$wpdb->prefix . "baseballNuke_players`.`playerID`;";
mysql_query($query);
$wpdb->query = "DROP VIEW `" . $wpdb->prefix .
"baseballNuke_pitchTotals` ";
$query = "CREATE VIEW `" . $wpdb->prefix . "baseballNuke_pitchTotals` AS
select `" . $wpdb->prefix . "baseballNuke_players`.`playerID` AS
`playerID`,`" . $wpdb->prefix . "baseballNuke_players`.`lastname` AS
`lastname`,`" . $wpdb->prefix . "baseballNuke_players`.`firstname` AS
`firstname`,`" . $wpdb->prefix . "baseballNuke_players`.`middlename` AS
`middlename`,`" . $wpdb->prefix . "baseballNuke_players`.`jerseyNum` AS
`jerseyNum`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`piWin`) AS
`piTotWin`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`piLose`) AS
`piTotLose`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`piSave`) AS
`piTotSave`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`piIP`) AS
`piTotIP`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`piHits`) AS
`piTotHits`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`piRuns`) AS
`piTotRuns`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`piER`) AS
`piTotER`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`piWalks`) AS
`piTotWalks`,sum(`" . $wpdb->prefix . "baseballNuke_stats`.`piSO`) AS
`piTotSO`,year(now()) AS `year` from ((`" . $wpdb->prefix .
"baseballNuke_stats` join `" . $wpdb->prefix . "baseballNuke_players`)
join `" . $wpdb->prefix . "baseballNuke_schedule`) where ((`" .
$wpdb->prefix . "baseballNuke_schedule`.`gameID` = `" . $wpdb->prefix .
"baseballNuke_stats`.`gameID`) and (`" . $wpdb->prefix .
"baseballNuke_stats`.`playerID` = `" . $wpdb->prefix .
"baseballNuke_players`.`playerID`) and (year(`" . $wpdb->prefix .
"baseballNuke_schedule`.`gameDate`) = year(now())) and (`" . $wpdb->prefix
. "baseballNuke_players`.`season` = year(now())) and (`" . $wpdb->prefix .
"baseballNuke_stats`.`piIP` > 0)) group by `" . $wpdb->prefix .
"baseballNuke_players`.`playerID`;";
mysql_query($query);
$wpdb->flush();
return;
}
}}}
and a register_activation_hook function:
{{{
function bbnuke_plugin_activation()
{
global $wpdb;
// check if tables exists and create
bbnuke_db_delta();
// check if tables are empty and fill with default values
bbnuke_check_tables();
add_option( 'bbnuke_plugin_options', array(), '', 'no');
bbnuke_set_option_defaults();
return;
}
}}}
the views on the tables can also change with a new version of the plugin.
my workaround is like above to create the views with nativq mysql_query
calls.
--
Ticket URL: <http://core.trac.wordpress.org/ticket/15332#comment:4>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software
More information about the wp-trac
mailing list