Optimisation de la requête des ressources valides
[auf_savoirs_en_partage_django.git] / auf_savoirs_en_partage / scripts / sphinx.conf.py.in
CommitLineData
5212238e
EMS
1#!/usr/bin/python
2# coding: utf-8
3#
4# Ce script compose dynamiquement une configuration pour Sphinx. Il suffit
5# que le fichier soit exécutable pour que Sphinx exécute le script plutôt
6# que de le lire comme un fichier statique.
7#
8# Le manuel de sphinx se trouve au http://sphinxsearch.com/docs/manual-0.9.9.html
9
10import glob
11import os
12import sys
13
14sys.path[0:0] = [
15 "%(directory)s",
16 "%(directory)s/auf_savoirs_en_partage",
17 ]
18os.environ['DJANGO_SETTINGS_MODULE'] = 'production'
19for d in glob.glob("%(directory)s/eggs/*"):
20 sys.path[0:0] = [d,]
21for d in glob.glob("%(directory)s/parts/*"):
22 sys.path[0:0] = [d,]
23
24from django.conf import settings
25
31249cf3
EMS
26SQL_HOST = settings.DATABASES['default']['HOST']
27SQL_USER = settings.DATABASES['default']['USER']
28SQL_PASS = settings.DATABASES['default']['PASSWORD']
29SQL_DB = settings.DATABASES['default']['NAME']
4134daa0 30DATA_DIR = '/var/lib/sphinxsearch/data'
5212238e
EMS
31
32def multiline(s):
33 """Place un marqueur de continuation avant chaque saut de ligne."""
34 return s.replace("\n", "\\\n")
35
acd5cd8f
EMS
36def emit_source(name, sql_query, sql_query_info=None, sql_attr_multi=None, sql_attr_uint=None,
37 sql_attr_str2ordinal=None):
5212238e
EMS
38 print '''
39source %%(name)s
40{
41 type = mysql
42 sql_host = %%(sql_host)s
43 sql_user = %%(sql_user)s
44 sql_pass = %%(sql_pass)s
45 sql_db = %%(sql_db)s
46 sql_query_pre = SET NAMES utf8
47 sql_query_pre = SET SESSION query_cache_type=OFF
48 sql_query = %%(sql_query)s
49''' %% dict(name=name, sql_host=SQL_HOST, sql_user=SQL_USER,
50 sql_pass=SQL_PASS, sql_db=SQL_DB, sql_query=multiline(sql_query))
51
52 if sql_query_info:
53 print ' sql_query_info = ' + sql_query_info
54
55 if sql_attr_multi:
56 for attr in sql_attr_multi:
57 print ' sql_attr_multi = uint %%s from field' %% attr
58
59 if sql_attr_uint:
60 for attr in sql_attr_uint:
61 print ' sql_attr_uint = ' + attr
62
acd5cd8f
EMS
63 if sql_attr_str2ordinal:
64 for attr in sql_attr_str2ordinal:
65 print ' sql_attr_str2ordinal = ' + attr
66
5212238e
EMS
67 print '}'
68
69def emit_index(name):
70 print '''
71index %%(name)s
72{
73 morphology = libstemmer_fr
74 charset_type = utf-8
0e90c1a7 75 charset_table = 0..9, A..Z->a..z, _, a..z, U+2D, \\
5212238e
EMS
76 U+C0->a, U+C2->a, U+E0->a, U+E2->a, \\
77 U+C7->c, U+E7->c, \\
78 U+C8->e, U+C9->e, U+CA->e, U+E8->e, U+E9->e, U+EA->e, U+EB->e, \\
79 U+CE->i, U+EE->i, U+EF->i, \\
80 U+D4->o, U+F4->o, \\
81 U+F9->u, U+FB->u, U+FC->u
82 source = %%(name)s
83 path = %%(path)s
42a890cf 84 stopwords = %(directory)s/auf_savoirs_en_partage/stopwords.txt
5212238e
EMS
85}''' %% dict(name=name, path=os.path.join(DATA_DIR, name))
86
4134daa0 87emit_source('savoirsenpartage_ressources',
5212238e
EMS
88 '''SELECT r.id AS id,
89 r.title AS title,
90 r.description AS description,
91 r.creator AS creator,
92 r.contributor AS contributor,
93 r.subject AS subject,
94 r.publisher AS publisher,
ede02173 95 r.categorie_id as categorie,
230671ff 96 TO_DAYS(r.modified) AS modified,
5212238e
EMS
97 GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines,
98 GROUP_CONCAT(DISTINCT d.id_discipline) AS discipline_ids,
99 GROUP_CONCAT(DISTINCT p.nom) AS pays,
100 GROUP_CONCAT(DISTINCT reg.nom) AS regions,
101 GROUP_CONCAT(DISTINCT reg.id) AS region_ids
102 FROM savoirs_record r
103 LEFT JOIN savoirs_record_disciplines rd ON rd.record_id = r.id
104 LEFT JOIN discipline d ON d.id_discipline = rd.discipline_id
105 LEFT JOIN savoirs_record_pays rp ON rp.record_id = r.id
106 LEFT JOIN ref_pays p ON p.id = rp.pays_id
107 LEFT JOIN savoirs_record_regions rr ON rr.record_id = r.id
108 LEFT JOIN ref_region reg ON reg.id = rr.region_id OR reg.id = p.region
109 LEFT JOIN savoirs_record_listsets rl ON rl.record_id = r.id
110 LEFT JOIN savoirs_listset l ON l.spec = rl.listset_id
111 WHERE r.validated AND (l.spec IS NULL OR l.validated)
112 GROUP BY r.id''',
113 sql_query_info='SELECT * from savoirs_record WHERE id=$id',
230671ff
EMS
114 sql_attr_multi=['discipline_ids', 'region_ids'],
115 sql_attr_uint=['modified']
5212238e
EMS
116 )
117
4134daa0 118emit_source('savoirsenpartage_actualites',
5212238e
EMS
119 '''SELECT a.id_actualite AS id,
120 a.titre_actualite AS titre,
121 a.texte_actualite AS texte,
122 TO_DAYS(a.date_actualite) AS date,
011804bb 123 CASE s.type WHEN 'actu' THEN 1 WHEN 'appels' THEN 2 END AS type,
5212238e 124 GROUP_CONCAT(DISTINCT r.nom) AS regions,
27effd89
PP
125 GROUP_CONCAT(DISTINCT r.id) AS region_ids,
126 GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines,
127 GROUP_CONCAT(DISTINCT d.id_discipline) AS discipline_ids
5212238e 128 FROM actualite a
011804bb 129 INNER JOIN savoirs_sourceactualite s ON s.id = a.source_id
5212238e
EMS
130 LEFT JOIN actualite_regions ar ON ar.actualite_id = a.id_actualite
131 LEFT JOIN ref_region r ON r.id = ar.region_id
132 LEFT JOIN actualite_disciplines ad ON ad.actualite_id = a.id_actualite
133 LEFT JOIN discipline d ON d.id_discipline = ad.discipline_id
134 WHERE a.visible_actualite
135 GROUP BY a.id_actualite''',
136 sql_query_info='SELECT * from actualite WHERE id_actualite=$id',
27effd89 137 sql_attr_multi=['discipline_ids', 'region_ids'],
011804bb 138 sql_attr_uint=['date', 'type'],
5212238e
EMS
139 )
140
4134daa0 141emit_source('savoirsenpartage_evenements',
5212238e
EMS
142 '''SELECT e.id AS id,
143 e.titre AS titre,
144 e.mots_cles AS mots_cles,
145 e.type AS type,
fe254ccc
EMS
146 e.adresse AS adresse,
147 e.ville AS ville,
5212238e 148 e.description AS description,
fe254ccc
EMS
149 CONCAT_WS(e.prenom, e.nom, e.courriel, e.contact) AS contact,
150 p.nom AS pays,
5212238e 151 CONCAT_WS(',', d.nom_discipline, d2.nom_discipline) AS disciplines,
fe254ccc 152 CONCAT_WS(',', pr.nom, GROUP_CONCAT(DISTINCT r.nom)) AS regions,
a83b8efb
EMS
153 TO_DAYS(DATE(e.debut)) AS debut,
154 TO_DAYS(DATE(e.date_modification)) AS date_modification
5212238e
EMS
155 FROM savoirs_evenement e
156 LEFT JOIN discipline d ON d.id_discipline = e.discipline_id
157 LEFT JOIN discipline d2 ON d2.id_discipline = e.discipline_secondaire_id
158 LEFT JOIN savoirs_evenement_regions er ON er.evenement_id = e.id
159 LEFT JOIN ref_region r ON r.id = er.region_id
fe254ccc
EMS
160 LEFT JOIN ref_pays p ON p.code = e.pays_id
161 LEFT JOIN ref_region pr ON pr.id = p.region
5212238e
EMS
162 WHERE e.approuve
163 GROUP BY e.id''',
164 sql_query_info='SELECT * from savoirs_evenement WHERE id=$id',
a83b8efb 165 sql_attr_uint=['debut', 'date_modification'])
5212238e 166
4134daa0 167emit_source('savoirsenpartage_chercheurs',
edbcaa99 168 '''SELECT c.personne_ptr_id AS id,
5212238e
EMS
169 p.nom AS nom,
170 p.prenom AS prenom,
acd5cd8f 171 CONCAT(p.nom, ' ', p.prenom) AS nom_complet,
5212238e 172 c.theme_recherche AS theme_recherche,
35b0778c 173 c.equipe_recherche AS equipe_recherche,
5212238e 174 c.mots_cles AS mots_cles,
edbcaa99
EMS
175 c.membre_instance_auf AS membre_instance_auf,
176 c.expert_oif AS expert_oif,
177 c.membre_association_francophone AS membre_association_francophone,
178 c.membre_reseau_institutionnel AS membre_reseau_institutionnel,
5212238e 179 IFNULL(et.nom, etablissement_autre_nom) AS etablissement,
acd5cd8f 180 IFNULL(et.nom, etablissement_autre_nom) AS etablissement_attr,
5212238e 181 pays.nom AS pays,
acd5cd8f 182 pays.nom AS pays_attr,
5212238e
EMS
183 pays.id AS pays_id,
184 r.nom AS region,
185 r.id AS region_id,
27effd89
PP
186 d.nom_discipline AS discipline,
187 d.id_discipline AS discipline_id,
d8514fce 188 GROUP_CONCAT(DISTINCT IFNULL(pub.titre, pub.publication_affichage)) AS publications,
5212238e
EMS
189 t.titre AS these,
190 GROUP_CONCAT(DISTINCT g.nom) AS groupes,
191 GROUP_CONCAT(DISTINCT ex.nom) AS expertises,
192 GROUP_CONCAT(DISTINCT g.id) AS groupe_ids,
193 TO_DAYS(c.date_modification) AS date_modification,
d8514fce
EMS
194 CASE pays.nord_sud WHEN 'Nord' THEN 1
195 WHEN 'Sud' THEN 2
196 END AS nord_sud,
32a63420 197 CASE c.statut WHEN 'enseignant' THEN 1
5212238e
EMS
198 WHEN 'etudiant' THEN 2
199 WHEN 'independant' THEN 3
200 END AS statut,
c6efde25
OL
201 CASE p.genre WHEN 'm' THEN 1
202 WHEN 'f' THEN 2
203 END AS genre,
d9da735f 204 (ex.id IS NOT NULL) AS expert
5212238e 205 FROM chercheurs_chercheur c
edbcaa99 206 INNER JOIN chercheurs_personne p ON c.personne_ptr_id = p.id
5212238e 207 LEFT JOIN ref_etablissement et ON et.id = c.etablissement
acd5cd8f 208 LEFT JOIN ref_pays pays ON pays.code = IFNULL(et.pays, c.etablissement_autre_pays)
5212238e
EMS
209 LEFT JOIN ref_region r ON pays.region = r.id
210 LEFT JOIN discipline d ON d.id_discipline = c.discipline
edbcaa99
EMS
211 LEFT JOIN chercheurs_publication pub ON pub.chercheur_id = c.personne_ptr_id
212 LEFT JOIN chercheurs_these t ON t.chercheur_id = c.personne_ptr_id
18407f73 213 LEFT JOIN chercheurs_adhesiongroupe cg ON cg.chercheur = c.personne_ptr_id
5212238e 214 LEFT JOIN chercheurs_groupe g ON g.id = cg.groupe
edbcaa99 215 LEFT JOIN chercheurs_expertise ex ON ex.chercheur_id = c.personne_ptr_id
695930dd 216 WHERE p.actif
edbcaa99 217 GROUP BY c.personne_ptr_id''',
5212238e
EMS
218 sql_query_info='SELECT * from chercheurs_chercheur WHERE id=$id',
219 sql_attr_multi=['groupe_ids'],
27effd89 220 sql_attr_uint=['pays_id', 'region_id', 'discipline_id', 'nord_sud',
edbcaa99
EMS
221 'date_modification', 'statut', 'expert',
222 'membre_instance_auf', 'expert_oif',
223 'membre_association_francophone',
c6efde25
OL
224 'membre_reseau_institutionnel', 'genre', ],
225 sql_attr_str2ordinal=['nom_complet', 'etablissement_attr', 'pays_attr', ])
5212238e 226
4134daa0 227emit_source('savoirsenpartage_sites',
5212238e
EMS
228 '''SELECT s.id AS id,
229 s.titre AS titre,
230 s.description AS description,
231 s.editeur AS editeur,
232 s.auteur AS auteur,
233 s.mots_cles AS mots_cles,
234 GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines,
27effd89
PP
235 GROUP_CONCAT(DISTINCT d.id_discipline) AS discipline_ids,
236 p.nom AS pays,
237 p.id AS pays_id,
a611bc4a 238 GROUP_CONCAT(DISTINCT r.nom) AS regions,
27effd89 239 GROUP_CONCAT(DISTINCT r.id) AS region_ids,
a611bc4a 240 TO_DAYS(s.date_maj) AS date_maj
5212238e
EMS
241 FROM sitotheque_site s
242 LEFT JOIN sitotheque_site_discipline sd ON sd.site_id = s.id
243 LEFT JOIN discipline d ON d.id_discipline = sd.discipline_id
27effd89
PP
244 LEFT JOIN ref_pays p ON p.code = s.pays
245 LEFT JOIN sitotheque_site_regions sr ON sr.site_id = s.id
246 LEFT JOIN ref_region r ON r.id = sr.region_id
5212238e
EMS
247 GROUP BY s.id''',
248 'SELECT * FROM sitotheque_site WHERE id=$id',
27effd89
PP
249 sql_attr_multi=['discipline_ids', 'region_ids'],
250 sql_attr_uint=['date_maj', 'pays_id']
a611bc4a 251 )
5212238e 252
4134daa0
EMS
253emit_index('savoirsenpartage_actualites')
254emit_index('savoirsenpartage_ressources')
255emit_index('savoirsenpartage_evenements')
256emit_index('savoirsenpartage_chercheurs')
257emit_index('savoirsenpartage_sites')