Skip to content

Instantly share code, notes, and snippets.

@rich-hart

rich-hart/original_sql_query.sql

Last active August 29, 2015 14:16
Show Gist options
  • Save rich-hart/77bf6f3123a87a216c70 to your computer and use it in GitHub Desktop.
Save rich-hart/77bf6f3123a87a216c70 to your computer and use it in GitHub Desktop.
Download ZIP
taken from cnx-publishing/cnxpublishing/publish.py
Raw
original_sql_query.sql
WITH abstract_insertion AS (
INSERT INTO abstracts (abstractid, abstract, html)
VALUES (DEFAULT, NULL, %(summary)s)
RETURNING abstractid),
license_lookup AS (
SELECT licenseid
FROM licenses
WHERE url = %(license_url)s),
module_insertion AS (
INSERT INTO modules
(uuid, major_version, minor_version,
module_ident, portal_type, moduleid,
name, created, revised, language,
submitter, submitlog,
abstractid,
licenseid,
parent,
parentauthors,
authors, maintainers, licensors,
google_analytics, buylink,
stateid, doctype)
VALUES
({__uuid__}, {__major_version__}, {__minor_version__},
DEFAULT, %(_portal_type)s, {__moduleid__},
%(title)s, %(created)s, CURRENT_TIMESTAMP, %(language)s,
%(publisher)s, %(publication_message)s,
(SELECT abstractid FROM abstract_insertion),
(SELECT licenseid FROM license_lookup),
(SELECT module_ident FROM modules
WHERE uuid || '@' || concat_ws('.', major_version, minor_version) = %(parent_ident_hash)s),
(SELECT authors FROM modules
WHERE uuid || '@' || concat_ws('.', major_version, minor_version) = %(parent_ident_hash)s),
%(authors)s, %(publishers)s, %(copyright_holders)s,
DEFAULT, DEFAULT,
DEFAULT, ' ')
RETURNING
module_ident,
uuid||'@'||concat_ws('.',major_version,minor_version) AS ident_hash),
subjects AS (
INSERT INTO moduletags
SELECT (SELECT module_ident FROM module_insertion),
(SELECT tagid FROM tags WHERE tag = s)
FROM unnest(%(subjects)s::text[]) AS s),
keyword_inserts AS (
INSERT INTO keywords
(word)
(SELECT word FROM unnest(%(keywords)s::text[]) AS word
WHERE word NOT IN (SELECT k.word FROM keywords AS k))
RETURNING word, keywordid),
keywords_relationship_from_new AS (
INSERT INTO modulekeywords
(module_ident, keywordid)
(SELECT (SELECT module_ident FROM module_insertion), k.keywordid
FROM keyword_inserts AS k)),
keywords_relationship_from_existing AS (
INSERT INTO modulekeywords
(module_ident, keywordid)
(SELECT (SELECT module_ident FROM module_insertion), k.keywordid
FROM unnest(%(keywords)s::text[]) AS i
LEFT JOIN keywords AS k ON (i = k.word)
WHERE word not in (SELECT word FROM keyword_inserts)))
SELECT module_ident, ident_hash FROM module_insertion
Raw
python_output.py
sql = ('WITH abstract_insertion AS '
' ( INSERT INTO abstracts (abstractid, abstract, html) '
' VALUES (DEFAULT, '
' NULL, '
' %(summary)s) RETURNING abstractid), '
' license_lookup AS '
' ( SELECT licenseid '
' FROM licenses '
' WHERE url = %(license_url)s), '
' module_insertion AS '
' ( INSERT INTO modules (uuid, major_version, minor_version, module_ident, portal_type, moduleid, name, created, revised, language, submitter, submitlog, abstractid, licenseid, parent, parentauthors, authors, maintainers, licensors, google_analytics, buylink, stateid, doctype) '
' VALUES ({__uuid__}, {__major_version__}, {__minor_version__}, DEFAULT, %(_portal_type)s, {__moduleid__}, %(title)s, %(created)s, CURRENT_TIMESTAMP, %(language)s, %(publisher)s, %(publication_message)s, '
' (SELECT abstractid '
' FROM abstract_insertion), '
' (SELECT licenseid '
' FROM license_lookup), '
' (SELECT module_ident '
' FROM modules '
' WHERE uuid || \'@\' || concat_ws(\'.\', major_version, minor_version) = %(parent_ident_hash)s), '
' (SELECT authors '
' FROM modules '
' WHERE uuid || \'@\' || concat_ws(\'.\', major_version, minor_version) = %(parent_ident_hash)s), %(authors)s, '
' %(publishers)s, '
' %(copyright_holders)s, '
' DEFAULT, '
' DEFAULT, '
' DEFAULT, '
' \' \') RETURNING module_ident, '
' uuid||\'@\'||concat_ws(\'.\',major_version,minor_version) AS ident_hash), '
' subjects AS '
' ( INSERT INTO moduletags SELECT '
' (SELECT module_ident '
' FROM module_insertion), '
' (SELECT tagid '
' FROM tags '
' WHERE tag = s) '
' FROM unnest(%(subjects)s::text[]) AS s), '
' keyword_inserts AS '
' ( INSERT INTO keywords (word) '
' (SELECT word '
' FROM unnest(%(keywords)s::text[]) AS word '
' WHERE word NOT IN '
' (SELECT k.word '
' FROM keywords AS k)) RETURNING word, '
' keywordid), '
' keywords_relationship_from_new AS '
' ( INSERT INTO modulekeywords (module_ident, keywordid) '
' (SELECT '
' (SELECT module_ident '
' FROM module_insertion), k.keywordid '
' FROM keyword_inserts AS k)), '
' keywords_relationship_from_existing AS '
' ( INSERT INTO modulekeywords (module_ident, keywordid) '
' (SELECT '
' (SELECT module_ident '
' FROM module_insertion), k.keywordid '
' FROM unnest(%(keywords)s::text[]) AS i '
' LEFT JOIN keywords AS k ON (i = k.word) '
' WHERE word not in '
' (SELECT word '
' FROM keyword_inserts))) '
'SELECT module_ident, '
' ident_hash '
'FROM module_insertion')
Raw
raw_output.sql
WITH abstract_insertion AS
( INSERT INTO abstracts (abstractid, abstract, html)
VALUES (DEFAULT,
NULL,
%(summary)s) RETURNING abstractid),
license_lookup AS
( SELECT licenseid
FROM licenses
WHERE url = %(license_url)s),
module_insertion AS
( INSERT INTO modules (uuid, major_version, minor_version, module_ident, portal_type, moduleid, name, created, revised, language, submitter, submitlog, abstractid, licenseid, parent, parentauthors, authors, maintainers, licensors, google_analytics, buylink, stateid, doctype)
VALUES ({__uuid__}, {__major_version__}, {__minor_version__}, DEFAULT, %(_portal_type)s, {__moduleid__}, %(title)s, %(created)s, CURRENT_TIMESTAMP, %(language)s, %(publisher)s, %(publication_message)s,
(SELECT abstractid
FROM abstract_insertion),
(SELECT licenseid
FROM license_lookup),
(SELECT module_ident
FROM modules
WHERE uuid || '@' || concat_ws('.', major_version, minor_version) = %(parent_ident_hash)s),
(SELECT authors
FROM modules
WHERE uuid || '@' || concat_ws('.', major_version, minor_version) = %(parent_ident_hash)s), %(authors)s,
%(publishers)s,
%(copyright_holders)s,
DEFAULT,
DEFAULT,
DEFAULT,
' ') RETURNING module_ident,
uuid||'@'||concat_ws('.',major_version,minor_version) AS ident_hash),
subjects AS
( INSERT INTO moduletags SELECT
(SELECT module_ident
FROM module_insertion),
(SELECT tagid
FROM tags
WHERE tag = s)
FROM unnest(%(subjects)s::text[]) AS s),
keyword_inserts AS
( INSERT INTO keywords (word)
(SELECT word
FROM unnest(%(keywords)s::text[]) AS word
WHERE word NOT IN
(SELECT k.word
FROM keywords AS k)) RETURNING word,
keywordid),
keywords_relationship_from_new AS
( INSERT INTO modulekeywords (module_ident, keywordid)
(SELECT
(SELECT module_ident
FROM module_insertion), k.keywordid
FROM keyword_inserts AS k)),
keywords_relationship_from_existing AS
( INSERT INTO modulekeywords (module_ident, keywordid)
(SELECT
(SELECT module_ident
FROM module_insertion), k.keywordid
FROM unnest(%(keywords)s::text[]) AS i
LEFT JOIN keywords AS k ON (i = k.word)
WHERE word not in
(SELECT word
FROM keyword_inserts)))
SELECT module_ident,
ident_hash
FROM module_insertion
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment