By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,405 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,405 IT Pros & Developers. It's quick & easy.

Is it just me, or is Sqlite3 goofy?

P: n/a
Probably just me. I've only been using Access and SQL Server
for 12 years, so I'm sure my opinions don't count for anything.

I was, nevertheless, looking forward to Sqlite3. And now
that gmpy has been upgraded, I can go ahead and install
Python 2.5.

So I open the manual to Section 13.13 where I find the first
example of how to use Sqlite3:

<code>
conn = sqlite3.connect(':memory:')

c = conn.cursor()

# Create table
c.execute('''create table stocks
(date timestamp, trans varchar, symbol varchar,
qty decimal, price decimal)''')

# Insert a row of data
c.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")
</code>

Seems pretty simple, yet I was befuddled and bewildered
by this example. So much so that I added a query to see exactly
what was going on.

<code>
# added by me
c.execute('select * from stocks')
d = c.fetchone()
for t in d:
print type(t),t
</code>

Original code - what's wrong with this picture?
<type 'unicode'2006-01-05
<type 'unicode'BUY
<type 'unicode'RHAT
<type 'int'100
<type 'float'35.14

Why is the date returning as a string? Aren't the built in
converters supposed to handle that? Yes, if you enable
detect_types.

Added detect_types=sqlite3.PARSE_DECLTYPES

Traceback (most recent call last):
File "C:\Python25\sqlite_first_example.py", line 30, in <module>
c.execute('select * from stocks')
File "C:\Python25\lib\sqlite3\dbapi2.py", line 66, in
convert_timestamp
datepart, timepart = val.split(" ")
ValueError: need more than 1 value to unpack

Aha, that explains why they weren't enabled.

This failed because
- the value inserted was wrong format?
- and the builtin converter can't split it cuz it has no spaces?
when it worked it was because
- detect_types was not set, so converter not invoked when queried?

Yes, the format in the example was datetime.date and the field type
should have been cast [date], not [timestamp] which needs HH:MM:SS
for the converter to work properly (but only if detect_types
enabled).

If a correct format was inserted, converter would have worked

<type 'datetime.datetime'2006-09-04 13:30:00
<type 'unicode'BUY
<type 'unicode'RHAT
<type 'int'100
<type 'float'35.14

Or, had the field been properly cast as [date] instead of [timestamp]
it would also have worked.

<type 'datetime.date'2006-09-04
<type 'unicode'BUY
<type 'unicode'RHAT
<type 'int'100
<type 'float'35.14

Ah, this now partly explains the original result, since detect_types
is off by default, the field cast, not being a native sqlite3 type
was ignored and the data queried back as TEXT.

<type 'unicode'2006-09-04
<type 'unicode'BUY
<type 'unicode'RHAT
<type 'int'100
<type 'float'35.14

Ok, next issue, what the fuck are [varchar] and [decimal]?
They are certainly not Sqlite3 native types. If they are
user defined types, where are the converters and adapters?
Does Sqlite3 simply ignore a cast that isn't registered?

Note that although both qty and price were cast as [decimal]
they queried back as int and float.

Note also that it's "obvious" from the query example on page 13.13
that the example is FUBAR
- the date is a unicode string, not a datetime.date type
- the price is binary floating point, not decimal

<quote>
This example uses the iterator form:
>>c = conn.cursor()
c.execute('select * from stocks order by price')
for row in c:
... print row
...
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
(u'2006-04-05', u'BUY', u'MSOFT', 1000, 72.0)
</quote>

Here we have an example of things apparently working
for the wrong reason. A classic example of the programmer
who *thinks* he knows how it works because he wrote it.
This kind of sloppiness wouldn't last 5 minutes in a production
environment.

But why did Sqlite3 make qty an int and price a float?
Hard to say since THE FURTHER EXAMPLES IN THE DOCS don't
even bother to cast the field types. I guess I'm supposed
to guess how things are supposed to work. Can I trust that
default settings will be what I want?

Ha! Can I trust the baby with a hammer?

First, note that the script example in section 13.13.3

<quote>
import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
create table person(
firstname,
lastname,
age
);

create table book(
title,
author,
published
);

