Make WordPress Core

Opened 13 years ago

Last modified 6 years ago

#15332 reopened feature request

dbDelta($query) - do not create view

Reported by: christian_gnoth's profile christian_gnoth Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version: 3.0.1
Component: Database Keywords: needs-unit-tests needs-docs has-patch needs-testing
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.

Attachments (1)

15332.patch (2.0 KB) - added by mordauk 9 years ago.

Download all attachments as: .zip

Change History (17)

#1 @nacin
13 years ago

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

dbDelta doesn't handle views.

#2 @christian_gnoth
13 years ago

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

#3 @westi
13 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?

#4 @christian_gnoth
13 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.

#5 @nacin
13 years ago

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

#6 @christian_gnoth
13 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.

#7 @nacin
13 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.

#8 @christian_gnoth
13 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.

#9 @dd32
13 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.

#10 @dd32
13 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.

#11 @mordauk
9 years ago

  • Keywords changed from reporter-feedback, needs-usecase to reporter-feedback needs-usecase
  • Resolution wontfix deleted
  • Status changed from closed to reopened

It's been 5 years now and I'd like to propose this get re-opened for consideration.

I can't see any good reason for dbDelta() to not support views now, especially since a view is being considered as part of the great taxonomy roadmap.

Knowing that dbDelta() didn't support views would have saved me several hours of debugging just now in one of my plugins.

#12 @boonebgorges
9 years ago

  • Keywords needs-patch needs-unit-tests needs-docs added; reporter-feedback needs-usecase removed
  • Milestone set to Future Release

Yes, I think it's reasonable to work on this.

In the interim, it may be helpful to have better internal docs for dbDelta(), which make it explicit that only a subset of MySQL CREATE syntax is handled (ideally, with an enumeration of this subset).

#13 @mordauk
9 years ago

It might also be nice to have a better return value when attempting to perform an unsupported operation. At the moment it just returns an empty array. Perhaps it could return a WP_Error instead.

@mordauk
9 years ago

#14 @mordauk
9 years ago

  • Keywords has-patch needs-testing added; needs-patch removed

15332.patch is a rough patch that does work. I'm not 100% sure on whether we should be validating the table / columns for view create / replace statements so I have not done anything for that at this time.

This ticket was mentioned in Slack in #docs by morganestes. View the logs.


7 years ago

#16 @domtra
6 years ago

Hi there, I just stumbled upon this ticket and would like to add some thoughts:

Views are a great way to prepare data in a way that makes it easier to query from an external source (WordPress in this case). And I believe that plugins can make some nice use of them. However, I do not believe that there is the need for dbDelta to handle views at all.

Views do not hold any additional data. They are just stored queries. If you want to alter a view and have the original definition in code already, the easiest thing to do is to change the sql code from 'CREATE VIEW' to 'CREATE OR REPLACE VIEW'. There is no need to alter / add / remove specific columns via a separate sql statement.

Maybe this helps in proceeding with this ticket.

Note: See TracTickets for help on using tickets.