Skip to content
Snippets Groups Projects

find_votes and list_votes fixes

Merged find_votes and list_votes fixes
find_list_votes_fixes into develop
Merged Andrzej Lisak requested to merge find_list_votes_fixes into develop
Viewing commit 76d51a4c
Next
Show latest version
9 files
+ 169
− 149
Compare changes
  • Side-by-side
  • Inline
Files
9
  • 76d51a4c 
    vote related views/types/functions extended by id since database_api requires it
new SQL function to cover find_votes functionality
neither voter nor post is mandatory in list_votes calls
fixed internal _get_post_id in bridge_api
find_votes_impl no longer requires full context, just what it actually uses: db
find_votes_impl no longer checks passed arguments, only its official API version does
list_votes_impl removed, only its official version is needed
official API find_votes/list_votes now properly pack response in dictionary with 'votes' list
hive/db/schema.py
+ 138
− 93
@@ -466,6 +466,60 @@ def setup(db):
sql = "CREATE INDEX hive_communities_ft1 ON hive_communities USING GIN (to_tsvector('english', title || ' ' || about))"
db.query(sql)
sql = """
DROP FUNCTION IF EXISTS find_comment_id(character varying, character varying, boolean)
;
CREATE OR REPLACE FUNCTION find_comment_id(
in _author hive_accounts.name%TYPE,
in _permlink hive_permlink_data.permlink%TYPE,
in _check boolean)
RETURNS INT
LANGUAGE 'plpgsql'
AS
$function$
DECLARE
post_id INT;
BEGIN
SELECT INTO post_id COALESCE( (SELECT hp.id
FROM hive_posts hp
JOIN hive_accounts ha ON ha.id = hp.author_id
JOIN hive_permlink_data hpd ON hpd.id = hp.permlink_id
WHERE ha.name = _author AND hpd.permlink = _permlink AND hp.counter_deleted = 0
), 0 );
IF _check AND (_author <> '' OR _permlink <> '') AND post_id = 0 THEN
RAISE EXCEPTION 'Post %/% does not exist', _author, _permlink;
END IF;
RETURN post_id;
END
$function$
;
"""
db.query_no_return(sql)
sql = """
DROP FUNCTION IF EXISTS find_account_id(character varying, boolean)
;
CREATE OR REPLACE FUNCTION find_account_id(
in _account hive_accounts.name%TYPE,
in _check boolean)
RETURNS INT
LANGUAGE 'plpgsql'
AS
$function$
DECLARE
account_id INT;
BEGIN
SELECT INTO account_id COALESCE( ( SELECT id FROM hive_accounts WHERE name=_account ), 0 );
IF _check AND account_id = 0 THEN
RAISE EXCEPTION 'Account % does not exist', _account;
END IF;
RETURN account_id;
END
$function$
;
"""
db.query_no_return(sql)
sql = """
DROP FUNCTION if exists process_hive_post_operation(character varying,character varying,character varying,character varying,timestamp without time zone,timestamp without time zone)
;
@@ -859,6 +913,7 @@ def setup(db):
CREATE OR REPLACE VIEW hive_votes_view
AS
SELECT
hv.id,
hv.voter_id as voter_id,
ha_a.name as author,
hpd.permlink as permlink,
@@ -885,6 +940,7 @@ def setup(db):
DROP TYPE IF EXISTS database_api_vote CASCADE;
CREATE TYPE database_api_vote AS (
id BIGINT,
voter VARCHAR(16),
author VARCHAR(16),
permlink VARCHAR(255),
@@ -896,30 +952,47 @@ def setup(db):
reputation FLOAT4
);
DROP FUNCTION IF EXISTS get_account(character varying, boolean);
CREATE OR REPLACE FUNCTION get_account(
in _account hive_accounts.name%TYPE,
in _check boolean)
RETURNS INT
DROP FUNCTION IF EXISTS find_votes( character varying, character varying )
;
CREATE OR REPLACE FUNCTION public.find_votes
(
in _AUTHOR hive_accounts.name%TYPE,
in _PERMLINK hive_permlink_data.permlink%TYPE
)
RETURNS SETOF database_api_vote
LANGUAGE 'plpgsql'
AS
$function$
DECLARE
account_id INT;
DECLARE _POST_ID INT;
BEGIN
SELECT INTO account_id COALESCE( ( SELECT id FROM hive_accounts WHERE name=_account ), 0 );
IF _check AND account_id = 0 THEN
RAISE EXCEPTION 'Account % does not exist', _account;
END IF;
_POST_ID = find_comment_id( _AUTHOR, _PERMLINK, True);
RETURN account_id;
END
$function$
;
RETURN QUERY
(
SELECT
v.id,
v.voter,
v.author,
v.permlink,
v.weight,
v.rshares,
v.percent,
v.last_update,
v.num_changes,
v.reputation
FROM
hive_votes_view v
WHERE
v.post_id = _POST_ID
ORDER BY
voter_id
);
DROP FUNCTION IF EXISTS list_votes_by_voter_comment( character varying, character varying, character varying, int );
END
$function$;
DROP FUNCTION IF EXISTS list_votes_by_voter_comment( character varying, character varying, character varying, int )
;
CREATE OR REPLACE FUNCTION public.list_votes_by_voter_comment
(
in _VOTER hive_accounts.name%TYPE,
@@ -935,38 +1008,39 @@ def setup(db):
DECLARE _POST_ID INT;
BEGIN
_VOTER_ID = get_account( _VOTER, true );
_POST_ID = find_comment_id( _AUTHOR, _PERMLINK, True);
_VOTER_ID = find_account_id( _VOTER, _VOTER != '' );
_POST_ID = find_comment_id( _AUTHOR, _PERMLINK, _AUTHOR != '' OR _PERMLINK != '' );
RETURN QUERY
(
SELECT
v.voter,
v.author,
v.permlink,
v.weight,
v.rshares,
v.percent,
v.last_update,
v.num_changes,
v.reputation
FROM
hive_votes_view v
WHERE
( v.voter_id = _VOTER_ID and v.post_id >= _POST_ID )
OR
( v.voter_id > _VOTER_ID )
ORDER BY
voter_id,
post_id
LIMIT _LIMIT
SELECT
v.id,
v.voter,
v.author,
v.permlink,
v.weight,
v.rshares,
v.percent,
v.last_update,
v.num_changes,
v.reputation
FROM
hive_votes_view v
WHERE
( v.voter_id = _VOTER_ID and v.post_id >= _POST_ID )
OR
( v.voter_id > _VOTER_ID )
ORDER BY
voter_id,
post_id
LIMIT _LIMIT
);
END
$function$;
DROP FUNCTION IF EXISTS list_votes_by_comment_voter( character varying, character varying, character varying, int );
DROP FUNCTION IF EXISTS list_votes_by_comment_voter( character varying, character varying, character varying, int )
;
CREATE OR REPLACE FUNCTION public.list_votes_by_comment_voter
(
in _VOTER hive_accounts.name%TYPE,
@@ -982,31 +1056,32 @@ def setup(db):
DECLARE _POST_ID INT;
BEGIN
_VOTER_ID = get_account( _VOTER, true );
_POST_ID = find_comment_id( _AUTHOR, _PERMLINK, True);
_VOTER_ID = find_account_id( _VOTER, _VOTER != '' );
_POST_ID = find_comment_id( _AUTHOR, _PERMLINK, _AUTHOR != '' OR _PERMLINK != '' );
RETURN QUERY
(
SELECT
v.voter,
v.author,
v.permlink,
v.weight,
v.rshares,
v.percent,
v.last_update,
v.num_changes,
v.reputation
FROM
hive_votes_view v
WHERE
( v.post_id = _POST_ID and v.voter_id >= _VOTER_ID )
OR
( v.post_id > _POST_ID )
ORDER BY
post_id,
voter_id
LIMIT _LIMIT
SELECT
v.id,
v.voter,
v.author,
v.permlink,
v.weight,
v.rshares,
v.percent,
v.last_update,
v.num_changes,
v.reputation
FROM
hive_votes_view v
WHERE
( v.post_id = _POST_ID and v.voter_id >= _VOTER_ID )
OR
( v.post_id > _POST_ID )
ORDER BY
post_id,
voter_id
LIMIT _LIMIT
);
END
@@ -1014,36 +1089,6 @@ def setup(db):
"""
db.query_no_return(sql)
sql = """
DROP FUNCTION IF EXISTS find_comment_id(character varying, character varying, boolean)
;
CREATE OR REPLACE FUNCTION find_comment_id(
in _author hive_accounts.name%TYPE,
in _permlink hive_permlink_data.permlink%TYPE,
in _check boolean)
RETURNS INT
LANGUAGE 'plpgsql'
AS
$function$
DECLARE
post_id INT;
BEGIN
SELECT INTO post_id COALESCE( (SELECT hp.id
FROM hive_posts hp
JOIN hive_accounts ha ON ha.id = hp.author_id
JOIN hive_permlink_data hpd ON hpd.id = hp.permlink_id
WHERE ha.name = _author AND hpd.permlink = _permlink AND hp.counter_deleted = 0
), 0 );
IF _check AND (_author <> '' OR _permlink <> '') AND post_id = 0 THEN
RAISE EXCEPTION 'Post %/% does not exist', _author, _permlink;
END IF;
RETURN post_id;
END
$function$
;
"""
db.query_no_return(sql)
sql = """
DROP TYPE IF EXISTS database_api_post CASCADE;
CREATE TYPE database_api_post AS (