Re: [SQLObject] Temporary Objects (I'm going in!)

[Luke Opperman <luke@me...>]

> I think t.insertObject(c) isn't a good idea.  Client(1) is an
> object
> that may be in use in several places, and is presumed to be
> transparently persistent.  When you put it into the transaction
> you've
> made temporary for all users of the object.  Bad bugs will follow.
> At
> least in threaded situations.
>
> This is why I think the object has to be instantiated as part of
> the
> transaction, or perhaps copied if you don't like instantiation...
>
> t = Transaction()
> c = Client(1)
> ctmp = c.inTransaction(t)
> # or...
> ctmp = Client(1, t)
>
>   Ian
>

I couldn't agree more, this is also refers to the challenges (from an
interface perspective) of "insertObject" for new objects. Hence my
semantics note, "a temporary object (including one in Transaction:
i'm considering transactions to be a simple wrapper around a
temporary object implementation) is not visible to anything that
doesn't explicitly reference the python-side instance of it". This is
also why it seems to me that any Real objects referenced by or to a
temp object need to immediately become temp (part of the
transaction).

My original interface suggestion is very close to what you just wrote,
with cInst.temp() or cClass.temp(...) returning temporary objects,
outside of being referenced by any other thread unless explicitly
passed by the programmer.

Anyways, all just wanking until something gets built. :) I'll get on
it.

- Luke

