Changeset 31 for trunk/SQL


Ignore:
Timestamp:
Dec 30, 2018, 7:37:55 PM (6 years ago)
Author:
chronos
Message:
  • Modified: Store date of meet items including time. For items without specified time, just use current time (time of import).
Location:
trunk/SQL
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • trunk/SQL/Structure.sql

    r10 r31  
    1 -- MySQL dump 10.13  Distrib 5.7.22, for Linux (x86_64)
    2 --
    3 -- Host: localhost    Database: tanec
    4 -- ------------------------------------------------------
    5 -- Server version       5.7.22-0ubuntu18.04.1
    6 
    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 */;
    17 
    18 --
    19 -- Table structure for table `Dance`
    20 --
    21 
    22 DROP TABLE IF EXISTS `Dance`;
    23 /*!40101 SET @saved_cs_client     = @@character_set_client */;
    24 /*!40101 SET character_set_client = utf8 */;
     1-- phpMyAdmin SQL Dump
     2-- version 4.6.6deb5
     3-- https://www.phpmyadmin.net/
     4--
     5-- Počítač: localhost:3306
     6-- Vytvořeno: Ned 30. pro 2018, 19:07
     7-- Verze serveru: 10.1.34-MariaDB-0ubuntu0.18.04.1
     8-- Verze PHP: 7.2.10-0ubuntu0.18.04.1
     9
     10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
     11SET time_zone = "+00:00";
     12
     13--
     14-- Databáze: `tanec`
     15--
     16
     17-- --------------------------------------------------------
     18
     19--
     20-- Struktura tabulky `Dance`
     21--
     22
    2523CREATE TABLE `Dance` (
    26   `Id` int(11) NOT NULL AUTO_INCREMENT,
    27   `Name` varchar(255) NOT NULL,
    28   `Group` int(11) DEFAULT NULL,
    29   PRIMARY KEY (`Id`),
    30   KEY `Group` (`Group`),
    31   CONSTRAINT `Dance_ibfk_1` FOREIGN KEY (`Group`) REFERENCES `DanceGroup` (`Id`)
    32 ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
    33 /*!40101 SET character_set_client = @saved_cs_client */;
    34 
    35 --
    36 -- Table structure for table `DanceGroup`
    37 --
    38 
    39 DROP TABLE IF EXISTS `DanceGroup`;
    40 /*!40101 SET @saved_cs_client     = @@character_set_client */;
    41 /*!40101 SET character_set_client = utf8 */;
     24  `Id` int(11) NOT NULL,
     25  `Name` varchar(255) NOT NULL,
     26  `Group` int(11) DEFAULT NULL
     27) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     28
     29--
     30-- RELACE PRO TABULKU `Dance`:
     31--   `Group`
     32--       `DanceGroup` -> `Id`
     33--
     34
     35-- --------------------------------------------------------
     36
     37--
     38-- Struktura tabulky `DanceGroup`
     39--
     40
    4241CREATE TABLE `DanceGroup` (
    43   `Id` int(11) NOT NULL AUTO_INCREMENT,
    44   `Name` varchar(255) NOT NULL,
    45   PRIMARY KEY (`Id`)
    46 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    47 /*!40101 SET character_set_client = @saved_cs_client */;
    48 
    49 --
    50 -- Table structure for table `MeetItem`
    51 --
    52 
    53 DROP TABLE IF EXISTS `MeetItem`;
    54 /*!40101 SET @saved_cs_client     = @@character_set_client */;
    55 /*!40101 SET character_set_client = utf8 */;
     42  `Id` int(11) NOT NULL,
     43  `Name` varchar(255) NOT NULL
     44) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     45
     46--
     47-- RELACE PRO TABULKU `DanceGroup`:
     48--
     49
     50-- --------------------------------------------------------
     51
     52--
     53-- Struktura tabulky `MeetItem`
     54--
     55
    5656CREATE TABLE `MeetItem` (
    57   `Id` int(11) NOT NULL AUTO_INCREMENT,
     57  `Id` int(11) NOT NULL,
    5858  `Message` text NOT NULL,
    5959  `Name` varchar(255) NOT NULL,
    60   `Age` varchar(255) NOT NULL,
    61   `Height` varchar(255) NOT NULL,
     60  `Age` int(11) DEFAULT NULL,
     61  `Height` int(11) DEFAULT NULL,
    6262  `Source` int(11) NOT NULL,
    6363  `Email` varchar(255) NOT NULL,
    6464  `Phone` varchar(255) NOT NULL,
    6565  `Gender` int(11) NOT NULL,
    66   `Date` date NOT NULL,
    67   `Weight` varchar(255) NOT NULL,
     66  `Time` datetime NOT NULL,
     67  `Weight` int(11) DEFAULT NULL,
    6868  `Location` varchar(255) NOT NULL,
    69   PRIMARY KEY (`Id`),
    70   KEY `Source` (`Source`),
    71   CONSTRAINT `MeetItem_ibfk_1` FOREIGN KEY (`Source`) REFERENCES `MeetSource` (`Id`)
    72 ) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8;
    73 /*!40101 SET character_set_client = @saved_cs_client */;
    74 
    75 --
    76 -- Table structure for table `MeetSource`
    77 --
    78 
    79 DROP TABLE IF EXISTS `MeetSource`;
    80 /*!40101 SET @saved_cs_client     = @@character_set_client */;
    81 /*!40101 SET character_set_client = utf8 */;
     69  `Link` varchar(255) NOT NULL
     70) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     71
     72--
     73-- RELACE PRO TABULKU `MeetItem`:
     74--   `Source`
     75--       `MeetSource` -> `Id`
     76--
     77
     78-- --------------------------------------------------------
     79
     80--
     81-- Struktura tabulky `MeetSource`
     82--
     83
    8284CREATE TABLE `MeetSource` (
    83   `Id` int(11) NOT NULL AUTO_INCREMENT,
     85  `Id` int(11) NOT NULL,
    8486  `Name` varchar(255) NOT NULL,
    8587  `URL` varchar(255) NOT NULL,
    86   `Method` varchar(255) NOT NULL,
    87   PRIMARY KEY (`Id`)
    88 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    89 /*!40101 SET character_set_client = @saved_cs_client */;
    90 
    91 --
    92 -- Table structure for table `Music`
    93 --
    94 
    95 DROP TABLE IF EXISTS `Music`;
    96 /*!40101 SET @saved_cs_client     = @@character_set_client */;
    97 /*!40101 SET character_set_client = utf8 */;
     88  `Method` varchar(255) NOT NULL
     89) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     90
     91--
     92-- RELACE PRO TABULKU `MeetSource`:
     93--
     94
     95-- --------------------------------------------------------
     96
     97--
     98-- Struktura tabulky `Music`
     99--
     100
    98101CREATE TABLE `Music` (
    99   `Id` int(11) NOT NULL AUTO_INCREMENT,
     102  `Id` int(11) NOT NULL,
    100103  `Name` varchar(255) NOT NULL,
    101104  `URL` varchar(255) NOT NULL,
    102   `Dance` int(11) NOT NULL,
    103   PRIMARY KEY (`Id`),
    104   KEY `Dance` (`Dance`),
    105   CONSTRAINT `Music_ibfk_1` FOREIGN KEY (`Dance`) REFERENCES `Dance` (`Id`)
    106 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    107 /*!40101 SET character_set_client = @saved_cs_client */;
    108 
    109 --
    110 -- Table structure for table `Resource`
    111 --
    112 
    113 DROP TABLE IF EXISTS `Resource`;
    114 /*!40101 SET @saved_cs_client     = @@character_set_client */;
    115 /*!40101 SET character_set_client = utf8 */;
     105  `Dance` int(11) NOT NULL
     106) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     107
     108--
     109-- RELACE PRO TABULKU `Music`:
     110--   `Dance`
     111--       `Dance` -> `Id`
     112--
     113
     114-- --------------------------------------------------------
     115
     116--
     117-- Struktura tabulky `Resource`
     118--
     119
    116120CREATE TABLE `Resource` (
    117   `Id` int(11) NOT NULL AUTO_INCREMENT,
     121  `Id` int(11) NOT NULL,
    118122  `Name` varchar(255) NOT NULL,
    119123  `Dance` int(11) NOT NULL,
    120124  `URL` varchar(255) NOT NULL,
    121   `Group` int(11) DEFAULT NULL,
    122   PRIMARY KEY (`Id`),
    123   KEY `Dance` (`Dance`),
    124   KEY `Group` (`Group`),
    125   CONSTRAINT `Resource_ibfk_1` FOREIGN KEY (`Dance`) REFERENCES `Dance` (`Id`),
    126   CONSTRAINT `Resource_ibfk_2` FOREIGN KEY (`Group`) REFERENCES `ResourceGroup` (`Id`)
    127 ) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8;
    128 /*!40101 SET character_set_client = @saved_cs_client */;
    129 
    130 --
    131 -- Table structure for table `ResourceGroup`
    132 --
    133 
    134 DROP TABLE IF EXISTS `ResourceGroup`;
    135 /*!40101 SET @saved_cs_client     = @@character_set_client */;
    136 /*!40101 SET character_set_client = utf8 */;
     125  `Group` int(11) DEFAULT NULL
     126) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     127
     128--
     129-- RELACE PRO TABULKU `Resource`:
     130--   `Dance`
     131--       `Dance` -> `Id`
     132--   `Group`
     133--       `ResourceGroup` -> `Id`
     134--
     135
     136-- --------------------------------------------------------
     137
     138--
     139-- Struktura tabulky `ResourceGroup`
     140--
     141
    137142CREATE TABLE `ResourceGroup` (
    138   `Id` int(11) NOT NULL AUTO_INCREMENT,
    139   `Name` varchar(255) NOT NULL,
    140   `URL` varchar(255) NOT NULL,
    141   PRIMARY KEY (`Id`)
    142 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    143 /*!40101 SET character_set_client = @saved_cs_client */;
    144 
    145 --
    146 -- Table structure for table `School`
    147 --
    148 
    149 DROP TABLE IF EXISTS `School`;
    150 /*!40101 SET @saved_cs_client     = @@character_set_client */;
    151 /*!40101 SET character_set_client = utf8 */;
     143  `Id` int(11) NOT NULL,
     144  `Name` varchar(255) NOT NULL,
     145  `URL` varchar(255) NOT NULL
     146) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     147
     148--
     149-- RELACE PRO TABULKU `ResourceGroup`:
     150--
     151
     152-- --------------------------------------------------------
     153
     154--
     155-- Struktura tabulky `School`
     156--
     157
    152158CREATE TABLE `School` (
    153   `Id` int(11) NOT NULL AUTO_INCREMENT,
     159  `Id` int(11) NOT NULL,
    154160  `Name` varchar(255) NOT NULL,
    155161  `Address` varchar(255) NOT NULL,
    156   `URL` varchar(255) NOT NULL,
    157   PRIMARY KEY (`Id`)
    158 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    159 /*!40101 SET character_set_client = @saved_cs_client */;
    160 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    161 
    162 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    163 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    164 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    165 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    166 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    167 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    168 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
    169 
    170 -- Dump completed on 2018-08-04 22:01:08
     162  `URL` varchar(255) NOT NULL
     163) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     164
     165--
     166-- RELACE PRO TABULKU `School`:
     167--
     168
     169--
     170-- Klíče pro exportované tabulky
     171--
     172
     173--
     174-- Klíče pro tabulku `Dance`
     175--
     176ALTER TABLE `Dance`
     177  ADD PRIMARY KEY (`Id`),
     178  ADD KEY `Group` (`Group`);
     179
     180--
     181-- Klíče pro tabulku `DanceGroup`
     182--
     183ALTER TABLE `DanceGroup`
     184  ADD PRIMARY KEY (`Id`);
     185
     186--
     187-- Klíče pro tabulku `MeetItem`
     188--
     189ALTER TABLE `MeetItem`
     190  ADD PRIMARY KEY (`Id`),
     191  ADD KEY `Source` (`Source`);
     192
     193--
     194-- Klíče pro tabulku `MeetSource`
     195--
     196ALTER TABLE `MeetSource`
     197  ADD PRIMARY KEY (`Id`);
     198
     199--
     200-- Klíče pro tabulku `Music`
     201--
     202ALTER TABLE `Music`
     203  ADD PRIMARY KEY (`Id`),
     204  ADD KEY `Dance` (`Dance`);
     205
     206--
     207-- Klíče pro tabulku `Resource`
     208--
     209ALTER TABLE `Resource`
     210  ADD PRIMARY KEY (`Id`),
     211  ADD KEY `Dance` (`Dance`),
     212  ADD KEY `Group` (`Group`);
     213
     214--
     215-- Klíče pro tabulku `ResourceGroup`
     216--
     217ALTER TABLE `ResourceGroup`
     218  ADD PRIMARY KEY (`Id`);
     219
     220--
     221-- Klíče pro tabulku `School`
     222--
     223ALTER TABLE `School`
     224  ADD PRIMARY KEY (`Id`);
     225
     226--
     227-- AUTO_INCREMENT pro tabulky
     228--
     229
     230--
     231-- AUTO_INCREMENT pro tabulku `Dance`
     232--
     233ALTER TABLE `Dance`
     234  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     235--
     236-- AUTO_INCREMENT pro tabulku `DanceGroup`
     237--
     238ALTER TABLE `DanceGroup`
     239  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     240--
     241-- AUTO_INCREMENT pro tabulku `MeetItem`
     242--
     243ALTER TABLE `MeetItem`
     244  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=93;
     245--
     246-- AUTO_INCREMENT pro tabulku `MeetSource`
     247--
     248ALTER TABLE `MeetSource`
     249  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
     250--
     251-- AUTO_INCREMENT pro tabulku `Music`
     252--
     253ALTER TABLE `Music`
     254  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     255--
     256-- AUTO_INCREMENT pro tabulku `Resource`
     257--
     258ALTER TABLE `Resource`
     259  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     260--
     261-- AUTO_INCREMENT pro tabulku `ResourceGroup`
     262--
     263ALTER TABLE `ResourceGroup`
     264  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     265--
     266-- AUTO_INCREMENT pro tabulku `School`
     267--
     268ALTER TABLE `School`
     269  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     270--
     271-- Omezení pro exportované tabulky
     272--
     273
     274--
     275-- Omezení pro tabulku `Dance`
     276--
     277ALTER TABLE `Dance`
     278  ADD CONSTRAINT `Dance_ibfk_1` FOREIGN KEY (`Group`) REFERENCES `DanceGroup` (`Id`);
     279
     280--
     281-- Omezení pro tabulku `MeetItem`
     282--
     283ALTER TABLE `MeetItem`
     284  ADD CONSTRAINT `MeetItem_ibfk_1` FOREIGN KEY (`Source`) REFERENCES `MeetSource` (`Id`);
     285
     286--
     287-- Omezení pro tabulku `Music`
     288--
     289ALTER TABLE `Music`
     290  ADD CONSTRAINT `Music_ibfk_1` FOREIGN KEY (`Dance`) REFERENCES `Dance` (`Id`);
     291
     292--
     293-- Omezení pro tabulku `Resource`
     294--
     295ALTER TABLE `Resource`
     296  ADD CONSTRAINT `Resource_ibfk_1` FOREIGN KEY (`Dance`) REFERENCES `Dance` (`Id`),
     297  ADD CONSTRAINT `Resource_ibfk_2` FOREIGN KEY (`Group`) REFERENCES `ResourceGroup` (`Id`);

  • trunk/SQL/Update.php

    r25 r31  
    77ALTER TABLE `MeetItem` CHANGE `Weight` `Weight` INT NULL DEFAULT NULL;
    88UPDATE `MeetItem` SET `Weight`=NULL WHERE `Weight`=0;
     9
     10ALTER TABLE `MeetItem` CHANGE `Date` `Time` DATETIME NOT NULL;
Note: See TracChangeset for help on using the changeset viewer.