forked from hasslesstech/edu-dis-labs
201 lines
45 KiB
HTML
201 lines
45 KiB
HTML
|
<!DOCTYPE html>
|
||
|
|
<html lang="en-US">
|
||
|
|
<head>
|
||
|
|
<meta charset="utf-8">
|
||
|
|
<meta name="viewport" content="width=device-width,initial-scale=1">
|
||
|
|
<title>Реалізація інформаційного та програмного забезпечення | Xpertise</title>
|
||
|
|
<meta name="generator" content="VuePress 1.8.2">
|
||
|
|
<link rel="icon" href="/edu-dis-labs-bodnar/favicon.ico">
|
||
|
|
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/KaTeX/0.5.1/katex.min.css">
|
||
|
|
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
|
||
|
|
<meta name="description" content="Лабораторні роботи з дисципліни "Організація баз даних"">
|
||
|
|
|
||
|
|
<link rel="preload" href="/edu-dis-labs-bodnar/assets/css/0.styles.71ff2442.css" as="style"><link rel="preload" href="/edu-dis-labs-bodnar/assets/js/app.0f2f35e9.js" as="script"><link rel="preload" href="/edu-dis-labs-bodnar/assets/js/2.a7c45aa3.js" as="script"><link rel="preload" href="/edu-dis-labs-bodnar/assets/js/24.4951bab4.js" as="script"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/10.35d89b73.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/11.603171d8.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/12.9a82e5ab.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/13.4f9f149e.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/14.36796f7d.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/15.9c6bcc86.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/16.56faf7ac.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/17.a2c48682.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/18.62bb8897.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/19.a26f6889.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/20.f88e7407.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/21.2cc4761f.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/22.39a9ded2.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/23.f9948ada.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/25.8863a270.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/26.c4b8726e.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/27.9a1f882a.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/3.78e8da28.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/4.8211a5d0.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/5.22a8c3cd.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/6.234fb2a8.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/7.352b63be.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/8.acae36a2.js"><link rel="prefetch" href="/edu-dis-labs-bodnar/assets/js/9.2e26f8b0.js">
|
||
|
|
<link rel="stylesheet" href="/edu-dis-labs-bodnar/assets/css/0.styles.71ff2442.css">
|
||
|
|
</head>
|
||
|
|
<body>
|
||
|
|
<div id="app" data-server-rendered="true"><div class="theme-container"><header class="navbar"><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/edu-dis-labs-bodnar/" class="home-link router-link-active"><!----> <span class="site-name">Xpertise</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/edu-dis-labs-bodnar/" class="nav-link">
|
||
|
|
Початок
|
||
|
|
</a></div> <a href="http://139.162.162.130:3000/hasslesstech/edu-dis-labs" target="_blank" rel="noopener noreferrer" class="repo-link">
|
||
|
|
Gitea
|
||
|
|
<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <aside class="sidebar"><nav class="nav-links"><div class="nav-item"><a href="/edu-dis-labs-bodnar/" class="nav-link">
|
||
|
|
Початок
|
||
|
|
</a></div> <a href="http://139.162.162.130:3000/hasslesstech/edu-dis-labs" target="_blank" rel="noopener noreferrer" class="repo-link">
|
||
|
|
Gitea
|
||
|
|
<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav> <ul class="sidebar-links"><li><a href="/edu-dis-labs-bodnar/intro/" class="sidebar-link">Вступ</a></li><li><section class="sidebar-group collapsable depth-0"><a href="/edu-dis-labs-bodnar/requirements/" class="sidebar-heading clickable"><span>Розроблення вимог до системи</span> <span class="arrow right"></span></a> <!----></section></li><li><a href="/edu-dis-labs-bodnar/use-cases/" class="sidebar-link">Розроблення вимог до функціональности системи</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/use-cases/#загальна-схема" class="sidebar-link">Загальна схема</a></li><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/use-cases/#схеми-використання-для-окремих-діячів" class="sidebar-link">Схеми використання для окремих діячів</a></li><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/use-cases/#сценаріі-використання" class="sidebar-link">Сценарії використання</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/use-cases/#для-гостя" class="sidebar-link">Для гостя</a></li><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/use-cases/#для-зареєстрованого-користувача" class="sidebar-link">Для зареєстрованого користувача</a></li></ul></li></ul></li><li><a href="/edu-dis-labs-bodnar/design/" class="sidebar-link">Проєктування архітектури системи</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/design/#be-модель" class="sidebar-link">BE модель</a></li><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/design/#er-модель" class="sidebar-link">ER-модель</a></li><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/design/#реляціина-схема" class="sidebar-link">Реляційна схема</a></li></ul></li><li><a href="/edu-dis-labs-bodnar/software/" aria-current="page" class="active sidebar-link">Реалізація інформаційного та програмного забезпечення</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/software/#sql-скрипт-для-створення-та-початкового-наповнення-бази-даних" class="sidebar-link">SQL-скрипт для створення та початкового наповнення бази даних</a></li><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/software/#restfull-сервіс-для-керування-опитуваннями" class="sidebar-link">RESTfull-сервіс для керування опитуваннями</a></li></ul></li><li><a href="/edu-dis-labs-bodnar/test/" class="sidebar-link">Тестування працездатності системи</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/test/#запуск-сервера" class="sidebar-link">Запуск сервера</a></li><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/test/#метод-post" class="sidebar-link">Метод POST</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/edu-dis-labs-bodnar/test/#post-question" class="sidebar-link">POST QUESTION</a></li></
|
||
|
|
|
||
|
|
<span class="token keyword">SET</span> <span class="token variable">@OLD_UNIQUE_CHECKS</span><span class="token operator">=</span>@<span class="token variable">@UNIQUE_CHECKS</span><span class="token punctuation">,</span> UNIQUE_CHECKS<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
|
||
|
|
<span class="token keyword">SET</span> <span class="token variable">@OLD_FOREIGN_KEY_CHECKS</span><span class="token operator">=</span>@<span class="token variable">@FOREIGN_KEY_CHECKS</span><span class="token punctuation">,</span> FOREIGN_KEY_CHECKS<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
|
||
|
|
<span class="token keyword">SET</span> <span class="token variable">@OLD_SQL_MODE</span><span class="token operator">=</span>@<span class="token variable">@SQL_MODE</span><span class="token punctuation">,</span> SQL_MODE<span class="token operator">=</span><span class="token string">'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'</span><span class="token punctuation">;</span>
|
||
|
|
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token comment">-- Schema mydb</span>
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token comment">-- Schema mydb</span>
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token keyword">CREATE</span> <span class="token keyword">SCHEMA</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8 <span class="token punctuation">;</span>
|
||
|
|
<span class="token keyword">USE</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span> <span class="token punctuation">;</span>
|
||
|
|
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token comment">-- Table `mydb`.`Account`</span>
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>Account<span class="token punctuation">`</span> <span class="token punctuation">(</span>
|
||
|
|
<span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>username<span class="token punctuation">`</span> <span class="token keyword">CHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>password<span class="token punctuation">`</span> <span class="token keyword">CHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">`</span>id_UNIQUE<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">ASC</span><span class="token punctuation">)</span> VISIBLE<span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">`</span>username_UNIQUE<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>username<span class="token punctuation">`</span> <span class="token keyword">ASC</span><span class="token punctuation">)</span> VISIBLE<span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">InnoDB</span><span class="token punctuation">;</span>
|
||
|
|
|
||
|
|
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token comment">-- Table `mydb`.`Survey`</span>
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>Survey<span class="token punctuation">`</span> <span class="token punctuation">(</span>
|
||
|
|
<span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>isPaused<span class="token punctuation">`</span> <span class="token keyword">TINYINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>isNamed<span class="token punctuation">`</span> <span class="token keyword">TINYINT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>name<span class="token punctuation">`</span> <span class="token keyword">CHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>duration<span class="token punctuation">`</span> <span class="token keyword">CHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">`</span>id_UNIQUE<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">ASC</span><span class="token punctuation">)</span> VISIBLE<span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">CONSTRAINT</span> <span class="token punctuation">`</span>fk_Survey_Account1<span class="token punctuation">`</span>
|
||
|
|
<span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">REFERENCES</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>Account<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span>
|
||
|
|
<span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">InnoDB</span><span class="token punctuation">;</span>
|
||
|
|
|
||
|
|
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token comment">-- Table `mydb`.`Question`</span>
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>Question<span class="token punctuation">`</span> <span class="token punctuation">(</span>
|
||
|
|
<span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span><span class="token keyword">Text</span><span class="token punctuation">`</span> <span class="token keyword">CHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">`</span>id_UNIQUE<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">ASC</span><span class="token punctuation">)</span> VISIBLE<span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">CONSTRAINT</span> <span class="token punctuation">`</span>fk_Question_Survey1<span class="token punctuation">`</span>
|
||
|
|
<span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">REFERENCES</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>Survey<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span>
|
||
|
|
<span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">InnoDB</span><span class="token punctuation">;</span>
|
||
|
|
|
||
|
|
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token comment">-- Table `mydb`.`Response`</span>
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>Response<span class="token punctuation">`</span> <span class="token punctuation">(</span>
|
||
|
|
<span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span><span class="token keyword">Value</span><span class="token punctuation">`</span> <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">16384</span><span class="token punctuation">)</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> <span class="token punctuation">`</span>id_UNIQUE<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">ASC</span><span class="token punctuation">)</span> VISIBLE<span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">CONSTRAINT</span> <span class="token punctuation">`</span>fk_Response_Question<span class="token punctuation">`</span>
|
||
|
|
<span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">REFERENCES</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>Question<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span>
|
||
|
|
<span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">CONSTRAINT</span> <span class="token punctuation">`</span>fk_Response_Account1<span class="token punctuation">`</span>
|
||
|
|
<span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">REFERENCES</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>Account<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span>
|
||
|
|
<span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">InnoDB</span><span class="token punctuation">;</span>
|
||
|
|
|
||
|
|
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token comment">-- Table `mydb`.`Link`</span>
|
||
|
|
<span class="token comment">-- -----------------------------------------------------</span>
|
||
|
|
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>Link<span class="token punctuation">`</span> <span class="token punctuation">(</span>
|
||
|
|
<span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>uses<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>responces<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>usageLimit<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>responceLimit<span class="token punctuation">`</span> <span class="token keyword">INT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token punctuation">`</span>path<span class="token punctuation">`</span> <span class="token keyword">CHAR</span><span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
|
||
|
|
<span class="token keyword">CONSTRAINT</span> <span class="token punctuation">`</span>fk_Link_Survey1<span class="token punctuation">`</span>
|
||
|
|
<span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">REFERENCES</span> <span class="token punctuation">`</span>mydb<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token punctuation">`</span>Survey<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span>
|
||
|
|
<span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">NO</span> <span class="token keyword">ACTION</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">InnoDB</span><span class="token punctuation">;</span>
|
||
|
|
|
||
|
|
|
||
|
|
<span class="token keyword">SET</span> SQL_MODE<span class="token operator">=</span><span class="token variable">@OLD_SQL_MODE</span><span class="token punctuation">;</span>
|
||
|
|
<span class="token keyword">SET</span> FOREIGN_KEY_CHECKS<span class="token operator">=</span><span class="token variable">@OLD_FOREIGN_KEY_CHECKS</span><span class="token punctuation">;</span>
|
||
|
|
<span class="token keyword">SET</span> UNIQUE_CHECKS<span class="token operator">=</span><span class="token variable">@OLD_UNIQUE_CHECKS</span><span class="token punctuation">;</span>
|
||
|
|
</code></pre></div><h2 id="restfull-сервіс-для-керування-опитуваннями"><a href="#restfull-сервіс-для-керування-опитуваннями" class="header-anchor">#</a> RESTfull-сервіс для керування опитуваннями</h2> <p>Код серверу (Python, Flask) для таблиці "question":</p> <div class="language-python extra-class"><pre class="language-python"><code><span class="token keyword">from</span> flask <span class="token keyword">import</span> Flask<span class="token punctuation">,</span> request
|
||
|
|
<span class="token keyword">import</span> mariadb <span class="token keyword">as</span> mdb
|
||
|
|
<span class="token keyword">import</span> json
|
||
|
|
|
||
|
|
<span class="token keyword">def</span> <span class="token function">init_db</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
|
||
|
|
<span class="token keyword">try</span><span class="token punctuation">:</span>
|
||
|
|
<span class="token keyword">return</span> mdb<span class="token punctuation">.</span>connect<span class="token punctuation">(</span>
|
||
|
|
host<span class="token operator">=</span><span class="token string">"10.1.1.36"</span><span class="token punctuation">,</span>
|
||
|
|
port<span class="token operator">=</span><span class="token number">3306</span><span class="token punctuation">,</span>
|
||
|
|
user<span class="token operator">=</span><span class="token string">"andruxa"</span><span class="token punctuation">,</span>
|
||
|
|
password<span class="token operator">=</span><span class="token string">"password1"</span><span class="token punctuation">,</span>
|
||
|
|
database<span class="token operator">=</span><span class="token string">"odb"</span>
|
||
|
|
<span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">except</span> mdb<span class="token punctuation">.</span>Error <span class="token keyword">as</span> e<span class="token punctuation">:</span>
|
||
|
|
<span class="token keyword">print</span><span class="token punctuation">(</span><span class="token string-interpolation"><span class="token string">f"Error connecting to MariaDB Platform: </span><span class="token interpolation"><span class="token punctuation">{</span>e<span class="token punctuation">}</span></span><span class="token string">"</span></span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">return</span> <span class="token boolean">None</span>
|
||
|
|
|
||
|
|
app <span class="token operator">=</span> Flask<span class="token punctuation">(</span>__name__<span class="token punctuation">)</span>
|
||
|
|
|
||
|
|
<span class="token decorator annotation punctuation">@app<span class="token punctuation">.</span>route</span><span class="token punctuation">(</span><span class="token string">"/api/question"</span><span class="token punctuation">,</span> methods<span class="token operator">=</span><span class="token punctuation">[</span><span class="token string">"GET"</span><span class="token punctuation">,</span> <span class="token string">"POST"</span><span class="token punctuation">,</span> <span class="token string">"PUT"</span><span class="token punctuation">,</span> <span class="token string">"DELETE"</span><span class="token punctuation">]</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">def</span> <span class="token function">api_question</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">:</span>
|
||
|
|
db <span class="token operator">=</span> init_db<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">if</span> <span class="token keyword">not</span> db<span class="token punctuation">:</span>
|
||
|
|
<span class="token keyword">return</span> <span class="token string">'{"error": "Failed to connect to database"}\n'</span><span class="token punctuation">,</span> <span class="token number">500</span>
|
||
|
|
|
||
|
|
cur <span class="token operator">=</span> db<span class="token punctuation">.</span>cursor<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
|
||
|
|
match request<span class="token punctuation">.</span>method<span class="token punctuation">:</span>
|
||
|
|
case <span class="token string">"GET"</span><span class="token punctuation">:</span>
|
||
|
|
cur<span class="token punctuation">.</span>execute<span class="token punctuation">(</span><span class="token string">"SELECT id, text, survey_id FROM question;"</span><span class="token punctuation">)</span>
|
||
|
|
result <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">{</span><span class="token string">"id"</span><span class="token punctuation">:</span> row<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token string">"text"</span><span class="token punctuation">:</span> row<span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token string">"survey_id"</span><span class="token punctuation">:</span> row<span class="token punctuation">[</span><span class="token number">2</span><span class="token punctuation">]</span><span class="token punctuation">}</span> <span class="token keyword">for</span> row <span class="token keyword">in</span> cur<span class="token punctuation">]</span>
|
||
|
|
db<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">return</span> json<span class="token punctuation">.</span>dumps<span class="token punctuation">(</span>result<span class="token punctuation">)</span>
|
||
|
|
|
||
|
|
case <span class="token string">"POST"</span><span class="token punctuation">:</span>
|
||
|
|
data <span class="token operator">=</span> request<span class="token punctuation">.</span>get_json<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
cur<span class="token punctuation">.</span>execute<span class="token punctuation">(</span><span class="token string">"INSERT INTO question (text, survey_id) VALUES (?, ?);"</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>data<span class="token punctuation">[</span><span class="token string">'text'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> data<span class="token punctuation">[</span><span class="token string">'survey_id'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
|
||
|
|
db<span class="token punctuation">.</span>commit<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
db<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">return</span> <span class="token string">'{"success": true}\n'</span>
|
||
|
|
|
||
|
|
case <span class="token string">"PUT"</span><span class="token punctuation">:</span>
|
||
|
|
data <span class="token operator">=</span> request<span class="token punctuation">.</span>get_json<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
cur<span class="token punctuation">.</span>execute<span class="token punctuation">(</span><span class="token string">"UPDATE question SET text = ?, survey_id = ? WHERE id = ?;"</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>data<span class="token punctuation">[</span><span class="token string">'text'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> data<span class="token punctuation">[</span><span class="token string">'survey_id'</span><span class="token punctuation">]</span><span class="token punctuation">,</span> data<span class="token punctuation">[</span><span class="token string">'id'</span><span class="token punctuation">]</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
|
||
|
|
db<span class="token punctuation">.</span>commit<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
db<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">return</span> <span class="token string">'{"success": true}\n'</span>
|
||
|
|
|
||
|
|
case <span class="token string">"DELETE"</span><span class="token punctuation">:</span>
|
||
|
|
data <span class="token operator">=</span> request<span class="token punctuation">.</span>get_json<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
cur<span class="token punctuation">.</span>execute<span class="token punctuation">(</span><span class="token string">"SELECT id, text, survey_id FROM question WHERE id = ?;"</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>data<span class="token punctuation">[</span><span class="token string">'id'</span><span class="token punctuation">]</span><span class="token punctuation">,</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
|
||
|
|
result <span class="token operator">=</span> <span class="token punctuation">[</span><span class="token punctuation">{</span><span class="token string">"id"</span><span class="token punctuation">:</span> row<span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token string">"text"</span><span class="token punctuation">:</span> row<span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">,</span> <span class="token string">"survey_id"</span><span class="token punctuation">:</span> row<span class="token punctuation">[</span><span class="token number">2</span><span class="token punctuation">]</span><span class="token punctuation">}</span> <span class="token keyword">for</span> row <span class="token keyword">in</span> cur<span class="token punctuation">]</span>
|
||
|
|
cur<span class="token punctuation">.</span>execute<span class="token punctuation">(</span><span class="token string">"DELETE FROM question WHERE id = ?;"</span><span class="token punctuation">,</span> <span class="token punctuation">(</span>data<span class="token punctuation">[</span><span class="token string">'id'</span><span class="token punctuation">]</span><span class="token punctuation">,</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
|
||
|
|
db<span class="token punctuation">.</span>commit<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
db<span class="token punctuation">.</span>close<span class="token punctuation">(</span><span class="token punctuation">)</span>
|
||
|
|
<span class="token keyword">return</span> json<span class="token punctuation">.</span>dumps<span class="token punctuation">(</span>result<span class="token punctuation">)</span>
|
||
|
|
|
||
|
|
</code></pre></div></div> <footer class="page-edit"><!----> <div class="last-updated"><span class="prefix">Останнє оновлення:</span> <span class="time">5/24/2024, 4:32:20 PM</span></div></footer> <div class="page-nav"><p class="inner"><span class="prev">
|
||
|
|
←
|
||
|
|
<a href="/edu-dis-labs-bodnar/design/" class="prev">
|
||
|
|
Проєктування архітектури системи
|
||
|
|
</a></span> <span class="next"><a href="/edu-dis-labs-bodnar/test/">
|
||
|
|
Тестування працездатності системи
|
||
|
|
</a>
|
||
|
|
→
|
||
|
|
</span></p></div> </main></div><div class="global-ui"><!----></div></div>
|
||
|
|
<script src="/edu-dis-labs-bodnar/assets/js/app.0f2f35e9.js" defer></script><script src="/edu-dis-labs-bodnar/assets/js/2.a7c45aa3.js" defer></script><script src="/edu-dis-labs-bodnar/assets/js/24.4951bab4.js" defer></script>
|
||
|
|
</body>
|
||
|
|
</html>
|