Software Público Brasileiro

/* --------------------------------------------------- */
/* TABELAS DA INFRAESTRTURA BÁSICA DO LB */
/* ------------------------ */

/* ----------------------------------- */
/* lb_base */
/* ------------- */
/* [OK] */

CREATE TABLE lb_base
(
  id_base serial NOT NULL,
  name character varying NOT NULL,
  struct character varying NOT NULL,
  dt_base timestamp without time zone NOT NULL,
  idx_exp boolean NOT NULL,
  idx_exp_url character varying,
  idx_exp_time integer,
  file_ext boolean NOT NULL,
  file_ext_time integer,
  txt_mapping character varying,
  CONSTRAINT lb_base_pkey PRIMARY KEY (id_base),
  CONSTRAINT lb_base_name_key UNIQUE (name)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_base
  OWNER TO postgres;

/* ------ */

INSERT INTO public.lb_base (name,struct,dt_base,idx_exp,idx_exp_url,idx_exp_time,file_ext,file_ext_time) VALUES (
'_app_config','{"content": [{"field": {"name": "nm_aplicacao", "datatype": "Text", "required": false, "alias": "Aplicação", "multivalued": false, "indices": ["Textual", "Ordenado"], "description": "Define o nome da aplicação"}}, {"field": {"name": "nm_apelido", "datatype": "Text", "required": false, "alias": "nm_apelido", "multivalued": false, "indices": ["Textual", "Ordenado"], "description": "Apelido da aplicação. Campo usado para identificar à qual aplicação o config pertence ou de qual aplicação. Oriundo da tabela Aplicação nm_apelido."}}, {"group": {"content": [{"field": {"name": "manual", "datatype": "File", "required": false, "alias": "manual", "multivalued": false, "indices": ["Textual"], "description": "Manuais"}}, {"field": {"name": "ch_manual", "datatype": "Text", "required": true, "alias": "Chave do manual", "multivalued": false, "indices": ["Textual"], "description": "Chave para tornar única a identificação de cada manual."}}, {"field": {"name": "nm_manual", "datatype": "Text", "required": true, "alias": "Nome do manual", "multivalued": false, "indices": ["Textual"], "description": "Nome do manual"}}, {"field": {"name": "color", "datatype": "Text", "required": false, "alias": "Cor", "multivalued": false, "indices": ["Textual"], "description": "Cor na qual aparece para download"}}, {"group": {"content": [{"field": {"name": "nr_cpf_user_alteracao", "datatype": "Text", "required": false, "alias": "Número do CPF do usuário da alteração", "multivalued": false, "indices": ["Textual"], "description": "Número do CPF do usuário da ultima alteração. Campo usado para informar o cpf do usuário que fez a ultima alteração."}}, {"field": {"name": "dt_alteracao", "datatype": "DateTime", "required": false, "alias": "Data e hora da alteração", "multivalued": false, "indices": ["Textual"], "description": "Data e Hora no formato DD/MM/AAAA - HH:MM:SS . Campo usado para informar a data e hora da alteração."}}, {"field": {"name": "nm_user_alteracao", "datatype": "Text", "required": false, "alias": "Nome do usuário da alteração", "multivalued": false, "indices": ["Textual", "Ordenado"], "description": "Nome do usuário da alteração. Campo usado para informar o nome do usuário que fez a alteração."}}], "metadata": {"multivalued": true, "alias": "Alteração", "name": "alteracao", "description": "Alteração"}}}, {"group": {"content": [{"field": {"name": "nr_cpf_user_inclusao", "datatype": "Text", "required": false, "alias": "Número do CPF do usuário que incluiu.", "multivalued": false, "indices": ["Textual"], "description": "Número do CPF do usuário que incluiu. Campo usado para informar o cpf do usuário que fez a inclusão do registro."}}, {"field": {"name": "dt_inclusao", "datatype": "Text", "required": false, "alias": "Data e Hora da inclusão", "multivalued": false, "indices": ["Textual"], "description": "Data e Hora no formato DD/MM/AAAA - HH:MM:SS de inclusão do usuário no Cadastro. Campo usado para informar a data que o usuário foi incluído."}}, {"field": {"name": "nm_user_inclusao", "datatype": "Text", "required": false, "alias": "Nome do usuário que incluiu", "multivalued": false, "indices": ["Textual"], "description": "Nome do usuário que incluiu. Campo usado para informar o nome do usuário que realizou a inclusão do usuário."}}], "metadata": {"multivalued": false, "alias": "Inclusão", "name": "inclusao", "description": "Inclusão"}}}], "metadata": {"multivalued": true, "alias": "Manuais", "name": "manuais", "description": "Manuais"}}}], "metadata": {"idx_exp": false, "description": "Configurações da aplicação", "color": "#000000", "file_ext_time": 0, "dt_base": "01/01/2015 00:00:00", "idx_exp_url": "", "file_ext": false, "password": "12345678", "id_base": ID_BASE_PLACEHOLDER, "name": "_app_config", "idx_exp_time": 0, "model": {"manuais": [{"inclusao": {"dt_inclusao": "Text", "nr_cpf_user_inclusao": "Text", "nm_user_inclusao": "Text"}, "color": "Text", "manual": "File", "ch_manual": "Text", "nm_manual": "Text", "alteracao": [{"dt_alteracao": "DateTime", "nm_user_alteracao": "Text", "nr_cpf_user_alteracao": "Text"}]}], "nm_aplicacao": "Text", "nm_apelido": "Text"}}}',TO_DATE('2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),false,'',0,false,0);

UPDATE lb_base SET struct = replace(struct, 'ID_BASE_PLACEHOLDER', cast(id_base as text)) WHERE name='_app_config';

/* ------ */

INSERT INTO public.lb_base (name,struct,dt_base,idx_exp,idx_exp_url,idx_exp_time,file_ext,file_ext_time) VALUES (
'_history','{"content": [{"field": {"alias": "id_base", "description": "Base old ID.", "name": "id_base", "datatype": "Integer", "indices": ["Textual"], "required": true, "multivalued": false}}, {"field": {"alias": "author", "description": "Event Author.", "name": "author", "datatype": "Text", "indices": ["Textual"], "required": true, "multivalued": false}}, {"field": {"alias": "date", "description": "Event Date.", "name": "date", "datatype": "DateTime", "indices": ["Textual"], "required": true, "multivalued": false}}, {"field": {"alias": "name", "description": "Base old name.", "name": "name", "datatype": "Text", "indices": ["Textual"], "required": true, "multivalued": false}}, {"field": {"alias": "structure", "description": "Base old structure", "name": "structure", "datatype": "Json", "indices": ["Textual"], "required": true, "multivalued": false}}, {"field": {"alias": "status", "description": "Base status", "name": "status", "datatype": "Text", "indices": ["Textual"], "required": true, "multivalued": false}}], "metadata": {"idx_exp": false, "description": "LightBase - History Meta Base.", "color": "#000000", "file_ext_time": 10, "idx_exp_time": 0, "idx_exp_url": "", "model": {"status": "Text", "name": "Text", "author": "Text", "id_base": "Integer", "date": "DateTime", "structure": "Json"}, "password": "password", "dt_base": "01/01/2015 00:00:00", "file_ext": false, "id_base": ID_BASE_PLACEHOLDER, "name": "_history"}}',TO_DATE('2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),false,'',0,false,10);

UPDATE lb_base SET struct = replace(struct, 'ID_BASE_PLACEHOLDER', cast(id_base as text)) WHERE name='_history';

/* ------ */

INSERT INTO public.lb_base (name,struct,dt_base,idx_exp,idx_exp_url,idx_exp_time,file_ext,file_ext_time) VALUES (
'_portal','{"content": [{"field": {"name": "nm_portal", "datatype": "Text", "required": true, "alias": "Nome do Portal", "multivalued": false, "indices": ["Textual", "Ordenado"], "description": "Contm o nome do portal. Usado para diferenciar os portais."}}, {"field": {"name": "alias_portal", "datatype": "Text", "required": true, "alias": "Apelido do Portal", "multivalued": false, "indices": ["Textual"], "description": "Este campo informa um apelido, possibilitando exibir um nome amigvel."}}, {"field": {"name": "ds_portal", "datatype": "Text", "required": true, "alias": "Descriço", "multivalued": false, "indices": ["Textual"], "description": "Contm um texto falando sobre o portal. Qual a finalidade do portal criado."}}, {"field": {"name": "cpf_user", "datatype": "Text", "required": false, "alias": "CPF do Usurio", "multivalued": false, "indices": ["Textual", "Ordenado"], "description": "Indica o cpf do usurio que criou o portal. Auxilia a listar os portais de cada usurio"}}, {"group": {"content": [{"field": {"name": "nm_base", "datatype": "Text", "required": true, "alias": "Nome da Base", "multivalued": false, "indices": ["Textual"], "description": "Contm o nome da base, deve ser o mesmo nome com o qual a base est salva no lightbase. Ser usado para auxiliar nas pesquisas feitas pelo portal."}}, {"field": {"name": "ds_base", "datatype": "Text", "required": true, "alias": "Apelido da Base", "multivalued": false, "indices": ["Textual"], "description": "Este campo informa um apelido, possibilitando exibir um nome amigvel no Portal de Pesquisas."}}, {"field": {"name": "url_index", "datatype": "Url", "required": false, "alias": "Url do Indexador", "multivalued": false, "indices": ["Textual"], "description": "Uma URL que indica o host onde ser feita a pesquisa via REST. Pode ser, por exemplo, a URL de um host com o ELastic Search instalado e com os dados indexados."}}, {"field": {"name": "url_detail", "datatype": "Url", "required": false, "alias": "URL de Detalhe", "multivalued": false, "indices": ["Textual"], "description": "Representa a URL da pgina de detalhes. O valor  oriundo da aplicaço em questo, ou seja, de acordo a aplicaço referenciada o nome da pgina pode ser DetalhesRecebidos.aspx ou DetalhesExpedido.aspx"}}, {"field": {"name": "url_app", "datatype": "Url", "required": false, "alias": "Url da aplicaço", "multivalued": false, "indices": ["Textual"], "description": "Este campo  usado para possibilitar links entre o portal e a aplicaço que utiliza esta base, como, por exemplo, abrir os detalhes de um registro pesquisado para abrir na pgina da aplicaço."}}, {"field": {"name": "nr_order", "datatype": "Integer", "required": false, "alias": "Ordem", "multivalued": false, "indices": ["Textual"], "description": "Auxilia a ordenar as abas no portal de pesquisa."}}, {"group": {"content": [{"group": {"content": [{"field": {"name": "nm_display_direct", "datatype": "Text", "required": false, "alias": "Nome exibido", "multivalued": false, "indices": ["Textual"], "description": "Contm o nome com o qual o campo deve ser exibido na tela de pesquisa direta."}}, {"field": {"name": "in_display_direct", "datatype": "Boolean", "required": false, "alias": "Exibir?", "multivalued": false, "indices": ["Textual"], "description": "Contm uma flag informando se o campo deve ser exibido na tela de pesquisa direta."}}, {"field": {"name": "nm_type_control_direct", "datatype": "Text", "required": false, "alias": "Tipo de controle", "multivalued": false, "indices": ["Textual"], "description": "Define o controle que deve ser carregado para o campo na tela de pesquisa direta."}}, {"field": {"name": "nr_position_direct", "datatype": "Integer", "required": false, "alias": "Posiço", "multivalued": false, "indices": ["Textual"], "description": "Contm um inteiro informando a posiço do campo na tela de pesquisa direta."}}, {"field": {"name": "script_direct", "datatype": "TextArea", "required": false, "alias": "script", "multivalued": false, "indices": ["Textual"], "description": "Contm um script que ser executado no momento que o campo for exibido na tela de pesquisa direta."}}], "metadata": {"multivalued": false, "alias": "Informaçes busca direta", "name": "inf_direct_search", "description": "Contm informaçes de como o campo se comporta na tela de busca direta."}}}, {"group": {"content": [{"field": {"name": "nm_display_advanced", "datatype": "Text", "required": false, "alias": "Nome exibido", "multivalued": false, "indices": ["Textual"], "description": "Contm o nome com o qual o campo deve ser exibido na tela de pesquisa avançada."}}, {"field": {"name": "in_display_advanced", "datatype": "Boolean", "required": false, "alias": "Exibir?", "multivalued": false, "indices": ["Textual"], "description": "Contm uma flag informando se o campo deve ser exibido na tela de pesquisa avançada."}}, {"field": {"name": "in_fixed_advanced", "datatype": "Boolean", "required": false, "alias": "Campo fixo?", "multivalued": false, "indices": ["Textual"], "description": "Contm uma flag informando se o campo  fixo na tela de pesquisa avançada. Nesta tela os campo so escolhidos dinamicamente e inseridos como argumento de pesquisa, mas se o campo for fixo ela j fica fixado na tela."}}, {"field": {"name": "nm_type_control_advanced", "datatype": "Text", "required": false, "alias": "Tipo de Controle", "multivalued": false, "indices": ["Textual"], "description": "Define o controle que deve ser carregado na pesquisa avançada para exibir este campo."}}, {"field": {"name": "script_advanced", "datatype": "TextArea", "required": false, "alias": "script", "multivalued": false, "indices": ["Textual"], "description": "Contm um script para ser executado na pgina nome momento em que o campo  exibido."}}], "metadata": {"multivalued": false, "alias": "Informaçes pesquisa avançada", "name": "inf_advanced_search", "description": "Contm informaçes que definem como o campo se comporta na tela de pesquisa avançada."}}}, {"group": {"content": [{"field": {"name": "nm_display_listed", "datatype": "Text", "required": false, "alias": "Nome exibido", "multivalued": false, "indices": ["Textual"], "description": "Contm o nome que deve exibido na coluna da tabela resultado de pesquisa."}}, {"field": {"name": "in_select_listed", "datatype": "Boolean", "required": false, "alias": "Recuperar?", "multivalued": false, "indices": ["Textual"], "description": "Contm uma flag informando se o campo deve ser selecionado no resultado de pesquisa, ou seja, recuperado nas consultas."}}, {"field": {"name": "in_display_listed", "datatype": "Boolean", "required": false, "alias": "Exibir?", "multivalued": false, "indices": ["Textual"], "description": "Contm uma flag informando se o campo deve ser exibido no resultado de pesquisa"}}, {"field": {"name": "in_search_listed", "datatype": "Boolean", "required": false, "alias": "Pesquisar?", "multivalued": false, "indices": ["Textual"], "description": "Contm uma flag informando se o campo  usado na pesquisa. Essa informaço  relevante para a pesquisa direta, que realiza a busca nos campos que contm essa flag marcada com true."}}, {"field": {"name": "nr_position_listed", "datatype": "Integer", "required": false, "alias": "Posiço", "multivalued": false, "indices": ["Textual"], "description": "Defina a posiço da coluna na tabela de resultado de pesquisa."}}, {"field": {"name": "script_listed", "datatype": "TextArea", "required": false, "alias": "script", "multivalued": false, "indices": ["Textual"], "description": "Contm um script que ser executado na exibiço do campo na tabela de resultado de pesquisa.  til para criar botes, links, etc."}}, {"field": {"name": "in_sortable_listed", "datatype": "Boolean", "required": false, "alias": "Ordenar?", "multivalued": false, "indices": ["Textual"], "description": "Indica se a coluna  ordenvel na tabela do resultado de pesquisa."}}], "metadata": {"multivalued": false, "alias": "Informaçes resultado de pesquisa", "name": "inf_listed_search", "description": "Contm as informaçes do campo de como se comportar na pesquisa listada."}}}, {"group": {"content": [{"field": {"name": "nm_display_detailed", "datatype": "Text", "required": false, "alias": "Nome exibido", "multivalued": false, "indices": ["Textual"], "description": "contm o nome com o qual o campo deve ser exibido na tela de detalhes."}}, {"field": {"name": "in_display_detailed", "datatype": "Boolean", "required": false, "alias": "Exibir?", "multivalued": false, "indices": ["Textual"], "description": "Contm uma flag dizendo se o campo  exibido ou no tela de detalhes."}}, {"field": {"name": "in_search_detailed", "datatype": "Boolean", "required": false, "alias": "Pesquisar?", "multivalued": false, "indices": ["Textual"], "description": "Contm uma flag informando se o campo  pesquisado para chamar a pgina de detalhes."}}, {"field": {"name": "nr_position_detailed", "datatype": "Integer", "required": false, "alias": "Posiço", "multivalued": false, "indices": ["Textual"], "description": "Contm um inteiro informando a posiço do campo na tela de detalhes."}}, {"field": {"name": "script_detailed", "datatype": "TextArea", "required": false, "alias": "Script", "multivalued": false, "indices": ["Textual"], "description": "Contm um script que deve ser executado na exibiço do campo na pgina de detalhes. Por exemplo, criar um link dentro campo."}}], "metadata": {"multivalued": false, "alias": "Informaçes da tela de detalhes", "name": "inf_detailed_search", "description": "Contm as informaçes de como o campo se comporta na tela de detalhes. Caso esta tela seja tratada no portal, pois ela pode ser de outra aplicaço e neste caso usasse o campo url_detail."}}}, {"group": {"content": [{"field": {"name": "data_tabulated", "datatype": "Json", "required": false, "alias": "Lista de dados", "multivalued": false, "indices": ["Textual"], "description": "Contm uma lista de objetos json. Serve para criar uma tabela esttica com os campo tabelados. Por exemplo, uma tabela de siglas e nomes de estados."}}, {"field": {"name": "nm_field_value_tabulated", "datatype": "Text", "required": false, "alias": "Nome do campo valor", "multivalued": false, "indices": ["Textual"], "description": "Contm o nome do campo da base referenciada que contm o valor."}}, {"field": {"name": "nm_field_key_tabulated", "datatype": "Text", "required": false, "alias": "Nome Campo Chave", "multivalued": false, "indices": ["Textual"], "description": "Contm o nome do campo da base referenciada que contm a chave do valor."}}, {"field": {"name": "relational_key_tabulated", "datatype": "Text", "required": false, "alias": "Chave relacional", "multivalued": false, "indices": ["Textual"], "description": "Contm o nome da coluna que contm a chave da relaço.  o campo referenciado na base."}}, {"field": {"name": "nm_table_tabulated", "datatype": "Text", "required": false, "alias": "Nome da tabela referenciada", "multivalued": false, "indices": ["Textual"], "description": "Contm o nome da tabela que est sendo usada para tabelar o campo."}}], "metadata": {"multivalued": false, "alias": "Informaçes Campo Tabelado", "name": "inf_field_tabulated", "description": "Define as informaçes de campos tabelados. S precisa ser preenchido se o tipo de campo for igual a tabulated."}}}, {"field": {"name": "groups_can_view", "datatype": "Text", "required": false, "alias": "Grupos", "multivalued": true, "indices": ["Textual"], "description": "Define os grupos que podem visualizar o campo."}}, {"field": {"name": "ds_field", "datatype": "Text", "required": false, "alias": "Apelido do campo", "multivalued": false, "indices": ["Textual"], "description": "Define o apelido do campo.  um nome mais amigvel para ser exibido no portal."}}, {"field": {"name": "nm_field", "datatype": "Text", "required": true, "alias": "Nome do campo", "multivalued": false, "indices": ["Textual"], "description": "Nome do campo da base. Deve coincidir com o nome do campo que est salvo no lightbase."}}, {"field": {"name": "nm_type_field", "datatype": "Text", "required": true, "alias": "Tipo de campo", "multivalued": false, "indices": ["Textual"], "description": "Indica qual o tipo de campo.  utilizado pelo portal para saber como exibir o campo.  oriundo dos tipos de campo que o lightbase usa para criar campos."}}], "metadata": {"multivalued": true, "alias": "Campo", "name": "field", "description": "Representa os campos da base. Informa onde e como sero usados pelo portal."}}}], "metadata": {"multivalued": true, "alias": "Bases do Portal", "name": "bases", "description": "Contm as Bases do portal."}}}], "metadata": {"idx_exp": false, "description": "Novo conceito do portal. Uma base _portal lista todos os portais separados por nome e com o id do usu-ario que criou.", "color": "", "file_ext_time": 0, "dt_base": "01/01/2015 00:00:00", "idx_exp_url": "", "file_ext": false, "password": "BRLight@)!$", "id_base": ID_BASE_PLACEHOLDER, "name": "_portal", "idx_exp_time": 0, "model": {"alias_portal": "Text", "cpf_user": "Text", "bases": [{"url_index": "Url", "nm_base": "Text", "field": [{"inf_advanced_search": {"in_fixed_advanced": "Boolean", "in_display_advanced": "Boolean", "nm_type_control_advanced": "Text", "nm_display_advanced": "Text", "script_advanced": "TextArea"}, "inf_listed_search": {"in_search_listed": "Boolean", "nm_display_listed": "Text", "in_select_listed": "Boolean", "in_sortable_listed": "Boolean", "nr_position_listed": "Integer", "script_listed": "TextArea", "in_display_listed": "Boolean"}, "inf_field_tabulated": {"nm_table_tabulated": "Text", "nm_field_key_tabulated": "Text", "relational_key_tabulated": "Text", "data_tabulated": "Json", "nm_field_value_tabulated": "Text"}, "inf_direct_search": {"nr_position_direct": "Integer", "nm_display_direct": "Text", "script_direct": "TextArea", "nm_type_control_direct": "Text", "in_display_direct": "Boolean"}, "groups_can_view": ["Text"], "ds_field": "Text", "nm_type_field": "Text", "nm_field": "Text", "inf_detailed_search": {"nm_display_detailed": "Text", "in_search_detailed": "Boolean", "in_display_detailed": "Boolean", "script_detailed": "TextArea", "nr_position_detailed": "Integer"}}], "url_detail": "Url", "url_app": "Url", "nr_order": "Integer", "ds_base": "Text"}], "nm_portal": "Text", "ds_portal": "Text"}}}',TO_DATE('2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),false,'',0,false,0);

UPDATE lb_base SET struct = replace(struct, 'ID_BASE_PLACEHOLDER', cast(id_base as text)) WHERE name='_portal';

/* ------ */

INSERT INTO public.lb_base (name,struct,dt_base,idx_exp,idx_exp_url,idx_exp_time,file_ext,file_ext_time) VALUES (
'_user','{"content": [{"field": {"alias": "id", "description": "LightBase''s uses ID", "name": "id_user", "datatype": "Integer", "indices": ["Textual", "Ordenado"], "required": true, "multivalued": false}}, {"field": {"alias": "name", "description": "User''s name", "name": "name_user", "datatype": "Text", "indices": ["Textual", "Ordenado"], "required": true, "multivalued": false}}, {"field": {"alias": "email", "description": "User''s mail", "name": "email_user", "datatype": "Text", "indices": ["Textual", "Ordenado"], "required": true, "multivalued": false}}, {"field": {"alias": "passwd", "description": "User''s password", "name": "passwd_user", "datatype": "Text", "indices": ["Textual", "Ordenado"], "required": true, "multivalued": false}}, {"group": {"content": [{"field": {"alias": "name_base", "description": "Name of the base the user can access", "name": "name_base", "datatype": "Text", "indices": ["Textual", "Ordenado", "Fuzzy"], "required": false, "multivalued": false}}, {"field": {"alias": "access_type", "description": "Type of access the user has", "name": "access_type", "datatype": "Text", "indices": ["Textual", "Ordenado"], "required": false, "multivalued": false}}], "metadata": {"alias": "bases", "description": "List of bases that the user can access and what kind of access it is", "multivalued": true, "name": "bases_user"}}}, {"field": {"alias": "creation_date", "description": "Date the user account was created", "name": "creation_date_user", "datatype": "Date", "indices": ["Textual", "Ordenado"], "required": true, "multivalued": false}}, {"field": {"alias": "status", "description": "Check if the user is activer or not", "name": "status_user", "datatype": "Boolean", "indices": ["Textual", "Ordenado"], "required": true, "multivalued": false}}], "metadata": {"idx_exp": false, "description": "LightBase''s Users Meta Base.", "color": "#000000", "file_ext_time": 0, "idx_exp_time": 0, "idx_exp_url": "", "model": {"name_user": "Text", "status_user": "Boolean", "bases_user": [{"name_base": "Text", "access_type": "Text"}], "id_user": "Integer", "creation_date_user": "Date", "email_user": "Text", "passwd_user": "Text"}, "password": "3Ax!vj6gV#DEtR", "dt_base": "01/01/2015 00:00:00", "file_ext": false, "id_base": ID_BASE_PLACEHOLDER, "name": "_user"}}',TO_DATE('2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),false,'',0,false,0);

UPDATE lb_base SET struct = replace(struct, 'ID_BASE_PLACEHOLDER', cast(id_base as text)) WHERE name='_user';

/* ------ */

INSERT INTO public.lb_base (name,struct,dt_base,idx_exp,idx_exp_url,idx_exp_time,file_ext,file_ext_time) VALUES (
'log_lbconverter','{"content": [{"field": {"name": "nm_base", "datatype": "Text", "required": true, "alias": "Nome da base", "multivalued": false, "indices": ["Ordenado"], "description": "Nome da base"}}, {"field": {"name": "id_doc_orig", "datatype": "Integer", "required": true, "alias": "id_doc_orig", "multivalued": false, "indices": ["Textual", "Ordenado"], "description": "id do documento que originou o erro."}}, {"field": {"name": "id_file_orig", "datatype": "Text", "required": true, "alias": "id_file_orig", "multivalued": false, "indices": ["Textual", "Unico"], "description": "ID do arquivo que originou o erro."}}, {"field": {"name": "file_name", "datatype": "Text", "required": true, "alias": "file_name", "multivalued": false, "indices": ["Ordenado"], "description": "File name"}}, {"field": {"name": "error_msg", "datatype": "Text", "required": true, "alias": "Mensagem de erro", "multivalued": false, "indices": ["Nenhum"], "description": "Mensagem de erro"}}, {"field": {"name": "dt_error", "datatype": "DateTime", "required": true, "alias": "Data do erro", "multivalued": false, "indices": ["Textual", "Ordenado"], "description": "Data do erro"}}], "metadata": {"idx_exp": false, "description": "LightBase - Log de erros do LBConverter", "color": "#000000", "file_ext_time": 0, "dt_base": "01/01/2015 00:00:00", "idx_exp_url": "", "file_ext": false, "password": "qqqqqqqq", "id_base": ID_BASE_PLACEHOLDER, "name": "log_lbconverter", "idx_exp_time": 0, "model": {"id_file_orig": "Text", "nm_base": "Text", "file_name": "Text", "id_doc_orig": "Integer", "dt_error": "DateTime", "error_msg": "Text"}}}',TO_DATE('2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),false,'',0,false,0);

UPDATE lb_base SET struct = replace(struct, 'ID_BASE_PLACEHOLDER', cast(id_base as text)) WHERE name='log_lbconverter';

/* ------ */

INSERT INTO public.lb_base (name,struct,dt_base,idx_exp,idx_exp_url,idx_exp_time,file_ext,file_ext_time) VALUES (
'log_lbindex','{"content": [{"field": {"name": "nm_base", "datatype": "Text", "required": true, "alias": "Nome da base", "multivalued": false, "indices": ["Ordenado"], "description": "Nome da base"}}, {"field": {"name": "id_doc_orig", "datatype": "Integer", "required": true, "alias": "identificador do documento", "multivalued": false, "indices": ["Textual", "Ordenado"], "description": "id do documento que originou o erro."}}, {"field": {"name": "error_msg", "datatype": "Text", "required": true, "alias": "Mensagem de erro", "multivalued": false, "indices": ["Nenhum"], "description": "Mensagem de erro"}}, {"field": {"name": "dt_error", "datatype": "DateTime", "required": true, "alias": "Data do erro", "multivalued": false, "indices": ["Textual", "Ordenado"], "description": "Data e Hora no formato DD/MM/AAAA - HH:MM:SS do erro"}}, {"field": {"name": "dt_last_up_orig", "datatype": "DateTime", "required": true, "alias": "dt_last_up_orig", "multivalued": false, "indices": ["Textual", "Ordenado"], "description": "Data e Hora no formato DD/MM/AAAA - HH:MM:SS da última atualização do registro que originou o erro."}}], "metadata": {"idx_exp": false, "description": "LightBase - Log de erros do LBIndex", "color": "#000000", "file_ext_time": 0, "dt_base": "01/01/2015 00:00:00", "idx_exp_url": "", "file_ext": false, "password": "qqqqqqqq", "id_base": ID_BASE_PLACEHOLDER, "name": "log_lbindex", "idx_exp_time": 0, "model": {"dt_last_up_orig": "DateTime", "id_doc_orig": "Integer", "dt_error": "DateTime", "error_msg": "Text", "nm_base": "Text"}}}',TO_DATE('2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),false,'',0,false,0);

UPDATE lb_base SET struct = replace(struct, 'ID_BASE_PLACEHOLDER', cast(id_base as text)) WHERE name='log_lbindex';

/* ------ */

/* ----------------------------------- */
/* _app_config */
/* ------------- */
/* [OK] */

CREATE TABLE lb_doc__app_config
(
  id_doc serial NOT NULL,
  document json NOT NULL,
  dt_doc timestamp without time zone NOT NULL,
  dt_last_up timestamp without time zone NOT NULL,
  dt_del timestamp without time zone,
  dt_idx timestamp without time zone,
  nm_user_alteracao character varying[],
  nm_apelido character varying,
  nm_aplicacao character varying,
  CONSTRAINT lb_doc__app_config_pkey PRIMARY KEY (id_doc)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_doc__app_config
  OWNER TO postgres;

/* ------ */
/* [OK] */

CREATE TABLE lb_file__app_config
(
  id_file uuid NOT NULL,
  id_doc integer,
  filename character varying NOT NULL,
  file bytea NOT NULL,
  mimetype character varying NOT NULL,
  filesize integer NOT NULL,
  filetext character varying,
  dt_ext_text timestamp without time zone,
  CONSTRAINT lb_file__app_config_pkey PRIMARY KEY (id_file)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_file__app_config
  OWNER TO postgres;

/* ----------------------------------- */
/* _history */
/* ------------- */
/* [OK] */

CREATE TABLE lb_doc__history
(
  id_doc serial NOT NULL,
  document json NOT NULL,
  dt_doc timestamp without time zone NOT NULL,
  dt_last_up timestamp without time zone NOT NULL,
  dt_del timestamp without time zone,
  dt_idx timestamp without time zone,
  CONSTRAINT lb_doc__history_pkey PRIMARY KEY (id_doc)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_doc__history
  OWNER TO postgres;

/* ------ */
/* [OK] */

CREATE TABLE lb_file__history
(
  id_file uuid NOT NULL,
  id_doc integer,
  filename character varying NOT NULL,
  file bytea NOT NULL,
  mimetype character varying NOT NULL,
  filesize integer NOT NULL,
  filetext character varying,
  dt_ext_text timestamp without time zone,
  CONSTRAINT lb_file__history_pkey PRIMARY KEY (id_file)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_file__history
  OWNER TO postgres;

/* ----------------------------------- */
/* _portal */
/* ------------- */
/* [OK] */

CREATE TABLE lb_doc__portal
(
  id_doc serial NOT NULL,
  document json NOT NULL,
  dt_doc timestamp without time zone NOT NULL,
  dt_last_up timestamp without time zone NOT NULL,
  dt_del timestamp without time zone,
  dt_idx timestamp without time zone,
  cpf_user character varying,
  nm_portal character varying,
  CONSTRAINT lb_doc__portal_pkey PRIMARY KEY (id_doc)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_doc__portal
  OWNER TO postgres;

/* ------ */
/* [OK] */

CREATE TABLE lb_file__portal
(
  id_file uuid NOT NULL,
  id_doc integer,
  filename character varying NOT NULL,
  file bytea NOT NULL,
  mimetype character varying NOT NULL,
  filesize integer NOT NULL,
  filetext character varying,
  dt_ext_text timestamp without time zone,
  CONSTRAINT lb_file__portal_pkey PRIMARY KEY (id_file)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_file__portal
  OWNER TO postgres;

/* ----------------------------------- */
/* _user */
/* ------------- */
/* [OK] */

CREATE TABLE lb_doc__user
(
  id_doc serial NOT NULL,
  document json NOT NULL,
  dt_doc timestamp without time zone NOT NULL,
  dt_last_up timestamp without time zone NOT NULL,
  dt_del timestamp without time zone,
  dt_idx timestamp without time zone,
  name_base character varying[],
  id_user integer,
  status_user boolean,
  access_type character varying[],
  name_user character varying,
  creation_date_user date,
  email_user character varying,
  passwd_user character varying,
  CONSTRAINT lb_doc__user_pkey PRIMARY KEY (id_doc)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_doc__user
  OWNER TO postgres;

/* ------ */
/* [OK] */

CREATE TABLE lb_file__user
(
  id_file uuid NOT NULL,
  id_doc integer,
  filename character varying NOT NULL,
  file bytea NOT NULL,
  mimetype character varying NOT NULL,
  filesize integer NOT NULL,
  filetext character varying,
  dt_ext_text timestamp without time zone,
  CONSTRAINT lb_file__user_pkey PRIMARY KEY (id_file)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_file__user
  OWNER TO postgres;

/* ----------------------------------- */
/* lb_index_error */
/* ------------- */
/* [OK] */

CREATE TABLE lb_index_error
(
  id_error serial NOT NULL,
  base character varying NOT NULL,
  id_doc integer NOT NULL,
  dt_error timestamp without time zone NOT NULL,
  msg_error character varying,
  CONSTRAINT lb_index_error_pkey PRIMARY KEY (id_error)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_index_error
  OWNER TO postgres;

/* ----------------------------------- */
/* log_lbindex */
/* ------------- */
/* [OK] */

CREATE TABLE lb_doc_log_lbindex
(
  id_doc serial NOT NULL,
  document json NOT NULL,
  dt_doc timestamp without time zone NOT NULL,
  dt_last_up timestamp without time zone NOT NULL,
  dt_del timestamp without time zone,
  dt_idx timestamp without time zone,
  id_doc_orig integer,
  dt_last_up_orig timestamp without time zone,
  nm_base character varying,
  dt_error timestamp without time zone,
  CONSTRAINT lb_doc_log_lbindex_pkey PRIMARY KEY (id_doc)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_doc_log_lbindex
  OWNER TO postgres;

/* ------ */
/* [OK] */

CREATE TABLE lb_file_log_lbindex
(
  id_file uuid NOT NULL,
  id_doc integer,
  filename character varying NOT NULL,
  file bytea NOT NULL,
  mimetype character varying NOT NULL,
  filesize integer NOT NULL,
  filetext character varying,
  dt_ext_text timestamp without time zone,
  CONSTRAINT lb_file_log_lbindex_pkey PRIMARY KEY (id_file)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_file_log_lbindex
  OWNER TO postgres;

/* ----------------------------------- */
/* log_lbconverter */
/* ------------- */
/* [OK] */

CREATE TABLE lb_doc_log_lbconverter
(
  id_doc serial NOT NULL,
  document json NOT NULL,
  dt_doc timestamp without time zone NOT NULL,
  dt_last_up timestamp without time zone NOT NULL,
  dt_del timestamp without time zone,
  dt_idx timestamp without time zone,
  file_name character varying,
  id_doc_orig integer,
  nm_base character varying,
  dt_error timestamp without time zone,
  CONSTRAINT lb_doc_log_lbconverter_pkey PRIMARY KEY (id_doc)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_doc_log_lbconverter
  OWNER TO postgres;

/* ------ */
/* [OK] */

CREATE TABLE lb_file_log_lbconverter
(
  id_file uuid NOT NULL,
  id_doc integer,
  filename character varying NOT NULL,
  file bytea NOT NULL,
  mimetype character varying NOT NULL,
  filesize integer NOT NULL,
  filetext character varying,
  dt_ext_text timestamp without time zone,
  CONSTRAINT lb_file_log_lbconverter_pkey PRIMARY KEY (id_file)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_file_log_lbconverter
  OWNER TO postgres;

/* ----------------------------------- */
/* lb_txt_idx */
/* ------------- */
/* [OK] */

CREATE TABLE lb_txt_idx
(
  id_idx serial NOT NULL,
  nm_idx character varying NOT NULL,
  cfg_idx character varying NOT NULL,
  dt_crt_idx timestamp without time zone NOT NULL,
  dt_upt_idx timestamp without time zone NOT NULL,
  url_idx character varying NOT NULL,
  actv_idx boolean NOT NULL,
  struct character varying NOT NULL,
  CONSTRAINT lb_txt_idx_pkey PRIMARY KEY (id_idx),
  CONSTRAINT lb_txt_idx_nm_idx_key UNIQUE (nm_idx)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE lb_txt_idx
  OWNER TO postgres;
\q

/* --------------------------------------------------- */