Just for the record:
it wouldn't require any significant amount more code.
Yes it would. My code currently approximates to this:
cursor.execute("insert into foo (x,y,z) values(?,?,?)", x,y,z)
It would have to change into this:
# column x is defined as string
if isinstance(x, string): storex=x
elif isinstance(x, int): storex=`x`
elif isinstance(x, bool):
if x: storex="1" else: storex="0"
else # various other types and conditions for this context
# repeat for y and z
....
# add in values
cursor.execute("insert into foo (x,y,z) values(?,?,?)", storex, storey, storez)
In fact, particularly for queries (and reading data tends to be more
common than writing), there should be less code.
Currently:
x,y,z=cursor.execute("select x,y,z from foo")
Change to:
resultx, resulty, resultz=cursor.execute("select x,y,z from foo")
# now do type conversion back to the correct types
if # somehow know it may have been an int:
x=int(resultx)
elif # somehow know it may have been a bool:
x=bool(int(resultx))
... you get the picture ...
Not having manifest types in the database throws away information
when you store values and requires restituting them when reading.
My main app happens to store phone numbers. You won't believe
how irritating it is when I find things automatically assume they
are integers.
Or, looking at this
another way, perhaps the Python bindings for SQLite should be taking
care of this for you.
They can't, unless they do something like silenty add an extra
column that stores the types of the values in the other columns
and attempt to transparently modify the SQL as it flys by
to get or update that column. (BTW I also happen to be an author
of wrappers for Python). (Your proposal sort of does this
by introducing a manifest type.)
The solution you discussed will make code that has to deal with
random SQLite databases produced by other applications, or earlier
versions of the same app significantly more complicated. However
I don't think there are too many of them to be concerned about.
I would suggest finding an open source application that uses
SQLite and see if you would indeed make it simpler. One good
example I would suggest is Trac which was originally written
to use SQLite.
Roger
