SQLite Gatilho, visualizações e índice com exemplo

Por: Marcus Allen Marcus Allen
Hours Atualização do

No uso diário de SQLite, você precisará de algumas ferramentas administrativas em seu banco de dados. Você também pode usá-los para tornar a consulta ao banco de dados mais eficiente, criando índices, ou mais reutilizável, criando visualizações.

SQLite Ver

As visualizações são muito semelhantes às tabelas. Mas as Views são tabelas lógicas; eles não são armazenados fisicamente como tabelas. Uma visualização é composta por uma instrução select.

Você pode definir uma visualização para suas consultas complexas e reutilizá-las sempre que desejar, chamando a visualização diretamente, em vez de reescrever as consultas novamente.

Instrução CREATE VIEW

Para criar uma visualização em um banco de dados, você pode usar a instrução CREATE VIEW seguida do nome da visualização e, em seguida, colocar a consulta desejada.

Exemplo: No exemplo a seguir criaremos uma View com o nome “Todos os AlunosVer”no banco de dados de amostra“TutoriaisSampleDB.db" como segue:

Passo 1) Abra Meu Computador e navegue até o seguinte diretório “C:\sqlite”E depois abra“sqlite3.exe":

SQLite Ver

Passo 2) Abra o banco de dados “TutoriaisSampleDB.db”pelo seguinte comando:

SQLite Ver

Passo 3) A seguir está uma sintaxe básica do comando sqlite3 para criar a Visualização

CREATE VIEW AllStudentsView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Não deve haver nenhuma saída do comando como esta:

SQLite Ver

Passo 4) Para garantir que a visualização seja criada, você pode selecionar a lista de visualizações no banco de dados executando o seguinte comando:

SELECT name FROM sqlite_master WHERE type = 'view';

Você deverá ver a vista “Todos os AlunosVer”é retornado:

SQLite Ver

Passo 5) Agora que nossa view foi criada, você pode usá-la como uma tabela normal, mais ou menos assim:

SELECT * FROM AllStudentsView;

Este comando irá consultar a visualização “AllStudents” e selecionar todas as linhas dela conforme mostrado na imagem a seguir:

SQLite Ver

Vistas Temporárias

As visualizações temporárias são temporárias para a conexão de banco de dados atual usada para criá-las. Então, se você fechar a conexão com o banco de dados, todas as visualizações temporárias serão excluídas automaticamente. As visualizações temporárias são criadas usando um dos seguintes comandos:

  • CRIAR VISUALIZAÇÃO DE TEMPERATURA, ou
  • CRIAR VISUALIZAÇÃO TEMPORÁRIA.

As visualizações temporárias são úteis se você deseja realizar algumas operações naquele momento e não precisa que seja uma visualização permanente. Então, você apenas cria uma visualização temporária e faz o processamento usando essa visualização. Later ao fechar a conexão com o banco de dados, ele será excluído automaticamente.

Exemplo:

No exemplo a seguir, abriremos uma conexão com o banco de dados e, em seguida, criaremos uma visualização temporária.

Depois disso, fecharemos essa conexão e verificaremos se a visualização temporária ainda existe ou não.

Passo 1) Abra sqlite3.exe no diretório “C:\sqlite”Como explicado anteriormente.

Passo 2) Abra uma conexão com o banco de dados “TutoriaisSampleDB.db” executando o seguinte comando:

.open TutorialsSampleDB.db

Passo 3) Escreva o seguinte comando que criará uma visualização temporária “Todos os alunosTempView'

CREATE TEMP VIEW AllStudentsTempView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

SQLite Ver

Passo 4) Certifique-se de que a visualização temporária “Todos os alunosTempView”é criado executando o seguinte comando:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

SQLite Ver

Passo 5) Feche sqlite3.exe e abra-o novamente.

Passo 6) Abra uma conexão com o banco de dados “TutoriaisSampleDB.db”pelo seguinte comando:

.open TutorialsSampleDB.db

Passo 7) Execute o seguinte comando para obter a lista de visualizações temporárias criadas no banco de dados:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Você não deverá ver nenhuma saída, pois a visualização temporária que criamos foi excluída quando fechamos a conexão com o banco de dados na etapa anterior. Caso contrário, desde que você mantenha a conexão com o banco de dados aberta, você poderá ver a visualização temporária com os dados.

