dekassegui/lotomania-atualiza-db.sh

## lotomania-atualiza-db.sh
#!/bin/bash
#
# Script para atualizar e (re)criar, se necessário, o db da Lotomania com dados
# baixados do website da Caixa Econômica Federal Loterias.

# formata indiferentemente ao separador de campos, data no formato
# yyyy.mm.dd ou dd.mm.yyyy como data no formato yyyy-mm-dd
full_date() {
  # padroniza os separadores de campos
  local d=${1//[^0-9]/-}
  # se a data é dd-mm-yyyy então modifica para yyyy-mm-dd
  [[ ${d:2:1} == '-' ]] && echo ${d:6:4}-${d:3:2}-${d:0:2} || echo $d
}

# formata indiferentemente ao separador de campos, data no formato
# yyyy.mm.dd ou dd.mm.yyyy como data no formato "data por extenso"
long_date() {
  date -d $(full_date $1) '+%A, %d de %B de %Y'
}

# computa a data presumida do concurso da Lotomania anterior e mais recente que
# a data ISO-8601 fornecida ou a data corrente do sistema em caso contrário
mania_date() {
  # prepara a data alvo com data arbitrária ou data corrente
  (( $# )) && dia=$(date -d "$*" +'%F %H:%M:%S %z') || dia=$(date +'%F %H:%M:%S %z')
  read u F ndays <<< $(date -d "$dia" +'%u %F 0')
  # testa se data alvo é terça, quinta ou sábado e se horário da data alvo é
  # anterior a 20:00 o qual é o horário usual dos sorteios
  if (( ${u}%2 == 0 && $(date -d "$dia" +%s) < $(date -d "$F 20:00" +%s) ))
  then
    ndays=1
  # testa se data alvo é domingo ou se horário da data alvo é anterior a 20:00
  elif (( $u == 7 || $(date -d "$dia" +%s) < $(date -d "$F 20:00" +%s) )); then
    ndays=2
  fi
  date -d "$F -$ndays days" +%F
}

echo -e '\nData presumida do sorteio mais recente: '$(long_date $(mania_date))'.'

declare -r html=lotomania.htm         # arquivo da série temporal de concursos
                                      # baixado a cada execução e preservado até
                                      # a seguinte como backup
declare -r dbname=lotomania.sqlite    # arquivo do db SQLite, opcionalmente
                                      # (re)criado, preenchido a cada execução
declare -r concursos=concursos.dat    # arquivo plain/text dos dados de
                                      # concursos para preenchimento do db
declare -r ganhadores=ganhadores.dat  # arquivo plain/text dos dados de
                                      # acertadores para preenchimento do db

# link para o arquivo html remoto que contém a série histórica dos concursos
declare -r url=http://loterias.caixa.gov.br/wps/portal/loterias/landing/lotomania/!ut/p/a1/04_Sj9CPykssy0xPLMnMz0vMAfGjzOLNDH0MPAzcDbz8vTxNDRy9_Y2NQ13CDA38jYEKIoEKnN0dPUzMfQwMDEwsjAw8XZw8XMwtfQ0MPM2I02-AAzgaENIfrh-FqsQ9wBmoxN_FydLAGAgNTKEK8DkRrACPGwpyQyMMMj0VAajYsZo!/dl5/d5/L2dBISEvZ0FBIS9nQSEh/pw/Z7_HGK818G0K85260Q5OIRSC42045/res/id=historicoHTML/c=cacheLevelPage/=/

# preserva, se existir, o arquivo da série de concursos baixado anteriormente
[[ -e $html ]] && mv $html $html~

printf '\n-- Baixando arquivo remoto.\n'

# Download do arquivo html da série histórica dos concursos, com imediata
# contagem da quantidade de concursos e extração do número serial do concurso
# mais recente – certamente o último.
read m n <<< $(xidel $url --download=$html --output-encoding=UTF-8 -s --xpath 'concat(count(html/body/table/tbody/tr[@bgcolor]), " ", html/body/table/tbody/tr[last()]/td[1])')

# restaura o arquivo e aborta execução do script se o download foi mal sucedido
if [[ ! -e $html ]]; then
  printf '\nAviso: Não foi possível baixar o arquivo remoto.\n\n'
  [[ -e $html~ ]] && mv $html~ $html
  exit 1
fi

xpath() {
  xidel $html --output-encoding=UTF-8 -s --xpath "$1"
}

# checa a sequência dos números seriais dos concursos no html
if (( n > m )); then
  # monta o array dos números seriais dos concursos
  read -d' ' -a z <<< $(xpath 'html/body/table/tbody/tr[@bgcolor]/td[1]')
  r=$(( n-m ))
  printf '\nAviso: %d registros ausentes no html:\n\n' $r
  # pesquisa componentes ausentes na frente do array
  for (( j=1; j<${z[0]}; j++, r-- )); do printf ' %04d' $j; done
  # pesquisa componentes ausentes dentro do array
  for (( i=0; r>0 && i<m-1; i++ )); do
    for (( j=${z[i]}+1; j<${z[i+1]}; j++, r-- )); do printf ' %04d' $j; done
  done
  printf '\n'
  unset z     # elimina o array dos números
fi

# cria ou recria o db se o arquivo container não existir
if [[ ! -e $dbname ]]; then
  printf '\n-- Criando o db.\n'
  sqlite3 $dbname <<EOT
.read lotomania.sql
EOT
fi

# requisita o número do concurso mais recente registrado ou "zero" se db vazio
m=$(sqlite3 $dbname 'SELECT IFNULL((SELECT MAX(concurso) FROM concursos), 0)')

if (( n > m )); then

  printf '\n-- Extraindo dados dos concursos.\n'

  # extrai do html baixado os dados dos concursos – exceto dos acertadores – que
  # são armazenados num arquivo text/plain conveniente para importação no SQLite
  xpath "html/body/table/tbody/tr[@bgcolor and td[1]>$m] / translate(string-join((td[1], string-join((substring(td[2],7), substring(td[2],4,2), substring(td[2],1,2)), '-'), td[position()>2 and position()<22], td[22] mod 100, td[23], td[24], td[position()>25]), '|'), ',.', '.')" > $concursos

  # contabiliza o número de acertadores a partir do concurso mais antigo não
  # registrado, dado que o db pode estar desatualizado a mais de um concurso
  n=$(xpath "sum(html/body/table/tbody/tr[@bgcolor and td[1]>$m]/td[24])")

  if (( n > 0 )); then
    printf '\n-- Extraindo dados dos acertadores.\n'
    # extrai do html baixado somente dados dos acertadores, que são armazenados
    # num arquivo text/plain conveniente para importação no db SQLite
    xpath "html/body/table/tbody/tr[@bgcolor and td[1]>$m and td[24]>0]/td[25]/table/tbody/tr / concat(ancestor::tr[@bgcolor]/td[1], '|', upper-case(concat(if (string-length(td[1])=0) then 'NULL' else td[1], '|', if (string-length(td[2])=0) then 'NULL' else td[2])))" > $ganhadores
  else
    > $ganhadores   # cria arquivo vazio que evita erro ao importar dados
  fi

  printf '\n-- Preenchendo o db.\n'

  # preenche as tabelas dos concursos e dos acertadores com os dados extraídos
  sqlite3 $dbname <<EOT
.import $concursos concursos
.import $ganhadores ganhadores
EOT

fi

# notifica o número serial e data do concurso mais recente no db
read n s <<< $(sqlite3 -separator ' ' $dbname 'select concurso, data_sorteio from concursos order by data_sorteio desc limit 1')
printf '\nConcurso mais recente no DB: %04d (%s).\n\n' $n "$(long_date $s)"

# pesquisa e notifica reincidência da combinação das bolas sorteadas mais
# recente da série histórica dos concursos
m=$(sqlite3 $dbname "WITH cte(N, M) AS (SELECT LE49, GE50 FROM bolas_juntadas WHERE concurso == $n) SELECT count(1) FROM bolas_juntadas, cte WHERE LE49 == N and GE50 == M")
(( m > 1 )) && printf 'Nota: A combinação das bolas sorteados %s\n\n' "ocorreu $m vezes!"

## lotomania.sql
-- (Re)cria o esquema completo do db da Lotomania para uso no SQLite ou similar
-- com modificações. Recomendamos que as tabelas sejam preenchidas com os dados
-- oficiais – disponíveis no website da Caixa Econômica Federal > Loterias >
-- Lotomania – para que o db seja confiável para análises estatísticas,
-- identificação de padrões, etc.
-- Este código aberto público e gratuito está sob Licença LGPL versão 3.0.

BEGIN TRANSACTION;
PRAGMA legacy_file_format = ON;

DROP TABLE IF EXISTS concursos;
CREATE TABLE concursos (
  -- tabela obtida por conversão de documento HTML baixado do website da Caixa >
  -- Loterias, que contém a série temporal completa dos concursos da Lotomania
  concurso                    INTEGER PRIMARY KEY,
  data_sorteio                DATETIME NOT NULL ON CONFLICT ABORT, -- yyyy-mm-dd
  bola1                       INTEGER CHECK (bola1  BETWEEN 0 AND 99),
  bola2                       INTEGER CHECK (bola2  BETWEEN 0 AND 99),
  bola3                       INTEGER CHECK (bola3  BETWEEN 0 AND 99),
  bola4                       INTEGER CHECK (bola4  BETWEEN 0 AND 99),
  bola5                       INTEGER CHECK (bola5  BETWEEN 0 AND 99),
  bola6                       INTEGER CHECK (bola6  BETWEEN 0 AND 99),
  bola7                       INTEGER CHECK (bola7  BETWEEN 0 AND 99),
  bola8                       INTEGER CHECK (bola8  BETWEEN 0 AND 99),
  bola9                       INTEGER CHECK (bola9  BETWEEN 0 AND 99),
  bola10                      INTEGER CHECK (bola10 BETWEEN 0 AND 99),
  bola11                      INTEGER CHECK (bola11 BETWEEN 0 AND 99),
  bola12                      INTEGER CHECK (bola12 BETWEEN 0 AND 99),
  bola13                      INTEGER CHECK (bola13 BETWEEN 0 AND 99),
  bola14                      INTEGER CHECK (bola14 BETWEEN 0 AND 99),
  bola15                      INTEGER CHECK (bola15 BETWEEN 0 AND 99),
  bola16                      INTEGER CHECK (bola16 BETWEEN 0 AND 99),
  bola17                      INTEGER CHECK (bola17 BETWEEN 0 AND 99),
  bola18                      INTEGER CHECK (bola18 BETWEEN 0 AND 99),
  bola19                      INTEGER CHECK (bola19 BETWEEN 0 AND 99),
  bola20                      INTEGER CHECK (bola20 BETWEEN 0 AND 99),
  arrecadacao_total           DOUBLE,
  ganhadores_20_numeros       INTEGER,
  ganhadores_19_numeros       INTEGER,
  ganhadores_18_numeros       INTEGER,
  ganhadores_17_numeros       INTEGER,
  ganhadores_16_numeros       INTEGER,
  ganhadores_nenhum_numero    INTEGER,
  valor_rateio_20_numeros     DOUBLE,
  valor_rateio_19_numeros     DOUBLE,
  valor_rateio_18_numeros     DOUBLE,
  valor_rateio_17_numeros     DOUBLE,
  valor_rateio_16_numeros     DOUBLE,
  valor_rateio_nenhum_numero  DOUBLE,
  acumulado_20_numeros        DOUBLE,
  estimativa_premio           DOUBLE,
  valor_acumulado_especial    DOUBLE,
  CONSTRAINT bolas_unicas CHECK(
    bola1 NOT IN (bola2, bola3, bola4, bola5, bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola2 NOT IN (bola3, bola4, bola5, bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola3 NOT IN (bola4, bola5, bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola4 NOT IN (bola5, bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola5 NOT IN (bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola6 NOT IN (bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola7 NOT IN (bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola8 NOT IN (bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola9 NOT IN (bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola10 NOT IN (bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola11 NOT IN (bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola12 NOT IN (bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola13 NOT IN (bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola14 NOT IN (bola15, bola16, bola17, bola18, bola19, bola20) AND
    bola15 NOT IN (bola16, bola17, bola18, bola19, bola20) AND
    bola16 NOT IN (bola17, bola18, bola19, bola20) AND
    bola17 NOT IN (bola18, bola19, bola20) AND
    bola18 NOT IN (bola19, bola20) AND
    bola19 != bola20
  )
);

CREATE TRIGGER IF NOT EXISTS on_concursos_insert AFTER INSERT ON concursos BEGIN

  INSERT INTO bolas_juntadas (concurso, LE49, GE50) VALUES (
    new.concurso,
    -- monta a representação bitwise dos números entre 00 e 49 inclusive
    (select case when new.bola1<=49 then 1<<new.bola1 else 0 end)
    | (select case when new.bola2<=49 then 1<<new.bola2 else 0 end)
    | (select case when new.bola3<=49 then 1<<new.bola3 else 0 end)
    | (select case when new.bola4<=49 then 1<<new.bola4 else 0 end)
    | (select case when new.bola5<=49 then 1<<new.bola5 else 0 end)
    | (select case when new.bola6<=49 then 1<<new.bola6 else 0 end)
    | (select case when new.bola7<=49 then 1<<new.bola7 else 0 end)
    | (select case when new.bola8<=49 then 1<<new.bola8 else 0 end)
    | (select case when new.bola9<=49 then 1<<new.bola9 else 0 end)
    | (select case when new.bola10<=49 then 1<<new.bola10 else 0 end)
    | (select case when new.bola11<=49 then 1<<new.bola11 else 0 end)
    | (select case when new.bola12<=49 then 1<<new.bola12 else 0 end)
    | (select case when new.bola13<=49 then 1<<new.bola13 else 0 end)
    | (select case when new.bola14<=49 then 1<<new.bola14 else 0 end)
    | (select case when new.bola15<=49 then 1<<new.bola15 else 0 end)
    | (select case when new.bola16<=49 then 1<<new.bola16 else 0 end)
    | (select case when new.bola17<=49 then 1<<new.bola17 else 0 end)
    | (select case when new.bola18<=49 then 1<<new.bola18 else 0 end)
    | (select case when new.bola19<=49 then 1<<new.bola19 else 0 end)
    | (select case when new.bola20<=49 then 1<<new.bola20 else 0 end),
    -- monta a representação bitwise dos números entre 50 e 99 inclusive
    (select case when new.bola1>=50 then 1<<new.bola1-50 else 0 end)
    | (select case when new.bola2>=50 then 1<<new.bola2-50 else 0 end)
    | (select case when new.bola3>=50 then 1<<new.bola3-50 else 0 end)
    | (select case when new.bola4>=50 then 1<<new.bola4-50 else 0 end)
    | (select case when new.bola5>=50 then 1<<new.bola5-50 else 0 end)
    | (select case when new.bola6>=50 then 1<<new.bola6-50 else 0 end)
    | (select case when new.bola7>=50 then 1<<new.bola7-50 else 0 end)
    | (select case when new.bola8>=50 then 1<<new.bola8-50 else 0 end)
    | (select case when new.bola9>=50 then 1<<new.bola9-50 else 0 end)
    | (select case when new.bola10>=50 then 1<<new.bola10-50 else 0 end)
    | (select case when new.bola11>=50 then 1<<new.bola11-50 else 0 end)
    | (select case when new.bola12>=50 then 1<<new.bola12-50 else 0 end)
    | (select case when new.bola13>=50 then 1<<new.bola13-50 else 0 end)
    | (select case when new.bola14>=50 then 1<<new.bola14-50 else 0 end)
    | (select case when new.bola15>=50 then 1<<new.bola15-50 else 0 end)
    | (select case when new.bola16>=50 then 1<<new.bola16-50 else 0 end)
    | (select case when new.bola17>=50 then 1<<new.bola17-50 else 0 end)
    | (select case when new.bola18>=50 then 1<<new.bola18-50 else 0 end)
    | (select case when new.bola19>=50 then 1<<new.bola19-50 else 0 end)
    | (select case when new.bola20>=50 then 1<<new.bola20-50 else 0 end)
  );

  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola1);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola2);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola3);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola4);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola5);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola6);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola7);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola8);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola9);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola10);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola11);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola12);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola13);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola14);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola15);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola16);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola17);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola18);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola19);
  INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola20);
