Clé composites en SQL

L’intérêt d’une clé composite / composée est de pouvoir utiliser plusieurs champs différents en tant que clé primaire d’une table. En effet, si l’association de deux colonnes est unique, pourquoi recréér artificellement un id qui ne servirait que de clé primaire ?

Illustration :


CREATE TABLE example1(
field1 text not null,
field2 text not null,
PRIMARY KEY (field1, field2)
);

Requete SELECT subtile… comment afficher de manière unique un résultat d’une requête SQL ?

Si vous avez une requête SQL donnant un résultat avec des doublons et que vous souhaitez éliminer les doublons pour avoir ces résultats de manière unique, pensez à utiliser le mot clé DISTINCT.

Exemple imaginons que vous souhaitiez extraire les emails de vos clients n’ayant pas acheté depuis une certaine date. Vous disposez d’une table « orders » et « clients »… Notre requête ressemblera à cela

SELECT email, name FROM clients,order WHERE order.date < 20090601 (ou un timestamp quelconque…);

Si dans votre réponse vous obtenez plusieurs fois le meme mail, hop, DISTINCT vous assurera de l’unicité de vos résultats ! Chacun des champs de la table retournée sera unique.

La requête devient donc :

SELECT DISTINCT email, name FROM clients,order WHERE order.date < 20090601 (ou un timestamp quelconque…);

Pratique non ?

Place dans un classement et rang parmi des enregistrements MySQL

Si vous voulez réaliser un Top 1000 pour votre jeu en ligne ou plus généralement connaitre quelle est la place d’un de vos tuple dans un ensemble trié d’enregistrements, voici une petite astuce qui vous permettra d’obtenir le résultat avec un moyen 100 % SQL ( pas besoin de variable compteur coté serveur).

Imaginez une table players contenant les colonnes « pseudo, score et time_played ». Notre objectif est de connaitre la position d’un joueur dans le classement total connaissant le champ « pseudo ». L’obtention de son rang va se faire en 3 temps :

1. On va commencer par initialiser une variable MySQL avec la commande SET:
SET @counter = 0;

2. L’étape suivante consiste à effectuer une première requête qui va compter et créer une colonne indiquant le rang de chacun des enregistrements au sein de la table.
SELECT pseudo,score,time_played,@counter:=@counter+1 AS rang FROM players ORDER BY score ASC;

3. Enfin, nous allons isoler l’enregistrement qui nous intéresse, à savoir, celui dont la valeur pseudo est celle que nous cherchons.

Il s’agit d’une simple requête du type

SELECT place,pseudo FROM (*****) AS subrequest WHERE pseudo = ‘John Doe’;

Si vous avez suivi, vous aurez d’ores et déja compris qu’il va falloir remplacer les ***** par notre requête construite à l’étape 2 (il s’agit de ce qu’on appelle une sous- requete). Le résultat obtenu devrait être une table contenant le rang et le pseudonyme de notre joueur ‘John Doe’.

N’hésitez pas à envoyer un commentaire si vous avez besoin d’aide !

Références :
Documentation MySQL

Sécuriser MySQL : création d’un utilisateur dédié à une base de donnée

Dans l’ensemble de cet article on supposera l’utilisateur MySQL root existant avec le mot de passe: rootPass

Etape 1 : Créer l’utilisateur

Connectez vous en root sur la table appelée « mysql », et tapez la commandes suivantes.

INSERT INTO user(host,user,password) VALUES (‘localhost’,’nom_user’,PASSWORD(‘pass_user’));

Etape 2 : Créer la base de donnée

En root, il suffit d’entrer les commandes CREATE DATABASE nom_database;.
Pour générer les tables, que vous avez déjà probablement crée lors de la phase de développement, je vous conseille d’exporter la structure depuis le SGBD de dev, puis de l’insérer dans le SGBD de production en utilisant:
mysql -u root -prootPass nom_database <votrefichier.sql

Etape 3 : Attribuer les droits : insertion, suppression, et lecture sur l’ensemble de la base

L’attribution des droits s’effectue avec l’usage de la commane GRANT, comme suit:

GRANT SELECT, INSERT, UPDATE ON nom_database.* TO nom_user@’localhost’ ;

Validez l’attribution de ces droits par:
flush privileges;

Références

http://dev.mysql.com/doc/refman/5.0/fr/adding-users.html

Récupérer l’id (autoincrement) après un insert

Fiche recette du 12 février 2009:

Comment récupérér la valeur d’un champ déclaré « auto_increment » lors d’un INSERT INTO exécuté depuis Java ?

L’API JDBC fournit une méthode extrèmement pratique appelée getGeneratedKeys():

PreparedStatement stat = connectionSql.prepareStatement("INSERT INTO maTable SET " +
"login = ?," +
"email = ?");
int i=1;
stat.setString(i++, leLogin);
stat.setString(i++, leMail);
int nb_lignes = stat.executeUpdate();
ResultSet rs = stat.getGeneratedKeys();
int idAuto_increment = 0;
if( rs.next() ) {
idAuto_increment = rs.getInt(1);
}


Un enchainement de méthodes très certainement utile à connaitre… et que je recherche à chaque fois que je travaille avec MySql et son connecteur Java…

Crappy loop inside mysql request… Play with mysql procedure.

If you miss something during the conception of your mysql database, or wish to add some new tables for additional features in your web application, it could be a mess to build the new version on your database without droping all data of the existing tables.

I personnaly needed to keep thousand of registered users (in a table) and nevertheless i had to provide some messaging feature in another table.

I decided to:

1. Create the new table in my existing database:

CREATE TABLE `newone` (
`id` INT NOT NULL ,
`field1` TINYINT NOT NULL ,
`field2` BLOB NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;

2. Set my default values for each field:

ALTER TABLE `newone` CHANGE `field1` `field1` TINYINT( 4 ) NOT NULL DEFAULT ‘999999’

3. Insert as many lines as registered user, so that every registered user have their own line in the « newone » table:
i used the LOOP mysql statement to achieve this:

delimiter !!

CREATE PROCEDURE massiveInsert(IN maxInserts INT)
BEGIN
DECLARE i INT;
SET i=1;
myloop: LOOP
SET i=i+1;
IF i=maxInserts THEN
LEAVE myloop;
END IF;
insert into newone values();
END LOOP myloop;
END!!

Doesn’t it looks like 80’s GW*-Basic, ehhh ?

To run this procedure, just type:
call massiveInsert();

What do you need to remind concerning MySQL procedures:

– Define you OWN delimiter, unsing the delimiter. Otherwise, you will get the Error Code : 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near blahblahblah

– Use the keyword IN to input parameters, and next the variable name + parameter type.

– Use the call() instruction to execute your procedure.

References:

mysql manual

Installer le connecteur mysql java sur un serveur Tomcat

Nous allons installer le support de mysql au serveur Tomcat.

Il est important de ne pas dupliquer l’installation du connecteur (ne pas inclure dans le code source s’il n’est pas inclus.

il suffit de copier le fichier jar dans le dossier catalina_home/common/libs

Ainsi on fait un:

wget http://mir2.ovh.net/ftp.mysql.com/Downloads/Connector-J/mysql-connector-java-5.1.6.tar.gz

puis tar -zxvf ./mysql-connector-java-5.1.6.tar.gz

et enfin mv ./mysql-connector-java-5.1.6.tar.gz /catalinahome/common/libs/

et ca devrait être bon 🙂 N’oubliez pas de remplacer catalina_home par votre propre répertoire d’installation de Tomcat bien entendu…