|
|
Linha 1: |
Linha 1: |
| Tabelas da base corrente <code>DL05s_main</code>. Documentação gerada por recursos do "psql" e [https://tableconvert.com/markdown-to-mediawiki tableconvert.com/markdown-to-mediawiki].
| | RESOLVIDO! Foi para [[a4a:Convenções/Dados/SQL]] |
| | |
| Ver também [[Usuário:Peter/Modelos endereço]]
| |
| | |
| == Table <code>optim.jurisdiction</code> ==
| |
| Jurisdições nacionais e locais, sem geometrias (ver tabela complementar _geom).
| |
| | |
| {| class="wikitable"
| |
| ! Column !! Type !! Nullable !! Description
| |
| |-
| |
| | osm_id || bigint || not null || Relation identifier in OpenStreetMap.
| |
| |-
| |
| | jurisd_base_id || integer || not null || ISO3166-1-numeric COUNTRY ID (e.g. Brazil is 76) or negative for non-iso (ex. oceans).
| |
| |-
| |
| | jurisd_local_id || integer || not null || Numeric official ID like IBGE_ID of BR jurisdiction. For example ACRE is 12 and its cities are {1200013, 1200054,etc}.
| |
| |-
| |
| | parent_id || bigint || || osm_id of top admin_level.
| |
| |-
| |
| | admin_level || smallint || not null || OSM convention for admin_level tag in country.
| |
| |-
| |
| | name || text || not null || Name of jurisdiction
| |
| |-
| |
| | parent_abbrev || text || not null || Abbreviation of parent name.
| |
| |-
| |
| | abbrev || text || || Name abbreviation.
| |
| |-
| |
| | wikidata_id || bigint || || wikidata identifier without Q prefix.
| |
| |-
| |
| | lexlabel || text || not null || Cache from name; e.g. sao.paulo.
| |
| |-
| |
| | isolabel_ext || text || not null || Cache from parent_abbrev (ISO) and name (camel case); e.g. BR-SP-SaoPaulo.
| |
| |-
| |
| | ddd || integer || || Direct distance dialing.
| |
| |-
| |
| | housenumber_system_type || text || || Housenumber system.
| |
| |-
| |
| | lex_urn || text || || Housenumber system law.
| |
| |-
| |
| | info || jsonb || || Others information.
| |
| |-
| |
| | name_en || text || || City name in english.
| |
| |-
| |
| | isolevel || integer || || 1=country, 2=state, 3=mun
| |
| |-
| |
| | ne_country_id || integer || || NaturalEarthData country gid.
| |
| |-
| |
| | int_country_id || integer || || One byte identifier, adopted for internal use. Shorter than ISO and NaturalEarthData.
| |
| |}
| |
| <small><pre>
| |
| Indexes:
| |
| "jurisdiction_pkey" PRIMARY KEY, btree (osm_id)
| |
| "jurisdiction_isolabel_ext_idx1" btree (isolabel_ext)
| |
| "jurisdiction_isolabel_ext_key" UNIQUE CONSTRAINT, btree (isolabel_ext)
| |
| "jurisdiction_jurisd_base_id_jurisd_local_id_key" UNIQUE CONSTRAINT, btree (jurisd_base_id, jurisd_local_id)
| |
| "jurisdiction_jurisd_base_id_parent_abbrev_abbrev_key" UNIQUE CONSTRAINT, btree (jurisd_base_id, parent_abbrev, abbrev)
| |
| "jurisdiction_jurisd_base_id_parent_abbrev_lexlabel_key" UNIQUE CONSTRAINT, btree (jurisd_base_id, parent_abbrev, lexlabel)
| |
| "jurisdiction_jurisd_base_id_parent_abbrev_name_key" UNIQUE CONSTRAINT, btree (jurisd_base_id, parent_abbrev, name)
| |
| "jurisdiction_wikidata_id_key" UNIQUE CONSTRAINT, btree (wikidata_id)
| |
| Check constraints:
| |
| "jurisdiction_admin_level_check" CHECK (admin_level > 0 AND admin_level < 100)
| |
| "jurisdiction_name_check" CHECK (length(name) < 60)
| |
| Foreign-key constraints:
| |
| "jurisdiction_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES optim.jurisdiction(osm_id)
| |
| Referenced by:
| |
| TABLE "optim.donor" CONSTRAINT "donor_scope_osm_id_fkey" FOREIGN KEY (scope_osm_id) REFERENCES optim.jurisdiction(osm_id)
| |
| TABLE "optim.jurisdiction" CONSTRAINT "jurisdiction_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES optim.jurisdiction(osm_id)
| |
| Access method: heap
| |
| </pre></small>
| |
| | |
| === Queries ===
| |
| Queries que geram reports e sugestão de análise das views próprias.
| |
| <syntaxhighlight lang="sql">
| |
| -- R0a
| |
| select '* '||vw_name from optim.admin_views_dependency_summary
| |
| where ref_tab_name='optim.jurisdiction' order by 1
| |
| ;
| |
| -- R0b
| |
| select schema_name, name, arguments_simplified , return_type
| |
| from doc_UDF_using_a_table('optim.jurisdiction') t order by 1,2,3
| |
| ;
| |
| ...
| |
| </syntaxhighlight>
| |
| | |
| === Reports ===
| |
| '''R0a'''. Views que fazem uso da tabela:
| |
| * api.jurisdiction: reproduz a tabela na API.
| |
| * api.stats_donated_packcomponent: fornece estatísticas de pacotes doados, organizada por jurisdição.
| |
| * optim.jurisdiction_lexlabel: reformata isolabel_ext como URN LEX.
| |
| * optim.vw01donorevidencecmd:
| |
| * optim.vw01full_donated_packtpl: pacotes doados em join completo.
| |
| * optim.vw01full_jurisdiction_geom: jurisdição completa, com geometria.
| |
| * optim.vw01int_country_id: (lixo) lista o ID interno de país.
| |
| * optim.vw02generate_list: gera JSON com árvore de países, com optim.vw01generate_list.
| |
| * optim.vw02publication: metadados para ato de publicação de um pacote.
| |
| * optim.vwjurisdiction_synonym: lista de-para de todos os sinônimos válidos de uma jurisdição (CEP, abreviação, URN LEX etc.).
| |
| * osmc.jurisdictions_select: JSON para interface de seletor de jurisdição.
| |
| * public.qwerty: (resgatar para optim) gerador de comando bash "curl" para criar CSVs de todas as jurisdições.
| |
| | |
| '''R0b'''. Funções que fazem uso da tabela:
| |
| {| class="wikitable"
| |
| ! schema_name !! name !! arguments_simplified !! return_type
| |
| |-
| |
| | api || jurisdiction_autocomplete || {text} || jsonb
| |
| |-
| |
| | api || osmcode_decode_postal || {text,text} || jsonb
| |
| |-
| |
| | api || osmcode_encode || {text,integer} || jsonb
| |
| |-
| |
| | api || osmcode_encode_sci || {text,integer} || jsonb
| |
| |-
| |
| | optim || generate_synonym_csv || {text,text} || text
| |
| |-
| |
| | optim || generate_synonym_ref_csv || {text} || text
| |
| |-
| |
| | optim || insert_donor_pack || {text} || text
| |
| |-
| |
| | optim || insert_jurisdpoint || {NULL} || text
| |
| |-
| |
| | osmc || encode_short_code || {text,"bit varying",text,USER-DEFINED,text,integer,text} || record
| |
| |-
| |
| | osmc || l0cover_upsert_co || {NULL} || text
| |
| |}
| |
| | |
| == Table <code>optim.jurisdiction_geom</code> ==
| |
| Geometrias de [[#optim.jurisdiction]]. Geometrias das jurisdições nacionais e locais.
| |
| {| class="wikitable"
| |
| ! Column !! Type !! Nullable !! Description
| |
| |-
| |
| | osm_id || bigint || not null || Relation identifier in OpenStreetMap.
| |
| |-
| |
| | isolabel_ext || text || not null || ISO 3166-1 alpha-2 code and name (camel case); e.g. BR-SP-SaoPaulo.
| |
| |-
| |
| | geom || geometry(Geometry,4326) || || Geometry for osm_id identifier
| |
| |-
| |
| | geom_svg || geometry(Geometry,4326) || || Simplified geometry version to use in svg interface.
| |
| |-
| |
| | kx_ghs1_intersects || text[] || ||
| |
| |-
| |
| | kx_ghs2_intersects || text[] || ||
| |
| |}
| |
| <small><pre>
| |
| Indexes:
| |
| "jurisdiction_geom_pkey" PRIMARY KEY, btree (osm_id)
| |
| "jurisdiction_geom_isolabel_ext_key" UNIQUE CONSTRAINT, btree (isolabel_ext)
| |
| "optim_jurisdiction_geom_idx1" gist (geom)
| |
| "optim_jurisdiction_geom_isolabel_ext_idx1" btree (isolabel_ext)
| |
| "optim_jurisdiction_geom_svg_idx1" gist (geom_svg)
| |
| Access method: heap
| |
| </pre></small>
| |
| | |
| == Table <code>osmc.coverage</code> ==
| |
| Coberturas nacionais e de suas jurisdições. As geometrias nacionais são expressas em LatLong, as locais conforme sua projeção oficial.
| |
| | |
| Ingestão: arquivos "coverage.csv" do git. Função ou make para construção?? <br/> Exemplos: [https://git.osm.codes/BR_new/blob/main/data/coverage.csv BR_new/data/coverage.csv], [https://git.osm.codes/CO_new/blob/main/data/coverage.csv CO_new/data/coverage.csv]. As colunas "status" e "cover" são copiadas em "status" e "kx_prefix".
| |
| | |
| {| class="wikitable"
| |
| ! Column !! Type !! Default !! Description | |
| |-
| |
| | cbits || bit varying || || Coverage cell (hierarchical) identifier.
| |
| |-
| |
| | isolabel_ext || text || || Jurisdiction identifier. ISO 3166-2 code extended for subjurisdiction name (camel case); e.g. BR-SP-SaoPaulo.
| |
| |-
| |
| | cindex || text || || First digit of the coverage cell, a prefix in base 32nvu. Used only case is_country=false.
| |
| |-
| |
| | bbox || integer[] || || Coverage cell BBOX.
| |
| |-
| |
| | status || smallint || 0 || Coverage status. Convention: 0: generated, 1: revised, 2: homologated.
| |
| |-
| |
| | is_country || boolean || false || True if it is a cell of national coverage.
| |
| |-
| |
| | is_contained || boolean || false || True if it is a cell contained in the jurisdiction. Must validate.
| |
| |-
| |
| | is_overlay || boolean || false || True if it is an overlay cell.
| |
| |-
| |
| | kx_prefix || text || || Coverage cell prefix in base 32nvu.
| |
| |-
| |
| | geom || geometry || || Coverage cell geometry on country's projection (local SRID).
| |
| |-
| |
| | geom_srid4326 || geometry || || Coverage cell geometry on WGS84 LatLong (4326 srid). Used only case is_country=true.
| |
| |}
| |
| | |
| <small><pre>
| |
| Indexes:
| |
| "osm_coverage_cbits10true_idx" btree ((cbits::bit(10))) WHERE is_country IS TRUE
| |
| "osm_coverage_cbits15false_idx" btree ((cbits::bit(14)), isolabel_ext) WHERE is_country IS FALSE
| |
| "osm_coverage_geom4326_idx1" gist (geom_srid4326)
| |
| "osm_coverage_geom_idx1" gist (geom)
| |
| "osm_coverage_isolabel_ext_false_idx" btree (isolabel_ext) WHERE is_country IS FALSE
| |
| "osm_coverage_isolabel_ext_idx1" btree (isolabel_ext)
| |
| "osm_coverage_isolabel_ext_true_idx" btree (isolabel_ext) WHERE is_country IS TRUE
| |
| Check constraints:
| |
| "coverage_status_check" CHECK (status = ANY (ARRAY[0, 1, 2]))
| |
| </pre></small>
| |
| | |
| === Queries ===
| |
| Queries que geram reports e sugestão de análise das views próprias.
| |
| <syntaxhighlight lang="sql">
| |
| -- R0a
| |
| select * from optim.admin_views_dependency_summary where ref_tab_name='osmc.coverage'
| |
| ;
| |
| -- R0b
| |
| select schema_name, name, arguments_simplified , return_type
| |
| from doc_UDF_using_a_table('osmc.cover') t order by 1,2,3
| |
| ;
| |
| | |
| -- R1
| |
| select distinct
| |
| substring(isolabel_ext,1,2) as country,
| |
| natcod.vbit_to_baseh(substring(cbits,1,8),16,true) as cbits_b16h,
| |
| is_country, status,
| |
| count(*) n, count(distinct isolabel_ext) n_jurisds
| |
| from osmc.coverage
| |
| group by 1,2,3,4
| |
| order by 1,2,3 desc,4;
| |
| | |
| -- R2a
| |
| select substring(isolabel_ext,1,2) as country, is_country, is_overlay,
| |
| count(*) n, count(distinct isolabel_ext) n_jurisds, count(distinct cbits) n_cells,
| |
| min(l) l_min, max(l) l_max, round(avg(l),2) l_avg,
| |
| PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY l) l_mdn
| |
| from (select *, round(length(cbits)/4)::int as l from osmc.coverage) t
| |
| group by 1,2,3 order by 1,2 desc,3;
| |
| | |
| -- R2b
| |
| select substring(isolabel_ext,1,2) as country, is_country, is_overlay,
| |
| count(*) n, count(distinct isolabel_ext) n_jurisds,
| |
| count(distinct kx_prefix) n_cells, count(distinct kx_prefix||'.'||cindex::text) n_cells_idx,
| |
| count(distinct kx_prefix||'.'||isolabel_ext) n_cells_jur,
| |
| min(l) l_min, max(l) l_max, round(avg(l),2) l_avg,
| |
| PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY l) l_mdn
| |
| from (select *, length(kx_prefix) as l from osmc.coverage) t
| |
| group by 1,2,3 order by 1,2 desc,3;
| |
| | |
| -- R3
| |
| select isolabel_ext, is_overlay,
| |
| array_agg( natcod.vbit_to_baseh(substring(cbits,11),16,true) order by cbits) cover
| |
| from osmc.coverage
| |
| where isolabel_ext like 'CO-ARA-%'
| |
| group by 1,2 order by 1,2;
| |
| | |
| -- R4. Testar contra qwerty2, que tem comjunto consistente. Aqui apenas CA e CO apresentam parte consistente.
| |
| select substr(isolabel_ext,1,2) as iso, len, round(avg(a),1) area_avg, max(a) area_max, count(*) n
| |
| from (
| |
| select isolabel_ext, length(cbits) len, round(st_area(geom)/10000.0) a, is_contained, is_overlay
| |
| from osmc.coverage where is_contained order by 2 desc,1 | |
| ) t group by 1,2 order by 1,2
| |
| ;
| |
| </syntaxhighlight>
| |
| | |
| === Reports ===
| |
| '''R0a'''. Views que fazem uso desta tabela:
| |
| * osmc.jurisdictions_select
| |
| * osmc.tmpvw10: temporária
| |
| * osmc.tmpvwpoeira: indica "sobras" dos polígonos que não foram aceitas como cobertura válida, por serem menores que 100 metros quadrados.
| |
| | |
| '''R0b'''. Funções que fazem uso desta tabela. (removidas as variantes "EC" e "UY")
| |
| {| class="wikitable"
| |
| ! schema_name !! name !! arguments_simplified !! return_type
| |
| |-
| |
| | api || jurisdiction_coverage || {text,integer} || jsonb
| |
| |-
| |
| | api || jurisdiction_geojson_from_isolabel || {text} || jsonb
| |
| |-
| |
| | api || osmcode_decode_postal || {text,text} || jsonb
| |
| |-
| |
| | api || osmcode_decode_postal_absolute || {text,text} || jsonb
| |
| |-
| |
| | api || osmcode_decode_scientific_absolute || {text,text,integer} || jsonb
| |
| |-
| |
| | api || osmcode_encode || {text,integer} || jsonb
| |
| |-
| |
| | osmc || afa_log_to_hbig || {text,text} || int8
| |
| |-
| |
| | osmc || cellcontainsproperly || {text,text,integer,text,integer} || text
| |
| |-
| |
| | osmc || check_coverage || {text,ARRAY,text,ARRAY,ARRAY,ARRAY,ARRAY,boolean,ARRAY} || record
| |
| |-
| |
| | osmc || cover_child_geometries || {text,text,integer,text,text,USER-DEFINED} || record
| |
| |-
| |
| | osmc || encode_point_brazil || {USER-DEFINED} || text
| |
| |-
| |
| | osmc || encode_point_colombia || {USER-DEFINED} || text
| |
| |-
| |
| | osmc || encode_postal_br || {USER-DEFINED,"double precision",integer,text} || jsonb
| |
| |-
| |
| | osmc || encode_postal_cm || {USER-DEFINED,"double precision",integer,text} || jsonb
| |
| |-
| |
| | osmc || encode_postal_co || {USER-DEFINED,"double precision",integer,text} || jsonb
| |
| |-
| |
| | osmc || encode_scientific_br || {USER-DEFINED,"double precision",integer} || jsonb
| |
| |-
| |
| | osmc || encode_scientific_cm || {USER-DEFINED,"double precision",integer} || jsonb
| |
| |-
| |
| | osmc || encode_scientific_co || {USER-DEFINED,"double precision",integer} || jsonb
| |
| |-
| |
| | osmc || encode_short_code || {text,"bit varying",text,USER-DEFINED,text,integer,text} || record
| |
| |-
| |
| | osmc || generate_cover_csv || {text,text} || text
| |
| |-
| |
| | osmc || l0_upsert || {text,smallint,integer,integer,integer,integer,integer,ARRAY,ARRAY} || text
| |
| |-
| |
| | osmc || l0cover_upsert_br || {NULL} || text
| |
| |-
| |
| | osmc || l0cover_upsert_co || {NULL} || text
| |
| |-
| |
| | osmc || update_coverage_isolevel3 || {text,smallint,ARRAY,ARRAY} || text
| |
| |}
| |
| | |
| '''R1'''. Sumário do conteúdo corrente (final de 2023). Diferencia coberturas nacionais, expressa contagem de células e contagem de jurisdições.
| |
|
| |
| {| class="wikitable"
| |
| ! country !! cbits_b16h !! is_country !! status !! n !! n_jurisds
| |
| |-
| |
| | BR || 13 || t || 1 || 18 || 1
| |
| |-
| |
| | BR || 13 || f || 0 || 72726 || 5570
| |
| |-
| |
| | CM || 1e || t || 1 || 14 || 1
| |
| |-
| |
| | CM || 1e || f || 0 || 2738 || 360
| |
| |-
| |
| | CO || 2a || t || 1 || 16 || 1
| |
| |-
| |
| | CO || 2a || f || 0 || 15244 || 1115
| |
| |-
| |
| | CO || 2a || f || 2 || 22 || 1
| |
| |-
| |
| | EC || 36 || t || 1 || 22 || 1
| |
| |-
| |
| | UY || d6 || t || 1 || 19 || 1
| |
| |-
| |
| | UY || d6 || f || 0 || 1 || 1
| |
| |}
| |
| | |
| '''R2a'''. Tamanho mais frequente (mediana "mdn") da cobertura. Comprimento ''l'' dado em quantidade de dígitos hexadecimais. <br/>PS: ignorando EC e UY, que não estão configurados.
| |
| | |
| {| class="wikitable"
| |
| ! country !! is_country !! is_overlay !! n !! n_jurisds !! l_min !! l_max !! l_avg !! l_mdn
| |
| |-
| |
| | BR || t || f || 18 || 1 || 4 || 4 || 4.00 || 4
| |
| |-
| |
| | BR || f || f || 72565 || 5570 || 5 || 9 || 7.77 || 8
| |
| |-
| |
| | BR || f || t || 161 || 11 || 7 || 9 || 8.11 || 8
| |
| |-
| |
| | CM || t || f || 14 || 1 || 3 || 3 || 3.00 || 3
| |
| |-
| |
| | CM || f || f || 2730 || 360 || 3 || 7 || 5.48 || 5
| |
| |-
| |
| | CM || f || t || 8 || 1 || 7 || 7 || 7.00 || 7
| |
| |-
| |
| | CO || t || f || 16 || 1 || 3 || 3 || 3.00 || 3
| |
| |-
| |
| | CO || f || f || 14165 || 1116 || 4 || 8 || 5.82 || 6
| |
| |-
| |
| | CO || f || t || 1101 || 1091 || 6 || 9 || 7.39 || 8
| |
| |}
| |
| | |
| '''R2b'''. Mesmo que R2a porém usando "kx_prefix" direto. Linhas is_country removidas pois não usam "kx_prefix".
| |
| | |
| {| class="wikitable"
| |
| ! country !! is_ctry !! is_ovly !! n !! n_jurisds !! n_cells !! n_cel_idx !! n_cel_jur !! l_min !! l_max !! l_avg !! l_mdn
| |
| |-
| |
| | BR || f || f || 72565 || 5570 || 40933 || 69557 || 72565 || 2 || 5 || 3.77 || 4
| |
| |-
| |
| | BR || f || t || 161 || 11 || 161 || 161 || 161 || 3 || 5 || 4.11 || 4
| |
| |-
| |
| | CM || f || f || 2730 || 360 || 1411 || 2302 || 2626 || 2 || 5 || 3.50 || 3
| |
| |-
| |
| | CM || f || t || 8 || 1 || 4 || 4 || 4 || 5 || 5 || 5.00 || 5
| |
| |-
| |
| | CO || f || f || 14165 || 1116 || 7552 || 13530 || 14165 || 2 || 5 || 3.82 || 4
| |
| |-
| |
| | CO || f || t || 1101 || 1091 || 1100 || 1100 || 1101 || 4 || 6 || 4.70 || 5
| |
| |}
| |
| | |
| '''R3'''. Para reproduzir a planilha CSV precisamos agrupar cbits e converte-los em base16h. Exemplo, municípios de "CO-ARA". As coberturas-overlay ainda não aparecem no git, e aqui apenas a célula de um ponto é utilizada.
| |
| | |
| {| class="wikitable"
| |
| ! isolabel_ext !! is_overlay !! n !! cover
| |
| |-
| |
| | CO-ARA-Arauca || f || 14 || {cd4,cd5,cd6,cd7,e2a,e2e,e2f,e80,e81,e82,e83,e84,e85,e86}
| |
| |-
| |
| | CO-ARA-Arauca || t || 1 || {cd7fG}
| |
| |-
| |
| | CO-ARA-Arauquita || f || 9 || {c7e,c7f,cd0,cd1,cd2,cd3,cd4,cd5,cd6}
| |
| |-
| |
| | CO-ARA-Arauquita || t || 1 || {cd3cQ}
| |
| |-
| |
| | CO-ARA-CravoNorte || f || 13 || {e28,e2a,e2b,e2c,e2d,e2e,e2f,e38,e3a,e80,e81,e84,e85}
| |
| |-
| |
| | CO-ARA-CravoNorte || t || 1 || {e2b5Q}
| |
| |-
| |
| | CO-ARA-Fortul || f || 5 || {c6f,cc5,cd0,cd1,cd2}
| |
| |-
| |
| | CO-ARA-Fortul || t || 1 || {cd0bG}
| |
| |-
| |
| | CO-ARA-PuertoRondon || f || 9 || {c7d,c7e,c7f,cd1,cd4,cd5,e28,e2a,e80}
| |
| |-
| |
| | CO-ARA-PuertoRondon || t || 1 || {c7e4Q}
| |
| |-
| |
| | CO-ARA-Saravena || f || 4 || {cc5,cc7,cd0,cd2}
| |
| |-
| |
| | CO-ARA-Saravena || t || 1 || {cd22Q}
| |
| |-
| |
| | CO-ARA-Tame || f || 13 || {c6c,c6d,c6e,c6f,c78,c79,c7a,c7b,c7e,cc5,cd0,cd1,cd4}
| |
| |-
| |
| | CO-ARA-Tame || t || 1 || {c7acQ}
| |
| |}
| |
| | |
| == Outros esquemas e tabelas ==
| |
| | |
| === download.redirects ===
| |
| | |
| {| class="wikitable"
| |
| ! Column !! Type !! Nullable !! Description
| |
| |-
| |
| | donor_id || int || || Donor identifier in optim.donor
| |
| |-
| |
| | filename_original || text || || (not used)
| |
| |-
| |
| | package_path || text || || (not used)
| |
| |-
| |
| | hashedfname || text || not null || The filenaname, based on SHA256.extension
| |
| |-
| |
| | hashedfnameuri || text || || The real URL for file download of the file with the expected SHA256.
| |
| |}
| |
| <small><pre>
| |
| Indexes:
| |
| "redirects_pkey" PRIMARY KEY, btree (hashedfname)
| |
| -- correto seria unique substring(hashedfname,1,64) para excluir a extension da unicidade
| |
| "redirects_hashedfname_hashedfnameuri_key" UNIQUE CONSTRAINT, btree (hashedfname, hashedfnameuri)
| |
| Check constraints:
| |
| "redirects_hashedfname_check" CHECK (hashedfname ~ '^[0-9a-f]{64,64}\.[a-z0-9]+$'::text)
| |
| </pre></small>
| |
| | |
| Tem relação com a API do ''endpoint'' <code>http://DL.digital-guard.org</code>.
| |
| onde faz UNION com a tabela <code>optim.donated_packcomponent_cloudcontrol</code>. No servidor, localmente faz <code>curl http://127.0.0.1:3105/redirects</code>. Na prática é chamado por PHP, ver <code>/var/www/dl.digital-guard.org/dl.php</code>.
| |
| <syntaxhighlight lang="sql">
| |
| -- R0. Testa como a API fornece
| |
| select * from api.redirects where fhash like 'e5b%';
| |
| | |
| CREATE VIEW optim.vw_download_redirects AS
| |
| select substring(hashedfname,1,7) as short_id, donor_id, hashedfname, hashedfnameuri from download.redirects
| |
| ;
| |
| -- R1. Gera testes bash dos downloads. Demora, já são mais de 700
| |
| COPY (
| |
| SELECT 'url_'||short_id ||'="' || hashedfnameuri || '"' FROM optim.vw_download_redirects
| |
| UNION ALL
| |
| SELECT format(
| |
| 'wget --spider "$url_%s" 2>/dev/null && echo "url_%1$s exists" || echo "!url_%1$s does not exist: $url1"',
| |
| short_id
| |
| )
| |
| FROM optim.vw_download_redirects
| |
| ) to '/tmp/dl_urls_test.sh'; -- sh /tmp/dl_urls_test.sh
| |
| </syntaxhighlight>
| |
| | |
| == Outros lembretes ==
| |
| | |
| <pre>
| |
| CREATE VIEW optim.admin_views_dependency AS
| |
| -- https://stackoverflow.com/a/69473861/287948
| |
| select
| |
| u.view_schema schema_name,
| |
| u.view_name,
| |
| u.table_schema referenced_table_schema,
| |
| u.table_name referenced_table_name,
| |
| v.view_definition
| |
| from information_schema.view_table_usage u
| |
| join information_schema.views v on u.view_schema = v.table_schema
| |
| and u.view_name = v.table_name
| |
| where u.table_schema not in ('information_schema', 'pg_catalog')
| |
| order by u.view_schema, u.view_name
| |
| ;
| |
| CREATE VIEW optim.admin_views_dependency_summary AS
| |
| select schema_name||'.'||view_name as vw_name,
| |
| referenced_table_schema||'.'||referenced_table_name as ref_tab_name
| |
| from optim.admin_views_dependency
| |
| ;
| |
| | |
| -- DROP VIEW optim.admin_udf_summary;
| |
| CREATE VIEW optim.admin_udf_summary AS
| |
| SELECT n.oid, quote_ident(n.nspname) as schema , quote_ident(p.proname) as function,
| |
| pg_get_function_result(n.oid) as fres
| |
| FROM pg_catalog.pg_proc p
| |
| JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
| |
| WHERE n.nspname not like 'pg%'
| |
| ;
| |
| | |
| CREATE or replace FUNCTION doc_UDF_using_a_table(
| |
| p_table text,
| |
| p_schema_name text DEFAULT NULL,
| |
| p_name_like text DEFAULT '',
| |
| p_name_notlike text DEFAULT '',
| |
| p_oid oid DEFAULT NULL
| |
| ) RETURNS TABLE (
| |
| id text,
| |
| oid oid,
| |
| schema_name text,
| |
| name text,
| |
| language text, --new
| |
| definition_md5 text, -- version control
| |
| arguments_simplified text[],
| |
| arguments text,
| |
| return_type text,
| |
| prokind text,
| |
| comment text
| |
| ) AS $f$
| |
| SELECT doc_UDF_transparent_id(u.schema_name, u.name::text, s.arguments_simplified::text[]) AS id,
| |
| u.oid, u.schema_name, u.name::text,
| |
| u.language, md5(u.definition),
| |
| s.arguments_simplified::text[] as arguments_simplified,
| |
| u.arguments::text AS arguments,
| |
| u.return_type, u.prokind, u.comment
| |
| FROM doc_UDF_show_simplified_signature($2,$3,$4) s
| |
| INNER JOIN doc_UDF_show($2,$3,$4,$5) u ON s.oid=u.oid::text
| |
| WHERE u.definition ilike '%'|| p_table ||'%'
| |
| $f$ LANGUAGE SQL IMMUTABLE;
| |
| </pre>
| |