END;

CREATE TRIGGER IF NOT EXISTS on_concursos_delete AFTER DELETE ON concursos BEGIN
  DELETE FROM bitmasks WHERE (concurso == old.concurso);
  DELETE FROM bolas_juntadas WHERE (concurso == old.concurso);
  DELETE FROM bolas_sorteadas WHERE (concurso == old.concurso);
  DELETE FROM ganhadores WHERE (concurso == old.concurso);
END;

DROP TABLE IF EXISTS ganhadores;
CREATE TABLE ganhadores (
  concurso  INTEGER NOT NULL,
  cidade    TEXT,
  uf        TEXT,
  FOREIGN KEY (concurso) REFERENCES concursos(concurso)
);

CREATE TRIGGER IF NOT EXISTS on_ganhadores_insert BEFORE INSERT ON ganhadores
WHEN new.cidade == "NULL" OR trim(new.cidade) == ""
BEGIN
  INSERT INTO ganhadores VALUES(new.concurso, NULL, new.uf);
  SELECT RAISE(IGNORE);   --> cancela inserção do registro original
END;

DROP TABLE IF EXISTS bolas_juntadas;
CREATE TABLE bolas_juntadas (
  -- tabela dos números sorteados nos concursos, cujas incidências são
  -- representadas nos bits de valores inteiros, conforme suas magnitudes,
  -- preenchida automaticamente na inclusão de novos registros de concursos
  concurso  INTEGER,
  LE49 INTEGER,       -- 00 <= números <= 49
  GE50 INTEGER,       -- 50 <= números <= 99
  FOREIGN KEY (concurso) REFERENCES concursos(concurso)
);

