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