Mostra la font per a Paraulogic: claus primàries amb AUTO INCREMENT i taules relacionades

=Introducció=
*https://vilaweb.cat/paraulogic/
[[Fitxer:Paraulogic2.png | thumbnail]]
El paraulogic és un joc de paraules que s'ha popularitzat darrerament. Es tracta de cercar paraules vàlides (han d'estar en el diccionari DIEC) combinant 7 lletres, una de les quals (la del mig) és obligatòria)

Volem introduir la solució que es publica cada dia (la solució del dia anterior), en una base de dades, de la manera més ràpida i eficient possible.

=Definició de les dades=
El model de dades és bastant senzill. És una relació 1:N. Cada dia representa que és un JOC, que pot tenir N paraules. Del ''JOC'' ens interessa el dia, les lletres vàlides d'aquell dia, i el número de paraules vàlides d'aquell dia.

De la ''PARAULA'' ens interessa la col·lecció de paraules, referenciades al dia que va sortir la paraula. ''paraula'' no és una bona clau primària (una paraula pot sortir diferents dies), sinó que tenim ''id_paraula''. De la taula ''JOC'' també tenim ''id_joc'' com a clau primària. La cosa queda:

<pre>
CREATE DATABASE paraulogic DEFAULT CHARACTER SET utf8;
USE paraulogic;

DROP TABLE PARAULA;
DROP TABLE JOC;

# a SQL podem utilitzar expressions regulars
# https://www.geeksforgeeks.org/mysql-regular-expressions-regexp/
CREATE TABLE JOC (
id_joc SMALLINT AUTO_INCREMENT PRIMARY KEY,
lletres CHAR(7) NOT NULL CHECK (lletres REGEXP '[A-Z]{7}'),
total TINYINT UNSIGNED,
dia DATE
);

CREATE TABLE PARAULA (
id_paraula MEDIUMINT AUTO_INCREMENT PRIMARY KEY,
id_joc SMALLINT NOT NULL,
paraula VARCHAR(25) NOT NULL,
FOREIGN KEY (id_joc) REFERENCES JOC(id_joc) ON DELETE CASCADE ON UPDATE CASCADE
);

ALTER TABLE PARAULA ADD COLUMN num_cars TINYINT UNSIGNED;
</pre>

=Inserció de les dades=
En aquest cas ens interessa utilitzar '''AUTO_INCREMENT''', i així ens oblidem de portar el compte dels IDs. Ara bé, la paraula l'hem de relacionar correctament amb el id_joc. Ho farem de la següent manera:
<pre>
INSERT INTO JOC(lletres,total, dia) VALUES ('EGLNOTU',75,'2022/01/19');
SET @last_id = LAST_INSERT_ID();

INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'adagi',LENGTH('adagi'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'adagi',LENGTH('aganada'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'adagi',LENGTH('agnada'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'adagi',LENGTH('aguda'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aiguada',LENGTH('aiguada'));
...

INSERT INTO JOC(lletres,total, dia) VALUES ('VAEJNRT',91,'2022/01/20');
SET @last_id = LAST_INSERT_ID();

INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'arravatar',LENGTH('arravatar'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'avant',LENGTH('avant'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'avantatjar',LENGTH('avantatjar'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'avanter',LENGTH('avanter'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'avantera',LENGTH('avantera'));
...

INSERT INTO JOC(lletres,total, dia) VALUES ('AEHIRUX',107,'2022/01/21');
SET @last_id = LAST_INSERT_ID();

INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aeri',LENGTH('aeri'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aèria',LENGTH('aèria'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'ahir',LENGTH('ahir'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'ai-ai',LENGTH('ai-ai'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aïrar',LENGTH('aïrar'));
...

INSERT INTO JOC(lletres,total, dia) VALUES ('NCEORTU',126,'2022/01/22');
SET @last_id = LAST_INSERT_ID();

INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'cent',LENGTH('cent'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'centè',LENGTH('centè'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'centener',LENGTH('centener'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'centó',LENGTH('centó'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'centre',LENGTH('centre'));
...

INSERT INTO JOC(lletres,total, dia) VALUES ('GAELOPS',116,'2022/01/23');
SET @last_id = LAST_INSERT_ID();

INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'agà',LENGTH('agà'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'agalla',LENGTH('agalla'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'àgape',LENGTH('àgape'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aglà',LENGTH('aglà'));
INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'aglòs',LENGTH('aglòs'));
...
</pre>

=Procediment diari: Excel=
Tenim un Excel (Libreoffice Calc) on cada dia copiem la llisa de paraules vàlides, a la primera columna.

A la segona columna tenim la fórmula:
<pre>
=CONCATENA("INSERT INTO PARAULA(id_joc,paraula,num_cars) VALUES(@last_id,'";A1;"',LENGTH('";A1;"'));")
</pre>
Fixem-nos que amb la funció ''LENGTH'' també podem introduir a la bd el número de caràcters de la paraula. És una informació que pot ser rellevant si volem fer algunes estadístiques.

Per tant, tenim un procediment fàcil i ràpid d'introduir cada dia les paraules, i ens despreocupem dels valors dels índex (''id_joc'', ''id_paraula''), però tenim la certesa de que la informació està ben relacionada.
=Dades gener 2022=
Les dades que van sortir al ''Paraulògic'' entre el 19 i el 24 de gener de 2022 les tens aquí:
*[[Fitxer:Paraulogic.sql.zip]]
=Consultes=
==1. dies disponibles a la base de dades==
<pre>
mysql> select distinct dia from JOC order by dia;
+------------+
| dia        |
+------------+
| 2022-01-19 |
| 2022-01-20 |
| 2022-01-21 |
| 2022-01-22 |
| 2022-01-23 |
| 2022-01-24 |
+------------+
</pre>
==2. dia amb més paraules==
Farem aquesta consulta pas a pas.
<pre>
#1. Relacionem les dues taules.

select * from PARAULA P INNER JOIN JOC J
ON P.id_joc=J.id_joc;

#2. Només ens interessa el dia i el id_paraula:

select dia,id_paraula from PARAULA P INNER JOIN JOC J
ON P.id_joc=J.id_joc;

#3. Ara ja podem agrupar per dia:

select dia,count(id_paraula) as num from PARAULA P INNER JOIN JOC J
ON P.id_joc=J.id_joc
GROUP BY dia;

#4. Ordenem aquesta llista (ja sabem quin és el valor més gran):

select dia,count(id_paraula) as num from PARAULA P INNER JOIN JOC J
ON P.id_joc=J.id_joc
GROUP BY dia order by num DESC;

#5. Ens quedem només amb el primer valor:

select dia,count(id_paraula) as num from PARAULA P INNER JOIN JOC J
ON P.id_joc=J.id_joc
GROUP BY dia order by num DESC LIMIT 1;

+------------+-----+
| dia        | num |
+------------+-----+
| 2022-01-22 | 126 |
+------------+-----+
</pre>

{{Autor}}, gener 2022