CREATE TRIGGER IF NOT EXISTS on_juntadas_insert AFTER INSERT ON bolas_juntadas BEGIN
  INSERT INTO bitmasks (concurso, mask) VALUES (
    new.concurso,
    (
      -- montagem RTL recursiva da máscara via operações bitwise sucessivas
      WITH RECURSIVE bits (n, r, s) AS (
        VALUES (-1, "", "")
        UNION ALL
        SELECT n+1, (new.GE50 >> n+1 & 1) || r, (new.LE49 >> n+1 & 1) || s
        FROM bits WHERE n < 50
      ) SELECT r || s FROM bits WHERE n == 49
    )
  );
END;

DROP TABLE IF EXISTS bitmasks;
CREATE TABLE bitmasks (
  -- tabela das "máscaras" dos números sorteados em cada concurso, preenchidas
  -- da direita para a esquerda (aka RTL) i.e.: as incidências dos 100 números
  -- (00 a 99) estão representadas nas respectivas posições na ordem inversa
  -- da escrita natural (de várias línguas ocidentais) conforme convencionado
  -- para representação de números inteiros na base 2 – binary representation
  concurso  INTEGER,
  mask      TEXT,
  FOREIGN KEY (concurso) REFERENCES concursos(concurso)
);

DROP TABLE IF EXISTS bolas_sorteadas;
CREATE TABLE bolas_sorteadas (
  -- tabela conveniência p/facilitar análise dos números sorteados ao longo do
  -- tempo, preenchida nas inclusões de novos registros de concursos
  concurso  INTEGER,
  bola      INTEGER,
  FOREIGN KEY (concurso) REFERENCES concursos(concurso)
);

