[auf_savoirs_en_partage_django.git] / auf_savoirs_en_partage / scripts / sphinx.conf.py.in

#!/usr/bin/python
# coding: utf-8
# 
# Ce script compose dynamiquement une configuration pour Sphinx. Il suffit
# que le fichier soit exécutable pour que Sphinx exécute le script plutôt
# que de le lire comme un fichier statique.
#
# Le manuel de sphinx se trouve au http://sphinxsearch.com/docs/manual-0.9.9.html

import glob
import os
import sys

sys.path[0:0] = [
  "%(directory)s",
  "%(directory)s/auf_savoirs_en_partage",
  ]
os.environ['DJANGO_SETTINGS_MODULE'] = 'production'
for d in glob.glob("%(directory)s/eggs/*"):
    sys.path[0:0] = [d,]
for d in glob.glob("%(directory)s/parts/*"):
    sys.path[0:0] = [d,]

from django.conf import settings

SQL_HOST = settings.DATABASE_HOST
SQL_USER = settings.DATABASE_USER
SQL_PASS = settings.DATABASE_PASSWORD
SQL_DB = settings.DATABASE_NAME
DATA_DIR = '/var/lib/sphinxsearch/data'

def multiline(s):
    """Place un marqueur de continuation avant chaque saut de ligne."""
    return s.replace("\n", "\\\n")

def emit_source(name, sql_query, sql_query_info=None, sql_attr_multi=None, sql_attr_uint=None,
                sql_attr_str2ordinal=None):
    print '''
source %%(name)s
{
    type = mysql
    sql_host = %%(sql_host)s
    sql_user = %%(sql_user)s
    sql_pass = %%(sql_pass)s
    sql_db = %%(sql_db)s
    sql_query_pre = SET NAMES utf8
    sql_query_pre = SET SESSION query_cache_type=OFF
    sql_query = %%(sql_query)s
''' %% dict(name=name, sql_host=SQL_HOST, sql_user=SQL_USER,
            sql_pass=SQL_PASS, sql_db=SQL_DB, sql_query=multiline(sql_query))

    if sql_query_info:
        print '    sql_query_info = ' + sql_query_info

    if sql_attr_multi:
        for attr in sql_attr_multi:
            print '    sql_attr_multi = uint %%s from field' %% attr

    if sql_attr_uint:
        for attr in sql_attr_uint:
            print '    sql_attr_uint = ' + attr
    
    if sql_attr_str2ordinal:
        for attr in sql_attr_str2ordinal:
            print '    sql_attr_str2ordinal = ' + attr

    print '}'

def emit_index(name):
    print '''
index %%(name)s
{
    morphology = libstemmer_fr
    charset_type = utf-8
    charset_table = 0..9, A..Z->a..z, _, a..z, \\
                    U+C0->a, U+C2->a, U+E0->a, U+E2->a, \\
                    U+C7->c, U+E7->c, \\
                    U+C8->e, U+C9->e, U+CA->e, U+E8->e, U+E9->e, U+EA->e, U+EB->e, \\
                    U+CE->i, U+EE->i, U+EF->i, \\
                    U+D4->o, U+F4->o, \\
                    U+F9->u, U+FB->u, U+FC->u
    source = %%(name)s
    path = %%(path)s
    stopwords = %(directory)s/auf_savoirs_en_partage/stopwords.txt
}''' %% dict(name=name, path=os.path.join(DATA_DIR, name))

emit_source('savoirsenpartage_ressources', 
            '''SELECT r.id AS id, 
                      r.title AS title, 
                      r.description AS description, 
                      r.creator AS creator, 
                      r.contributor AS contributor, 
                      r.subject AS subject, 
                      r.publisher AS publisher, 
                      GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines, 
                      GROUP_CONCAT(DISTINCT d.id_discipline) AS discipline_ids,
                      GROUP_CONCAT(DISTINCT p.nom) AS pays, 
                      GROUP_CONCAT(DISTINCT reg.nom) AS regions,
                      GROUP_CONCAT(DISTINCT reg.id) AS region_ids
                FROM savoirs_record r 
                LEFT JOIN savoirs_record_disciplines rd ON rd.record_id = r.id 
                LEFT JOIN discipline d ON d.id_discipline = rd.discipline_id 
                LEFT JOIN savoirs_record_pays rp ON rp.record_id = r.id 
                LEFT JOIN ref_pays p ON p.id = rp.pays_id 
                LEFT JOIN savoirs_record_regions rr ON rr.record_id = r.id 
                LEFT JOIN ref_region reg ON reg.id = rr.region_id OR reg.id = p.region 
                LEFT JOIN savoirs_record_listsets rl ON rl.record_id = r.id
                LEFT JOIN savoirs_listset l ON l.spec = rl.listset_id
                WHERE r.validated AND (l.spec IS NULL OR l.validated)
                GROUP BY r.id''',
            sql_query_info='SELECT * from savoirs_record WHERE id=$id',
            sql_attr_multi=['discipline_ids', 'region_ids']
            )

