Projet

Général

Profil

US #251 » SCORE_SQL_V3.sql

ahlem belgacem, 25/07/2023 13:20

 
1
use score ;
2
CREATE TABLE themes(id int NOT NULL AUTO_INCREMENT, themeId varchar(255) NOT NULL, name text NOT NULL, language enum('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN', PRIMARY KEY(id)) ;
3
CREATE TABLE hashtags(id int NOT NULL AUTO_INCREMENT, hashtagId varchar(255) NOT NULL, name text NOT NULL, type enum('BuildingBlocks', 'Practice') NOT NULL DEFAULT 'BuildingBlocks', language enum('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN', PRIMARY KEY(id)) ;
4
CREATE TABLE learning_objectives(id int NOT NULL AUTO_INCREMENT, learningId varchar(255) NOT NULL, name text NOT NULL, language enum('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'FR', PRIMARY KEY(id)); 
5
CREATE TABLE files(id int NOT NULL AUTO_INCREMENT, description text NOT NULL, pathfile varchar(255) NOT NULL, extension varchar(255) NOT NULL, buildingBlockId int NOT NULL, PRIMARY KEY(id)) ;
6
CREATE TABLE links(id int NOT NULL AUTO_INCREMENT, lien varchar(255) NOT NULL, descriptionLien text NOT NULL, buildingBlockId int NOT NULL, PRIMARY KEY(id)) ;
7
CREATE TABLE formats(id int NOT NULL AUTO_INCREMENT, formatId varchar(255) NOT NULL, name text NOT NULL, language enum('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN', PRIMARY KEY(id)) ;
8
CREATE TABLE building_blocks(id int NOT NULL AUTO_INCREMENT, buildingBlocksgroup varchar(255) NOT NULL, title text NOT NULL, duration varchar(255) NOT NULL, views int NOT NULL, likes int NOT NULL, createdAt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), shortDescription text NOT NULL, longDescription text NOT NULL, status enum('save', 'publish') NOT NULL DEFAULT 'save', language enum('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN', authorId int NULL, PRIMARY KEY(id)) ;
9
CREATE TABLE building_block_step_by_steps(id int NOT NULL AUTO_INCREMENT, title text NOT NULL, numstep int NOT NULL, description text NOT NULL, buildingBlockId int NOT NULL, PRIMARY KEY(id)) ;
10
CREATE TABLE authors(id int NOT NULL AUTO_INCREMENT, name text NOT NULL, phoneNumber varchar(255) NOT NULL, title varchar(255) NOT NULL, email varchar(255) NOT NULL, country varchar(255) NOT NULL, linkedIn varchar(255) NOT NULL, site varchar(255) NOT NULL, Image varchar(255) NULL, UNIQUE INDEX IDX_ea066641108f693660071dfa79(email), PRIMARY KEY(id)) ;
11
CREATE TABLE domains(id int NOT NULL AUTO_INCREMENT, domainId varchar(255) NOT NULL, name text NOT NULL, language enum('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN', PRIMARY KEY(id)) ;
12
CREATE TABLE languages(id varchar(2) NOT NULL, name varchar(2) NOT NULL, PRIMARY KEY(id)) ;
13
CREATE TABLE feed_back ( id int NOT NULL AUTO_INCREMENT, session text NOT NULL, firstname varchar(255) NOT NULL, lastname varchar(255) NOT NULL, email varchar(255) NOT NULL, goodpoint text NOT NULL, tutorimprove text NOT NULL, additionnalComment text NOT NULL, language enum ('FR', 'EN', 'ES', 'LT') NOT NULL DEFAULT 'EN', usefulness enum ('verygood', 'good', 'fair', 'poor', 'verypoor') NOT NULL DEFAULT 'verygood',confident enum ('yes', 'no', 'unsure') NOT NULL DEFAULT 'no', content enum ('verygood', 'good','fair', 'poor', 'verypoor') NOT NULL DEFAULT 'verygood', teaching enum ('verygood', 'good', 'fair', 'poor', 'verypoor')NOT NULL DEFAULT 'verygood',datePublication datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), PRIMARY KEY (`id`));
14
CREATE TABLE items(id int NOT NULL AUTO_INCREMENT, title varchar(255) NOT NULL, description varchar(255) NOT NULL, price int NOT NULL, PRIMARY KEY(id)) ;
15
CREATE TABLE users(id int NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, lastName varchar(255) NOT NULL, email varchar(255) NOT NULL, password varchar(255) NOT NULL, role enum('user', 'admin') NOT NULL DEFAULT 'admin', createdAt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), updatedAt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), UNIQUE INDEX IDX_97672ac88f789774dd47f7c8be(email), PRIMARY KEY(id)) ;
16
CREATE TABLE buildingBlocks_Formats(buildingBlocks_id int NOT NULL, format_id int NOT NULL, INDEX IDX_30c7ceed9a3d610b4a0f5deddd(buildingBlocks_id), INDEX IDX_cf31615cd97841ae456c50cb29(format_id), PRIMARY KEY(buildingBlocks_id, format_id)); 
17
CREATE TABLE buildingBlocks_Themes(buildingBlocks_id int NOT NULL, themes_id int NOT NULL, INDEX IDX_ba53d2f4d30592d9a0798a00e2(buildingBlocks_id), INDEX IDX_4aa82abe6eb7165e0a147a974c(themes_id), PRIMARY KEY(buildingBlocks_id, themes_id)) ;
18
CREATE TABLE buildingBlocks_Hashtags(buildingBlocks_id int NOT NULL, hashtags_id int NOT NULL, INDEX IDX_1b5afa6ca51a4f323707b7c8e5(buildingBlocks_id), INDEX IDX_67cfefb5759411a5c3f1d32316(hashtags_id), PRIMARY KEY(buildingBlocks_id, hashtags_id)); 
19
CREATE TABLE buildingBlocks_LearningObjectives(buildingBlocks_id int NOT NULL, LearningObjectives_id int NOT NULL, INDEX IDX_4b114aa36e86661f974c81705f(buildingBlocks_id), INDEX IDX_1f0e8446ce1619524eb28da0b2(LearningObjectives_id), PRIMARY KEY(buildingBlocks_id, LearningObjectives_id)) ;
20
ALTER TABLE files ADD CONSTRAINT FK_26d5f2c46a0297c1a8d84738fda FOREIGN KEY(buildingBlockId) REFERENCES building_blocks(id);
21
ALTER TABLE links ADD CONSTRAINT FK_594b2392f112921a9d73c8f9faf FOREIGN KEY(buildingBlockId) REFERENCES building_blocks(id);
22
ALTER TABLE building_blocks ADD CONSTRAINT FK_b8a114cd05468cc2e8fac012756 FOREIGN KEY(authorId) REFERENCES authors(id);
23
ALTER TABLE building_block_step_by_steps ADD CONSTRAINT FK_3723c3e00d10a8754c455958aa4 FOREIGN KEY(buildingBlockId) REFERENCES building_blocks(id);
24
ALTER TABLE buildingBlocks_Formats ADD CONSTRAINT FK_30c7ceed9a3d610b4a0f5deddda FOREIGN KEY(buildingBlocks_id) REFERENCES building_blocks(id);
25
ALTER TABLE buildingBlocks_Formats ADD CONSTRAINT FK_cf31615cd97841ae456c50cb291 FOREIGN KEY(format_id) REFERENCES formats(id);
26
ALTER TABLE buildingBlocks_Themes ADD CONSTRAINT FK_ba53d2f4d30592d9a0798a00e28 FOREIGN KEY(buildingBlocks_id) REFERENCES building_blocks(id);
27
ALTER TABLE buildingBlocks_Themes ADD CONSTRAINT FK_4aa82abe6eb7165e0a147a974c6 FOREIGN KEY(themes_id) REFERENCES themes(id);
28
ALTER TABLE buildingBlocks_Hashtags ADD CONSTRAINT FK_1b5afa6ca51a4f323707b7c8e58 FOREIGN KEY(buildingBlocks_id) REFERENCES building_blocks(id);
29
ALTER TABLE buildingBlocks_Hashtags ADD CONSTRAINT FK_67cfefb5759411a5c3f1d32316e FOREIGN KEY(hashtags_id) REFERENCES hashtags(id);
30
ALTER TABLE buildingBlocks_LearningObjectives ADD CONSTRAINT FK_4b114aa36e86661f974c81705fe FOREIGN KEY(buildingBlocks_id) REFERENCES building_blocks(id);
31
ALTER TABLE buildingBlocks_LearningObjectives ADD CONSTRAINT FK_1f0e8446ce1619524eb28da0b2a FOREIGN KEY(LearningObjectives_id) REFERENCES learning_objectives(id);
32
INSERT INTO formats(id, formatId, name, language) VALUES
33
		('1', 'd657ca21-c77e-4b47-8b6c-e47b7df8dec4', 'Teacher guidance','EN'),
34
		('2', 'd657ca21-c77e-4b47-8b6c-e47b7df8dec4', 'Orientation de l enseignant','FR'),
35
		('3', 'd657ca21-c77e-4b47-8b6c-e47b7df8dec4', 'Orientación del profesor','ES'),
36
		('4', 'd657ca21-c77e-4b47-8b6c-e47b7df8dec4', 'Mokytojo nurodymas','LT'),
37
		('5', 'fdd207a2-3b64-4b89-aec0-c177c65583fb', 'Workbook','EN'),
38
		('6', 'fdd207a2-3b64-4b89-aec0-c177c65583fb', 'Cahier','FR'),
39
		('7', 'fdd207a2-3b64-4b89-aec0-c177c65583fb', 'Libro de trabajo','ES'),
40
		('8', 'fdd207a2-3b64-4b89-aec0-c177c65583fb', 'Darbo knyga', 'LT'),
41
		('9', '481167a4-02ff-4479-8b27-d81d4b700448', 'Lesson Plan','EN'),
42
		('10', '481167a4-02ff-4479-8b27-d81d4b700448', 'Plan de cours','FR'),
43
		('11', '481167a4-02ff-4479-8b27-d81d4b700448', 'Plan de estudios', 'ES'),
44
		('12', '481167a4-02ff-4479-8b27-d81d4b700448', 'Pamokos planas', 'LT'),
45
		('13', 'a09702af-b453-4d28-9d38-eb5bd047910d', 'Module', 'EN'),
46
		('14', 'a09702af-b453-4d28-9d38-eb5bd047910d', 'Module','FR'),
47
		('15', 'a09702af-b453-4d28-9d38-eb5bd047910d', 'Módulo','ES'),
48
		('16', 'a09702af-b453-4d28-9d38-eb5bd047910d', 'Modulis','LT');
49

    
50
INSERT INTO themes(id, themeId, name, language) VALUES
51
		('1', '3144097b-d324-4fc5-ac57-e0c201327ad0', 'Material flows and ecologies','EN'),
52
		('2', '3144097b-d324-4fc5-ac57-e0c201327ad0', 'Flux de matières et écologies','FR'),
53
		('3', '3144097b-d324-4fc5-ac57-e0c201327ad0', 'Flujos de materiales y ecologías','ES'),
54
		('4', '3144097b-d324-4fc5-ac57-e0c201327ad0', 'Medžiagų srautai ir ekologijos','LT'),
55
		('5', '2690ef9f-9c19-4d2b-be1a-dbadca5debf7', 'Products and Technologies', 'EN'),
56
		('6', '2690ef9f-9c19-4d2b-be1a-dbadca5debf7', 'Produits et Technologies','FR'),
57
		('7', '2690ef9f-9c19-4d2b-be1a-dbadca5debf7', 'Productos y Tecnologías','ES'),
58
		('8', '2690ef9f-9c19-4d2b-be1a-dbadca5debf7', 'Produktai ir technologijos','LT'),
59
		('9', '39c4a460-3215-4ecc-a753-6e741d5b7b8e', 'People and Society','EN'),
60
		('10', '39c4a460-3215-4ecc-a753-6e741d5b7b8e', 'Les gens et la société','FR'),
61
		('11', '39c4a460-3215-4ecc-a753-6e741d5b7b8e', 'Gente y Sociedad', 'ES'),
62
		('12', '39c4a460-3215-4ecc-a753-6e741d5b7b8e', 'Žmonės ir visuomenė','LT'),
63
		('13', '25c461df-6da7-42a3-b6fc-9d0f0cbc61a9', 'Economy and policies', 'EN'),
64
		('14', '25c461df-6da7-42a3-b6fc-9d0f0cbc61a9', 'Économie et politiques','FR'),
65
		('15', '25c461df-6da7-42a3-b6fc-9d0f0cbc61a9', 'Economía y políticas', 'ES'),
66
		('16', '25c461df-6da7-42a3-b6fc-9d0f0cbc61a9', 'Ekonomika ir politika','LT'),
67
		('17', 'eb3ec847-6f48-47a9-937f-1181999c0ac9', 'CE & YOU','EN'),
68
		('18', 'eb3ec847-6f48-47a9-937f-1181999c0ac9', 'CE et YOU','FR'),
69
		('19', 'eb3ec847-6f48-47a9-937f-1181999c0ac9', 'CE y TU','ES'),
70
		('20', 'eb3ec847-6f48-47a9-937f-1181999c0ac9', 'CE IR TU','LT');
71
 
72
INSERT INTO hashtags (id, hashtagId, name, type, language) VALUES
73
        ('1', '7f06d145-048b-4adf-b2ac-333e00caad76', 'Health', 'BuildingBlocks', 'EN'),
74
        ('2', '7f06d145-048b-4adf-b2ac-333e00caad76', 'Santé', 'BuildingBlocks', 'FR'),
75
        ('3', '7f06d145-048b-4adf-b2ac-333e00caad76', 'Salud', 'BuildingBlocks', 'ES'),
76
        ('4', '7f06d145-048b-4adf-b2ac-333e00caad76', 'Sveikata', 'BuildingBlocks', 'LT'),
77
        ('5', 'b54b8b8d-c5ff-48d0-b988-63cc1bd9f9bd', 'Energy', 'BuildingBlocks', 'EN'),
78
        ('6', 'b54b8b8d-c5ff-48d0-b988-63cc1bd9f9bd', 'Energie', 'BuildingBlocks', 'FR'),
79
        ('7', 'b54b8b8d-c5ff-48d0-b988-63cc1bd9f9bd', 'Energía', 'BuildingBlocks', 'ES'),
80
        ('8', 'b54b8b8d-c5ff-48d0-b988-63cc1bd9f9bd', 'Energija', 'BuildingBlocks', 'LT'),
81
        ('9', '1a0246e7-0de7-4be0-a16f-c16934bd53a7', 'blockchain', 'BuildingBlocks', 'EN'),
82
        ('10', '1a0246e7-0de7-4be0-a16f-c16934bd53a7', 'blockchain', 'BuildingBlocks', 'FR'),
83
        ('11', '1a0246e7-0de7-4be0-a16f-c16934bd53a7', 'cadena de bloques', 'BuildingBlocks', 'ES'),
84
        ('12', '1a0246e7-0de7-4be0-a16f-c16934bd53a7', 'blockchain', 'BuildingBlocks', 'LT'),
85
		('13', '7f06d145-048b-4adf-b2ac-333e00caad76', 'Health', 'Practice', 'EN'),
86
		('14', '7f06d145-048b-4adf-b2ac-333e00caad76', 'Santé', 'Practice', 'FR'),
87
		('15', '7f06d145-048b-4adf-b2ac-333e00caad76', 'Salud', 'Practice', 'ES'),
88
		('16', '7f06d145-048b-4adf-b2ac-333e00caad76', 'Sveikata', 'Practice', 'LT'),
89
		('17', 'b54b8b8d-c5ff-48d0-b988-63cc1bd9f9bd', 'Energy', 'Practice', 'EN'),
90
		('18', 'b54b8b8d-c5ff-48d0-b988-63cc1bd9f9bd', 'Energie', 'Practice', 'FR'),
91
		('19', 'b54b8b8d-c5ff-48d0-b988-63cc1bd9f9bd', 'Energía', 'Practice', 'ES'),
92
		 ('20', 'b54b8b8d-c5ff-48d0-b988-63cc1bd9f9bd', 'Energija', 'Practice', 'LT');      
93
INSERT INTO learning_objectives(id, learningId, name, language) VALUES
94
		('1', '920a147b-16ea-46de-b4ba-08b18f128a4d', 'Enhance the teachers skills towards circular economies', 'EN'),
95
		('2', '920a147b-16ea-46de-b4ba-08b18f128a4d', 'Améliorer les compétences de l enseignant vers les économies circulaires', 'FR'),
96
		('3', '920a147b-16ea-46de-b4ba-08b18f128a4d', 'Potenciar las habilidades del docente hacia las economías circulares','ES'),
97
		('4', '920a147b-16ea-46de-b4ba-08b18f128a4d', 'Tobulinkite mokytojo įgūdžius susijusius su žiedine ekonomika', 'LT'),
98
		('5', '9cd984ba-3cfb-4d12-8085-0d5f3dc3c457', 'Support the teacher to design and implement challenge-based projects in school', 'EN'),
99
		('6', '9cd984ba-3cfb-4d12-8085-0d5f3dc3c457', 'Soutenir l enseignant pour concevoir et mettre en œuvre des projets basés sur des défis à l école','FR'),
100
		('7', '9cd984ba-3cfb-4d12-8085-0d5f3dc3c457', 'Apoyar al maestro para diseñar e implementar proyectos basados en desafíos en la escuela', 'ES'),
101
		('8', '9cd984ba-3cfb-4d12-8085-0d5f3dc3c457', 'Padėkite mokytojui kurti ir įgyvendinti iššūkiais pagrįstus projektus mokykloje','LT'),
102
		('9', '4ecde2c2-273d-4a2f-b2bd-37eb99e680af', 'Teach the teacher how to reflect with human-centred design approaches','EN'),
103
		('10', '4ecde2c2-273d-4a2f-b2bd-37eb99e680af', 'Enseigner à l enseignant comment réfléchir avec des approches de conception centrées sur l humain', 'FR'),
104
		('11', '4ecde2c2-273d-4a2f-b2bd-37eb99e680af', 'Enseñar al profesor a reflexionar con enfoques de diseño centrados en el ser humano', 'ES'),
105
		('12', '4ecde2c2-273d-4a2f-b2bd-37eb99e680af', 'Išmokykite mokytoją reflektuoti taikant į žmogų orientuoto dizaino metodus', 'LT'),
106
		('13', 'ac345346-ebfe-49fd-a221-7bc645de294b', 'Help the teacher to better interact and collaborate inside and outside the classroom','EN'),
107
		('14', 'ac345346-ebfe-49fd-a221-7bc645de294b', 'Aider l enseignant à mieux interagir et collaborer à l intérieur et à l extérieur de la classe', 'FR'),
108
		('15', 'ac345346-ebfe-49fd-a221-7bc645de294b', 'Ayudar al profesor a interactuar y colaborar mejor dentro y fuera del aula.', 'ES'),
109
		('16', 'ac345346-ebfe-49fd-a221-7bc645de294b', 'Padėkite mokytojui geriau bendrauti ir bendradarbiauti klasėje ir už jos ribų', 'LT'),
110
		('17', '42785ac7-ec49-4c77-b962-31a075e86784', 'Help the teacher better understand and deal with student eco-anxiety', 'EN'),
111
		('18', '42785ac7-ec49-4c77-b962-31a075e86784', 'Aider l enseignant à mieux comprendre et gérer l éco-anxiété des élèves','FR'),
112
		('19', '42785ac7-ec49-4c77-b962-31a075e86784', 'Ayudar al maestro a comprender y manejar mejor la eco-ansiedad de los estudiantes.','ES'),
113
		('20', '42785ac7-ec49-4c77-b962-31a075e86784', 'Padekite mokytojui geriau suprasti mokinių ekologinį nerimą ir su juo susidoroti','LT'),
114
		('21', '761cb50c-038b-43f1-a630-f4ff53c8628a', 'Help the teacher engage in circular transitions pathways','EN'),
115
		('22', '761cb50c-038b-43f1-a630-f4ff53c8628a', 'Aider l enseignant à sengager dans des parcours de transitions circulaires','FR'),
116
		('23', '761cb50c-038b-43f1-a630-f4ff53c8628a', 'Ayude al maestro a participar en vías de transiciones circulares', 'ES'),
117
		('24', '761cb50c-038b-43f1-a630-f4ff53c8628a', 'Padėkite mokytojui įsitraukti į žiedinius perėjimus','LT'); 
118
INSERT INTO users(id, name, lastName, email, password, role) VALUES
119
		('1', 'admin', 'admin', 'admin1.score@score.test', '$2a$08$tPnAxusiethLxrXN/Lg94eMbcYWKbQuXSHKKpUQIjRuKAONCyCRBG', 'admin'), 
120
		('2', 'admin', 'admin', 'admin2.score@score.test', '$2a$08$tPnAxusiethLxrXN/Lg94eMbcYWKbQuXSHKKpUQIjRuKAONCyCRBG', 'admin');
121
INSERT INTO languages(id, name) VALUES
122
		('FR', 'FR'), 
123
		('EN', 'EN'),
124
		('ES', 'ES'),
125
		('LT', 'LT');
126
INSERT INTO domains(id, domainId, name, language) VALUES
127
		('1', '196c4911-2a1e-46dc-853f-9cd6da28d02f', 'Food','EN'),
128
		('2', '196c4911-2a1e-46dc-853f-9cd6da28d02f', 'Nourriture','FR'),
129
		('3', '196c4911-2a1e-46dc-853f-9cd6da28d02f', 'Alimento','ES'),
130
		('4', '196c4911-2a1e-46dc-853f-9cd6da28d02f', 'maistas','LT'),
131
		('5', 'c6e6df9c-db14-42e1-91b2-731d15fe8122', 'Textile','EN'),
132
		('6', 'c6e6df9c-db14-42e1-91b2-731d15fe8122', 'Textile','FR'),
133
		('7', 'c6e6df9c-db14-42e1-91b2-731d15fe8122', 'Textil','ES'),
134
		('8', 'c6e6df9c-db14-42e1-91b2-731d15fe8122', 'Tekstilės','LT'),
135
		('9', 'fb65a96d-0f10-49a9-99a1-d34e4205cc1f', 'Electronics','EN'),
136
		('10', 'fb65a96d-0f10-49a9-99a1-d34e4205cc1f', 'Electronique','FR'),
137
		('11', 'fb65a96d-0f10-49a9-99a1-d34e4205cc1f', 'Electrónica','ES'),
138
		('12', 'fb65a96d-0f10-49a9-99a1-d34e4205cc1f', 'Elektronika','LT'),
139
		('13', 'ab1b5484-61a2-4c6f-b6c2-71cc8290916e', 'Others','EN'),
140
		('14', 'ab1b5484-61a2-4c6f-b6c2-71cc8290916e', 'Autres','FR'),
141
		('15', 'ab1b5484-61a2-4c6f-b6c2-71cc8290916e', 'Otras','ES'),
142
		('16', 'ab1b5484-61a2-4c6f-b6c2-71cc8290916e', 'Kiti','LT');
    (1-1/1)