[sqlite] User-defined SQL functions

2016-02-22 Thread E.Pasma
Hi, forget to mention that a function like this was earlier considered as being tricky and living dangerously. Also we found a bug (missing break) between line 80 and 81 which will lead to a memory leak every time a text value is stored ... switch (pval->t) { case SQLITE_INTEGER:

[sqlite] MIN/MAX of column loses decltype

2016-02-22 Thread Stephan Beal
On Mon, Feb 22, 2016 at 7:15 PM, Eric Hill wrote: > to the data. But SQLite doesn't really have any functions that manipulate > Julian dates (as far as I can tell). We have written our own SQL date > functions anyway. So maybe I can just avoid this issue entirely. > FWIW, strftime() uses them

[sqlite] MIN/MAX of column loses decltype

2016-02-22 Thread Simon Slavin
On 22 Feb 2016, at 6:15pm, Eric Hill wrote: > Length(theDate) returns 9, and typeof(theDate) returns "real". I assume > typeof is just returning the character version of the column affinity. No. typeof() is returning the type of that specific piece of data, not the column in general. In th

[sqlite] MIN/MAX of column loses decltype

2016-02-22 Thread Eric Hill
Hey, Simon, Length(theDate) returns 9, and typeof(theDate) returns "real". I assume typeof is just returning the character version of the column affinity. Typeof(MAX(theDate)) and length(MAX(theDate)) also return "real" and 9 respectively. At this point, I am rethinking the decision to conver

[sqlite] MIN/MAX of column loses decltype

2016-02-22 Thread Keith Medcalf
Actually, you can. If you pass a real to datetime it will return the correct result: sqlite> select typeof(cast(julianday('now') as real)); real sqlite> select datetime(cast(julianday('now') as real)); 2016-02-23 00:43:37 This is because datetime (the time functions) accept a string of the for

[sqlite] Multiple Column index

2016-02-22 Thread Rowan Worth
On 22 February 2016 at 17:07, Michele Pradella wrote: > CREATE INDEX indexAB ON test(DateTime,CarPlate); CREATE INDEX indexA ON test(DateTime); > So if I have a query like this >>> "SELECT * FROM table_name WHERE DateTime>> DateTime>VALUE_MIN" >>> >> > Generally speaking,

[sqlite] MIN/MAX of column loses decltype

2016-02-22 Thread Simon Slavin
On 22 Feb 2016, at 4:02pm, Eric Hill wrote: > I can call sqlite3_column_decltype() and get back "REAL_DATE" and know I > should convert. > > However, if I execute this SQL: > > SELECT MAX( theDate ) FROM t1; > > My call to sqlite3_column_decltype() returns nothing - the decltype of the > co

[sqlite] MIN/MAX of column loses decltype

2016-02-22 Thread Eric Hill
Hey, I'm at 3.8.11.1 of SQLite - could move to more recent version if it would help. When I move date columns into SQLite, I declare their type as "REAL_DATE". I do this because I convert from my application's date (seconds since 1904) to Julian date (a date that SQLite likes, sort of) when th

[sqlite] User-defined SQL functions

2016-02-22 Thread E.Pasma
21 feb 2016, Igor Tandetnik: > On 2/21/2016 12:09 PM, Olivier Mascia wrote: >> Is it possible to implement a SQL function >> (https://www.sqlite.org/c3ref/create_function.html >> ), which implementation would be able to return the same value for >> the duration of the current transaction? >> >

[sqlite] FTS4 inquiry (non-printable characters, column delimiters)

2016-02-22 Thread Dan Kennedy
On 02/22/2016 10:27 AM, Ephraim Stevens wrote: > Greetings All, > > I use the Full Text Search facilities extensively. The feature is intuitive > and works beautifully and super fast even with 100GB+ databases. I must > applaud the SQLITE dev team on such amazing work. > > I have two questions with

[sqlite] FTS design document

2016-02-22 Thread Dan Kennedy
On 02/21/2016 03:54 PM, Charles Leifer wrote: > Is anyone aware of a design doc for any of the FTS implementations? Looking > for something a bit more technical than the docs. If not, where in the > source would you recommend starting? Thanks! There is no such document unfortunately. The FTS5 cod

[sqlite] MIN/MAX of column loses decltype

2016-02-22 Thread Richard Hipp
On 2/22/16, Eric Hill wrote: > > But SQLite doesn't really have any functions that > manipulate Julian dates (as far as I can tell). If you mean "Julian Calendar" you are correct. If you mean "Julian Day Number", then all of the built-in SQLite date-manipulation functions will work fine. Note t

[sqlite] MIN/MAX of column loses decltype

2016-02-22 Thread Richard Hipp
On 2/22/16, Eric Hill wrote: > I assume > typeof is just returning the character version of the column affinity. No. typeof returns the datatype of the value in its argument. -- D. Richard Hipp drh at sqlite.org

[sqlite] FTS design document

2016-02-22 Thread Charles Leifer
Perfect, thank you! On Feb 22, 2016 1:55 AM, "Dan Kennedy" wrote: > On 02/21/2016 03:54 PM, Charles Leifer wrote: > >> Is anyone aware of a design doc for any of the FTS implementations? >> Looking >> for something a bit more technical than the docs. If not, where in the >> source would you recom

[sqlite] Select top 1 from duplicate values

2016-02-22 Thread Quan Yong Zhai
sqlite> create table mytable(f1 integer,f2 integer,f3 integer); sqlite> insert into mytable values(1,2,8),(1,3,9),(2,4,8),(2,5,2),(3,6,4),(3,7,8),(4,2,4); sqlite> select f1, max(f2), f3 from mytable where (f3&8)!=0 group by f1; 1|3|9 2|4|8 3|7|8 sqlite>

[sqlite] Select top 1 from duplicate values

2016-02-22 Thread ad...@shuling.net
Hi, I am using SQLite 3.11. I create a table as follows: CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER, F3 INTEGER); Then add the following records: INSERT INTO MyTable (F1, F2, F3) Values (1, 2, 8);

[sqlite] Multiple Column index

2016-02-22 Thread Michele Pradella
> On 22 Feb 2016, at 9:07am, Michele Pradella > wrote: > >> Already done this check. My last question was about to reduce the number of >> indexes on table avoiding kill a "quite unnecessary" index that if used do a >> better job that the other. >> Generally speaking, I think that if you use a

[sqlite] Multiple Column index

2016-02-22 Thread Michele Pradella
>>> Your indexes are badly designed. >>> >>> You require the following two indexes: >>> CREATE INDEX indexAB ON test(DateTime,CarPlate); >>> CREATE INDEX indexBA ON test(CarPlate,DateTime); >>> >>> The indexes: CREATE INDEX indexA ON test(DateTime); CREATE INDEX indexB ON test(CarPlate);

[sqlite] Multiple Column index

2016-02-22 Thread Michele Pradella
> Your indexes are badly designed. > > You require the following two indexes: > CREATE INDEX indexAB ON test(DateTime,CarPlate); > CREATE INDEX indexBA ON test(CarPlate,DateTime); > > The indexes: >> CREATE INDEX indexA ON test(DateTime); >> CREATE INDEX indexB ON test(CarPlate); > serve no useful

[sqlite] Multiple Column index

2016-02-22 Thread Simon Slavin
On 22 Feb 2016, at 9:07am, Michele Pradella wrote: > Already done this check. My last question was about to reduce the number of > indexes on table avoiding kill a "quite unnecessary" index that if used do a > better job that the other. > Generally speaking, I think that if you use and index

[sqlite] Multiple Column index

2016-02-22 Thread Simon Slavin
On 22 Feb 2016, at 8:39am, Michele Pradella wrote: >> Your indexes are badly designed. >> >> You require the following two indexes: >> CREATE INDEX indexAB ON test(DateTime,CarPlate); >> CREATE INDEX indexBA ON test(CarPlate,DateTime); >> >> The indexes: >>> CREATE INDEX indexA ON test(DateTi

[sqlite] User-defined SQL functions

2016-02-22 Thread Keith Medcalf
You have a bug (missing break) between line 80 and 81 which will lead to a memory leak every time a text value is stored ... switch (pval->t) { case SQLITE_INTEGER: pval->n=0; pval->i=sqlite3_value_int64(arg); break; case SQLITE_TEXT:

[sqlite] Question about BerkeleyDB "SQLited"

2016-02-22 Thread javaj1...@elxala.com
yes, you are right, I actually have the same version, two years old anyway The feature I was missing is "savefile and writefile" functions for blob fields which comes I think in 2014-06 (http://www.sqlite.org/src/finfo?name=ext/misc/fileio.c) So for that problem, probably it is enough adding filei