emit_source('savoirsenpartage_actualites',
            '''SELECT a.id_actualite AS id, 
                      a.titre_actualite AS titre, 
                      a.texte_actualite AS texte, 
                      TO_DAYS(a.date_actualite) AS date,
                      GROUP_CONCAT(DISTINCT r.nom) AS regions, 
                      GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines
               FROM actualite a 
               LEFT JOIN actualite_regions ar ON ar.actualite_id = a.id_actualite 
               LEFT JOIN ref_region r ON r.id = ar.region_id 
               LEFT JOIN actualite_disciplines ad ON ad.actualite_id = a.id_actualite 
               LEFT JOIN discipline d ON d.id_discipline = ad.discipline_id
               WHERE a.visible_actualite
               GROUP BY a.id_actualite''',
            sql_query_info='SELECT * from actualite WHERE id_actualite=$id',
            sql_attr_uint=['date']
           )

emit_source('savoirsenpartage_evenements',
            '''SELECT e.id AS id,
                      e.titre AS titre,
                      e.mots_cles AS mots_cles,
                      e.type AS type,
                      e.lieu AS lieu,
                      e.description AS description,
                      e.contact AS contact,
                      CONCAT_WS(',', d.nom_discipline, d2.nom_discipline) AS disciplines,
                      GROUP_CONCAT(DISTINCT r.nom) AS regions,
                      TO_DAYS(DATE(e.debut)) AS debut
               FROM savoirs_evenement e
               LEFT JOIN discipline d ON d.id_discipline = e.discipline_id
               LEFT JOIN discipline d2 ON d2.id_discipline = e.discipline_secondaire_id
               LEFT JOIN savoirs_evenement_regions er ON er.evenement_id = e.id
               LEFT JOIN ref_region r ON r.id = er.region_id
               WHERE e.approuve
               GROUP BY e.id''',
            sql_query_info='SELECT * from savoirs_evenement WHERE id=$id',
            sql_attr_uint=['debut'])
            