insert into book(title, author, published)
values (
'Dirk Gently''s Holistic Detective Agency
'Douglas Adams',
1987
);
""")
</quote>

contains not one but TWO syntax errors! A single quote after
the word Agency is missing as is the comma that should be at
the end of that line. Seems that no one actually ever tried
this example.

That's easily fixed. But I was curious about why the fields
don't have type casts. After the previous debacle and
knowing that this code was never tested, I am not going to
assume it works. Better add a query to make sure.

cur.execute("select title, author, published from book")
d = cur.fetchall()
for i in d: print i
print

(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', 1987)

Ok, so if not cast, the fields must default (and probably also when
a cast is made that hasn't been defined).

But watch this: being clueless (but not stupid) is a gift I have
for troubleshooting. I tried (incorrectly) to insert another record:

cur.execute("insert into book(title, author, published) values ('Dirk
Gently''s Holistic Detective Agency','Douglas Adams','1987')")

(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', 1987)
(u"Dirk Gently's Holistic Detective Agency", u'Douglas Adams', u'1987')

Uhh...how can a database have a different field type for each record?

Simple, without a cast when the table is created, the field type is
whatever you insert into it. That's how the default must work,
each record has a data structure independent of every other record!

Wow. Just think of the kind of bugs *that* must cause.

Bugs?

Here's MY example, creating a Cartesian Product

<code>
import sqlite3
letters = [(2,),('10',),('20',),(200,)]
con = sqlite3.connect(":memory:")
con.text_factory = str
con.execute("create table letter(c integer)")
con.executemany("insert into letter(c) values (?)", letters)
print 'Queried: ',
for row in con.execute("select c from letter"):
print row,
print
print
print 'Sorted: ',
for row in con.execute("select c from letter order by c"):
print row[0],
print
print
print 'Cartesian Product: ',
for row in con.execute("select a.c, b.c, c.c from letter as a, letter
as b, letter as c"):
print row[0]+row[1]+row[2],
</code>

Note that the list of data to be inserted contains both strings and
ints. But because the field was correctly cast as [integer], Sqlite3
actually stored integers in the db. We can tell that from how the
"order by" returned the records.

Queried: (2,) (10,) (20,) (200,)

Sorted: 2 10 20 200

Cartesian Product: 6 14 24 204 14 22 32 212 24 32 42 222 204 212
222 402 14 22 32 212 22 30 40 220 32 40 50 230 212 220 230 410 24
32 42 222 32 40 50 230 42 50 60 240 222 230 240 420 204 212 222
402 212 220 230 410 222 230 240 420 402 410 420 600

Because if I cast them as [text] the sort order changes (and my
Cartesian Product becomes concatenation instead of summation).

Queried: ('2',) ('10',) ('20',) ('200',)

Sorted: 10 2 20 200

Cartesian Product: 222 2210 2220 22200 2102 21010 21020 210200
2202 22010 22020 220200 22002 220010 220020 2200200 1022 10210
10220 102200 10102 101010 101020 1010200 10202 102010 102020
1020200 102002 1020010 1020020 10200200 2022 20210 20220 202200
20102 201010 201020 2010200 20202 202010 202020 2020200 202002
2020010 2020020 20200200 20022 200210 200220 2002200 200102
2001010 2001020 20010200 200202 2002010 2002020 20020200 2002002
20020010 20020020 200200200

But if I omit the cast altogether, then the db stores the input
exactly as it was inserted, so the c field contains both
text and integers wreaking havoc with my sort order, making
records un-queryable using "where" and causing my Cartesian
Product to crash.

Queried: (2,) ('10',) ('20',) (200,)

Sorted: 2 200 10 20

Cartesian Product: 6

Traceback (most recent call last):
File "C:\Python25\user\sqlite_test2.py", line 225, in <module>
print row[0]+row[1]+row[2],
TypeError: unsupported operand type(s) for +: 'int' and 'str'

Yeah, I know, I've heard it before.

"This behavior is by design."

It's still fuckin' goofy.

Sep 5 '06
Share this Question
Share on Google+
66 Replies


P: n/a
me********@aol.com wrote:
Because I can extrapolate. I *know* before even trying it that
if I export all my data from a sqlite db to a csv file and then try
to import it into Access that there will be problems if the fields
aren't static typed.
that's just the old "C++/Java is better than Smalltalk/Python/Ruby"
crap. we've seen it before, and it's no more true when it comes from
you than when it comes from some Java head. people who've actually used
dynamic typing knows that it doesn't mean that all objects have random
types all the time.
That's one of the reasons why I was such a good test engineer.
I could anticipate problems the design engineers didn't think of
and I would deliberately provoke those problems during testing
and crash their hardware/software.

I wasn't very popular.
no wonder, if you kept running around telling your colleagues that they
were liars and crackpots and slanderers when things didn't work as you
expected. what's your current line of work, btw?

</F>

Sep 12 '06 #51

P: n/a
On 12 Sep 2006 10:24:00 -0700,
me********@aol.com <me********@aol.comwrote:
So, knowing that, would you agree that

<quote Python Library Reference 13.13>
If switching to a larger database such as PostgreSQL or Oracle
is later necessary, the switch should be relatively easy.
</quote>

is misleading if not outright untruthful?
As the original author of that sentence, I don't think it's either
misleading or untruthful; 'relatively easy' gives me wiggle room.
However, to fix your complaint, I've changed the paragraph to read:

SQLite is a C library that provides a lightweight disk-based
database that doesn't require a separate server process and allows
accessing the database using a nonstandard variant of the SQL query
language. Some applications can use SQLite for internal data
storage. It's also possible to prototype an application using SQLite
and then port the code to a larger database such as PostgreSQL or
Oracle.

Of course, if you accept Fredrik's reading of the SQL standard, the
word 'nonstandard' in the revised text is incorrect; SQLite is
compliant with the standard but in an unusual way. (But most readers
will interpret "nonstandard" as meaning "not like most other SQL
databases", so I'll let it stand.)

--amk
Sep 12 '06 #52

P: n/a
Fredrik Lundh wrote:
me********@aol.com wrote:
Because I can extrapolate. I *know* before even trying it that
if I export all my data from a sqlite db to a csv file and then try
to import it into Access that there will be problems if the fields
aren't static typed.

that's just the old "C++/Java is better than Smalltalk/Python/Ruby"
crap. we've seen it before, and it's no more true when it comes from
you than when it comes from some Java head. people who've actually used
dynamic typing knows that it doesn't mean that all objects have random
types all the time.
No, it isn't the same old crap. When I define an Access field as
Double, I cannot insert a value such as ">200" or "ND" or "Yes".
I'm not saying static typing is better, just that migrating a dynamic
types to static types may cause difficulties that wouldn't be present
if it was static to static.

And if you call the "C++/Java is better than Smalltalk/Python/Ruby"
statement crap, why do you accept the statement that
"static typing is a bug in the SQL specification"? Isn't that
crap also?
>
That's one of the reasons why I was such a good test engineer.
I could anticipate problems the design engineers didn't think of
and I would deliberately provoke those problems during testing
and crash their hardware/software.

I wasn't very popular.

no wonder, if you kept running around telling your colleagues that they
were liars and crackpots and slanderers when things didn't work as you
expected.
Nobody cared about that. What they cared about was my
reporting to their boss that the latest version of the software
was no better than the previous version who then had to figure
out how to explain to the customer that the improvement he
was promised didn't materialize and who then had to explain
to his boss why the customer still hadn't signed off on the
delivery and pay the bill.
what's your current line of work, btw?
Database manager for an a geotechnical consulting firm doing
environmental remediation.
>
</F>
Sep 12 '06 #53

P: n/a
me********@aol.com wrote:
>that's just the old "C++/Java is better than Smalltalk/Python/Ruby"
crap. we've seen it before, and it's no more true when it comes from
you than when it comes from some Java head. people who've actually used
dynamic typing knows that it doesn't mean that all objects have random
types all the time.

No, it isn't the same old crap. When I define an Access field as
Double, I cannot insert a value such as ">200" or "ND" or "Yes".
I'm not saying static typing is better, just that migrating a dynamic
types to static types may cause difficulties that wouldn't be present
if it was static to static.
dynamic typing != random typing. if your program is using the DB-API to
add data to an SQLite database, who, exactly, is inserting the values?
who's producing the data? under what circumstances would that code
produce or insert arbitrarily typed data?

</F>

Sep 12 '06 #54

P: n/a

A.M. Kuchling wrote:
On 12 Sep 2006 10:24:00 -0700,
me********@aol.com <me********@aol.comwrote:
So, knowing that, would you agree that

<quote Python Library Reference 13.13>
If switching to a larger database such as PostgreSQL or Oracle
is later necessary, the switch should be relatively easy.
</quote>

is misleading if not outright untruthful?

As the original author of that sentence, I don't think it's either
misleading or untruthful; 'relatively easy' gives me wiggle room.
Ok, I appologize for saying that. Got a little carried away
by the flames.
However, to fix your complaint, I've changed the paragraph to read:

SQLite is a C library that provides a lightweight disk-based
database that doesn't require a separate server process and allows
accessing the database using a nonstandard variant of the SQL query
language. Some applications can use SQLite for internal data
storage. It's also possible to prototype an application using SQLite
and then port the code to a larger database such as PostgreSQL or
Oracle.

Of course, if you accept Fredrik's reading of the SQL standard, the
word 'nonstandard' in the revised text is incorrect; SQLite is
compliant with the standard but in an unusual way. (But most readers
will interpret "nonstandard" as meaning "not like most other SQL
databases", so I'll let it stand.)
And that was basically what I was originally asking for.

That and fixing the broken examples.

And maybe add a few more to illustrate SQLite manifest
typing which is alien to a long time Access user.

Should I start a new thread (sans polemics) to discuss the
examples?
>
--amk
Sep 12 '06 #55

P: n/a
dynamic typing != random typing. if your program is using the
DB-API to add data to an SQLite database, who, exactly, is
inserting the values? who's producing the data? under what
circumstances would that code produce or insert arbitrarily
typed data?
Must be the code written by a Dr. Jekyll/Mr. Hyde personality...

"But honestly, boss, I didn't write this code! It was my evil
alter-ego that puts VARCHAR values containing Gilbert & Sullivan
lyrics into the Amount_Due CURRENCY fields!"

:)

-tkc


Sep 12 '06 #56

P: n/a
Tim Chase wrote:
>>dynamic typing != random typing. if your program is using the
DB-API to add data to an SQLite database, who, exactly, is
inserting the values? who's producing the data? under what
circumstances would that code produce or insert arbitrarily
typed data?


Must be the code written by a Dr. Jekyll/Mr. Hyde personality...

"But honestly, boss, I didn't write this code! It was my evil
alter-ego that puts VARCHAR values containing Gilbert & Sullivan
lyrics into the Amount_Due CURRENCY fields!"
Hence the phrase "Going for a song"?

groan-along-with-me-ly y'rs - steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

Sep 12 '06 #57

P: n/a
me********@aol.com wrote:
A.M. Kuchling wrote:
>>On 12 Sep 2006 10:24:00 -0700,
me********@aol.com <me********@aol.comwrote:
>>>So, knowing that, would you agree that

<quote Python Library Reference 13.13>
If switching to a larger database such as PostgreSQL or Oracle
is later necessary, the switch should be relatively easy.
</quote>

is misleading if not outright untruthful?

As the original author of that sentence, I don't think it's either
misleading or untruthful; 'relatively easy' gives me wiggle room.


Ok, I appologize for saying that. Got a little carried away
by the flames.

>>However, to fix your complaint, I've changed the paragraph to read:

SQLite is a C library that provides a lightweight disk-based
database that doesn't require a separate server process and allows
accessing the database using a nonstandard variant of the SQL query
language. Some applications can use SQLite for internal data
storage. It's also possible to prototype an application using SQLite
and then port the code to a larger database such as PostgreSQL or
Oracle.

Of course, if you accept Fredrik's reading of the SQL standard, the
word 'nonstandard' in the revised text is incorrect; SQLite is
compliant with the standard but in an unusual way. (But most readers
will interpret "nonstandard" as meaning "not like most other SQL
databases", so I'll let it stand.)


And that was basically what I was originally asking for.

That and fixing the broken examples.

And maybe add a few more to illustrate SQLite manifest
typing which is alien to a long time Access user.

Should I start a new thread (sans polemics) to discuss the
examples?
Why don't you? That would seem like a productive forward direction.

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

Sep 12 '06 #58

P: n/a
On 12 Sep 2006 13:03:09 -0700,
me********@aol.com <me********@aol.comwrote:
Ok, I appologize for saying that. Got a little carried away
by the flames.
Apology accepted; no problem.
That and fixing the broken examples.
That's also done. I fixed the executescript.py example, and tried
running all the other examples as a check; that didn't turn up any
more crashers.

The development version of the docs, built nightly from the SVN trunk,
are at <http://docs.python.org/dev/>. Note that the version number is
now 2.6a0 on the trunk, but I can backport fixes to 2.5-maint as
they're made. (Any new changes won't get in 2.5c2, which should be
released tomorrow, but will get into 2.5final if the fixes are made by
about the 17th.)
Should I start a new thread (sans polemics) to discuss the
examples?
Certainly!

--amk
Sep 12 '06 #59

P: n/a
On Tue, 12 Sep 2006 15:54:25 -0500,
A.M. Kuchling <am*@amk.cawrote:
(Any new changes won't get in 2.5c2, which should be
released tomorrow, but will get into 2.5final if the fixes are made by
about the 17th.)
And in fact the formatted development version no longer reflects
what's in the trunk: I've just checked in a bunch of minor typo fixes
and other edits to the trunk. So you may want to wait until tomorrow
when these fixes show up... or you can look at the original LaTeX in
the SVN browser at <http://svn.python.org/view/python/trunk/Doc/lib/>.

--amk

Sep 12 '06 #60

P: n/a
>"But honestly, boss, I didn't write this code! It was my
>evil alter-ego that puts VARCHAR values containing Gilbert &
Sullivan lyrics into the Amount_Due CURRENCY fields!"
Hence the phrase "Going for a song"?
I am the very model of a modern major database,
For gigabytes of information gathered out in userspace.
For banking applications to a website crackers will deface,
You access me from console or spiffy user interface.

My multi-threaded architecture offers you concurrency,
And loads of RAM for caching things reduces query latency.
The data is correctly typed, a fact that I will guarantee,
Each datum has a data type, it's specified explicitly.

is-it-friday-yet'ly yers,

-tkc

Sep 12 '06 #61

P: n/a

Tim Chase wrote:
"But honestly, boss, I didn't write this code! It was my
evil alter-ego that puts VARCHAR values containing Gilbert &
Sullivan lyrics into the Amount_Due CURRENCY fields!"
Hence the phrase "Going for a song"?

I am the very model of a modern major database,
For gigabytes of information gathered out in userspace.
For banking applications to a website crackers will deface,
You access me from console or spiffy user interface.

My multi-threaded architecture offers you concurrency,
And loads of RAM for caching things reduces query latency.
The data is correctly typed, a fact that I will guarantee,
Each datum has a data type, it's specified explicitly.
Mensantor:
When I was a lad I had to load
Data to Access via import mode.
Data came in as DBase III
And had to be converted to csv.
I so carefully converted the csv's
That now I am the admin of big db's.

Chorus:
He so carefully converted the csv's
That now he is the admin of big db's.
>
is-it-friday-yet'ly yers,

-tkc
Sep 12 '06 #62

P: n/a
"Tim Chase" <py*********@tim.thechases.comwrote:

To: "Steve Holden" <st***@holdenweb.com>

"But honestly, boss, I didn't write this code! It was my
evil alter-ego that puts VARCHAR values containing Gilbert &
Sullivan lyrics into the Amount_Due CURRENCY fields!"
Hence the phrase "Going for a song"?

I am the very model of a modern major database,
For gigabytes of information gathered out in userspace.
For banking applications to a website crackers will deface,
You access me from console or spiffy user interface.

My multi-threaded architecture offers you concurrency,
And loads of RAM for caching things reduces query latency.
The data is correctly typed, a fact that I will guarantee,
Each datum has a data type, it's specified explicitly.

is-it-friday-yet'ly yers,

-tkc
I love this group - thanks for that - it made my day ...

- Hendrik

Sep 13 '06 #63

P: n/a
First of all, anyone with extensive experience in database systems
understand that validating and cleaning input is an unavoidable task.

Static typing can help identify some of the problems, but far from
all, and there is often data processing done before the data enters
the database, so it's often too late to do the cleaning at that time
anyway.

Once you are dealing with data within your system, it's a bug in the
software if data doesn't fulfill the intended constraints. Such
problems should be identified by unit tests.

Mike Owens wrote:
Next, as far as transferring you data, you most likely have to resort
to some delimited format, or INSERT statements, which is no different
than any other database.
You can always write a Python script which reads from one
database, cleans up the data and inserts into another, one
row at a time. This isn't just a porting activity. I've written
a Python app that moved data from a DB2 mainframe production
database to DB2 testing database on Windows. It moved data
from a hierarchy of tables, starting in a given table, and
reading foreign keys from system tables (very slow in mainframe
DB2!) to figure out what data to bring from which tables.

Since the system used abstract keys and moved data to a populated
database, it had to generate new keys and adjust the foreign keys
in the dependent tables. It also had to work around bugs and
quirks in Windows NT, DB2 and ADODBAPI etc. These things are
relatively easy, but it's never trivial. There are always a lot
of details to deal with.

For big volumes, you typically need to use some kind of bulk
loading facility. Inserts are generally much too slow. (I'm
talking about general data porting woes here--in the case of
SQLite it's not likely that you have many million rows of data.)

Efficient bulk loading means that you have different data format
for different database systems, and also that you need to validate
your data before insertion, so the problems directly related to
SQLite doesn't seem very odd.
Sep 13 '06 #64

P: n/a
Bryan Olson wrote:
Ben Sizer wrote:
It's not a crackpot theory. It's a completely reasonable theory. SQL is
based on relational algebra, which provides a mathematical set of
operators for grouping data that is stored in separate sets. That data
is selected and projected according to its value, and nothing else. The
concept of it having a 'type' has been overlaid on top of this,
presumably to facilitate efficient implementation, which tends to
require fixed-width rows (and hence columns). It's not necessary in any
sense, and it's reasonable to argue that if it was trivial to implement
variable width columns as efficiently as fixed width columns, that
explicit data types might never have needed to exist.

The mathematical definition of the relational model includes
that data values are drawn from specific sets.
Well, I did say relational algebra, which from what I understand
predates the official 'relational model'.
Implementing variable width columns has nothing to do with it.
On a practical level, it has lots to do with it!
Here's
the reference:

1.3. A Relational View of Data

The term relation is used here in its accepted mathematical
sense. Given sets S1, S2, иии, Sn, (not necessarily
distinct), R is a relation on these n sets if it is a set
of n-tuples each of which has its first element from S1,
its second element from S2, and so on [1]. We shall refer to
Sj as the jth domain of R.
Does it specify anywhere that sets S1...Sn cannot each be the universal
set? To put it another way - although the spec implies the existence of
limited set domains, and data types enforce limited domains, I don't
think a requirement to allow limited domains is a requirement for
static data types.

--
Ben Sizer

Sep 14 '06 #65

P: n/a
Ben Sizer wrote:
Bryan Olson wrote:
>Ben Sizer wrote:
>>It's not a crackpot theory. It's a completely reasonable theory. SQL is
based on relational algebra, which provides a mathematical set of
operators for grouping data that is stored in separate sets. That data
is selected and projected according to its value, and nothing else. The
concept of it having a 'type' has been overlaid on top of this,
presumably to facilitate efficient implementation, which tends to
require fixed-width rows (and hence columns). It's not necessary in any
sense, and it's reasonable to argue that if it was trivial to implement
variable width columns as efficiently as fixed width columns, that
explicit data types might never have needed to exist.
>The mathematical definition of the relational model includes
that data values are drawn from specific sets.

Well, I did say relational algebra, which from what I understand
predates the official 'relational model'.
Relational algebra got into it when you said "SQL is based on
relational algebra". SQL is based on the relation model.
Incidentally SQL's expressions are closer to relation calculus
than to relational algebra.

Furthermore, relation algebra does deal with types. One can,
for example, infer the type of a result from the type of the
operands.

>Implementing variable width columns has nothing to do with it.

On a practical level, it has lots to do with it!
There are subtler points on that, but it's not the issue here.
>Here's
the reference:

1.3. A Relational View of Data

The term relation is used here in its accepted mathematical
sense. Given sets S1, S2, иии, Sn, (not necessarily
distinct), R is a relation on these n sets if it is a set
of n-tuples each of which has its first element from S1,
its second element from S2, and so on [1]. We shall refer to
Sj as the jth domain of R.

Does it specify anywhere that sets S1...Sn cannot each be the universal
set?
No; it indicates that they can be different, and when they are,
value not of the given set are not legal.
To put it another way - although the spec implies the existence of
limited set domains, and data types enforce limited domains, I don't
think a requirement to allow limited domains is a requirement for
static data types.
One might argue that a one-atom-type-only DBMS can still be
relational, but that's not what SQLite offers. As we've seen,
one does declare the type of a column, and the DBMS seems to
prefer the stated type in that it will sometimes convert
values of other types.
--
--Bryan
Sep 14 '06 #66

P: n/a
Fredrik Lundh wrote:
>... dynamic typing != random typing.
So true. To get a really good random typing going, you need a
cryptographically strong random number generator to feed the
application of type constructors to values during the execution
of a program. Perhaps the best way to do this is to modify the
the interpreter. We could call the new language "Python?!", or
actually use an interobang if Unicode has such a character.

--Scott David Daniels
sc***********@acm.org
Sep 22 '06 #67

66 Replies

This discussion thread is closed

Replies have been disabled for this discussion.