A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.

learn more… | top users | synonyms

0
votes
1answer
482 views

Text string stored in SQLite Integer column?

I'm a database novice looking at an SQLite database which appears to be storing text in an integer column. Here's an example session at the sqlite3 command line: sqlite> .schema mytable CREATE ...
0
votes
2answers
46 views

How to use relationships to extract data via select

Disclaimer: Novice in SQL I have 3 tables. There is one "major" table (labeled used_for_lookup below) used to do look-ups on and the other two (labeled fid_table and did_table below) are foreign keys ...
0
votes
1answer
35 views

Trigger with multiple inserts based on JSON data

I am using sqlite and have its json1 extension available. I have the following tables (reduced to a minimal example): -- table filled from external source, -- with only a little possible impact on ...
0
votes
1answer
66 views

Add row after every rows of a table from select statement

There are two tables. I want to add every record of one table under every record of the other table. The two tables and the result table are shown below. TABLE_1 TABLE_2 TABLE_3 TYPE ...
0
votes
2answers
571 views

Group by + Order by Behavior Explanation? [duplicate]

Suppose I have: Product(_id,Name) Provider(_id,Name) Provides(ProductId, ProviderId, Price) If I want to obtain the providers who sell the most expensive products, and whose name starts with A. ...
0
votes
1answer
2k views

What's a good workbench tool for sqlite similar to MySQLWorkbench for MySQL? [closed]

What's a good workbench tool for sqlite similar to MySQLWorkbench for MySQL? I'm used to MySQLWorkbench for working with MySQL databases. Now I have some sqlite databases to work with and would ...
0
votes
1answer
237 views

Foreign key double inheritance SQLITE

I'm designing a domotic system database and I'm trying to figure out how to represent two foreign keys. This is the cardinality I want. Consider 1...N Areas (rooms, halls, kitchens, etc.): Area 1 - ...
0
votes
2answers
115 views

Problem in Nested Query [closed]

I have a query like this SELECT m.title FROM Movie AS M WHERE (SELECT MAX(r.stars) FROM rating AS r WHERE r.mID = M.mID); I want to show the MAX(r.stars) in my result set, what am I doing wrong?
0
votes
1answer
53 views

Should the value of a select max be indexed when the where clause is already indexed

I have a customer_orders table in a schema designed to be shared between multiple companies. Each customer_order has an invoice number that should always start at 1 and increment based on company ...
0
votes
1answer
205 views

SQLite: updating string values

I couldn't find any answer for my question so I'm writing. Are there any restrictions for updating strings in SQLite database ? I'm wondering if any new value is written into database file there ...
0
votes
1answer
10 views

A different number of AFTER INSERT trigger alarms in SQLite and SQL Server

I have trigger CREATE TRIGGER [Foo] AFTER INSERT ON [Bar] BEGIN UPDATE Log SET count = count + 1; END When i execute query insert into Bar (ColumnName) values ('SomeValue1'), ('SomeValue2'), ...
0
votes
1answer
16 views

Matching separate unicode characters as one

I am writing a C# program, and I'm querying a SQLite database. I have multiple diacritical characters (e.g ã, õ, ó, ê) which I need to match in my search. I have the command "SELECT * FROM ...
0
votes
1answer
39 views

SQLite3 foreign key mismatch