SQLite Ver

Observações:

  • Você não pode usar as instruções INSERT, DELETE ou UPDATE com visualizações, apenas você pode usar o comando “select from views” conforme mostrado na etapa 5 no exemplo CREATE View.
  • Para excluir uma VIEW, você pode usar a instrução “DROP VIEW”:
DROP VIEW AllStudentsView;

Para garantir que a visualização seja excluída, você pode executar o seguinte comando que fornece a lista de visualizações no banco de dados:

SELECT name FROM sqlite_master WHERE type = 'view';

Você não encontrará visualizações retornadas porque a visualização foi excluída, conforme a seguir:

SQLite Ver

ARTIGOS RELACIONADOS

SQLite Índice

Se você tem um livro e deseja pesquisar uma palavra-chave nesse livro. Você pesquisará essa palavra-chave no índice do livro. Em seguida, você navegará até o número da página dessa palavra-chave para ler mais informações sobre ela.

No entanto, se não houver índice nem número de página nesse livro, você digitalizará o livro inteiro do início ao fim até encontrar a palavra-chave que está procurando. E isso é muito difícil principalmente quando você tem um índice e um processo muito lento para pesquisar uma palavra-chave.

Índices em SQLite (e o mesmo conceito válido para outros Sistemas de Gerenciamento de Banco de Dados também) funciona da mesma forma que os índices encontrados no final dos livros.

Quando você procura algumas linhas em um SQLite tabela com critérios de pesquisa, SQLite irá pesquisar todas as linhas da tabela até encontrar as linhas que você está procurando que correspondam aos critérios de pesquisa. E esse processo fica muito lento quando você tem tabelas maiores.

Os índices acelerarão as consultas de pesquisa de dados e ajudarão a realizar a recuperação de dados das tabelas. Os índices são definidos nas colunas da tabela.

Melhorando o desempenho com índices:

Os índices podem melhorar o desempenho da pesquisa de dados em uma tabela. Quando você cria um índice em uma coluna, SQLite criará uma estrutura de dados para esse índice onde cada valor do campo possui um ponteiro para toda a linha à qual o valor pertence.

Então, se você executar uma consulta com uma condição de pesquisa em uma coluna que faz parte de um índice, SQLite procurará primeiro o valor no índice. SQLite não examinará a tabela inteira em busca disso. Em seguida, ele lerá o local para onde o valor aponta para a linha da tabela. SQLite irá localizar a linha nesse local e recuperá-la.

Entretanto, se a coluna que você está procurando não fizer parte de um índice, SQLite realizará uma varredura nos valores da coluna para encontrar os dados que você está procurando. Geralmente será um processo mais lento se não houver índice.

Imagine um livro sem índice e você precisa pesquisar uma palavra específica. Você digitalizará o livro inteiro, da primeira à última página, procurando por essa palavra. No entanto, se você tiver um índice desse livro, primeiro procurará a palavra nele. Obtenha o número da página onde ele está localizado e navegue até ele. O que será muito mais rápido do que digitalizar o livro inteiro de capa a capa.

SQLite CRIAR ÍNDICE

Para criar um índice em uma coluna, você deve usar o comando CREATE INDEX. E você deve defini-lo da seguinte forma:

  • Você deve especificar o nome do índice após o comando CREATE INDEX.
  • Após o nome do índice, deve-se colocar a palavra-chave “ON”, seguida do nome da tabela na qual o índice será criado.
  • Em seguida, a lista de nomes de colunas usadas para o índice.
  • Você pode usar uma das seguintes palavras-chave “ASC” ou “DESC” após qualquer nome de coluna para especificar uma ordem de classificação usada para ordenar os dados do índice.

Exemplo:

No exemplo a seguir, criaremos um índice “StudentNameIndex”na mesa dos alunos no“Estudantes”banco de dados da seguinte forma:

Passo 1) Navegue até a pasta “C:\sqlite”Como explicado anteriormente.

Passo 2) Abra sqlite3.exe.

Passo 3) Abra o banco de dados “TutoriaisSampleDB.db”pelo seguinte comando:

