Fixed table definitions and structures to reflect currently used system on appdb.winehq.org. Fixed create scripts to create user with correct permissions for database.

Henti Smith henti at lsd.co.za
Fri Jul 4 01:57:54 CDT 2008


---
 tables/appdb_tables.sql      |  161 ++++++++++++++++++++---------------------
 tables/buglinks.sql          |   18 +++---
 tables/bugzilla_tables.sql   |    2 +-
 tables/create_apidb_base.sql |    2 +-
 tables/create_tables         |    4 +-
 tables/create_user.sql       |    3 +-
 tables/distributions.sql     |   16 ++--
 tables/error_log.sql         |   18 +++---
 tables/maintainers.sql       |   22 +++---
 tables/monitors.sql          |   18 +++---
 tables/prefs_list.sql        |   19 +++---
 tables/testResults.sql       |   42 +++++------
 tables/user_list.sql         |   22 +++---
 tables/user_prefs.sql        |   10 ++--
 tables/user_privs.sql        |    6 +-
 15 files changed, 178 insertions(+), 185 deletions(-)

diff --git a/tables/appdb_tables.sql b/tables/appdb_tables.sql
index 2347ea7..bc2e77e 100644
--- a/tables/appdb_tables.sql
+++ b/tables/appdb_tables.sql
@@ -19,49 +19,48 @@ drop table if exists appNotes;
  * vendor information
  */
 create table vendor (
-       vendorId         int not null auto_increment,
-       vendorName       varchar(100) not null,
-       vendorURL        varchar(200),
-       state           enum('accepted','queued','deleted') NOT NULL default 'accepted',
-       key(vendorId)
+  `vendorId` int(11) NOT NULL auto_increment,
+  `vendorName` varchar(100) NOT NULL default '',
+  `vendorURL` varchar(200) default NULL,
+  `state` enum('accepted','queued','deleted') NOT NULL default 'accepted',
+  KEY `vendorId` (`vendorId`)
 );
 
 
 /*
  * application
  */
