Optimisation de la requête des ressources valides
[auf_savoirs_en_partage_django.git] / auf_savoirs_en_partage / scripts / sphinx.conf.py.in
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
10 import glob
11 import os
12 import sys
13
14 sys.path[0:0] = [
15 "%(directory)s",
16 "%(directory)s/auf_savoirs_en_partage",
17 ]
18 os.environ['DJANGO_SETTINGS_MODULE'] = 'production'
19 for d in glob.glob("%(directory)s/eggs/*"):
20 sys.path[0:0] = [d,]
21 for d in glob.glob("%(directory)s/parts/*"):
22 sys.path[0:0] = [d,]
23
24 from django.conf import settings
25
26 SQL_HOST = settings.DATABASES['default']['HOST']
27 SQL_USER = settings.DATABASES['default']['USER']
28 SQL_PASS = settings.DATABASES['default']['PASSWORD']
29 SQL_DB = settings.DATABASES['default']['NAME']
30 DATA_DIR = '/var/lib/sphinxsearch/data'
31
32 def multiline(s):
33 """Place un marqueur de continuation avant chaque saut de ligne."""
34 return s.replace("\n", "\\\n")
35
36 def emit_source(name, sql_query, sql_query_info=None, sql_attr_multi=None, sql_attr_uint=None,
37 sql_attr_str2ordinal=None):
38 print '''
39 source %%(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
63 if sql_attr_str2ordinal:
64 for attr in sql_attr_str2ordinal:
65 print ' sql_attr_str2ordinal = ' + attr
66
67 print '}'
68
69 def emit_index(name):
70 print '''
71 index %%(name)s
72 {
73 morphology = libstemmer_fr
74 charset_type = utf-8
75 charset_table = 0..9, A..Z->a..z, _, a..z, U+2D, \\
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
84 stopwords = %(directory)s/auf_savoirs_en_partage/stopwords.txt
85 }''' %% dict(name=name, path=os.path.join(DATA_DIR, name))
86
87 emit_source('savoirsenpartage_ressources',
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,
95 r.categorie_id as categorie,
96 TO_DAYS(r.modified) AS modified,
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',
114 sql_attr_multi=['discipline_ids', 'region_ids'],
115 sql_attr_uint=['modified']
116 )
117
118 emit_source('savoirsenpartage_actualites',
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,
123 CASE s.type WHEN 'actu' THEN 1 WHEN 'appels' THEN 2 END AS type,
124 GROUP_CONCAT(DISTINCT r.nom) AS regions,
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
128 FROM actualite a
129 INNER JOIN savoirs_sourceactualite s ON s.id = a.source_id
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',
137 sql_attr_multi=['discipline_ids', 'region_ids'],
138 sql_attr_uint=['date', 'type'],
139 )
140
141 emit_source('savoirsenpartage_evenements',
142 '''SELECT e.id AS id,
143 e.titre AS titre,
144 e.mots_cles AS mots_cles,
145 e.type AS type,
146 e.adresse AS adresse,
147 e.ville AS ville,
148 e.description AS description,
149 CONCAT_WS(e.prenom, e.nom, e.courriel, e.contact) AS contact,
150 p.nom AS pays,
151 CONCAT_WS(',', d.nom_discipline, d2.nom_discipline) AS disciplines,
152 CONCAT_WS(',', pr.nom, GROUP_CONCAT(DISTINCT r.nom)) AS regions,
153 TO_DAYS(DATE(e.debut)) AS debut,
154 TO_DAYS(DATE(e.date_modification)) AS date_modification
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
160 LEFT JOIN ref_pays p ON p.code = e.pays_id
161 LEFT JOIN ref_region pr ON pr.id = p.region
162 WHERE e.approuve
163 GROUP BY e.id''',
164 sql_query_info='SELECT * from savoirs_evenement WHERE id=$id',
165 sql_attr_uint=['debut', 'date_modification'])
166
167 emit_source('savoirsenpartage_chercheurs',
168 '''SELECT c.personne_ptr_id AS id,
169 p.nom AS nom,
170 p.prenom AS prenom,
171 CONCAT(p.nom, ' ', p.prenom) AS nom_complet,
172 c.theme_recherche AS theme_recherche,
173 c.equipe_recherche AS equipe_recherche,
174 c.mots_cles AS mots_cles,
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,
179 IFNULL(et.nom, etablissement_autre_nom) AS etablissement,
180 IFNULL(et.nom, etablissement_autre_nom) AS etablissement_attr,
181 pays.nom AS pays,
182 pays.nom AS pays_attr,
183 pays.id AS pays_id,
184 r.nom AS region,
185 r.id AS region_id,
186 d.nom_discipline AS discipline,
187 d.id_discipline AS discipline_id,
188 GROUP_CONCAT(DISTINCT IFNULL(pub.titre, pub.publication_affichage)) AS publications,
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,
194 CASE pays.nord_sud WHEN 'Nord' THEN 1
195 WHEN 'Sud' THEN 2
196 END AS nord_sud,
197 CASE c.statut WHEN 'enseignant' THEN 1
198 WHEN 'etudiant' THEN 2
199 WHEN 'independant' THEN 3
200 END AS statut,
201 CASE p.genre WHEN 'm' THEN 1
202 WHEN 'f' THEN 2
203 END AS genre,
204 (ex.id IS NOT NULL) AS expert
205 FROM chercheurs_chercheur c
206 INNER JOIN chercheurs_personne p ON c.personne_ptr_id = p.id
207 LEFT JOIN ref_etablissement et ON et.id = c.etablissement
208 LEFT JOIN ref_pays pays ON pays.code = IFNULL(et.pays, c.etablissement_autre_pays)
209 LEFT JOIN ref_region r ON pays.region = r.id
210 LEFT JOIN discipline d ON d.id_discipline = c.discipline
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
213 LEFT JOIN chercheurs_adhesiongroupe cg ON cg.chercheur = c.personne_ptr_id
214 LEFT JOIN chercheurs_groupe g ON g.id = cg.groupe
215 LEFT JOIN chercheurs_expertise ex ON ex.chercheur_id = c.personne_ptr_id
216 WHERE p.actif
217 GROUP BY c.personne_ptr_id''',
218 sql_query_info='SELECT * from chercheurs_chercheur WHERE id=$id',
219 sql_attr_multi=['groupe_ids'],
220 sql_attr_uint=['pays_id', 'region_id', 'discipline_id', 'nord_sud',
221 'date_modification', 'statut', 'expert',
222 'membre_instance_auf', 'expert_oif',
223 'membre_association_francophone',
224 'membre_reseau_institutionnel', 'genre', ],
225 sql_attr_str2ordinal=['nom_complet', 'etablissement_attr', 'pays_attr', ])
226
227 emit_source('savoirsenpartage_sites',
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,
235 GROUP_CONCAT(DISTINCT d.id_discipline) AS discipline_ids,
236 p.nom AS pays,
237 p.id AS pays_id,
238 GROUP_CONCAT(DISTINCT r.nom) AS regions,
239 GROUP_CONCAT(DISTINCT r.id) AS region_ids,
240 TO_DAYS(s.date_maj) AS date_maj
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
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
247 GROUP BY s.id''',
248 'SELECT * FROM sitotheque_site WHERE id=$id',
249 sql_attr_multi=['discipline_ids', 'region_ids'],
250 sql_attr_uint=['date_maj', 'pays_id']
251 )
252
253 emit_index('savoirsenpartage_actualites')
254 emit_index('savoirsenpartage_ressources')
255 emit_index('savoirsenpartage_evenements')
256 emit_index('savoirsenpartage_chercheurs')
257 emit_index('savoirsenpartage_sites')