From 419bddcb0377e82e7177356350d35bf84b3e80d8 Mon Sep 17 00:00:00 2001 From: Bryan Newbold Date: Fri, 17 Aug 2018 15:08:47 -0700 Subject: cockroach: refactor custom insert queries into diesel Basically reverts some big speedups around "single query inserts" (to ident, rev, and edit tables concurrently), because cockroach doesn't support "multiple uses of CTEs" (table expressions). There's probably a way to rewrite using subqueries instead. Also, would actually want to refactor and use NewRow structs instead of tuples, I think. --- rust/src/api_server.rs | 195 ++++++++++++++++++++++++------------------------- 1 file changed, 95 insertions(+), 100 deletions(-) diff --git a/rust/src/api_server.rs b/rust/src/api_server.rs index e35ffd21..b445d63a 100644 --- a/rust/src/api_server.rs +++ b/rust/src/api_server.rs @@ -497,26 +497,25 @@ impl Server { check_issn(extid)?; } - let edit: ContainerEditRow = diesel::sql_query( - "WITH rev AS ( INSERT INTO container_rev (name, publisher, issnl, wikidata_qid, abbrev, coden, extra_json) - VALUES ($1, $2, $3, $4, $5, $6, $7) - RETURNING id ), - ident AS ( INSERT INTO container_ident (rev_id) - VALUES ((SELECT rev.id FROM rev)) - RETURNING id ) - INSERT INTO container_edit (editgroup_id, ident_id, rev_id) VALUES - ($8, (SELECT ident.id FROM ident), (SELECT rev.id FROM rev)) - RETURNING *", - ).bind::(entity.name) - .bind::, _>(entity.publisher) - .bind::, _>(entity.issnl) - .bind::, _>(entity.wikidata_qid) - .bind::, _>(entity.abbrev) - .bind::, _>(entity.coden) - .bind::, _>(entity.extra) - .bind::(editgroup_id) + let rev_id: Uuid = insert_into(container_rev::table) + .values((container_rev::name.eq(entity.name), + container_rev::publisher.eq(entity.publisher), + container_rev::issnl.eq(entity.issnl), + container_rev::wikidata_qid.eq(entity.wikidata_qid), + container_rev::abbrev.eq(entity.abbrev), + container_rev::coden.eq(entity.coden), + container_rev::extra_json.eq(entity.extra))) + .returning(container_rev::id) + .get_result(conn)?; + let ident_id: Uuid = insert_into(container_ident::table) + .values(container_ident::rev_id.eq(rev_id)) + .returning(container_ident::id) + .get_result(conn)?; + let edit: ContainerEditRow = insert_into(container_edit::table) + .values((container_edit::editgroup_id.eq(editgroup_id), + container_edit::ident_id.eq(ident_id), + container_edit::rev_id.eq(rev_id))) .get_result(conn)?; - edit.into_model() } @@ -537,23 +536,23 @@ impl Server { check_wikidata_qid(extid)?; } - let edit: CreatorEditRow = diesel::sql_query( - "WITH rev AS ( INSERT INTO creator_rev (display_name, given_name, surname, orcid, wikidata_qid, extra_json) - VALUES ($1, $2, $3, $4, $5, $6) - RETURNING id ), - ident AS ( INSERT INTO creator_ident (rev_id) - VALUES ((SELECT rev.id FROM rev)) - RETURNING id ) - INSERT INTO creator_edit (editgroup_id, ident_id, rev_id) VALUES - ($7, (SELECT ident.id FROM ident), (SELECT rev.id FROM rev)) - RETURNING *", - ).bind::(entity.display_name) - .bind::, _>(entity.given_name) - .bind::, _>(entity.surname) - .bind::, _>(entity.orcid) - .bind::, _>(entity.wikidata_qid) - .bind::, _>(entity.extra) - .bind::(editgroup_id) + let rev_id: Uuid = insert_into(creator_rev::table) + .values((creator_rev::display_name.eq(entity.display_name), + creator_rev::given_name.eq(entity.given_name), + creator_rev::surname.eq(entity.surname), + creator_rev::orcid.eq(entity.orcid), + creator_rev::wikidata_qid.eq(entity.wikidata_qid), + creator_rev::extra_json.eq(entity.extra))) + .returning(creator_rev::id) + .get_result(conn)?; + let ident_id: Uuid = insert_into(creator_ident::table) + .values(creator_ident::rev_id.eq(rev_id)) + .returning(creator_ident::id) + .get_result(conn)?; + let edit: CreatorEditRow = insert_into(creator_edit::table) + .values((creator_edit::editgroup_id.eq(editgroup_id), + creator_edit::ident_id.eq(ident_id), + creator_edit::rev_id.eq(rev_id))) .get_result(conn)?; edit.into_model() @@ -570,25 +569,24 @@ impl Server { Some(param) => fcid2uuid(¶m)?, }; - let edit: FileEditRow = - diesel::sql_query( - "WITH rev AS ( INSERT INTO file_rev (size, sha1, sha256, md5, mimetype, extra_json) - VALUES ($1, $2, $3, $4, $5, $6) - RETURNING id ), - ident AS ( INSERT INTO file_ident (rev_id) - VALUES ((SELECT rev.id FROM rev)) - RETURNING id ) - INSERT INTO file_edit (editgroup_id, ident_id, rev_id) VALUES - ($7, (SELECT ident.id FROM ident), (SELECT rev.id FROM rev)) - RETURNING *", - ).bind::, _>(entity.size) - .bind::, _>(entity.sha1) - .bind::, _>(entity.sha256) - .bind::, _>(entity.md5) - .bind::, _>(entity.mimetype) - .bind::, _>(entity.extra) - .bind::(editgroup_id) - .get_result(conn)?; + let rev_id: Uuid = insert_into(file_rev::table) + .values((file_rev::size.eq(entity.size), + file_rev::sha1.eq(entity.sha1), + file_rev::sha256.eq(entity.sha256), + file_rev::md5.eq(entity.md5), + file_rev::mimetype.eq(entity.mimetype), + file_rev::extra_json.eq(entity.extra))) + .returning(file_rev::id) + .get_result(conn)?; + let ident_id: Uuid = insert_into(file_ident::table) + .values(file_ident::rev_id.eq(rev_id)) + .returning(file_ident::id) + .get_result(conn)?; + let edit: FileEditRow = insert_into(file_edit::table) + .values((file_edit::editgroup_id.eq(editgroup_id), + file_edit::ident_id.eq(ident_id), + file_edit::rev_id.eq(rev_id))) + .get_result(conn)?; let _releases: Option> = match entity.releases { None => None, @@ -684,36 +682,35 @@ impl Server { None => None, }; - let edit: ReleaseEditRow = diesel::sql_query( - "WITH rev AS ( INSERT INTO release_rev (title, release_type, release_status, release_date, doi, pmid, pmcid, wikidata_qid, isbn13, core_id, volume, issue, pages, work_ident_id, container_ident_id, publisher, language, extra_json) - VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18) - RETURNING id ), - ident AS ( INSERT INTO release_ident (rev_id) - VALUES ((SELECT rev.id FROM rev)) - RETURNING id ) - INSERT INTO release_edit (editgroup_id, ident_id, rev_id) VALUES - ($19, (SELECT ident.id FROM ident), (SELECT rev.id FROM rev)) - RETURNING *", - ).bind::(entity.title) - .bind::, _>(entity.release_type) - .bind::, _>(entity.release_status) - .bind::, _>( - entity.release_date.map(|v| v.naive_utc().date())) - .bind::, _>(entity.doi) - .bind::, _>(entity.pmid) - .bind::, _>(entity.pmcid) - .bind::, _>(entity.wikidata_qid) - .bind::, _>(entity.isbn13) - .bind::, _>(entity.core_id) - .bind::, _>(entity.volume) - .bind::, _>(entity.issue) - .bind::, _>(entity.pages) - .bind::(work_id) - .bind::, _>(container_id) - .bind::, _>(entity.publisher) - .bind::, _>(entity.language) - .bind::, _>(entity.extra) - .bind::(editgroup_id) + let rev_id: Uuid = insert_into(release_rev::table) + .values((release_rev::title.eq(entity.title), + release_rev::release_type.eq(entity.release_type), + release_rev::release_status.eq(entity.release_status), + release_rev::release_date.eq(entity.release_date.map(|v| v.naive_utc().date())), + release_rev::doi.eq(entity.doi), + release_rev::pmid.eq(entity.pmid), + release_rev::pmcid.eq(entity.pmcid), + release_rev::wikidata_qid.eq(entity.wikidata_qid), + release_rev::isbn13.eq(entity.isbn13), + release_rev::core_id.eq(entity.core_id), + release_rev::volume.eq(entity.volume), + release_rev::issue.eq(entity.issue), + release_rev::pages.eq(entity.pages), + release_rev::work_ident_id.eq(work_id), + release_rev::container_ident_id.eq(container_id), + release_rev::publisher.eq(entity.publisher), + release_rev::language.eq(entity.language), + release_rev::extra_json.eq(entity.extra))) + .returning(release_rev::id) + .get_result(conn)?; + let ident_id: Uuid = insert_into(release_ident::table) + .values(release_ident::rev_id.eq(rev_id)) + .returning(release_ident::id) + .get_result(conn)?; + let edit: ReleaseEditRow = insert_into(release_edit::table) + .values((release_edit::editgroup_id.eq(editgroup_id), + release_edit::ident_id.eq(ident_id), + release_edit::rev_id.eq(rev_id))) .get_result(conn)?; let _refs: Option> = match entity.refs { @@ -825,21 +822,19 @@ impl Server { Some(param) => fcid2uuid(¶m)?, }; - let edit: WorkEditRow = - diesel::sql_query( - "WITH rev AS ( INSERT INTO work_rev (extra_json) - VALUES ($1) - RETURNING id ), - ident AS ( INSERT INTO work_ident (rev_id) - VALUES ((SELECT rev.id FROM rev)) - RETURNING id ) - INSERT INTO work_edit (editgroup_id, ident_id, rev_id) VALUES - ($2, (SELECT ident.id FROM ident), (SELECT rev.id FROM rev)) - RETURNING *", - ).bind::, _>(entity.extra) - .bind::(editgroup_id) - .get_result(conn)?; - + let rev_id: Uuid = insert_into(work_rev::table) + .values(work_rev::extra_json.eq(entity.extra)) + .returning(work_rev::id) + .get_result(conn)?; + let ident_id: Uuid = insert_into(work_ident::table) + .values(work_ident::rev_id.eq(rev_id)) + .returning(work_ident::id) + .get_result(conn)?; + let edit: WorkEditRow = insert_into(work_edit::table) + .values((work_edit::editgroup_id.eq(editgroup_id), + work_edit::ident_id.eq(ident_id), + work_edit::rev_id.eq(rev_id))) + .get_result(conn)?; edit.into_model() } -- cgit v1.2.3