sqlalchemy and threadlocal docs on pylons tutorial

As a new Pylons user who knows nothing about databases, I
agree that
it's slightly confusing. If threadlocal or sessioncontext
is the
recommended usage pattern, then it's fine to have it. But
it would be
good to have a little note, maybe a footnote, in the
tutorial that
explains what it does, and how this situation differs from
the
SQLAlchemy tutorial.

Also, I spent a bit of time trying to figure out how to get
my project
to echo debugging SQL like in the SQLAlchemy tutorial.
Perhaps this
should also be included as a footnote in the Pylons
tutorial:

        model.meta.connect(
           
request.environ['paste.config']['app_conf']['dsn'],
            echo=True
        )

Finally, it would be nice for people new to databases to
have a brief
note about primary keys. Seeing the QuickWiki tutorial use
String(40)
as a primary key led me to believe that I could use
fixed-length
strings as primary keys, and I also thought that it was
somehow good
for the primary key to be a "real" data field in
the table. This worked
allright for awhile but later on I faced a host of problems
from
cryptic errors (MySQL, unlike SQLite, doesn't like
fixed-length unicode
strings as primary keys) to mysteriously disappearing data
(if your
primary key in table A is partially composed of a foreign
key to a row
in table B which is newly created, you may have to call
objectstore.flush() before creating the entry in table A).

Eventually I figured out that the easiest thing to do is
just to make
an Integer "id" column in each table and make
that the primary key.

I'm not advocating that a detailed introduction to
databases be added
to the tutorial. But it might be nice to mention very
briefly that if
you don't know what a primary key is, it's a unique ID for
each row in
a database table, and furthermore the easiest way to deal
with them is
to add an extra column to each table of type Integer, and
make that the
primary key (unless this advice is in error; as I noted, I
don't know
anything about databases).


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/pylons-discuss
-~----------~----~----~----~------~----~------~--~---

On Tue, 2006-09-19 at 19:45 -0700, Bayle wrote:
> I'm not advocating that a detailed introduction to
databases be added
> to the tutorial. But it might be nice to mention very
briefly that if
> you don't know what a primary key is, it's a unique
ID for each row in
> a database table, and furthermore the easiest way to
deal with them is
> to add an extra column to each table of type Integer,
and make that the
> primary key (unless this advice is in error; as I
noted, I don't know
> anything about databases).

Many database professionals would argue against such advice.
 Although a
primary key is supposed to be a unique ID for a given
database "entity",
it is generally preferable to use "natural" or
"standard" IDs as primary
keys.  Since the page title in the wiki is unique it's a
"natural"
choice.  "Standard" IDs include things like
country, currency and
language codes, SSN or other government-dictated IDs, ISBNs,
UPCs, etc.
The fact that MySQL can't handle Unicode strings as primary
keys is more
a reflection on the implementation.  Nevertheless, because
natural
identifiers are not sufficiently unique (personal names),
too lengthy (a
complete street address) or otherwise cumbersome, the
practice of using
sequentially-assigned integers is quite common, although
technically
they are surrogate primary keys (see
http://en.
wikipedia.org/wiki/Surrogate_key).

Joe


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/pylons-discuss
-~----------~----~----~----~------~----~------~--~---

Joe wrote:
> <snip>
> it is generally preferable to use "natural"
or "standard" IDs as primary
> keys.  Since the page title in the wiki is unique it's
a "natural"
> choice.
> <snip>
>
> Joe

For almost every project where we used natural primary keys,
I've
needed to later add some utility to allow the user to alter
them.  This
code needs to look accross every table that reference this
primary key
and alter it there.  Running it without concurrency problems
is
difficult and so dealing with the entire issue is just extra
work and a
headache.  It also introduces another user interface element
that I
have to train to users (key changes must be done in single
user mode
using this special key change menu item, blah-blah-blah). 
So a while
back, I switched to never using natural primary keys.  I've
also heard
that integers are more efficient but it's not the reason I
use them.

Surogate primary keys are also the most common pattern I see
from
experienced DBAs/developers/teams.  SQLObject even defaults
that way
not because it is more simple, but I suspect because Ian and
others
must think it is good practice.

Even on the wikipedia page you reference, it says,
"Having the key
independent of all other columns insulates the database
relationships
from changes in data values or database design (making your
database
more agile) and guarantees uniqueness.  Some database
designers use
surrogate keys religiously regardless of the suitability of
other
candidate keys, while others will use a key already present
in the
data, if there is one."

