# -*- mode: python; tab-width:8; py-indent-offset:4; indent-tabs-mode:nil -*-

import sqlite3
import thread

class db(object):

    def __init__(self, dbname):
        self.dbname = dbname
        self._connections = {}

    def _get_conn(self):
        thread_id = thread.get_ident()
        conn = self._connections.get(thread_id, None)

        if not conn:
            conn = sqlite3.connect(self.dbname)
            self._connections[thread_id] = conn

        return conn

    def get_viewinfo(self, viewname):
        c = self._get_conn().cursor()
        c.execute('select title,startdate,enddate from viewinfo where viewname = :viewname;',
                 {"viewname":viewname}) 
        title,start,end = list(c)[0]

        c.execute('select clubid from viewclubs where viewname = :viewname order by indx;',
                  {"viewname":viewname})
        club_ids = [row[0] for row in c]
        return title, club_ids, start, end

    def get_clubs(self):
        c = self._get_conn().cursor()
        c.execute('select clubid, fullname, url, favicon from clubs;')
        return list(c)

    def get_rides(self, datestr):
        c = self._get_conn().cursor()
        c.execute("""select clubid, name, notes, url from rides """
                  """where date(date)>=date(:datestr, '-3 days') and """
                 """       date(date)<=date(:datestr, '+2 days');""",
                 {"datestr":datestr})
        return list(c)


def main():

    def show_rides(date):
        rides = ridedb.get_rides(date)
        print len(rides), "RIDES on date", date
        for r in rides:
             print r
         
    ridedb = db("rides.sqlite")

    show_rides("2009-02-21")
    show_rides("2009-04-11")
    show_rides("2009-04-18")
    show_rides("2009-03-07")
    show_rides("2009-05-02")

    viewname = "main"
    title, club_ids, start, end = ridedb.get_viewinfo(viewname)
    print "View:", viewname
    print "Title:", title
    print "ids:", club_ids
    print "Dates:", start, "thru", end

if __name__ == "__main__":
    main()