Re: [sqlite] EXT :Re: Compiling SQLite3 with MSVC 2010

[Igor Korot]

Michael,

On Sun, Nov 4, 2012 at 5:59 AM, Black, Michael (IS)
 wrote:
> Hmmm...interesting...I'm using VS 2010 Express 32-bit and I would've assumed 
> the warnings would match.  I also enabled SQLITE_64BIT_STATS to try and force 
> the first warning but that didn't cause it.
> Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 16.00.40219.01 for 
> 80x86

Well, I'm getting it with Professional build of 32-bits.

>
> That's a bit disconcerting actually but I guess Express is less pedantic than 
> Studio.
>
> You can always stick this in to shut it up.  There's a yin yang to fixing 
> thesesimple enough to throw a cast in there...but down the road if you 
> make other changes on the right-hand-side datatype that could be of use so 
> you would be suppressing a valid warning.  It's a mixed bag.  So suppressing 
> warnings from picky compilers is the best way to go.
>
> Do you still see then on lower levels?

Not with /W3.
But it's interesting that -Wall does not produce them with gcc...

Thank you.

>
> #pragma warning(disable: 4244) // possible loss of data
>
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Jonas Malaco Filho [jonasmalacofi...@gmail.com]
> Sent: Saturday, November 03, 2012 3:48 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Compiling SQLite3 with MSVC 2010
>
> Actually, on MSVC 2010 I just got the following errors with /W3:
>
> -- Rebuild All started: Project: Shell, Configuration: Release x64
> --
>   shell.c
>   sqlite3.c
> ..\src\sqlite3.c(78502): warning C4244: 'initializing' : conversion from
> 'sqlite_int64' to 'tRowcnt', possible loss of data
> ..\src\sqlite3.c(78503): warning C4244: 'initializing' : conversion from
> 'sqlite_int64' to 'tRowcnt', possible loss of data
> ..\src\sqlite3.c(78504): warning C4244: 'initializing' : conversion from
> 'sqlite_int64' to 'tRowcnt', possible loss of data
> ..\src\sqlite3.c(104145): warning C4244: '=' : conversion from 'i64' to
> 'double', possible loss of data
> ..\src\sqlite3.c(104170): warning C4244: '=' : conversion from 'i64' to
> 'double', possible loss of data
>   Generating code
>   Finished generating code
>   Shell.vcxproj ->
> X:\jonas-malaco-filho\lib\SQLite\Shell\..\bin\x86-64\sqlite3.exe
> -- Rebuild All started: Project: Shell, Configuration: Release Win32
> --
>   shell.c
>   sqlite3.c
> ..\src\sqlite3.c(78502): warning C4244: 'initializing' : conversion from
> 'sqlite_int64' to 'tRowcnt', possible loss of data
> ..\src\sqlite3.c(78503): warning C4244: 'initializing' : conversion from
> 'sqlite_int64' to 'tRowcnt', possible loss of data
> ..\src\sqlite3.c(78504): warning C4244: 'initializing' : conversion from
> 'sqlite_int64' to 'tRowcnt', possible loss of data
> ..\src\sqlite3.c(104145): warning C4244: '=' : conversion from 'i64' to
> 'double', possible loss of data
> ..\src\sqlite3.c(104170): warning C4244: '=' : conversion from 'i64' to
> 'double', possible loss of data
>   Generating code
>   Finished generating code
>   Shell.vcxproj ->
> X:\jonas-malaco-filho\lib\SQLite\Shell\..\bin\x86\sqlite3.exe
> == Rebuild All: 2 succeeded, 0 failed, 0 skipped ==
>
>
> *Jonas Malaco Filho*
>
>
>
> 2012/11/3 Black, Michael (IS) 
>
>> You probably have the warning level turned up high.
>>
>> Up to level 3 it compiles without warnings.  Level 4 starts complaining
>> loudly.
>>
>>
>> Michael D. Black
>> Senior Scientist
>> Advanced Analytics Directorate
>> Advanced GEOINT Solutions Operating Unit
>> Northrop Grumman Information Systems
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on behalf of Igor Korot [ikoro...@gmail.com]
>> Sent: Friday, November 02, 2012 6:25 PM
>> To: General Discussion of SQLite Database
>> Subject: EXT :Re: [sqlite] Compiling SQLite3 with MSVC 2010
>>
>> Richard,
>>
>> On Fri, Nov 2, 2012 at 4:14 PM, Richard Hipp  wrote:
>> > On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot  wrote:
>> >
>> >> Hi, ALL,
>> >> Is anybody trying to compile SQLite with MSVC 2010?
>> >>
>> >
>> > Tests 9e and 9f at http://www.sqlite.org/checklists/3071400#c9 were
>> > performed using MSVC 2010.
>> >
>> >
>> >>
>> >> I am getting a lot of warnings.
>> >> Is there any interest in fixing those?
>> >>
>> >
>> > No.  See http://www.sqlite.org/testing.html#staticanalysis for an
>> > explanation.
>>
>> I just read this link. Interesting information.
>> IIUC, all those warnings are harmless and they do not appear on other
>> platforms.
>> Which means that either gcc is more forgiving or that I am trying to
>> compile my
>> application with some very strange configuration.
>> Or 

Re: [sqlite] SQLite leaks memory

[Igor Korot]

Hi, Clemens,

On Sun, Nov 4, 2012 at 3:11 AM, Clemens Ladisch  wrote:
> Igor Korot wrote:
>> When the user asks to edit the data it starts the transaction, then
>> set the "SAVEPOINT"
>
> Why do you need a savepoint in addition to the transaction?

Because it is a transaction inside transaction for the scenario #3.

>
>> Now the problem: only during the scenario #3 I have a lot of memory
>> leaks. They are reported in the Visual Studio debug
>> window. Looking at the data I did a global search and it turns out
>> that those leakage are coming from the sqlite.
>
> How are those leaks shown?  With a call stack?  In that case, please
> don't keep it a secret.
>
> It would be helpful to show the code for scenario #3.
> (It might be possible that your code doesn't correctly free some
> SQLite object.)

Well I spent a good 30 minutes+ going thru vld report (all those
'false positives')
(I reported them to vld actually). At the very end of it there was a
callstack that
gave a leak. Which is now fixed.
Nevertheless, it would be nice to know what people use to identify leaks...

Thank you.
>
>
> Regards,
> Clemens
> ___
> 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] Performance of sparse sqlite tables

[Simon Slavin]

On 4 Nov 2012, at 5:26pm, "Dominguez Bonini, David"  
wrote:

> I have an application where a table is filled with elements whose primary key 
> is specified at insertion, and is actually a combination of several 
> independent IDs. Example:  ItemID = (id0 << 32) + (id1 << 16) + (id2).
> The range covered by each ID guarantees that their sum will never exceed the 
> 64 bit maximum size of an sqlite primary key. The advantage of this approach 
> is that a function performing a SELECT statement can pre-compute the id that 
> needs to be retrieved from the database. This is what I call a sparse table, 
> meaning that the table will never have more than X items, but the primary key 
> range is actually much bigger than X. Sorry if my definitions are not 
> standard, SQL is not my native language :)

You have invented a 'hashing function' which is the normal term used for that 
kind of calculation.  If this renders unique ItemIDs for you, and you can 
easily calculate the hash for any item, then it's probably not slowing you down 
much.

> This scheme is used because IDs are usually inserted all at once in a single 
> batch, and then they have to be regularly updated over a very long time. So, 
> there are relatively few INSERTS and a LOT of UPDATES and SELECTS.
> 
> I'm wondering if the advantage in search speed obtained by this ID assignment 
> scheme may somehow be offset by other factors like additional memory usage, 
> less efficient inserts, etc. Can anyone offer counterarguments, or recommend 
> a better scheme?