Re: [SQLObject] Temporary Objects (I'm going in!)

[Ian Bicking <ianb@co...>]

On Tue, 2003-04-29 at 12:17, Luke Opperman wrote:
> > from Transaction import Transaction
> > from invoicing import Client
> > t = Transaction()
> > c = Client(1)
> > t.insertObject(c)
> > c.address1 = "100 New Street"
> > t.saveChanges()

I think t.insertObject(c) isn't a good idea.  Client(1) is an object
that may be in use in several places, and is presumed to be
transparently persistent.  When you put it into the transaction you've
made temporary for all users of the object.  Bad bugs will follow.  At
least in threaded situations.

This is why I think the object has to be instantiated as part of the
transaction, or perhaps copied if you don't like instantiation...

t = Transaction()
c = Client(1)
ctmp = c.inTransaction(t)
# or...
ctmp = Client(1, t)

  Ian

Re: [SQLObject] possible problem with lazy loading still happening

[Ian Bicking <ianb@co...>]

On Tue, 2003-04-29 at 10:20, Frank Barknecht wrote:
> Hallo,
> Ian Bicking hat gesagt: // Ian Bicking wrote:
> 
> > Okay, it turns out I accidentally changed _cacheValues default to False,
> > instead of True, which causes SQLObject to fetch from the database for
> > every access.  Sorry about that...
> 
> Ohh, noooo! 
> 
> Sorry for crying out so loud, but now I get my old problem with
> changed values not visible in Webware again. I had hoped, that there
> had been a fix recently, but now it turns out that caching was
> disabled... I gave two several examples for this in previous mails,
> which all remained unanswerd... 

Yes, I didn't have time at that point to look into what seemed like a
difficult problem.  Then you said it was fixed, and I was happy to have
missed the problem altogether... but I'll look at it again.

  Ian

Re: [SQLObject] Temporary Objects

[Ian Bicking <ianb@co...>]

On Tue, 2003-04-29 at 11:53, Brad Bollenbach wrote:
> > That seems confusing to me.  If you're going to use database
> > transactions, why not just use them entirely?  You only seem to really
> > gain something if you can avoid them entirely (and thus make it possible
> > to add transactions to lesser databases).
> 
> To be useable with database backends that don't support transactions
> natively, and to provide a consistent interface for using them either
> way.

If we can create in-Python transactions, that'd be great... but if we
create something that's in Python, but still depends on database
transactions (and so isn't usable from MySQL), then I'm not clear what
the advantage is.

  Ian

Re: [SQLObject] Temporary Objects (I'm going in!)

[Ian Bicking <ianb@co...>]

On Tue, 2003-04-29 at 12:17, Luke Opperman wrote:
[snip]
> Now, an also seemingly functionally equivalent would be Ian's
> TempConnection idea, although I'm not sure I like the interface/look
> of needing to pass in an alternate connection for each tempobject. In
> general, I want tempobjects to be created and used just like they
> were a regular SQLObject, except that they need to be
> persisted/committed. However, I think this might be a clean
> *internal* implementation for my concept of temp objects: when an
> object becomes temp (added to a transaction, or explicitly), it just
> swaps it's connection for a TempConnection. Hmm....

TempConnection uses the same interface you'd use for database
transactions (which I haven't really tried... but in theory that's how
you'd do database transactions).  I think, at least, that both these
should have the same interface.

But when I think more about it, it's just so damn difficult... is this
really that much better than a database transaction?  I mean, maybe it's
possible to get this kind of working, but I suspect it will be a
crippled version of the functionality you'd normally have available.

> That's enough thoughts for now. I'd appreciate comments, but I think
> it just need to implement something and see how it plays..

That's probably a good idea... the issue will probably become much
clearer that way.

  Ian

Re: [SQLObject] Temporary Objects

[Ian Bicking <ianb@co...>]

On Tue, 2003-04-29 at 11:23, Nick wrote:
> The reason I think something like that is so important is for web based
> transactions (which I assume is what started this thread).  If your
> script fails for some reason, you (usually) want to roll back the entire
> transaction.  Which brings me to a question... is it possible to clear
> out the entire object cache so you can maintain a persistant connection
> over several web requests?

If you pass a connection in to the constructor, then that connection's
cache is used (not a global cache).  So when the connection goes away,
so does the cache.

  Ian

Re: [SQLObject] creating joins

[Ian Bicking <ianb@co...>]

On Tue, 2003-04-29 at 12:05, Luke Opperman wrote:
> Automatically creating the joins might conceivably solve this,
> although I'm pretty sure I can come up with with the same import
> circular dependencies whether it is at module import time (solved by
> strings) or object instantiation time (although classRegistry can
> probably be used to save this: you'll never import that FK'ed class
> if it's already in the classRegistry...) Hmm.

I think this should be okay, so long as *somebody* imports all the
classes.  You can't use one class until the other class has been setup
(because of the fixup step).

The the module that uses one class doesn't need to import all of them --
so long as they all get imported when the application gets started up
(at least, that's how it should work).

  Ian

Re: [SQLObject] creating joins

[Luke Opperman <luke@me...>]

> from MyDB import Person
> > peep = Person(1)
> > for cat in peep.cats: # error because Cat has not been imported.
> >     print cat
>
> Problem solved, because Cat has been imported through the package.
>
> Nick

Ugh. Certainly this works.. I'm initially shying away from doing full
imports like this for performance reasons, but I suppose it's a
one-time cost per interpreter. Also, I could create functional
partitions of some of these large databases where this concerns me
(from MyDB.ProductStuff import ....). Hmm, ok.

(The other part that bothers me is maintaining the list of DB Objects
in both the directory and in the __init__, but I suppose I can solve
that with an "import everything in this dir" script I have lying
around, in __init__.py instead of explicit imports.)

- Luke

Re: [SQLObject] creating joins

[Nick <nick@dd...>]

On Tue, 2003-04-29 at 14:01, Luke Opperman wrote:
> ------ Person.py
> class Person(SQLObject):
>     _columns = [StringCol(\'name\')]
>     _joins = [MultipleJoin(\'Cat\')]
> 
> ------ Cat.py
> class Cat(SQLObject):
>     _columns = [StringCol('name'), KeyCol('person_id',
> foreignKey='Person')]

Stick these in a package, MyDB with the lines in __init__:
from Person import Person
from Cat import Cat

> from Person import Person
to:
from MyDB import Person
> peep = Person(1)
> for cat in peep.cats: # error because Cat has not been imported.
>     print cat

Problem solved, because Cat has been imported through the package.

Nick

Re: [SQLObject] creating joins

[Luke Opperman <luke@me...>]

> As long as you make your classes part of a module, I don't see this
> being a problem, since the class names are all stored up when you
> define
> the class, which will happen whether you import 1, 2, or all the
> classes
> in a module.

 I'm not quite following: Here's the full example that with
SQLObject's current classRegistry/string-defined FKs will lead to
third-part classes needing to import things they don't directly know
about:

------ Person.py
class Person(SQLObject):
    _columns = [StringCol(\'name\')]
    _joins = [MultipleJoin(\'Cat\')]

------ Cat.py
class Cat(SQLObject):
    _columns = [StringCol('name'), KeyCol('person_id',
foreignKey='Person')]

------ MyApp.py

from Person import Person
peep = Person(1)
for cat in peep.cats: # error because Cat has not been imported.
    print cat

------ END

I really dislike having to import Cat in MyApp.py (which solves the
problem), since it's not explicitly used, and there's no reason MyApp
should even know where to import it from. Of course, neither does
SQLObject, so I don't have a solution.

- Luke



--
 i find your contempt for naked feet curious.

Re: [SQLObject] creating joins

[Nick <nick@dd...>]

On Tue, 2003-04-29 at 12:05, Luke Opperman wrote:
> This is not SQLObject specific, but I guess I've never understood the
> "tablename_id" argument for making joins cleaner. in SQL:
> 
> ".... ON person.id = other.person_id...." vs
> ".... ON person.person_id = other.person_id"

try USING (person_id) instead.

> So yeah. Not sure what my point is,
> except I'll assume the new Style object ought to cover this, and
> that's that.

I looked at that, and I think it's a step in the right direction, but it
needs to be fleshed out a little more I think.

> The problem (that i've experienced) is circular *import* dependencies.
> I would agree, strings seem to solve this. With caveats mentioned in
> another mail about joined/FK'ed classes possibly needing to be
> imported by a third-party class that doesn't want to have to know
> that Person has a Phone object (and hence where to import Phone
> from), just that Person.phone returns a Phone object.

As long as you make your classes part of a module, I don't see this
being a problem, since the class names are all stored up when you define
the class, which will happen whether you import 1, 2, or all the classes
in a module.

Nick

Re: [SQLObject] Temporary Objects (I'm going in!)

[Luke Opperman <luke@me...>]

Ok, here's my current thoughts on TempObjects/Transactions:

First, whether the external interface is a Transaction object that you
add objects to or not (Brad's suggestion):

> from Transaction import Transaction
> from invoicing import Client
> t = Transaction()
> c = Client(1)
> t.insertObject(c)
> c.address1 = "100 New Street"
> t.saveChanges()

... it seems to me the changes to SQLObject are still the same as my
initial suggestions (Object.temp(), Object.persist()): ie,
t.insertObject(c) needs to call something equivalent to c.temp() so
that it no longer does updates, and t.saveChanges() needs to call
c.persist().

Also, i've seen very little suggestion for an alternate way to make a
Temp *new* object within a Transaction. You need to a way to specify
"create a temp object" as opposed to "create an object then add it to
this Transaction". In general new objects make things much messier,
with the issue of fake IDs needing to updated when
persisted/committed. Again, this is regardless of whether the
metaphor is transactions or tempobjects.

I am in complete agreement with Ian over "Stores" vs "Objects". I
don't want stores; this biases my thoughts on Transaction objects, as
they are explicit temporary stores.

Now, an also seemingly functionally equivalent would be Ian's
TempConnection idea, although I'm not sure I like the interface/look
of needing to pass in an alternate connection for each tempobject. In
general, I want tempobjects to be created and used just like they
were a regular SQLObject, except that they need to be
persisted/committed. However, I think this might be a clean
*internal* implementation for my concept of temp objects: when an
object becomes temp (added to a transaction, or explicitly), it just
swaps it's connection for a TempConnection. Hmm....

So here's my plan of action: implement .temp() and .persist() (I'll
call it .commit() if it makes people happier :) within SQLObject.
This will allow the concept of transactions to be implemented
completely independent of database support, if so desired.

Semantics:

1. temporary versions of an SQLObject will not be visible to any other
connection (ie, unless you have a python-side reference to the
object, it doesn't exist.)

2. temporary versions of an SQLObject can reference either other temp
objects or real objects. Joins queried on a temp object will return
possibly a mix of real and fake objects. The question here is, do we
return temp versions of the real objects? tempPerson.employees, do
something with the employee objects, presumably (from a transaction
perspective) those changes are only saved if the tempPerson is
saved... getting messy....

3. this leads to: real objects can never reference a temp object
without becoming temp themselves. I think this is the way to go, but
it leads to the final mess

4. does persisting any object persist all temp objects it touches?
This makes sense from the example above: create a tempPerson, start
adding temp employees to it, I'd like to simply persist tempPerson
and have it handle the rest. but does it work the other way (persist
one of the employees, and the whole tree/graph is persisted)? Should
there be an alternate way to persist just one? (In the context of a
transaction this becomes particularily important: when do things I
didn't explicitly add to the transaction but that are changed via
join/FK get persisted?)

That's enough thoughts for now. I'd appreciate comments, but I think
it just need to implement something and see how it plays..

- Luke

Re: [SQLObject] creating joins

[Luke Opperman <luke@me...>]

Quoting Nick <nick@...>:

> Most non-MySQL schema designers (in my experience, anyway) tend to
> name
> columns that are the same data the same in every table they appear
> in.
> That means, if person_id is in Address, then the key in Person is
> person_id as well.  This uncomplicates JOINS in a major way, as
> well as
> maintains consistency in your data dictionary.  Therefore, idName
> for a
> table is almost always the same name as the foreign key in another
> table, except where you have 2 in the same table.

I'll have to say this is a style issue, not so much a MySQL issue. :)
A quick poll amongst developers here (all Postgres or MSSQL people)
shows 50/50 between naming id columns "table_id" or just "id". I'm
definitely an "id" person. :) (We've got one guy who routinely names
every column in a table "tablename_colname". This makes as much sense
to me as "tablename_id", so yeah..

This is not SQLObject specific, but I guess I've never understood the
"tablename_id" argument for making joins cleaner. in SQL:

".... ON person.id = other.person_id...." vs
".... ON person.person_id = other.person_id"

always seemed to be duplicating the person metadata to me, and makes
me forget which is primary. whereas there's no doubt in my mind
whether something is a foreignKey or primaryKey if it's just "id".

Everyone here names foreignkeys "table_id" except in rare cases (two
that come to mind: foreignKey to own table (usually called
"parent_table_id" by myself) and multiple foreignKeys to another
table, called arbitrary things). So yeah. Not sure what my point is,
except I'll assume the new Style object ought to cover this, and
that's that.

> I was originally
> using class references for my foreign key definitions and ran into
> the circular dependecy probelms, but there were ways around that,
> too. The programmer *could* get into an infinite loop of they follow
> circular references, but that's their problem :-P
> That can happen now.

The problem (that i've experienced) is circular *import* dependencies.
I would agree, strings seem to solve this. With caveats mentioned in
another mail about joined/FK'ed classes possibly needing to be
imported by a third-party class that doesn't want to have to know
that Person has a Phone object (and hence where to import Phone
from), just that Person.phone returns a Phone object.

Automatically creating the joins might conceivably solve this,
although I'm pretty sure I can come up with with the same import
circular dependencies whether it is at module import time (solved by
strings) or object instantiation time (although classRegistry can
probably be used to save this: you'll never import that FK'ed class
if it's already in the classRegistry...) Hmm.

- Luke

Re: [SQLObject] creating joins

[David M. Cook <dave@da...>]

On Tue, Apr 29, 2003 at 11:36:13AM -0500, Nick wrote:

> Most non-MySQL schema designers (in my experience, anyway) tend to name
> columns that are the same data the same in every table they appear in. 
> That means, if person_id is in Address, then the key in Person is
> person_id as well.  This uncomplicates JOINS in a major way, 

It's called a natural join.  In postgres if you name your columns this way
you can do

select * from person join address

and it automatically does the join on the common column.

Dave Cook

Re: [SQLObject] Temporary Objects

[Brad Bollenbach <brad@bb...>]

On 04/29/03 04:13, Ian Bicking wrote:
> On Sat, 2003-04-26 at 12:48, Nick wrote:
> > I'm new to the project, but I've been working on a project that is so
> > similar for the past 2 years (since Python 2.2a1) that it's fairly
> > spooky.  I mean, the classes are even named and organized almost exactly
> > the same. 
> 
> It's because I'm inside your head!
> 
> > On Sat, 2003-04-26 at 11:01, Brad Bollenbach wrote:
> > > On 04/25/03 16:56, Luke Opperman wrote:
> > > > Hey all -
> > > > 
> > > > Ok, I'm going to present a possibly controversial idea. I'd like to
> > > > propose the concept of 'temporary' objects, for use in cases where
> > > > you want to save values (say from a series of web forms) in
> > > > SQLObject-constrained containers (columns, relationships), but don't
> > > > want to persist to the database until the user finishes the process.
> > >
> > > Your motives are well-founded, but I don't entirely agree with your
> > > choice of metaphor.
> > > 
> > > What you're really looking for is transactions.
> > 
> > Right on, that's what I did with my code.  What I did was never run any
> > queries and keep a pool of "dirty" object to commit when a commit() call
> > was made, and rollback() would reset to original values.  new()s and
> > delete(s) obviously can't be done this way or it will screw up
> > subsequent queries, so the DBIs transaction model was used in addition
> > to the pool.  It worked really well.
> 
> That seems confusing to me.  If you're going to use database
> transactions, why not just use them entirely?  You only seem to really
> gain something if you can avoid them entirely (and thus make it possible
> to add transactions to lesser databases).

To be useable with database backends that don't support transactions
natively, and to provide a consistent interface for using them either
way.

-- 
Brad Bollenbach
BBnet.ca

Re: [SQLObject] creating joins

[Nick <nick@dd...>]

On Tue, 2003-04-29 at 03:23, Ian Bicking wrote:
> On Mon, 2003-04-28 at 14:49, Nick wrote:
> > I was wondering, when instantiating a Join object, shouldn't joinColumn
> > default to idName of the class instead of always making up the id column
> > with the Style?
>
> Say you have a Person and an Address (one to many), Address has a
> person_id column, but Person has an id column, and they go together. 
> How does idName come into it? 

Most non-MySQL schema designers (in my experience, anyway) tend to name
columns that are the same data the same in every table they appear in. 
That means, if person_id is in Address, then the key in Person is
person_id as well.  This uncomplicates JOINS in a major way, as well as
maintains consistency in your data dictionary.  Therefore, idName for a
table is almost always the same name as the foreign key in another
table, except where you have 2 in the same table.

> I mean, ideally when you define the foreign key column in Address it
> would automatically create the join, but the fear of circular
> dependencies has scared me off from doing that so far :)

Actually, I did do it that way, and you're not going to get any circular
dependencies as long as you're using strings to resolve your class names
late.  Perhaps you could explain some of your fears to me, because I
still don't fully grok every aspect of the code yet :)  I was originally
using class references for my foreign key definitions and ran into the
circular dependecy probelms, but there were ways around that, too.  The
programmer *could* get into an infinite loop of they follow circular
references, but that's their problem :-P  That can happen now.

Nick

Re: [SQLObject] Temporary Objects

[Nick <nick@dd...>]

On Tue, 2003-04-29 at 04:13, Ian Bicking wrote:
> That seems confusing to me.  If you're going to use database
> transactions, why not just use them entirely?  You only seem to really
> gain something if you can avoid them entirely (and thus make it possible
> to add transactions to lesser databases).

The point there is so that you can roll back an individual object rather
than the entire transaction, if desired.  Plus, you're not performing
any communication to the database, until it's completely necessary. 
Important for web services and applications, where you can send your
content so the user can see response while you do all your db writes at
the end.

> You could keep everything in memory if you could pre-allocate IDs for
> the new objects, which is kind of interesting -- if you throw the object
> away, you've only lost some numbers, and numbers are cheap (103493,
> 10943, 23943 -- see, so cheap I can throw them away!).  Stupid MySQL
> doesn't even have sequences, though, which makes this possibility
> difficult.
> 
> I'm okay with deletes not being transactional (outside of using database
> transactions).  I'm not sure I see as much of a use case for that

The reason you would actually want to make the queries inside a
transaction for inserts and deletes is because you would want the
results to show up in subsequent selects.  On inserts, you certainly
wouldn't see new rows if they weren't sent to the db in a transactions. 
Now that I think about it, you *can* manage deletes I suppose by
checking the cache to see if the row had been deleted and then neglect
to return it in the resulting list.

> > but since there's a fairly complete caching mechanism it's only a matter
> > of marking objects as dirty when modifications are made and then
> > checking the cache and running an update() on every dirty object.
> 
> Certainly a database connection could be lazy and not do the update when
> it was asked (but just remember to do it later).

The reason I think something like that is so important is for web based
transactions (which I assume is what started this thread).  If your
script fails for some reason, you (usually) want to roll back the entire
transaction.  Which brings me to a question... is it possible to clear
out the entire object cache so you can maintain a persistant connection
over several web requests?

Nick

Re: [SQLObject] possible problem with lazy loading still happening

[Frank Barknecht <fbar@fo...>]

Hallo,
Ian Bicking hat gesagt: // Ian Bicking wrote:

> Okay, it turns out I accidentally changed _cacheValues default to False,
> instead of True, which causes SQLObject to fetch from the database for
> every access.  Sorry about that...

Ohh, noooo! 

Sorry for crying out so loud, but now I get my old problem with
changed values not visible in Webware again. I had hoped, that there
had been a fix recently, but now it turns out that caching was
disabled... I gave two several examples for this in previous mails,
which all remained unanswerd... 

How can I get rid of that (preferrable without disabling caching)? 

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] Deleting items

[Brad Bollenbach <brad@bb...>]

On 04/29/03 14:18, Chris Parsons wrote:
> Hi
> 
> Sorry:
> - if this is a basic question
> - if I've already posted the same question, but my connection 
> dropped while I sent it and I can't find it in the archive
> 
> Anyway, when I try and delete an item from my table I get
> 
> Traceback (most recent call last): File "/var/www/cgi-bin/timesheet.py", line 267, in ? e.destroy() File
> "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 831, in destroy self._connection.cache.purge(self.id) AttributeError: 'CacheSet'
> object has no attribute 'purge' 

To fix this problem, upgrade your version of SQLObject.

In future, please limit your code snippets to a minimal example that
illustrates your problem.

Hope that helps.

-- 
Brad Bollenbach
BBnet.ca

[SQLObject] Deleting items

[Chris Parsons <chrisp@sp...>]

Hi

Sorry:
- if this is a basic question
- if I've already posted the same question, but my connection 
dropped while I sent it and I can't find it in the archive

Anyway, when I try and delete an item from my table I get

Traceback (most recent call last): File "/var/www/cgi-bin/timesheet.py", line 267, in ? e.destroy() File
"/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 831, in destroy self._connection.cache.purge(self.id) AttributeError: 'CacheSet'
object has no attribute 'purge' 

Although the item is deleted from the table.

The code that I'm using is

    if form['action'].value == "delEntry":
      if form.has_key("entryID"):
        e = Entry(int(form['entryID'].value))
        e.destroy()

int(form['entryID'].value) is definitely a valid integer for 
the table.

Using MySQL-python V0.9.2
Using MySQL V4.0.12
Using Python V2.2

Full code and table structure below

Thank you!

Chris P

DROP DATABASE IF EXISTS speak_timesheet;
CREATE DATABASE speak_timesheet;
Use speak_timesheet;
CREATE TABLE user (
    id      SMALLINT NOT NULL AUTO_INCREMENT,
    first_name   CHAR(20)        NOT NULL,
    last_name    CHAR(30)        NOT NULL,
    login_name   CHAR(20)        NOT NULL,
    user_state   ENUM("Active", "Inactive") DEFAULT "Active" NOT NULL,
    normal_week_hours DOUBLE(5,2) DEFAULT 37.5 NOT NULL,
    toil_remaining SMALLINT DEFAULT 0 NOT NULL,
    toil_expire SMALLINT DEFAULT 0 NOT NULL,
    holiday_year_start DATE NOT NULL,
    holidays_per_year SMALLINT DEFAULT 25 NOT NULL,
    holidays_used SMALLINT DEFAULT 0 NOT NULL,
    PRIMARY KEY (id),
    UNIQUE INDEX (login_name),
);
LOAD DATA INFILE 'c:/mysql/tsload/users.txt' INTO TABLE user;

CREATE TABLE week (
    id     INT     NOT NULL AUTO_INCREMENT,
    start_date  DATE  NOT NULL,
    week_state  ENUM("Open", "Closed", "Archive") DEFAULT "Open" NOT NULL,
    user_id   SMALLINT  NOT NULL,
    work_hours  DOUBLE(5,2) DEFAULT 0 NOT NULL,
    target_hours DOUBLE(5,2) NOT NULL,
    holiday_used DOUBLE(5,2) DEFAULT 0 NOT NULL,
    PRIMARY KEY (id),
);

CREATE TABLE entry (
    id  INT NOT NULL AUTO_INCREMENT,
    week_id  INT NOT NULL,
    dow      INT NOT NULL,
    type     ENUM("Normal", "Holiday", "Sick", "Bank Holiday") DEFAULT "Normal" NOT NULL,
    duration DOUBLE(5,2) NOT NULL,
    PRIMARY KEY (id),
);

#!/usr/bin/python
from SQLObject import *
import sys
import os
import string
import time
import cgi
import Cookie
import re

######## Utility Functions #############

__connection__ = MySQLConnection('localhost', "speak_timesheet","cgiuser", "")

dayFromName = { "Mon" : 0, "Tue" : 1, "Wed" : 2, "Thu" : 3, "Fri" : 4, "Sat" : 5 , "Sun" : 6 }
daysOfWeek = ("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")

def firstDayOfThisWeek():
  timeNow = time.time()
  tim = time.localtime(timeNow)
  timeNow = timeNow - (tim[6] * 24 * 3600) - (tim[3] * 3600) - (tim[4] *  60) - tim[5]
  return time.localtime(timeNow)

def strptime_cjp(tstr):
  y, m, d = string.split(tstr, '-')
  return (int(y), int(m), int(d), 0, 0, 0, 0, 0, -1)

def timeToFloat(timeStr):
  try:
    ret = re.match('([0-2]*[0-9]):([0-5][0-9])', timeStr)
    retValue = round(float(float(ret.group(1))) + float(float(ret.group(2))/60),1)
  except AttributeError:
    pass
  else:
    return retValue
  try:
    ret = re.match('([0-2]*[0-9])', timeStr)
    retValue = round(float(int(ret.group(0))),1)
  except AttributeError:
    pass
  else:
    return retValue
  return None



######## DB Classes ####################
  
class User(SQLObject):
  _fromDatabase = True
  _columns = [Col('loginName',alternateID=True)]
  _joins = [MultipleJoin('Week')]
  
  def updateWeek(self):
    if self.userState != 'Active':
      return
    # add up holiday from this holiday year and total
    # add up toil from last 6 weeks before start of this week
    startThisWeek = time.mktime(firstDayOfThisWeek())
    seventyDaysAgo = startThisWeek - (70 * 24 * 3600)
    fiftySixDaysAgo  = startThisWeek - (56 * 24 * 3600)
    toilRemaining = 0
    toilExpire = 0
    for w in self.weeks:
      if w.weekState == "Closed":
        startWeek = time.mktime(strptime_cjp(w.startDate))
        if startWeek >= seventyDaysAgo:
          toilRemaining = toilRemaining + w.workHours - w.targetHours
          if startWeek < fiftySixDaysAgo:
            toilExpire = toilExpire + w.workHours - w.targetHours
    self.toilRemaining = int(toilRemaining)
    self.toilExpire = int(toilExpire)
        
      

  def userPrint(self):
    print '<table width=550 col=4 BGCOLOR="#ffff33">'
    print '<tr><td>Name: </td><td><b> ' + self.firstName + ' ' + self.lastName + \
      '</b></td><td> &nbsp</td><td>&nbsp;</td></tr>'
    print '<tr><td>TOIL remaining (hours):</td><td><b>'+str(self.toilRemaining)+'</b></td><td>' + \
      'Expire next 2 wks:</td><td><b> '
    tr = self.toilExpire
    if tr > 0:
      print '<font color="#990000">' + str(tr) + '</font>'
    else:
      print str(tr)
    print '</b></td></tr>'
    print '<tr><td>Holiday year start:</td><td><b>' + self.holidayYearStart + '</b></td>' + \
    '<td>Normal hours/week:</td><td><b>' + str(self.normalWeekHours)  + '</tr>'
    print '<tr><td>Holiday remaining: </td><td><b>' + \
      str(self.holidaysPerYear - self.holidaysUsed) + '</b></td>' + \
      '<td>Holiday per year</td><td><b>' + str(self.holidaysPerYear) + \
       '</b></td></tr></table>'

class Week(SQLObject):
  _fromDatabase = True
  _columns = [Col('userID',foreignKey='User')]
  _joins = [MultipleJoin('Entry')]
  _defaultOrder = "start_date DESC"
  
  def closeWeek(self):
    if self.weekState != 'Closed':
      self.weekState = 'Closed'
      self.workHours = 0
      self.holidayUsed = 0
      # For each entry in week:
      #  Add up hours worked if counted as worked
      #  Add to holiday if holiday
      # Then call person close week to change totals
      for e in self.entrys:
        if e.type == 'Normal' or e.type == 'Sick' or e.type == 'Bank Holiday':
          self.workHours = self.workHours + e.duration
        elif e.type == 'Holiday':
          if e.duration > 3.75:
            holiday = 1
          elif e.duration > 0:
            holiday = 0.5
          else:
            holiday = 0
          self.holidayUsed = self.holidayUsed + holiday
      p = self.user
      p.updateWeek()

  def reopenWeek(self):
    if self.weekState == 'Closed':
      self.weekState = 'Open'
      p = self.user
      p.updateWeek()

  def printEntryTable(self):
    if self.weekState == 'Open':
      print '<table valign="center" width=550 bgcolor=#66cc00 bordercolor="#000000" border=0>'
      print '<form method="post" action="/cgi-bin/timesheet.py">'
      print '<tr><td>Week begin: <b>' + self.startDate + \
        '</b></td><td><b>' + self.weekState + '</b></td><td>' +  \
        'Target: ' + \
        '<INPUT TYPE="HIDDEN" NAME="action" VALUE="closeWeek"> \
        <INPUT TYPE="TEXT" NAME="targetHours" SIZE="3" VALUE="' + \
        str(self.user.normalWeekHours) + '">' + \
        '<INPUT TYPE="HIDDEN" NAME="weekId" VALUE="' + str(self.id) + '">' + \
        '&nbsp;&nbsp<INPUT TYPE="SUBMIT" VALUE = "Close"></td></tr></form>' + \
        '<tr><td COLSPAN="3">' +  \
        '<table align="center" width=450 style="border:0px;" bgcolor=#66cc00 border=0>' + \
        '<tr><th>Day</th><th>Type</th><th>Hours</th><th></th></tr>'
      lastDay = 6
      for e in Entry.select(Entry.q.weekId == self.id, orderBy=("entry.dow","entry.id")):
        print '<tr><td>' + daysOfWeek[e.dow] + '</td><td>' + e.type + '</td><td>' + str(round(e.duration,2)) + \
        '</td><td> <a href="/cgi-bin/timesheet.py?action=delEntry&entryID=' + str(e.id) + '">Delete</a></td></tr>'
        lastDay = e.dow
      print '<tr><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td></tr>'
      # First end line has 'hours' input box
      print  ' <tr VALIGN="TOP"> <td><FORM METHOD="POST" ACTION="/cgi-bin/timesheet.py">  \
      <INPUT TYPE="HIDDEN" NAME="action" VALUE="setEntry">  \
      <INPUT TYPE="HIDDEN" NAME="weekId" VALUE="' + str(self.id) + '"> \
      <SELECT NAME="weekday" SIZE="1">'
      for i in range(7):
        if i == (lastDay + 1) % 7:
          print '<OPTION SELECTED> ' + daysOfWeek[i]
        else:
          print '<OPTION> ' + daysOfWeek[i]
      print '</SELECT></td><td> \
      <SELECT NAME="type" SIZE="1"> \
      <OPTION SELECTED> Normal <OPTION> Holiday <OPTION> Sick </SELECT> </td><td> \
      Hours: <INPUT TYPE="TEXT" NAME="hours" VALUE="7.5" SIZE="2"></td><td> \
      <INPUT VALUE="Add" TYPE="submit"></FORM></td></tr>'
      # Second end line has 'start/finish' input box
      print '<FORM METHOD="POST" ACTION="/cgi-bin/timesheet.py">  \
      <INPUT TYPE="HIDDEN" NAME="action" VALUE="setEntry">  \
      <INPUT TYPE="HIDDEN" NAME="weekId" VALUE="' + str(self.id) + '">  \
      <tr VALIGN="TOP"> <td>\
      <SELECT NAME="weekday" SIZE="1">'
      for i in range(7):
        if i == (lastDay + 1) % 7:
          print '<OPTION SELECTED> ' + daysOfWeek[i]
        else:
          print '<OPTION> ' + daysOfWeek[i]
      print '</SELECT></td><td> \
      <SELECT NAME="type" SIZE="1"> \
      <OPTION SELECTED> Normal <OPTION> Holiday <OPTION> Sick </SELECT> </td><td> \
      Start:&nbsp;<INPUT TYPE="TEXT" NAME="startTime" VALUE="09:30" SIZE="4"> \
      &nbsp;End:&nbsp;<INPUT TYPE="TEXT" NAME="endTime" VALUE="17:30" SIZE="4"></td><td>  \
      <INPUT VALUE="Add" TYPE="submit"><br><font size="-2"><center>24 hour clock please<center></font></FORM></td></tr></table></tr>'
      print '</table>'
    elif self.weekState == 'Closed':
      print '<table width=550 style="border:0px;" bgcolor=#ff6600 border=0>'
      print '<tr valign=top><td>Week begin: <b>' + self.startDate + \
        '</b></td><td> <b>' + self.weekState + ' </b><a href="/cgi-bin/timesheet.py?action=reopenWeek&weekId='+str(self.id) + '"><font size="-1">(reopen)</font></td><td>' +  \
        'Worked: <b>' + str(round(self.workHours,1)) + '</b></td><td>Over/-under: <b>' + \
        str(round(self.workHours-self.targetHours,1)) + '</b></td></tr>'
      print '</table>'

class Entry(SQLObject):
  _fromDatabase = True
  _columns = [Col('weekID',foreignKey='Week')]

######## Page Code #####################

C = Cookie.SimpleCookie()
form = cgi.FieldStorage()
if form.has_key("tsUser"):
  tsUser = form["tsUser"].value
  C["tsUser"] = tsUser
  C["tsUser"]["expires"] = 1000000
  print C
else:
  try:
    C.load(os.environ["HTTP_COOKIE"])
  except:
    tsUser = ""
    pass
  else:
    tsUser = C["tsUser"].value
print "Cache-control: no-cache"
print "Content-type: text/html"
print
print '<head><link rel="stylesheet" type="text/css" href="/office.css" ></head><body><font size="-2"><a style="color:#000000" href="/cgi-bin/timesheet.py">refresh</a>&nbsp;&nbsp;<a style="color:#000000" href="http://192.168.69.147/cgi-bin/webmin">home</a></font><center>';
sys.stderr = sys.stdout
if tsUser=="":
  print """
  <FORM METHOD="POST" ACTION="/cgi-bin/timesheet.py">
 Enter name: <INPUT TYPE="TEXT" NAME="tsUser" VALUE="">
 <INPUT TYPE="submit" Value="Login">
 </FORM>
  """
else:
  if form.has_key("action"):
#### setEntry
    dur = 0
    if form['action'].value == "setEntry":
      if form.has_key("weekId") and form.has_key("weekday") \
      and form.has_key("type") and (form.has_key("hours") or \
      (form.has_key("startTime") and form.has_key("endTime"))):
        if form.has_key("hours"):
          dur = float(form['hours'].value)
        else:
          startHours = timeToFloat(form['startTime'].value)
          endHours = timeToFloat(form['endTime'].value)
          if startHours is None or endHours is None:
            print 'Invalid time format, sorry'
            dur = 0
          else:
            if endHours < startHours:
              # assume went past midnight
              endHours = endHours + 24;
              print 'Warning: either you worked past midnight, or you didn\'t use the 24 hour clock just now.  If the later, the entry you just added (%s, %s) is wrong, delete it and try again!' % (form['weekday'].value, str(endHours-startHours))
            dur = endHours - startHours
        if dur != 0:
          e = Entry.new(weekID = int(form['weekId'].value), \
          dow = dayFromName[form['weekday'].value], \
          type = form['type'].value, \
          duration = dur )
#### closeWeek
    if form['action'].value == "closeWeek":
      if form.has_key("targetHours") and form.has_key("weekId"):
        w = Week(int(form['weekId'].value))
        w.targetHours = float(form['targetHours'].value)
        w.closeWeek()
#### reopenWeek
    if form['action'].value == "reopenWeek":
      if form.has_key("weekId"):
        w = Week(int(form['weekId'].value))
        w.reopenWeek()
#### delEntry
    if form['action'].value == "delEntry":
      if form.has_key("entryID"):
        e = Entry(int(form['entryID'].value))
        e.destroy()

  p = User.byLoginName(tsUser)
  p.userPrint()
  # If we don't already have a week entry for this week, make one
  thisWeekAsString = time.strftime("%Y-%m-%d", firstDayOfThisWeek())
  if len(Week.select(AND(Week.q.startDate == thisWeekAsString, Week.q.userID == p.id))) == 0:
    newWeek = Week.new(startDate=time.strftime("%Y-%m-%d",firstDayOfThisWeek),weekState='Open',userID=p.id,targetHours=p.normalWeekHours)
  for w in p.weeks:
    w.printEntryTable()
  print '</table>'
print '</center></body></html>'

Re: [SQLObject] possible problem with lazy loading still happening

[Ian Bicking <ianb@co...>]

On Fri, 2003-04-25 at 00:00, Peter Wilkinson wrote:
> Hi,
> I've been doing some work with SQLObject with debug turned on and 
> noticed a lot more querying of the database going on than I expected.

Okay, it turns out I accidentally changed _cacheValues default to False,
instead of True, which causes SQLObject to fetch from the database for
every access.  Sorry about that...

  Ian

Re: [SQLObject] Preparing for 0.4

[Ian Bicking <ianb@co...>]

On Tue, 2003-04-22 at 23:48, Peter Wilkinson wrote:
[snip]
> The autoCommit=1 and the pooling conditional is just copied directly 
> from PostgresConnection, SQLite supports transactions so I presume that 
> for the same reasons this is needed.

Okay, applied to CVS.

> I changed how it deals with getting a connection so that code like the 
> following will work without needing to turn the results into lists, 
> really any code where you have database access inside loops like this 
> not just deletes.
> 
> users = User.select('all')
> for i in users:
>      for ul in u.userLocations:
>          ul.destroySelf()
> 
> SQLite + Python only supports one connection at a time. In my 
> understanding the select loop should be able to run in a different 
> connection to the delete as SQLite only locks when changing the db. I'm 
> trying to find out exactly where the oddness is happening.
> 
> Can you see any problems with doing this?

Yes, that seems to work.  I don't know enough about the SQLite and
concurrency to really know, but if it works then its fine with me.

I wonder if it works okay with transactions...

  Ian

Re: [SQLObject] Preparing for 0.4

[Ian Bicking <ianb@co...>]

Yes, I believe Bud pointed this out to me.

It's annoying, because TIMESTAMP in MySQL is funny, but Postgres'
TIMESTAMP is like MySQL's DATETIME.  On a related note, this kind of
makes me want to stick with StringCol (vs. TextCol)...

On Wed, 2003-04-23 at 07:58, David M. Cook wrote:
> Ah, so it does do something for a lot of people (e.g. RH 8 has PG 7.2 AFAIK),
> and it looks like it does the right thing in that case:
> 
> class DateTimeCol(Col):
> 
>     # 3-03 @@: provide constraints; right now we let the database
>     # do any parsing and checking.  And DATE and TIME?
> 
>     def _mysqlType(self):
>         return 'DATETIME'
> 
>     def _postgresType(self):
>         return 'TIMESTAMP'
> 	
> Dave
> 
> 
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> sqlobject-discuss mailing list
> sqlobject-discuss@...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: [SQLObject] possible problem with lazy loading still happening

[Ian Bicking <ianb@co...>]

On Fri, 2003-04-25 at 00:00, Peter Wilkinson wrote:
> Hi,
> I've been doing some work with SQLObject with debug turned on and 
> noticed a lot more querying of the database going on than I expected.

First things first, what version are you using?

> Also I've been looking at adding support for an Interbase/Firebird 
> connection, has anyone else done any work on this?

Someone mentioned it some time ago, but nothing came of it.  Basic
support should be quite easy to add.  Introspection is a bit more
difficult, but you can leave that out to start with.  If you look at
PostgresConnection that should give you an idea.  Ignore addColumn,
delColumn, columnsFromSchema, and guessClass to begin with.

  Ian

Re: [SQLObject] exploring SQLObject as a bridge

[Ian Bicking <ianb@co...>]

On Fri, 2003-04-25 at 12:10, Donnal Walter wrote:
> I am sure I will have other question of a more theoretical nature,
> but my first question is merely to ask for recommendations for a
> database backend. Since I am only exploring for now, I would am
> interested in the easiest to install on my local machine. I had
> planned on using Gadfly, for example, but I don't see it on the
> list of databases supported by SQLObject. SQLite seems attractive,
> but if I understand the instructions correctly, I would have to
> compile it myself, and I don't have a C compiler. (Or is there a
> Windows binary available?) What RDBMS would you recommend?

I'd recommend SQLite -- I've only been using it very recently, but it
seems quite nice, and seems like it fits your environment.  If you
really want minimal dependencies, DBMConnection will do that for you --
all the dependencies are included in the standard library.

  Ian