emit_source('savoirsenpartage_chercheurs',
            '''SELECT c.id AS id,
                      p.nom AS nom,
                      p.prenom AS prenom,
                      CONCAT(p.nom, ' ', p.prenom) AS nom_complet,
                      c.theme_recherche AS theme_recherche,
                      c.groupe_recherche AS groupe_recherche,
                      c.mots_cles AS mots_cles,
                      c.membre_association_francophone_details AS membre_association_francophone_details,
                      c.membre_reseau_institutionnel_details AS membre_reseau_institutionnel_details,
                      c.expert_oif_details AS expert_oif_details,
                      c.membre_instance_auf_details AS membre_instance_auf_details,
                      IFNULL(et.nom, etablissement_autre_nom) AS etablissement,
                      IFNULL(et.nom, etablissement_autre_nom) AS etablissement_attr,
                      pays.nom AS pays,
                      pays.nom AS pays_attr,
                      pays.id AS pays_id,
                      r.nom AS region,
                      r.id AS region_id,
                      GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines,
                      GROUP_CONCAT(DISTINCT pub.titre) AS publications,
                      t.titre AS these,
                      GROUP_CONCAT(DISTINCT g.nom) AS groupes,
                      GROUP_CONCAT(DISTINCT ex.nom) AS expertises,
                      GROUP_CONCAT(DISTINCT g.id) AS groupe_ids,
                      TO_DAYS(c.date_modification) AS date_modification,
                      CASE pays WHEN 'Nord' THEN 1 
                                WHEN 'Sud' THEN 2 
                                END AS nord_sud,
                      CASE statut WHEN 'enseignant' THEN 1
                                  WHEN 'etudiant' THEN 2
                                  WHEN 'independant' THEN 3
                                  END AS statut,
                      (ex.id IS NOT NULL) AS expert
               FROM chercheurs_chercheur c
               INNER JOIN chercheurs_personne p ON c.personne = p.id
               LEFT JOIN ref_etablissement et ON et.id = c.etablissement
               LEFT JOIN ref_pays pays ON pays.code = IFNULL(et.pays, c.etablissement_autre_pays)
               LEFT JOIN ref_region r ON pays.region = r.id
               LEFT JOIN discipline d ON d.id_discipline = c.discipline
               LEFT JOIN chercheurs_publication pub ON pub.chercheur_id = c.id
               LEFT JOIN chercheurs_these t ON t.chercheur_id = c.id
               LEFT JOIN chercheurs_chercheurgroupe cg ON cg.chercheur = c.id
               LEFT JOIN chercheurs_groupe g ON g.id = cg.groupe
               LEFT JOIN chercheurs_expertise ex ON ex.chercheur_id = c.id
               GROUP BY c.id''',
            sql_query_info='SELECT * from chercheurs_chercheur WHERE id=$id',
            sql_attr_multi=['groupe_ids'],
            sql_attr_uint=['pays_id', 'region_id', 'nord_sud', 'date_modification', 'statut', 'expert'],
            sql_attr_str2ordinal=['nom_complet', 'etablissement_attr', 'pays_attr'])
                      
emit_source('savoirsenpartage_sites',
            '''SELECT s.id AS id,
                      s.titre AS titre,
                      s.description AS description,
                      s.editeur AS editeur,
                      s.auteur AS auteur,
                      s.mots_cles AS mots_cles,
                      GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines,
                      GROUP_CONCAT(DISTINCT p.nom) AS pays,
                      GROUP_CONCAT(DISTINCT p.id) AS pays_ids,
                      GROUP_CONCAT(DISTINCT r.nom) AS regions,
                      TO_DAYS(s.date_maj) AS date_maj
               FROM sitotheque_site s
               LEFT JOIN sitotheque_site_discipline sd ON sd.site_id = s.id
               LEFT JOIN discipline d ON d.id_discipline = sd.discipline_id
               LEFT JOIN ref_pays p ON p.id = s.pays
               LEFT JOIN ref_region r ON r.id = p.region
               GROUP BY s.id''',
            'SELECT * FROM sitotheque_site WHERE id=$id',
            sql_attr_multi=['pays_ids'],
            sql_attr_uint=['date_maj']
           )

emit_index('savoirsenpartage_actualites')
emit_index('savoirsenpartage_ressources')
emit_index('savoirsenpartage_evenements')
emit_index('savoirsenpartage_chercheurs')
emit_index('savoirsenpartage_sites')
Commit	Line	Data
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
	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.DATABASE_HOST
	27	SQL_USER = settings.DATABASE_USER
	28	SQL_PASS = settings.DATABASE_PASSWORD
	29	SQL_DB = settings.DATABASE_NAME
4134daa0	30	DATA_DIR = '/var/lib/sphinxsearch/data'
5212238e EMS	31
	32	def multiline(s):
	33	"""Place un marqueur de continuation avant chaque saut de ligne."""
	34	return s.replace("\n", "\\\n")
	35
acd5cd8f EMS	36	def emit_source(name, sql_query, sql_query_info=None, sql_attr_multi=None, sql_attr_uint=None,
acd5cd8f EMS	37	sql_attr_str2ordinal=None):
5212238e EMS	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
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
	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, \\
	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))
