Logo Hardware.com.br
neodarkman
neodarkman Veterano Registrado
921 Mensagens 35 Curtidas

SQLite - consulta que não funciona

#1 Por neodarkman 21/08/2015 - 10:48
Olá...
Tenho um banco simples em MySql que estou migrando para o SQLite. Nada complexo.

No MySql tenho a consulta:


select d.descricao,r.disciplina,

max(case r.tipo when 'ADP' then r.nota end) as ADP,

max(case r.tipo when 'AIEB' then r.nota end) as AIEB,

max(case r.tipo when 'BONUS' then r.nota end) as BONUS,

IFNULL(max(case r.tipo when 'SIM' then r.nota end),'---') as SIM,

max(case r.tipo when 'PS' then r.nota end) as PS,

sum(r.nota) as NB,r2.nota as FALTAS

from resultado as r

inner join aluno as a

on a.matricula = r.matricula

inner join disciplina as d

on r.disciplina = d.id

inner join resultado as r2

on r.ano = r2.ano and r.bimestre = r2.bimestre

and r.disciplina = r2.disciplina

and r.matricula = r2.matricula

and r.ano = 2015 and r.bimestre = 2

where

r.tipo <> 'FALTAS'

and r2.tipo = 'FALTAS' and a.matricula = '142246'

group by a.matricula,a.numero,a.nome,

a.serie,a.turma,a.turno,d.descricao,r2.nota


Funciona perfeitamente. Todavia no SQLite nada retorna. Obtenho a mensagem:
0 row(s) affected in 0.003688 second(s).

Sei que o IFNULL() não existe no SQLite, mas mesmo retirando não há retorno.

As tabelas possuem dados.
Alguma ideia?
sqlite case max mysql consulta then when sqlite funciona
Responder
Gokuro
Gokuro Veterano Registrado
704 Mensagens 76 Curtidas
#2 Por Gokuro
21/08/2015 - 15:12
IFNULL está implementado no SQLite, documentado em http://www.sqlite.org/lang_corefunc.html

e sem conhecer mais detalhes é ruim sugerir alguma coisa, mas não seria problema de "collation" nas comparações de colunas com afinidade TEXT ?
Se for, use COLLATE NOCASE, ou o que for adequado, diretamente nas expressões e um detalhe que desconhecia: pode-se declarar "collation" em SELECTs.

[]'s
Gokuro
Gokuro Veterano Registrado
704 Mensagens 76 Curtidas
#4 Por Gokuro
23/08/2015 - 12:43
neodarkman disse:
Quais os detalhes?

Definição e conteúdo das tabelas.

0 rows indica que a view pode estar vazia e por suposição, se houver NULLs numa coluna presente numa restrição então usar o operador = pode levar a resultados inesperados, usa-se o operador IS (NOT) neste caso.

Dica: Use a extensão SQLite Manager do Firefox https://github.com/lazierthanthou/sqlite-manager para agilizar o desenvolvimento das consultas.

[]'s
neodarkman
neodarkman Veterano Registrado
921 Mensagens 35 Curtidas
#5 Por neodarkman
23/08/2015 - 19:15
No MySQL a estrutura é essa:


CREATE DATABASE  IF NOT EXISTS `setoroeste` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `setoroeste`;

-- MySQL dump 10.13  Distrib 5.5.16, for Win32 (x86)

--

-- Host: localhost  Database: setoroeste

-- ------------------------------------------------------

-- Server version   5.5.24-log



/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;



--

-- Table structure for table `aluno`

--



DROP TABLE IF EXISTS `aluno`;

