Re: [sqlite] Status of LSM1 extension

[Keith Medcalf]

On Thursday, 5 March, 2020 20:39, Charles Leifer  wrote:

>Keith, if you could share a bit more details on how you do that, I'd be
>interested.

I presume you mean how to create a "built-in" extension, which is available for 
all connections, just the built-in functions and modules.

There is a built-in define SQLITE_EXTRA_INIT used in main.c which allows for a 
"user function" to be called at the end of the SQLite3 initialization process.  
You compile the code/amalgamation with SQLITE_EXTRA_INIT defined to the name of 
a function that takes a NULL parameter and returns an SQLITE error code 
(SQLITE_OK, SQLITE_ERROR, etc).  This function is called *after* the SQLite3 
core is initialized.  It does additional initialization of the library.

For example, to autoload some extensions on every connection you can append the 
code for the extension to the amalgamation sqlite3.c file and then append you 
EXTRA_INIT function to add the extension init procedure to the auto extension 
list:

-DSQLITE_EXTRA_INIT=coreinit

static int coreinit(const char* dummy)
(
return sqlite3_auto_extension((void*)sqlite3_series_init);
}

If you append series.c to the amalgamation, and then the coreinit function, and 
compile with -DSQLITE_EXTRA_INIT=coreinit, then when SQLite3 is initialized the 
coreinit function will run after the initialization is complete and add the 
sqlite3_series_init function to the auto_extension list.  Then when any new 
connection is opened the generate_series extension will be registered on that 
connection.

Of course, the extensions and the coreinit function do not have to be part of 
the amalgamation compilation unit -- they can be in a separate file and 
statically linked.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] Status of LSM1 extension

[Charles Leifer]

Keith, if you could share a bit more details on how you do that, I'd be
interested.

On Thu, Mar 5, 2020 at 8:43 AM Keith Medcalf  wrote:

>
> On Thursday, 5 March, 2020 05:51, Dominique Devienne 
> wrote:
>
> >PS: I'd still very much appreciate an LSM1 amalgamation
>
> cd ext/lsm1
> tclsh tool/mklsm1c.tcl
>
> which will write an lsm1.c amalgamation in the current directory (ext/lsm1)
>
> You can append this to the amalgamation and use an EXTRA_INIT hook to
> initialize it, just like building in any other extension (though you need
> to define SQLITE_ENABLE_LSM1 in order for the extension code to be compiled)
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Status of LSM1 extension

[Keith Medcalf]

On Thursday, 5 March, 2020 05:51, Dominique Devienne  
wrote:

>PS: I'd still very much appreciate an LSM1 amalgamation

cd ext/lsm1
tclsh tool/mklsm1c.tcl

which will write an lsm1.c amalgamation in the current directory (ext/lsm1)

You can append this to the amalgamation and use an EXTRA_INIT hook to 
initialize it, just like building in any other extension (though you need to 
define SQLITE_ENABLE_LSM1 in order for the extension code to be compiled)

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Status of LSM1 extension

[Dominique Devienne]

On Thu, Mar 5, 2020 at 12:35 PM Dan Kennedy  wrote:
> On 5/3/63 16:11, Dominique Devienne wrote:
> > I'm interested in LSM1 [1] as an alternative to SQLite [...]
>
> [...], I don't think it's too bad of an implementation. The
> automated tests are reasonably good - although of course not as good as
> SQLite's though. And the docs are stored in kind of a ridiculous place
> at the moment, but I think they're quite complete.
>
> Not planning to develop this any further unless a big user emerges,
> which is not impossible. I do intend to fix any reported bugs though.

Thanks Dan. I appreciate your candor, and support commitment.
Given the above, I'll start with SQLite, and when/if I'm done with what
I need to do, will try to give LSM1 a try, to compare performance, and
report back.

Thanks again, --DD

PS: I'd still very much appreciate an LSM1 amalgamation
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Status of LSM1 extension

[Dan Kennedy]