DROP INDEX IF EXISTS ndx;
CREATE INDEX ndx ON bolas_sorteadas (concurso, bola);

DROP VIEW IF EXISTS info_bolas;
CREATE VIEW info_bolas AS
  SELECT M AS concurso, bola, frequencia, latencia, (frequencia < u AND latencia >= v) AS atipico
  FROM (
    SELECT bola, count(bola) AS frequencia, (M-max(concurso)) AS latencia, M, (M*20.0/100) AS u, (100.0/20) AS v
    FROM (SELECT max(concurso) AS M FROM concursos), bolas_sorteadas
    GROUP BY bola
  );

DROP VIEW IF EXISTS espera;
CREATE VIEW espera AS
  -- sequência de tempos de espera por concurso com 1+ acertadores das 20 bolas
  WITH cte(s) AS (
    SELECT group_concat(ganhadores_20_numeros>0, '') FROM concursos
  ), ones(n, p) AS (
    SELECT 1, instr(s, '1') FROM cte
    UNION ALL
    SELECT n+1, p+instr(substr(s, p+1), '1') AS m FROM cte, ones WHERE m > p
  ) SELECT n AS ndx, p AS fim, p AS len FROM ones WHERE n == 1
    UNION ALL
    SELECT t.n, t.p, t.p-x.p FROM ones AS t JOIN ones AS x ON t.n == x.n+1;

COMMIT;
	#!/bin/bash
	#
	# Script para atualizar e (re)criar, se necessário, o db da Lotomania com dados
	# baixados do website da Caixa Econômica Federal Loterias.

	# formata indiferentemente ao separador de campos, data no formato
	# yyyy.mm.dd ou dd.mm.yyyy como data no formato yyyy-mm-dd
	full_date() {
	# padroniza os separadores de campos
	local d=${1//[^0-9]/-}
	# se a data é dd-mm-yyyy então modifica para yyyy-mm-dd
	[[ ${d:2:1} == '-' ]] && echo ${d:6:4}-${d:3:2}-${d:0:2} \|\| echo $d
	}

	# formata indiferentemente ao separador de campos, data no formato
	# yyyy.mm.dd ou dd.mm.yyyy como data no formato "data por extenso"
	long_date() {
	date -d $(full_date $1) '+%A, %d de %B de %Y'
	}

	# computa a data presumida do concurso da Lotomania anterior e mais recente que
	# a data ISO-8601 fornecida ou a data corrente do sistema em caso contrário
	mania_date() {
	# prepara a data alvo com data arbitrária ou data corrente
	(( $# )) && dia=$(date -d "$*" +'%F %H:%M:%S %z') \|\| dia=$(date +'%F %H:%M:%S %z')
	read u F ndays <<< $(date -d "$dia" +'%u %F 0')
	# testa se data alvo é terça, quinta ou sábado e se horário da data alvo é
	# anterior a 20:00 o qual é o horário usual dos sorteios
	if (( ${u}%2 == 0 && $(date -d "$dia" +%s) < $(date -d "$F 20:00" +%s) ))
	then
	ndays=1
	# testa se data alvo é domingo ou se horário da data alvo é anterior a 20:00
	elif (( $u == 7 \|\| $(date -d "$dia" +%s) < $(date -d "$F 20:00" +%s) )); then
	ndays=2
	fi
	date -d "$F -$ndays days" +%F
	}

	echo -e '\nData presumida do sorteio mais recente: '$(long_date $(mania_date))'.'

	declare -r html=lotomania.htm # arquivo da série temporal de concursos
	# baixado a cada execução e preservado até
	# a seguinte como backup
	declare -r dbname=lotomania.sqlite # arquivo do db SQLite, opcionalmente
	# (re)criado, preenchido a cada execução
	declare -r concursos=concursos.dat # arquivo plain/text dos dados de
	# concursos para preenchimento do db
	declare -r ganhadores=ganhadores.dat # arquivo plain/text dos dados de
	# acertadores para preenchimento do db

	# link para o arquivo html remoto que contém a série histórica dos concursos
	declare -r url=http://loterias.caixa.gov.br/wps/portal/loterias/landing/lotomania/!ut/p/a1/04_Sj9CPykssy0xPLMnMz0vMAfGjzOLNDH0MPAzcDbz8vTxNDRy9_Y2NQ13CDA38jYEKIoEKnN0dPUzMfQwMDEwsjAw8XZw8XMwtfQ0MPM2I02-AAzgaENIfrh-FqsQ9wBmoxN_FydLAGAgNTKEK8DkRrACPGwpyQyMMMj0VAajYsZo!/dl5/d5/L2dBISEvZ0FBIS9nQSEh/pw/Z7_HGK818G0K85260Q5OIRSC42045/res/id=historicoHTML/c=cacheLevelPage/=/

	# preserva, se existir, o arquivo da série de concursos baixado anteriormente
	[[ -e $html ]] && mv $html $html~

	printf '\n-- Baixando arquivo remoto.\n'

	# Download do arquivo html da série histórica dos concursos, com imediata
	# contagem da quantidade de concursos e extração do número serial do concurso
	# mais recente – certamente o último.
	read m n <<< $(xidel $url --download=$html --output-encoding=UTF-8 -s --xpath 'concat(count(html/body/table/tbody/tr[@bgcolor]), " ", html/body/table/tbody/tr[last()]/td[1])')

	# restaura o arquivo e aborta execução do script se o download foi mal sucedido
	if [[ ! -e $html ]]; then
	printf '\nAviso: Não foi possível baixar o arquivo remoto.\n\n'
	[[ -e $html~ ]] && mv $html~ $html
	exit 1
	fi

	xpath() {
	xidel $html --output-encoding=UTF-8 -s --xpath "$1"
	}

	# checa a sequência dos números seriais dos concursos no html
	if (( n > m )); then
	# monta o array dos números seriais dos concursos
	read -d' ' -a z <<< $(xpath 'html/body/table/tbody/tr[@bgcolor]/td[1]')
	r=$(( n-m ))
	printf '\nAviso: %d registros ausentes no html:\n\n' $r
	# pesquisa componentes ausentes na frente do array
	for (( j=1; j<${z[0]}; j++, r-- )); do printf ' %04d' $j; done
	# pesquisa componentes ausentes dentro do array
	for (( i=0; r>0 && i<m-1; i++ )); do
	for (( j=${z[i]}+1; j<${z[i+1]}; j++, r-- )); do printf ' %04d' $j; done
	done
	printf '\n'
	unset z # elimina o array dos números
	fi

	# cria ou recria o db se o arquivo container não existir
	if [[ ! -e $dbname ]]; then
	printf '\n-- Criando o db.\n'
	sqlite3 $dbname <<EOT
	.read lotomania.sql
	EOT
	fi

	# requisita o número do concurso mais recente registrado ou "zero" se db vazio
	m=$(sqlite3 $dbname 'SELECT IFNULL((SELECT MAX(concurso) FROM concursos), 0)')

	if (( n > m )); then

	printf '\n-- Extraindo dados dos concursos.\n'

	# extrai do html baixado os dados dos concursos – exceto dos acertadores – que
	# são armazenados num arquivo text/plain conveniente para importação no SQLite
	xpath "html/body/table/tbody/tr[@bgcolor and td[1]>$m] / translate(string-join((td[1], string-join((substring(td[2],7), substring(td[2],4,2), substring(td[2],1,2)), '-'), td[position()>2 and position()<22], td[22] mod 100, td[23], td[24], td[position()>25]), '\|'), ',.', '.')" > $concursos

	# contabiliza o número de acertadores a partir do concurso mais antigo não
	# registrado, dado que o db pode estar desatualizado a mais de um concurso
	n=$(xpath "sum(html/body/table/tbody/tr[@bgcolor and td[1]>$m]/td[24])")

	if (( n > 0 )); then
	printf '\n-- Extraindo dados dos acertadores.\n'
	# extrai do html baixado somente dados dos acertadores, que são armazenados
	# num arquivo text/plain conveniente para importação no db SQLite
	xpath "html/body/table/tbody/tr[@bgcolor and td[1]>$m and td[24]>0]/td[25]/table/tbody/tr / concat(ancestor::tr[@bgcolor]/td[1], '\|', upper-case(concat(if (string-length(td[1])=0) then 'NULL' else td[1], '\|', if (string-length(td[2])=0) then 'NULL' else td[2])))" > $ganhadores
	else
	> $ganhadores # cria arquivo vazio que evita erro ao importar dados
	fi

	printf '\n-- Preenchendo o db.\n'

	# preenche as tabelas dos concursos e dos acertadores com os dados extraídos
	sqlite3 $dbname <<EOT
	.import $concursos concursos
	.import $ganhadores ganhadores
	EOT

	fi

	# notifica o número serial e data do concurso mais recente no db
	read n s <<< $(sqlite3 -separator ' ' $dbname 'select concurso, data_sorteio from concursos order by data_sorteio desc limit 1')
	printf '\nConcurso mais recente no DB: %04d (%s).\n\n' $n "$(long_date $s)"

	# pesquisa e notifica reincidência da combinação das bolas sorteadas mais
	# recente da série histórica dos concursos
	m=$(sqlite3 $dbname "WITH cte(N, M) AS (SELECT LE49, GE50 FROM bolas_juntadas WHERE concurso == $n) SELECT count(1) FROM bolas_juntadas, cte WHERE LE49 == N and GE50 == M")
	(( m > 1 )) && printf 'Nota: A combinação das bolas sorteados %s\n\n' "ocorreu $m vezes!"
	-- (Re)cria o esquema completo do db da Lotomania para uso no SQLite ou similar
	-- com modificações. Recomendamos que as tabelas sejam preenchidas com os dados
	-- oficiais – disponíveis no website da Caixa Econômica Federal > Loterias >
	-- Lotomania – para que o db seja confiável para análises estatísticas,
	-- identificação de padrões, etc.
	-- Este código aberto público e gratuito está sob Licença LGPL versão 3.0.

	BEGIN TRANSACTION;
	PRAGMA legacy_file_format = ON;

	DROP TABLE IF EXISTS concursos;
	CREATE TABLE concursos (
	-- tabela obtida por conversão de documento HTML baixado do website da Caixa >
	-- Loterias, que contém a série temporal completa dos concursos da Lotomania
	concurso INTEGER PRIMARY KEY,
	data_sorteio DATETIME NOT NULL ON CONFLICT ABORT, -- yyyy-mm-dd
	bola1 INTEGER CHECK (bola1 BETWEEN 0 AND 99),
	bola2 INTEGER CHECK (bola2 BETWEEN 0 AND 99),
	bola3 INTEGER CHECK (bola3 BETWEEN 0 AND 99),
	bola4 INTEGER CHECK (bola4 BETWEEN 0 AND 99),
	bola5 INTEGER CHECK (bola5 BETWEEN 0 AND 99),
	bola6 INTEGER CHECK (bola6 BETWEEN 0 AND 99),
	bola7 INTEGER CHECK (bola7 BETWEEN 0 AND 99),
	bola8 INTEGER CHECK (bola8 BETWEEN 0 AND 99),
	bola9 INTEGER CHECK (bola9 BETWEEN 0 AND 99),
	bola10 INTEGER CHECK (bola10 BETWEEN 0 AND 99),
	bola11 INTEGER CHECK (bola11 BETWEEN 0 AND 99),
	bola12 INTEGER CHECK (bola12 BETWEEN 0 AND 99),
	bola13 INTEGER CHECK (bola13 BETWEEN 0 AND 99),
	bola14 INTEGER CHECK (bola14 BETWEEN 0 AND 99),
	bola15 INTEGER CHECK (bola15 BETWEEN 0 AND 99),
	bola16 INTEGER CHECK (bola16 BETWEEN 0 AND 99),
	bola17 INTEGER CHECK (bola17 BETWEEN 0 AND 99),
	bola18 INTEGER CHECK (bola18 BETWEEN 0 AND 99),
	bola19 INTEGER CHECK (bola19 BETWEEN 0 AND 99),
	bola20 INTEGER CHECK (bola20 BETWEEN 0 AND 99),
	arrecadacao_total DOUBLE,
	ganhadores_20_numeros INTEGER,
	ganhadores_19_numeros INTEGER,
	ganhadores_18_numeros INTEGER,
	ganhadores_17_numeros INTEGER,
	ganhadores_16_numeros INTEGER,
	ganhadores_nenhum_numero INTEGER,
	valor_rateio_20_numeros DOUBLE,
	valor_rateio_19_numeros DOUBLE,
	valor_rateio_18_numeros DOUBLE,
	valor_rateio_17_numeros DOUBLE,
	valor_rateio_16_numeros DOUBLE,
	valor_rateio_nenhum_numero DOUBLE,
	acumulado_20_numeros DOUBLE,
	estimativa_premio DOUBLE,
	valor_acumulado_especial DOUBLE,
	CONSTRAINT bolas_unicas CHECK(
	bola1 NOT IN (bola2, bola3, bola4, bola5, bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola2 NOT IN (bola3, bola4, bola5, bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola3 NOT IN (bola4, bola5, bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola4 NOT IN (bola5, bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola5 NOT IN (bola6, bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola6 NOT IN (bola7, bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola7 NOT IN (bola8, bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola8 NOT IN (bola9, bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola9 NOT IN (bola10, bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola10 NOT IN (bola11, bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola11 NOT IN (bola12, bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola12 NOT IN (bola13, bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola13 NOT IN (bola14, bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola14 NOT IN (bola15, bola16, bola17, bola18, bola19, bola20) AND
	bola15 NOT IN (bola16, bola17, bola18, bola19, bola20) AND
	bola16 NOT IN (bola17, bola18, bola19, bola20) AND
	bola17 NOT IN (bola18, bola19, bola20) AND
	bola18 NOT IN (bola19, bola20) AND
	bola19 != bola20
	)
	);

	CREATE TRIGGER IF NOT EXISTS on_concursos_insert AFTER INSERT ON concursos BEGIN

	INSERT INTO bolas_juntadas (concurso, LE49, GE50) VALUES (
	new.concurso,
	-- monta a representação bitwise dos números entre 00 e 49 inclusive
	(select case when new.bola1<=49 then 1<<new.bola1 else 0 end)
	\| (select case when new.bola2<=49 then 1<<new.bola2 else 0 end)
	\| (select case when new.bola3<=49 then 1<<new.bola3 else 0 end)
	\| (select case when new.bola4<=49 then 1<<new.bola4 else 0 end)
	\| (select case when new.bola5<=49 then 1<<new.bola5 else 0 end)
	\| (select case when new.bola6<=49 then 1<<new.bola6 else 0 end)
	\| (select case when new.bola7<=49 then 1<<new.bola7 else 0 end)
	\| (select case when new.bola8<=49 then 1<<new.bola8 else 0 end)
	\| (select case when new.bola9<=49 then 1<<new.bola9 else 0 end)
	\| (select case when new.bola10<=49 then 1<<new.bola10 else 0 end)
	\| (select case when new.bola11<=49 then 1<<new.bola11 else 0 end)
	\| (select case when new.bola12<=49 then 1<<new.bola12 else 0 end)
	\| (select case when new.bola13<=49 then 1<<new.bola13 else 0 end)
	\| (select case when new.bola14<=49 then 1<<new.bola14 else 0 end)
	\| (select case when new.bola15<=49 then 1<<new.bola15 else 0 end)
	\| (select case when new.bola16<=49 then 1<<new.bola16 else 0 end)
	\| (select case when new.bola17<=49 then 1<<new.bola17 else 0 end)
	\| (select case when new.bola18<=49 then 1<<new.bola18 else 0 end)
	\| (select case when new.bola19<=49 then 1<<new.bola19 else 0 end)
	\| (select case when new.bola20<=49 then 1<<new.bola20 else 0 end),
	-- monta a representação bitwise dos números entre 50 e 99 inclusive
	(select case when new.bola1>=50 then 1<<new.bola1-50 else 0 end)
	\| (select case when new.bola2>=50 then 1<<new.bola2-50 else 0 end)
	\| (select case when new.bola3>=50 then 1<<new.bola3-50 else 0 end)
	\| (select case when new.bola4>=50 then 1<<new.bola4-50 else 0 end)
	\| (select case when new.bola5>=50 then 1<<new.bola5-50 else 0 end)
	\| (select case when new.bola6>=50 then 1<<new.bola6-50 else 0 end)
	\| (select case when new.bola7>=50 then 1<<new.bola7-50 else 0 end)
	\| (select case when new.bola8>=50 then 1<<new.bola8-50 else 0 end)
	\| (select case when new.bola9>=50 then 1<<new.bola9-50 else 0 end)
	\| (select case when new.bola10>=50 then 1<<new.bola10-50 else 0 end)
	\| (select case when new.bola11>=50 then 1<<new.bola11-50 else 0 end)
	\| (select case when new.bola12>=50 then 1<<new.bola12-50 else 0 end)
	\| (select case when new.bola13>=50 then 1<<new.bola13-50 else 0 end)
	\| (select case when new.bola14>=50 then 1<<new.bola14-50 else 0 end)
	\| (select case when new.bola15>=50 then 1<<new.bola15-50 else 0 end)
	\| (select case when new.bola16>=50 then 1<<new.bola16-50 else 0 end)
	\| (select case when new.bola17>=50 then 1<<new.bola17-50 else 0 end)
	\| (select case when new.bola18>=50 then 1<<new.bola18-50 else 0 end)
	\| (select case when new.bola19>=50 then 1<<new.bola19-50 else 0 end)
	\| (select case when new.bola20>=50 then 1<<new.bola20-50 else 0 end)
	);

	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola1);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola2);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola3);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola4);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola5);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola6);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola7);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola8);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola9);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola10);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola11);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola12);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola13);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola14);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola15);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola16);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola17);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola18);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola19);
	INSERT INTO bolas_sorteadas (concurso, bola) VALUES (new.concurso, new.bola20);
	END;

	CREATE TRIGGER IF NOT EXISTS on_concursos_delete AFTER DELETE ON concursos BEGIN
	DELETE FROM bitmasks WHERE (concurso == old.concurso);
	DELETE FROM bolas_juntadas WHERE (concurso == old.concurso);
	DELETE FROM bolas_sorteadas WHERE (concurso == old.concurso);
	DELETE FROM ganhadores WHERE (concurso == old.concurso);
	END;

	DROP TABLE IF EXISTS ganhadores;
	CREATE TABLE ganhadores (
	concurso INTEGER NOT NULL,
	cidade TEXT,
	uf TEXT,
	FOREIGN KEY (concurso) REFERENCES concursos(concurso)
	);

	CREATE TRIGGER IF NOT EXISTS on_ganhadores_insert BEFORE INSERT ON ganhadores
	WHEN new.cidade == "NULL" OR trim(new.cidade) == ""
	BEGIN
	INSERT INTO ganhadores VALUES(new.concurso, NULL, new.uf);
	SELECT RAISE(IGNORE); --> cancela inserção do registro original
	END;

	DROP TABLE IF EXISTS bolas_juntadas;
	CREATE TABLE bolas_juntadas (
	-- tabela dos números sorteados nos concursos, cujas incidências são
	-- representadas nos bits de valores inteiros, conforme suas magnitudes,
	-- preenchida automaticamente na inclusão de novos registros de concursos
	concurso INTEGER,
	LE49 INTEGER, -- 00 <= números <= 49
	GE50 INTEGER, -- 50 <= números <= 99
	FOREIGN KEY (concurso) REFERENCES concursos(concurso)
	);

	CREATE TRIGGER IF NOT EXISTS on_juntadas_insert AFTER INSERT ON bolas_juntadas BEGIN
	INSERT INTO bitmasks (concurso, mask) VALUES (
	new.concurso,
	(
	-- montagem RTL recursiva da máscara via operações bitwise sucessivas
	WITH RECURSIVE bits (n, r, s) AS (
	VALUES (-1, "", "")
	UNION ALL
	SELECT n+1, (new.GE50 >> n+1 & 1) \|\| r, (new.LE49 >> n+1 & 1) \|\| s
	FROM bits WHERE n < 50
	) SELECT r \|\| s FROM bits WHERE n == 49
	)
	);
	END;

	DROP TABLE IF EXISTS bitmasks;
	CREATE TABLE bitmasks (
	-- tabela das "máscaras" dos números sorteados em cada concurso, preenchidas
	-- da direita para a esquerda (aka RTL) i.e.: as incidências dos 100 números
	-- (00 a 99) estão representadas nas respectivas posições na ordem inversa
	-- da escrita natural (de várias línguas ocidentais) conforme convencionado
	-- para representação de números inteiros na base 2 – binary representation
	concurso INTEGER,
	mask TEXT,
	FOREIGN KEY (concurso) REFERENCES concursos(concurso)
	);

	DROP TABLE IF EXISTS bolas_sorteadas;
	CREATE TABLE bolas_sorteadas (
	-- tabela conveniência p/facilitar análise dos números sorteados ao longo do
	-- tempo, preenchida nas inclusões de novos registros de concursos
	concurso INTEGER,
	bola INTEGER,
	FOREIGN KEY (concurso) REFERENCES concursos(concurso)
	);

	DROP INDEX IF EXISTS ndx;
	CREATE INDEX ndx ON bolas_sorteadas (concurso, bola);

	DROP VIEW IF EXISTS info_bolas;
	CREATE VIEW info_bolas AS
	SELECT M AS concurso, bola, frequencia, latencia, (frequencia < u AND latencia >= v) AS atipico
	FROM (
	SELECT bola, count(bola) AS frequencia, (M-max(concurso)) AS latencia, M, (M*20.0/100) AS u, (100.0/20) AS v
	FROM (SELECT max(concurso) AS M FROM concursos), bolas_sorteadas
	GROUP BY bola
	);

	DROP VIEW IF EXISTS espera;
	CREATE VIEW espera AS
	-- sequência de tempos de espera por concurso com 1+ acertadores das 20 bolas
	WITH cte(s) AS (
	SELECT group_concat(ganhadores_20_numeros>0, '') FROM concursos
	), ones(n, p) AS (
	SELECT 1, instr(s, '1') FROM cte
	UNION ALL
	SELECT n+1, p+instr(substr(s, p+1), '1') AS m FROM cte, ones WHERE m > p
	) SELECT n AS ndx, p AS fim, p AS len FROM ones WHERE n == 1
	UNION ALL
	SELECT t.n, t.p, t.p-x.p FROM ones AS t JOIN ones AS x ON t.n == x.n+1;

	COMMIT;