/*!40101 SET @saved_cs_client  = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `aluno` (

  `matricula` varchar(9) NOT NULL,

  `numero` int(11) DEFAULT NULL,

  `nome` varchar(128) DEFAULT NULL,

  `serie` int(11) DEFAULT NULL,

  `turma` char(1) DEFAULT NULL,

  `turno` char(1) DEFAULT NULL,

  PRIMARY KEY (`matricula`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;





--

-- Table structure for table `disciplina`

--



DROP TABLE IF EXISTS `disciplina`;

/*!40101 SET @saved_cs_client  = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `disciplina` (

  `id` char(3) NOT NULL,

  `descricao` varchar(16) DEFAULT NULL,

  `area` varchar(12) DEFAULT NULL,

  `ordem` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;





--

-- Table structure for table `gabarito`

--



DROP TABLE IF EXISTS `gabarito`;

/*!40101 SET @saved_cs_client  = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `gabarito` (

  `ano` int(11) DEFAULT NULL,

  `bimestre` int(11) DEFAULT NULL,

  `serie` int(11) DEFAULT NULL,

  `turno` char(1) DEFAULT NULL,

  `tipo` varchar(16) DEFAULT NULL,

  `disciplina` char(3) DEFAULT NULL,

  `questao` int(11) DEFAULT NULL,

  `resposta` varchar(3) DEFAULT NULL,

  KEY `fk_gabarito_001` (`disciplina`),

  CONSTRAINT `fk_gabarito_001` FOREIGN KEY (`disciplina`) REFERENCES `disciplina` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Table structure for table `marcacao`

--



DROP TABLE IF EXISTS `marcacao`;

/*!40101 SET @saved_cs_client  = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `marcacao` (

  `ano` int(11) DEFAULT NULL,

  `bimestre` int(11) DEFAULT NULL,

  `tipo` varchar(16) DEFAULT NULL,

  `matricula` varchar(9) DEFAULT NULL,

  `disciplina` char(3) DEFAULT NULL,

  `questao` int(11) DEFAULT NULL,

  `resposta` char(1) DEFAULT NULL,

  `pontuacao` decimal(16,2) DEFAULT NULL,

  `marcacaocol` varchar(45) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;



--

-- Table structure for table `resultado`

--



DROP TABLE IF EXISTS `resultado`;

/*!40101 SET @saved_cs_client  = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `resultado` (

  `ano` int(11) DEFAULT NULL,

  `bimestre` int(11) DEFAULT NULL,

  `tipo` varchar(16) DEFAULT NULL,

  `disciplina` char(3) DEFAULT NULL,

  `matricula` varchar(9) DEFAULT NULL,

  `nota` decimal(16,2) DEFAULT NULL,

  KEY `fk_resultado_001` (`disciplina`),

  KEY `fk_resultado_002` (`matricula`),

  CONSTRAINT `fk_resultado_001` FOREIGN KEY (`disciplina`) REFERENCES `disciplina` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;







--

-- Table structure for table `resultado_final`

--



DROP TABLE IF EXISTS `resultado_final`;

/*!40101 SET @saved_cs_client  = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `resultado_final` (

  `ano` int(11) DEFAULT NULL,

  `bimestre` int(11) DEFAULT NULL,

  `matricula` varchar(9) DEFAULT NULL,

  `nota` decimal(16,2) DEFAULT NULL,

  `class` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;


Algumas tabelas não são usadas na consulta que mencionei antes.
As tabelas e chaves são as mesmas no SQLite.
Lembre-se só Deus salva... o homem faz backup.isso_ai.png

Corei5-10th - 16Gb DDR4 - Kubuntu 20.04/win10Pro
Acer Aspire Corei5-10th - 12Gb DDR4 Kubuntu 20.04 -Main
Linux User # 156897
Gokuro
Gokuro Veterano Registrado
704 Mensagens 76 Curtidas
#6 Por Gokuro
23/08/2015 - 20:31
Quase certamente há um erro muito comum de conversão de dados do MySql para SQLite que pode ser a razão do mal funcionamento, pois a conversão das tabelas é trivial.

Geralmente, os dados numa tabela do MySql são armazenados num arquivo tipo texto, usando um separador de campos/colunas que por default é o "|" , via comando SELECT * FROM nome_tabela; ou similar, que vai gerar uma linha pra cada registro da tabela.

A importação dos dados para o SQLite é feita via comando .import FILE nome_tabela que vai ler as linhas correspondentes a cada registro, atribuindo valores extraídos entre separadores a cada campo/coluna e aí ocorre o erro.
Se o conteúdo de uma coluna qualquer está entre dois separadores consecutivos (||), será importado uma string de comprimento zero "\0" ao invés do NULL e como a tipagem é por afinidade, acaba por aceitar tal valor como válido.

Workaround:

UPDATE nome_tabela SET nome_coluna=NULL WHERE nome_coluna == "";


Mesmo que os dados sejam importados de outra maneira, use o comando acima que mal não fará.

[]'s
neodarkman
neodarkman Veterano Registrado
921 Mensagens 35 Curtidas
#7 Por neodarkman
24/08/2015 - 07:51
Gokuro... valeu pela ajuda. Mas.... eu não estou importando os dados... apenas a estrutura do banco foi importada. Os dados (novos) estão sendo inseridos diretamente no banco já no SQLite. Na hora das consultas relacionadas, é que estou tendo os problemas mencionados...
Lembre-se só Deus salva... o homem faz backup.isso_ai.png

Corei5-10th - 16Gb DDR4 - Kubuntu 20.04/win10Pro
Acer Aspire Corei5-10th - 12Gb DDR4 Kubuntu 20.04 -Main
Linux User # 156897
Gokuro
Gokuro Veterano Registrado
704 Mensagens 76 Curtidas
#8 Por Gokuro
24/08/2015 - 14:33
O arquivo anexado contém o "dump" do database com poucas modificações, comprovando que a consulta funciona desde que as tabelas sejam preenchidas corretamente e as restrições respeitadas.

Leia ou cole o sql e requisite a tua consulta original:

SELECT * FROM consulta;


[]'s

Importante: Não se esqueça de ativar a integridade referencial para inserir/eliminar/modificar dados:

pragma foreign_keys=ON;

Anexos

setoroeste.dump
Gokuro
Gokuro Veterano Registrado
704 Mensagens 76 Curtidas
#10 Por Gokuro
24/08/2015 - 19:30
A coluna tipo da tabela resultado está mais amigável com o COLLATION NOCASE e isso fez diferença porque tipo influencia muito no resultado da consulta e deveria ter valores padronizados, exceto para "FALTAS" que também é armazenado na coluna nota, mas o que interessa é que funciona E pra demonstrar mais objetivamente, construi registros exatamente como filtrados na consulta original.

Observe que declarei as FOREIGN KEYS no modo mais simplista possível, exemplifiquei como tratar as colunas tipo CHAR/VARCHAR e não por preguiça, não criei os índices originais que implicitamente são apenas do tipo UNIQUE, portanto use o CREATE INDEX explicitamente se desejar.

Sentiu porque SQLite é o terror dos DBs de griffe? LOL

[]'s
neodarkman
neodarkman Veterano Registrado
921 Mensagens 35 Curtidas
#11 Por neodarkman
25/08/2015 - 08:53
Gokuro... novos problemas....
Não consigo deletar o registro exemplo que colocou..... erro que aparece:

Error while execiting query: foreign key constraint failed.

Se insiro novos registros, não aceita pois diz que:

Column matricula is not unique

De fato essa coluna não poder ser única haja vista que posso ter um aluno que saiu de uma turma e foi para outra, MAS, eu preciso que essa referência à turma antiga fique registrada no banco.

Caraca de simples o SQLite tem nada.!!!
Putz, estou tentando migrar o banco para que meu aplicativo seja mais "portátil", isto é, não precise da instalação do MySQL na máquina cliente...
para quem sabe, no futuro, facilita a migração para tablets... mas está fróids....
Lembre-se só Deus salva... o homem faz backup.isso_ai.png

Corei5-10th - 16Gb DDR4 - Kubuntu 20.04/win10Pro
Acer Aspire Corei5-10th - 12Gb DDR4 Kubuntu 20.04 -Main
Linux User # 156897
Gokuro
Gokuro Veterano Registrado
704 Mensagens 76 Curtidas
#12 Por Gokuro
25/08/2015 - 12:50
Antes de tudo leia sobre FOREIGN KEYS no SQLite: http://www.sqlite.org/foreignkeys.html

Altere ou remova a restrição FOREIGN KEY conforme conveniência, consulte o diagrama: http://www.sqlite.org/syntax/foreign-key-clause.html

A complexidade do SQLite é aparente. FOREIGN KEY, TRIGGER, etc, funcionam como nos DBs de griffe e cada um tem sua peculiaridade.
Tenho certeza que você escolheu o software adequado e a migração já está sendo bem sucedida.

[]'s
neodarkman
neodarkman Veterano Registrado
921 Mensagens 35 Curtidas
#13 Por neodarkman
25/08/2015 - 17:33
Gokuro....
li....li, re-li... mas não compreendi.
Não consegui resolver o problema da a matrícula ser única.
Na tabela original eu usei uma coluna id como chave primária.. tu usou a matrícula. Mas daí não posso ter "duas" matriculas... a matrícula tem que ser única... o problema de apagar creio ter resolvido... mas o de inserir... não.
Não querendo abusar, poderia me dar mais esclarecimentos?
Lembre-se só Deus salva... o homem faz backup.isso_ai.png

Corei5-10th - 16Gb DDR4 - Kubuntu 20.04/win10Pro
Acer Aspire Corei5-10th - 12Gb DDR4 Kubuntu 20.04 -Main
Linux User # 156897
Gokuro
Gokuro Veterano Registrado
704 Mensagens 76 Curtidas
#14 Por Gokuro
25/08/2015 - 19:05
A única restrição adicional na tabela resultado foi na coluna matricula que pode ser revertida para o original:
...
matricula VARCHAR(9),
...
sem influenciar na consulta original.

As declarações de tipo/restrições nas tabelas PAI originais não foram alteradas e nem houve adição de chaves primárias ou índices únicos.

Somente a coluna id da tabela disciplina é referenciada como FOREIGN KEY e implicitamente com restrição ON DELETE RESTRICT (impede eliminação se alguém a referencia) como documentado.

Se nada se aplica, em qual tabela/coluna há impedimento de eliminação?
neodarkman
neodarkman Veterano Registrado
921 Mensagens 35 Curtidas
#15 Por neodarkman
25/08/2015 - 19:11
Gokuro.....
Obrigado pela ajuda. Quanto a eliminação já resolvi. Meu problema agora é na inserção de dados...
Não consigo inserir duas matriculas iguais.... por exemplo:

matricula,numero,nome,serie,turma,turno,status (status: (A)tivo ou (I)nativo)
12345,15,zeninguem,1,C,V,I

Esse aluno saiu do 1C e foi pro 1H; dai eu tenho:

12345,15,zeninguem,1,H,V,A

não consigo inserir esse segundo dado, haja vista que o sqlite diz que a matrícula é única.
Daí se importo de uma tabela (excell) terei os dois registros, mas o sqlite não permite essa "duplicidade". No entanto ela é necessária.
Lembre-se só Deus salva... o homem faz backup.isso_ai.png

Corei5-10th - 16Gb DDR4 - Kubuntu 20.04/win10Pro
Acer Aspire Corei5-10th - 12Gb DDR4 Kubuntu 20.04 -Main
Linux User # 156897
Responder Tópico
© 1999-2024 Hardware.com.br. Todos os direitos reservados.
Imagem do Modal