On 5/3/63 16:11, Dominique Devienne wrote:

Hi,

I'm interested in LSM1 [1] as an alternative to SQLite, since in a
particular use-case,
I'm using SQLite mostly as a key-value store, and write performance is
particularly important,
in addition to MVCC. Sounds like it could be an excellent fit here,
and the fact it comes from
the SQLite team is something I value.

That said, the only online doc for LSM1 ([2] and [3]) are from the
defunct SQLite4 web-site,
and the main blog post is starting to look dated [4]. I thus wonder
about the level of quality
and support on LSM1, and lack of doc for it in the main SQLite web-site.

In terms of practicality, there's also no amalgamation for LSM1. And the virtual
table over LSM1 data-files [5], something I was look for, does not
appear to be documented
anywhere. Notably whether using that vtable using the familiar SQLite
API is advisable
instead of using the different and unfamiliar LSM1-specific API.

I'm just looking for clarity and advice around LSM1, as well as
commitments regarding
its level of quality, testing, and support. And whether we can hope to
have more doc and
amalgamation deliverables in the future. It sounds like it's a really
nice piece of code, but
the fact there's very little noise and advertisement about it is
somewhat worrying.



It's very much a solution looking for a problem at this point. It's not, 
as far as I know, used in anything that is too widely deployed.


That said, I don't think it's too bad of an implementation. The 
automated tests are reasonably good - although of course not as good as 
SQLite's though. And the docs are stored in kind of a ridiculous place 
at the moment, but I think they're quite complete.


Not planning to develop this any further unless a big user emerges, 
which is not impossible. I do intend to fix any reported bugs though.


Dan.






Thanks, --DD

[1] https://www2.sqlite.org/src/dir?name=ext/lsm1
[2] https://sqlite.org/src4/doc/trunk/www/lsmusr.wiki
[3] https://sqlite.org/src4/doc/trunk/www/lsmapi.wiki
[4] https://charlesleifer.com/blog/lsm-key-value-storage-in-sqlite3/
[5] https://www2.sqlite.org/src/finfo?name=ext/lsm1/lsm_vtab.c
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Status of LSM1 extension

[Dominique Devienne]

Hi,

I'm interested in LSM1 [1] as an alternative to SQLite, since in a
particular use-case,
I'm using SQLite mostly as a key-value store, and write performance is
particularly important,
in addition to MVCC. Sounds like it could be an excellent fit here,
and the fact it comes from
the SQLite team is something I value.

That said, the only online doc for LSM1 ([2] and [3]) are from the
defunct SQLite4 web-site,
and the main blog post is starting to look dated [4]. I thus wonder
about the level of quality
and support on LSM1, and lack of doc for it in the main SQLite web-site.

In terms of practicality, there's also no amalgamation for LSM1. And the virtual
table over LSM1 data-files [5], something I was look for, does not
appear to be documented
anywhere. Notably whether using that vtable using the familiar SQLite
API is advisable
instead of using the different and unfamiliar LSM1-specific API.

I'm just looking for clarity and advice around LSM1, as well as
commitments regarding
its level of quality, testing, and support. And whether we can hope to
have more doc and
amalgamation deliverables in the future. It sounds like it's a really
nice piece of code, but
the fact there's very little noise and advertisement about it is
somewhat worrying.

Thanks, --DD

[1] https://www2.sqlite.org/src/dir?name=ext/lsm1
[2] https://sqlite.org/src4/doc/trunk/www/lsmusr.wiki
[3] https://sqlite.org/src4/doc/trunk/www/lsmapi.wiki
[4] https://charlesleifer.com/blog/lsm-key-value-storage-in-sqlite3/
[5] https://www2.sqlite.org/src/finfo?name=ext/lsm1/lsm_vtab.c
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Status analyze with Sqlite

[Keith Medcalf]