.open TutorialsSampleDB.db

Passo 4) Crie um novo índice “ÍndiceNomeAluno”usando o seguinte comando:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Você não deverá ver nenhuma saída para isso:

SQLite Índice

Passo 5) Para garantir que o índice foi criado você pode executar a seguinte consulta, que fornece a lista de índices criados na tabela Alunos:

PRAGMA index_list(Students);

Você deverá ver o índice que acabamos de criar retornado:

SQLite Índice

Observações:

  • Os índices podem ser criados não apenas com base em colunas, mas também em expressões. Algo assim:
CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

O “OrderTotalIndex” será baseado na coluna OrderId e também na multiplicação do valor da coluna Quantidade e do valor da coluna Preço. Portanto, qualquer consulta para “OrderId” e “Quantity*Price” será eficiente, pois a consulta usará o índice.

  • Se você especificou uma cláusula WHERE na instrução CREATE INDEX, o índice será um índice parcial. Nesse caso, haverá entradas no índice apenas para as linhas que correspondem às condições da cláusula WHERE. Por exemplo, no seguinte índice: 
    CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;

    (No exemplo acima, o índice será um índice parcial, pois há uma cláusula WHERE especificada. Nesse caso, o índice será aplicado apenas aos pedidos que tenham valor de quantidade maior que 10000. Observe que, este índice é chamado de parcial índice por causa da cláusula WHERE, não da expressão usada nele. No entanto, você pode usar as expressões com índices normais.)

  • Você pode usar a instrução CREATE UNIQUE INDEX em vez de CREATE INDEX para evitar entradas duplicadas para as colunas e, portanto, todos os valores da coluna indexada serão exclusivos.
  • Para excluir um índice, use o comando DROP INDEX seguido do nome do índice a ser excluído.

SQLite Gatilho

Introduction to SQLite Gatilho

Triggers são operações automáticas predefinidas executadas quando ocorre uma ação específica em uma tabela de banco de dados. Um gatilho pode ser definido para ser acionado sempre que uma das seguintes ações ocorrer em uma tabela:

  • INSERT em uma tabela.
  • DELETE linhas de uma tabela.
  • ATUALIZE uma das colunas da tabela.

SQLite suporta o gatilho FOR EACH ROW para que as operações predefinidas no gatilho sejam executadas para todas as linhas envolvidas nas ações ocorridas na tabela (seja inserção, exclusão ou atualização).

SQLite CRIAR TRIGGER

