"Events" API logic #218
Assignees
Milestone
Comments
|
|
#279 in progress |
|
All transactions listed above should be moved to notifications (#280) and will not require scanning for entire chain. Events should contain all transactions that were issued by the address, not related to it |
|
I will update the SQL query as it is optimized. Here is the first prototype, which includes only a part of the queries and satisfies the minimum execution time. -- Pocket posts
select
('pocketnetteam')TP,
t.Height,
t.BlockNum,
t.Hash
from Transactions t indexed by Transactions_Type_Last_String1_Height_Id
where t.Type in (200,201,202)
and t.String1 = 'PEj7QNjKdDPqE9kMDRboKoCtp8V6vZeZPd'
and t.Last = 1
and t.Height > 1554639
and (t.Height < 1684239 or (t.Height = 1684239 and t.BlockNum < 9999999))
----------------------------------------
-- Incoming money
union
select
('money')TP,
t.Height,
t.BlockNum,
t.Hash
from TxOutputs o indexed by TxOutputs_AddressHash_TxHeight_TxHash
join Transactions t indexed by Transactions_Hash_Type_Height
on t.Hash = o.TxHash
and t.Type in (1,2,3) -- 1 default money transfer, 2 coinbase, 3 coinstake
and t.Height > 1554639
and (t.Height < 1684239 or (t.Height = 1684239 and t.BlockNum < 9999999))
join TxOutputs i indexed by TxOutputs_SpentTxHash
on i.SpentTxHash = o.TxHash
and i.AddressHash != o.AddressHash
where o.AddressHash in ( 'PQzoDW8StdS3skmDuUK4z5L9dMq24n72M4' )
and o.TxHeight > 1642441
and o.TxHeight < 1684239
----------------------------------------
-- Referals
union
select
('referals')TP,
t.Height,
t.BlockNum,
t.Hash
from Transactions t --indexed by Transactions_Type_Last_String2_Height
where t.Type = 100
and t.Last = 1
and t.String2 in ( 'PKiJHawMenkQPm7oW4cCJ1XuZhkvSNtgin' )
and t.Height > 1554639
and (t.Height < 1684239 or (t.Height = 1684239 and t.BlockNum < 9999999))
and t.ROWID = (select min(tt.ROWID) from Transactions tt where tt.Id = t.Id)
/* TODO : VERY VERY SLOW
----------------------------------------
-- Comment answers
union
select
('answers')TP,
c.String1 as AddressOrd,
--orig.Height as HeightOrd,
a.Height,
a.BlockNum,
a.Hash,
a.Type,
a.String1 as addrFrom,
a.String2 as RootTxHash,
a.String3 as posttxid,
a.String4 as parentid,
a.String5 as answerid,
a.Time,
null
from Transactions c indexed by Transactions_Type_Last_String1_Height_Id -- My comments
join Transactions a indexed by Transactions_Type_Last_Height_String5_String1
on a.Type in (204, 205) and a.Last = 1 and a.String5 = c.String2 and a.String1 != c.String1
and (a.Height < ? or (a.Height = ? and a.BlockNum < ?))
--join Transactions orig indexed by Transactions_Hash_Height -- TODO (losty): very slow here. However, even slow without it
-- TODO: creating Transactions_Type_Last_Height_String5_String1_String2 for c speed it up a lot
-- on orig.Hash = a.String2
where c.Type in (204, 205)
and c.Last = 1
and c.String1 in ( ? )
and c.Height > 0
*/
----------------------------------------
-- Comments for my content
union
select
('comments')TP,
c.Height,
c.BlockNum,
c.Hash
from Transactions p indexed by Transactions_String1_Last_Height
cross join Transactions c indexed by Transactions_Type_Last_String3_Height
on c.Type in (204,205)
and c.Height > 0
and c.Last = 1
and c.String3 = p.String2
and c.String1 != p.String1
and c.Hash = c.String2
and c.Height > 1554639
and (c.Height < 1684239 or (c.Height = 1684239 and c.BlockNum < 9999999))
where p.Type in (200,201,202)
and p.Last = 1
and p.Height > 1554639
and p.String1 in ( 'PKiJHawMenkQPm7oW4cCJ1XuZhkvSNtgin' )
----------------------------------------
-- Subscribers
union
select
('subscribers')TP,
subs.Height,
subs.BlockNum,
subs.Hash
from Transactions subs --indexed by Transactions_Type_Last_String2_Height
join Transactions u --indexed by Transactions_Type_Last_String1_Height_Id
on u.Type in (100)
and u.Last = 1
and u.String1 = subs.String1
and u.Height is not null
where subs.Type in (302, 303) -- Ignoring unsubscribers?
and subs.Last = 1
and subs.String2 in ( 'PKiJHawMenkQPm7oW4cCJ1XuZhkvSNtgin' )
and subs.Height > 1554639
and (subs.Height < 1684239 or (subs.Height = 1684239 and subs.BlockNum < 9999999))
----------------------------------------
-- Comment scores
union
select
('commentscores')TP,
s.Height,
s.BlockNum,
s.Hash
from Transactions c indexed by Transactions_Type_Last_String1_Height_Id
join Transactions s indexed by Transactions_Type_Last_String2_Height
on s.Type in (301)
and s.Last in (0,1)
and s.String2 = c.String2
and s.Height > 1554639
and (s.Height < 1684239 or (s.Height = 1684239 and s.BlockNum < 9999999))
where c.Type in (204,205)
and c.Last = 1
and c.Height > 1554639
and c.String1 in ( 'PKiJHawMenkQPm7oW4cCJ1XuZhkvSNtgin' )
----------------------------------------
-- Content scores
union
select
('contentscores')TP,
s.Height,
s.BlockNum,
s.Hash
from Transactions c indexed by Transactions_Type_Last_String1_Height_Id
join Transactions s indexed by Transactions_Type_Last_String2_Height
on s.Type in (300) and s.Last in (0,1) and s.String2 = c.String2
and s.Height > 1554639
and (s.Height < 1684239 or (s.Height = 1684239 and s.BlockNum < 9999999))
where c.Type in (200, 201, 202)
and c.Last = 1
and c.String1 in ( 'PKiJHawMenkQPm7oW4cCJ1XuZhkvSNtgin' )
and c.Height > 1554639
----------------------------------------
-- Content from private subscribers
union
select
('privatecontent')TP,
cps.Height,
cps.BlockNum,
cps.Hash
from Transactions subs indexed by Transactions_Type_Last_String1_Height_Id -- Subscribers private
cross join Transactions cps indexed by Transactions_Type_Last_String1_Height_Id -- content for private subscribers
on cps.Type in (200,201,202)
and cps.Last = 1
and cps.String1 = subs.String2
and cps.Hash = cps.String2 -- Only first content record
and cps.Height > 1556041
and (cps.Height < 1685641 or (cps.Height = 1685641 and cps.BlockNum < 999999))
left join Payload p
on p.TxHash = cps.Hash
where subs.Type = 303
and subs.Last = 1
and subs.Height > 1556041
and subs.String1 in ( 'PT3Tpy79Mbvoz4Wi41kqdeNXQLzLurfSfL' )
----------------------------------------
-- Boosts for my content
union
select
('boost')TP,
tBoost.Height,
tBoost.BlockNum,
tBoost.Hash
from Transactions tBoost indexed by Transactions_Type_Last_Height_Id
join Transactions tContent indexed by Transactions_Type_Last_String1_String2_Height
on tContent.Type in (200,201,202)
and tContent.Last in (0,1)
and tContent.Height > 1554639
and tContent.String1 in ( 'PKiJHawMenkQPm7oW4cCJ1XuZhkvSNtgin' )
and tContent.String2 = tBoost.String2
where tBoost.Type in (208)
and tBoost.Last in (0,1)
and tBoost.Height > 1554639
and (tBoost.Height < 1684239 or (tBoost.Height = 1684239 and tBoost.BlockNum < 9999999))
----------------------------------------
-- Reposts
union
select
('reposts')TP,
r.Height,
r.BlockNum,
r.Hash
from Transactions p indexed by Transactions_Type_Last_String1_Height_Id
join Transactions r indexed by Transactions_Type_Last_String3_Height
on r.Type in (200,201,202)
and r.Last = 1
and r.String3 = p.Hash
and r.Height > 1554639
and (r.Height < 1684239 or (r.Height = 1684239 and r.BlockNum < 9999999))
where p.Type in (200,201,202)
and p.Last = 1
and p.Height > 1554639
and p.String1 in ( 'PKiJHawMenkQPm7oW4cCJ1XuZhkvSNtgin' )
----------------------------------------
-- Global order and limit for pagination
order by Height desc, BlockNum desc
limit 10New indexescreate index Transactions_Hash_Type_Height on Transactions (Hash, Type, Height);
create index TxOutputs_AddressHash_TxHeight_TxHash on TxOutputs (AddressHash, TxHeight, TxHash); |
create index Transactions_Type_Last_String5_Height on Transactions (Type, Last, String5, Height) |
|
"getevents" request. Params:
The depth of the search is always equal to 3 months (129600 blocks). Request example: Response: |
7 tasks
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Two API methods:
geteventscount [ address, bottomHeight ]address- account address for filter eventsheight- start block height for calculate count of eventsgetevents [ address, topHeight, topHeightTx, pageSize, [ filter ] ]address- account address for filter eventstopHeight- top block height for start paginationtopHeightTx- pagination start recordpageSize- pagination count[ filter ]- array of events for filterShort forms of accounts and content are also required: #217
Prototype methods:
pocketnet.core/src/pocketdb/repositories/web/WebRpcRepository.h
Lines 98 to 105 in d6f8b68
The text was updated successfully, but these errors were encountered: