Hi,
I created a new function bibtex_debian_pool() which has an INNER JOIN and
also escapes '#'. So, the query in generate_bibtex.py will change to :
# create BibTeX file
bf = open(self.bibtexfile, 'w')
if self.all_ref == 1:
query = "SELECT * FROM bibtex()"
else:
query = "SELECT * FROM bibtex_debian_pool()"
cur.execute(query)
for row in cur.fetchall():
print >>bf, row[0]
bf.close()
This is what you meant, right ?
Now when I was looking at the patch, I think I have sent the wrong patch -
missed a 'replace' in this one. If the approach is right, then I can resend
the two patches for upgrade_bibtex.sql and also resend the patch creating
"generate_bibtex.py" with the changes above for review.
On Thu, Apr 23, 2015 at 5:16 PM, <[email protected]> wrote:
> From: Akshita Jha <[email protected]>
>
> ---
> sql/upgrade_bibtex.sql | 89
> ++++++++++++++++++++++++++++++++++++++++++++++++++
> 1 file changed, 89 insertions(+)
>
> diff --git a/sql/upgrade_bibtex.sql b/sql/upgrade_bibtex.sql
> index 5b10aa3..29e8e02 100644
> --- a/sql/upgrade_bibtex.sql
> +++ b/sql/upgrade_bibtex.sql
> @@ -83,3 +83,92 @@ AS $$
> ORDER BY bibentry -- p.source
> ;
> $$;
> +
>
> +/************************************************************************************
> + * Create a BibTex file from references. These references are taken for
> source *
> + * which are both in bibref table and sources table.
> *
> +
> ************************************************************************************/
> +
> +CREATE OR REPLACE FUNCTION bibtex_debian_pool ()
> +RETURNS SETOF TEXT LANGUAGE SQL
> +AS $$
> + SELECT DISTINCT
> + CASE WHEN bibjournal.value IS NULL AND bibin.value IS NOT NULL
> AND bibpublisher.value IS NOT NULL THEN '@Book{' || bibkey.value
> + ELSE CASE WHEN bibauthor.value IS NULL OR bibjournal.value
> IS NULL THEN '@Misc{'|| bibkey.value ||
> + CASE WHEN bibauthor.value IS NULL THEN E',\n Key
> = "' || bibkey.value || '"' ELSE '' END -- without author we need a
> sorting key
> + ELSE '@Article{' || bibkey.value END END ||
> + CASE WHEN bibauthor.value IS NOT NULL THEN E',\n Author =
> {' || bibauthor.value || '}' ELSE '' END ||
> + CASE WHEN bibtitle.value IS NOT NULL THEN E',\n Title =
> "{' ||
> + replace(replace(replace(replace(bibtitle.value,
> + '_', E'\\_'), --
> + '%', E'\\%'), --
> + '#', E'\\#'), --
> + E'\xe2\x80\x89', E'\\,') -- TeX syntax for '_'
> and UTF-8 "thin space"
> + -- see
> http://www.utf8-chartable.de/unicode-utf8-table.pl?start=8192&number=128&utf8=string-literal
> + || '}"'
> + ELSE '' END ||
> + CASE WHEN bibbooktitle.value IS NOT NULL THEN E',\n
> Booktitle = "{' || bibbooktitle.value || '}"' ELSE '' END ||
> + CASE WHEN bibyear.value IS NOT NULL THEN E',\n Year =
> {' || bibyear.value || '}' ELSE '' END ||
> + CASE WHEN bibmonth.value IS NOT NULL THEN E',\n Month =
> {' || bibmonth.value || '}' ELSE '' END ||
> + CASE WHEN bibjournal.value IS NOT NULL THEN E',\n Journal =
> {' || replace(bibjournal.value, '&', E'\\&') || '}' ELSE '' END ||
> + CASE WHEN bibaddress.value IS NOT NULL THEN E',\n Address =
> {' || bibaddress.value || '}' ELSE '' END ||
> + CASE WHEN bibpublisher.value IS NOT NULL THEN E',\n
> Publisher = {' || bibpublisher.value || '}' ELSE '' END ||
> + CASE WHEN bibvolume.value IS NOT NULL THEN E',\n Volume =
> {' || bibvolume.value || '}' ELSE '' END ||
> + CASE WHEN bibnumber.value IS NOT NULL THEN E',\n Number =
> {' || bibnumber.value || '}' ELSE '' END ||
> + CASE WHEN bibpages.value IS NOT NULL THEN E',\n Pages =
> {' || regexp_replace(bibpages.value, E'(\\d)-([\\d])', E'\\1--\\2') ||
> '}' ELSE '' END ||
> + CASE WHEN biburl.value IS NOT NULL THEN E',\n URL =
> {' ||
> + replace(replace(replace(replace(replace(biburl.value,
> + '_', E'\\_'), --
> + '%', E'\\%'), --
> + '&', E'\\&'), --
> + '#', E'\\#'), --
> + '~', E'\\~{}') --
> + || '}'
> + ELSE '' END ||
> + CASE WHEN bibdoi.value IS NOT NULL THEN E',\n DOI =
> {' ||
> + replace(replace(bibdoi.value,
> + '_', E'\\_'), --
> + '#', E'\\#'), --
> + '&', E'\\&') --
> + || '}'
> + ELSE '' END ||
> + CASE WHEN bibpmid.value IS NOT NULL THEN E',\n PMID =
> {' || bibpmid.value || '}' ELSE '' END ||
> + CASE WHEN bibeprint.value IS NOT NULL THEN E',\n EPrint =
> {' ||
> + replace(replace(replace(replace(replace(bibeprint.value,
> + '_', E'\\_'), --
> + '%', E'\\%'), --
> + '&', E'\\&'), --
> + '#', E'\\#'), --
> + '~', E'\\~{}') --
> + || '}'
> + ELSE '' END ||
> + CASE WHEN bibin.value IS NOT NULL THEN E',\n In =
> {' || bibin.value || '}' ELSE '' END ||
> + CASE WHEN bibissn.value IS NOT NULL THEN E',\n ISSN =
> {' || bibissn.value || '}' ELSE '' END ||
> + E',\n}\n'
> + AS bibentry
> +-- p.source AS source,
> +-- p.rank AS rank,
> + FROM (SELECT DISTINCT source, package, rank FROM bibref) p
> + INNER JOIN sources s ON s.source = p.source
> + LEFT OUTER JOIN bibref bibkey ON p.source = bibkey.source AND
> bibkey.rank = p.rank AND bibkey.package = p.package AND bibkey.key
> = 'bibtex'
> + LEFT OUTER JOIN bibref bibyear ON p.source = bibyear.source AND
> bibyear.rank = p.rank AND bibyear.package = p.package AND
> bibyear.key = 'year'
> + LEFT OUTER JOIN bibref bibmonth ON p.source = bibmonth.source AND
> bibmonth.rank = p.rank AND bibmonth.package = p.package AND
> bibmonth.key = 'month'
> + LEFT OUTER JOIN bibref bibtitle ON p.source = bibtitle.source AND
> bibtitle.rank = p.rank AND bibtitle.package = p.package AND
> bibtitle.key = 'title'
> + LEFT OUTER JOIN bibref bibbooktitle ON p.source = bibbooktitle.source
> AND bibbooktitle.rank = p.rank AND bibbooktitle.package = p.package AND
> bibbooktitle.key = 'booktitle'
> + LEFT OUTER JOIN bibref bibauthor ON p.source = bibauthor.source AND
> bibauthor.rank = p.rank AND bibauthor.package = p.package AND
> bibauthor.key = 'author'
> + LEFT OUTER JOIN bibref bibjournal ON p.source = bibjournal.source AND
> bibjournal.rank = p.rank AND bibjournal.package = p.package AND
> bibjournal.key = 'journal'
> + LEFT OUTER JOIN bibref bibaddress ON p.source = bibaddress.source AND
> bibaddress.rank = p.rank AND bibaddress.package = p.package AND
> bibaddress.key = 'address'
> + LEFT OUTER JOIN bibref bibpublisher ON p.source = bibpublisher.source
> AND bibpublisher.rank = p.rank AND bibpublisher.package = p.package AND
> bibpublisher.key = 'publisher'
> + LEFT OUTER JOIN bibref bibvolume ON p.source = bibvolume.source AND
> bibvolume.rank = p.rank AND bibvolume.package = p.package AND
> bibvolume.key = 'volume'
> + LEFT OUTER JOIN bibref bibdoi ON p.source = bibdoi.source AND
> bibdoi.rank = p.rank AND bibdoi.package = p.package AND bibdoi.key
> = 'doi'
> + LEFT OUTER JOIN bibref bibpmid ON p.source = bibpmid.source AND
> bibpmid.rank = p.rank AND bibpmid.package = p.package AND
> bibpmid.key = 'pmid'
> + LEFT OUTER JOIN bibref biburl ON p.source = biburl.source AND
> biburl.rank = p.rank AND biburl.package = p.package AND biburl.key
> = 'url'
> + LEFT OUTER JOIN bibref bibnumber ON p.source = bibnumber.source AND
> bibnumber.rank = p.rank AND bibnumber.package = p.package AND
> bibnumber.key = 'number'
> + LEFT OUTER JOIN bibref bibpages ON p.source = bibpages.source AND
> bibpages.rank = p.rank AND bibpages.package = p.package AND
> bibpages.key = 'pages'
> + LEFT OUTER JOIN bibref bibeprint ON p.source = bibeprint.source AND
> bibeprint.rank = p.rank AND bibeprint.package = p.package AND
> bibeprint.key = 'eprint'
> + LEFT OUTER JOIN bibref bibin ON p.source = bibin.source AND
> bibin.rank = p.rank AND bibin.package = p.package AND bibin.key
> = 'in'
> + LEFT OUTER JOIN bibref bibissn ON p.source = bibissn.source AND
> bibissn.rank = p.rank AND bibissn.package = p.package AND
> bibissn.key = 'issn'
> + ORDER BY bibentry -- p.source
> +;
> +$$;
> +
> --
> 1.9.1
>
>
--
Akshita Jha
