Plings Input API - MySQL

From Plings Info

Jump to: navigation, search

MySQL create table instructions

Many people need to create a local data store for Plings data before they can send it to the Plings Input API.

This was something we were messing around with and thought might be a useful resource to others. It's possible that it only gets you 90% of the way there, and you may want to alter data definitions, default values, field sizes, etc for yourself, but it should cover all the fields in the Plings Input API specification (as of 20/04/09). Hopefully it saves you a bit of work.

If you can improve on this then please contact us and let us know. 

CREATE TABLE `venues` (
  `ProviderVenueID` int(11) NOT NULL auto_increment,
  `plingsplace_id` int(11) default NULL,
  `Name` varchar(50) default NULL,
  `BuildingNameNo` varchar(255) default NULL,
  `Postcode` varchar(10) default NULL,
  `ContactForename` varchar(100) default NULL,
  `ContactSurname` varchar(100) default NULL,
  `ContactNumber` varchar(25) default NULL,
  `ContactEmail` varchar(100) default NULL,
  `ContactFax` varchar(30) default NULL,
  `GeoCoordSystem` varchar(50) NOT NULL default 'WGS84DD',
  `GeoCoordX` decimal(13,10) default NULL,
  `GeoCoordY` decimal(13,10) default NULL,
  `Description` varchar(500) default NULL,
  `Website` varchar(250) default NULL,
  `ParkingSpaces` mediumint(9) default NULL,
  `CyclePark` varchar(10) default NULL,
  `DisabledFacilitiesNotes` varchar(500) default NULL,
  `created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `updated_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `user_id` int(11) default NULL,
  PRIMARY KEY  (`ProviderVenueID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=58 ;
 
 
CREATE TABLE `organisations` (
  `DPProviderID` int(11) NOT NULL auto_increment,
  `Name` varchar(50) default NULL,
  `Project Department` varchar(100) default NULL,
  `BuildingNameNo` varchar(70) default NULL,
  `Street` varchar(70) default NULL,
  `Town` varchar(70) default NULL,
  `PostTown` varchar(100) default NULL,
  `County` varchar(100) default NULL,
  `PostCode` varchar(10) default NULL,
  `ContactForename` varchar(100) default NULL,
  `ContactSurname` varchar(100) default NULL,
  `ContactEmail` varchar(150) default NULL,
  `ContactPhone` varchar(20) default NULL,
  `ContactFax` varchar(20) default NULL,
  `Description` varchar(500) default NULL,
  `MinAge` tinyint(2) default NULL,
  `MaxAge` tinyint(2) default NULL,
  `Website` varchar(200) default NULL,
  `created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `updated_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `user_id` int(11) default NULL,
  PRIMARY KEY  (`DPProviderID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=60 ;
 
CREATE TABLE `activity` (
  `ActivitySourceID` int(11) NOT NULL auto_increment,
  `plingsactivityid` int(11) default NULL,
  `Name` varchar(150) NOT NULL,
  `Description` text,
  `Starts` datetime NOT NULL,
  `Ends` datetime NOT NULL,
  `ContactName` varchar(255) NOT NULL,
  `ContactNumber` varchar(255) NOT NULL,
  `ContactEmail` varchar(255) NOT NULL,
  `ContactAddress` varchar(255) NOT NULL,
  `MinAge` tinyint(4) default NULL,
  `MaxAge` tinyint(4) default NULL,
  `Cost` decimal(10,0) default NULL,
  `Categories` varchar(255) NOT NULL,
  `Keywords` varchar(255) NOT NULL,
  `ECMCodes` tinyint(4) default NULL,
  `ActivityTypeCodes` tinyint(4) default NULL,
  `LinkWithActivity` int(11) default NULL,
  `LinkWithActivitySourceID` varchar(255) default NULL,
  `first_submitted` timestamp NOT NULL default '0000-00-00 00:00:00',
  `updated_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `venue_id` int(11) default NULL,
  `organisation_id` int(11) NOT NULL,
  `user_id` tinyint(6) NOT NULL,
  PRIMARY KEY  (`ActivitySourceID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;

Notes

  • This assumes Categories and Keywords are stored as a comma separated list - 250 characters should be long enough but may not be. Also if using Plings Input API - SOAP you may want to send these against a recognised taxonomy (e.g. IPSV), so you might need to build something else in there.
  • ECMCodes stored as a tinyint only allows you to classify against one code, so you might want to change that...
  • Also you might have more data that's worth storing, e.g. venue street name, town, etc - we don't require you to send this to us, but you might want to add it to your tables for your own use.
Retrieved from "http://www.plings.info/wiki/index.php/Plings_Input_API_-_MySQL"
Personal tools