Author: Nikita Popov (nikic)
Date: 2021-04-07T11:13:08+02:00
Commit:
https://github.com/php/web-master/commit/723ded37c9e84a2ede50aeb8fabd26e232c595bc
Raw diff:
https://github.com/php/web-master/commit/723ded37c9e84a2ede50aeb8fabd26e232c595bc.diff
Add up-to-date schema
Some of the tables are not in use though.
Changed paths:
A schema.sql
D event.sql
D note.sql
D users.sql
M README.md
Diff:
diff --git a/README.md b/README.md
index 7724794..7fc315e 100644
--- a/README.md
+++ b/README.md
@@ -13,7 +13,7 @@ CREATE USER 'nobody'@'localhost';
GRANT ALL PRIVILEGES ON phpmasterdb.* TO 'nobody'@'localhost';
# Create tables
-mysql -unobody phpmasterdb < users.sql
+mysql -unobody phpmasterdb < schema.sql
# Create user test:test
INSERT INTO users (username, svnpasswd, cvsaccess) VALUES ('test',
'$2y$10$iGHyxmfHI62Xyr3DPf8faOPCvmU1UMVMlhJQ/FqooqgPJ3STMHTyG', 1);
diff --git a/event.sql b/event.sql
deleted file mode 100644
index 395f14a..0000000
--- a/event.sql
+++ /dev/null
@@ -1,26 +0,0 @@
-#
-# Table structure for table 'phpcal'
-#
-
-CREATE TABLE phpcal (
- id int(8) NOT NULL auto_increment,
- sdato date default NULL,
- edato date default NULL,
- recur varchar(12) default NULL,
- sdesc varchar(32) NOT NULL default '',
- url varchar(128) default NULL,
- email varchar(128) NOT NULL default '',
- ldesc text,
- tipo int(1) NOT NULL default '0',
- approved int(1) NOT NULL default '0',
- app_by varchar(16) default NULL,
- country char(3) NOT NULL default '',
- category tinyint(4) NOT NULL default '0',
- PRIMARY KEY (id),
- KEY sdato (sdato),
- KEY edato (edato),
- KEY country (country),
- KEY category (category),
- FULLTEXT KEY sdesc (sdesc,ldesc,email)
-) TYPE=MyISAM;
-
diff --git a/note.sql b/note.sql
deleted file mode 100644
index e1b9c5c..0000000
--- a/note.sql
+++ /dev/null
@@ -1,42 +0,0 @@
-/* the note table holds notes for the php manual. */
-/* TODO: there is a similar table for php-gtk's manual. it should probably be
- merged with this one. */
-/* TODO: the user stuff should be linked to the users table so people could
- edit their own notes. */
-/* TODO: lang should probably be linked to a languages table of some sort.
- but we're not really using it yet, so maybe we don't want it at all. */
-
-/* used by:
- main.php.net/entry/user-note.php
- main.php.net/entry/user-notes-vote.php
- main.php.net/fetch/user-notes.php
- main.php.net/manage/user-notes.php
-*/
-
-CREATE TABLE IF NOT EXISTS note (
- id mediumint(9) NOT NULL auto_increment,
- sect varchar(80) NOT NULL default '',
- user varchar(80) default NULL,
- note text,
- ts datetime NOT NULL,
- status varchar(16) default NULL,
- lang varchar(16) default NULL,
- votes int(11) NOT NULL default '0',
- rating int(11) NOT NULL default '0',
- updated datetime,
- PRIMARY KEY (id),
- KEY idx_sect (sect)
-) ENGINE=MyISAM PACK_KEYS=1;
-
--- New votes table added for keeping track of user notes ratings
-CREATE TABLE IF NOT EXISTS `votes` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `note_id` mediumint(9) NOT NULL,
- `ip` bigint(20) unsigned NOT NULL DEFAULT '0',
- `hostip` bigint(20) unsigned NOT NULL DEFAULT '0',
- `ts` datetime NOT NULL,
- `vote` tinyint(1) unsigned NOT NULL DEFAULT '0',
- PRIMARY KEY (`id`),
- KEY `note_id` (`note_id`,`ip`,`vote`),
- KEY `hostip` (`hostip`)
-) ENGINE=MyISAM AUTO_INCREMENT=1;
diff --git a/schema.sql b/schema.sql
new file mode 100644
index 0000000..b378fbc
--- /dev/null
+++ b/schema.sql
@@ -0,0 +1,284 @@
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8mb4 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+--
+-- Table structure for table `auth`
+--
+
+DROP TABLE IF EXISTS `auth`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `auth` (
+ `authid` varchar(64) NOT NULL DEFAULT '',
+ `authkey` varchar(255) NOT NULL DEFAULT '',
+ `cburl` varchar(255) DEFAULT NULL,
+ `owneremail` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`authid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `country`
+--
+
+DROP TABLE IF EXISTS `country`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `country` (
+ `id` char(3) NOT NULL DEFAULT '',
+ `alpha2` char(2) NOT NULL DEFAULT '',
+ `name` varchar(255) NOT NULL DEFAULT '',
+ `lat` int(8) NOT NULL DEFAULT 0,
+ `lon` int(8) NOT NULL DEFAULT 0,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `mirrors`
+--
+
+DROP TABLE IF EXISTS `mirrors`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `mirrors` (
+ `id` int(8) NOT NULL AUTO_INCREMENT,
+ `mirrortype` int(1) NOT NULL DEFAULT 1,
+ `cc` char(3) NOT NULL DEFAULT '',
+ `lang` varchar(5) NOT NULL DEFAULT '',
+ `hostname` varchar(40) NOT NULL DEFAULT '',
+ `cname` varchar(80) NOT NULL DEFAULT '',
+ `maintainer` varchar(255) NOT NULL DEFAULT '',
+ `providername` varchar(255) NOT NULL DEFAULT '',
+ `providerurl` varchar(255) NOT NULL DEFAULT '',
+ `has_stats` int(1) NOT NULL DEFAULT 0,
+ `has_search` int(1) NOT NULL DEFAULT 0,
+ `active` int(1) NOT NULL DEFAULT 0,
+ `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `lastedited` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `lastupdated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `lastchecked` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `phpversion` varchar(16) NOT NULL DEFAULT '',
+ `acmt` text DEFAULT NULL,
+ `ocmt` text DEFAULT NULL,
+ `maintainer2` varchar(255) NOT NULL DEFAULT '',
+ `load_balanced` varchar(4) DEFAULT NULL,
+ `ext_avail` text DEFAULT NULL,
+ `local_hostname` varchar(255) DEFAULT NULL,
+ `ipv4_addr` varchar(55) DEFAULT NULL,
+ `ipv6_addr` varchar(55) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `hostname` (`hostname`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `note`
+--
+
+DROP TABLE IF EXISTS `note`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `note` (
+ `id` mediumint(9) NOT NULL AUTO_INCREMENT,
+ `sect` varchar(80) NOT NULL DEFAULT '',
+ `user` varchar(80) DEFAULT NULL,
+ `note` text DEFAULT NULL,
+ `ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `status` varchar(16) DEFAULT NULL,
+ `lang` varchar(16) DEFAULT NULL,
+ `votes` int(11) NOT NULL DEFAULT 0,
+ `rating` int(11) NOT NULL DEFAULT 0,
+ `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ PRIMARY KEY (`id`),
+ KEY `idx_sect` (`sect`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PACK_KEYS=1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `phpcal`
+--
+
+DROP TABLE IF EXISTS `phpcal`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `phpcal` (
+ `id` int(8) NOT NULL AUTO_INCREMENT,
+ `sdato` date DEFAULT NULL,
+ `edato` date DEFAULT NULL,
+ `recur` varchar(12) DEFAULT NULL,
+ `sdesc` varchar(32) NOT NULL DEFAULT '',
+ `url` varchar(128) DEFAULT NULL,
+ `email` varchar(128) NOT NULL DEFAULT '',
+ `ldesc` text DEFAULT NULL,
+ `tipo` int(1) NOT NULL DEFAULT 0,
+ `approved` int(1) NOT NULL DEFAULT 0,
+ `app_by` varchar(16) DEFAULT NULL,
+ `country` char(3) NOT NULL DEFAULT '',
+ `category` tinyint(4) NOT NULL DEFAULT 0,
+ PRIMARY KEY (`id`),
+ KEY `sdato` (`sdato`),
+ KEY `edato` (`edato`),
+ KEY `country` (`country`),
+ KEY `category` (`category`),
+ FULLTEXT KEY `sdesc` (`sdesc`,`ldesc`,`email`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `sites`
+--
+
+DROP TABLE IF EXISTS `sites`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `sites` (
+ `cat` varchar(48) NOT NULL DEFAULT '',
+ `name` varchar(80) NOT NULL DEFAULT '',
+ `url` varchar(80) NOT NULL DEFAULT '',
+ `email` varchar(60) NOT NULL DEFAULT '',
+ `password` varchar(16) NOT NULL DEFAULT '',
+ `approved` enum('N','Y') NOT NULL DEFAULT 'N',
+ `approved_by` varchar(20) NOT NULL DEFAULT '',
+ `note` text DEFAULT NULL,
+ `ts` datetime DEFAULT NULL,
+ PRIMARY KEY (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PACK_KEYS=1;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `tokens`
+--
+
+DROP TABLE IF EXISTS `tokens`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `tokens` (
+ `tokid` varchar(64) NOT NULL DEFAULT '',
+ `generatedfor` varchar(64) NOT NULL DEFAULT '',
+ `username` varchar(16) NOT NULL DEFAULT '',
+ `expires` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE
current_timestamp(),
+ PRIMARY KEY (`tokid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `users`
+--
+
+DROP TABLE IF EXISTS `users`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `users` (
+ `userid` int(11) NOT NULL AUTO_INCREMENT,
+ `svnpasswd` varchar(60) NOT NULL DEFAULT '',
+ `name` varchar(255) NOT NULL DEFAULT '',
+ `email` varchar(255) NOT NULL DEFAULT '',
+ `username` varchar(16) DEFAULT NULL,
+ `cvsaccess` int(1) NOT NULL DEFAULT 0,
+ `spamprotect` int(1) NOT NULL DEFAULT 1,
+ `forgot` varchar(32) DEFAULT NULL,
+ `dns_allow` int(1) NOT NULL DEFAULT 1,
+ `dns_type` varchar(5) NOT NULL DEFAULT 'NONE',
+ `dns_target` varchar(255) NOT NULL DEFAULT '',
+ `last_commit` datetime DEFAULT NULL,
+ `num_commits` int(11) NOT NULL DEFAULT 0,
+ `verified` int(1) NOT NULL DEFAULT 0,
+ `use_sa` int(11) DEFAULT 5,
+ `greylist` int(11) NOT NULL DEFAULT 0,
+ `enable` int(1) NOT NULL DEFAULT 0,
+ `pchanged` int(11) DEFAULT 0,
+ `ssh_keys` text DEFAULT NULL,
+ PRIMARY KEY (`userid`),
+ UNIQUE KEY `email` (`email`),
+ UNIQUE KEY `username` (`username`),
+ FULLTEXT KEY `name` (`name`,`email`,`username`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `users_cvs`
+--
+
+DROP TABLE IF EXISTS `users_cvs`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `users_cvs` (
+ `userid` int(11) NOT NULL DEFAULT 0,
+ `cvsuser` char(16) NOT NULL DEFAULT '',
+ `approved` tinyint(1) NOT NULL DEFAULT 0,
+ PRIMARY KEY (`userid`),
+ UNIQUE KEY `cvsuser` (`cvsuser`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `users_note`
+--
+
+DROP TABLE IF EXISTS `users_note`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `users_note` (
+ `noteid` int(11) NOT NULL AUTO_INCREMENT,
+ `userid` int(11) NOT NULL DEFAULT 0,
+ `entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `note` text DEFAULT NULL,
+ PRIMARY KEY (`noteid`),
+ FULLTEXT KEY `note` (`note`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `users_profile`
+--
+
+DROP TABLE IF EXISTS `users_profile`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `users_profile` (
+ `userid` int(11) NOT NULL,
+ `markdown` text NOT NULL,
+ `html` text NOT NULL,
+ PRIMARY KEY (`userid`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+/*!40101 SET character_set_client = @saved_cs_client */;
+
+--
+-- Table structure for table `votes`
+--
+
+DROP TABLE IF EXISTS `votes`;
+/*!40101 SET @saved_cs_client = @@character_set_client */;
+/*!40101 SET character_set_client = utf8 */;
+CREATE TABLE `votes` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `note_id` mediumint(9) NOT NULL,
+ `ip` bigint(20) unsigned NOT NULL DEFAULT 0,
+ `hostip` bigint(20) unsigned NOT NULL DEFAULT 0,
+ `ts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
+ `vote` tinyint(1) unsigned NOT NULL DEFAULT 0,
+ PRIMARY KEY (`id`),
+ KEY `note_id` (`note_id`,`ip`,`vote`),
+ KEY `hostip` (`hostip`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+/*!40101 SET character_set_client = @saved_cs_client */;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+-- Dump completed on 2021-04-07 8:26:18
diff --git a/users.sql b/users.sql
deleted file mode 100644
index e7f85c2..0000000
--- a/users.sql
+++ /dev/null
@@ -1,69 +0,0 @@
-/* user-related tables */
-
-/* various things that may hang off the users table in the future:
- * ownership of bugs and bug comments
- * 'subscription' to bugs
- * 'subscription' to notes updates of manual sections
- * cvs acls
-*/
-
-/* the users table is the main one. it contains the name, email, and
- crypted password for each user. the password is crypted using the
- standard unix DES-based crypt (for interop with cvs) */
-/* we have a full-text index on name, username and email for searching, and we
- require unique email addresses for each account. the username must also
- be unique (when present). */
-/* a user will be able to change the email address associated with
- their account if they know the password. */
-/* the cvsaccess field requires more thought. we might want to expand
- it to a more general flags field or something. it already implies
- an email alias in addition to cvs access. */
-/* dns_allow states whether or not a user gets a <username>.people.php.net
hostname.
- Abusive users can have their dns privilidges revoked using this field.
- dns_type is (currently) one of 'A','NS','CNAME' or 'NONE'.
- dns_target is dependent on dns_type and should be self explanatory */
-CREATE TABLE users (
- userid int(11) NOT NULL auto_increment,
- passwd varchar(16) NOT NULL default '',
- svnpasswd varchar(60) NOT NULL default '',
- md5passwd varchar(32) NOT NULL default '',
- name varchar(255) NOT NULL default '',
- email varchar(255) NOT NULL default '',
- username varchar(16) default NULL,
- cvsaccess int(1) NOT NULL default '0',
- spamprotect int(1) NOT NULL default '1',
- forgot varchar(32) default NULL,
- dns_allow int(1) NOT NULL default '1',
- dns_type varchar(5) NOT NULL default 'NONE',
- dns_target varchar(255) NOT NULL default '',
- last_commit datetime default NULL,
- num_commits int(11) NOT NULL default '0',
- verified int(1) NOT NULL default '0',
- use_sa int(11) default '5',
- greylist int(11) NOT NULL default '0',
- enable int(1) NOT NULL default '0',
- pchanged int(11) default '0',
- ssh_keys TEXT default NULL,
- PRIMARY KEY (userid),
- UNIQUE KEY email (email),
- UNIQUE KEY username (username),
- FULLTEXT KEY name (name,email,username)
-) ENGINE=MyISAM;
-
-/* the user_note table just contains notes about each user. */
-CREATE TABLE users_note (
- noteid int(11) NOT NULL auto_increment,
- userid int(11) NOT NULL default '0',
- entered datetime NOT NULL,
- note text,
- PRIMARY KEY (noteid),
- FULLTEXT KEY note (note)
-) ENGINE=MyISAM;
-
-/* the users_profile table contains up to one profile row for each user */
-CREATE TABLE users_profile (
- userid int(11) NOT NULL,
- markdown TEXT NOT NULL,
- html TEXT NOT NULL,
- PRIMARY KEY (userid)
-) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
PHP Webmaster List Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
