[sqlite] Sqlite Query Builder

Hi Does anybody know of a visual query builder which can be used on Linux to create syntactically-correct views/queries for a Sqlite database? I'm thinking of something along the lines of the query builder in MS Access or LibreOffice Base. Thanks Nige

[sqlite] determining is-leap-year in sqlite

On 18/02/2016 21:55, Stephan Beal wrote: > On Thu, Feb 18, 2016 at 10:42 PM, Stephan Beal at public.gmane.org> > wrote: > >> Here we go: >> >> http://fossil.wanderinghorse.net/download/cal.sql >> > > sorry, one more: it was just updated with minor doc improvements and better > syntax conformance

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 10:42 PM, Stephan Beal wrote: > Here we go: > > http://fossil.wanderinghorse.net/download/cal.sql > sorry, one more: it was just updated with minor doc improvements and better syntax conformance (i had used a lot of double-quotes simply out of recent scripting habit).

[sqlite] determining is-leap-year in sqlite

On 2016/02/18 10:38 PM, Stephan Beal wrote: > On Thu, Feb 18, 2016 at 9:16 PM, Stephan Beal > wrote: > >> Okay, i've hit a small stump and i'm looking for a hint without giving it >> away: >> >> January and February 2016: >> >> [stephan at host:~/tmp]$ sqlite3 < cal.sql >>1 2

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 10:22 PM, Stephan Beal wrote: > On Thu, Feb 18, 2016 at 10:19 PM, Richard Hipp wrote: > >> On 2/18/16, Stephan Beal wrote: >> > >> > Thanks again to all for the feedback and suggestions! >> > >> >> After your talk, can we publish your calendar CTE as another example >>

[sqlite] Find SQLITE_BUSY reason?

I realized I never wrote back to the list about the solution (so others who might get into the same kind problem can see what the actual problem was for us). The problem wasn't caused by the update to a newer version of sqlite. By coincidence the firmware which included the new sqlite version

[sqlite] determining is-leap-year in sqlite

On 2016/02/18 10:16 PM, Stephan Beal wrote: > On Thu, Feb 18, 2016 at 8:59 PM, R Smith wrote: > >> etc. >> Nice job on the calendar and good luck with the presentation! > > Okay, i've hit a small stump and i'm looking for a hint without giving it > away: > > January and February 2016: > >

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 10:19 PM, Richard Hipp wrote: > On 2/18/16, Stephan Beal wrote: > > > > Thanks again to all for the feedback and suggestions! > > > > After your talk, can we publish your calendar CTE as another example > in the SQLite documentation? > i would be humbled. No need to

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 9:52 PM, Stephan Beal wrote: > -- >> Feb 2016 >> 1 2 3 4 5 6 7 >> 8 9 10 11 12 13 14 >> 15 16 17 18 19 20 21 >> 22 23 24 25 26 27 28 >> >> Thank you!!! >> >> Not half bad, if i may say so :). >> > > Except that Feb. has 29 days this

[sqlite] determining is-leap-year in sqlite

