[[post_data {
    "author" : "KyMAN",
    "since" : 20210818,
    "version" : 20230308
}]]
# SQL

Esta sección se encargará de la gestión SQL del proyecto. Para este ámbito se tratará una parte
común que viene siendo la estructura de datos interna.

> [[! note NOTA]]: Somos conscientes que cada motor SQL fue concevido para un fin concreto, sin
embargo, aquí los usaremos para fines de universalización y gestión en entornos.

Antes de empezar, es importante destacar que los lenguajes SQL que vamos a utilizar se pueden
dividir en dos grupos, y cada cual está orientado a fines de globalización según su ámbito, los
cuales son:

- **Centralizados**:
    - **MariaDB/MySQL**: Sistemas Open Source basados en dicha tecnología.
    - **SQL Server**: Para entornos Microsoft.
- **Locales**:
    - **SQLite**: Para aplicaciones de entorno de escritorio, local o de usuario concreto.

## Estructura de cada dato

La estructura de los datos básicos que se gestionarán dentro del AnP es un poco compleja por lo que
se utilizarán varias estructuras que se irán uniendo. Las estructuras de datos se basarán en un
entorno relacional y una ayuda de rendimiento de datos planos. Todos los datos, a excepción de unos
pocos, se basarán en la misma estructura sintáctica que consta de las siguientes entidades:

- **Entidad de identificación**: Entidad que se encarga de identificar al dato concreto.
- **Entidad de datos**: Entidad que gestiona los históricos de cambios de datos universales de una
entidad.
- **Entidad de textos**: Entidad que gestiona los históricos de textos internacionalizados de una
entidad.
- **Entidad plana**: Entidad que almacena los datos actuales de una entidad, sin vinculación ni
filtro dentro de los históricos.

<table style="width:100%;">
    <thead>
        <tr>
            <th>Completo</th>
            <th>Datos</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>

```mermaid
erDiagram

Entidad {
    integer id
    datetime date_in
    datetime date_out
}

EntidadData {
    integer id
    integer session
    integer entidad
    varchar i18n
    datetime deleted
    datetime date_in
    datetime date_out
}

EntidadText {
    integer id
    integer session
    integer entidad
    integer language
    datetime date_in
    datetime date_out
}

EntidadPlain {
    integer id
    integer entidad
    integer data_id
    integer data_session
    integer text_id
    integer text_session
    integer language
    datetime deleted
    datetime data_date_in
    datetime data_date_out
    datetime text_date_in
    datetime text_date_out
    datetime date_in
    datetime date_out
}

Entidad ||--|{ EntidadData : tiene
Entidad ||--|{ EntidadText : tiene
Entidad ||--|{ EntidadPlain : es

```

            </td>
            <td>

```mermaid
erDiagram

Entidad {
    integer id
    datetime date_in
    datetime date_out
}

EntidadData {
    integer id
    integer session
    integer entidad
    datetime deleted
    datetime date_in
    datetime date_out
}

EntidadPlain {
    integer id
    integer entidad
    integer data_id
    integer data_session
    datetime deleted
    datetime data_date_in
    datetime data_date_out
    datetime date_in
    datetime date_out
}

Entidad ||--|{ EntidadData : tiene
Entidad ||--|{ EntidadPlain : es

```

            </td>
        </tr>
    </tbody>
</table>

> [[! important IMPORTANTE]]: Las entidades planas irán encabezadas por guión bajo y no contendrán
su sufijo "Plain". Aquí se puso de esta forma por incompatibilidad de los guiones bajos con el
MermaidJS, lo cual queda para posibles entornos con el mismo problema.

Sabiendo esto, cualquier dato que se exponga se basará en este sistema a excepción de algunas
estructuras tales como Logs, Trazas, Excepciones o Sesiones por el hecho de ser datos inamovibles
que no pueden contener histórico de cambios por falta de éstos.

Con respecto a las tablas que no dependen de una estructura histórica, internamente todas contendrán
un valor Integer como ID y otros dos Datetime para la fecha de alta y fecha de baja tal que así:

```mermaid
erDiagram

Table {
    integer id
    datetime date_in
    datetime date_out
}

```

## Estructura de los datos

Aquí nos vamos a encontrar con diferentes subestructuras que se unificarán en una sola estructura, y
hay que tener encuenta que cada dato que aparece en esta estructura va a estar condicionada a una de
las estructuras anteriores si o a ninguna (Dato inamovible) según se especifica. Para empezar con
esta sección, nos basaremos en el orden de prioridad de las subestructuras.