There are so many considerations involved in this, including your file system, 
the amount of memory you have spare, your OS, and the total width of your 
table, that we couldn't guess the result.  Your only way to find out is to do 
the actual test.  However, I suspect that any speed gain or loss involved in a 
scheme like this would be trivial when measured against the overall performance 
of your software.  SQLite is itself extremely fast even if you just store your 
data without any special processing.  You can probably get better return on 
your time by spending it thinking about a better user-interface, or some other 
element of your application.

If, on the other hand, you have tested and found that your application is just 
a tiny bit too slow to be usable, and that profiling is showing that most of 
the time is spend inside SQLite functions, that's a different matter.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance of sparse sqlite tables

[Pavel Ivanov]

I'd say generally speaking your way of storing data has no significant
downsides. There's just one "but": if each row in your table stores pretty
significant amount of data (blobs, long text fields or just lots of
different fields) you'd better not make your ItemID INTEGER PRIMARY KEY.
Because SQLite stores all rows in the table in the order of rowids. So
every time you update your ItemID SQLite would have to move the whole row
to a new place. So for the case of big rows I'd suggest to make some other
column INTEGER PRIMARY KEY and add unique constraint to your ItemID. It
won't hurt your search speed (could make it faster actually) and will make
updates faster. Although it will come with a larger size of the database
file.

Pavel


On Sun, Nov 4, 2012 at 9:26 AM, Dominguez Bonini, David <
david.doming...@ikusi.com> wrote:

> Hi,
>
> I have an application where a table is filled with elements whose primary
> key is specified at insertion, and is actually a combination of several
> independent IDs. Example:  ItemID = (id0 << 32) + (id1 << 16) + (id2).
> The range covered by each ID guarantees that their sum will never exceed
> the 64 bit maximum size of an sqlite primary key. The advantage of this
> approach is that a function performing a SELECT statement can pre-compute
> the id that needs to be retrieved from the database. This is what I call a
> sparse table, meaning that the table will never have more than X items, but
> the primary key range is actually much bigger than X. Sorry if my
> definitions are not standard, SQL is not my native language :)
>
> This scheme is used because IDs are usually inserted all at once in a
> single batch, and then they have to be regularly updated over a very long
> time. So, there are relatively few INSERTS and a LOT of UPDATES and SELECTS.
>
> I'm wondering if the advantage in search speed obtained by this ID
> assignment scheme may somehow be offset by other factors like additional
> memory usage, less efficient inserts, etc. Can anyone offer
> counterarguments, or recommend a better scheme?
>
> Regards
>
> David
>
> ___
> 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] Performance of sparse sqlite tables

[Dominguez Bonini, David]

Hi,

I have an application where a table is filled with elements whose primary key 
is specified at insertion, and is actually a combination of several independent 
IDs. Example:  ItemID = (id0 << 32) + (id1 << 16) + (id2).
The range covered by each ID guarantees that their sum will never exceed the 64 
bit maximum size of an sqlite primary key. The advantage of this approach is 
that a function performing a SELECT statement can pre-compute the id that needs 
to be retrieved from the database. This is what I call a sparse table, meaning 
that the table will never have more than X items, but the primary key range is 
actually much bigger than X. Sorry if my definitions are not standard, SQL is 
not my native language :)

This scheme is used because IDs are usually inserted all at once in a single 
batch, and then they have to be regularly updated over a very long time. So, 
there are relatively few INSERTS and a LOT of UPDATES and SELECTS.

I'm wondering if the advantage in search speed obtained by this ID assignment 
scheme may somehow be offset by other factors like additional memory usage, 
less efficient inserts, etc. Can anyone offer counterarguments, or recommend a 
better scheme?

Regards

David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Compiling SQLite3 with MSVC 2010

[Black, Michael (IS)]

Hmmm...interesting...I'm using VS 2010 Express 32-bit and I would've assumed 
the warnings would match.  I also enabled SQLITE_64BIT_STATS to try and force 
the first warning but that didn't cause it.
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 16.00.40219.01 for 80x86

That's a bit disconcerting actually but I guess Express is less pedantic than 
Studio.

