3.5.2. How to edit the database

A .sql script will be used to edit the database. Psql instructions will be used in a .sql script to carry out the changes.

1- Depending on the tables you want to generate, you will need different data.

New User

NAME AND SURNAME

USER NAME THAT YOU WILL HAVE IN THE SYSTEM

E-MAIL

PHONE

PASSWORD

New Tenant

TENANT

New Grouping

GROUP

COMPANY ID

Company Tax Number

BUSINESS NAME

2- The passwords will be encrypted by executing the following jar file, and inputting the passwords from the terminal. The output of the jar will be the encrypted password. You can use Shift + Click in the folder containing the jar and "Open command window here" to quickly open a terminal in the folder containing the .jar in Windows

java –jar PBKDF2Generator.jar PASSWORD

3- We generate the sql script that we use to fill tables.

You can add these statements to an sql script and run the script. It is also possible to enter the psql terminal and enter the sentences directly there.

We use the INSERT INTO statement to enter new data into a table as shown below.

There may be several users, several tenants and several groupings. In the definition of the grouping the tenant to which it belongs is defined. USUARIO_AGRUPACION_REL links a user to a group and a tenant.

If we want to delete data sets in a table we can use the statement:

DELETE FROM “TABLENAME” WHERE “FIELD”= ‘FIELD VALUE’

ENVLOTDAS and ENVLOTNODAS are, respectively, shipping scenarios with Dashboard and without Dashboard.

The value of RID_ID must be:

“RT”+VALOR DE: ID_ESCENARIO+”-“+VALOR DE: ID_TENANT_FUSE+VALOR DE: ID_AGRUPACION_FUSE+”-“+VALOR DE: ID_TIPO_DOC_FUSE+”V”+NUMERO DE VERSION ACTUAL

Contents of ~/Downloads/fuse_conf/scripts_sql/GeneraciondetablasPostgreSQLv1.sql

INSERT INTO "USUARIOS_WS" ("ID_USUARIO","EMAIL","NOMBRE","TELEFONO", "PBKDF2") VALUES ('NOMBRE DE USUARIO QUE TENDRÁ EN EL SISTEMA', 'EMAIL', 'NOMBRE Y APELLIDOS', 'TELÉFONO', 'CONTRASEÑA ENCRIPTADA EN EL PASO 2');

INSERT INTO "TENANT_EXT" ("ID_TENANT_EXT","DESCRIPCION","URN") VALUES ('TENANT', 'NOMBRE DE LA EMPRESA CLIENTE', 'urn:es:techedgegroup:sii:tenant:ext:NOMBRE DEL CLIENTE(Sin espacios o mayúsculas)');

INSERT INTO "AGRUPACION_EXT" ("ID_TENANT_EXT","ID_AGRUPACION_EXT","DESCRIPCION","ID_SOCIEDAD", "NIF_SOCIEDAD", "RAZON_SOCIAL",”ID_ORIGEN”) VALUES ('TENANT', 'AGRUPACIÓN', 'DESCRIPCION AGRUPACION', 'ID DE SOCIEDAD', 'NIF DE SOCIEDAD', 'RAZON SOCIAL', ‘ID ORIGEN’);

INSERT INTO "USUARIO_AGRUPACION_REL" ("ID_TENANT_EXT","ID_AGRUPACION_EXT", "ID_USUARIO") VALUES ('TENANT','AGRUPACION','NOMBRE DEL USUARIO');

INSERT INTO "PRODUCTOS" ("ID_PRODUCTO", "DESCRIPCION", "URN") VALUES ('SIIAEAT', 'aplicativo de envio de lotes al AEAT', 'urn:es:techedge:aplicativo:sii:lotes:aeat');

INSERT INTO "ESCENARIO" ("ID_ESCENARIO", "DESCRIPCION", "URN", "ID_PRODUCTO") VALUES ('ENVLOTDAS/ENVLOTNODAS', 'Escenario de envio de lotes al AEAT con dashboard', 'urn:es:techedge:escenario:fuse:envio:lotes:aeat:dash', 'SIIAEAT');

INSERT INTO "TENANTS_FUSE" ("ID_TENANT_FUSE","DESCRIPCION") VALUES ('X**', 'Descripcion');

INSERT INTO "AGRUPACIONES_FUSE" ("ID_TENANT_FUSE","ID_AGRUPACION_FUSE","DESCRIPCION") VALUES ('X**', 'ANY', 'Desarrollo');

INSERT INTO "TIPOS_DOCUMENTO" ("ID_TIPO_DOC_FUSE","DESCRIPCION", "URN") VALUES ('SUMEMI','SuministroLRFacturasEmitidas','urn:es:techedge:documento:suministro:facturas:emitidas');

INSERT INTO "DOCUMENTOS_EXT" ("ID_TIPO_DOC_EXT","ID_VER_DOC_EXT", "XML_SCHEMA_REF", "MIME_TYPE", "DESCRIPCION") VALUES ('SuministroLRFacturasEmitidas', 'A0', 'SuministroLRFacturasEmitidas', 'text/xml', 'SuministroLRFacturasEmitidas');

INSERT INTO "CORRELACIONES" ("ID_ESCENARIO", "ID_TENANT_EXT", "ID_AGRUPACION_EXT", "ID_TIPO_DOC_EXT", "ID_VER_DOC_EXT", "ID_TENANT_FUSE", "ID_AGRUPACION_FUSE", "ID_TIPO_DOC_FUSE") VALUES ('ENVLOTDAS/ENVLOTNODAS', 'TENANT', 'AGRUPACIÓN', 'SuministroLRFacturasEmitidas', 'A0', 'X**', 'ANY', 'SUMEMI');

INSERT INTO "RUTAS" ("ID_RUTA","DESCRIPCION", "PATH_FICH_CONFIG") VALUES ('RTENVLOTDAS-X02ANY-SUMEMI-V1', 'Ruta de envio de lotes para b+dgtal Desarrollo', NULL);

INSERT INTO "PASOS_RUTA" ("ID_RUTA","ID_SECUENCIA","ID_MODULO","PATH_FICH_CONFIG") VALUES ('RTENVLOTDAS-X02ANY-SUMEMI-V1', '1', 'STP-COM-SUMEMI', NULL);

INSERT INTO "CALCULO_RUTA" ("ID_ESCENARIO", "ID_TENANT_FUSE", "ID_AGRUPACION_FUSE", "ID_TIPO_DOC_FUSE", "ID_RUTA") VALUES ('ENVLOTDAS/ENVLOTNODAS', 'X**', 'ANY', 'SUMEMI', 'RTENVLOTDAS-X02ANY-SUMEMI-V1');