### Sesiones

Las sesiones gestionarán tanto los usuarios como las direcciones desde las cuales se conectan así
como el monitoreo de las acciones que hacen, desde donde lo hacen y cuando lo hacen. La estructura
de datos es la siguiente:

```mermaid
erDiagram

Users {
    varchar nick
    varchar password
    boolean accessible
}

Ips {
    varchar address
    boolean accessible
}

Sessions {
    integer id
    integer user
    integer ip
    datetime date_in
    datetime date_last
    datetime date_out
}

Sessions }o--|| Users : belong
Sessions }o--|| Ips : belong

```

Por defecto, los usuarios tiene una contraseña la cual estará hasheada.

En dichos usuarios encontraremos dos usuarios especiales los cuales son "guest" y "machine" donde el
primero es el usuario por defecto para usuarios invitados mientras que el segundo es un usuario Bot
que gestiona cosas internas de la base de datos y funcionamiento interno del programa. Dicho usuario
no es manejable por ningún usuario. También nos encontraremos una dirección IP local en IPv6 la cual
es "::1" que representa conexiones locales. Con todos estos datos se creará una sesión fija que
gestionará el Bot "machine".

Tanto el usuario "machine" como "guest" no tendrán contraseña (Sin contraseña no pueden ser
iniciados) ni tendrán permisos de uso inicialmente.

Los datos de las sesiones no se gestionarán como datos planos sino como una vista que concatena los
datos de las sesiones con los datos planos actuales de los usuarios y las Ips.

### Grupos

Los grupos son usados para gestionar elementos comunes como la configuración y los permisos de la
base de datos contra usuarios y direcciones de conexión. Su estructura sería la siguiente:

```mermaid
erDiagram

Groups {
    varchar name
    varchar description
    boolean owner
}

GroupsUsers {
    integer group
    integer user
    boolean belong
}

GroupsIps {
    integer group
    integer ip
    boolean belong
}

GroupsUsersBelong {
    integer group
    integer user
}

GroupsIpsBelong {
    integer group
    integer ip
}

Groups ||--o{ GroupsUsers : in
Groups ||--o{ GroupsIps : in
Users ||--|{ GroupsUsers : in
Ips ||--|{ GroupsIps : in
GroupsUsers ||--o| GroupsUsersBelong : is
GroupsIps ||--o| GroupsIpsBelong : is
Session }o..o{ GroupsUsersBelong : in
Session }o..o{ GroupsIpsBelong : in

```

El nombre de los grupos no son un elemento internacionalizado por lo que sólo constrarán de la tabla
de histórico de datos.

Para la gestión de cualquier cosa que vaya dirigido a un usuario o IP concreta, o un conjunto de
éstos, se usarán los grupos, que a partir de su pertenencia, se usará el propio nombre (campo name
del grupo) para determinar lo que hiciere necesario fuera de la base de datos, o el propio ID del
campo para referenciar internamente en la base de datos.

Por defecto, cada vez que se cree un usuario o IP concreta, éste tendrá su grupo propio, el cual a
posteriori podría ser compartido, a mayores de poder pertenecer a otros grupos que interesen. Los
grupos que son de un usuario o IP concreto irán identificados por el campo "owner" a "true".

> [[! note NOTA]]: Los grupos son un condicionante a la hora de elegir un nombre de un usuario
puesto que si ya existe, éste no puede ser escogido, lo mismo que si ya existe como usuario, el
grupo no puede ser creado.

> [[! important IMPORTANTE]]: Los grupos pueden afectar al registro de IPs por lo que se aconseja no
hacer nunca uso de ningún nombre que pueda hacer conflicto con una dirección IP ni similares como
"localhost".

### Configuraciones

Las configuraciones de cada usuario se almacenarán a nivel de datos y pueden ser usados en cualquier
nivel del AnP (Cliente, servidor y datos). Las configuraciones se basarán en los grupos, teniendo
prioridad sobre el Owner del usuario. Han de resolverse sobre una única vista la cual determine la
tabla de configuración plana de cada usuario fuera de los valores globales.