Your expected result does not make any logical algorithmic sense whatsoever.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Steffen Mangold
> Sent: Friday, 23 November, 2012 02:02
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Status analyze with Sqlite
> 
> 
> 
> > ID  | TimeStamp | Status
> > 
> > 0   | 2012-07-24 22:23:00   | status1
> > 1   | 2012-07-24 22:23:05   | status1
> > 2   | 2012-07-24 22:23:10   | status2
> > 3   | 2012-07-24 22:23:16   | status2
> > 4   | 2012-07-24 22:23:21   | status2
> > 5   | 2012-07-24 22:23:26   | status2
> > 6   | 2012-07-24 22:23:32   | status2
> > 7   | 2012-07-24 22:23:37   | status3
> > 8   | 2012-07-24 22:23:42   | status3
> > 9   | 2012-07-24 22:23:47   | status3
> >
> > What I want as result is
> > ID  | Begin | End   | Status
> > ---
> 
> > 0   | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
> > 1   | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
> > 2   | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3
> >
> 
> >
> > Hmmm, the ID in the result bears virtually no relation to the ID in the
> data. Is that  intentional?
> >
> > Anyhow, some of what you want could come from
> >
> > select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data
> group by Status order by Status
> >
> 
> Hi Gerry,
> 
> thank you but this won't work if the table look like this:
> 
>  ID   | TimeStamp | Status
>  
>  0| 2012-07-24 22:23:00   | status1
>  1| 2012-07-24 22:23:05   | status1
>  2| 2012-07-24 22:23:10   | status2
>  3| 2012-07-24 22:23:16   | status2
>  4| 2012-07-24 22:23:21   | status1
>  5| 2012-07-24 22:23:26   | status1
>  6| 2012-07-24 22:23:32   | status2
>  7| 2012-07-24 22:23:37   | status3
>  8| 2012-07-24 22:23:42   | status3
>  9| 2012-07-24 22:23:47   | status3
> 
> Than you get:
>  ID   | Begin | End   | Status
>  
> ---
>  0| 2012-07-24 22:23:00   | 2012-07-24 22:23:26   | status1
>  1| 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
>  2| 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3
> 
> But it should be:
> ID| Begin | End   | Status
>  
> ---
>  0| 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
>  0| 2012-07-24 22:23:10   | 2012-07-24 22:23:16   | status2
>  1| 2012-07-24 22:23:21   | 2012-07-24 22:23:26   | status1
>  2| 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3
> 
> You are right the ID column is not relevant. You can ignore them.
> 
> regards
> Steffen Mangold



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

Re: [sqlite] Status analyze with Sqlite

[Simon Slavin]

On 23 Nov 2012, at 9:01am, Steffen Mangold  wrote:

> But it should be:
> ID| Begin | End   | Status
> ---
> 0 | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
> 0 | 2012-07-24 22:23:10   | 2012-07-24 22:23:16   | status2
> 1 | 2012-07-24 22:23:21   | 2012-07-24 22:23:26   | status1
> 2 | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

You can find the 'Begin's by, for every line, looking up the previous line with 
a sub-SELECT and checking to see whether it has the same status.  Then you do 
the 'Ends' by looking up the following line.  Might have some trouble with the 
first and last lines in the table.  It would be a horribly complicated SQL 
command.

However, do you absolutely have to do this inside a SQL command ?  It's trivial 
in any programming language but puts a great load on a SQL engine.  In code you 
only have to read every row once, in an order already provided by an INDEX.  If 
you do it in SQL you also have to do at least one look-up for every row, keyed 
on a long string.

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

Re: [sqlite] Status analyze with Sqlite

[Clemens Ladisch]

Steffen Mangold wrote:
> TimeStamp | Status
> -
> 2012-07-24 22:23:00   | status1
> 2012-07-24 22:23:05   | status1
> 2012-07-24 22:23:10   | status2
> 2012-07-24 22:23:16   | status2
> 2012-07-24 22:23:21   | status1
> 2012-07-24 22:23:26   | status1
> 2012-07-24 22:23:32   | status2
> 2012-07-24 22:23:37   | status3
> 2012-07-24 22:23:42   | status3
> 2012-07-24 22:23:47   | status3
>
> This is what I want:
>
> Begin | End   | Status
> ---
> 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
> 2012-07-24 22:23:10   | 2012-07-24 22:23:16   | status2
> 2012-07-24 22:23:21   | 2012-07-24 22:23:26   | status1
> 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

