SQL Sample

See Natural Codes as foundation.

create schema natcod;
create table natcod.sample (
  v  bit varying PRIMARY KEY, -- can be null?
  kx_s smallint  -- size, for (s,n) representation
) ;
CREATE TABLE natcod.sample_small (
  kx_z smallint, -- zeros for  (z,n) representation
  kx_n bigint,   -- numeric value, for (s,n) and (z,n) representations. Only when kx_s<64.
  kx_hb bigint   -- hidden bit representation, when kx_s<63, to use more 1 bit.
) INHERITS (natcod.sample);
-- create view natcode.vw01fullrep_sample AS .. caches!

INSERT INTO natcod.sample_small (v, kx_n, kx_s)
 SELECT x::bit(1)::varbit, x, 1  FROM generate_series(0,1) t(x)
 --  SELECT format('UNION ALL SELECT x::bit(%x)::varbit, x, %s FROM generate_series(0,%s) t(x)', i, i, 2^i-1) as script
 --  FROM generate_series(1,15) t(i);
 UNION ALL SELECT x::bit(2)::varbit, x, 2  FROM generate_series(0,3) t(x)
 UNION ALL SELECT x::bit(3)::varbit, x, 3  FROM generate_series(0,7) t(x)
 UNION ALL SELECT x::bit(4)::varbit, x, 4  FROM generate_series(0,15) t(x)
 UNION ALL SELECT x::bit(5)::varbit, x, 5  FROM generate_series(0,31) t(x)
 UNION ALL SELECT x::bit(6)::varbit, x, 6  FROM generate_series(0,63) t(x)
 UNION ALL SELECT x::bit(7)::varbit, x, 7  FROM generate_series(0,127) t(x)
 UNION ALL SELECT x::bit(8)::varbit, x, 8  FROM generate_series(0,255) t(x)
 UNION ALL SELECT x::bit(9)::varbit, x, 9  FROM generate_series(0,511) t(x)
 UNION ALL SELECT x::bit(10)::varbit, x, 10  FROM generate_series(0,1023) t(x)
 UNION ALL SELECT x::bit(11)::varbit, x, 11  FROM generate_series(0,2047) t(x)
 UNION ALL SELECT x::bit(12)::varbit, x, 12  FROM generate_series(0,4095) t(x)
 UNION ALL SELECT x::bit(13)::varbit, x, 13  FROM generate_series(0,8191) t(x)
 UNION ALL SELECT x::bit(14)::varbit, x, 14  FROM generate_series(0,16383) t(x)
 UNION ALL SELECT x::bit(15)::varbit, x, 15  FROM generate_series(0,32767) t(x)
;
UPDATE natcod.sample_small
  SET kx_z = length(regexp_replace(v::text,'1.*$','')) -- pode ser na view
  -- ,kx_hb = natcod.vbit_to_hbig(v)
;
INSERT INTO natcod.sample (v, kx_s)
  SELECT B'0000000000'||('X' || md5('Hello'||x))::varbit, 138 from generate_series(1,20) t(x)
  UNION ALL SELECT ('X' || md5('Hello'||x))::varbit, 128 from generate_series(1,20) t(x)
;

Testando hipoteses experimentalmente:

• "A ordem lexicografica e numerica sao as mesmas quando size o mesmo?"
  select v,kx_n from natcod.sample_small where kx_s=5 order by v
  ou select v,kx_n, CASE WHEN (kx_n - coalesce(lag(kx_n,1) over(),0)) != 1 THEN 'ERROR!' ELSE '' END alerta from natcod.sample_small where kx_s=9 order by v
• ...