Para criar um novo TRIGGER, você pode usar a instrução CREATE TRIGGER da seguinte forma:

  • Após CREATE TRIGGER, você deve especificar um nome de gatilho.
  • Após o nome do gatilho, você deve especificar quando exatamente o nome do gatilho deve ser executado. Você tem três opções:
  • BEFORE – o gatilho será executado antes da instrução INSERT, UPDATE ou delete especificada.
  • Depois – o gatilho será executado após a instrução INSERT, UPDATE ou delete especificada.
  • INSTEAD OF – Substituirá a ação ocorrida que disparou o gatilho pela instrução especificada no TRIGGER. O gatilho INSTEAD OF não é aplicável a tabelas, apenas a visualizações.
  • Então, você deve especificar o tipo de ação, o gatilho será acionado quando isso acontecer. DELETE, INSERT ou UPDATE.
  • Você pode escolher um nome de coluna opcional para que o gatilho não seja acionado a menos que a ação aconteça nessa coluna.
  • Então você deve especificar o nome da tabela na qual o gatilho será criado.
  • Dentro do corpo do gatilho, você deve especificar a instrução que deve ser executada para cada linha quando o gatilho for acionado.

    • Os gatilhos serão ativados (disparados) apenas dependendo do tipo de instrução especificada no comando create trigger. Por exemplo:

    • O gatilho BEFORE INSERT será ativado (disparado) antes de qualquer instrução de inserção.
    • O gatilho AFTER UPDATE será ativado (disparado) após qualquer instrução de atualização,… e assim por diante.

    Dentro do gatilho, você pode consultar os valores recém-inseridos usando a palavra-chave “novo”. Além disso, você pode consultar os valores excluídos ou atualizados usando a palavra-chave antiga. Como segue:

    • Dentro dos gatilhos INSERT – uma nova palavra-chave pode ser usada.
    • Dentro dos gatilhos UPDATE – palavras-chave novas e antigas podem ser usadas.
    • Dentro dos gatilhos DELETE – a palavra-chave antiga pode ser usada.

    Exemplo

    A seguir, criaremos um gatilho que será acionado antes de inserir um novo aluno no “Estudantes" tabela.

    Ele registrará o aluno recém-inserido na tabela “Registro de Alunos”Com um carimbo de data/hora automático para a data e hora atual em que a instrução de inserção ocorreu. Como segue:

    Passo 1) Navegue até o diretório “C:\sqlite”E execute sqlite3.exe.

    Passo 2) Abra o banco de dados “TutoriaisSampleDB.db” executando o seguinte comando:

    .open TutorialsSampleDB.db

    Passo 3) crie o gatilho “InsertIntoStudentTrigger”Executando o seguinte comando:

    CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

    A função "data hora()" fornecerá o carimbo de data e hora atual quando a instrução de inserção ocorreu. Para que possamos registrar a transação de inserção com carimbos de data/hora automáticos adicionados a cada transação.

    O comando deve ser executado com êxito e você não obtém nenhuma saída:

    SQLite Gatilho

    O gatilho "InsertIntoStudentTrigger” será acionado cada vez que você inserir um novo aluno na tabela de alunos. O "novo”A palavra-chave refere-se aos valores que serão inseridos. Por exemplo, o “novo.StudentId” será a carteira de estudante que será inserida.

    Agora testaremos como a trigger se comporta quando inserimos um novo aluno.

    Passo 4) Escreva o seguinte comando que irá inserir um novo aluno na tabela de alunos:

    INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

    Passo 5) Escreva o seguinte comando que selecionará todas as linhas do “Registro de Alunos" mesa:

    SELECT * FROM StudentsLog;

    Você deverá ver uma nova linha retornada para o novo aluno que acabamos de inserir:

    SQLite Gatilho

    Esta linha foi inserida pela trigger antes de inserir o novo aluno com id 11.

    Neste exemplo usamos o gatilho “ InsertIntoStudentTrigger ”criamos, para registrar quaisquer transações de inserção na tabela“Registro de Alunos”automaticamente. Da mesma forma, você pode registrar qualquer atualização ou excluir instruções.

    Evitando atualizações não intencionais com gatilhos:

    Usando os gatilhos BEFORE UPDATE em uma tabela, você pode evitar as instruções de atualização em uma coluna com base em uma expressão.

    Exemplo

    No exemplo a seguir, evitaremos que qualquer instrução update atualize a coluna “nomedoaluno” na tabela Alunos:

    Passo 1) Navegue até o diretório “C:\sqlite”E execute sqlite3.exe.

    Passo 2) Abra o banco de dados “TutoriaisSampleDB.db” executando o seguinte comando:

    .open TutorialsSampleDB.db

    Passo 3) Crie um novo gatilho “preventUpdateStudentName" na mesa "Estudantes”executando o seguinte comando

    CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

    A "LEVANTAR”O comando gerará um erro com uma mensagem de erro“ Você não pode atualizar o nome do aluno “, e então impedirá a execução da instrução de atualização.

    Agora, verificaremos se o gatilho funciona bem e evita qualquer atualização da coluna studentname.

    Passo 4) Execute o seguinte comando de atualização, que atualizará o nome do aluno “tomada" ser "Jack1".

    UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

    Você deverá receber a mensagem de erro que especificamos no gatilho, dizendo que “Você não pode atualizar o nome do aluno" como segue:

    SQLite Gatilho

    Passo 5) Execute o seguinte comando, que selecionará a lista de nomes de alunos na tabela de alunos.

    SELECT StudentName FROM Students;

    Você verá que o nome do aluno “Jack” continua o mesmo e não muda:

    SQLite Gatilho

    Resumo

    Visualizações, índices e gatilhos são ferramentas muito poderosas para administrar um SQLite banco de dados. Você pode acompanhar as operações de modificação de dados quando elas acontecem em uma tabela. Você também pode otimizar a operação de recuperação de dados do banco de dados criando índices.

    Você pode gostar: