Re: [sqlite] Subtract times hh:mm:ss
Am 02.10.2012 21:23, schrieb Bart Smissaert:
Nice one, thanks for that.
Just in case you use the COM-Wrapper - and this operation takes
place in a plain Recordset-Select (and isn't stored somewhere
in the DB itself, e.g. in a Trigger), then you can reduce the
amount of function-calls a bit, when you use something like that:
Define the Table-Field with the wrapper-supported Time-FieldType,
which ends up as 'hh:mm:ss' Text in the SQLite-DB-Field - but is
correctly translated back into a Date-Type in the receiving cRecordset.
To safe a few CPU-Cycles in the Query, you can directly place
the Double-representation of a VB(A)-Date in the Query-String
(done in the Example per ? Placeholder in a cSelectCommand).
The Diff-expression in the Select then looks this way:
CTime(? - CDbl(HMS))
CDbl, to convert the TextContent of the HMS-Field into the
Double-representation of a VB-Date - and CTime to
convert the difference back into a 'hh:mm:ss' String.
Not sure, if that is faster than SQLites built-in Date/Time-Functions,
but worth a try...
' Into a Form (clicking the Form gives the Delta to its "LoadTime")
' Output then i.e.: HMS 04:21:27 True DTS 00:00:01 True
Option Explicit
Private Cnn As New cConnection, GetDeltaCmd As cSelectCommand
Private Sub Form_Load()
Cnn.CreateNewDB '<- InMemory
'the wrappers Time-FieldType ensures 'hh:mm:ss' TextFormat in the DB
Cnn.Execute "Create Table T(HMS Time)"
With Cnn.CreateCommand("Insert Into T Values(?)")
.SetTime 1, Now()
.Execute
End With
Const GetDeltaSQL = "Select HMS, CTime(? - CDbl(HMS)) As DTS From T"
Set GetDeltaCmd = Cnn.CreateSelectCommand(GetDeltaSQL)
End Sub
Private Sub Form_Click()
GetDeltaCmd.SetDouble 1, Now() 'we place the Param directly as Double
With GetDeltaCmd.Execute 'returns a cRecordset
Debug.Print !HMS.Name, !HMS.Value, VarType(!HMS.Value) = vbDate,
Debug.Print !DTS.Name, !DTS.Value, VarType(!DTS.Value) = vbString
End With
End Sub
Olaf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue with SQLite3 for WinRT ARM
On Fri, Sep 28, 2012 at 9:15 AM, Richard Hipp wrote: > > > On Fri, Sep 28, 2012 at 9:06 AM, Clemens Ladisch wrote: > >> Christian Le Gall wrote: >> > I have included an example project >> >> ... and the mailing list server has stripped it. >> Please put it somewhere on the web. >> > > The SQLite developers got the project files last night, through a > back-channel. We are working the problem now. > > Our best theory so far is that this is a compiler bug. But we haven't > proven that yet. As I said, we are still working the problem... > We have no confirmed that the problem was in the ARM optimizer on the latest version of MSVC. We have checked in a change to SQLite to work around the problem here: http://www.sqlite.org/src/info/9fab9edd0d -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Custom SQL functions and "nullvalue"
Howdy!
The SQLite3 Tcl interface has a "nullvalue" command, which determines how NULLs
are represented as Tcl values. If you do a query on a NULL value, you get the
"nullvalue" value. ("nullvalue" defaults to the empty string.)
However, if a NULL value is passed to a custom SQL function, defined using the
Tcl [$db function] command, the function body (a Tcl proc) appears to get the
empty string instead of the desired "nullvalue" for the given value. This
seems like a bug to me. Am I wrong?
I'm using SQLite3 3.7.7.1; the function is called from a trigger (if that
matters).
Will
--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subtract times hh:mm:ss
Nice one, thanks for that.
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik wrote:
> On 10/2/2012 1:00 PM, Bart Smissaert wrote:
>>
>> Is there a way to subtract times in the text format hh:mm:ss
>> and return the difference in the same format?
>
>
> select time(julianday('03:22:11') - julianday('01:22:33') - .5);
> select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
> 'unixepoch');
>
> Both of these return '01:59:38'.
> --
> Igor Tandetnik
>
> ___
> 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
Re: [sqlite] Subtract times hh:mm:ss
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Subtract times hh:mm:ss
Is there a way to subtract times in the text format hh:mm:ss and return the difference in the same format? I am sure it could be done with various calculations and casts, but maybe there is a simple, ready-made way to do this. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] truncate after using chunk size and related...
Hi,
I have some problems trying to truncate a db file to its "normal" size.
I use sqlite as a file format in a mostly append only mode.
Because I need to be prepared for high data rate, I chose to use chunk size
to avoid file fragmentation (e.g, I use a chunk size of 4MB, but sometimes
up to 100MB for blobs).
But very often data is very small, i.e. a few kb; therefore I need/want to
truncate it back to the "normal" size.
What is the correct way of doing it?
Note: I am using WAL mode during writing, and switching back on stop (when
I also want the truncation).
Another note: I did find a way to achieve this (in app code), but I'm not
sure if it's guaranteed. Therefore I'm not even writing how :), waiting for
better answers.
Using "PRAGMA auto_vacuum" and related does NOT solve anything, because the
file is just bigger; there are no pages in "freelist".
Vacuum is also not really an option; copying data is very expensive (can be
very large), when there is nothing really to do.
I might also have readers, so closing and manually truncating the file is
also not an option.
This is extract of summary from analyzer:
Page size in bytes 8192
Pages in the whole file (measured) 8
Pages in the whole file (calculated).. 8
Pages on the freelist (per header) 0 0.0%
Size of the file in bytes. 65536
At this point my file is 4MB, and there is no way to reduce it the required
size of 8 pages(see above).
Enabling debug and reading the source code, I found that pager_truncate is
never called because the line
if( pPager->dbSize!=pPager->dbFileSize ){
does not pass condition; both values at this point (I added some logging
lines) show 512 pages (= 4MB file / 8kB page size).
I understand that dbSize is initialized based on file size on open, and
never really shrinks (unless one really writes many pages, then delete
them)...
Further reading, I found a possible solution: Why shouldn't PRAGMA
incremental_vacuum truncate the file even if there is no free list???
I added this code to sqlite3BtreeIncrVacuum, before sqlite3BtreeLeave
(therefore working regardless of autoVacuum mode!):
//gc: truncate if needed!
> Pager *pPager = pBt->pPager;
> if(pPager->dbSize > pBt->nPage){
>if( pPager->eState>=PAGER_WRITER_CACHEMOD ||
> pager_open_journal(pPager) == SQLITE_OK ){
> sqlite3PagerTruncateImage(pPager, pBt->nPage);
>}
> }
> sqlite3BtreeLeave(p);
> return rc;
>
Is there any problem with this approach?? It looks to work as I want,
except it writes pages to journal before truncating.
This looks completely unnecessary as they contain no data; especially when
the chunk is 100MB (yes, I also have this case for blobs).
Maybe someone with more knowledge can solve it, this way or another.
Thanks for attention,
Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with Foreign Key constraints
On 10/1/12 1:32 PM, "Duquette, William H (318K)" wrote: >Howdy! > >I have some code that does the following: > >1. Takes a snapshot of some number of database tables, e.g., saves the >data from those tables as a text string. >2. Later, clears the tables and restores their content from the snapshot. > >The snapshot is restored by creating a new INSERT statement for each row, >with the literal column values in it, and evaluating each of these >statements in sequence. > >The tables contain foreign key constraints with "DEFERRABLE INITIALLY >DEFERRED" specified; thus, I execute all of these INSERTs within a >transaction so that I won't get spurious constraint failures. > >This has been working, but it's slow, so I'm trying to rework the >algorithm to use queries with variable references. Then I update the >variables once for each row, and call the same query over and over again. > This is much faster...but at the end of the transaction I'm getting a >foreign key constraint failure. So far as I can tell, all the data is as >it should be; and the only difference, so far as I can tell, is that I'm >now using variables rather than literals. NULLs. The old code preserves NULLs, the new code doesn't. *Never mind.* :-) Will > >Any ideas? > >Will > > >-- >Will Duquette -- william.h.duque...@jpl.nasa.gov >Athena Development Lead -- Jet Propulsion Laboratory >"It's amazing what you can do with the right tools." > >___ >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