I have following SQLite3 statement inside Qt/C++ class method: bool UeSettings::ueSaveSettings(const QString& address, const QString& port, ...
0
votes
1answer
26 views

Can sqlite dump database schema in a fixed order?

I am using SQLiteStudio 3.0.7 to dump the database schema regularly for versioning purposes and it seems to change the order of the table definitions at random. This wreaks havoc on the diffs as even ...
0
votes
1answer
104 views

Derived classes with same base class and class specific ids

I have three classes in my project. One base class and two derived classes. public class Animal { public long Id { get; set; } public DateTime Created { get; set; } public string Name { ...
0
votes
1answer
143 views

Efficient way to get last 10 rows of each group in SQLite, can CTE help?

Consider there is a table of job runs history with the following schema: job_runs ( run_id integer not null, -- identifier of the run job_id integer not null, -- identifier of the job ...
0
votes
2answers
30 views

Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQL

Suppose I have two tables A and B. Table A has a primary key, A.ID for instance, and each row in B contains a reference to some A.ID (there is a foreign key column in B). As a specific example: A: ...
0
votes
1answer
34 views

SQLite Data Types In Context of Disk Space

I would like to better understand which data types in SQLite take up more disk space. I've looked through official documentation but could not find anything in context of storage space. Any guide ...
0
votes
2answers
458 views

Install DBeaver SQLite driver offline?

I'd like to use DBeaver with SQLite, but the network firewall disallows external connections. So DBeaver cannot download the driver automatically. Where can I download the SQLite driver for it and ...
0
votes
2answers
407 views

Sqlite query slow whilst it should be fast

I have the following query: SELECT breakType,date,blockname,modname,metadata,nbtdata,x,y,z FROM BlocksBrokenByExplosion WHERE dimension=0 AND y>60 AND y<70 AND x>150 AND ...
0
votes
2answers
2k views

SQLite CSV import won't create a table automatically

According to https://www.sqlite.org/cli.html#csv, sqlite> .mode csv sqlite> .import C:/work/somedata.csv tab1 There are two cases to consider: (1) Table "tab1" does not previously ...
0
votes
1answer
96 views

Issue with accented characters in SQLiteStudio

I downloaded a SQLite 3 dump of Northwind, and opened it in SQLiteStudio. Accented characters aren't displayed nicely. How to solve the issue? I suspect some encoding issue.
0
votes
1answer
1k views

ON DELETE CASCADE not deleting entry for foreign key

I am running the below query in ideone CREATE TABLE AUTHOR ( AUTHOR_ID NUMBER(5) PRIMARY KEY, AUTHOR_NAME VARCHAR2(18) ); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL'); ...
0
votes
1answer
29 views

Retrieve index column

I have this index schema: CREATE index comments_idx ON comments (books) How can I retrieve column name ("books") through SQL query?
0
votes
1answer
66 views

Query executes fast, but keeps the table locked for some time

Executing the following query is very fast (75ms), but removing the comment from the last row it becomes slow (2.5s), and the time increases exponentially with the number of rows in the table. It ...
0
votes
2answers
126 views

One table with a flag or two tables

I have a problem with the design of a database: In my Android application i need to store the upload (the status, so if the file is in upload it will report the % of the upload) of a file and a list ...
0
votes
1answer
148 views

PHP SQLite Admin is running on PHP 5.4

I tried phpsqliteadmin But it doesn't seem to work anymore on PHP 5.4. There are several alternatives, but none of them seems to be "The One" that is maintained and widely used. Is there a standard ...
0
votes
1answer
1k views

Database design for mobile app

At first I would like to say hello to all of you, since it is my first post here on DB Administrators. I am writing an app which store drinks and their data. At the beginning it was storing all in ...
0
votes
2answers
180 views

How to optimize query

I'm using SQLite3 and the following query, It seems like there should be a better performing way to write this query; Is there? Select Name, Sum(Case When Used ...
0
votes
1answer
961 views

nesting MAX for SELECT

I have a table, containing TV series episodes: series: | series_id | season | episode | title | 1 | 1 | 18 | xxx | 1 | 2 | 3 | xxy | 3 | ...
0
votes
1answer
412 views

No such column error jdbc

Suppose I have a table: SERVICE={SID,NAME,PORT} and with the help of a Java program, I want to select the SID of the known port with the help of a SQL query: Select SID from SERVICE ...
0
votes
1answer
54 views

Order result by number of occurence of a word in a text

I am working with sqlite3/python . I would like to order results by the number of occurence of %@% in text. For exemple Jimmy 10 // Jimmy wrote 10 strings like %@% James 2 Sophia 0 ..etc The ...
0
votes
1answer
548 views

Keep rows only if Date exists in both table

This is my first time here, so please bear with me. Assuming i have 2 table, Table 1 Date | Closing 1/1/2013 | 1 2/1/2013 | 2 Table 2 Date | Closing 1/1/2013 | 3 3/1/2013 | 4 What sql statements ...
0
votes
1answer
100 views

Performance: order of tables in joined statement

I have the following SQL statement, running on a SQLite database on a Windows mobile device. SELECT table1.uniqueidentifier1, table1.int1, table1.varchar1, table1.decimal1, table1.decimal2 ...
0
votes
2answers
111 views

Advice on scripting a “whitewashed” and downsized development db from main db

Currently we (an active opensource project) have a large PostgreSQL production database which contains all the site records. Since we can't just allow every developer or contributor access to the ...
0
votes
1answer
941 views

How to output query to file using SQLite3

Is there an equivalent to Oracle's spool for sqlite3?
0
votes
1answer
1k views

Delete with subquery with no unique columns

I have a table LIKES (ID1, ID2). Both id1 and id2 are non unique columns. Database is SQLite delete from LIKES where ID1 = 10 /// Wrong there more then one record with ID1=10 delete from LIKES ...
0
votes
2answers
779 views

How can I simplify this query?

I have written the following SQLite query: SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight' FROM ( SELECT "transaction".transamount AS ...
0
votes
1answer
403 views

Structuring SQLite database with 2 relationships?

I'm current working on a SQLite-database in a android project which will store 3 types of objects; feed, users and posts. There will be several feeds containing several varying users(a user can be ...
0
votes
1answer
370 views

Store a small log file as a blob in sqlite

I am utilizing a small sqlite database to keep track of some jobs that run on a linux box. Most of them have small (less than 50 lines) log files... I'd like to store those log files as a record in my ...
0
votes
2answers
134 views

Very slow request with small difference

I use sqlite3 on a Mac Mini. I'm a Java and Web programmer, with some SQL experience, not as much as an expert. I have three requests. First and second are fast (0.2s), and the third is very slow ...
0
votes
0answers
15 views

SQLite text columns

When having a table in SQLite that has a TEXT data type, is there any implication if the length of the contained string differ significantly?
0
votes
0answers
16 views

Recover data from a corrupted, encrypted SQLite database file

We use the System.Data.SQLite library to use SQLite in .NET applications, which allows the database file to be encrypted with a password. We have a user who is getting an error that the database disk ...
0
votes
0answers
24 views

Sync, not import, CSV files with SQLite

In MySQL I have observed that under table options, the storage engine can be configured as a CSV file. This gives me a nice clean csv file in the database directory. This file doesn't come with column ...
0
votes
1answer
29 views

How to get average of views before clicking on advertisement?

I've got two tables containing information about viewing and clicking on advertisement by each user: Clicks: userID Action Time 123 c 2016-01-08 01:57:00 123 c 2016-01-11 ...
0
votes
0answers
40 views

Optimization beyond creating indexes

From the Explain query Output i already created db.execute("CREATE INDEX x1 ON TRAVELDAYS(Day, Value);") db.execute("CREATE INDEX x2 ON TRAINS(TrainDaysUID);") db.execute("CREATE INDEX x3 ON ...
0
votes
1answer
52 views

sqlite database table with 1000's of columns

I am using sqlite3 in an application to conduct an alalysis of distances between sets of points. Distances between these points have been precomputed using dijkstra's algorithim and left me with a ...
0
votes
0answers
41 views

How to get row position in sqlite after sorting?

I have table, UUID | INT | LONG AAA | 1 | 123 BBB | 3 | 241 CCC | 2 | 145 And i sort it by INT value. SELECT * FROM table ORDER BY int_val DESC then i want to get a row from table and ...
0
votes
0answers
22 views

SQLite: choosing a unique separator

I'm working on a project that will result in an SQLite database of about 6 GB of text content (encoded UTF-8). The text will be diverse: it will have a great deal of plain text writing, but also a ...
0
votes
0answers
22 views

Switching SQLite environments

I submitted this on SuperUser without response; someone suggested I try it here. I like Firefox's SQLite Manager plugin because it's easy to show people how to use it. This is helpful if I want to ...