```mermaid
erDiagram

SettingsTypes {
    varchar name
    varchar description
}

Settings {
    integer type
    varchar name
    varchar description
    varchar default
}

SettingsValues {
    integer id
    integer session
    integer settings
    varchar value
    datetime date_in
    datetime date_out
}

SettingsUsers {
    integer id
    integer session
    integer settings
    integer user
    varchar value
    datetime date_in
    datetime date_out
}

SettingsValuesPlain {
    integer id
    integer settings
    integer type_id
    integer value_id
    varchar value
    varchar type
    datetime settings_date_in
    datetime settings_date_out
    datetime date_in
    datetime date_out
}

SettingsUsersPlain {
    integer id
    integer settings
    integer type_id
    integer value_id
    integer user
    varchar value
    varchar type
    datetime settings_date_in
    datetime settings_date_out
    datetime date_in
    datetime date_out
}

Settings }o--|| SettingsTypes : is
Settings ||--o{ SettingsValues : has
Settings ||--o{ SettingsUsers : has
Users ||--o{ SettingsUsers : has
SettingsValues ||..|| SettingsValuesPlain : is
SettingsUsers ||..|| SettingsUsersPlain : is

```

Las configuraciones no van por los grupos puesto que no son más que usadas o de forma global o por
parte de cada usuario. Las configuraciones son diferentes al ámbito de los permisos por lo que se
trabajan independientemente.

Por la misma premisa que en el párrafo anterior, las configuraciones poseerán dos tablas planas
customizadas que permiten acceder de forma rápida a las configuraciones propias de cada usuario, o
en el caso de ser de ámbito global, a dichas configuraciones.

Para establecer en las tablas planas el valor de configuración se analizar primero si tienen algún
valor para un usuario en caso de ser para dicho usuario, o valor global si son globales, donde en
caso de tener más de un valor siempre se cogerá el último; pero en caso de no tener, se cogerá el
valor por defecto establecido en el propio parámetro de configuración.

Con respecto a la I18N, aun a pesar de ser un elemento internacionalizado, se usaré su propio nombre
como valor I18N para ser representado en un entorno cliente, por lo que la tabla de textos se omite
a nivel de datos.

### Permisos

Esta sección se encarga de gestionar los permisos de los usuarios principalmente, aunque afecta
también directamente a las direcciones IP. Los permisos solo se pueden otorgar, no se pueden
denegar, puesto que aunque deniegues a un grupo un permiso concreto, si éste pertenece a otro grupo,
éste será utilizado desde éste; y se tomarán como los permisos de un usuario aquellos que salgan del
conjunto total de los grupos a los que pertenece.

```mermaid
erDiagram

Permissions {
    varchar name
    varchar description
}

PermissionsGroups {
    integer permission
    integer group
    boolean granted
}

PermissionsGroupsPlain {
    integer id
    integer session
    integer permission_group
    integer group
    integer permission
    varchar name
    datetime date_in
}

Groups ||--o{ PermissionsGroups : has
Permissions ||--o{ PermissionsGroups : has
PermissionsGroups ||--o| PermissionsGroupsView : is

```

> [[! important IMPORTANTE]]: Aunque por defecto se cuenten todos los permisos, se puede hacer un
sistema de denegación en base a un permiso que tenga un usuario a partir de la tenencia, como puede
ser un Ban o cualquier otro tipo de permiso custom para esas acciones, como por ejemplo, al tener el
permiso de "registered" uno ya no tendría permiso de acceder al formulario del Log In, sería absurdo
inicialmente.

# SQL Server

- https://learn.microsoft.com/es-es/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-ver16

Para instalar el SQL Server Connector en Linux es importante instalar el Driver 
"sqlsrv". Al ir por PDO también es importante instalar el Driver "pdo_sqlsrv". 
Ésto puede llevarse a cabo a partir de los siguientes comandos Linux:

```sh
# REQUIERE DE LAS HERRAMIENTAS DE MICROSOFT QUE HAY EN EL SIGUIENTE PUNTO.
sudo apt install php-pear php-dev unixodbc-dev

# IMPORTANTE: Antes de este bloque, probar a partir de la línea "AHORA". Si sale que no existe el repo hacer lo siguiente.
# Por culpa de fallos en los certificados:
wget curl.se/ca/cacert.pem # En Internet saldrá a partir de HAXX, empresa Sponsor de cURL (Es redirección).
php -r "print_r(openssl_get_cert_locations());"
# Poner el Path de "default_cert_file" en el mv, en mi caso el siguiente.
sudo mv cacert.pem /usr/local/ssl/cert.pem
sudo pecl channel-update pecl.php.net
sudo pecl upgrade-all

### FALLA EN DEBIAN ###
# Por fallas en las actualizaciones de paquetería hay que Downgradear los siguientes paquetes:
sudo apt install -y --allow-downgrades odbcinst=2.3.7 odbcinst1debian2=2.3.7 unixodbc=2.3.7 unixodbc-dev=2.3.7
### FALLA EN DEBIAN ###

# AHORA
sudo pecl -d php_suffix=VERSION install sqlsrv pdo_sqlsrv

# Sólo hacerlo en las versiones las cuales se halla instalado el sqlsrv.
sudo echo -e "; priority=20\nextension=sqlsrv.so\n">/etc/php/VERSION/mods-avaliable/sqlsrv.ini
sudo echo -e "; priority=30\nextension=pdo_sqlsrv.so\n">/etc/php/VERSION/mods-avaliable/pdo_sqlsrv.ini
# IMPORTANTE: NO INTEGRAR EN PHP.INI.

### FALLA EN DEBIAN ###
# Agregar al Path SBIN.
export PATH=/sbin:$PATH
### FALLA EN DEBIAN ###

sudo phpenmod -v VERSION sqlsrv pdo_sqlsrv
# Darán un Warning las versiones no instaladas, no pasa nada.

service apache2 restart
sudo /etc/init.d/apache2 restart
```

Cambio de versión CLI de PHP:

```sh
update-alternatives --set php /usr/bin/phpVERSION
```

El String de conexión quedaría así:

```php
$connection = new PDO("sqlsrv:Server=IP,PORT;Encrypt=0;TrustServerCertificate=yes;", "USUARIO", "CONTRASEÑA");
```

> [[! important IMPORTANTE]]: Tras la instalación se puede Upgradear lo Downgradeado.

## Antiguo

Una vez instalados hay que crear el INI del Driver dentro de los Mods de la versión de PHP que vamos a trabajar, por ejemplo, podría ser la siguiente:

```txt
/etc/php/7.4/mods-avaliable
```

Una vez allí crearemos dos INI los cuales son:

<table>
    <thead>
        <tr>
            <th></th>
            <th>sqlsrv</th>
            <th>pdo_sqlsrv</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <th>Nombre</th>
            <td>sqlsrv.ini</td>
            <td>pdo_sqlsrv.ini</td>
        </tr>
        <tr>
            <th>Contenido</th>
            <td>

```sh
; priority=30
extension=pdo_sqlsrv.so
```

            </td>
            <td>

```sh
; priority=20
extension=sqlsrv.so
```

            </td>
        </tr>
    </tbody>
    <tfoot>
        <tr>
            <th></th>
            <th>sqlsrv</th>
            <th>pdo_sqlsrv</th>
        </tr>
    </tfoot>
</table>

Reiniciamos el Apache y listo.

```sh
sudo /etc/init.d/apache2 restart
```

El String Connection de SQL Server en PDO sería el siguiente:

```txt
sqlsrv:Server=SERVIDOR;Database=BASE_DE_DATOS
```

Quedando, con las variables del String Variables:

```txt
{engine}:Server={host},{port};Database={database}
```

## sqlcmd Linux executer

Para hacer ejecuciones en SQL Server desde Debian tenemos que hacer lo siguiente:

```sh
apt install curl gpg build-essential
curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor -o /usr/share/keyrings/packages.microsoft.gpg

# Ubuntu 22
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list
# Debian 11
curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list

apt update
ACCEPT_EULA=Y apt install -y msodbcsql18
ACCEPT_EULA=Y apt install -y mssql-tools18
```

Y fuera de SU:

```sh
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
```

Con sudo sería:

```sh
sudo apt install curl gpg build-essential
curl -fsSL https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/packages.microsoft.gpg

# Ubuntu 22
sudo curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list
# Debian 11
sudo curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list

sudo apt update
sudo ACCEPT_EULA=Y apt install -y msodbcsql18
sudo ACCEPT_EULA=Y apt install -y mssql-tools18

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
```

El comando sería el siguiente:

```sh
sqlcmd -S IP,PORT -U sa -P Abc123.. -i test.server.sql -C
```

[[html_data {
    "title" : "Documentación - SQL",
    "url" : "https://anp.k3y.pw/es/py/",
    "author" : "Tarsier, angelus, inoro, KyMAN",
    "since" : 20210818,
    "version" : 20230308,
    "key_words" : "anp,attach,play,attach&play,documentación,sql",
    "description" : "Documentación SQL del proyecto AnP.",
    "project" : "AnP",
    "logo" : "https://anp.k3y.pw/images/anp.png",
    "language" : "es"
}]]