On 2016/02/18 9:31 PM, Stephan Beal wrote: >> The first CTE sets up some parameters in the first 3 fields used to draw >> the graph - play with those parameters for fun. >> > i wouldn't even know what to do with them :/. Change them of course! :) - to be specific, the first 3 values from the

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 9:50 PM, Stephan Beal wrote: > i could do with the \r, but CHAR(10) does indeed do the trick: > withOUT the \r... > > -- > Feb 2016 > 1 2 3 4 5 6 7 > 8 9 10 11 12 13 14 > 15 16 17 18 19 20 21 > 22 23 24 25 26 27 28 > > Thank

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 9:42 PM, R Smith wrote: > Use the Mandelbrot set CTE for a cheat-sheet... > CHAR(13)||CHAR(10)... etc. Doh! i could do with the \r, but CHAR(10) does indeed do the trick: select str from strMonth where year=2016 and monthNum in (1,2) looks like... [stephan at

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 9:16 PM, Stephan Beal wrote: > Okay, i've hit a small stump and i'm looking for a hint without giving it > away: > > January and February 2016: > > [stephan at host:~/tmp]$ sqlite3 < cal.sql > 1 2 3 > 4 5 6 7 8 9 10 > 11 12 13 14 15 16 17 > 18 19

[sqlite] determining is-leap-year in sqlite

Stephan, > Yeah, i should have mentioned that i'm simplifying to the range of dates > "sometime within my lifetime." Anything else is irrelevant for my > presentation ;). then, you only need to calculate ( year % 4 ). This gives you a window from 1900-3-1 to 2100-2-28 (two complete centuries!).

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 8:59 PM, R Smith wrote: > etc. > Nice job on the calendar and good luck with the presentation! Okay, i've hit a small stump and i'm looking for a hint without giving it away: January and February 2016: [stephan at host:~/tmp]$ sqlite3 < cal.sql 1 2 3

[sqlite] determining is-leap-year in sqlite

On 2016/02/18 8:34 PM, Stephan Beal wrote: > Every calendar known to man sucks rocks in some regard or other, so > i'm not gonna sweat it. This is just a demo, and i've got a few hours > of budget left on it, so i'm working on this as the finale. (The > Mandelbrot CTE will be first, just to

[sqlite] Can't understand why I need this cast

The '+ - * / ' operator trying to convert text to numeric, so typeof(strftime('%s', starttime)-(180*60)) -> integer typeof('1') -> text typeof('1' + 0) -> integer typeof( 1 || 0 ) -> text ???: Rob Willett :

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 7:53 PM, R Smith wrote: > May I offer this CTE from the tutorials in SQLitespeed in case you have a > Math library linked. > (your math function names for cos(), sin() and degtorad() may differ): > with graph(gWidth, aInc, gAngle, gCos, gCosA, gSin, gSinA) AS ( >

[sqlite] determining is-leap-year in sqlite

On 2016/02/18 7:46 PM, Stephan Beal wrote: > Hi, all, > > i just found a useful trick i thought someone else might be able to use... > > As part of a presentation i'm preparing to introduce colleagues to CTEs, > i'm attempting to build a calendar (with output similar to the Unix 'cal' >

[sqlite] MIN/MAX query

On 2016/02/18 7:24 PM, David Bicking wrote: > Whatever mangled the text must have put a 2 in front of the 7, cause the copy > in my sent mail box has a 1 in front of the 7. I never got a copy of my > email from the mailing list, so I never saw the mangled version, just quotes > of it. I

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 7:27 PM, R Smith wrote: > >> While I won't spoil your calendar fun, I have to ask, why not simply use > the functionality SQLite already has to know exactly which months has which > days? > Good question: the presentation is specifically about CTEs and i want to show

[sqlite] Can't understand why I need this cast

It looks like strftime('%s',...) should return an integer and strftime('%J', ...) should return a double value. But it always return text value. ???: Rob Willett : ?2016/?2/?18 18:20 ???: SQLite mailing

[sqlite] MIN/MAX query

On 2016/02/18 4:59 PM, Igor Tandetnik wrote: > On 2/18/2016 4:55 AM, R Smith wrote: >> First of, your intended results require a fundamentally wrong assumption >> about Sets. (SQL is essentially operating on SETs and sets have no >> order). >> You should really have another column, like a

[sqlite] Can't understand why I need this cast

sqlite>Select typeof(strftime('%s','now')); text sqlite>select typeof('1' + 1); integer ???: Rob Willett : ?2016/?2/?18 18:20 ???: SQLite mailing list ??: [sqlite] Can't

[sqlite] determining is-leap-year in sqlite

On Thu, Feb 18, 2016 at 6:59 PM, Eric Rubin-Smith wrote: > > > > > > select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) % > 365) > > > > Here you assume that all years have either 365 or 366 days. Would that it > were so! > > Look at the year 1752 -- you may notice something

[sqlite] MIN/MAX query

> Um, I understand sets; which is why I knew the naive group by wouldn't work. > I guess I should have stated my question is HOW do I define the group so that > in the order of I,L, clusters of common V values are a "group". I need to > return the value of I and V, with the minimum and maximum L

[sqlite] determining is-leap-year in sqlite

Hi, all, i just found a useful trick i thought someone else might be able to use... As part of a presentation i'm preparing to introduce colleagues to CTEs, i'm attempting to build a calendar (with output similar to the Unix 'cal' command). (Please no spoilers - let me figure it out!) As part

[sqlite] NuGet problem

On 18 Feb 2016, at 5:49pm, Carl Barnes wrote: > QSqlDatabase::addDatabase("QSQLITE"); Ah. You're using Qt. I'm hope a Qt expert will be along to answer your question. If not, you should be able to find support in a Qt forum. Simon.

[sqlite] MIN/MAX query

Whatever mangled the text must have put a 2 in front of the 7, cause the copy in my sent mail box has a 1 in front of the 7.?? I never got a copy of my email from the mailing list, so I never saw the mangled version, just quotes of it. I assumed you made the typo and didn't want to nit-pick

[sqlite] determining is-leap-year in sqlite

> Excellent CTE query, thanks, but one question: the query uses > group_concat() and the documentation says 'The order of the concatenated > elements is arbitrary.' To a primitive observer any sufficiently advanced technology appears to be magic. group_concat() does not concatenate items in

[sqlite] MIN/MAX query

Thanks. You understood my intention, and confirmed my fear that I couldn't do it efficiently in SQL. David From: Igor Tandetnik To: sqlite-users at mailinglists.sqlite.org Sent: Thursday, February 18, 2016 9:59 AM Subject: Re: [sqlite] MIN/MAX query On 2/18/2016 4:55 AM, R Smith

[sqlite] MIN/MAX query

Um, I understand sets; which is why I knew the naive group by wouldn't work. I guess I should have stated my question is HOW do I define the group so that in the order of I,L, clusters of common V values are a "group". I need to return the value of I and V, with the minimum and maximum L in that

[sqlite] determining is-leap-year in sqlite

On 2/18/16, Stephan Beal wrote: > > Thanks again to all for the feedback and suggestions! > After your talk, can we publish your calendar CTE as another example in the SQLite documentation? -- D. Richard Hipp drh at sqlite.org

[sqlite] determining is-leap-year in sqlite

On 2/18/16, R Smith wrote: > > Use the Mandelbrot set CTE for a cheat-sheet... > CHAR(13)||CHAR(10)... etc. > Simpler: char(13,10). The char() function takes one *or more* arguments and generates one character for each. -- D. Richard Hipp drh at sqlite.org

[sqlite] Can't understand why I need this cast

On 18 February 2016 at 15:29, Richard Hipp wrote: > On 2/18/16, Simon Davies wrote: >> > > The documentation says: "An expression of the form "CAST(expr AS > type)" has an affinity thta is the same as a column with a declared > type of "type".". Since the RHS of the <= operator now has

[sqlite] Can't understand why I need this cast

On 18 February 2016 at 12:34, Richard Hipp wrote: > On 2/18/16, Simon Davies wrote: >> sqlite> select '11' <= 11; >> 0 >> sqlite> select '11' <= cast(11 as integer); >> 1 >> sqlite> select '11' >= 11; >> 1 > > Sort order in SQLite is: (1) NULLs, (2) Numeric values in numeric > order, (3)

[sqlite] NuGet problem

On 18 Feb 2016, at 2:29pm, Carl Barnes wrote: > I'm expecting this file autosc.sqlite along with the other autosc files. I'm sorry but I don't know what 'autosc' files are. AutoSys ? I hope someone else can help. Simon.

[sqlite] NuGet problem

On Thu, 18 Feb 2016 13:37:59 +1100, wrote: > Thanks for the answer. At least now I know why. > > I have already written all the P/Invoke code. It's written, tested, working > fine, using the sqlite3.dll size 658,797. Having the header and EXE is > convenient too, in a development context. > > I

[sqlite] NuGet problem

On 18 Feb 2016, at 1:51pm, Carl Barnes wrote: > I'm having problems getting the windows .exe sqlite3. When I go to the > sqlite.org/download.html site I only see the dll and no .exe. > What is it that I'm missing? It's now packaged with other programs. Look for "Precompiled Binaries" on that

[sqlite] Can't understand why I need this cast

Consider asql> select '1' >= 1, '1' <= 1; '1' >= 1 '1' <= 1 1 0 This is because of the implicit ordering of TEXT and INTEGER values. Strftime() returns a text; in the expression "strftime() + 1" the arithmetic operator "casts" its left hand operand to integer

[sqlite] Get count of unique values?

Hi, I create a table as follows: CREATE TABLE MyTable (F1 INTEGER, F2 INTEGER); Then add the following records: INSERT INTO MyTable (F1, F2) Values (1, 2); INSERT INTO MyTable (F1, F2) Values (1, 3); INSERT INTO MyTable (F1, F2) Values (2, 4); INSERT INTO MyTable (F1, F2) Values (2, 5); INSERT

[sqlite] NuGet problem

Thanks for the answer. At least now I know why. I have already written all the P/Invoke code. It's written, tested, working fine, using the sqlite3.dll size 658,797. Having the header and EXE is convenient too, in a development context. I was able to install the package you suggest, but there is

[sqlite] determining is-leap-year in sqlite

On 2/18/16, Eric Rubin-Smith wrote: >> >> >> select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) % >> 365) >> > > Here you assume that all years have either 365 or 366 days. Would that it > were so! > > Look at the year 1752 -- you may notice something odd happened that >

