Perguntas[sqlite](dba)

select * from tblA a 
 left join tblB b on b.id = a.bId 
   group by a.bId 

Se eu agrupar com base em uma coluna, obtenho apenas o primeiro registro dos registros que têm o mesmo bIDvalor. Existe uma maneira de também obter o número total de registros que têm o mesmo bIdvalor para cada linha? Sem fazer uma consulta separada...

id | bId
--------
1  |  1
2  |  1
3  |  1
4  |  2
5  |  3


so in the above query I would expect

id | bId. | counts
-------------------
1  |  1  |  3   <- 3 records with bId = 1
4  |  2  |  1
5  |  3  |  1

Se eu tiver vários leitores de banco de dados, que são basicamente instâncias do SQLite que abrem o arquivo db no modo "leitura", é normal que um único gravador que abre o db no modo "criação" não consiga liberar uma transação até que todos os leitores fechem o banco de dados?

Estou tentando descobrir se é um comportamento normal do sqlite ou um bug/limitação de uma biblioteca sqlite específica que estou usando.

Não entendo por que um escritor precisaria esperar que os leitores parassem de usar o arquivo de banco de dados. Não faz sentido para mim, porque os leitores não podem alterar os dados.

Pegue o seguinte código:

pragma foreign_keys = ON;

create table people(people_id integer primary key, name text not null);
insert into people (name) values ("Mom"), ("Jack the Ripper");

create table family_member(people_id integer primary key references people(people_id));

insert into family_member values ((select people_id from people where name = "Mom"));
insert into family_member values ((select people_id from people where name = "Dad"));  -- silent error here

select name from family_member inner join people using (people_id);
-- Jack the Ripper is part of the family

Como isso é legítimo no sqlite? Nem mesmo um aviso é gerado. Isso é por boas razões, por razões de legado, há algo na documentação sobre isso que eu não encontrei? Para mim, isso é preocupante e acredito que nenhum outro SQL DB se comporte dessa forma.

Tenho lido a documentação completa de pesquisa de texto no SQLite e tudo faz sentido, exceto que parece não haver acomodação para realmente poder vincular linhas FTS com linhas em outras tabelas. Acho que não quero colocar todas as colunas da minha entidade na minha tabela FTS porque não posso especificar tipos de dados (ou STRICT) para ela, e não preciso que todos os dados sejam indexados para um FTS. Então, eu queria usar um relacionamento FK para fazer um link 1:1 da minha tabela de entidade principal para sua tabela FTS, assim:

CREATE VIRTUAL TABLE usersFTS USING fts4(
    keywords,
    nicknames
);

-- Create the users table with an explicit foreign key reference to usersFTS
CREATE TABLE users(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    usersFTS_id INTEGER NOT NULL,
    FOREIGN KEY (usersFTS_id) REFERENCES usersFTS (rowid)
);

-- ... then:

-- Insert into usersFTS and capture the generated rowid for Alice's meta
INSERT INTO usersFTS (keywords, nicknames) VALUES ('software developer linux open-source', 'Ally Alice');

-- Capture the generated rowid for Alice
SELECT last_insert_rowid();  -- Assume it returns 1

-- Insert into users using the captured rowid
INSERT INTO users (name, email, usersFTS_id) VALUES ('Alice', '[email protected]', 1);

-- ... so that:

SELECT usr.id, usr.name, usr.email, fts.keywords, fts.nicknames
FROM usersFTS fts
INNER JOIN users usr ON usr.usersFTS_id = fts.rowid
WHERE fts.keywords MATCH '"software developer"';

Mas isso falha na INSERTafirmação:

Erro de lógica SQL: incompatibilidade de chave estrangeira - "users" referenciando "usersFTS"

Parece que uma tabela virtual não pode ter seu rowid referenciado em um relacionamento FK normal. Então, como posso vincular uma linha na minha tabela de pesquisa de texto completo a uma linha em outra tabela que contém mais dados sobre essa entidade? Existe alguma maneira de fazer isso no SQLite que mantenha a integridade referencial?

Acho que estou trabalhando com a versão mais recente do Sqlite e C++. Tenho uma tabela com dados, e preciso retornar esses dados na ordem que os inseri.

Tenho alguma garantia de que as colunas sempre serão devolvidas nesta ordem?

Talvez eu deva adicionar outra coluna para definir a ordem e usar ORDER BY:

Tenho um banco de dados sqlite que cresceu para 30 GB e ainda estou enviando dados para ele todos os dias. Tenho alguns serviços que gravam nele usando libsqlite3. Em breve, ele ficará grande demais para ser mantido localmente. Qual é a melhor maneira de migrar para uma configuração distribuída? Uma maneira que posso pensar é sshfs, mas isso não funcionará se minha máquina estiver offline. Uma solução que armazena em cache as partes mais recentemente usadas do meu banco de dados localmente e mantém o restante em outro lugar seria perfeita. Na maioria das vezes, usarei dados enviados recentemente. Eu poderia começar a arquivar dados de anos atrás, mas acessá-los exigiria trabalho manual.

Tenho uma tabela que armazena trilhas e quero adicionar tempos de setor para cada trilha. Cada pista poderá ter um número diferente de setores, a mesma pista terá sempre o mesmo número de setores e os tempos dos setores podem não existir para algumas pistas.

Ao recuperar os dados, gostaria de obter também o menor tempo de setor para cada setor para uma determinada faixa.

A melhor ideia que pude ter é ter um objeto json com sector1, sector2, ... etc. Tudo bem, mas requer que o código que recupera os dados saiba o número de setores para SELECTele, como no exemplo abaixo. Isto significa que o "número de setores" também precisaria ser armazenado.

CREATE TABLE test (track TEXT NOT NULL, sectors TEXT);
INSERT INTO test (track, sectors) VALUES ('track1', json_object('sector1', 10, 'sector2', 11, 'sector3', 12));
INSERT INTO test (track, sectors) VALUES ('track1', json_object('sector1', 11, 'sector2', 9, 'sector3', 12));
INSERT INTO test (track, sectors) VALUES ('track1', json_object('sector1', 10, 'sector2', 9, 'sector3', 12));
INSERT INTO test (track, sectors) VALUES ('track1', json_object('sector1', 12, 'sector2', 12, 'sector3', 4));
INSERT INTO test (track, sectors) VALUES ('track2', json_object('sector1', 12, 'sector2', 12, 'sector3', 3, 'sector4', 1));
INSERT INTO test (track) VALUES ('track1');
select * from test;
select min(DISTINCT json_extract(sectors, '$.sector1')), min(DISTINCT json_extract(sectors, '$.sector2')), min(DISTINCT json_extract(sectors, '$.sector3')) from test where track = 'track1';

Existe talvez uma maneira melhor de armazenar dados como este e poder fazê- SELECTlo como afirmei acima?

Se você excluir as linhas no final da tabela, o incremento automático será redefinido:

sqlite> CREATE TABLE my_table (
    id INTEGER PRIMARY KEY
);
...
sqlite> SELECT * FROM my_table;
1
2
3
sqlite> DELETE FROM my_table WHERE id = 3;
sqlite> SELECT * FROM my_table;
1
2
sqlite> INSERT INTO my_table DEFAULT VALUES;
sqlite> SELECT * FROM my_table;
1
2
3

Quero que o último comando retorne

1
2
4

Isso é possível?

Baseado em documentos Sqlite: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison , especialmente esta declaração:

Se um operando tiver afinidade INTEGER, REAL ou NUMERIC e o outro operando tiver TEXT ou BLOB ou nenhuma afinidade então a afinidade NUMERIC será aplicada ao outro operando.

Eu esperaria a seguinte consulta:

CREATE TABLE `invoice` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `amount` DOUBLE PRECISION DEFAULT NULL
);

insert into `invoice` (`amount`) values (4.0);
insert into `invoice` (`amount`) values (15.0);
insert into `invoice` (`amount`) values (4.0);

select *,
    typeof(amount), amount = '4',
    typeof(sum(amount)), sum(amount) = '4', sum(amount) = '4.0', sum(amount) = 4
from invoice
group by id;

para retornar o mesmo resultado para sum(amount) = '4'cada amount = '4'linha, pois ambos os tipos de operando têm o mesmo tipo em cada comparação (verificado usando typeof(), para não, SUM()a comparação está funcionando conforme o esperado).

Demonstração: http://sqlfiddle.com/#!5/59238/2

Eu tenho uma coluna definida como caractere variável (20). Mas está aceitando mais de 20 caracteres. Já procurei mas não encontrei nada a respeito (talvez esteja pesquisando errado). Alguém já passou por esse problema e sabe a causa e/ou solução?