5212238e EMS	86
4134daa0	87	emit_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,
	95	GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines,
	96	GROUP_CONCAT(DISTINCT d.id_discipline) AS discipline_ids,
	97	GROUP_CONCAT(DISTINCT p.nom) AS pays,
	98	GROUP_CONCAT(DISTINCT reg.nom) AS regions,
	99	GROUP_CONCAT(DISTINCT reg.id) AS region_ids
	100	FROM savoirs_record r
	101	LEFT JOIN savoirs_record_disciplines rd ON rd.record_id = r.id
	102	LEFT JOIN discipline d ON d.id_discipline = rd.discipline_id
	103	LEFT JOIN savoirs_record_pays rp ON rp.record_id = r.id
	104	LEFT JOIN ref_pays p ON p.id = rp.pays_id
	105	LEFT JOIN savoirs_record_regions rr ON rr.record_id = r.id
	106	LEFT JOIN ref_region reg ON reg.id = rr.region_id OR reg.id = p.region
	107	LEFT JOIN savoirs_record_listsets rl ON rl.record_id = r.id
	108	LEFT JOIN savoirs_listset l ON l.spec = rl.listset_id
	109	WHERE r.validated AND (l.spec IS NULL OR l.validated)
	110	GROUP BY r.id''',
	111	sql_query_info='SELECT * from savoirs_record WHERE id=$id',
	112	sql_attr_multi=['discipline_ids', 'region_ids']
	113	)
	114
4134daa0	115	emit_source('savoirsenpartage_actualites',
5212238e EMS	116	'''SELECT a.id_actualite AS id,
	117	a.titre_actualite AS titre,
	118	a.texte_actualite AS texte,
	119	TO_DAYS(a.date_actualite) AS date,
	120	GROUP_CONCAT(DISTINCT r.nom) AS regions,
	121	GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines
	122	FROM actualite a
	123	LEFT JOIN actualite_regions ar ON ar.actualite_id = a.id_actualite
	124	LEFT JOIN ref_region r ON r.id = ar.region_id
	125	LEFT JOIN actualite_disciplines ad ON ad.actualite_id = a.id_actualite
	126	LEFT JOIN discipline d ON d.id_discipline = ad.discipline_id
	127	WHERE a.visible_actualite
	128	GROUP BY a.id_actualite''',
	129	sql_query_info='SELECT * from actualite WHERE id_actualite=$id',
	130	sql_attr_uint=['date']
	131	)
	132
4134daa0	133	emit_source('savoirsenpartage_evenements',
5212238e EMS	134	'''SELECT e.id AS id,
	135	e.titre AS titre,
	136	e.mots_cles AS mots_cles,
	137	e.type AS type,
	138	e.lieu AS lieu,
	139	e.description AS description,
	140	e.contact AS contact,
	141	CONCAT_WS(',', d.nom_discipline, d2.nom_discipline) AS disciplines,
	142	GROUP_CONCAT(DISTINCT r.nom) AS regions,
	143	TO_DAYS(DATE(e.debut)) AS debut
	144	FROM savoirs_evenement e
	145	LEFT JOIN discipline d ON d.id_discipline = e.discipline_id
	146	LEFT JOIN discipline d2 ON d2.id_discipline = e.discipline_secondaire_id
	147	LEFT JOIN savoirs_evenement_regions er ON er.evenement_id = e.id
	148	LEFT JOIN ref_region r ON r.id = er.region_id
	149	WHERE e.approuve
	150	GROUP BY e.id''',
	151	sql_query_info='SELECT * from savoirs_evenement WHERE id=$id',
	152	sql_attr_uint=['debut'])
	153
4134daa0	154	emit_source('savoirsenpartage_chercheurs',
5212238e EMS	155	'''SELECT c.id AS id,
	156	p.nom AS nom,
	157	p.prenom AS prenom,
acd5cd8f	158	CONCAT(p.nom, ' ', p.prenom) AS nom_complet,
5212238e EMS	159	c.theme_recherche AS theme_recherche,
	160	c.groupe_recherche AS groupe_recherche,
	161	c.mots_cles AS mots_cles,
	162	c.membre_association_francophone_details AS membre_association_francophone_details,
	163	c.membre_reseau_institutionnel_details AS membre_reseau_institutionnel_details,
	164	c.expert_oif_details AS expert_oif_details,
	165	c.membre_instance_auf_details AS membre_instance_auf_details,
	166	IFNULL(et.nom, etablissement_autre_nom) AS etablissement,
acd5cd8f	167	IFNULL(et.nom, etablissement_autre_nom) AS etablissement_attr,
5212238e	168	pays.nom AS pays,
acd5cd8f	169	pays.nom AS pays_attr,
5212238e EMS	170	pays.id AS pays_id,
	171	r.nom AS region,
	172	r.id AS region_id,
	173	GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines,