[sqlite] Can't understand why I need this cast

Richard, The example is highly contrived. The actual SQL we are/were actually interested in is strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60)), AND strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60)) The rest of the SQL was to test what was

[sqlite] determining is-leap-year in sqlite

> > > select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) % 365) > Here you assume that all years have either 365 or 366 days. Would that it were so! Look at the year 1752 -- you may notice something odd happened that September. :-) Eric

[sqlite] NuGet problem

The autosc.sqlite I am using in the setDatabaseName function. The two lines below is how I connect to the database in windows. QSqlDatabase::addDatabase("QSQLITE"); setDatabaseName("C:/SqLite/autosc.sqlite"); Is there a better way in windows to create the db connection? Carl Barnes Software

[sqlite] Can't understand why I need this cast

sqlite> select '11' <= 11; 0 sqlite> select '11' <= cast(11 as integer); 1 sqlite> select '11' >= 11; 1 Certainly seems odd... On 18 February 2016 at 12:20, Rob Willett wrote: > Tim, > > We actually do store the timestamps as epoch seconds, but we wrote a quick > piece of SQL to test something

[sqlite] Can't understand why I need this cast

On 18 Feb 2016, at 12:20pm, Rob Willett wrote: > We know how to solve the problem, we?re puzzled though as our understanding > is clearly wrong. It'll help to test the function typeof(value) on the values you're handling like typeof(strftime('%s' , starttime)) to see what's happening.