-create table appFamily (
-	appId	      int not null auto_increment,
-	appName       varchar(100) not null,
-	vendorId      int not null,
-	keywords      text,
-	description   text,
-	webPage       varchar(100),
-	catId         int,
-	submitTime    datetime NOT NULL,
-	submitterId   int(11) NOT NULL default '0',
-	state         enum('accepted','queued','rejected','deleted') NOT NULL default 'accepted',
-	key(appId)
+CREATE TABLE `appFamily` (
+	  `appId` int(11) NOT NULL auto_increment,
+	  `appName` varchar(100) NOT NULL default '',
+	  `vendorId` int(11) NOT NULL default '0',
+	  `keywords` text,
+	  `description` text,
+	  `webPage` varchar(100) default NULL,
+	  `catId` int(11) default NULL,
+	  `submitTime` datetime NOT NULL default '0000-00-00 00:00:00',
+	  `submitterId` int(11) NOT NULL default '0',
+	  `state` enum('accepted','queued','rejected','deleted') NOT NULL default 'accepted',
+	  KEY `appId` (`appId`)
 );
 
-
 /*
  * a version of an application
  */
 create table appVersion (
-  versionId             int not null auto_increment,
-  appId                 int not null,
-  versionName           varchar(100) not null,
-  description           text,
-  rating    		 text,
-  ratingRelease 	 text,
-  submitTime            datetime NOT NULL,
-  submitterId           int(11) NOT NULL default '0',
-  license		enum('Retail','Open Source','Demo','Shareware','Free to use','Free to use and share'),
-  obsoleteBy            int(11) NOT NULL default '0',
-  state                 enum('accepted','queued','rejected','pending','deleted') NOT NULL default 'accepted',
-  key(versionId),
-  index(appId)
+  `versionId` int(11) NOT NULL auto_increment,
+  `appId` int(11) NOT NULL default '0',
+  `versionName` varchar(100) NOT NULL default '',
+  `description` text,
+  `rating` text,
+  `ratingRelease` text,
+  `submitTime` datetime NOT NULL default '0000-00-00 00:00:00',
+  `submitterId` int(11) NOT NULL default '0',
+  `license` enum('Retail','Open\nSource','Demo','Shareware','Free to use','Free to use and share') default NULL,
+  `obsoleteBy` int(11) NOT NULL default '0',
+  `state` enum('accepted','queued','rejected','pending','deleted') NOT NULL default 'accepted',
+  KEY `versionId` (`versionId`),
+  KEY `appId` (`appId`)
 );
 
 
@@ -92,21 +91,21 @@ create table appBundle (
  * appHitStats and catHitStats are to record statistics
  */
 create table appHitStats (
-  appHitId  int not null auto_increment,
-  time      datetime,
-  ip        varchar(16),
-  appId     int not null,	
-  count     int,
-  key(appHitId)
+  `appHitId` int(11) NOT NULL auto_increment,
+  `time` datetime NOT NULL default '0000-00-00 00:00:00',
+  `ip` varchar(16) default NULL,
+  `appId` int(11) NOT NULL default '0',
+  `count` int(11) default NULL,
+  KEY `appHitId` (`appHitId`)
 );
 
 create table catHitStats (
-  catHitId  int not null auto_increment,
-  time      datetime,
-  ip        varchar(16),
-  catId     int not null,
-  count     int,
-  key(catHitId)
+  `catHitId` int(11) NOT NULL auto_increment,
+  `time` datetime default NULL,
+  `ip` varchar(16) default NULL,
+  `catId` int(11) NOT NULL,
+  `count` int(11) default NULL,
+  KEY `catHitId` (`catHitId`)
 );
 
 
@@ -114,16 +113,16 @@ create table catHitStats (
  * user comments
  */
 create table appComments (
-  time        datetime,
-  commentId   int not null auto_increment,
-  parentId    int default 0,
-  versionId   int not null,
-  userId      int,
-  hostname    varchar(80),
-  subject     varchar(128),
-  body        text,
-  key(commentId),
-  index(versionId)
+  `time` datetime default NULL,
+  `commentId` int(11) NOT NULL auto_increment,
+  `parentId` int(11) default '0',
+  `versionId` int(11) default '0',
+  `userId` int(11) default NULL,
+  `hostname` varchar(80) default NULL,
+  `subject` varchar(128) default NULL,
+  `body` text,
+  KEY `commentId` (`commentId`),
+  KEY `versionId` (`versionId`)
 );
 
 
@@ -131,34 +130,32 @@ create table appComments (
 /*
  * links to screenshots and other stuff
  */
-create table appData (
-	id              int not null auto_increment,
-	appId           int not null,
-	versionId       int default 0,
-	type            enum('screenshot', 'url', 'bug','downloadurl'),
-	description     text,
-	url             varchar(255) default NULL,
-	submitTime      datetime NOT NULL,
-	submitterId     int(11) NOT NULL default '0',
-	queued          enum('true','false','rejected') NOT NULL default 'false',
-	KEY id (id),
-	KEY versionId (versionId)
+CREATE TABLE `appData` (
+	 `id` int(11) NOT NULL auto_increment,
+	 `appId` int(11) NOT NULL default '0',
+	 `versionId` int(11) default '0',
+	 `type` enum('screenshot','url','bug','downloadurl') default NULL,
+	 `description` text,
+	 `url` varchar(255) default NULL,
+	 `submitTime` datetime NOT NULL default '0000-00-00 00:00:00',
+	 `submitterId` int(11) NOT NULL default '0',
+	 `state` enum('accepted','queued','rejected') NOT NULL default 'accepted',
+	 KEY `id` (`id`),
+	 KEY `versionId` (`versionId`)
 );
- 
-
 
 /*
  * allow users to vote for apps, as in, request that an app gets better support
  */
 create table appVotes (
-	id		int not null auto_increment,
-	time		datetime,
-	versionId	int not null,
-	userId		int not null,
-	slot		int not null,
-	key(id),
-	index(versionId),
-	index(userId)
+  `id` int(11) NOT NULL auto_increment,
+  `time` datetime NOT NULL default '0000-00-00 00:00:00',
+  `versionId` int(11) NOT NULL default '0',
+  `userId` int(11) NOT NULL default '0',
+  `slot` int(11) NOT NULL default '0',
+  KEY `id` (`id`),
+  KEY `userId` (`userId`),
+  KEY `versionId` (`versionId`)
 );
 
 
@@ -166,12 +163,12 @@ create table appVotes (
  * application notes
  */
 create table appNotes (
-	noteId          int not null auto_increment,
-	noteTitle       varchar(255),
-	noteDesc        text,
-	versionId       int not null,
-	submitterId	int not null,
-	submitTime	datetime not null,
-	key(noteId)
+  `noteId` int(11) NOT NULL auto_increment,
+  `noteTitle` varchar(255) default NULL,
+  `noteDesc` text,
+  `versionId` int(11) NOT NULL default '0',
+  `submitterId` int(11) NOT NULL default '0',
+  `submitTime` datetime NOT NULL default '0000-00-00 00:00:00',
+  KEY `noteId` (`noteId`)
 );
 
diff --git a/tables/buglinks.sql b/tables/buglinks.sql
index f00738d..b0e0877 100644
--- a/tables/buglinks.sql
+++ b/tables/buglinks.sql
@@ -6,13 +6,13 @@ drop table if exists buglinks;
  * link a bug to a version of an application
  */
 create table buglinks (
-        linkId          int not null auto_increment,
-	bug_id          int not null,
-	versionId       int not null,
-	submitTime	datetime NOT NULL,
-	submitterId	int(11) NOT NULL default '0',
-	queued		enum('true','false') NOT NULL default 'false',
-        key(linkId),
-	index(bug_id),
-	index(versionId)
+  `linkId` int(11) NOT NULL auto_increment,
+  `bug_id` int(11) NOT NULL default '0',
+  `versionId` int(11) NOT NULL default '0',
+  `submitTime` datetime NOT NULL default '0000-00-00 00:00:00',
+  `submitterId` int(11) NOT NULL default '0',
+  `state` enum('accepted','queued') NOT NULL default 'accepted',
+  KEY `linkId` (`linkId`),
+  KEY `bug_id` (`bug_id`),
+  KEY `versionId` (`versionId`)
 );
diff --git a/tables/bugzilla_tables.sql b/tables/bugzilla_tables.sql
index 002b7ea..3ba6dec 100644
--- a/tables/bugzilla_tables.sql
+++ b/tables/bugzilla_tables.sql
@@ -3,7 +3,7 @@ create database if not exists bugs;
 use bugs;
 
 /* make sure the wineowner user has access to the bugs database */
-grant all on bugs.* to wineowner;
+grant all privileges on bugs.* to wineowner@"localhost";
 
 drop table if exists versions;
 
diff --git a/tables/create_apidb_base.sql b/tables/create_apidb_base.sql
index cb73f2e..85002c4 100644
--- a/tables/create_apidb_base.sql
+++ b/tables/create_apidb_base.sql
@@ -1,2 +1,2 @@
 create database if not exists apidb;
-grant all on apidb.* to wineowner;
+grant all privileges on apidb.* to wineowner@"localhost";
diff --git a/tables/create_tables b/tables/create_tables
index d41e1bd..3bf0913 100644
--- a/tables/create_tables
+++ b/tables/create_tables
@@ -5,7 +5,7 @@ echo NOTE: It is ok for this to fail if the user already exists
 echo       there does not appear to be a way to create a user only
 echo       if they do not exist so we have to live with a potential
 echo       error after we try.
-mysql -p -u root apidb < create_user.sql
+mysql -p -u root < create_user.sql
 
 echo Create the apidb database, and tables
 cat create_apidb_base.sql appdb_tables.sql \
@@ -13,4 +13,4 @@ cat create_apidb_base.sql appdb_tables.sql \
     user_privs.sql app_category.sql \
     maintainers.sql buglinks.sql monitors.sql \
     error_log.sql distributions.sql testResults.sql \
- | mysql -p -u root apidb
+ | mysql -p -u root 
diff --git a/tables/create_user.sql b/tables/create_user.sql
index 5360c64..8cf471f 100644
--- a/tables/create_user.sql
+++ b/tables/create_user.sql
@@ -1,2 +1 @@
-create user wineowner identified by 'lemonade';
-
+grant all privileges on apidb.* to wineowner@"localhost" identified by 'lemonade';
diff --git a/tables/distributions.sql b/tables/distributions.sql
index 4118694..1a5fa62 100644
--- a/tables/distributions.sql
+++ b/tables/distributions.sql
@@ -6,13 +6,13 @@ drop table if exists distributions;
  * Distributions table.
  */
 create table distributions (
-        distributionId      int not null auto_increment,
-        name                varchar(255) default NULL,
-        url                 varchar(255) default NULL,
-        submitTime          datetime NOT NULL,
-        submitterId         int(11) NOT NULL default '0',
-        state               enum('accepted','queued','deleted') NOT NULL default 'accepted',
-        key(distributionId),
-        index(name)
+  `distributionId` int(11) NOT NULL auto_increment,
+  `name` varchar(255) default NULL,
+  `url` varchar(255) default NULL,
+  `submitTime` datetime NOT NULL default '0000-00-00 00:00:00',
+  `submitterId` int(11) NOT NULL default '0',
+  `state` enum('accepted','queued','deleted') NOT NULL default 'accepted',
+  KEY `distributionId` (`distributionId`),
+  KEY `name` (`name`)
 );
 
diff --git a/tables/error_log.sql b/tables/error_log.sql
index 22430d6..62ee97c 100644
--- a/tables/error_log.sql
+++ b/tables/error_log.sql
@@ -3,12 +3,12 @@ use apidb;
 drop table if exists error_log;
 
 create table error_log (
-  id            int not null auto_increment,
-  submitTime    datetime,
-  userid        int not null default '0',
-  type          enum('sql_error', 'general_error'),
-  log_text      text,
-  request_text  text,
-  deleted       bool,
-  key(id)
-);
\ No newline at end of file
+  `id` int(11) NOT NULL auto_increment,
+  `submitTime` datetime default NULL,
+  `userid` int(11) NOT NULL default '0',
+  `type` enum('sql_error','general_error') default NULL,
+  `log_text` text,
+  `request_text` text,
+  `deleted` tinyint(1) default NULL,
+  KEY `id` (`id`)
+);
diff --git a/tables/maintainers.sql b/tables/maintainers.sql
index e149eda..6a40c6d 100644
--- a/tables/maintainers.sql
+++ b/tables/maintainers.sql
@@ -12,15 +12,15 @@ drop table if exists appMaintainers;
  * for both queued and unqueued maintainers
  */
 create table appMaintainers (
-    maintainerId        int not null auto_increment,
-    appId               int,
-    versionId           int,
-    userId              int,
-    maintainReason      text,
-    superMaintainer     bool,
-    submitTime          datetime,
-    queued              enum('true','false','rejected','pending') NOT NULL default 'false',
-    notificationLevel   int not null default '0',
-    notificationTime    datetime,
-    key(maintainerId)
+  `maintainerId` int(11) NOT NULL auto_increment,
+  `appId` int(11) default NULL,
+  `versionId` int(11) default NULL,
+  `userId` int(11) default NULL,
+  `maintainReason` text,
+  `superMaintainer` tinyint(1) default NULL,
+  `submitTime` datetime default NULL,
+  `notificationLevel` int(11) NOT NULL default '0',
+  `notificationTime` datetime default NULL,
+  `state` enum('accepted','queued','pending') NOT NULL default 'accepted',
+  KEY `maintainerId` (`maintainerId`)
 );
diff --git a/tables/monitors.sql b/tables/monitors.sql
index 048a6e1..e2e737f 100644
--- a/tables/monitors.sql
+++ b/tables/monitors.sql
@@ -6,13 +6,13 @@ drop table if exists appMonitors;
  * Let users monitor changes to applications
  */
 create table appMonitors (
-        monitorId       int not null auto_increment,
-	appId           int not null,
-	versionId       int not null,
-	submitTime	datetime NOT NULL,
-	userId	        int(11) NOT NULL default '0',
-        key(monitorId),
-	index(appid),
-	index(versionId),
-	index(userId)
+  `monitorId` int(11) NOT NULL auto_increment,
+  `appId` int(11) NOT NULL default '0',
+  `versionId` int(11) NOT NULL default '0',
+  `submitTime` datetime NOT NULL default '0000-00-00 00:00:00',
+  `userId` int(11) NOT NULL default '0',
+  KEY `monitorId` (`monitorId`),
+  KEY `appId` (`appId`),
+  KEY `versionId` (`versionId`),
+  KEY `userId` (`userId`)
 );
diff --git a/tables/prefs_list.sql b/tables/prefs_list.sql
index 1952a03..86b2e1e 100644
--- a/tables/prefs_list.sql
+++ b/tables/prefs_list.sql
@@ -1,17 +1,16 @@
 use apidb;
 
-drop table if exists prefs_list;
-
-CREATE TABLE prefs_list (
-	id		int auto_increment not null,
-	name		varchar(32),
-	def_value	text,
-	value_list	text,
-	description	text,
-
-	primary key(id)
+DROP TABLE IF EXISTS `prefs_list`;
+CREATE TABLE `prefs_list` (
+  `id` int(11) NOT NULL auto_increment,
+  `name` varchar(32) default NULL,
+  `def_value` text,
+  `value_list` text,
+  `description` text,
+  PRIMARY KEY  (`id`)
 );
 
+
 INSERT INTO prefs_list VALUES (0, 'debug', 'no', 'yes|no', 'Enable debugging information');
 INSERT INTO prefs_list VALUES (0, 'sidebar', 'left', 'left|right', 'Sidebar location');
 INSERT INTO prefs_list VALUES (0, 'window:query', 'no', 'yes|no', 'Display query results in a new window');
diff --git a/tables/testResults.sql b/tables/testResults.sql
index dd0d899..56927f0 100644
--- a/tables/testResults.sql
+++ b/tables/testResults.sql
@@ -1,25 +1,23 @@
 use apidb;
 
-drop table if exists testResults;
-
-/*
- * Version Test results
- */
-create table testResults (
-        testingId       int not null auto_increment,
-	versionId       int not null,
-        whatWorks	text,
-        whatDoesnt	text,
-        whatNotTested   text,
-        testedDate      datetime not null,
-        distributionId  int not null,
-	testedRelease 	tinytext,
-	installs	enum('Yes','No','N/A') NOT NULL default 'Yes',
-	runs		enum('Yes','No','Not Installable') NOT NULL default 'Yes',
-	testedRating  	enum('Platinum','Gold','Silver','Bronze','Garbage') NOT NULL,
-        comments        text,
-	submitTime	datetime NOT NULL,
-	submitterId	int(11) NOT NULL default '0',
-	state		enum('accepted','queued','rejected','pending','deleted') NOT NULL default 'accepted',
-        key(testingId)
+DROP TABLE IF EXISTS `testResults`;
+CREATE TABLE `testResults` (
+  `testingId` int(11) NOT NULL auto_increment,
+  `versionId` int(11) NOT NULL default '0',
+  `whatWorks` text,
+  `whatDoesnt` text,
+  `whatNotTested` text,
+  `testedDate` datetime NOT NULL default '0000-00-00 00:00:00',
+  `distributionId` int(11) NOT NULL default '0',
+  `testedRelease` tinytext,
+  `installs` enum('Yes','No','N/A') NOT NULL default 'Yes',
+  `runs` enum('Yes','No','Not installable') NOT NULL default 'Yes',
+  `testedRating` enum('Platinum','Gold','Silver','Bronze','Garbage') NOT NULL default 'Platinum',
+  `comments` text,
+  `submitTime` datetime NOT NULL default '0000-00-00 00:00:00',
+  `submitterId` int(11) NOT NULL default '0',
+  `state` enum('accepted','queued','rejected','pending','deleted') NOT NULL default 'accepted',
+  KEY `testingId` (`testingId`)
 );
+
+
diff --git a/tables/user_list.sql b/tables/user_list.sql
index d7cb197..1bb4038 100644
--- a/tables/user_list.sql
+++ b/tables/user_list.sql
@@ -3,17 +3,17 @@ use apidb;
 drop table if exists user_list;
 
 create table user_list (
-  stamp                   datetime not null,
-  userid                  int not null auto_increment,
-  password                text not null,
-  realname                text not null,
-  email                   varchar(255) not null,
-  created                 datetime not null,
-  inactivity_warn_stamp   datetime not null, /* the time we warned the user */
-  inactivity_warned       enum('true','false') NOT NULL default 'false', /* if true, we warned the user */
-  CVSrelease              text,
-  unique key(userid),
-  unique(email)
+  `stamp` datetime NOT NULL,
+  `userid` int(11) NOT NULL auto_increment,
+  `password` text NOT NULL,
+  `realname` text NOT NULL,
+  `email` varchar(255) NOT NULL,
+  `created` datetime NOT NULL,
+  `inactivity_warn_stamp` datetime NOT NULL,
+  `inactivity_warned` enum('true','false') NOT NULL default 'false',
+  `CVSrelease` text,
+  UNIQUE KEY `userid` (`userid`),
+  UNIQUE KEY `email` (`email`)
 );
 
 insert into user_list values (NOW(), 0, password('testing'), 'Administrator',
diff --git a/tables/user_prefs.sql b/tables/user_prefs.sql
index d3b1875..3937453 100644
--- a/tables/user_prefs.sql
+++ b/tables/user_prefs.sql
@@ -3,9 +3,9 @@ use apidb;
 drop table if exists user_prefs;
 
 CREATE TABLE user_prefs (
-        userid          int not null,
-        name            varchar(64) not null,
-	value		text,
-        key(userid),
-	key(name)
+  `userid` int(11) NOT NULL,
+  `name` varchar(64) NOT NULL,
+  `value` text,
+  KEY `userid` (`userid`),
+  KEY `name` (`name`)
 );
diff --git a/tables/user_privs.sql b/tables/user_privs.sql
index a52fe8a..f790ae2 100644
--- a/tables/user_privs.sql
+++ b/tables/user_privs.sql
@@ -3,9 +3,9 @@ use apidb;
 drop table if exists user_privs;
 
 CREATE TABLE user_privs (
-	userid		int not null,
-	priv		varchar(64) not null,
-	primary key(userid)
+  `userid` int(11) NOT NULL,
+  `priv` varchar(64) NOT NULL,
+  PRIMARY KEY  (`userid`)
 );
 
 insert into user_privs values (1000, 'admin');
-- 
1.5.4.3


--------------050602030000000306070802--



More information about the wine-patches mailing list