You can always stick this in to shut it up.  There's a yin yang to fixing 
thesesimple enough to throw a cast in there...but down the road if you make 
other changes on the right-hand-side datatype that could be of use so you would 
be suppressing a valid warning.  It's a mixed bag.  So suppressing warnings 
from picky compilers is the best way to go.

Do you still see then on lower levels?

#pragma warning(disable: 4244) // possible loss of data



Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jonas Malaco Filho [jonasmalacofi...@gmail.com]
Sent: Saturday, November 03, 2012 3:48 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Compiling SQLite3 with MSVC 2010

Actually, on MSVC 2010 I just got the following errors with /W3:

-- Rebuild All started: Project: Shell, Configuration: Release x64
--
  shell.c
  sqlite3.c
..\src\sqlite3.c(78502): warning C4244: 'initializing' : conversion from
'sqlite_int64' to 'tRowcnt', possible loss of data
..\src\sqlite3.c(78503): warning C4244: 'initializing' : conversion from
'sqlite_int64' to 'tRowcnt', possible loss of data
..\src\sqlite3.c(78504): warning C4244: 'initializing' : conversion from
'sqlite_int64' to 'tRowcnt', possible loss of data
..\src\sqlite3.c(104145): warning C4244: '=' : conversion from 'i64' to
'double', possible loss of data
..\src\sqlite3.c(104170): warning C4244: '=' : conversion from 'i64' to
'double', possible loss of data
  Generating code
  Finished generating code
  Shell.vcxproj ->
X:\jonas-malaco-filho\lib\SQLite\Shell\..\bin\x86-64\sqlite3.exe
-- Rebuild All started: Project: Shell, Configuration: Release Win32
--
  shell.c
  sqlite3.c
..\src\sqlite3.c(78502): warning C4244: 'initializing' : conversion from
'sqlite_int64' to 'tRowcnt', possible loss of data
..\src\sqlite3.c(78503): warning C4244: 'initializing' : conversion from
'sqlite_int64' to 'tRowcnt', possible loss of data
..\src\sqlite3.c(78504): warning C4244: 'initializing' : conversion from
'sqlite_int64' to 'tRowcnt', possible loss of data
..\src\sqlite3.c(104145): warning C4244: '=' : conversion from 'i64' to
'double', possible loss of data
..\src\sqlite3.c(104170): warning C4244: '=' : conversion from 'i64' to
'double', possible loss of data
  Generating code
  Finished generating code
  Shell.vcxproj ->
X:\jonas-malaco-filho\lib\SQLite\Shell\..\bin\x86\sqlite3.exe
== Rebuild All: 2 succeeded, 0 failed, 0 skipped ==


*Jonas Malaco Filho*



2012/11/3 Black, Michael (IS) 

> You probably have the warning level turned up high.
>
> Up to level 3 it compiles without warnings.  Level 4 starts complaining
> loudly.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Igor Korot [ikoro...@gmail.com]
> Sent: Friday, November 02, 2012 6:25 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Compiling SQLite3 with MSVC 2010
>
> Richard,
>
> On Fri, Nov 2, 2012 at 4:14 PM, Richard Hipp  wrote:
> > On Fri, Nov 2, 2012 at 7:05 PM, Igor Korot  wrote:
> >
> >> Hi, ALL,
> >> Is anybody trying to compile SQLite with MSVC 2010?
> >>
> >
> > Tests 9e and 9f at http://www.sqlite.org/checklists/3071400#c9 were
> > performed using MSVC 2010.
> >
> >
> >>
> >> I am getting a lot of warnings.
> >> Is there any interest in fixing those?
> >>
> >
> > No.  See http://www.sqlite.org/testing.html#staticanalysis for an
> > explanation.
>
> I just read this link. Interesting information.
> IIUC, all those warnings are harmless and they do not appear on other
> platforms.
> Which means that either gcc is more forgiving or that I am trying to
> compile my
> application with some very strange configuration.
> Or maybe it's C++11 that throws the compilation off of track?
>
> I'm just trying to understand why those warnings appear and why nobody else
> see them on other platforms.
>
> Thank you.
>
> >
> >
> >>
> >> If not what is the policy of using SQLite3 code? I'm using 3.7.14
> release.
> >>
> >> Thank you.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> 

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Black, Michael (IS)]

