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

learn more… | top users | synonyms

0
votes
0answers
185 views

Get milliseconds from a DateTime in Sqlite3?

I have this DateTime in my database: 20130420070028401 is suppose to be: 2013-04-20T07:00:28.401 To "get" the milliseconds from 1970 I'm sub-stringing the DateTime: ...
0
votes
2answers
405 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
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 ...
4
votes
2answers
356 views

Converting outer apply with Correlated subquery in SQL Server to SQLite

I need to convert a query in SQL Server to SQlite and I have replaced the outer apply to left outer join, but how to limit the subquery to return only 1 in this situation? And how to pass the ...
2
votes
0answers
86 views

Improving performance of a weighted search query in SQLite3

For an intra-site search I have this table in an SQLite3 database, that relates ids to search terms with a certain weight: CREATE TABLE search_index ( id INTEGER(7) REFERENCES other_table, ...
0
votes
1answer
95 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
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
1answer
479 views

can't create foreign key in sqlite

I'm trying to run this code in sqlite: sqlite> CREATE TABLE test(id integer primary key AUTOINCREMENT, page varchar(10), dp_record_id integer, foreign key (webserver_id) REFERENCES ...
1
vote
1answer
125 views

How to get a surrogate key value in order to put a foreign key in and create relationship?

I am trying to create two relationships between three tables, using foreign keys with values of primary keys, which are surrogate keys. But I don't understand how to enter the right value in the ...
0
votes
2answers
778 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 ...
3
votes
0answers
348 views

Are there any known benchmarks/statistics on UnQLite?

I came across UnQLite the other day in Stack Overflow and it has me intrigued for some software I'm working on. However, very few tests have been published on how well it performs. There are plenty of ...
1
vote
2answers
339 views

How to select data in the same table on different rows that are empty?

I use this select on my SQLite database: SELECT attributeX FROM table GROUP BY attributeX HAVING COUNT(attributeY) = 0; My table: attributeX (type integer) | attributeY (type text) ...
1
vote
1answer
91 views

Improve Query Performance

I have the following sqlite3-Tables: db.execute("""CREATE TABLE IF NOT EXISTS STATIONS (StationUID INTEGER PRIMARY KEY AUTOINCREMENT, StationNumber TEXT NOT NULL, ...
1
vote
1answer
34 views

Should I use VACUUM on a SQLite db with a log-structured filesystem?

I'm runnig a SQLite database on nilfs (which is a log-structured filesystem). Every now and then I delete old records, so that the size of the database never surpass a certain amount. But since the ...
0
votes
0answers
237 views

How to divide a big SQL Query into small query

I am trying to traverse the whole file system. I am picking each and every file and then trying to insert it into the SQLite Database. Before inserting I am also calculating the checksum of each file. ...
14
votes
2answers
12k views

For absolute performance, is SUM faster or COUNT?

This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100. I tend to prefer COUNT(CASE WHEN invoice_amount > 100 THEN 1 END) However, this is ...
0
votes
1answer
71 views

Is it correct to dynamically add rows to a table in sqlite, or should I use csv? (I'm working with objective c.)

Just like when adding a new contact in the ios contact app, you can add multiple phone numbers, and it doesn't know how many there will be, so therefore they don't know how many rows they should ...
0
votes
0answers
67 views

Bitfield in Database

What is the best way to store a Bitfield of 960 values in a Sqlite3-Database? I need to do queries of testing if the x-bit is set.
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
177 views

How to define the table structure for measured data in sqlite

I have an embedded system with an sqlite database. How should I define the table structure for the measured data from 4 sensors? Each sensor give us every second an numeric measured value. The ...
0
votes
0answers
166 views

SQLite 3 Tree List Without CTE

I adapted a SQL statement to work with SQLite 3. But unfortunately Common Table Expressions and WITH clause works only in SQLite 3.6 ahead, but the users of my Open Source application are using SQLite ...
1
vote
1answer
26 views

How to optimize search restricted by boolean column?

I want to optimize a query1 of the form SELECT yid, xid FROM x WHERE is_principal; Here, is_principal is a non-null boolean column of x. If I run EXPLAIN QUERY PLAN on this query, the output I get ...
2
votes
1answer
220 views

How to enforce an “exactly-one-per-group” constraint?

I have a table x that I've defined like this: CREATE TABLE x ( xid INTEGER NOT NULL PRIMARY KEY, yid INTEGER NOT NULL REFERENCES y(yid), is_principal BOOLEAN NOT NULL ); This definition ...
1
vote
1answer
89 views

Will my sqlite database occupy less space if I insert nulls instead of zeros

I have a table where most of the values inserted in one of the columns are 0. Should the database be smaller if I insert nulls instead of zeros?
1
vote
2answers
149 views

New to databases - does my ERD reflect good database design?

I'm making a database to hold data for a media player that I'm working on. Originally, I thought to use one table with the fields: {filePath,artist,title,album,track_number,length,date,artworkUrl} ...
-2
votes
1answer
117 views

SQL: how to scan an entire table sequentially from top to bottom

How do I 'scan' all the way through an SQL table (approx 120k rows/50 columns) from the top/beginning, all the way to the bottom/end, and for each row found, I will need to 'do something'. The 'do ...
1
vote
1answer
83 views

Child records under the parent record in SQLite [closed]

I have two tables for Billing, one Bill_Master and other is Bill_Detail. The records in both tables are as follows... **BILL_MASTER** id party bill_amount 1 abc 500 2 def ...
0
votes
1answer
404 views

DBEAVER: SQL error or missing database (near “,”: syntax error)

I'm using DBeaver (because Base only works on OS X) to work with an SQLite database. When I attempt to execute the following query, I get the error message 'SQL error or missing database (near ",": ...
-1
votes
1answer
58 views

Creating Triggers to insert into a table where the table is altered by other application

I have a database in my local machine which is actually updated by an online application. I want to create triggers for a particular table ( say table1) such that it inserts values (From Table1) ...
0
votes
0answers
63 views

Continuously copying an SQLite table

I have a SQLite database associated with an online application (Skype) on my computer, which is often changed by that application when I am using Internet. These are the columns of the database: ...
1
vote
0answers
314 views

Migrate from SQLite to MySQL (then back)

For various reasons, I must use a SQLite database with a local application. However, I wrote a Flask app that enables users to more easily classify items in the database, and it runs very slow on ...
2
votes
2answers
10k views

disk I/O error in SQLite

What are the possible things that would trigger the "disk I/O error"? I've been having this problem and I couldn't find a solution. I have a SQLite3 database, and I'm trying to insert data from a file ...
0
votes
0answers
313 views

insert record to child table which doesn't map to parent table - possible?

Actually I've 2 relevant questions: insert record to child table which doesn't map to parent table - possible? ON DELETE NO ACTION - when a record in parent table is deleted, it should affect ...
0
votes
1answer
2k views

SQLite - No Such Column TABLE.ROWID

I'm using this query on SQLite 3: Select Message.messageContent, Message.messageDateTime FROM Message INNER JOIN (Contact INNER JOIN contact_m2m_message ON Contact.contactID = ...
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
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
2answers
568 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
0answers
28 views

Unable to open MONyogs

I was trying to run through the docs for manually working with the MONyog internal sqlite dbs for purposes of configuration automation. At the point trying to open the data file I get an error $ ...
0
votes
1answer
69 views

SQLITE table joining with group

MESSAGE TABLE USERS msg_id | msg_to | msg_from | msg_body | msg_date user_id | user_name 1 1 2 hi 22/1/14 ...
6
votes
1answer
182 views

Many-to-many relationship with attributes

I'm working on a project where I need a book database. The problem is as follows: A book can be related to multiple people (authors, editors, traslators, etc.). Then a person may contribute to ...
1
vote
1answer
3k views

Joins in Update statement in sqlite

CREATE TABLE Table_1 ( Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , EmailId VARCHAR ) INSERT INTO Table_1 VALUES (1,'raj@gmail.com'); INSERT INTO Table_1 VALUES (3,'test@gmail.com'); ...
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 ...
1
vote
1answer
918 views

How to get the rowid in a TRIGGER for another table in SQLite

Given the below schema CREATE TABLE Item ( _id INTEGER NOT NULL, name NVARCHAR NOT NULL, parent INTEGER NULL CONSTRAINT fk_Recursive ...
2
votes
2answers
166 views

Create a result for a line chart with dates

I have this table in SQLite which I use to record the risk level identified in vulnerability assesments according to the day the scan was performed. This table looks like this: Scan Date | Risk ...
1
vote
1answer
769 views

Cartesian product of two tables not working as expected in MySQL but fine with SQlitee?

I was practising some sql joins and decided to create a schema online rather than using my local database on sql fiddle. My schema is pretty simple table 1 (5 entries): (emp->{id,name,dep}) table ...
0
votes
1answer
1k views

How to select non unique values from 1 column if a value doesn't exist in column 2

I have a table containing (among other things) 2 integer values, neither of which are unique. See below for example data: --------------- | a | b | --------------- | 1 | 1 | ...
2
votes
2answers
70 views

Strategy to find the unique bivariate polynomials

I have table that stores Tutte polynomials of a large set of graphs. These are bivariate polynomials with integer coefficients. An example of a polynomial taken from Wikipedia is: Since the ...
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 ...
1
vote
2answers
2k views

Improving `GROUP BY` query performance in sqlite3

I have a little web-application that is using sqlite3 as it's DB (the db is fairly small). Right now, I am generating some content to display using the following query: SELECT dbId, dlState, ...
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 ...