And what about the event at 22:23:32?


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

Re: [sqlite] Status analyze with Sqlite

[Steffen Mangold]

Hi Clemens,

first thank you for your comments.

>
> It works for what you've asked.  Perhaps you should not have kept your actual 
> requirements a secret.
>

Sorry for being unclear! :(
Hope my English is understandable (I'm from Germany).

>
> So you want to group only consecutive events with identical statuses, where 
> "consecutive" is defined on the ordering of the timestamps?
>
> Then why do you write them?  (And why are there duplicates in your last 
> example?)
>

You are right I only want consecutive events of same status. Here more a clear 
example.

This is the table:

TimeStamp   | Status
-
2012-07-24 22:23:00 | status1
2012-07-24 22:23:05 | status1
2012-07-24 22:23:10 | status2
2012-07-24 22:23:16 | status2
2012-07-24 22:23:21 | status1
2012-07-24 22:23:26 | status1
2012-07-24 22:23:32 | status2
2012-07-24 22:23:37 | status3
2012-07-24 22:23:42 | status3
2012-07-24 22:23:47 | status3


This is what I want:

Begin   | End   | Status
---
2012-07-24 22:23:00 | 2012-07-24 22:23:05   | status1
2012-07-24 22:23:10 | 2012-07-24 22:23:16   | status2
2012-07-24 22:23:21 | 2012-07-24 22:23:26   | status1
2012-07-24 22:23:37 | 2012-07-24 22:23:47   | status3

>
> Well, try this:
>
> SELECT TimeStamp AS Begin,
>(SELECT MAX(TimeStamp)
> FROM Data AS same
> WHERE same.Status = ou.Status
>   AND same.TimeStamp >= ou.TimeStamp
>   AND same.TimeStamp < (COALESCE((SELECT MIN(TimeStamp)
>   FROM Data AS next
>   WHERE next.TimeStamp > ou.TimeStamp
> AND next.Status <> ou.Status),
>  ''))
>) AS End,
>Status
> FROM Data AS ou
> WHERE Status IS NOT (SELECT Status
>  FROM (SELECT Status,
>   MAX(prev.TimeStamp)
>FROM Data AS prev
>   WHERE prev.TimeStamp < ou.TimeStamp))
>

Thank you I try this.

>
> (And it might be easier and faster to just query the events ordered by 
> timestamp, and aggregate statuses by hand in your code.)
>
Hm ok I make some test. I think you can be right that in-code aggregation is 
faster that subquerys.

Regards,
Steffen


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

Re: [sqlite] Status analyze with Sqlite

[Clemens Ladisch]

Steffen Mangold wrote:
> this won't work

It works for what you've asked.  Perhaps you should not have kept your
actual requirements a secret.

> if the table look like this:
>
>  ID   | TimeStamp | Status
>  
>  0| 2012-07-24 22:23:00   | status1
>  1| 2012-07-24 22:23:05   | status1
>  2| 2012-07-24 22:23:10   | status2
>  3| 2012-07-24 22:23:16   | status2
>  4| 2012-07-24 22:23:21   | status1
>  5| 2012-07-24 22:23:26   | status1
>  6| 2012-07-24 22:23:32   | status2
>  7| 2012-07-24 22:23:37   | status3
>  8| 2012-07-24 22:23:42   | status3
>  9| 2012-07-24 22:23:47   | status3
>
> Than you get:
>  ID   | Begin | End   | Status
>  
> ---
>  0| 2012-07-24 22:23:00   | 2012-07-24 22:23:26   | status1
>  1| 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
>  2| 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3
>
> But it should be:
> ID| Begin | End   | Status
>  
> ---
>  0| 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
>  0| 2012-07-24 22:23:10   | 2012-07-24 22:23:16   | status2
>  1| 2012-07-24 22:23:21   | 2012-07-24 22:23:26   | status1
>  2| 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

So you want to group only consecutive events with identical statuses,
where "consecutive" is defined on the ordering of the timestamps?

> You are right the ID column is not relevant. You can ignore them.

Then why do you write them?  (And why are there duplicates in your last
example?)


Well, try this:

SELECT TimeStamp AS Begin,
   (SELECT MAX(TimeStamp)
FROM Data AS same
WHERE same.Status = ou.Status
  AND same.TimeStamp >= ou.TimeStamp
  AND same.TimeStamp < (COALESCE((SELECT MIN(TimeStamp)
  FROM Data AS next
  WHERE next.TimeStamp > ou.TimeStamp
AND next.Status <> ou.Status),
 ''))
   ) AS End,
   Status
FROM Data AS ou
WHERE Status IS NOT (SELECT Status
 FROM (SELECT Status,
  MAX(prev.TimeStamp)
   FROM Data AS prev
   WHERE prev.TimeStamp < ou.TimeStamp))

(And it might be easier and faster to just query the events ordered by
timestamp, and aggregate statuses by hand in your code.)


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

Re: [sqlite] Status analyze with Sqlite

[Steffen Mangold]

> ID| TimeStamp | Status
> 
> 0 | 2012-07-24 22:23:00   | status1
> 1 | 2012-07-24 22:23:05   | status1
> 2 | 2012-07-24 22:23:10   | status2
> 3 | 2012-07-24 22:23:16   | status2
> 4 | 2012-07-24 22:23:21   | status2
> 5 | 2012-07-24 22:23:26   | status2
> 6 | 2012-07-24 22:23:32   | status2
> 7 | 2012-07-24 22:23:37   | status3
> 8 | 2012-07-24 22:23:42   | status3
> 9 | 2012-07-24 22:23:47   | status3
>
> What I want as result is
> ID| Begin | End   | Status
> ---
> 0 | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
> 1 | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
> 2 | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3
>

>
> Hmmm, the ID in the result bears virtually no relation to the ID in the data. 
> Is that  intentional?
>
> Anyhow, some of what you want could come from
> 
> select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data group 
> by Status order by Status
> 

Hi Gerry,

thank you but this won't work if the table look like this:

 ID | TimeStamp | Status
 
 0  | 2012-07-24 22:23:00   | status1
 1  | 2012-07-24 22:23:05   | status1
 2  | 2012-07-24 22:23:10   | status2
 3  | 2012-07-24 22:23:16   | status2
 4  | 2012-07-24 22:23:21   | status1
 5  | 2012-07-24 22:23:26   | status1
 6  | 2012-07-24 22:23:32   | status2
 7  | 2012-07-24 22:23:37   | status3
 8  | 2012-07-24 22:23:42   | status3
 9  | 2012-07-24 22:23:47   | status3

Than you get:
 ID | Begin | End   | Status
 ---
 0  | 2012-07-24 22:23:00   | 2012-07-24 22:23:26   | status1
 1  | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
 2  | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

But it should be:
ID  | Begin | End   | Status
 ---
 0  | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
 0  | 2012-07-24 22:23:10   | 2012-07-24 22:23:16   | status2
 1  | 2012-07-24 22:23:21   | 2012-07-24 22:23:26   | status1
 2  | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

You are right the ID column is not relevant. You can ignore them.

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

Re: [sqlite] Status analyze with Sqlite

[Gerry Snyder]

On 11/22/2012 4:47 PM, Steffen Mangold wrote:

HI sqlite community,

I have a problem I get stucked, maybe someone can help me. :(

My issue:

For instance if we have 10 rows with following data

ID  | TimeStamp | Status

0   | 2012-07-24 22:23:00   | status1
1   | 2012-07-24 22:23:05   | status1
2   | 2012-07-24 22:23:10   | status2
3   | 2012-07-24 22:23:16   | status2
4   | 2012-07-24 22:23:21   | status2
5   | 2012-07-24 22:23:26   | status2
6   | 2012-07-24 22:23:32   | status2
7   | 2012-07-24 22:23:37   | status3
8   | 2012-07-24 22:23:42   | status3
9   | 2012-07-24 22:23:47   | status3

What I want as result is
ID  | Begin | End   | Status
---
0   | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
1   | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
2   | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3



Hmmm, the ID in the result bears virtually no relation to the ID in the 
data. Is that intentional?


Anyhow, some of what you want could come from

select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data 
group by Status order by Status



HTH,

Gerry


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

[sqlite] Status analyze with Sqlite

[Steffen Mangold]

HI sqlite community,

I have a problem I get stucked, maybe someone can help me. :(

My issue:

For instance if we have 10 rows with following data

ID  | TimeStamp | Status

0   | 2012-07-24 22:23:00   | status1
1   | 2012-07-24 22:23:05   | status1
2   | 2012-07-24 22:23:10   | status2
3   | 2012-07-24 22:23:16   | status2
4   | 2012-07-24 22:23:21   | status2
5   | 2012-07-24 22:23:26   | status2
6   | 2012-07-24 22:23:32   | status2
7   | 2012-07-24 22:23:37   | status3
8   | 2012-07-24 22:23:42   | status3
9   | 2012-07-24 22:23:47   | status3

What I want as result is
ID  | Begin | End   | Status
---
0   | 2012-07-24 22:23:00   | 2012-07-24 22:23:05   | status1
1   | 2012-07-24 22:23:10   | 2012-07-24 22:23:32   | status2
2   | 2012-07-24 22:23:37   | 2012-07-24 22:23:47   | status3

What I have so far is

SELECT ou. ID AS ID,
ou.Status AS Status,
ou.TimeStamp AS Begin,
(SELECT MAX(TimeStamp)
FROM Data
WHERE TimeStamp >= ou.TimeStamp 
AND 
Status = ou.Status
AND
TimeStamp < '2010-02-24 00:00:00') AS End
FROM Data ou

But don't work :(
Can you please help me?

Regards
Steffen Mangold


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

Re: [sqlite] Status

[Krystian Bigaj]

I think that you can track progress at
http://www.sqlite.org/src/timeline?n=20=winrt

Best regards,
Krystian Bigaj


On 2 March 2012 16:37, Steven Nesbit  wrote:

> Sorry about that, WinRT Sqlite
>
> Steve
>
>
> ___
> 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] Status

[Steven Nesbit]

Sorry about that, WinRT Sqlite 

Steve


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

Re: [sqlite] Status

[Marc L. Allen]

Sorry... What effort?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Steven Nesbit
> Sent: Friday, March 02, 2012 10:35 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Status
> 
> What is the status of this effort?  We actually need to have the
> platform determined at runtime since we need to run on WinRT, Android
> and iOS.
> 
> 
> 
> Steve
> 
> ___
> 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] Status

[Steven Nesbit]

What is the status of this effort?  We actually need to have the platform 
determined at runtime since we need to run on WinRT, Android and iOS.



Steve

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

Re: [sqlite] status of unqlspec / sqlite

[Alek Paunov]

Hi,

Recent comment on the topic from unql mailing list:

https://groups.google.com/forum/#!msg/unql/dVc_cM1ZGw8/3QHE1_MIqRQJ

On 04.11.2011 10:50, sqlite-us...@h-rd.org wrote:

Hi,

some time ago Richard was involved in http://www.unqlspec.org/ . Is that
still going on? I am quite interested in a backend for sqlite.



Sqlite backend for UNQL frontend or new backend for VDBE in sqlite ?


thanks,

___
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] status of unqlspec / sqlite

[sqlite-us...@h-rd.org]

Hi,

some time ago Richard was involved in http://www.unqlspec.org/ .  Is  
that still going on?  I am quite interested in a backend for sqlite.


thanks,

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

Re: [sqlite] Status of SQLite's full-text search (and Python 2.5 support?)

[python]

P Kishor,

> I have no idea how I conveyed that impression. I think FTS3 is really 
> wonderful, and have implemented it in my own personal website. I firmly 
> believe in the "Why file when you can full-text search" doctrine.

Excellent! Thanks for the follow-up. You answered my questions. And put
my mind at ease.

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

Re: [sqlite] Status of SQLite's full-text search (and Python 2.5 support?)

[P Kishor]

On 4/20/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >>  Is SQLite's full text ready for production use yet?
>
>  > as ready as it will be. FTS 1/2 are deprecated.
>
>
> You don't sound too thrilled :) Are there any limitations that one
>  should be aware of?

I have no idea how I conveyed that impression. I think FTS3 is really
wonderful, and have implemented it in my own personal website. I
firmly believe in the "Why file when you can full-text search"
doctrine.

The folks ** who developed it are on this list and answer development
related questions pretty responsively. FTS3 works very well for me.

** (afaik, they work for a large company located in the zip code 900913)


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

Re: [sqlite] Status of SQLite's full-text search (and Python 2.5 support?)

[python]

>>  Is SQLite's full text ready for production use yet?

> as ready as it will be. FTS 1/2 are deprecated. 

You don't sound too thrilled :) Are there any limitations that one
should be aware of?

Thank you,

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

Re: [sqlite] Status of SQLite's full-text search (and Python 2.5 support?)

[P Kishor]

On 4/20/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I'm trying to figure out the status of SQLite's support for full-text
>  search.
>
>  It appears that full-text support was provisionally added to SQLite in
>  late 2006 via an extension module. It sounds like this early version was
>  experimental only.
>
>  After more googling it appears that there are two add-on modules for
>  full text search: FTS1 and FTS2.
>
>  Is SQLite's full text ready for production use yet?

as ready as it will be. FTS 1/2 are deprecated. Use FTS3 that is a
part of the main distro now.


>
>  I'm also wondering where I can find out if the version of SQLite that
>  ships as part of Python 2.5 includes automatic support for full text
>  search.
>
>  Thank you,
>  Malcolm
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Status of SQLite's full-text search (and Python 2.5 support?)

[python]

I'm trying to figure out the status of SQLite's support for full-text
search.

It appears that full-text support was provisionally added to SQLite in
late 2006 via an extension module. It sounds like this early version was
experimental only.

After more googling it appears that there are two add-on modules for
full text search: FTS1 and FTS2.

Is SQLite's full text ready for production use yet?

I'm also wondering where I can find out if the version of SQLite that
ships as part of Python 2.5 includes automatic support for full text
search.

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

Re: [sqlite] Status of FOREIGN KEY implementation

[David M. Cook]

On Wed, Aug 11, 2004 at 05:09:49PM -0700, Cliff Hudson wrote:

> engine in a mobile device.  Foreign key constraints are one of the features
> I would like to see in any database engine we eventually use.  According to
> the web site, this feature is not yet implemented, and the mailing list

This doesn't answer your question, but you can use triggers to implement
things like "on delete cascade", e.g.

  CREATE TRIGGER recording_disc_id_delete DELETE ON disc
  BEGIN
  DELETE FROM recording WHERE disc_id=old.id;
  END;

If you have a lot of tables, you could use a scripting language to generate
the triggers based on sqlite metadata.  For some metadata PRAGMAs see:

  http://sqlite.org/lang.html#pragma

Dave Cook

Re: [sqlite] Status Update: Unix file locking is badly broken

[D. Richard Hipp]

The latest version in CVS defers close() calls until locks
have all cleared.  If you are running SQLite on unix and
have a chance, please throw everything you can at this
new version.  If I get no reports of problems over the next
few days I'll call this one 2.8.10 and release it.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]