If speed and storage are a concern then as somebody else notedjulianday is 
the way to go.
Just don't confuse CURRENT_TIMESTAMP with CURRENT_TIME -- you can still extract 
just date from the field if you need it.

sqlite> create table t(id,time);
sqlite> insert into t values(1,julianday(CURRENT_TIMESTAMP));
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(id,time);
INSERT INTO "t" VALUES(1,2456236.05462963); -- you can see storage mode is 
double
COMMIT;
sqlite> select id,date(time) from t;
1|2012-11-04
sqlite> select id,datetime(time) from t;
1|2012-11-04 13:18:40


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Sunday, November 04, 2012 1:34 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"

Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" 
:
>CREATE TABLE t(id,time);
>
INSERT INTO t VALUES(1,CURRENT_DATE);
>
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
>
INSERT INTO t VALUES(3,datetime('now'));
>
INSERT INTO t VALUES(4,date('now'));
>
SELECT * FROM t;
>
1|2012-11-02
>
2|2012-11-02 14:10:15
>
3|2012-11-02 14:10:15
>
4|2012-11-02
>
>
Perhaps the documentation needs to be better?  Apparently you couldn't find 
this info...
Indeed, I was never aware of CURRENT_*.

Anyway, all these functions return current moment as _string_ and this is not a 
great way to store datetime in db, isn't it?
- more memory occupied;
- slower compare;
- cannot add & substract;
etc.




>
>
Michael D. Black
>
Senior Scientist
>
Advanced Analytics Directorate
>
Advanced GEOINT Solutions Operating Unit
>
Northrop Grumman Information Systems
>
>

>
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
>
Sent: Friday, November 02, 2012 8:08 AM
>
To: General Discussion of SQLite Database
>
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"
>
>
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin :
>
>
>
>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко  wrote:
>
>
>
>
>
> it is a common practice to store datetime values as UNIX time UTC.
>
>
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
>
>
>
Please read
>
>
>
>
>

>
>
>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
functions that modify or format date values.
>
>
It's about having useful shortcut for getting current moment that doesn't have 
(string) parameters and so can be easily remembered and typed.
>
>
Compare:
>
MS SQL: CURRENT_TIMESTAMP
>
PostgreSQL: now()
>
Oracle: sysdate
>
>
To:
>
Sqlite: strftime('%s','now')
>
>
>
>Simon.
>
___
>
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

Re: [sqlite] SQLite leaks memory

[Clemens Ladisch]

Igor Korot wrote:
> When the user asks to edit the data it starts the transaction, then
> set the "SAVEPOINT"

Why do you need a savepoint in addition to the transaction?

> Now the problem: only during the scenario #3 I have a lot of memory
> leaks. They are reported in the Visual Studio debug
> window. Looking at the data I did a global search and it turns out
> that those leakage are coming from the sqlite.

How are those leaks shown?  With a call stack?  In that case, please
don't keep it a secret.

It would be helpful to show the code for scenario #3.
(It might be possible that your code doesn't correctly free some
SQLite object.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite.dll locks files forever starting fromv1.0.82.0

[Alex Reitbort]

>Alex Reitbort wrote:
>> 
>> When running this sample with System.Data.SQLite.dll v1.0.81 everything
>> works ok.
>> 
>> When running this sample with System.Data.SQLite.dll v1.0.82, it fails on
>> File.Delete line with IOException: The process cannot access the file
>> 'alex.db' because it is being used by another process.
>> 
> 
>As of 1.0.82.0, the command also needs to be disposed before the connection
>will be fully closed.
>
>--
>Joe Mistachkin

Thank you.

Alex Reitbort
Software Developer
Skyline Software Systems Inc.
www.skylineglobe.com


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Roger Andersson]

-Ursprungligt meddelande- 
From: Baruch Burstein