[sqlite] Can't understand why I need this cast

Tim, We actually do store the timestamps as epoch seconds, but we wrote a quick piece of SQL to test something out and wanted to use the ISO date. Thats when the SQL failed and we couldn?t understand why. We hate it when we don?t understand why things don?t work the way we expect. Our OCD

[sqlite] NuGet problem

Trying to use NuGet to install Sqlite. The error is: "Could not install package 'sqlite.redist 3.8.4.2'. You are trying to install this package into a project that targets '.NETFramework,Version=v4.5', but the package does not contain any assembly references or content files that are compatible

[sqlite] Can't understand why I need this cast

Quad, Thanks, that almost makes sense. What still confuses us is that the other maths expressions work OK without a cast. e.g. strftime('%s' , starttime) - (180 * 60) and (strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime) and strftime('%s' , starttime) >= (strftime('%s'

[sqlite] Can't understand why I need this cast

On 18 Feb 2016 at 10:20, Rob Willett wrote: > I?m sure this is a really dumb question but I?m struggling to > understand why the following SQL is needed for what should be a trivial > SQL expression. > > I?ve minimised the example down to (hopefully) make it simpler. > > I have a table with

[sqlite] MIN/MAX query

Let me see if I can unpuzzle this question - kindly say if it is not correctly assumed: - I have a table: I L V 1 1 A 1 2 A 1 3 A 1 4 B 1 5 B 1 6 A 2 7 A 2 1 C 2 2 C I want to return the minimal and maximum L for each "group" of V in a given I.

[sqlite] Get count of unique values?

On 18 Feb 2016, at 5:42am, wrote: > Then I want to obtain the total count of unique values for F1 field. In the > above sample, since unique F1 field values are 1, 2, 3 and 4, so the count > is 4. How to write SQL query to obtain the value? First, get a list of the unique values: SELECT F1

[sqlite] Can't understand why I need this cast

On 2/18/16, Simon Davies wrote: > > Ok on sort order, but why > > SQLite version 3.8.11.1 2015-07-29 20:00:57 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> > sqlite> select '11' <= 11; > 0 >

[sqlite] Can't understand why I need this cast

Hi, I?m sure this is a really dumb question but I?m struggling to understand why the following SQL is needed for what should be a trivial SQL expression. I?ve minimised the example down to (hopefully) make it simpler. I have a table with an ISO date StartTime in it held as a string.

[sqlite] MIN/MAX query

David Bicking wrote: > I have a table > I L V > 1 1 A > 1 2 A > 1 3 A > 1 4 B > 1 5 B > 1 6 A > 1 7 A > 2 1 C > 2 2 C > I want to return the minimal and maximum L for each "group" of V in a given I. > > The result I want: > I MinL MaxL V > 1 13 A > 1 4

[sqlite] MIN/MAX query

On 2/18/2016 4:55 AM, R Smith wrote: > First of, your intended results require a fundamentally wrong assumption > about Sets. (SQL is essentially operating on SETs and sets have no order). > You should really have another column, like a primary key ID that notes > the position of each line Looks

[sqlite] Storing Images in Blobs - Best Practices

How do you know that it is sqlite that is corrupting the images, could your conversion to nsdata (or elsewhere prior to writing to teh DB) be the culprit? Given your schema if a record has become corrupt then the following two columns (the data for which which would be stored contiguously after

[sqlite] NuGet problem

Thank you Simon for your reply. These are the only pre-compiled Binaries I have found: I found this in the 'tools' bundle, sqldiff.exe, sqlite3.exe, and sqlite3_analyzer.exe I would think the diff and the analyzer are tools, but sqlite3 is a question. I'm expecting this file autosc.sqlite along

[sqlite] Storing Images in Blobs - Best Practices

Hello Joe, I'm inclined to think the problem is in the client side too. I store many GB's of images as blobs and have no issues. It sounds like you're re-using a buffer before the insert has completed. I'd wonder if it's related to how you're binding the parameters to the update.

[sqlite] NuGet problem

I'm having problems getting the windows .exe sqlite3. When I go to the sqlite.org/download.html site I only see the dll and no .exe. What is it that I'm missing? Carl Barnes Software Engineer, CIS Almond Computers, DBA A computer service company specializing in custom software, computer

[sqlite] Possible error using length on UTF-8 characters

Actually the TCL and Sqlite are running on QNX. The web client (Firefox) is running on Windows. I also have an Ubuntu 12.04 VM, so have tried using Firefox from there to connect, and get the same result. However, the Sqlite and TCL combination on the Linux VM do behave as expected - they are

[sqlite] Can't understand why I need this cast

On 2/18/16, Rob Willett wrote: > > select > strftime('%s' , starttime), > strftime('%s' , starttime) - (180 * 60), > strftime('%s' , starttime) + (180 * 60), > strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60)), > (strftime('%s' , starttime) + (180

[sqlite] Can't understand why I need this cast

On 2/18/16, Simon Davies wrote: > sqlite> select '11' <= 11; > 0 > sqlite> select '11' <= cast(11 as integer); > 1 > sqlite> select '11' >= 11; > 1 Sort order in SQLite is: (1) NULLs, (2) Numeric values in numeric order, (3) Strings in the order of whatever collating sequence applies, and (4)

[sqlite] Get count of unique values?

On 2/18/2016 12:42 AM, admin at shuling.net wrote: > Then I want to obtain the total count of unique values for F1 field. select count(distinct F1) from MyTable; -- Igor Tandetnik