select time intervals in sql

Date Tags sql

consider the following example :

create table elem (
    id INTEGER,
    name VARCHAR,
    num INTEGER
);
create table proxy (
    elem_id INTEGER,
    time_id INTEGER
);
create table time (
    id INTEGER,
    timestamp DATESTAMP
);

insert into elem values (0,"one",1);
insert into elem values (1,"one",2);
insert into elem values (2,"one",3);
insert into elem values (3,"two",1);

insert into proxy values (0,0);
insert into proxy values (0,1);
insert into proxy values (1,2);
insert into proxy values (1,3);
insert into proxy values (2,4);
insert into proxy values (3,4);

insert into time values (0,"12/12/2008");
insert into time values (1,"13/12/2008");
insert into time values (2,"14/12/2008");
insert into time values (3,"15/12/2008");
insert into time values (4,"16/12/2008");

What I want is to write a query that will group for each (name,num) the start and end edges of the interval given by the table time:

For example:

one | 1 | 12/12/2008 | 13/12/2008
one | 2 | 14/12/2008 | 15/12/2008
one | 3 | 16/12/2008 | 16/12/2008
two | 1 | 16/12/2008 | 16/12/2008

First we create a simple view to unclutter the query statement.

 create temp view all_t as select elem.name,elem.num,time.timestamp from elem,time,proxy where proxy.elem_id = elem.id and time.id = proxy.time_id ;

Then the sql query is pretty straightforward …

select name,num,min(timestamp),max(timestamp) from all_t group by name, num;
one|1|12/12/2008|13/12/2008
one|2|14/12/2008|15/12/2008
one|3|16/12/2008|16/12/2008
two|1|16/12/2008|16/12/2008

dynamic forms with django

Today I started learning how to write web forms in django. My quest was to write a simple search form where I could specify multiple criteria . Django is a very nice and flexible framework written in python and it is also reasonably well documented.

I don’t feel writing much today. this is the code :

The tricky part was to understand how to re-display the view and to add a new field. This is easily accomplished in django using the formset class that allows to put display together more then one form. In this case the logic is simple. First we display an empty form with two submit buttons, Add and Search. Search brings the obvious result. Add gets the data that was submitted, validates it and re-display the form with an additional field. Note also that the default validation functions are used transparently to validate the input of each sub-form.

views.py

from django.http import HttpResponse
from django.shortcuts import render_to_response
from cudfdep.depo.forms import SearchField
from django.forms.formsets import formset_factory
from django.http import Http404

def search(request):
    SearchFormSet = formset_factory(SearchField)
    data = {'formset': SearchFormSet(), 'debug' : 'default'}
    if request.method == 'POST':
        formset = SearchFormSet(request.POST)
        if formset.is_valid() :
            if request.POST.get('form-submit') == 'Add':
                data = {'formset': SearchFormSet(initial=formset.cleaned_data), 'debug' : 'add'}
            elif (request.POST.get('form-submit') == 'Search' ):
                data = {'formset': SearchFormSet(), 'debug' : 'result' }
            else :
                raise Http404('Invalid request')
        else :
            data = {'formset': formset, 'debug' : 'not valid'}

    return render_to_response('search_form.html', data)

The forms file describes the form logic to be displayed.

forms.py

from django import forms

class SearchField(forms.Form):
    date = forms.DateTimeField(label='date', widget=forms.DateTimeInput)
    arch = forms.ChoiceField([(0,'i386'),(1,'amd64')], widget=forms.Select,initial=0)
    release = forms.ChoiceField([(0,'etch'),(1,'lenny'),(2,'sid'),(3,'squeeze')],widget=forms.Select,initial=0)

This is the template :

search_form.html

<html>
<head>
    <title>Search Form</title>
</head>
<body>
    <form action="" method="post">
      {{ formset.management_form }}
      <table>
          {% for form in formset.forms %}
          {{ form.as_table }}
          {% endfor %}
      </table>
      <input name='form-submit' type="submit" value="Add">
      <input name='form-submit' type="submit" value="Search">
    </form>
    {% if debug %}
        <p style="color: red;">{{ debug }}</p>
    {% endif %}
</body>
</html>

my freerunner is finally free!!

This week I got myself a sim card and I started doing a more appropriate use of my freerunner. For a start I’ve flushed it with the latest SHR distribution. The SHR is based on FSO and I’ve the feeling that is stable enough for daily use. After struggling a bit I’ve also installed debian. The installer script is still a bit broken and, in particular in this period, is very fragile because of the state of flush of debian unstable. Nonetheless, after a couple of restart I managed to get it going.

I’ve also flushed the FR with QI, the new boot loader, that is seems faster then u-boot. It is also more strict about the kernel location and naming, but nothing I can’t handle :)

Last but not least, I flushed the new GSM firmware using the image that is provided by OM. It worked straightaway, nothing much to report here.

All in all, I’m pretty happy with the state of things. I received and made phone calls as well as sms. Sometimes there are small usability quirks that make it more difficult to use the phone that what it should. I’ve seen a lot of bug reports and suggestions and I’m sure we’re going to solve everything soon.

Not very much of a technical post. Maybe I’ll collect few links later on…

quotes of the day

Date Tags quotes

“Democracy is two wolves and a lamb voting on what to have for lunch.” -Benjamin Franklin

“Security is like virginity…once compromised it is lost forever.” -Anonymous Coward

yes, I do read /.

transparently open compressed files in ocaml

The Pervasive.in_channel in the ocaml std library is not extensible. If you want to mix different in_channel, for example, one from Pervasive and on from the Gzip library, then you are in trouble. The good fellows of extlib solved this problem providing an extensible stream data type in the IO library [1] . This is a small example using that module.

let gzip_open_in file =
    let ch = Gzip.open_in file in
    IO.create_in
    ~read:(fun () -> Gzip.input_char ch)
    ~input:(Gzip.input ch)
    ~close:(fun () -> Gzip.close_in ch)

let std_open_in file =
    let ch = open_in file in
    IO.create_in
    ~read:(fun () -> input_char ch)
    ~input:(input ch)
    ~close:(fun () -> close_in ch)

let main () =
    let file = Sys.argv.(1) in
    let ch =
        if Filename.check_suffix file ".gz" then gzip_open_in file 
        else std_open_in file
    in
    try
        while true do
            print_endline (IO.read_line ch)
        done
    with End_of_file ->
        IO.close_in ch
;;

main ();;

untested compile commands (I use ocamlbuild + custom myocamlbuild.ml):

ocamlfind ocamldep -package extlib -package zip -modules test.ml > test.ml.depends
ocamlfind ocamlc -c -package extlib -package zip -o test.cmo test.ml
ocamlfind ocamlc -linkpkg -package extlib -package zip test.cmo -o test.byte

[1] http://ocaml-lib.sourceforge.net/doc/IO.html