Sent: Sunday, November 04, 2012 10:43 AM
To: Григорий Григоренко ; General Discussion of SQLite Database
Subject: Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

CURRENT_TIMESTAMP returns "-MM-DD HH:MM:SS", not a unix timestamp. I
think other DB systems all use this function to return a unix timestamp.

- Reply -
There might be some that does, but
Oracle11g
SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---
2012-11-04 11:05:23,537000 +01:00

Oracle9i
SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---
2012-11-04 11:08:48,942211 EUROPE/STOCKHOLM

MySQL 5
mysql> select CURRENT_TIMESTAMP;
+-+
| CURRENT_TIMESTAMP   |
+-+
| 2012-11-04 11:06:31 |
+-+

Regards
Roger 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Baruch Burstein]

CURRENT_TIMESTAMP returns "-MM-DD HH:MM:SS", not a unix timestamp. I
think other DB systems all use this function to return a unix timestamp.

On Sun, Nov 4, 2012 at 9:34 AM, Григорий Григоренко wrote:

>
>
>
> Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" <
> michael.bla...@ngc.com>:
> >CREATE TABLE t(id,time);
> >
> INSERT INTO t VALUES(1,CURRENT_DATE);
> >
> INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
> >
> INSERT INTO t VALUES(3,datetime('now'));
> >
> INSERT INTO t VALUES(4,date('now'));
> >
> SELECT * FROM t;
> >
> 1|2012-11-02
> >
> 2|2012-11-02 14:10:15
> >
> 3|2012-11-02 14:10:15
> >
> 4|2012-11-02
> >
> >
> Perhaps the documentation needs to be better?  Apparently you couldn't
> find this info...
> Indeed, I was never aware of CURRENT_*.
>
> Anyway, all these functions return current moment as _string_ and this is
> not a great way to store datetime in db, isn't it?
> - more memory occupied;
> - slower compare;
> - cannot add & substract;
> etc.
>
>
>
>
> >
> >
> Michael D. Black
> >
> Senior Scientist
> >
> Advanced Analytics Directorate
> >
> Advanced GEOINT Solutions Operating Unit
> >
> Northrop Grumman Information Systems
> >
> >
> 
> >
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Григорий Григоренко [grigore...@mail.ru]
> >
> Sent: Friday, November 02, 2012 8:08 AM
> >
> To: General Discussion of SQLite Database
> >
> Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for
> "strftime('%s','now')"
> >
> >
> Thu, 1 Nov 2012 19:57:42 + от Simon Slavin :
> >
> >
> >
> >
> On 1 Nov 2012, at 7:55pm, Григорий Григоренко  wrote:
> >
> >
> >
> >
> >
> > it is a common practice to store datetime values as UNIX time UTC.
> >
> >
> >
> > Maybe, Sqlite should have some shortcut for evaluating current moment?
> >
> >
> >
> >
> >
> Please read
> >
> >
> >
> >
> >
> 
> >
> >
> >
> Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking
> functions that modify or format date values.
> >
> >
> It's about having useful shortcut for getting current moment that doesn't
> have (string) parameters and so can be easily remembered and typed.
> >
> >
> Compare:
> >
> MS SQL: CURRENT_TIMESTAMP
> >
> PostgreSQL: now()
> >
> Oracle: sysdate
> >
> >
> To:
> >
> Sqlite: strftime('%s','now')
> >
> >
> >
> >Simon.
> >
> ___
> >
> 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
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" 
:
>CREATE TABLE t(id,time);
>
INSERT INTO t VALUES(1,CURRENT_DATE);
>
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
>
INSERT INTO t VALUES(3,datetime('now'));
>
INSERT INTO t VALUES(4,date('now'));
>
SELECT * FROM t;
>
1|2012-11-02
>
2|2012-11-02 14:10:15
>
3|2012-11-02 14:10:15
>
4|2012-11-02
>
>
Perhaps the documentation needs to be better?  Apparently you couldn't find 
this info...
Indeed, I was never aware of CURRENT_*.

