Changeset 57 for trunk/SQL


Ignore:
Timestamp:
May 27, 2020, 8:04:56 PM (5 years ago)
Author:
chronos
Message:
  • Added: Allow to disable MeetSource in database.
  • Added: Show how many meet items were parsed and how many new added.
Location:
trunk/SQL
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • trunk/SQL/Structure.sql

    r51 r57  
    1 -- --------------------------------------------------------
    2 -- Hostitel:                     localhost
    3 -- Verze serveru:                10.3.20-MariaDB-0ubuntu0.19.10.1 - Ubuntu 19.10
    4 -- OS serveru:                   debian-linux-gnu
    5 -- HeidiSQL Verze:               10.3.0.5771
    6 -- --------------------------------------------------------
    7 
    8 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    9 /*!40101 SET NAMES utf8 */;
    10 /*!50503 SET NAMES utf8mb4 */;
    11 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    12 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    13 
    14 -- Exportování struktury pro tabulka tanec.Dance
    15 CREATE TABLE IF NOT EXISTS `Dance` (
    16   `Id` int(11) NOT NULL AUTO_INCREMENT,
    17   `Name` varchar(255) NOT NULL,
    18   `Group` int(11) DEFAULT NULL,
    19   PRIMARY KEY (`Id`),
    20   KEY `Group` (`Group`),
    21   CONSTRAINT `Dance_ibfk_1` FOREIGN KEY (`Group`) REFERENCES `DanceGroup` (`Id`)
    22 ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
    23 
    24 -- Export dat nebyl vybrán.
    25 
    26 -- Exportování struktury pro tabulka tanec.DanceFigure
    27 CREATE TABLE IF NOT EXISTS `DanceFigure` (
    28   `Id` int(11) NOT NULL AUTO_INCREMENT,
     1-- phpMyAdmin SQL Dump
     2-- version 4.9.5deb2
     3-- https://www.phpmyadmin.net/
     4--
     5-- Počítač: localhost:3306
     6-- Vytvořeno: Stř 27. kvě 2020, 19:39
     7-- Verze serveru: 10.3.22-MariaDB-1ubuntu1
     8-- Verze PHP: 7.4.3
     9
     10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
     11SET AUTOCOMMIT = 0;
     12START TRANSACTION;
     13SET time_zone = "+00:00";
     14
     15--
     16-- Databáze: `tanec`
     17--
     18
     19-- --------------------------------------------------------
     20
     21--
     22-- Struktura tabulky `Dance`
     23--
     24
     25CREATE TABLE `Dance` (
     26  `Id` int(11) NOT NULL,
     27  `Name` varchar(255) NOT NULL,
     28  `Group` int(11) DEFAULT NULL
     29) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     30
     31-- --------------------------------------------------------
     32
     33--
     34-- Struktura tabulky `DanceFigure`
     35--
     36
     37CREATE TABLE `DanceFigure` (
     38  `Id` int(11) NOT NULL,
    2939  `NameCz` varchar(255) NOT NULL,
    3040  `NameEn` varchar(255) NOT NULL,
    31   `Dance` int(11) NOT NULL,
    32   PRIMARY KEY (`Id`),
    33   UNIQUE KEY `Id` (`Id`),
    34   KEY `Dance` (`Dance`),
    35   CONSTRAINT `DanceFigure_ibfk_1` FOREIGN KEY (`Dance`) REFERENCES `Dance` (`Id`)
    36 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
    37 
    38 -- Export dat nebyl vybrán.
    39 
    40 -- Exportování struktury pro tabulka tanec.DanceGroup
    41 CREATE TABLE IF NOT EXISTS `DanceGroup` (
    42   `Id` int(11) NOT NULL AUTO_INCREMENT,
    43   `Name` varchar(255) NOT NULL,
    44   PRIMARY KEY (`Id`)
    45 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    46 
    47 -- Export dat nebyl vybrán.
    48 
    49 -- Exportování struktury pro tabulka tanec.Event
    50 CREATE TABLE IF NOT EXISTS `Event` (
    51   `Id` int(11) NOT NULL AUTO_INCREMENT,
     41  `Dance` int(11) NOT NULL
     42) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
     43
     44-- --------------------------------------------------------
     45
     46--
     47-- Struktura tabulky `DanceGroup`
     48--
     49
     50CREATE TABLE `DanceGroup` (
     51  `Id` int(11) NOT NULL,
     52  `Name` varchar(255) NOT NULL
     53) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     54
     55-- --------------------------------------------------------
     56
     57--
     58-- Struktura tabulky `Event`
     59--
     60
     61CREATE TABLE `Event` (
     62  `Id` int(11) NOT NULL,
    5263  `TimeFrom` datetime DEFAULT NULL,
    5364  `TimeTo` datetime DEFAULT NULL,
    … …  
    6071  `Location` varchar(255) DEFAULT NULL,
    6172  `TimeImport` datetime DEFAULT NULL,
    62   `RemoteId` varchar(255) DEFAULT NULL,
    63   PRIMARY KEY (`Id`),
    64   KEY `Source` (`Source`)
    65 ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4;
    66 
    67 -- Export dat nebyl vybrán.
    68 
    69 -- Exportování struktury pro tabulka tanec.EventSource
    70 CREATE TABLE IF NOT EXISTS `EventSource` (
    71   `Id` int(11) NOT NULL AUTO_INCREMENT,
     73  `RemoteId` varchar(255) DEFAULT NULL
     74) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
     75
     76-- --------------------------------------------------------
     77
     78--
     79-- Struktura tabulky `EventSource`
     80--
     81
     82CREATE TABLE `EventSource` (
     83  `Id` int(11) NOT NULL,
    7284  `Name` varchar(255) DEFAULT NULL,
    7385  `Method` varchar(255) DEFAULT NULL,
    74   `URL` varchar(255) DEFAULT NULL,
    75   PRIMARY KEY (`Id`)
    76 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
    77 
    78 -- Export dat nebyl vybrán.
    79 
    80 -- Exportování struktury pro tabulka tanec.MeetItem
    81 CREATE TABLE IF NOT EXISTS `MeetItem` (
    82   `Id` int(11) NOT NULL AUTO_INCREMENT,
     86  `URL` varchar(255) DEFAULT NULL
     87) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
     88
     89-- --------------------------------------------------------
     90
     91--
     92-- Struktura tabulky `MeetItem`
     93--
     94
     95CREATE TABLE `MeetItem` (
     96  `Id` int(11) NOT NULL,
    8397  `Message` text NOT NULL,
    8498  `Name` varchar(255) NOT NULL,
    … …  
    95109  `TimeImport` datetime DEFAULT NULL,
    96110  `RemoteId` varchar(255) DEFAULT NULL,
    97   `Hidden` int(11) DEFAULT 0,
    98   PRIMARY KEY (`Id`),
    99   KEY `Source` (`Source`),
    100   KEY `Time` (`Time`),
    101   CONSTRAINT `MeetItem_ibfk_1` FOREIGN KEY (`Source`) REFERENCES `MeetSource` (`Id`)
    102 ) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=utf8;
    103 
    104 -- Export dat nebyl vybrán.
    105 
    106 -- Exportování struktury pro tabulka tanec.MeetSource
    107 CREATE TABLE IF NOT EXISTS `MeetSource` (
    108   `Id` int(11) NOT NULL AUTO_INCREMENT,
     111  `Hidden` int(11) DEFAULT 0
     112) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     113
     114-- --------------------------------------------------------
     115
     116--
     117-- Struktura tabulky `MeetSource`
     118--
     119
     120CREATE TABLE `MeetSource` (
     121  `Id` int(11) NOT NULL,
    109122  `Name` varchar(255) NOT NULL,
    110123  `URL` varchar(255) NOT NULL,
    111124  `Method` varchar(255) NOT NULL,
    112   PRIMARY KEY (`Id`)
    113 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
    114 
    115 -- Export dat nebyl vybrán.
    116 
    117 -- Exportování struktury pro tabulka tanec.Movie
    118 CREATE TABLE IF NOT EXISTS `Movie` (
    119   `Id` int(11) NOT NULL AUTO_INCREMENT,
     125  `Enabled` int(11) NOT NULL DEFAULT 1
     126) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     127
     128-- --------------------------------------------------------
     129
     130--
     131-- Struktura tabulky `Movie`
     132--
     133
     134CREATE TABLE `Movie` (
     135  `Id` int(11) NOT NULL,
    120136  `NameCz` varchar(255) NOT NULL,
    121137  `NameEn` varchar(255) NOT NULL,
    122138  `Year` int(11) NOT NULL,
    123139  `Imdb` varchar(255) NOT NULL,
    124   `Csfd` varchar(255) NOT NULL,
    125   PRIMARY KEY (`Id`),
    126   KEY `Year` (`Year`)
    127 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    128 
    129 -- Export dat nebyl vybrán.
    130 
    131 -- Exportování struktury pro tabulka tanec.Music
    132 CREATE TABLE IF NOT EXISTS `Music` (
    133   `Id` int(11) NOT NULL AUTO_INCREMENT,
     140  `Csfd` varchar(255) NOT NULL
     141) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     142
     143-- --------------------------------------------------------
     144
     145--
     146-- Struktura tabulky `Music`
     147--
     148
     149CREATE TABLE `Music` (
     150  `Id` int(11) NOT NULL,
    134151  `Name` varchar(255) NOT NULL,
    135152  `URL` varchar(255) NOT NULL,
    136   `Dance` int(11) NOT NULL,
    137   PRIMARY KEY (`Id`),
    138   KEY `Dance` (`Dance`),
    139   CONSTRAINT `Music_ibfk_1` FOREIGN KEY (`Dance`) REFERENCES `Dance` (`Id`)
    140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    141 
    142 -- Export dat nebyl vybrán.
    143 
    144 -- Exportování struktury pro tabulka tanec.Resource
    145 CREATE TABLE IF NOT EXISTS `Resource` (
    146   `Id` int(11) NOT NULL AUTO_INCREMENT,
     153  `Dance` int(11) NOT NULL
     154) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     155
     156-- --------------------------------------------------------
     157
     158--
     159-- Struktura tabulky `Resource`
     160--
     161
     162CREATE TABLE `Resource` (
     163  `Id` int(11) NOT NULL,
    147164  `Name` varchar(255) NOT NULL,
    148165  `Dance` int(11) NOT NULL,
    149166  `URL` varchar(255) NOT NULL,
    150   `Group` int(11) DEFAULT NULL,
    151   PRIMARY KEY (`Id`),
    152   KEY `Dance` (`Dance`),
    153   KEY `Group` (`Group`),
    154   CONSTRAINT `Resource_ibfk_1` FOREIGN KEY (`Dance`) REFERENCES `Dance` (`Id`),
    155   CONSTRAINT `Resource_ibfk_2` FOREIGN KEY (`Group`) REFERENCES `ResourceGroup` (`Id`)
    156 ) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8;
    157 
    158 -- Export dat nebyl vybrán.
    159 
    160 -- Exportování struktury pro tabulka tanec.ResourceGroup
    161 CREATE TABLE IF NOT EXISTS `ResourceGroup` (
    162   `Id` int(11) NOT NULL AUTO_INCREMENT,
    163   `Name` varchar(255) NOT NULL,
    164   `URL` varchar(255) NOT NULL,
    165   PRIMARY KEY (`Id`)
    166 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    167 
    168 -- Export dat nebyl vybrán.
    169 
    170 -- Exportování struktury pro tabulka tanec.School
    171 CREATE TABLE IF NOT EXISTS `School` (
    172   `Id` int(11) NOT NULL AUTO_INCREMENT,
     167  `Group` int(11) DEFAULT NULL
     168) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     169
     170-- --------------------------------------------------------
     171
     172--
     173-- Struktura tabulky `ResourceGroup`
     174--
     175
     176CREATE TABLE `ResourceGroup` (
     177  `Id` int(11) NOT NULL,
     178  `Name` varchar(255) NOT NULL,
     179  `URL` varchar(255) NOT NULL
     180) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     181
     182-- --------------------------------------------------------
     183
     184--
     185-- Struktura tabulky `School`
     186--
     187
     188CREATE TABLE `School` (
     189  `Id` int(11) NOT NULL,
    173190  `Name` varchar(255) NOT NULL,
    174191  `Address` varchar(255) NOT NULL,
    175   `URL` varchar(255) NOT NULL,
    176   PRIMARY KEY (`Id`)
    177 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
    178 
    179 -- Export dat nebyl vybrán.
    180 
    181 /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
    182 /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
    183 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
     192  `URL` varchar(255) NOT NULL
     193) ENGINE=InnoDB DEFAULT CHARSET=utf8;
     194
     195--
     196-- Klíče pro exportované tabulky
     197--
     198
     199--
     200-- Klíče pro tabulku `Dance`
     201--
     202ALTER TABLE `Dance`
     203  ADD PRIMARY KEY (`Id`),
     204  ADD KEY `Group` (`Group`);
     205
     206--
     207-- Klíče pro tabulku `DanceFigure`
     208--
     209ALTER TABLE `DanceFigure`
     210  ADD PRIMARY KEY (`Id`),
     211  ADD UNIQUE KEY `Id` (`Id`),
     212  ADD KEY `Dance` (`Dance`);
     213
     214--
     215-- Klíče pro tabulku `DanceGroup`
     216--
     217ALTER TABLE `DanceGroup`
     218  ADD PRIMARY KEY (`Id`);
     219
     220--
     221-- Klíče pro tabulku `Event`
     222--
     223ALTER TABLE `Event`
     224  ADD PRIMARY KEY (`Id`),
     225  ADD KEY `Source` (`Source`);
     226
     227--
     228-- Klíče pro tabulku `EventSource`
     229--
     230ALTER TABLE `EventSource`
     231  ADD PRIMARY KEY (`Id`);
     232
     233--
     234-- Klíče pro tabulku `MeetItem`
     235--
     236ALTER TABLE `MeetItem`
     237  ADD PRIMARY KEY (`Id`),
     238  ADD KEY `Source` (`Source`),
     239  ADD KEY `Time` (`Time`);
     240
     241--
     242-- Klíče pro tabulku `MeetSource`
     243--
     244ALTER TABLE `MeetSource`
     245  ADD PRIMARY KEY (`Id`);
     246
     247--
     248-- Klíče pro tabulku `Movie`
     249--
     250ALTER TABLE `Movie`
     251  ADD PRIMARY KEY (`Id`),
     252  ADD KEY `Year` (`Year`);
     253
     254--
     255-- Klíče pro tabulku `Music`
     256--
     257ALTER TABLE `Music`
     258  ADD PRIMARY KEY (`Id`),
     259  ADD KEY `Dance` (`Dance`);
     260
     261--
     262-- Klíče pro tabulku `Resource`
     263--
     264ALTER TABLE `Resource`
     265  ADD PRIMARY KEY (`Id`),
     266  ADD KEY `Dance` (`Dance`),
     267  ADD KEY `Group` (`Group`);
     268
     269--
     270-- Klíče pro tabulku `ResourceGroup`
     271--
     272ALTER TABLE `ResourceGroup`
     273  ADD PRIMARY KEY (`Id`);
     274
     275--
     276-- Klíče pro tabulku `School`
     277--
     278ALTER TABLE `School`
     279  ADD PRIMARY KEY (`Id`);
     280
     281--
     282-- AUTO_INCREMENT pro tabulky
     283--
     284
     285--
     286-- AUTO_INCREMENT pro tabulku `Dance`
     287--
     288ALTER TABLE `Dance`
     289  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     290
     291--
     292-- AUTO_INCREMENT pro tabulku `DanceFigure`
     293--
     294ALTER TABLE `DanceFigure`
     295  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     296
     297--
     298-- AUTO_INCREMENT pro tabulku `DanceGroup`
     299--
     300ALTER TABLE `DanceGroup`
     301  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     302
     303--
     304-- AUTO_INCREMENT pro tabulku `Event`
     305--
     306ALTER TABLE `Event`
     307  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     308
     309--
     310-- AUTO_INCREMENT pro tabulku `EventSource`
     311--
     312ALTER TABLE `EventSource`
     313  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     314
     315--
     316-- AUTO_INCREMENT pro tabulku `MeetItem`
     317--
     318ALTER TABLE `MeetItem`
     319  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     320
     321--
     322-- AUTO_INCREMENT pro tabulku `MeetSource`
     323--
     324ALTER TABLE `MeetSource`
     325  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     326
     327--
     328-- AUTO_INCREMENT pro tabulku `Movie`
     329--
     330ALTER TABLE `Movie`
     331  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     332
     333--
     334-- AUTO_INCREMENT pro tabulku `Music`
     335--
     336ALTER TABLE `Music`
     337  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     338
     339--
     340-- AUTO_INCREMENT pro tabulku `Resource`
     341--
     342ALTER TABLE `Resource`
     343  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     344
     345--
     346-- AUTO_INCREMENT pro tabulku `ResourceGroup`
     347--
     348ALTER TABLE `ResourceGroup`
     349  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     350
     351--
     352-- AUTO_INCREMENT pro tabulku `School`
     353--
     354ALTER TABLE `School`
     355  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
     356
     357--
     358-- Omezení pro exportované tabulky
     359--
     360
     361--
     362-- Omezení pro tabulku `Dance`
     363--
     364ALTER TABLE `Dance`
     365  ADD CONSTRAINT `Dance_ibfk_1` FOREIGN KEY (`Group`) REFERENCES `DanceGroup` (`Id`);
     366
     367--
     368-- Omezení pro tabulku `DanceFigure`
     369--
     370ALTER TABLE `DanceFigure`
     371  ADD CONSTRAINT `DanceFigure_ibfk_1` FOREIGN KEY (`Dance`) REFERENCES `Dance` (`Id`);
     372
     373--
     374-- Omezení pro tabulku `MeetItem`
     375--
     376ALTER TABLE `MeetItem`
     377  ADD CONSTRAINT `MeetItem_ibfk_1` FOREIGN KEY (`Source`) REFERENCES `MeetSource` (`Id`);
     378
     379--
     380-- Omezení pro tabulku `Music`
     381--
     382ALTER TABLE `Music`
     383  ADD CONSTRAINT `Music_ibfk_1` FOREIGN KEY (`Dance`) REFERENCES `Dance` (`Id`);
     384
     385--
     386-- Omezení pro tabulku `Resource`
     387--
     388ALTER TABLE `Resource`
     389  ADD CONSTRAINT `Resource_ibfk_1` FOREIGN KEY (`Dance`) REFERENCES `Dance` (`Id`),
     390  ADD CONSTRAINT `Resource_ibfk_2` FOREIGN KEY (`Group`) REFERENCES `ResourceGroup` (`Id`);
     391COMMIT;

  • trunk/SQL/Update.php

    r51 r57  
    8282  PRIMARY KEY (`Id`)
    8383) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
     84
     85ALTER TABLE `MeetSource` ADD `Enabled` int(11) NOT NULL DEFAULT 1;
Note: See TracChangeset for help on using the changeset viewer.