9Wcu7rx6

1-- Script for creating the tables and inserting the keywords used for voice commands in the database.
2-- Author: Gonçalo Martins
3-- 31/05/2019
4-- mysql -uroot -pnonius nivo_services < voice_bootstrap.sql
5
6DROP TABLE IF EXISTS voice_questions;
7DROP TABLE IF EXISTS voice_keywords;
8
9CREATE TABLE IF NOT EXISTS `voice_questions` (
10  `id` int(11) NOT NULL auto_increment,
11  `question` varchar(1024) NOT NULL,
12  `answer` varchar(1024) NOT NULL,
13  `language_id` int(11) NOT NULL, 
14  PRIMARY KEY  (`id`)
15) CHARACTER SET = utf8;
16ALTER TABLE `voice_questions` ADD CONSTRAINT FKvoice_questions FOREIGN KEY (language_id) REFERENCES languages(id);
17
18CREATE TABLE IF NOT EXISTS `voice_keywords` (
19  `id` int(11) NOT NULL auto_increment,
20  `keyword` varchar(255) NOT NULL,
21  `function` varchar(255) NOT NULL,
22  `language_id` int(11) NOT NULL, 
23  PRIMARY KEY  (`id`)
24) CHARACTER SET = utf8;
25ALTER TABLE `voice_keywords` ADD CONSTRAINT FKvoice_keywords FOREIGN KEY (language_id) REFERENCES languages(id);
26ALTER TABLE `voice_keywords` ADD UNIQUE `UNQkeyword_language`(`keyword`, `language_id`);
27
28
29TRUNCATE TABLE voice_keywords;
30-- Portuguese keywords (id = @pt_id)
31SET @pt = 'PT';
32SELECT id INTO @pt_id FROM languages WHERE code=@pt;
33INSERT INTO voice_keywords(keyword, function, language_id) VALUES('abrir', 'open', @pt_id);
34INSERT INTO voice_keywords(keyword, function, language_id) VALUES('abre', 'open', @pt_id);
35INSERT INTO voice_keywords(keyword, function, language_id) VALUES('abra', 'open', @pt_id);
36INSERT INTO voice_keywords(keyword, function, language_id) VALUES('lance', 'open', @pt_id);
37INSERT INTO voice_keywords(keyword, function, language_id) VALUES('lancar', 'open', @pt_id);
38INSERT INTO voice_keywords(keyword, function, language_id) VALUES('lanca', 'open', @pt_id);
39INSERT INTO voice_keywords(keyword, function, language_id) VALUES('iniciar', 'open', @pt_id);
40INSERT INTO voice_keywords(keyword, function, language_id) VALUES('inicia', 'open', @pt_id);
41INSERT INTO voice_keywords(keyword, function, language_id) VALUES('inicie', 'open', @pt_id);
42INSERT INTO voice_keywords(keyword, function, language_id) VALUES('perguntar', 'answer', @pt_id);
43INSERT INTO voice_keywords(keyword, function, language_id) VALUES('diga', 'answer', @pt_id);
44INSERT INTO voice_keywords(keyword, function, language_id) VALUES('diz', 'answer', @pt_id);
45INSERT INTO voice_keywords(keyword, function, language_id) VALUES('dizer', 'answer', @pt_id);
46INSERT INTO voice_keywords(keyword, function, language_id) VALUES('desligar', 'turnOff', @pt_id);
47INSERT INTO voice_keywords(keyword, function, language_id) VALUES('desliga', 'turnOff', @pt_id);
48INSERT INTO voice_keywords(keyword, function, language_id) VALUES('desligue', 'turnOff', @pt_id);
49INSERT INTO voice_keywords(keyword, function, language_id) VALUES('ligar', 'turnOn', @pt_id);
50INSERT INTO voice_keywords(keyword, function, language_id) VALUES('liga', 'turnOn', @pt_id);
51INSERT INTO voice_keywords(keyword, function, language_id) VALUES('ligue', 'turnOn', @pt_id);
52INSERT INTO voice_keywords(keyword, function, language_id) VALUES('reproduzir', 'play', @pt_id);
53INSERT INTO voice_keywords(keyword, function, language_id) VALUES('reproduza', 'play', @pt_id);
54INSERT INTO voice_keywords(keyword, function, language_id) VALUES('reproduz', 'play', @pt_id);
55INSERT INTO voice_keywords(keyword, function, language_id) VALUES('ver', 'play', @pt_id);
56INSERT INTO voice_keywords(keyword, function, language_id) VALUES('tocar', 'play', @pt_id);
57INSERT INTO voice_keywords(keyword, function, language_id) VALUES('toca', 'play', @pt_id);
58INSERT INTO voice_keywords(keyword, function, language_id) VALUES('toque', 'play', @pt_id);
59INSERT INTO voice_keywords(keyword, function, language_id) VALUES('canal', 'play', @pt_id);
60INSERT INTO voice_keywords(keyword, function, language_id) VALUES('pesquisar', 'search', @pt_id);
61INSERT INTO voice_keywords(keyword, function, language_id) VALUES('pesquisa', 'search', @pt_id);
62INSERT INTO voice_keywords(keyword, function, language_id) VALUES('pesquise', 'search', @pt_id);
63INSERT INTO voice_keywords(keyword, function, language_id) VALUES('procurar', 'search', @pt_id);
64INSERT INTO voice_keywords(keyword, function, language_id) VALUES('procura', 'search', @pt_id);
65INSERT INTO voice_keywords(keyword, function, language_id) VALUES('procure', 'search', @pt_id);
66INSERT INTO voice_keywords(keyword, function, language_id) VALUES('retroceder', 'home', @pt_id);
67INSERT INTO voice_keywords(keyword, function, language_id) VALUES('voltar', 'home', @pt_id);
68INSERT INTO voice_keywords(keyword, function, language_id) VALUES('sair', 'home', @pt_id);
69INSERT INTO voice_keywords(keyword, function, language_id) VALUES('fechar', 'home', @pt_id);
70INSERT INTO voice_keywords(keyword, function, language_id) VALUES('menu', 'home', @pt_id);
71-- English keywords (id = @en_id)
72SET @en = 'EN';
73SELECT id INTO @en_id FROM languages WHERE code=@en;
74INSERT INTO voice_keywords(keyword, function, language_id) VALUES('open', 'open', @en_id);
75INSERT INTO voice_keywords(keyword, function, language_id) VALUES('launch', 'open', @en_id);
76INSERT INTO voice_keywords(keyword, function, language_id) VALUES('start', 'open', @en_id);
77INSERT INTO voice_keywords(keyword, function, language_id) VALUES('ask', 'answer', @en_id);
78INSERT INTO voice_keywords(keyword, function, language_id) VALUES('tell', 'answer', @en_id);
79INSERT INTO voice_keywords(keyword, function, language_id) VALUES('inquire', 'answer', @en_id);
80INSERT INTO voice_keywords(keyword, function, language_id) VALUES('turn', 'turnOnOrOff', @en_id);
81INSERT INTO voice_keywords(keyword, function, language_id) VALUES('disconnect', 'turnOff', @en_id);
82INSERT INTO voice_keywords(keyword, function, language_id) VALUES('connect', 'turnOn', @en_id);
83INSERT INTO voice_keywords(keyword, function, language_id) VALUES('power', 'turnOnOrOff', @en_id);
84INSERT INTO voice_keywords(keyword, function, language_id) VALUES('deactivate', 'turnOff', @en_id);
85INSERT INTO voice_keywords(keyword, function, language_id) VALUES('activate', 'turnOn', @en_id);
86INSERT INTO voice_keywords(keyword, function, language_id) VALUES('shut', 'turnOnOrOff', @en_id);
87INSERT INTO voice_keywords(keyword, function, language_id) VALUES('switch', 'turnOnOrOff', @en_id);
88INSERT INTO voice_keywords(keyword, function, language_id) VALUES('play', 'play', @en_id);
89INSERT INTO voice_keywords(keyword, function, language_id) VALUES('watch', 'play', @en_id);
90INSERT INTO voice_keywords(keyword, function, language_id) VALUES('channel', 'play', @en_id);
91INSERT INTO voice_keywords(keyword, function, language_id) VALUES('search', 'search', @en_id);
92INSERT INTO voice_keywords(keyword, function, language_id) VALUES('seek', 'search', @en_id);
93INSERT INTO voice_keywords(keyword, function, language_id) VALUES('find', 'search', @en_id);
94INSERT INTO voice_keywords(keyword, function, language_id) VALUES('home', 'home', @en_id);
95INSERT INTO voice_keywords(keyword, function, language_id) VALUES('back', 'home', @en_id);
96INSERT INTO voice_keywords(keyword, function, language_id) VALUES('exit', 'home', @en_id);
97INSERT INTO voice_keywords(keyword, function, language_id) VALUES('return', 'home', @en_id);
98INSERT INTO voice_keywords(keyword, function, language_id) VALUES('leave', 'home', @en_id);
99INSERT INTO voice_keywords(keyword, function, language_id) VALUES('quit', 'home', @en_id);
100INSERT INTO voice_keywords(keyword, function, language_id) VALUES('close', 'home', @en_id);
101-- French keywords (id = @fr_id)
102-- ouvrir|allumer|fermer|chaine|jouer|lancer|chercher|trouver|sortie|menu|retour
103SET @fr = 'FR';
104SELECT id INTO @fr_id FROM languages WHERE code=@fr;
105INSERT INTO voice_keywords(keyword, function, language_id) VALUES('ouvrir', 'open', @fr_id);
106INSERT INTO voice_keywords(keyword, function, language_id) VALUES('lancer', 'open', @fr_id);
107INSERT INTO voice_keywords(keyword, function, language_id) VALUES('question', 'answer', @fr_id);
108INSERT INTO voice_keywords(keyword, function, language_id) VALUES('interrogation', 'answer', @fr_id);
109INSERT INTO voice_keywords(keyword, function, language_id) VALUES('allumer', 'turnOn', @fr_id);
110INSERT INTO voice_keywords(keyword, function, language_id) VALUES('fermer', 'turnOff', @fr_id);
111INSERT INTO voice_keywords(keyword, function, language_id) VALUES('chaine', 'play', @fr_id);
112INSERT INTO voice_keywords(keyword, function, language_id) VALUES('jouer', 'play', @fr_id);
113INSERT INTO voice_keywords(keyword, function, language_id) VALUES('trouver', 'search', @fr_id);
114INSERT INTO voice_keywords(keyword, function, language_id) VALUES('chercher', 'search', @fr_id);
115INSERT INTO voice_keywords(keyword, function, language_id) VALUES('sortie', 'home', @fr_id);
116INSERT INTO voice_keywords(keyword, function, language_id) VALUES('menu', 'home', @fr_id);
117INSERT INTO voice_keywords(keyword, function, language_id) VALUES('retour', 'home', @fr_id);
118-- Spanish keywords (id = @es_id)
119SET @es = 'ES';
120SELECT id INTO @es_id FROM languages WHERE code=@es;
121INSERT INTO voice_keywords(keyword, function, language_id) VALUES('abrir', 'open', @es_id);
122INSERT INTO voice_keywords(keyword, function, language_id) VALUES('abre', 'open', @es_id);
123INSERT INTO voice_keywords(keyword, function, language_id) VALUES('preguntar', 'answer', @es_id);
124INSERT INTO voice_keywords(keyword, function, language_id) VALUES('desconectar', 'turnOff', @es_id);
125INSERT INTO voice_keywords(keyword, function, language_id) VALUES('desconecta', 'turnOff', @es_id);
126INSERT INTO voice_keywords(keyword, function, language_id) VALUES('conectar', 'turnOn', @es_id);
127INSERT INTO voice_keywords(keyword, function, language_id) VALUES('conecta', 'turnOn', @es_id);
128INSERT INTO voice_keywords(keyword, function, language_id) VALUES('reproducir', 'play', @es_id);
129INSERT INTO voice_keywords(keyword, function, language_id) VALUES('juega', 'play', @es_id);
130INSERT INTO voice_keywords(keyword, function, language_id) VALUES('jugar', 'play', @es_id);
131INSERT INTO voice_keywords(keyword, function, language_id) VALUES('ver', 'play', @es_id);
132INSERT INTO voice_keywords(keyword, function, language_id) VALUES('buscar', 'search', @es_id);
133INSERT INTO voice_keywords(keyword, function, language_id) VALUES('busca', 'search', @es_id);
134INSERT INTO voice_keywords(keyword, function, language_id) VALUES('salir', 'home', @es_id);
135INSERT INTO voice_keywords(keyword, function, language_id) VALUES('atras', 'home', @es_id);
136INSERT INTO voice_keywords(keyword, function, language_id) VALUES('retiro', 'home', @es_id);
137-- German keywords (id = @de_id)
138-- offne|starte|einschalten|ausschalte|ausfahrt|aus|abspiele|kanal|suche|finde|verlassen|retour
139SET @de = 'DE';
140SELECT id INTO @de_id FROM languages WHERE code=@de;
141INSERT INTO voice_keywords(keyword, function, language_id) VALUES('offne', 'open', @de_id);
142INSERT INTO voice_keywords(keyword, function, language_id) VALUES('starte', 'open', @de_id);
143INSERT INTO voice_keywords(keyword, function, language_id) VALUES('fragen', 'answer', @de_id);
144INSERT INTO voice_keywords(keyword, function, language_id) VALUES('einschalten', 'turnOn', @de_id);
145INSERT INTO voice_keywords(keyword, function, language_id) VALUES('ausschalte', 'turnOff', @de_id);
146INSERT INTO voice_keywords(keyword, function, language_id) VALUES('aus', 'turnOff', @de_id);
147INSERT INTO voice_keywords(keyword, function, language_id) VALUES('kanal', 'play', @de_id);
148INSERT INTO voice_keywords(keyword, function, language_id) VALUES('abspiele', 'play', @de_id);
149INSERT INTO voice_keywords(keyword, function, language_id) VALUES('spiele', 'play', @de_id);
150INSERT INTO voice_keywords(keyword, function, language_id) VALUES('schaue', 'play', @de_id);
151INSERT INTO voice_keywords(keyword, function, language_id) VALUES('suche', 'search', @de_id);
152INSERT INTO voice_keywords(keyword, function, language_id) VALUES('finde', 'search', @de_id);
153INSERT INTO voice_keywords(keyword, function, language_id) VALUES('retour', 'home', @de_id);
154INSERT INTO voice_keywords(keyword, function, language_id) VALUES('verlassen', 'home', @de_id);
155-- Danish keywords (id = @da_id)
156-- aben|start|tilslut|sluk|afspil|spille|se|kanal|jage|menu|udgang|bortgang|afslut|forlade|glemme
157SET @da = 'DA';
158SELECT id INTO @da_id FROM languages WHERE code=@da;
159INSERT INTO voice_keywords(keyword, function, language_id) VALUES('aben', 'open', @da_id);
160INSERT INTO voice_keywords(keyword, function, language_id) VALUES('start', 'open', @da_id);
161INSERT INTO voice_keywords(keyword, function, language_id) VALUES('tilslut', 'turnOn', @da_id);
162INSERT INTO voice_keywords(keyword, function, language_id) VALUES('sluk', 'turnOff', @da_id);
163INSERT INTO voice_keywords(keyword, function, language_id) VALUES('afspil', 'play', @da_id);
164INSERT INTO voice_keywords(keyword, function, language_id) VALUES('spille', 'play', @da_id);
165INSERT INTO voice_keywords(keyword, function, language_id) VALUES('se', 'play', @da_id);
166INSERT INTO voice_keywords(keyword, function, language_id) VALUES('kanal', 'play', @da_id);
167INSERT INTO voice_keywords(keyword, function, language_id) VALUES('jage', 'search', @da_id);
168INSERT INTO voice_keywords(keyword, function, language_id) VALUES('menu', 'home', @da_id);
169INSERT INTO voice_keywords(keyword, function, language_id) VALUES('udgang', 'home', @da_id);
170INSERT INTO voice_keywords(keyword, function, language_id) VALUES('bortgang', 'home', @da_id);
171INSERT INTO voice_keywords(keyword, function, language_id) VALUES('afslut', 'home', @da_id);
172INSERT INTO voice_keywords(keyword, function, language_id) VALUES('forlade', 'home', @da_id);
173INSERT INTO voice_keywords(keyword, function, language_id) VALUES('glemme', 'home', @da_id);
174-- Dutch keywords (id = @nl_id)
175-- open|schakel|speel|kanaal|zoek|vind|menu|terug|uitgang|verlaten
176SET @nl = 'NL';
177SELECT id INTO @nl_id FROM languages WHERE code=@nl;
178INSERT INTO voice_keywords(keyword, function, language_id) VALUES('open', 'open', @nl_id);
179INSERT INTO voice_keywords(keyword, function, language_id) VALUES('vragen', 'answer', @nl_id);
180INSERT INTO voice_keywords(keyword, function, language_id) VALUES('schakel', 'turnOnOrOff', @nl_id);
181INSERT INTO voice_keywords(keyword, function, language_id) VALUES('speel', 'play', @nl_id);
182INSERT INTO voice_keywords(keyword, function, language_id) VALUES('kanaal', 'play', @nl_id);
183INSERT INTO voice_keywords(keyword, function, language_id) VALUES('zoek', 'search', @nl_id);
184INSERT INTO voice_keywords(keyword, function, language_id) VALUES('vind', 'search', @nl_id);
185INSERT INTO voice_keywords(keyword, function, language_id) VALUES('menu', 'home', @nl_id);
186INSERT INTO voice_keywords(keyword, function, language_id) VALUES('terug', 'home', @nl_id);
187INSERT INTO voice_keywords(keyword, function, language_id) VALUES('uitgang', 'home', @nl_id);
188INSERT INTO voice_keywords(keyword, function, language_id) VALUES('verlaten', 'home', @nl_id);
189-- Russian keywords (id = @ru_id)
190-- otkryt|otkrit|vklyuchi|viklyuchi|kanal|smotret|iskat|nahodit|vernut
191SET @ru = 'RU';
192SELECT id INTO @ru_id FROM languages WHERE code=@ru;
193INSERT INTO voice_keywords(keyword, function, language_id) VALUES('otkryt', 'open', @ru_id);
194INSERT INTO voice_keywords(keyword, function, language_id) VALUES('otkrit', 'open', @ru_id);
195INSERT INTO voice_keywords(keyword, function, language_id) VALUES('sprosit', 'answer', @ru_id);
196INSERT INTO voice_keywords(keyword, function, language_id) VALUES('vklyuchi', 'turnOn', @ru_id);
197INSERT INTO voice_keywords(keyword, function, language_id) VALUES('vyklyuchi', 'turnOff', @ru_id);
198INSERT INTO voice_keywords(keyword, function, language_id) VALUES('viklyuchi', 'turnOff', @ru_id);
199INSERT INTO voice_keywords(keyword, function, language_id) VALUES('kanal', 'play', @ru_id);
200INSERT INTO voice_keywords(keyword, function, language_id) VALUES('igrat', 'play', @ru_id);
201INSERT INTO voice_keywords(keyword, function, language_id) VALUES('smotret', 'play', @ru_id);
202INSERT INTO voice_keywords(keyword, function, language_id) VALUES('iskat', 'search', @ru_id);
203INSERT INTO voice_keywords(keyword, function, language_id) VALUES('nahodit', 'search', @ru_id);
204INSERT INTO voice_keywords(keyword, function, language_id) VALUES('vernut', 'home', @ru_id);
205-- Bootstrap some questions, just note that the answer has to be "speakable" using the TextToSpeech mechanism
206INSERT INTO voice_questions(question, answer, language_id) VALUES("what is the company's name", "the company's name is Nonius", @en_id);
207INSERT INTO voice_questions(question, answer, language_id) VALUES("how can I get more information", "just go to www dot Nonius Software dot com", @en_id);
208INSERT INTO voice_questions(question, answer, language_id) VALUES("why is peter pan flying all the time", "because he neverlands", @en_id);
209INSERT INTO voice_questions(question, answer, language_id) VALUES("qual o nome da empresa", "o nome da empresa é Nonius", @pt_id);
210INSERT INTO voice_questions(question, answer, language_id) VALUES("como posso obter mais informações", "vá a www ponto nonius software ponto com", @pt_id);
211INSERT INTO voice_questions(question, answer, language_id) VALUES("como posso saber mais", "vá a www ponto nonius software ponto com", @pt_id);
212INSERT INTO voice_questions(question, answer, language_id) VALUES("que nome se dá a uma ferramenta perdida", "uma foice", @pt_id);
213INSERT INTO voice_questions(question, answer, language_id) VALUES("como se chama uma ferramenta perdida", "uma foice", @pt_id);
214
215SELECT voice_keywords.keyword, voice_keywords.function, languages.code as language
216FROM voice_keywords
217INNER JOIN languages
218ON voice_keywords.language_id = languages.id
219ORDER BY languages.code;