Anyway, all these functions return current moment as _string_ and this is not a 
great way to store datetime in db, isn't it?
- more memory occupied;
- slower compare;
- cannot add & substract;
etc.




>
>
Michael D. Black
>
Senior Scientist
>
Advanced Analytics Directorate
>
Advanced GEOINT Solutions Operating Unit
>
Northrop Grumman Information Systems
>
>

>
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
>
Sent: Friday, November 02, 2012 8:08 AM
>
To: General Discussion of SQLite Database
>
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"
>
>
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin :
>
>
>
>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко  wrote:
>
>
>
>
>
> it is a common practice to store datetime values as UNIX time UTC.
>
>
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
>
>
>
Please read
>
>
>
>
>

>
>
>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
functions that modify or format date values.
>
>
It's about having useful shortcut for getting current moment that doesn't have 
(string) parameters and so can be easily remembered and typed.
>
>
Compare:
>
MS SQL: CURRENT_TIMESTAMP
>
PostgreSQL: now()
>
Oracle: sysdate
>
>
To:
>
Sqlite: strftime('%s','now')
>
>
>
>Simon.
>
___
>
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] SQLite leaks memory

[Igor Korot]

Hi, ALL,
I have a very interesting situation.
I'm trying to compile my project using Visual Studio 2010. It is a C++
executable and I just inserted the source code from the
amalgamation archive into the project. I also changed the settings for
the sqlite3.c to be recognized as a "C" source code and
set a warning level to be /W3.

Now I have a class that is communicates with the database file. In
this class I have 3 routine: one for adding records, one for deleting
records and one for updating records.Since there are multiple records
all 3 functions utilize transactions.

The first function is very simple: I grab the data and drop them in
the db. No problem at all.
The second one is also simple: check if the user wants to delete and
if yes deletes records.
The third one is actually a little complicated.

It utilizes the capabilities of the first 2 functions.
When the user asks to edit the data it starts the transaction, then
set the "SAVEPOINT" deletes the appropriate records
and then insert the changed data.

Now the problem: only during the scenario #3 I have a lot of memory
leaks. They are reported in the Visual Studio debug
window. Looking at the data I did a global search and it turns out
that those leakage are coming from the sqlite.

Is there a way to isolate those leakage? I don't think I found a bug
in the SQLite engine but I don't see a good data
from my program in the leakage report of Visual Studio neither.

Any suggestions? Since the program is GUI-based I can't use
_CtrDbg-like functions.

I tried to use VLD but it reported leaks in legitimate sqlite functions.

Any help would be appreciated.

Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Fri, 2 Nov 2012 10:25:18 -0400 от Richard Hipp :

>
>
>On Fri, Nov 2, 2012 at 10:18 AM, Simon Davies  
>wrote:
>

datetime() will give current date and time
>>
likewise date(), time() etc
>>
>If I read the original post correctly, I think the OP is requesting a new 
>function, "now()", that returns the number of seconds since 1970.  This would 
>be the same as "CAST(strftime('%s','now') AS INTEGER)", just easier to 
>remember.
Correct!


>
>By coincidence, such a function has already been added to the SQLite 
>implementation inside of Fossil.  See:
>
>http://www.fossil-scm.org/fossil/artifact/b743628236c?ln=669-679
>http://www.fossil-scm.org/fossil/artifact/b743628236c?ln=718
>
>Perhaps the OP can simply copy the code above into his own application?
>
I'm using Delphi with sqlite3.dll, maybe I should introduce my own function.






>
>-- 
>D. Richard Hipp
>d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Fri, 02 Nov 2012 15:32:44 +0100 от Clemens Ladisch :
>Igor Tandetnik wrote:
>
> SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause.
>
>
>
> http://sqlite.org/lang_createtable.html#tablecoldef
>
>
SQLite does in fact accept CURRENT_TIMESTAMP anywhere:
>http://www.sqlite.org/lang_expr.html
>
>
Yes, indeed. Perhaps, this should be 
on http://www.sqlite.org/lang_datefunc.html page.





>
Regards,
>
Clemens
>
___
>
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