That said, I don't like my tools to place unecessary
restrictions on
me.  I like that SQLAlchemy will work equally well with
databases that
I designed years ago, before I saw the light and stopped
using natural
primary keys.  I still like to design my database in the
database and I
find it trivial to manually add my own integer primary keys
to every
table.   I'm more of a fan of autoload=True and SQLSoup
than I am of
ActiveMapper although I see the advantage of a Django Data
Model Driven
framework (or TG-FastData) for standing up new systems with
no legacy
to worry about and I see how ActiveMapper is a necessary
part of that.
That's just not usually the space where I usually work and
SQLAlchemy
is the ideal tool for either situation.

Larry Maccherone


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/pylons-discuss
-~----------~----~----~----~------~----~------~--~---

Hi Larry,

On Tue, 2006-09-19 at 20:51 -0700, Larry wrote:
> For almost every project where we used natural primary
keys, I've
> needed to later add some utility to allow the user to
alter them.  This
> code needs to look accross every table that reference
this primary key
> and alter it there.  Running it without concurrency
problems is
> difficult and so dealing with the entire issue is just
extra work and a
> headache.  It also introduces another user interface
element that I
> have to train to users (key changes must be done in
single user mode
> using this special key change menu item,
blah-blah-blah).  So a while
> back, I switched to never using natural primary keys. 
I've also heard
> that integers are more efficient but it's not the
reason I use them.

My reply to Bayle was intended to be didactic, not to argue
for one
approach or the other, which is why I prefaced it with
"Many database
professionals" (plus, this is not the right forum). 
Personally, I tend
to deal with them on a case-by-case basis, but I'm
definitely not in the
"religiously using surrogates" camp.  OTOH, I
know a guy that thinks
sequential keys are almost sacrilegious 

Joe


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/pylons-discuss
-~----------~----~----~----~------~----~------~--~---

I saw where you started with, "Many database
professionals..." and that
almost caused me not to reply, but when I read, "it is
generally
preferable..." I thought you were arguing for using
natural keys.  It's
not the right forum to have a drawn out debate about this
but Bayle is
a new user and looking for guidance here and I wanted Bayle
to know
that others disagree on that particular issue of using
natural keys.
Thanks for clarifying that.  

I'm in the always use surrogates camp.  I can think of two
cases from
my own history where I thought it would be safe to use a
natural
primary key: SS# and Universal Product Code (UPC).

We had to stop using SS# because of privacy concerns.  Can
you remember
a time when everyone used SS# for things like student
numbers?  I can.
Now, it's sacrilegios.

But UPC should have been OK... there are no privacy concerns
there and
those never change, right??? It turns out that they do,
sorta.  UPC
usage is at the descretion of the manufacturer.  If he
changes internal
supplier for a sub-part, he may choose to assign a different
UPC.
Also, a vendor may use the same UPC for all items even if
they vary in
color.  The vast majority do not and we originally designed
our system
thinking that 1 UPC = 1 Color.  When that was proven wrong,
we had to
re-design.  Since UPC was a primary key and not a field, the
changes to
the system were much more pervasive than they would have
been if we'd
just needed to push UPC down into a 1:N table.

I wonder about the guy who thinks sequential keys are almost
sacrilegious.  What does he do in situations where no
natural key
exists?... like a person object?

Larry


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/pylons-discuss
-~----------~----~----~----~------~----~------~--~---

I saw where you started with, "Many database
professionals..." and that
almost caused me not to reply, but when I read, "it is
generally
preferable..." I thought you were arguing for using
natural keys.  It's
not the right forum to have a drawn out debate about this
but Bayle is
a new user and looking for guidance here and I wanted Bayle
to know
that others disagree on that particular issue of using
natural keys.
Thanks for clarifying that.  

I'm in the always use surrogates camp.  I can think of two
cases from
my own history where I thought it would be safe to use a
natural
primary key: SS# and Universal Product Code (UPC).

We had to stop using SS# because of privacy concerns.  Can
you remember
a time when everyone used SS# for things like student
numbers?  I can.
Now, it's sacrilegios.

But UPC should have been OK... there are no privacy concerns
there and
those never change, right??? It turns out that they do,
sorta.  UPC
usage is at the descretion of the manufacturer.  If he
changes internal
supplier for a sub-part, he may choose to assign a different
UPC.
Also, a vendor may use the same UPC for all items even if
they vary in
color.  The vast majority do not and we originally designed
our system
thinking that 1 UPC = 1 Color.  When that was proven wrong,
we had to
re-design.  Since UPC was a primary key and not a field, the
changes to
the system were much more pervasive than they would have
been if we'd
just needed to push UPC down into a 1:N table.

