[sqlite] FTS5 bug in SQLite 3.18.0
When SQLIte is compiled with sanitizers they report invalid readouts and undefined-behavior: ``` sqlite3.c:188900:5: runtime error: index 11 out of bounds for type 'char [8]' SUMMARY: AddressSanitizer: undefined-behavior sqlite3.c:188900:5 in sqlite3.c:189142:30: runtime error: index 12 out of bounds for type 'char [8]' SUMMARY: AddressSanitizer: undefined-behavior sqlite3.c:189142:30 in sqlite3.c:184801:14: runtime error: null pointer passed as argument 1, which is declared to never be null /usr/include/string.h:62:62: note: nonnull attribute specified here SUMMARY: AddressSanitizer: undefined-behavior sqlite3.c:184801:14 in ``` Compilation command: ``` $ clang 4.c sqlite3.c -o 4 -pthread -ldl -lm -DSQLITE_ENABLE_FTS5 -fsanitize=address -fsanitize=undefined ``` Additional information: ``` $ clang -v clang version 3.8.0-2ubuntu4 (tags/RELEASE_380/final) Target: x86_64-pc-linux-gnu Thread model: posix InstalledDir: /usr/bin Found candidate GCC installation: /usr/bin/../lib/gcc/i686-linux-gnu/5.4.0 Found candidate GCC installation: /usr/bin/../lib/gcc/i686-linux-gnu/6.0.0 Found candidate GCC installation: /usr/bin/../lib/gcc/x86_64-linux-gnu/5.4.0 Found candidate GCC installation: /usr/bin/../lib/gcc/x86_64-linux-gnu/6.0.0 Found candidate GCC installation: /usr/lib/gcc/i686-linux-gnu/5.4.0 Found candidate GCC installation: /usr/lib/gcc/i686-linux-gnu/6.0.0 Found candidate GCC installation: /usr/lib/gcc/x86_64-linux-gnu/5.4.0 Found candidate GCC installation: /usr/lib/gcc/x86_64-linux-gnu/6.0.0 Selected GCC installation: /usr/bin/../lib/gcc/x86_64-linux-gnu/5.4.0 Candidate multilib: .;@m64 Candidate multilib: 32;@m32 Candidate multilib: x32;@mx32 Selected multilib: .;@m64 $ uname -a Linux quasarHP 4.4.0-77-generic #98-Ubuntu SMP Wed Apr 26 08:34:02 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux ``` This behaviour appeared in the earlier versions of SQLite, for sure in Hope this helps, Krzysztof Małysa ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Tags / keywords support?
Thanks Constantine! Great idea with table contains tags per record! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Tags / keywords support?
Hi, I need extra field which contain tags / keywords describing such record. Then I want to find record ID by using tags. I know that it is easy with TEXT field and LIKE condition but I have issue with update speed. Let say that you have collection of photos and you want to add tags like "summer", "beach" (with ignoring duplicates). But then you want to remove from collection tags "beach". It is quite expensive (find, remove using native language then update record with new value). I'm reading about FTS but I think that it is overloaded for my needs and I didn't find functions for remove keywords. For example PostgreSQL has special field HSTORE which is list of key=>value type field. It is not suitable for tags but it is just example for special data types. HSTORE has routines for update (with ignoring duplicates), removing, search, enumerates etc. Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Check if file exists in SQL syntax?
Thanks Keith!
2014-10-11 2:01 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>:
>
> You do not say what operating system, but for example, on Windows, the
> following function works:
>
> SQLITE_PRIVATE void _GetFileAttributes(sqlite3_context *context, int argc,
> sqlite3_value **argv)
> {
> sqlite3_result_int(context,
> GetFileAttributesW(sqlite3_value_text16(argv[0])));
> }
>
> which is then declared to SQLite function interface (for connection db) thus:
>
> nErr += sqlite3_create_function(db, "GetFileAttributes", 1,
> SQLITE_ANY|SQLITE_DETERMINISTIC, 0, _GetFileAttributes,0, 0);
>
> Returning the file attributes for the given pathspec, or -1 if it does not
> exist.
>
>
> SQLite version 3.8.7 2014-10-09 15:08:17
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> select getfileattributes('D:\');
> 22
> sqlite> select getfileattributes('D:\momo');
> -1
> sqlite> select getfileattributes('D:\source\sqlite\sqlite3s.exe');
> 32
> sqlite> select getfileattributes('D:\source\sqlite');
> 16
> sqlite>
>
> You do similar on Unices just substitute the appropriate call to get the file
> attributes ...
>
>>-Original Message-
>>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>>boun...@sqlite.org] On Behalf Of Krzysztof
>>Sent: Friday, 10 October, 2014 12:21
>>To: General Discussion of SQLite Database
>>Subject: [sqlite] Check if file exists in SQL syntax?
>>
>>Hi,
>>
>>I'm collecting file names (full paths) in sqlite table. Each day I
>>need to remove non existing files from this table (thousands records).
>>For now I'm selecting all records and then checking if file exists
>>using C++ routine and if not then deleting it. I'm wondering if SQLite
>>has such function (could not find it in core functions). For example I
>>would like to call something like:
>>
>>CREATE TABLE my_table {
>> id INTEGER PRIMARY KEY,
>> filename TEXT,
>> tags TEXT
>>}
>>
>>DELETE FROM my_table WHERE NOT FileExists(filename)
>>
>>Regards
>>___
>>sqlite-users mailing list
>>sqlite-users@sqlite.org
>>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Check if file exists in SQL syntax?
Hi,
I'm collecting file names (full paths) in sqlite table. Each day I
need to remove non existing files from this table (thousands records).
For now I'm selecting all records and then checking if file exists
using C++ routine and if not then deleting it. I'm wondering if SQLite
has such function (could not find it in core functions). For example I
would like to call something like:
CREATE TABLE my_table {
id INTEGER PRIMARY KEY,
filename TEXT,
tags TEXT
}
DELETE FROM my_table WHERE NOT FileExists(filename)
Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Merging databases
Hi, I have daemon on server which each day create new sqlite database. Client application can download and present these databases. For example: User want to see data from last week so client application download 7 files. Advantage of this defragmentation is that it don't need to download big files. Disadvantage is that create queries is tricky. So here is my question. Is SQLite has some function for merging data? I'm wondering about ATTACH DATABASE, but maybe there is a better way? Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - disk I/O error only on virtual machine
Problem solved. It was shared folder issue ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite - disk I/O error only on virtual machine
Hi,
I noticed strange behavior when running application on virtualbox linux
mint 14 64bit machine. This occur only when I attach second database to
sqlite3connection and then call SQLTransaction.Commit. This is my code
(this is Free Pascal / Lazarus wrapper for SQLite):
try
FConn := TMySqlite3Connection.Create(Self);
FConn.Transaction := SQLTransaction1;
FConn.DatabaseName :=
IncludeTrailingBackslash(ProgramDirectory)+'db1.sqlite';
FConn.Connected := True;
FConn.ExecSQLNoTrans('attach database '+
QuotedStr(IncludeTrailingBackslash(ProgramDirectory)+'db2.sqlite')+'
as d2');
FConn.ExecuteDirect('create table if not exists a(col integer)');
FConn.ExecuteDirect('create table if not exists d2.b(col integer)');
SQLTransaction1.Commit;
except on e: Exception do
ShowMessage(e.Message);
end;
Even if error occur, commit succesful created tables. When I run it on
normal Linux installation (also Mint 14 64bit) then everything is ok. Both
OS have same sqlite version (3.7.13) from ubuntu repo.
I don't know if this is problem in sqlite or in my Free Pascal wrapper. I
also reported it on FPC mailing list
Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Are Foreign Keys indexed?
Thanks for answer! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Are Foreign Keys indexed?
Hi, I'm reading http://www.sqlite.org/foreignkeys.html but can't find answer. Are SQLite FK indexed? For example: CREATE TABLE artists ( id_artist INTEGER PRIMARY KEY // <- PK are indexed, that I know ) CREATE TABLE songs ( id_song INTEGER PRIMARY KEY, id_artist INTEGER, // <- Is this FK indexed? FOREIGN KEY(id_artist) REFERENCES artists(id_artist) ) To be more specific: select * from songs where id_artist = 123 Is above query searching sequentially or using index? I'm asking because if I remember, FireBird FK where indexed by default, but Oracle needed create index on that FK manually. How this work in SQLite? Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using sqlite in multi fork() without threads
Few days ago I posted questions about sqlite multi threading on sqlite mailing list. I could paste link to this thread but it is for members only. So here is content, maybe it wil help you in some cases: Hi, I'm reading documentation about multi threading. I just want to be sure: 1. I can safely create another thread (with new handle / connection) in my application and modify SAME database at the same time 2. I want use second connection in thread for importing while main thread working "normally". By import I mean not backup, but importing documents (CSV) 3. Can user modify table while another thread importing to it (in transaction)? What are restrictions? What should I look out for? 4. Records inserted by second thread will be "visible" for first connection after second thread commit transaction? Regards Answers: > Hi, > > I'm reading documentation about multi threading. I just want to be sure: > 1. I can safely create another thread (with new handle / connection) in my > application and modify SAME database at the same time > Yes, as long as you don't take compile-time or run-time actions to make SQLite single-threaded. The default is to be multi-threaded. > 2. I want use second connection in thread for importing while main thread > working "normally". By import I mean not backup, but importing documents > (CSV) > Yes. > 3. Can user modify table while another thread importing to it (in > transaction)? What are restrictions? What should I look out for? > Only one thread can be in a write transaction at a time. If you have two threads that need to write, they have to take turns. > 4. Records inserted by second thread will be "visible" for first connection > after second thread commit transaction? > Correct ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi thread questions
Thanks :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multi thread questions
Hi, I'm reading documentation about multi threading. I just want to be sure: 1. I can safely create another thread (with new handle / connection) in my application and modify SAME database at the same time 2. I want use second connection in thread for importing while main thread working "normally". By import I mean not backup, but importing documents (CSV) 3. Can user modify table while another thread importing to it (in transaction)? What are restrictions? What should I look out for? 4. Records inserted by second thread will be "visible" for first connection after second thread commit transaction? Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid
Ok I have done this with two commnads where first check if record exist. I tried do this with one command because I want to speed up importing from csv. CSV has one table for example: name | surname | country. I want split repeated columns to dictionary tables like: table customers - name - surname - id_country // FK to table countries I'm looking for one command which before inserting to "customers" will look to table countries and check if country exists, if not then insert and return id, else return existing id. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT OR IGNORE - returning new or existing rowid
Hi, When I use INSERT OR IGNORE, if insertion fail (record exists), then sqlite3_last_insert_rowid does return nothing. Is exists similar solution which: 1. If insert success then return new rowid 2. If insert fail (record exists) then return rowid of existing record Can I get this information in one command? Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
I don't understand :/ So what is solution in my case? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
I thought about reopen connection (this would solve my problem in simple way) but my dependencies are tricky. I'm using free pascal which have TSQLite3Connection. This class handle sqlite connection, so I can attach multiple tables in the same session, so I can share temp tables between all tables in my database. If I close connection, I lose data from all temp tables :/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
Can I call vacuum on custom tables (for example only on temp tables) ? Because vacuum takes some time (my "normal" tables are big and often modified, trim of those tables are not important for me) and I would like to free up memory taken only by temp tables ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite - Dropping TEMP table doesn't free allocated memory
Hi, I'm using PRAGMA temp_store=2 so all temp tables are created in memory. I have temp table with 1 000 000 000 records. Memory used by my test application grow up to ~80 MB. If I delete all records from this table or drop table, then my application still use 80 MB of memory. I have tried also PRAGMA shrink_memory. Why sqlite don't free memory? Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] On-line documentation license
Hello, I'm in the process of writing my bachelors and I'd like to use the awesome SQL syntax diagrams for DML and perhaps DDL statements from SQLites on-line documentation. I can't find anything on the license of the documentation (apart from "The details here are unclear."), so I thought it'd be best to ask - can I do that? Of course I will not claim any copyright for the diagrams and the appropriate information shall be added to the bibliography. The project on which I base my bachelors is a highly reusable, object oriented open source (code LGPL, examples BSD license) web application framework written in PHP. For data storage it's using SQLite or MySQL (and easily addaptable for other SQL dialects). The bachelors is being written in Polish but the source code is in English. I plan to release the framework (called SpInq) to the public at the end of this year, this time with English docs. PS. What was used to produce the diagrams, and would it be possible to get vectorized versions of them? (if vectorized would require any extra work, please ignore, the bitmap from the website will do) Thanks in advance, Krzysztof `ChanibaL` Bociurko <sql...@chanibal.net> xmpp://jab...@chanibal.net, http://chanibal.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] On-line documentation license
Hello, I'm in the process of writing my bachelors and I'd like to use the great SQL syntax diagrams for DML and perhaps DDL statements from SQLites on-line documentation. I can't find anything on the license of the documentation (apart from "The details here are unclear."), so I thought it'd be best to ask - can I do that? Of course I will not claim any copyright for the diagrams and the appropriate information shall be added to the bibliography. The project on which I base my bachelors is a highly reusable, object oriented open source (code LGPL, examples BSD license) web application framework written in PHP. For data storage it's using SQLite or MySQL (and easily addaptable for other SQL dialects). The bachelors is being written in Polish but the source code is in English. I plan to release the framework (called SpInq) to the public at the end of this year. PS. What was used to produce the diagrams, and would it be possible to get vectorized versions of them? (if vectorized would require any extra work, please ignore, the bitmap will do) Thanks in advance, -- Krzysztof `ChanibaL` Bociurko <sql...@chanibal.net> xmpp://jab...@chanibal.net, http://chanibal.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update callback and REPLACE
Hi all, I have a question about an update callback. It's supposed to be called for every row that's inserted, updated and deleted and it works fine. Now there's a little thing calles INSERT OR UPDATE (and its friends) which removes conflicting rows before the INSERT. The thing is, the callback gets called for the row inserted, but NOT for the row deleted. Is this expected behaviour? I'm depending on the callback to notify me of *all* changes and this one missing row is not something I like :) -KS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ORDER BY / HAVING clauses and aliased field names
Jay napisaÅ(a): There is no way I know of to avoid doing this. I couldn't find one in Oracle either. You can refer to them by their number: Select a, b, c FROM t Order by 1 is the same as: Select a, b, c FROM t Order by a If only this worked in HAVING clauses ;) But i guess HAVING 1 = query_parameter would be to confusing for the engine ;/ OK - I'll probably go into declaring another alias name and/or repeating the whole expression. The whole thing is however confusing. Is that a bug? Shouldn't aliased names get some kind of prority when thay are being referred to in HAVING / ORDER BY query sections? AFAIR those two clauses are being resolved at the very end , after WHERE and JOINs handling, also all the result columns are already defined - so how come is there any ambiguity? Either way - thanks for all your suggestions, With all regards, -- Krzysztof Kotowicz
Re: [sqlite] ORDER BY / HAVING clauses and aliased field names
Jay napisaÅ(a): WHERE 1 What is this supposed to do? It's not a boolean expression. AFAIK it would render the same result as 1=1 (it seems that it casts to boolean true), at least that behaviour was observed in MySQL. Nevermind, 1=1 or any other expression returning true for every row is sufficient for this query - this is just a placeholder when no search criteria is specified by the user (the query is dynamically generated). Did you try: ORDER BY coalesce(cd1.country_name, cd2.country_name) in place of: ORDER BY country_name Yes - it does help, thanks for suggestion, however - it would certainly be better if I could keep the ORDER BY / HAVING clauses expression-free (i.e. with only the field names). Is there any other way I could: a) avoid using expressions in ORDER BY / HAVING clauses b) leave the aliased field name unchanged - for compatibility (e. g. I have other SQL statements for fallback lang. substitution disabled and I simply refer to country_description.country_name field in them - so that I have the same field names in query results). I don't have any idea - maybe aliased field names share some kind of "namespace" I could refer them by in ORDER BY clauses like '_aliased.country_name' ? Thanks for your help in advance. With regards, -- Krzysztof Kotowicz
[sqlite] ORDER BY / HAVING clauses and aliased field names
Hi!
I'm porting some MySQL code to SQLite 2.8.16.
I'm having problems with a few multi-tables queries used by my application:
The problems seems to be related with inability of including "calculated
field" names in ORDER BY and/or HAVING clauses, although I might be
wrong (and most probably I am) .
The simplest of the queries:
SELECT countries.*,
coalesce(cd1.country_name, cd2.country_name) AS country_name,
coalesce(cd1.language_id, cd2.language_id) AS language_id
FROM countries
LEFT JOIN countries_description AS cd1 ON
(countries.country_id=cd1.country_id AND cd1.language_id=1)
LEFT JOIN countries_description AS cd2 ON
(countries.country_id=cd2.country_id AND cd2.language_id=0)
WHERE 1
GROUP BY countries.country_id
HAVING 1
ORDER BY country_name ASC;
returns error:
[nativecode=ambiguous column name: country_name]
Firstly let me explain given query:
I'm using fallback language substitution feature. I have one 'countries'
table with country_id as primary key, and 'countries_description' table
holding country names in different languages (two field primary key) -
schema below:
CREATE TABLE 'countries' (
'country_id' char(2) NOT NULL default '',
PRIMARY KEY ('country_id')
);
CREATE TABLE 'countries_description' (
'country_id' char(2) NOT NULL default '',
'language_id' tinyint(4) NOT NULL default '0',
'country_name' varchar(20) NOT NULL default '',
PRIMARY KEY ('country_id','language_id')
);
With presented query I'd like to retrieve all country_ids with assigned
name in language_id=1, and if there is no entry for that country in
language_id=1, get translated entry in language_id = 0 (fallback
language). Therefore i'm using two JOINS and coalesce() function.
Worth noting - i'm using PRAGMA short_column_names = 1; for
compatibility in multi-table SELECT results.
The code works perfectly in MySQL. SQLite complains about ambiguity of
'country_name' field in ORDER BY clause.
Using cd1.country_name or cd2.country_name helps, BUT it's not what i
expect of this query, i'd like to order by appropriate (fallback
translated or not) entries - therefore i wanted to use calculated field
'country_name' alias.
When I change the alias name to anything else (i.e. not to existing
field name)- it works OK, but is it possible to use alias name that is
not causing conflict with field names WITHOUT having to rewrite code
retreiving results (because of column name change)?
The same situation occurs with HAVING clause - using alias name of
calculated field throws ambiguity error because i'm joining tables with
fields named like alias - although they never appear as result fields
directly (they are used only in expression of alias field).
If you could help me with this mater, I would be very thankful. Any
tricks, hacks...
Thanks in advance,
With regards,
Krzysztof Kotowicz
[sqlite] Win CE support
If I was to provide patches for Windows CE support (and maintain win ce code in the future): a) would they get accepted (assuming the quality is up to sqlite's quality) ? Basically I'm asking if it's worthwile for me to spend time on doing that (it wouldn't if e.g. the patches wouldn't get integrated into official sources no matter how good they are) b) what's the best way to make it happen? open a bug and attach diff to it? Krzysztof Kowalczyk | http://blog.kowalczyk.info
