From 557f29d51fcf6cd1f7a3c60ff95013b0133560b0 Mon Sep 17 00:00:00 2001 From: Gabriel Moise Date: Tue, 25 Nov 2025 15:56:01 +0000 Subject: [PATCH 1/4] Teztale: Add assigned_shard_indices type Currently, this does not change anything semantically, just adds the field to be filled in later commits with information from the L1 context. --- teztale/bin_teztale_archiver/converter.ml | 16 ++++++- teztale/bin_teztale_archiver/json_archiver.ml | 21 +++++++-- teztale/bin_teztale_server/exporter.ml | 1 + teztale/lib_teztale_base/data.ml | 45 ++++++++++++++++--- 4 files changed, 71 insertions(+), 12 deletions(-) diff --git a/teztale/bin_teztale_archiver/converter.ml b/teztale/bin_teztale_archiver/converter.ml index f5900c85c098..a8d21ac288d3 100644 --- a/teztale/bin_teztale_archiver/converter.ml +++ b/teztale/bin_teztale_archiver/converter.ml @@ -20,7 +20,13 @@ let to_received_ops ctx endpoint auth level data = let received_ops = List.map (fun Data.Delegate_operations. - {delegate; first_slot; attesting_power; operations} + { + delegate; + first_slot; + attesting_power; + operations; + assigned_shard_indices = _; + } -> ( Consensus_ops.{address = delegate; first_slot; power = attesting_power}, List.flatten @@ -86,7 +92,13 @@ let included_ops_map level data = List.fold_left (fun acc Data.Delegate_operations. - {delegate; first_slot = _; attesting_power; operations} + { + delegate; + first_slot = _; + attesting_power; + operations; + assigned_shard_indices = _; + } -> List.fold_left (fun acc diff --git a/teztale/bin_teztale_archiver/json_archiver.ml b/teztale/bin_teztale_archiver/json_archiver.ml index 1f7334248ed0..8afc72926e95 100644 --- a/teztale/bin_teztale_archiver/json_archiver.ml +++ b/teztale/bin_teztale_archiver/json_archiver.ml @@ -109,7 +109,13 @@ let add_inclusion_in_block block_hash validators delegate_operations = List.fold_left (fun (acc, missing) Data.Delegate_operations.( - {delegate; first_slot; attesting_power; operations} as delegate_ops) + { + delegate; + first_slot; + attesting_power; + operations; + assigned_shard_indices; + } as delegate_ops) -> match List.partition @@ -133,6 +139,7 @@ let add_inclusion_in_block block_hash validators delegate_operations = op.op.kind ?ops_round:op.op.round operations; + assigned_shard_indices; } :: acc, missing' ) @@ -161,6 +168,7 @@ let add_inclusion_in_block block_hash validators delegate_operations = block_inclusion = [block_hash]; }; ]; + assigned_shard_indices = []; } :: acc) updated_known @@ -353,8 +361,13 @@ let dump_received logger path ?unaccurate level received_ops = List.fold_left (fun (acc, missing) Data.Delegate_operations.( - {delegate; first_slot; attesting_power; operations} as - delegate_ops) + { + delegate; + first_slot; + attesting_power; + operations; + assigned_shard_indices; + } as delegate_ops) -> match List.partition @@ -372,6 +385,7 @@ let dump_received logger path ?unaccurate level received_ops = first_slot; attesting_power; operations = merge_operations operations new_operations; + assigned_shard_indices; } :: acc, missing' ) @@ -415,6 +429,7 @@ let dump_received logger path ?unaccurate level received_ops = block_inclusion = []; }) ops; + assigned_shard_indices = []; } :: acc) updated_known diff --git a/teztale/bin_teztale_server/exporter.ml b/teztale/bin_teztale_server/exporter.ml index 8c2f98905c1c..50fd55c6d993 100644 --- a/teztale/bin_teztale_server/exporter.ml +++ b/teztale/bin_teztale_server/exporter.ml @@ -370,6 +370,7 @@ let translate_ops info = first_slot; attesting_power = power; operations = translate pkh_ops; + assigned_shard_indices = []; } :: acc) info diff --git a/teztale/lib_teztale_base/data.ml b/teztale/lib_teztale_base/data.ml index c6d0e986af7b..de6910e99cc3 100644 --- a/teztale/lib_teztale_base/data.ml +++ b/teztale/lib_teztale_base/data.ml @@ -101,6 +101,7 @@ module Delegate_operations = struct first_slot : int; attesting_power : int; operations : operation list; + assigned_shard_indices : int list; } let legacy_encoding = @@ -110,7 +111,13 @@ module Delegate_operations = struct (fun (delegate, reception_time, errors, block_inclusion) -> match (reception_time, block_inclusion) with | None, [] -> - {delegate; first_slot = 0; attesting_power = 0; operations = []} + { + delegate; + first_slot = 0; + attesting_power = 0; + operations = []; + assigned_shard_indices = []; + } | _, _ -> let mempool_inclusion = match reception_time with @@ -132,6 +139,7 @@ module Delegate_operations = struct block_inclusion; }; ]; + assigned_shard_indices = []; }) (obj4 (req "delegate" Tezos_crypto.Signature.Public_key_hash.encoding) @@ -142,15 +150,38 @@ module Delegate_operations = struct let encoding = let open Data_encoding in conv - (fun {delegate; first_slot; attesting_power; operations} -> - (delegate, first_slot, attesting_power, operations)) - (fun (delegate, first_slot, attesting_power, operations) -> - {delegate; first_slot; attesting_power; operations}) - (obj4 + (fun { + delegate; + first_slot; + attesting_power; + operations; + assigned_shard_indices; + } + -> + ( delegate, + first_slot, + attesting_power, + operations, + assigned_shard_indices )) + (fun ( delegate, + first_slot, + attesting_power, + operations, + assigned_shard_indices ) + -> + { + delegate; + first_slot; + attesting_power; + operations; + assigned_shard_indices; + }) + (obj5 (req "delegate" Tezos_crypto.Signature.Public_key_hash.encoding) (dft "first_slot" int16 0) (dft "endorsing_power" int16 0) - (dft "operations" (list operation_encoding) [])) + (dft "operations" (list operation_encoding) []) + (dft "assigned_shard_indices" (list int16) [])) let encoding = let open Data_encoding in -- GitLab From bf74c11204f9a97f18e88a6a9af5d51c50238466 Mon Sep 17 00:00:00 2001 From: Gabriel Moise Date: Tue, 25 Nov 2025 16:23:22 +0000 Subject: [PATCH 2/4] Teztale: Add SQL support for DAL shard assignments This commit only wires the SQL path; the archiver integration will be added in following commit(s). --- teztale/bin_teztale_server/exporter.ml | 74 ++++++++++++++++++--- teztale/bin_teztale_server/sql_requests.ml | 16 +++++ teztale/bin_teztale_server/sql_requests.mli | 2 + 3 files changed, 83 insertions(+), 9 deletions(-) diff --git a/teztale/bin_teztale_server/exporter.ml b/teztale/bin_teztale_server/exporter.ml index 50fd55c6d993..af32b638c7e4 100644 --- a/teztale/bin_teztale_server/exporter.ml +++ b/teztale/bin_teztale_server/exporter.ml @@ -150,6 +150,20 @@ let select_missing_blocks conf db_pool boundaries = Int32Map.empty) db_pool +let select_dal_shard_assignments = + Caqti_request.Infix.( + Caqti_type.(t2 int32 int32) + ->* Caqti_type.(t3 int32 Sql_requests.Type.public_key_hash int)) + "SELECT\n\ + \ er.level,\n\ + \ delegates.address,\n\ + \ dsa.shard_index\n\ + FROM endorsing_rights er\n\ + JOIN dal_shard_assignments dsa ON er.id = dsa.endorsing_right\n\ + JOIN delegates ON delegates.id = er.delegate\n\ + WHERE er.level >= $1\n\ + AND er.level <= $2" + let select_blocks conf db_pool boundaries = let block_request = Caqti_request.Infix.( @@ -257,7 +271,10 @@ let select_ops conf db_pool boundaries = let ops = Ops.add delegate - (first_slot, power, Tezos_crypto.Hashed.Operation_hash.Map.empty) + ( first_slot, + power, + Tezos_crypto.Hashed.Operation_hash.Map.empty, + [] (* assigned_shard_indices *) ) ops in Int32Map.add level ops info @@ -272,7 +289,7 @@ let select_ops conf db_pool boundaries = Ops.update delegate (function - | Some (first_slot, power, ops) -> + | Some (first_slot, power, ops, assigned_shard_indices) -> let op = match Tezos_crypto.Hashed.Operation_hash.Map.find_opt op_hash ops @@ -284,7 +301,7 @@ let select_ops conf db_pool boundaries = let ops' = Tezos_crypto.Hashed.Operation_hash.Map.add op_hash op ops in - Some (first_slot, power, ops') + Some (first_slot, power, ops', assigned_shard_indices) | None -> None) ops in @@ -305,7 +322,7 @@ let select_ops conf db_pool boundaries = Ops.update delegate (function - | Some (first_slot, power, ops) -> + | Some (first_slot, power, ops, assigned_shard_indices) -> let op = match Tezos_crypto.Hashed.Operation_hash.Map.find_opt op_hash ops @@ -318,12 +335,43 @@ let select_ops conf db_pool boundaries = let ops' = Tezos_crypto.Hashed.Operation_hash.Map.add op_hash op ops in - Some (first_slot, power, ops') + Some (first_slot, power, ops', assigned_shard_indices) | None -> None) ops in Int32Map.add level ops info in + let cb_shards (level, delegate, shard_index) info = + let ops = + match Int32Map.find_opt level info with Some m -> m | None -> Ops.empty + in + let ops = + Ops.update + delegate + (function + | Some (first_slot, power, op_map, assigned_shard_indices) -> + let assigned_shard_indices = + if List.mem shard_index assigned_shard_indices then + assigned_shard_indices + else shard_index :: assigned_shard_indices + in + Some (first_slot, power, op_map, assigned_shard_indices) + | None -> + let logger = Log.logger () in + Lib_teztale_base.Log.warning logger (fun () -> + Format.asprintf + "DAL: received shard index %d for delegate %a at level \ + %ld, but no rights were recorded for that delegate, level \ + pair. Ignoring." + shard_index + Tezos_crypto.Signature.Public_key_hash.pp + delegate + level) ; + None) + ops + in + Int32Map.add level ops info + in let* out = Caqti_lwt_unix.Pool.use (fun (module Db : Caqti_lwt.CONNECTION) -> @@ -338,10 +386,17 @@ let select_ops conf db_pool boundaries = Db.fold q_included cb_included boundaries out) db_pool in + let* out = + Caqti_lwt_unix.Pool.use + (fun (module Db : Caqti_lwt.CONNECTION) -> + maybe_with_metrics conf "select_operations_reception" @@ fun () -> + Db.fold q_received cb_received boundaries out) + db_pool + in Caqti_lwt_unix.Pool.use (fun (module Db : Caqti_lwt.CONNECTION) -> - maybe_with_metrics conf "select_operations_reception" @@ fun () -> - Db.fold q_received cb_received boundaries out) + maybe_with_metrics conf "select_dal_shard_assignments" @@ fun () -> + Db.fold select_dal_shard_assignments cb_shards boundaries out) db_pool let translate_ops info = @@ -363,14 +418,15 @@ let translate_ops info = Int32Map.map (fun info -> Tezos_crypto.Signature.Public_key_hash.Map.fold - (fun pkh (first_slot, power, pkh_ops) acc -> + (fun pkh (first_slot, power, pkh_ops, assigned_shard_indices) acc -> Lib_teztale_base.Data.Delegate_operations. { delegate = pkh; first_slot; attesting_power = power; operations = translate pkh_ops; - assigned_shard_indices = []; + assigned_shard_indices = + List.sort_uniq compare assigned_shard_indices; } :: acc) info diff --git a/teztale/bin_teztale_server/sql_requests.ml b/teztale/bin_teztale_server/sql_requests.ml index 87946cfa9da2..a066e7d84bc6 100644 --- a/teztale/bin_teztale_server/sql_requests.ml +++ b/teztale/bin_teztale_server/sql_requests.ml @@ -117,6 +117,14 @@ let create_missing_blocks = \ FOREIGN KEY (baker) REFERENCES delegates(id),\n\ \ UNIQUE (source, level, round))" +let create_dal_shard_assignments = + "CREATE TABLE IF NOT EXISTS dal_shard_assignments(\n\ + \ id $(PRIMARY_INCREMENTING_INT) PRIMARY KEY,\n\ + \ endorsing_right $(PRIMARY_INCREMENTING_INT_REF) NOT NULL,\n\ + \ shard_index INTEGER NOT NULL,\n\ + \ FOREIGN KEY (endorsing_right) REFERENCES endorsing_rights(id),\n\ + \ UNIQUE (endorsing_right, shard_index))" + module Mutex = struct let delegates = Lwt_mutex.create () @@ -137,6 +145,8 @@ module Mutex = struct let cycles = Lwt_mutex.create () let missing_blocks = Lwt_mutex.create () + + let dal_shard_assignments = Lwt_mutex.create () end let create_endorsing_rights_level_idx = @@ -167,6 +177,10 @@ let create_cycles_level_idx = let create_missing_blocks_level_idx = "CREATE INDEX IF NOT EXISTS missing_blocks_level_idx ON missing_blocks(level)" +let create_dal_shard_assignments_endorsing_right_idx = + "CREATE INDEX IF NOT EXISTS dal_shard_assignments_endorsing_right_idx ON \ + dal_shard_assignments(endorsing_right)" + let create_tables = [ create_delegates; @@ -179,6 +193,7 @@ let create_tables = create_endorsing_rights; create_cycles; create_missing_blocks; + create_dal_shard_assignments; create_endorsing_rights_level_idx; create_blocks_level_idx; create_operations_level_idx; @@ -187,6 +202,7 @@ let create_tables = create_operations_inclusion_operation_idx; create_cycles_level_idx; create_missing_blocks_level_idx; + create_dal_shard_assignments_endorsing_right_idx; ] let alter_blocks = diff --git a/teztale/bin_teztale_server/sql_requests.mli b/teztale/bin_teztale_server/sql_requests.mli index e9293af51ee3..25926af5ecef 100644 --- a/teztale/bin_teztale_server/sql_requests.mli +++ b/teztale/bin_teztale_server/sql_requests.mli @@ -41,6 +41,8 @@ module Mutex : sig val cycles : Lwt_mutex.t val missing_blocks : Lwt_mutex.t + + val dal_shard_assignments : Lwt_mutex.t end val create_tables : string list -- GitLab From 0799ad1da087d2271c3fec544534247d1dadd8dd Mon Sep 17 00:00:00 2001 From: Gabriel Moise Date: Tue, 25 Nov 2025 16:59:47 +0000 Subject: [PATCH 3/4] Teztale: Add DAL shard assignment wire encoding This wire type complements Delegate_operations.assigned_shard_indices, which remains the aggregated per-level view used in exported data. --- teztale/bin_teztale_server/sql_requests.ml | 16 +++++ teztale/bin_teztale_server/sql_requests.mli | 6 ++ .../bin_teztale_server/teztale_server_main.ml | 64 ++++++++++++++++++- teztale/lib_teztale_base/data.ml | 22 +++++++ 4 files changed, 107 insertions(+), 1 deletion(-) diff --git a/teztale/bin_teztale_server/sql_requests.ml b/teztale/bin_teztale_server/sql_requests.ml index a066e7d84bc6..dd7d54207e77 100644 --- a/teztale/bin_teztale_server/sql_requests.ml +++ b/teztale/bin_teztale_server/sql_requests.ml @@ -415,5 +415,21 @@ let insert_received_block = COALESCE(blocks_reception.validation_timestamp, \ excluded.validation_timestamp)" +let insert_dal_shard_assignment = + Caqti_request.Infix.( + Caqti_type.( + t3 + (* $1 level *) int32 + (* $2 delegate *) Type.public_key_hash + (* $3 shard_index *) int + ->. unit)) + "INSERT INTO dal_shard_assignments (endorsing_right, shard_index)\n\ + SELECT er.id AS endorsing_right, $3 AS shard_index\n\ + FROM endorsing_rights er\n\ + JOIN delegates ON er.delegate = delegates.id\n\ + WHERE er.level = $1\n\ + AND delegates.address = $2\n\ + ON CONFLICT DO NOTHING" + let maybe_with_metrics (c : Config.t) (name : string) (f : unit -> 'a Lwt.t) = if c.with_metrics then Metrics.sql name f else f () diff --git a/teztale/bin_teztale_server/sql_requests.mli b/teztale/bin_teztale_server/sql_requests.mli index 25926af5ecef..5d8dbe93f534 100644 --- a/teztale/bin_teztale_server/sql_requests.mli +++ b/teztale/bin_teztale_server/sql_requests.mli @@ -122,4 +122,10 @@ val insert_received_block : [`Zero] ) Caqti_request.t +val insert_dal_shard_assignment : + ( int32 * Tezos_crypto.Signature.public_key_hash * int, + unit, + [`Zero] ) + Caqti_request.t + val maybe_with_metrics : Config.t -> string -> (unit -> 'a Lwt.t) -> 'a Lwt.t diff --git a/teztale/bin_teztale_server/teztale_server_main.ml b/teztale/bin_teztale_server/teztale_server_main.ml index fc21d2ffc3a8..bf1b9a9ade5f 100644 --- a/teztale/bin_teztale_server/teztale_server_main.ml +++ b/teztale/bin_teztale_server/teztale_server_main.ml @@ -897,6 +897,20 @@ let import_callback ~logger conf db_pool g data = (level, first_slot, attesting_power, delegate)) data.Lib_teztale_base.Data.delegate_operations in + (* DAL shard assignments *) + let* () = + Tezos_lwt_result_stdlib.Lwtreslib.Bare.List.iter_es + (fun Lib_teztale_base.Data.Delegate_operations. + {delegate; assigned_shard_indices; _} + -> + Tezos_lwt_result_stdlib.Lwtreslib.Bare.List.iter_es + (fun shard_index -> + Db.exec + Sql_requests.insert_dal_shard_assignment + (level, delegate, shard_index)) + assigned_shard_indices) + data.Lib_teztale_base.Data.delegate_operations + in (* blocks *) let* () = Tezos_lwt_result_stdlib.Lwtreslib.Bare.List.iter_es @@ -1003,6 +1017,45 @@ let import_callback ~logger conf db_pool g data = ~body:"Level imported" ()) +let dal_shards_callback ~logger conf db_pool g shard_assignments = + let level = Int32.of_string (Re.Group.get g 1) in + let out = + Caqti_lwt_unix.Pool.use + (fun (module Db : Caqti_lwt.CONNECTION) -> + let open Tezos_lwt_result_stdlib.Lwtreslib.Bare.Monad.Lwt_result_syntax in + let* () = + let delegates = + List.map + (fun Lib_teztale_base.Data.Dal.{delegate; _} -> delegate) + shard_assignments + in + may_insert_delegates (module Db) conf delegates + in + let rows = + List.concat_map + (fun Lib_teztale_base.Data.Dal.{delegate; assigned_shard_indices} -> + List.map + (fun shard_index -> (level, delegate, shard_index)) + assigned_shard_indices) + shard_assignments + in + maybe_with_metrics conf "insert_dal_shard_assignments" @@ fun () -> + without_cache + Sql_requests.Mutex.dal_shard_assignments + Sql_requests.insert_dal_shard_assignment + (module Db) + conf + rows) + db_pool + in + with_caqti_error ~logger out (fun () -> + Cohttp_lwt_unix.Server.respond_string + ~headers: + (Cohttp.Header.init_with "content-type" "text/plain; charset=UTF-8") + ~status:`OK + ~body:"DAL shard assignments" + ()) + let extract_boundaries g = let min = Re.Group.get g 1 in let max = Re.Group.get g 2 in @@ -1016,7 +1069,9 @@ let extract_boundaries g = - //mempool Used by archiver to send data about consensus operations for a given level. - //import - Use by archiver to import past data recorded locally. + Used by archiver to import past data recorded locally. + - //dal_shards + Used by archiver to send DAL shard assignments per delegate for a given level. - /timestamp/ Get the levels (if any) before and after a given timestamp. - /ping @@ -1082,6 +1137,13 @@ let routes : Lib_teztale_base.Data.encoding body (import_callback ~logger conf db_pool g)) ); + ( Re.seq [Re.str "/"; Re.group (Re.rep1 Re.digit); Re.str "/dal_shards"], + fun g ~logger ~conf ~admins:_ ~users db_pool header meth body -> + post_only_endpoint !users header meth (fun _source -> + with_data + Lib_teztale_base.Data.Dal.shard_assignments_encoding + body + (dal_shards_callback ~logger conf db_pool g)) ); ( Re.seq [Re.str "/timestamp/"; Re.group (Re.rep1 Re.digit)], fun g ~logger ~conf:_ ~admins:_ ~users:_ db_pool _header meth _body -> get_only_endpoint meth (fun () -> diff --git a/teztale/lib_teztale_base/data.ml b/teztale/lib_teztale_base/data.ml index de6910e99cc3..779a6bb12dc3 100644 --- a/teztale/lib_teztale_base/data.ml +++ b/teztale/lib_teztale_base/data.ml @@ -391,3 +391,25 @@ module Archiver = struct (dft "preendorsements" (list Consensus_ops.block_op_encoding) []) (dft "baking_rights" (list baking_right_encoding) []))) end + +module Dal = struct + type shard_assignment = { + delegate : Tezos_crypto.Signature.public_key_hash; + assigned_shard_indices : int list; + } + + type shard_assignments = shard_assignment list + + let shard_assignment_encoding = + let open Data_encoding in + conv + (fun {delegate; assigned_shard_indices} -> + (delegate, assigned_shard_indices)) + (fun (delegate, assigned_shard_indices) -> + {delegate; assigned_shard_indices}) + (obj2 + (req "delegate" Tezos_crypto.Signature.Public_key_hash.encoding) + (dft "assigned_shard_indices" (list int16) [])) + + let shard_assignments_encoding = Data_encoding.list shard_assignment_encoding +end -- GitLab From 211cc0ae56417027c79e9d0960e769b2ddcda755 Mon Sep 17 00:00:00 2001 From: Gabriel Moise Date: Mon, 1 Dec 2025 13:18:04 +0000 Subject: [PATCH 4/4] Teztale: Dump_month: Update script with new table --- teztale/scripts/db/dump_month.sh | 54 ++++++++++++++++++++++---------- 1 file changed, 38 insertions(+), 16 deletions(-) mode change 100644 => 100755 teztale/scripts/db/dump_month.sh diff --git a/teztale/scripts/db/dump_month.sh b/teztale/scripts/db/dump_month.sh old mode 100644 new mode 100755 index d164382e32fa..d7df29597338 --- a/teztale/scripts/db/dump_month.sh +++ b/teztale/scripts/db/dump_month.sh @@ -30,8 +30,15 @@ mkdir -p "$OUTPUT_DIR" DUMP_FILE_PREFIX="teztale_${DATABASE}_dump" # Convert date to start and end timestamps (epoch) -START_TS=$(date -d "$DATE-01" +%s) -END_TS=$(date -d "$DATE-01 +1 month" +%s) +# Use GNU date if available (gdate on macOS), otherwise plain date +if command -v gdate >/dev/null 2>&1; then + DATE_BIN=gdate +else + DATE_BIN=date +fi + +START_TS=$("$DATE_BIN" -d "$DATE-01" +%s) +END_TS=$("$DATE_BIN" -d "$DATE-01 +1 month" +%s) echo "Dumping data from timestamp $START_TS to $END_TS [$DATABASE]" @@ -45,10 +52,11 @@ DROP VIEW IF EXISTS monthly_view_blocks_reception CASCADE; DROP VIEW IF EXISTS monthly_view_operations CASCADE; DROP VIEW IF EXISTS monthly_view_operations_reception CASCADE; DROP VIEW IF EXISTS monthly_view_operations_inclusion CASCADE; +DROP VIEW IF EXISTS monthly_view_endorsing_rights CASCADE; +DROP VIEW IF EXISTS monthly_view_dal_shard_assignments CASCADE; DROP VIEW IF EXISTS monthly_view_delegates CASCADE; DROP VIEW IF EXISTS monthly_view_cycles CASCADE; DROP VIEW IF EXISTS monthly_view_missing_blocks CASCADE; -DROP VIEW IF EXISTS monthly_view_endorsing_rights CASCADE; DROP VIEW IF EXISTS monthly_view_nodes CASCADE; -- Create view for blocks in the specified month @@ -78,6 +86,17 @@ CREATE VIEW monthly_view_operations_inclusion AS SELECT ops_inclusion.* FROM operations_inclusion ops_inclusion INNER JOIN monthly_view_blocks mb ON ops_inclusion.block = mb.id; +-- Create view for related endorsing_rights +CREATE VIEW monthly_view_endorsing_rights AS +SELECT er.* FROM endorsing_rights er +WHERE er.level >= (SELECT MIN(level) FROM monthly_view_blocks) +AND er.level <= (SELECT MAX(level) FROM monthly_view_blocks); + +-- Create view for related DAL shard assignments +CREATE VIEW monthly_view_dal_shard_assignments AS +SELECT dsa.* FROM dal_shard_assignments dsa +JOIN monthly_view_endorsing_rights er ON er.id = dsa.endorsing_right; + -- Create view for related delegates CREATE VIEW monthly_view_delegates AS SELECT DISTINCT d.* FROM delegates d @@ -85,6 +104,8 @@ WHERE d.id IN ( SELECT baker FROM monthly_view_blocks UNION SELECT endorser FROM monthly_view_operations + UNION + SELECT delegate FROM monthly_view_endorsing_rights ); -- Create view for related cycles @@ -100,12 +121,6 @@ SELECT mb.* FROM missing_blocks mb WHERE mb.level >= (SELECT MIN(level) FROM monthly_view_blocks) AND mb.level <= (SELECT MAX(level) FROM monthly_view_blocks); --- Create view for related endorsing_rights -CREATE VIEW monthly_view_endorsing_rights AS -SELECT er.* FROM endorsing_rights er -WHERE er.level >= (SELECT MIN(level) FROM monthly_view_blocks) -AND er.level <= (SELECT MAX(level) FROM monthly_view_blocks); - -- Create view for related nodes CREATE VIEW monthly_view_nodes AS SELECT DISTINCT n.* FROM nodes n @@ -124,10 +139,11 @@ SELECT (SELECT COUNT(*) FROM monthly_view_blocks_reception) as block_reception_count, (SELECT COUNT(*) FROM monthly_view_operations) as operation_count, (SELECT COUNT(*) FROM monthly_view_operations_reception) as operation_reception_count, + (SELECT COUNT(*) FROM monthly_view_endorsing_rights) as endorsing_rights_count, + (SELECT COUNT(*) FROM monthly_view_dal_shard_assignments) as dal_shard_assignments_count, (SELECT COUNT(*) FROM monthly_view_delegates) as delegate_count, (SELECT COUNT(*) FROM monthly_view_cycles) as cycle_count, (SELECT COUNT(*) FROM monthly_view_missing_blocks) as missing_block_count, - (SELECT COUNT(*) FROM monthly_view_endorsing_rights) as endorsing_rights_count, (SELECT COUNT(*) FROM monthly_view_nodes) as nodes_count; COMMIT; @@ -160,6 +176,12 @@ CREATE TABLE temp_monthly_view_operations_reception AS TABLE monthly_view_operat DROP TABLE IF EXISTS temp_monthly_view_operations_inclusion; CREATE TABLE temp_monthly_view_operations_inclusion AS TABLE monthly_view_operations_inclusion; +DROP TABLE IF EXISTS temp_monthly_view_endorsing_rights; +CREATE TABLE temp_monthly_view_endorsing_rights AS TABLE monthly_view_endorsing_rights; + +DROP TABLE IF EXISTS temp_monthly_view_dal_shard_assignments; +CREATE TABLE temp_monthly_view_dal_shard_assignments AS TABLE monthly_view_dal_shard_assignments; + DROP TABLE IF EXISTS temp_monthly_view_delegates; CREATE TABLE temp_monthly_view_delegates AS TABLE monthly_view_delegates; @@ -169,9 +191,6 @@ CREATE TABLE temp_monthly_view_cycles AS TABLE monthly_view_cycles; DROP TABLE IF EXISTS temp_monthly_view_missing_blocks; CREATE TABLE temp_monthly_view_missing_blocks AS TABLE monthly_view_missing_blocks; -DROP TABLE IF EXISTS temp_monthly_view_endorsing_rights; -CREATE TABLE temp_monthly_view_endorsing_rights AS TABLE monthly_view_endorsing_rights; - DROP TABLE IF EXISTS temp_monthly_view_nodes; CREATE TABLE temp_monthly_view_nodes AS TABLE monthly_view_nodes; @@ -203,10 +222,11 @@ pg_dump \ --table=temp_monthly_view_operations \ --table=temp_monthly_view_operations_reception \ --table=temp_monthly_view_operations_inclusion \ + --table=temp_monthly_view_endorsing_rights \ + --table=temp_monthly_view_dal_shard_assignments \ --table=temp_monthly_view_delegates \ --table=temp_monthly_view_cycles \ --table=temp_monthly_view_missing_blocks \ - --table=temp_monthly_view_endorsing_rights \ --table=temp_monthly_view_nodes exit_code_3=$? @@ -218,10 +238,11 @@ DROP VIEW IF EXISTS monthly_view_blocks_reception CASCADE; DROP VIEW IF EXISTS monthly_view_operations CASCADE; DROP VIEW IF EXISTS monthly_view_operations_reception CASCADE; DROP VIEW IF EXISTS monthly_view_operations_inclusion CASCADE; +DROP VIEW IF EXISTS monthly_view_endorsing_rights CASCADE; +DROP VIEW IF EXISTS monthly_view_dal_shard_assignments CASCADE; DROP VIEW IF EXISTS monthly_view_delegates CASCADE; DROP VIEW IF EXISTS monthly_view_cycles CASCADE; DROP VIEW IF EXISTS monthly_view_missing_blocks CASCADE; -DROP VIEW IF EXISTS monthly_view_endorsing_rights CASCADE; DROP VIEW IF EXISTS monthly_view_nodes CASCADE; DROP VIEW IF EXISTS monthly_view_data_summary; @@ -230,10 +251,11 @@ DROP TABLE IF EXISTS temp_monthly_view_blocks_reception; DROP TABLE IF EXISTS temp_monthly_view_operations; DROP TABLE IF EXISTS temp_monthly_view_operations_reception; DROP TABLE IF EXISTS temp_monthly_view_operations_inclusion; +DROP TABLE IF EXISTS temp_monthly_view_endorsing_rights; +DROP TABLE IF EXISTS temp_monthly_view_dal_shard_assignments; DROP TABLE IF EXISTS temp_monthly_view_delegates; DROP TABLE IF EXISTS temp_monthly_view_cycles; DROP TABLE IF EXISTS temp_monthly_view_missing_blocks; -DROP TABLE IF EXISTS temp_monthly_view_endorsing_rights; DROP TABLE IF EXISTS temp_monthly_view_nodes; EOF -- GitLab