WordPress.org

Make WordPress Core

Opened 3 years ago

Closed 3 years ago

#15332 closed feature request (wontfix)

dbDelta($query) - do not create view

Reported by: christian_gnoth Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.0.1
Component: Database Keywords: reporter-feedback, needs-usecase
Focuses: Cc:

Description

during plugin creation I create few tables with dbDelta($query). that is working without problems.
But on the end I create two views on this tables with dbDelta($query) - that is not working. The views are not created.

The sql is ok, manually the create works.

Change History (10)

comment:1 nacin3 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed

dbDelta doesn't handle views.

comment:2 christian_gnoth3 years ago

  • Resolution invalid deleted
  • Status changed from closed to reopened
  • Type changed from defect (bug) to feature request

comment:3 westi3 years ago

  • Cc westi added
  • Component changed from General to Database
  • Keywords reporter-feedback needs-usecase added; dbDelta create view removed
  • Milestone set to Awaiting Triage

This ticket needs more information to be correctly triaged.

  • What are you using the views for.
  • Why do you need dbDelta to support views.
  • How is dbDelta not working - examples of how you are using it?
  • Why is this suitable/required for the core of WordPress?

comment:4 christian_gnoth3 years ago

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.

comment:5 nacin3 years ago

  • Milestone Awaiting Triage deleted
  • Resolution set to wontfix
  • Status changed from reopened to closed

comment:6 christian_gnoth3 years ago

  • Resolution wontfix deleted
  • Status changed from closed to reopened

no need to close this ticket always !!! a simple hint would be enough.

I use the dbdelta function at plugin activation to check if the tables and views of the new release are same to those of the previous release, as this plugin gets extend in future.

the views are used in the plugin to retrieve data.

i use it like the statements aboce - the sql in the $query var and then the function call to dbDelta($query);
the views are not generated - no error messages.

comment:7 nacin3 years ago

  • Resolution set to wontfix
  • Status changed from reopened to closed

There's no need to support views via dbDelta. dbDelta is for altering table schemas. Views are inherently different.

comment:8 christian_gnoth3 years ago

  • Resolution wontfix deleted
  • Status changed from closed to reopened

if the new version of the plugin has a change in the view on the tables - it would be good if dbDelta supports that.

comment:9 dd323 years ago

  • Milestone set to Awaiting Review

All tickets need some kind of milestone.

My thoughts: dbDelta is for table schema's, Do views have a table schema in the same sense as a table? Can you execute ALTER/etc on the view? If so, Then it should be treated the same.

On the other hand, If views are as i remember them, then you'll need to hard-code the SQL regardless, as you need to drop/recreate the view to alter it..

Setting to awaiting review pending punting or closing as out of the scope of the Table Schema dbDelta.

comment:10 dd323 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to wontfix
  • Status changed from reopened to closed

After reviewing http://dev.mysql.com/doc/refman/5.0/en/alter-view.html i've come to the conclusion that, whilst views are nice, As core doesn't currently use them, and it falls outside of the scope of the original intention of dbDelta(), that really, supporting views seems like a wasted effort, Given that the few plugins that require them, can do so with very simple code already.

In the event that using views becomes more common amongst plugins, or core requires them, then that's when we should support them IMO.

As it is, I try to discourage people from using custom tables if the data can somehow be sloted into WordPress's API, in your case, it probably can't be, but until it's a common problem..

Someone can re-open this in a few years if the times change, or it's no longer feasible to continue the manual way.

Note: See TracTickets for help on using tickets.