42a890cf	174	GROUP_CONCAT(DISTINCT pub.titre) AS publications,
5212238e EMS	175	t.titre AS these,
	176	GROUP_CONCAT(DISTINCT g.nom) AS groupes,
	177	GROUP_CONCAT(DISTINCT ex.nom) AS expertises,
	178	GROUP_CONCAT(DISTINCT g.id) AS groupe_ids,
	179	TO_DAYS(c.date_modification) AS date_modification,
	180	CASE pays WHEN 'Nord' THEN 1
	181	WHEN 'Sud' THEN 2
	182	END AS nord_sud,
	183	CASE statut WHEN 'enseignant' THEN 1
	184	WHEN 'etudiant' THEN 2
	185	WHEN 'independant' THEN 3
	186	END AS statut,
d9da735f	187	(ex.id IS NOT NULL) AS expert
5212238e EMS	188	FROM chercheurs_chercheur c
	189	INNER JOIN chercheurs_personne p ON c.personne = p.id
	190	LEFT JOIN ref_etablissement et ON et.id = c.etablissement
acd5cd8f	191	LEFT JOIN ref_pays pays ON pays.code = IFNULL(et.pays, c.etablissement_autre_pays)
5212238e EMS	192	LEFT JOIN ref_region r ON pays.region = r.id
5212238e EMS	193	LEFT JOIN discipline d ON d.id_discipline = c.discipline
42a890cf EMS	194	LEFT JOIN chercheurs_publication pub ON pub.chercheur_id = c.id
42a890cf EMS	195	LEFT JOIN chercheurs_these t ON t.chercheur_id = c.id
5212238e EMS	196	LEFT JOIN chercheurs_chercheurgroupe cg ON cg.chercheur = c.id
	197	LEFT JOIN chercheurs_groupe g ON g.id = cg.groupe
	198	LEFT JOIN chercheurs_expertise ex ON ex.chercheur_id = c.id
	199	GROUP BY c.id''',
	200	sql_query_info='SELECT * from chercheurs_chercheur WHERE id=$id',
	201	sql_attr_multi=['groupe_ids'],
acd5cd8f EMS	202	sql_attr_uint=['pays_id', 'region_id', 'nord_sud', 'date_modification', 'statut', 'expert'],
acd5cd8f EMS	203	sql_attr_str2ordinal=['nom_complet', 'etablissement_attr', 'pays_attr'])
5212238e	204
4134daa0	205	emit_source('savoirsenpartage_sites',
5212238e EMS	206	'''SELECT s.id AS id,
	207	s.titre AS titre,
	208	s.description AS description,
	209	s.editeur AS editeur,
	210	s.auteur AS auteur,
	211	s.mots_cles AS mots_cles,
	212	GROUP_CONCAT(DISTINCT d.nom_discipline) AS disciplines,
	213	GROUP_CONCAT(DISTINCT p.nom) AS pays,
	214	GROUP_CONCAT(DISTINCT p.id) AS pays_ids,
a611bc4a EMS	215	GROUP_CONCAT(DISTINCT r.nom) AS regions,
a611bc4a EMS	216	TO_DAYS(s.date_maj) AS date_maj
5212238e EMS	217	FROM sitotheque_site s
	218	LEFT JOIN sitotheque_site_discipline sd ON sd.site_id = s.id
	219	LEFT JOIN discipline d ON d.id_discipline = sd.discipline_id
	220	LEFT JOIN ref_pays p ON p.id = s.pays
	221	LEFT JOIN ref_region r ON r.id = p.region
	222	GROUP BY s.id''',
	223	'SELECT * FROM sitotheque_site WHERE id=$id',
a611bc4a EMS	224	sql_attr_multi=['pays_ids'],
	225	sql_attr_uint=['date_maj']
	226	)
5212238e	227
4134daa0 EMS	228	emit_index('savoirsenpartage_actualites')
	229	emit_index('savoirsenpartage_ressources')
	230	emit_index('savoirsenpartage_evenements')
	231	emit_index('savoirsenpartage_chercheurs')
	232	emit_index('savoirsenpartage_sites')