[sqlite] [OS/2] Update OS/2 support in sqlite 3.7.x
> > If the underlying VFS does not support shared-memory (which the OS/2 VFS > does not) then SQLite simply will not go into WAL mode. No patching is > needed for this. Everything should work as delivered. > > What exactly is malfunctioning? What is the problem that this patch > attempts to fix? The mozilla bug was originally filed against 3.7.1. In pager.c was a function: static int pagerPagecount in an #ifndef SQLITE_OMIT_WAL block. The last 3 hunks of the original patch moved the exclusion of omit_wal further down, because all sqlite databases in a new profile had zero size on OS/2 and a warning was issued from firefox that history and bookmarks won't work, because the data bases were in use by another program. >> https://bugzilla.mozilla.org/attachment.cgi?id=474575 However, this issue has been fixed already in 3.7.2 [d1ed743b6e]. (Hopefully mozilla will update to 3.7.2 soon). The rest of the patch contained (in fossil format) in >>> https://bugzilla.mozilla.org/attachment.cgi?id=477692 is intended to explicitly disable WAL on OS/2 and bring the OS/2 related files up to date. That involves: - adding a new function, os2CurrentTimeInt64(), and refining the existing os2CurrentTime(); - adding new members to the "os2Vfs" structure, and explicitly defining them as NULL pointers since they support WAL; - updating the OS/2 semaphore documentation to match other platforms. It would be nice if these changes could be considered to get checked-in. Thanks, Walter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data consistency issues in WAL
On 29 Sep 2010, at 8:20pm, Erik Fears wrote: > -C++ with 3 threads. One of them a write thread, one a main reader thread, > and one a diagnostic reader thread Does each thread open the database independently, or are you passing the same handle from one to another ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] data consistency issues in WAL
On Wed, Sep 29, 2010 at 3:20 PM, Erik Fears wrote: > sqlite-users, > > Here's my test environment: > > -RHEL 5 on a vmware server (trying to move this to ESX) > -C++ with 3 threads. One of them a write thread, one a main reader thread, > and one a diagnostic reader thread > -journal-mode=WAL > -no change to default synchronization mode > > The write thread is updating a fairly small (< 1k) blob on one table. I can > see the change on my diagnostic reader thread, but my main reader thread > appears to be acting on an old copy. It has no application side caching, so > would be doing a SELECT everytime it needs this data. It appears to have an > old copy of this data until I restart the daemon > My guess is that you are holding a transaction open on the reader thread. As long as you have a transaction open, the reader will continue to see the state of the database as it existed when the transaction was first started - no updates will be visible to that reader, though the updates will be visible to other readers who start their transactions after the update occurs. This is a (very deliberate and desirable) feature, not a bug. > > Are there known consistency/timing issues with WAL? I'm wondering if vmware > server's outdated disk support is causing any issues. > > More importantly, this is pretty hard to diagnose and catch. Are there any > sqlite side logging or diagnostics I can enable that will help me prove > where the bug lies (my code, sqlite, vm)? I have sqlite side logging > enabled. I've played around with sqlite3_trace, but that only appears to > log > queries and not results. > > Thanks! > --erik > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- 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] data consistency issues in WAL
sqlite-users, Here's my test environment: -RHEL 5 on a vmware server (trying to move this to ESX) -C++ with 3 threads. One of them a write thread, one a main reader thread, and one a diagnostic reader thread -journal-mode=WAL -no change to default synchronization mode The write thread is updating a fairly small (< 1k) blob on one table. I can see the change on my diagnostic reader thread, but my main reader thread appears to be acting on an old copy. It has no application side caching, so would be doing a SELECT everytime it needs this data. It appears to have an old copy of this data until I restart the daemon Are there known consistency/timing issues with WAL? I'm wondering if vmware server's outdated disk support is causing any issues. More importantly, this is pretty hard to diagnose and catch. Are there any sqlite side logging or diagnostics I can enable that will help me prove where the bug lies (my code, sqlite, vm)? I have sqlite side logging enabled. I've played around with sqlite3_trace, but that only appears to log queries and not results. Thanks! --erik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting unique years from a timestamp column
yeah, total brain failure on my part. Should have been strftime('%Y',date).
Sorry.
--- On Wed, 9/29/10, J. Bobby Lopez wrote:
> From: J. Bobby Lopez
> Subject: Re: [sqlite] Getting unique years from a timestamp column
> To: "General Discussion of SQLite Database"
> Date: Wednesday, September 29, 2010, 12:42 PM
> This one doesn't seem to return the
> desired result, instead it returned a
> blank line?
>
> On Wed, Sep 29, 2010 at 12:40 PM, David Bicking
> wrote:
>
> > How about trying
> >
> > select distinct datetime(date,'%Y') as year from
> data;
> >
> > David
> >
> > --- On Wed, 9/29/10, J. Bobby Lopez
> wrote:
> >
> > > From: J. Bobby Lopez
> > > Subject: [sqlite] Getting unique years from a
> timestamp column
> > > To: sqlite-users@sqlite.org
> > > Date: Wednesday, September 29, 2010, 12:34 PM
> > > Hello,
> > >
> > > I'm looking to do something completely in SQLite,
> without
> > > the assistance of
> > > Perl.
> > >
> > > The problem is this:
> > >
> > > I have a table with a text column, and the column
> contains
> > > unix timestamps.
> > > I would like to get a list of the unique years
> from that
> > > timestamp column.
> > >
> > > Here's what the raw dataset looks like:
> > >
> > > 1|blopez|somekinda.log|2010-07-10
> > > 13:21:10|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > > 2|blopez|somekinda.log|2010-09-28
> > > 06:18:51|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > > 3|blopez|somekinda.log|2010-06-28
> > > 17:58:37|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > > 4|blopez|somekinda.log|2011-06-28
> > > 17:58:37|10.100.0.1|make_db.pl
> > > |usage_reporter()
> > >
> > > What I'd like to do is write a query that would
> return
> > > "2010" and "2011",
> > > the two unique years in the listing.
> > >
> > > It's pretty easy to get all records which match a
> single
> > > year, for example:
> > >
> > > SELECT id FROM data WHERE datetime(date) LIKE
> '2010%';
> > >
> > > I'm sure I could use a BEGIN/COMMIT block and
> test for each
> > > year
> > > individually, but I don't want to hard-code the
> year that
> > > I'm looking for,
> > > if you get my meaning.
> > >
> > > Any assistance on this would be
> appreciated. Thanks!
> > >
> > > Bobby
> > > ___
> > > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vertical -> Horizontal transformation
On Wed, Sep 29, 2010 at 6:16 PM, Petite Abeille wrote: > > On Sep 29, 2010, at 7:05 PM, Simon Slavin wrote: > >> SQL (not just SQLite) is traditionally terribly bad at doing matrix >> inversions. What do you want to use the wide short table for ? Does it >> really need to exist as a table ? > > Just for fun (and profit): > > http://www.orafaq.com/wiki/PIVOT > http://www.orafaq.com/wiki/UNPIVOT Ah, this is a pivot!!! Doh. And this [1] recipe shows how you can do it in SQLite. Thanks [1] http://softwaresalariman.blogspot.com/2008/05/pivot-table-hack-in-sqlite3-and-mysql.html I wonder if this is also more efficient To Simon, yes, we do *really* need the short wide structure but it doesn't necessarily have to be a table (i.e. could be a view). We do need it to be a database object though because a subsequent step of the process queries it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vertical -> Horizontal transformation
On Sep 29, 2010, at 7:05 PM, Simon Slavin wrote: > SQL (not just SQLite) is traditionally terribly bad at doing matrix > inversions. What do you want to use the wide short table for ? Does it > really need to exist as a table ? Just for fun (and profit): http://www.orafaq.com/wiki/PIVOT http://www.orafaq.com/wiki/UNPIVOT ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vertical -> Horizontal transformation
On 29 Sep 2010, at 5:34pm, Andy Chambers wrote: > I've got a nice normalized table and need to produce a de-normalized > view of this table (i.e. convert it > from tall skinny, into wide short table). SQL (not just SQLite) is traditionally terribly bad at doing matrix inversions. What do you want to use the wide short table for ? Does it really need to exist as a table ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting unique years from a timestamp column
It seems to me these are not separate functions at all, just aliases to
strftime(). I guess the work well as shortcuts in certain situations.
Thanks for the assistance!
Bobby
On Wed, Sep 29, 2010 at 12:48 PM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:
> datetime is just this:
>
> strftime('%Y-%m-%d %H:%M:%S', ...)
> From http://www.sqlite.org/lang_datefunc.html <
> http://www.sqlite.org/lang_datefunc.html>
>
> And that doesn't do what you want...you need strftime to get just the year.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Northrop Grumman Information Systems
>
>
>
>
> From: sqlite-users-boun...@sqlite.org on behalf of J. Bobby Lopez
> Sent: Wed 9/29/2010 11:46 AM
> To: General Discussion of SQLite Database
> Subject: EXTERNAL:Re: [sqlite] Getting unique years from a timestamp column
>
>
>
> I guess a follow-on question here would be, which function should I be
> using
> more often, the datetime() function, or the strftime()? I didn't think
> that
> the datetime() function accepted the format argument (%Y) like strftime()
> did.
>
> On Wed, Sep 29, 2010 at 12:42 PM, J. Bobby Lopez wrote:
>
>
>
>
> ___
> 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] Vertical -> Horizontal transformation
On Wed, Sep 29, 2010 at 11:34 AM, Andy Chambers wrote: > Hi, > > I've got a nice normalized table and need to produce a de-normalized > view of this table (i.e. convert it > from tall skinny, into wide short table). In order to do this, I was > planning on just joining the > table to itself for each "value" that needs to be turned into a > column. However, it turns out there's > 96 values so I run into the "only 64 tables in a join" constraint. Is > there another idiom for > doing this sort of transformation or should I just use two helper > tables with 33 values in each and > then join them. > > For those who have used other databases, is it quite common to have > this constraint? This seems > like it would be quite a common problem in ETL projects (admittedly > it's perhaps unusual to be > using sqlite for an ETL project). > > Hope I've made myself clear. You would make yourself even clearer if you gave the schema for both the source and the desired target tables. > > Cheers, > Andy > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting unique years from a timestamp column
On Wed, Sep 29, 2010 at 9:34 AM, J. Bobby Lopez wrote:
> Hello,
>
> I'm looking to do something completely in SQLite, without the assistance of
> Perl.
>
> The problem is this:
>
> I have a table with a text column, and the column contains unix timestamps.
> I would like to get a list of the unique years from that timestamp column.
>
> Here's what the raw dataset looks like:
>
> 1|blopez|somekinda.log|2010-07-10 13:21:10|10.100.0.1|make_db.pl
> |usage_reporter()
> 2|blopez|somekinda.log|2010-09-28 06:18:51|10.100.0.1|make_db.pl
> |usage_reporter()
> 3|blopez|somekinda.log|2010-06-28 17:58:37|10.100.0.1|make_db.pl
> |usage_reporter()
> 4|blopez|somekinda.log|2011-06-28 17:58:37|10.100.0.1|make_db.pl
> |usage_reporter()
>
> What I'd like to do is write a query that would return "2010" and "2011",
> the two unique years in the listing.
>
> It's pretty easy to get all records which match a single year, for example:
>
> SELECT id FROM data WHERE datetime(date) LIKE '2010%';
>
This might work for you
select distinct strftime('%Y', date) from data;
(Assuming your table is named "data", and the column with the time
stamp is called "date")
Stephan
> I'm sure I could use a BEGIN/COMMIT block and test for each year
> individually, but I don't want to hard-code the year that I'm looking for,
> if you get my meaning.
>
> Any assistance on this would be appreciated. Thanks!
>
> Bobby
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Stephan Wehner
-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting unique years from a timestamp column
datetime is just this:
strftime('%Y-%m-%d %H:%M:%S', ...)
>From http://www.sqlite.org/lang_datefunc.html
>
And that doesn't do what you want...you need strftime to get just the year.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org on behalf of J. Bobby Lopez
Sent: Wed 9/29/2010 11:46 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Getting unique years from a timestamp column
I guess a follow-on question here would be, which function should I be using
more often, the datetime() function, or the strftime()? I didn't think that
the datetime() function accepted the format argument (%Y) like strftime()
did.
On Wed, Sep 29, 2010 at 12:42 PM, J. Bobby Lopez wrote:
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting unique years from a timestamp column
That's cuz' the parameters were backwardsshould be select distinct datetime(,'%Y',date) as year from data; The way it was given "date" was being used as the format and %Y as the time valueergo blank... Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of J. Bobby Lopez Sent: Wed 9/29/2010 11:42 AM To: General Discussion of SQLite Database Subject: EXTERNAL:Re: [sqlite] Getting unique years from a timestamp column This one doesn't seem to return the desired result, instead it returned a blank line? On Wed, Sep 29, 2010 at 12:40 PM, David Bicking wrote: > How about trying > > select distinct datetime(date,'%Y') as year from data; > > David > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting unique years from a timestamp column
I guess a follow-on question here would be, which function should I be using more often, the datetime() function, or the strftime()? I didn't think that the datetime() function accepted the format argument (%Y) like strftime() did. On Wed, Sep 29, 2010 at 12:42 PM, J. Bobby Lopez wrote: > This one doesn't seem to return the desired result, instead it returned a > blank line? > > > On Wed, Sep 29, 2010 at 12:40 PM, David Bicking wrote: > >> How about trying >> >> select distinct datetime(date,'%Y') as year from data; >> >> David >> >> --- On Wed, 9/29/10, J. Bobby Lopez wrote: >> >> > From: J. Bobby Lopez >> > Subject: [sqlite] Getting unique years from a timestamp column >> > To: sqlite-users@sqlite.org >> > Date: Wednesday, September 29, 2010, 12:34 PM >> > Hello, >> > >> > I'm looking to do something completely in SQLite, without >> > the assistance of >> > Perl. >> > >> > The problem is this: >> > >> > I have a table with a text column, and the column contains >> > unix timestamps. >> > I would like to get a list of the unique years from that >> > timestamp column. >> > >> > Here's what the raw dataset looks like: >> > >> > 1|blopez|somekinda.log|2010-07-10 >> > 13:21:10|10.100.0.1|make_db.pl >> > |usage_reporter() >> > 2|blopez|somekinda.log|2010-09-28 >> > 06:18:51|10.100.0.1|make_db.pl >> > |usage_reporter() >> > 3|blopez|somekinda.log|2010-06-28 >> > 17:58:37|10.100.0.1|make_db.pl >> > |usage_reporter() >> > 4|blopez|somekinda.log|2011-06-28 >> > 17:58:37|10.100.0.1|make_db.pl >> > |usage_reporter() >> > >> > What I'd like to do is write a query that would return >> > "2010" and "2011", >> > the two unique years in the listing. >> > >> > It's pretty easy to get all records which match a single >> > year, for example: >> > >> > SELECT id FROM data WHERE datetime(date) LIKE '2010%'; >> > >> > I'm sure I could use a BEGIN/COMMIT block and test for each >> > year >> > individually, but I don't want to hard-code the year that >> > I'm looking for, >> > if you get my meaning. >> > >> > Any assistance on this would be appreciated. Thanks! >> > >> > Bobby >> > ___ >> > 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] Getting unique years from a timestamp column
This one doesn't seem to return the desired result, instead it returned a blank line? On Wed, Sep 29, 2010 at 12:40 PM, David Bicking wrote: > How about trying > > select distinct datetime(date,'%Y') as year from data; > > David > > --- On Wed, 9/29/10, J. Bobby Lopez wrote: > > > From: J. Bobby Lopez > > Subject: [sqlite] Getting unique years from a timestamp column > > To: sqlite-users@sqlite.org > > Date: Wednesday, September 29, 2010, 12:34 PM > > Hello, > > > > I'm looking to do something completely in SQLite, without > > the assistance of > > Perl. > > > > The problem is this: > > > > I have a table with a text column, and the column contains > > unix timestamps. > > I would like to get a list of the unique years from that > > timestamp column. > > > > Here's what the raw dataset looks like: > > > > 1|blopez|somekinda.log|2010-07-10 > > 13:21:10|10.100.0.1|make_db.pl > > |usage_reporter() > > 2|blopez|somekinda.log|2010-09-28 > > 06:18:51|10.100.0.1|make_db.pl > > |usage_reporter() > > 3|blopez|somekinda.log|2010-06-28 > > 17:58:37|10.100.0.1|make_db.pl > > |usage_reporter() > > 4|blopez|somekinda.log|2011-06-28 > > 17:58:37|10.100.0.1|make_db.pl > > |usage_reporter() > > > > What I'd like to do is write a query that would return > > "2010" and "2011", > > the two unique years in the listing. > > > > It's pretty easy to get all records which match a single > > year, for example: > > > > SELECT id FROM data WHERE datetime(date) LIKE '2010%'; > > > > I'm sure I could use a BEGIN/COMMIT block and test for each > > year > > individually, but I don't want to hard-code the year that > > I'm looking for, > > if you get my meaning. > > > > Any assistance on this would be appreciated. Thanks! > > > > Bobby > > ___ > > 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] Getting unique years from a timestamp column
Thank you so very much!
On Wed, Sep 29, 2010 at 12:39 PM, Richard Hipp wrote:
> On Wed, Sep 29, 2010 at 12:34 PM, J. Bobby Lopez wrote:
>
> > Hello,
> >
> > I'm looking to do something completely in SQLite, without the assistance
> of
> > Perl.
> >
> > The problem is this:
> >
> > I have a table with a text column, and the column contains unix
> timestamps.
> > I would like to get a list of the unique years from that timestamp
> column.
> >
> > Here's what the raw dataset looks like:
> >
> > 1|blopez|somekinda.log|2010-07-10 13:21:10|10.100.0.1|make_db.pl
> > |usage_reporter()
> > 2|blopez|somekinda.log|2010-09-28 06:18:51|10.100.0.1|make_db.pl
> > |usage_reporter()
> > 3|blopez|somekinda.log|2010-06-28 17:58:37|10.100.0.1|make_db.pl
> > |usage_reporter()
> > 4|blopez|somekinda.log|2011-06-28 17:58:37|10.100.0.1|make_db.pl
> > |usage_reporter()
> >
> > What I'd like to do is write a query that would return "2010" and "2011",
> > the two unique years in the listing.
> >
> > It's pretty easy to get all records which match a single year, for
> example:
> >
> > SELECT id FROM data WHERE datetime(date) LIKE '2010%';
> >
> > I'm sure I could use a BEGIN/COMMIT block and test for each year
> > individually, but I don't want to hard-code the year that I'm looking
> for,
> > if you get my meaning.
> >
> > Any assistance on this would be appreciated. Thanks!
> >
>
> SELECT DISTINCT strftime('%Y',date) FROM data;
>
>
> >
> > Bobby
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting unique years from a timestamp column
How about trying select distinct datetime(date,'%Y') as year from data; David --- On Wed, 9/29/10, J. Bobby Lopez wrote: > From: J. Bobby Lopez > Subject: [sqlite] Getting unique years from a timestamp column > To: sqlite-users@sqlite.org > Date: Wednesday, September 29, 2010, 12:34 PM > Hello, > > I'm looking to do something completely in SQLite, without > the assistance of > Perl. > > The problem is this: > > I have a table with a text column, and the column contains > unix timestamps. > I would like to get a list of the unique years from that > timestamp column. > > Here's what the raw dataset looks like: > > 1|blopez|somekinda.log|2010-07-10 > 13:21:10|10.100.0.1|make_db.pl > |usage_reporter() > 2|blopez|somekinda.log|2010-09-28 > 06:18:51|10.100.0.1|make_db.pl > |usage_reporter() > 3|blopez|somekinda.log|2010-06-28 > 17:58:37|10.100.0.1|make_db.pl > |usage_reporter() > 4|blopez|somekinda.log|2011-06-28 > 17:58:37|10.100.0.1|make_db.pl > |usage_reporter() > > What I'd like to do is write a query that would return > "2010" and "2011", > the two unique years in the listing. > > It's pretty easy to get all records which match a single > year, for example: > > SELECT id FROM data WHERE datetime(date) LIKE '2010%'; > > I'm sure I could use a BEGIN/COMMIT block and test for each > year > individually, but I don't want to hard-code the year that > I'm looking for, > if you get my meaning. > > Any assistance on this would be appreciated. Thanks! > > Bobby > ___ > 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] Getting unique years from a timestamp column
On Wed, Sep 29, 2010 at 12:34 PM, J. Bobby Lopez wrote:
> Hello,
>
> I'm looking to do something completely in SQLite, without the assistance of
> Perl.
>
> The problem is this:
>
> I have a table with a text column, and the column contains unix timestamps.
> I would like to get a list of the unique years from that timestamp column.
>
> Here's what the raw dataset looks like:
>
> 1|blopez|somekinda.log|2010-07-10 13:21:10|10.100.0.1|make_db.pl
> |usage_reporter()
> 2|blopez|somekinda.log|2010-09-28 06:18:51|10.100.0.1|make_db.pl
> |usage_reporter()
> 3|blopez|somekinda.log|2010-06-28 17:58:37|10.100.0.1|make_db.pl
> |usage_reporter()
> 4|blopez|somekinda.log|2011-06-28 17:58:37|10.100.0.1|make_db.pl
> |usage_reporter()
>
> What I'd like to do is write a query that would return "2010" and "2011",
> the two unique years in the listing.
>
> It's pretty easy to get all records which match a single year, for example:
>
> SELECT id FROM data WHERE datetime(date) LIKE '2010%';
>
> I'm sure I could use a BEGIN/COMMIT block and test for each year
> individually, but I don't want to hard-code the year that I'm looking for,
> if you get my meaning.
>
> Any assistance on this would be appreciated. Thanks!
>
SELECT DISTINCT strftime('%Y',date) FROM data;
>
> Bobby
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
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] Vertical -> Horizontal transformation
Hi, I've got a nice normalized table and need to produce a de-normalized view of this table (i.e. convert it from tall skinny, into wide short table). In order to do this, I was planning on just joining the table to itself for each "value" that needs to be turned into a column. However, it turns out there's 96 values so I run into the "only 64 tables in a join" constraint. Is there another idiom for doing this sort of transformation or should I just use two helper tables with 33 values in each and then join them. For those who have used other databases, is it quite common to have this constraint? This seems like it would be quite a common problem in ETL projects (admittedly it's perhaps unusual to be using sqlite for an ETL project). Hope I've made myself clear. Cheers, Andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Getting unique years from a timestamp column
Hello, I'm looking to do something completely in SQLite, without the assistance of Perl. The problem is this: I have a table with a text column, and the column contains unix timestamps. I would like to get a list of the unique years from that timestamp column. Here's what the raw dataset looks like: 1|blopez|somekinda.log|2010-07-10 13:21:10|10.100.0.1|make_db.pl |usage_reporter() 2|blopez|somekinda.log|2010-09-28 06:18:51|10.100.0.1|make_db.pl |usage_reporter() 3|blopez|somekinda.log|2010-06-28 17:58:37|10.100.0.1|make_db.pl |usage_reporter() 4|blopez|somekinda.log|2011-06-28 17:58:37|10.100.0.1|make_db.pl |usage_reporter() What I'd like to do is write a query that would return "2010" and "2011", the two unique years in the listing. It's pretty easy to get all records which match a single year, for example: SELECT id FROM data WHERE datetime(date) LIKE '2010%'; I'm sure I could use a BEGIN/COMMIT block and test for each year individually, but I don't want to hard-code the year that I'm looking for, if you get my meaning. Any assistance on this would be appreciated. Thanks! Bobby ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug in the ./configure script inside sqlite-3_7_2-tea.tar.gz
On 29 Sep 2010, at 14:00, sqlite-users-requ...@sqlite.org wrote: > On Tue, Sep 28, 2010 at 3:12 PM, Mike Holenderski wrote: > >> Dear SQLite developers, >> >> When trying to build SQLite from sqlite-3_7_2-tea.tar.gz I ran into the >> following problem: >> >> ./configure --with-tcl /home/mike/lib >> >> configure: WARNING: you should use --build, --host, --target >> configure: WARNING: invalid host type: /home/mike/tclkit/FreeBSD/lib/ >> checking for correct TEA configuration... ok (TEA 3.9) >> checking for Tcl configuration... configure: error: yes directory doesn't >> contain tclConfig.sh >> >> It appears that the path to tcl passed as --with-tcl argument to the >> ./configure script is not handled correctly (in the ./configure script the >> path is replaced by 'yes'). By hardcoding the path to tcl inside the >> ./configure script it worked, but this, of course, is not a solution. >> >> > The TEA build system has been completely redone since 3.7.2. Please try the > lastest snapshot found at http://www.sqlite.org/draft/download.html and let > us know if it works any better for you. > > >> Best regards, >> Mike >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org I have tried the snapshot sqlite-tea-snapshot-201009280724.zip (with sqlite3.7.3 inside) and unfortunately the problem still remains. I have tried building it on OSX 10.6.4 and on FreeBSD 8.1, with the same result. Note that the same problem also occurs for other ./configure parameters, e.g. --with-tclinclude. It seems that the parameters passed to the ./configure script are not handled correctly (I don't know sh so can't pinpoint the exact problem). Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE with 64bit unsigned integer key
Quoth Marco , on 2010-09-28 09:43:17 -0700: > Hello there sqlite-users, > I have a quick question regarding selection and deletion of large unsigned > integers. No, that---what? SQLite doesn't _have_ "unsigned" integers. Read http://sqlite.org/datatype3.html again and come back once you understand that > I create my table as following: > [1] CREATE TABLE t1 (instnum BIG UNSIGNED INTEGER PRIMARY KEY, ...) > > I then insert some row: > [2] INSERT INTO t1 (instnum, ...) VALUES (CAST (?1 AS BIG UNSIGNED > INTEGER), ...) doesn't do what you think it does (but is accepted syntactically, most likely for a certain amount of compatibility with other database engines). > How can I solve this issue? This isn't something to "solve". If you're working with SQLite, you work with SQLite's types. (Or patch it, if you're really so inclined, but then you may not have interchange capability.) Or if you're binding the query parameters from a language that has unsigned 64-bit integers as a separate data type and doesn't allow implicitly turning them two's-complement, then you might consider it a bug in the binding that bit 63 set becomes a sign bit rather than raising an error, I suppose. I didn't see you mention that anywhere, though. > Thank you, > Marco ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE with 64bit unsigned integer key
On 28 September 2010 17:43, Marco wrote: > Hello there sqlite-users, > I have a quick question regarding selection and deletion of large unsigned > integers. > > I create my table as following: > [1] CREATE TABLE t1 (instnum BIG UNSIGNED INTEGER PRIMARY KEY, ...) > > I then insert some row: > [2] INSERT INTO t1 (instnum, ...) VALUES (CAST (?1 AS BIG UNSIGNED > INTEGER), ...) http://www.sqlite.org/datatype3.html "BIG UNSIGNED INTEGER" resolves to storage class INTEGER - always signed. > > This is just a test for 64bit unsigned, so I have the following rows: > 1 > 2 > 18446744073709551615 (0x) > 9223372036854775808 (0x8000) > 0 > 3 > 1234 > 23456 > 654321 > > When I try to DELETE using: > [3] DELETE FROM t1 WHERE instnum <= CAST (?1 AS BIG UNSIGNED INTEGER); > > I don't always get what I would expect, to me it looks like large numbers > (64bit with the MSB set to 1) are treated as negative. > > For example if ?1 in query [3] is set to 654321, all rows are being deleted > (while 18446744073709551615 and 9223372036854775808 should not be deleted) > > If ?1 is set to 18446744073709551615, the rows 18446744073709551615 and > 9223372036854775808 are deleted, but the other ones are not. > (while all of them should be gone) > > (I was previously using [2] and [3] without explicit cast, but the result is > the same). > > How can I solve this issue? subtract 9223372036854775808 when storing values into the db, add when retrieving. > > Thank you, > Marco Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DELETE with 64bit unsigned integer key
Hello there sqlite-users, I have a quick question regarding selection and deletion of large unsigned integers. I create my table as following: [1] CREATE TABLE t1 (instnum BIG UNSIGNED INTEGER PRIMARY KEY, ...) I then insert some row: [2] INSERT INTO t1 (instnum, ...) VALUES (CAST (?1 AS BIG UNSIGNED INTEGER), ...) This is just a test for 64bit unsigned, so I have the following rows: 1 2 18446744073709551615 (0x) 9223372036854775808 (0x8000) 0 3 1234 23456 654321 When I try to DELETE using: [3] DELETE FROM t1 WHERE instnum <= CAST (?1 AS BIG UNSIGNED INTEGER); I don't always get what I would expect, to me it looks like large numbers (64bit with the MSB set to 1) are treated as negative. For example if ?1 in query [3] is set to 654321, all rows are being deleted (while 18446744073709551615 and 9223372036854775808 should not be deleted) If ?1 is set to 18446744073709551615, the rows 18446744073709551615 and 9223372036854775808 are deleted, but the other ones are not. (while all of them should be gone) (I was previously using [2] and [3] without explicit cast, but the result is the same). How can I solve this issue? Thank you, Marco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Triggers
On 29 September 2010 11:49, wrote: > Hi, > > I have this table: > > CREATE TABLE [repo] ( > [code] varchar(20) PRIMARY KEY NOT NULL, > [klass] varchar(2) > ); > > and trying to create this trigger: > > CREATE TRIGGER repo_klass > BEFORE INSERT ON `repo` > BEGIN > new.klass = SUBSTR(new.code,4,2); > END; > > I got this error: > near "new": syntax error. > > Kindly assist. http://www.sqlite.org/lang_createtrigger.html BEGIN ... END; brackets a valid update, insert, delete or select SQL statement. "new.klass = SUBSTR(new.code,4,2);" is not one of the above. try: CREATE TRIGGER repo_klass AFTER INSERT ON repo BEGIN UPDATE repo SET klass = SUBSTR( new.code, 4, 2 ) WHERE code=new.code; END; > > Thanks. > > Chris. Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Triggers
Hi, I have this table: CREATE TABLE [repo] ( [code] varchar(20) PRIMARY KEY NOT NULL, [klass] varchar(2) ); and trying to create this trigger: CREATE TRIGGER repo_klass BEFORE INSERT ON `repo` BEGIN new.klass = SUBSTR(new.code,4,2); END; I got this error: near "new": syntax error. Kindly assist. Thanks. Chris. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
