Necessary Database Fields

      Necessary Database Fields

      Here are scripts to generate the tables I've thrown together so far. I still need to make a Gamemaster database, and I may be wrong in some of the field types for some databases, so please correct me on anything that looks wrong.

      Database: GameTracker

      SQL-Abfrage

      1. -- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (x86_64)
      2. --
      3. -- Host: localhost Database: GameTracker
      4. -- ------------------------------------------------------
      5. -- Server version 5.5.35-0ubuntu0.12.04.2
      7. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
      8. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
      9. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
      10. /*!40101 SET NAMES utf8 */;
      11. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
      12. /*!40103 SET TIME_ZONE='+00:00' */;
      13. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
      14. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
      15. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
      16. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
      18. --
      19. -- Table structure for table `games`
      20. --
      22. DROP TABLE IF EXISTS `games`;
      23. /*!40101 SET @saved_cs_client = @@character_set_client */;
      24. /*!40101 SET character_set_client = utf8 */;
      25. CREATE TABLE `games` (
      26. `id` int(3) NOT NULL AUTO_INCREMENT,
      27. `gamename` varchar(30) NOT NULL,
      28. `queryport` int(5) NOT NULL,
      29. `disabledservices` text NOT NULL,
      30. `backendflags` int(11) NOT NULL,
      31. `secretkey` varchar(64) NOT NULL,
      32. `keylist` text NOT NULL,
      33. `keytypelist` text NOT NULL,
      34. PRIMARY KEY (`id`),
      35. KEY `id` (`id`)
      36. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      37. /*!40101 SET character_set_client = @saved_cs_client */;
      39. --
      40. -- Table structure for table `profiles`
      41. --
      43. DROP TABLE IF EXISTS `profiles`;
      44. /*!40101 SET @saved_cs_client = @@character_set_client */;
      45. /*!40101 SET character_set_client = utf8 */;
      46. CREATE TABLE `profiles` (
      47. `profileid` int(10) NOT NULL,
      48. `uniquenick` varchar(20) NOT NULL,
      49. `email` varchar(50) NOT NULL,
      50. `firstname` varchar(30) NOT NULL,
      51. `lastname` varchar(20) NOT NULL,
      52. `nick` varchar(30) NOT NULL,
      53. `userid` int(10) NOT NULL,
      54. `publickmask` varchar(64) NOT NULL,
      55. `deleted` tinyint(1) NOT NULL DEFAULT '0',
      56. `latitude` float NOT NULL DEFAULT '0',
      57. `longitude` float NOT NULL DEFAULT '0',
      58. PRIMARY KEY (`profileid`),
      59. UNIQUE KEY `profileid` (`profileid`)
      60. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      61. /*!40101 SET character_set_client = @saved_cs_client */;
      63. --
      64. -- Table structure for table `users`
      65. --
      67. DROP TABLE IF EXISTS `users`;
      68. /*!40101 SET @saved_cs_client = @@character_set_client */;
      69. /*!40101 SET character_set_client = utf8 */;
      70. CREATE TABLE `users` (
      71. `userid` int(10) NOT NULL,
      72. `email` varchar(50) NOT NULL,
      73. `deleted` tinyint(1) NOT NULL DEFAULT '0',
      74. `password` varchar(32) NOT NULL,
      75. `emailverified` tinyint(1) NOT NULL DEFAULT '0',
      76. UNIQUE KEY `password` (`password`),
      77. UNIQUE KEY `userid` (`userid`)
      78. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      79. /*!40101 SET character_set_client = @saved_cs_client */;
      80. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
      82. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
      83. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
      84. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
      85. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
      86. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
      87. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
      88. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
      90. -- Dump completed on 2014-03-07 17:43:40
      Alles anzeigen


      Database: Presence

      SQL-Abfrage

      1. -- MySQL dump 10.13 Distrib 5.5.35, for debian-linux-gnu (x86_64)
      2. --
      3. -- Host: localhost Database: Presence
      4. -- ------------------------------------------------------
      5. -- Server version 5.5.35-0ubuntu0.12.04.2
      7. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
      8. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
      9. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
      10. /*!40101 SET NAMES utf8 */;
      11. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
      12. /*!40103 SET TIME_ZONE='+00:00' */;
      13. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
      14. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
      15. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
      16. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
      18. --
      19. -- Table structure for table `addrequest`
      20. --
      22. DROP TABLE IF EXISTS `addrequest`;
      23. /*!40101 SET @saved_cs_client = @@character_set_client */;
      24. /*!40101 SET character_set_client = utf8 */;
      25. CREATE TABLE `addrequest` (
      26. `profileid` int(10) NOT NULL,
      27. `targetid` int(10) NOT NULL,
      28. `syncrequested` varchar(70) NOT NULL,
      29. `reason` varchar(50) NOT NULL
      30. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      31. /*!40101 SET character_set_client = @saved_cs_client */;
      33. --
      34. -- Table structure for table `blocks`
      35. --
      37. DROP TABLE IF EXISTS `blocks`;
      38. /*!40101 SET @saved_cs_client = @@character_set_client */;
      39. /*!40101 SET character_set_client = utf8 */;
      40. CREATE TABLE `blocks` (
      41. `profileid` int(10) NOT NULL,
      42. `blockedid` int(10) NOT NULL
      43. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      44. /*!40101 SET character_set_client = @saved_cs_client */;
      46. --
      47. -- Table structure for table `buddies`
      48. --
      50. DROP TABLE IF EXISTS `buddies`;
      51. /*!40101 SET @saved_cs_client = @@character_set_client */;
      52. /*!40101 SET character_set_client = utf8 */;
      53. CREATE TABLE `buddies` (
      54. `profileid` int(10) NOT NULL,
      55. `targetid` int(10) NOT NULL
      56. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      57. /*!40101 SET character_set_client = @saved_cs_client */;
      59. --
      60. -- Table structure for table `messages`
      61. --
      63. DROP TABLE IF EXISTS `messages`;
      64. /*!40101 SET @saved_cs_client = @@character_set_client */;
      65. /*!40101 SET character_set_client = utf8 */;
      66. CREATE TABLE `messages` (
      67. `to` int(10) NOT NULL,
      68. `from` int(10) NOT NULL,
      69. `message` varchar(100) NOT NULL,
      70. `date` date NOT NULL
      71. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      72. /*!40101 SET character_set_client = @saved_cs_client */;
      73. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
      75. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
      76. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
      77. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
      78. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
      79. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
      80. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
      81. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
      83. -- Dump completed on 2014-03-07 17:44:02
      Alles anzeigen