Perguntas[sqlite](dba)

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.

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?

Quando executo este código em Rust

statement.read::<i64, _>("creation_utc").unwrap(),

Eu recebo,

Erro {código: Nenhum, mensagem: Some("o índice está fora do intervalo ( creation_utc)") }

Mas verifiquei que a coluna está na tabela várias vezes. Que problema causa isso e como posso resolver esse erro?

Aqui está meu comando para o banco de dados GRASS GIS (driver SQL Lite usado):

db.execute sql="UPDATE streams_order SET factor=next_stream WHERE number IS NOT NULL AND next_stream IN (SELECT number FROM streams_order)"

Mas eu quero

db.execute sql="UPDATE streams_order SET factor=next_stream WHERE number IS NOT NULL AND next_stream NOT IN (SELECT number FROM streams_order)"

E o resultado deve ser:

+---------+--------------+--------+---------+
| stream  |  next_stream   factor |  number |
+-------------------------------------------+
  370           -1           
  372           370         370       372
  374           440         
  376           372                   376
  380           374         374       380
.............................................

não funciona sem nenhum erro: a coluna "fator" fica vazia após executar o comando. Se eu colocar IN, funciona bem. Os tipos de coluna são todos INT.

Sandbox com dados reais está aqui https://dbfiddle.uk/zg8_xJUz