Comment récupérer le dernier ID auto-incrémenté d'une table SQLite ?

Question

Je possède une table Messages avec les colonnes ID (clé primaire, auto-incrément) et Content (texte).
Je possède une table Utilisateurs avec les colonnes nom d'utilisateur (clé primaire, texte) et Hash.
Un message est envoyé par un Expéditeur (utilisateur) à de nombreux destinataires (utilisateurs) et un destinataire (utilisateur) peut avoir de nombreux messages.
J'ai créé une table Messages_Recipients avec deux colonnes : MessageID (faisant référence à la colonne ID de la table Messages) et Recipient (faisant référence à la colonne nom d'utilisateur dans la table Utilisateurs). Cette table représente la relation de plusieurs à plusieurs entre les destinataires et les messages.

Donc, la question que je me pose est la suivante. L'ID d'un nouveau message sera créé après qu'il a été stocké dans la base de données. Mais comment puis-je conserver une référence à la ligne de Message que je viens d'ajouter afin de récupérer ce nouvel ID de Message?
Je peux toujours chercher dans la base de données la dernière ligne ajoutée bien sûr, mais cela pourrait éventuellement renvoyer une ligne différente dans un environnement multi-threaded?

MODIFIER : Tel que je le comprends pour SQLite, vous pouvez utiliser SELECT last_insert_rowid(). Mais comment puis-je appeler cette instruction depuis ADO.Net?

Mon code de persistence (messages et messagesRecipients sont des DataTables):

public void Persist(Message message)
{
    pm_databaseDataSet.MessagesRow messagerow;
    messagerow = messages.AddMessagesRow(message.Sender,
                            message.TimeSent.ToFileTime(),
                            message.Content,
                            message.TimeCreated.ToFileTime());
    UpdateMessages();
    var x = messagerow;//J'espérais que messagerow retiendrait une
    //référence à la nouvelle ligne dans la table Messages, mais ce n'est pas le cas.
    foreach (var recipient in message.Recipients)
    {
        var row = messagesRecipients.NewMessages_RecipientsRow();
        row.Recipient = recipient;
        //row.MessageID= Comment le trouver ??
        messagesRecipients.AddMessages_RecipientsRow(row);
        UpdateMessagesRecipients();//méthode non affichée
    } 

}

private void UpdateMessages()
{
    messagesAdapter.Update(messages);
    messagesAdapter.Fill(messages);
}

Answer 1

Ceci est un ancien post mais pour les lecteurs ultérieurs, ci-dessous pourrait être la manière la plus simple et la plus fiable - il suffit d'utiliser la clause Returning avec RowId (c'est un alias spécial pour la clé primaire numérique dans SQLite) :

insert into MyTable (Column1, Column2) Values (Val1, Val2) Returning RowId;

En .Net, vous pouvez simplement l'exécuter et retourner la valeur avec ExecuteScalar.

Answer 2

Sqlite3_last_insert_rowid() n'est pas sûr dans un environnement multithread (et est documenté comme tel sur SQLite). Cependant, la bonne nouvelle est que vous pouvez jouer avec la chance, voir ci-dessous

L'ID de réservation n'est PAS implémenté dans SQLite, vous pouvez également éviter PK en utilisant votre propre clé primaire UNIQUE si vous connaissez quelque chose toujours variant dans vos données.

Note: Voyez si la clause sur RETURNING ne résout pas votre problème https://www.sqlite.org/lang_returning.html Comme cela n'est disponible que dans les versions récentes de SQLite et peut entraîner des frais supplémentaires, considérez utiliser le fait que c'est vraiment de la malchance si vous avez une insertion entre vos requêtes à SQLite

Voyez également si vous avez absolument besoin de récupérer PK interne de SQlite, pouvez-vous concevoir votre propre PK prédictible : https://sqlite.org/withoutrowid.html

Si vous avez besoin d'un PK AUTOINCREMENT traditionnel, oui il y a un petit risque que l'ID que vous obtenez puisse appartenir à une autre insertion. Un risque petit mais inacceptable.

Une solution de contournement est d'appeler deux fois la fonction sqlite3_last_insert_rowid()

1 AVANT mon Insertion, puis #2 APRÈS mon insertion

comme suit :

int IdLast = sqlite3_last_insert_rowid(m_db);   // Avant (cet ID est déjà utilisé)

const int rc = sqlite3_exec(m_db, sql,NULL, NULL, &m_zErrMsg);

int IdEnd = sqlite3_last_insert_rowid(m_db);    // Après l'insertion, très probablement le bon,

Dans la grande majorité des cas IdEnd==IdLast+1. C'est le "bon chemin" et vous pouvez compter sur IdEnd comme étant l'ID que vous recherchez.

Sinon vous devez effectuer un SELECT supplémentaire où vous pouvez utiliser des critères basés sur IdLast à IdEnd (tous les critères supplémentaires dans la clause WHERE sont bons à ajouter s'il y en a)

Utilisez ROWID (qui est un mot-clé SQLite) pour SÉLECTIONNER la plage d'ID pertinente.

"SELECT my_pk_id FROM Symbols WHERE ROWID>%d && ROWID<=%d;",IdLast,IdEnd); 

// remarquez le > dans : ROWID>%zd, car nous savons déjà que IdLast n'est pas celui que nous cherchons.

Comme le deuxième appel à sqlite3_last_insert_rowid est effectué immédiatement après l'INSERT, ce SELECT ne retourne généralement que 2 ou 3 lignes au maximum. Ensuite, cherchez dans le résultat du SELECT les données que vous avez insérées pour trouver le bon ID.

Amélioration des performances: Comme l'appel à sqlite3_last_insert_rowid() est beaucoup plus rapide que l'INSERT, (même si le mutex peut contredire cela, c'est statistiquement vrai) je mise sur IdEnd pour être le bon et je défais les résultats du SELECT à la fin. Dans presque tous les cas que nous avons testés, la dernière LIGNE contient l'ID que vous recherchez).

Amélioration des performances: Si vous avez une clé UNIQUE supplémentaire, ajoutez-la au WHERE pour obtenir une seule ligne.

J'ai expérimenté en utilisant 3 threads pour des insertions massives, cela a fonctionné comme prévu, la préparation + la manipulation de la BD prenant la grande majorité des cycles CPU, puis le résultat est que le risque d'un mélange d'IDs est de l'ordre de 1/1000 insertions (situation où IdEnd>IdLast+1)

Donc, la pénalité d'un SELECT supplémentaire pour résoudre cela est assez faible.

Autrement dit, l'avantage d'utiliser sqlite3_last_insert_rowid() est grand dans la grande majorité des insertions, et en utilisant une certaine précaution, peut même être utilisé en MT.

Avertissement : La situation est légèrement plus délicate en mode transactionnel.

De plus, SQLite ne garantit pas explicitement que l'ID sera contigu et croissant (à moins d'AUTOINCREMENT). (Au moins je n'ai pas trouvé d'informations à ce sujet, mais en regardant le code source de SQLite, cela le présume)

Answer 3

Je pense que la meilleure façon sera d'utiliser sqllite avec ibatis et de faire ce qui suit pour insérer un nouvel objet :

  INSERT INTO table
  (name, addressLine1, addressLine2, city, state,region,zip,division,description)
  VALUES
  (#name#, #address1#, #address2#, #city#, #state#,#reg#,#zip#,#division#,#description#)

    SELECT last_insert_rowid() as value

Answer 4

La méthode la plus simple serait d'utiliser :

SELECT MAX(id) FROM yourTableName LIMIT 1;

si vous essayez de récupérer cet dernier identifiant dans une relation pour affecter une autre table, par exemple :
( si une facture est ajoutée, AJOUTEZ la liste des articles à l'identifiant de la facture )
dans ce cas, utilisez quelque chose comme :

var cmd_result = cmd.ExecuteNonQuery(); // retourne le nombre de lignes affectées

ensuite, utilisez cmd_result pour déterminer si la requête précédente a été exécutée avec succès,
quelque chose comme :
if(cmd_result > 0) suivi de votre requête SELECT MAX(id) FROM yourTableName LIMIT 1;
pour vous assurer de ne pas cibler le mauvais identifiant de ligne au cas où la commande précédente n'a pas ajouté de lignes.

en fait, la condition cmd_result > 0 est très importante en cas d'échec. Surtout si vous développez une application sérieuse, vous ne voulez pas que vos utilisateurs se réveillent en trouvant des articles ajoutés aléatoirement à leur facture.

Answer 5

J'ai récemment trouvé une solution à ce problème qui sacrifie une certaine surcharge de performance pour garantir que vous obtenez le dernier ID inséré correctement.

Disons que vous avez une table people. Ajoutez une colonne appelée random_bigint:

create table people (
    id int primary key,
    name text,
    random_bigint int not null
);

Ajoutez un index unique sur random_bigint:

create unique index people_random_bigint_idx
ON people(random_bigint);

Dans votre application, générez un bigint aléatoire à chaque fois que vous insérez un enregistrement. Je suppose qu'il y a une possibilité triviale qu'une collision se produise, donc vous devriez gérer cette erreur.

Mon application est en Go et le code qui génère un bigint aléatoire ressemble à ceci:

func RandomPositiveBigInt() (int64, error) {
    nBig, err := rand.Int(rand.Reader, big.NewInt(9223372036854775807))
    if err != nil {
        return 0, err
    }

    return nBig.Int64(), nil
}

Après avoir inséré l'enregistrement, interrogez la table avec un filtre where sur la valeur du bigint aléatoire:

select id from people where random_bigint = 

L'index unique ajoutera une petite surcharge à l'insertion. La recherche de l'ID, bien que très rapide en raison de l'index, ajoutera également une légère surcharge.

Cependant, cette méthode garantira un dernier ID inséré correct.