I wonder about the guy who thinks sequential keys are almost
sacrilegious.  What does he do in situations where no
natural key
exists?... like a person object?

Larry


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/pylons-discuss
-~----------~----~----~----~------~----~------~--~---

On Wed, 2006-09-20 at 04:33 -0700, Larry wrote:
> We had to stop using SS# because of privacy concerns. 
Can you remember
> a time when everyone used SS# for things like student
numbers?  I can.
> Now, it's sacrilegios.

The banking and brokerage industries still use it.  The
medical and
insurance industries (and countless others) always ask for
it.  They may
not use them as primary keys officially but I'd be
surprised if they
don't have a UNIQUE index on it.

> I wonder about the guy who thinks sequential keys are
almost
> sacrilegious.  What does he do in situations where no
natural key
> exists?... like a person object?

I don't know.  I'll limit myself to quoting a recent rant
of his, in
response to someone who was asking for support of
"integer indexes".

---
"I don't know what you mean here.  Possibly you are
looking for 
auto-increment columns? ... A knowledgeable and disciplined 
database designer will never use them lightly, but in the
rare case
where a surrogate key is genuinely unavoidable they'll be
neat enough.
My objection to these is the past was that they discourage
designers
from learning about the serious but non-obvious problems
surrogate keys
hide.   I concede that battle is entirely lost, so what the
hell?  If it
sells product..."

"Another problem with sequential keys in
general--apart from the fact
that users invariably start to attribute unexpected meaning
to them--is
the effect they have on the performance of highly concurrent
systems
when used with B-trees."
---

These are not entirely unreasonable points.  In any case, I
suggest that
if anyone wants more they can visit comp.databases.theory
for regular
flame wars on the subject.

Joe


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/pylons-discuss
-~----------~----~----~----~------~----~------~--~---

an important article (well, a blog post that links to an
important  
article) against the usage of surrogate primary keys:

http://spyced.blogspot.com/2006/07/single-c
olumn-primary-keys-should- 
be.html


On Sep 19, 2006, at 11:51 PM, Larry wrote:

>
> Joe wrote:
>> <snip>
>> it is generally preferable to use
"natural" or "standard" IDs as  
>> primary
>> keys.  Since the page title in the wiki is unique
it's a "natural"
>> choice.
>> <snip>
>>
>> Joe
>
> For almost every project where we used natural primary
keys, I've
> needed to later add some utility to allow the user to
alter them.   
> This
> code needs to look accross every table that reference
this primary key
> and alter it there.  Running it without concurrency
problems is
> difficult and so dealing with the entire issue is just
extra work  
> and a
> headache.  It also introduces another user interface
element that I
> have to train to users (key changes must be done in
single user mode
> using this special key change menu item,
blah-blah-blah).  So a while
> back, I switched to never using natural primary keys. 
I've also heard
> that integers are more efficient but it's not the
reason I use them.
>
> Surogate primary keys are also the most common pattern
I see from
> experienced DBAs/developers/teams.  SQLObject even
defaults that way
> not because it is more simple, but I suspect because
Ian and others
> must think it is good practice.
>
> Even on the wikipedia page you reference, it says,
"Having the key
> independent of all other columns insulates the database
relationships
> from changes in data values or database design (making
your database
> more agile) and guarantees uniqueness.  Some database
designers use
> surrogate keys religiously regardless of the
suitability of other
> candidate keys, while others will use a key already
present in the
> data, if there is one."
>
> That said, I don't like my tools to place unecessary
restrictions on
> me.  I like that SQLAlchemy will work equally well with
databases that
> I designed years ago, before I saw the light and
stopped using natural
> primary keys.  I still like to design my database in
the database  
> and I
> find it trivial to manually add my own integer primary
keys to every
> table.   I'm more of a fan of autoload=True and
SQLSoup than I am of
> ActiveMapper although I see the advantage of a Django
Data Model  
> Driven
> framework (or TG-FastData) for standing up new systems
with no legacy
> to worry about and I see how ActiveMapper is a
necessary part of that.
> That's just not usually the space where I usually work
and SQLAlchemy
> is the ideal tool for either situation.
>
> Larry Maccherone
>
>
> >


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discussgooglegroups.com
To unsubscribe from this group, send email to
pylons-discuss-unsubscribegooglegroups.com
For more options, visit this group at http://
groups.google.com/group/pylons-discuss
-~----------~----~----~----~------~----~------~--~---