I'm able to reproduce the problem. 1. Delete all database files. 2. Create table 'foo' without the unique attribute and add the three rows. No errors because there is no unique constraint. 3. Add 'unique=True' to table foo's definition and add the three rows. No error. I guess Sqlite can't update existing tables? 4. Delete all database files. 5. Rerun test with unique attribute. Third insert fails.
>From various things I've read it seems that Sqlite/web2py has problems with
migrating tables. So I have to ask if Sqlite is the right choice for me? My
DB needs are small: five tables with a total of 500 rows. I do expect to
tweak the tables every now and then, and would prefer not to lose data.
I'm wary of moving to a DB that is much bigger. I've observed many projects
slow down to a crawl when they start using systems like Oracle because of
problems coordinating changes with DBAs, tuning, and complex
deployment/upgrades. I'd like to avoid the whole enterprise database space
entirely.
Any suggestions?
Thanks,
C. Helck
On Mon, Jan 4, 2010 at 10:14 PM, mdipierro <mdipie...@cs.depaul.edu> wrote:
> I just tried it on mine:
> >>> db=DAL('sqlite://test.db')
> >>> db.define_table('foo', Field('name', unique=True))
> >>> db.foo.truncate()
> >>> db.foo.insert(name='joe')
> 1
> >>> db.foo.insert(name='sally')
> 2
> >>> db.foo.insert(name='joe')
> Traceback (most recent call last):
> File "<console>", line 1, in <module>
> File "/Users/mdipierro/web2py/gluon/sql.py", line 1849, in insert
> self._db._execute(query)
> File "/Users/mdipierro/web2py/gluon/sql.py", line 891, in <lambda>
> self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
> IntegrityError: column name is not unique
> >>>
> >>> print "Tables"
> Tables
> >>> print db().select(db.foo.ALL)
> foo.id,foo.name
> 1,joe
> 2,sally
>
>
> I guess you have a buggy version of sqlite. I would also try delete
> everything in databases/ and try again. In case something was
> corrupted with the .table files.
>
> On Jan 4, 8:39 pm, Christopher Helck <christopher.he...@gmail.com>
> wrote:
> > My DB skills are not strong, so please forgive if this is a dumb
> question.
> > Why does the following from my db.py not do what I want it to do?
> > #################
> > db.define_table('foo', Field('name', unique=True))
> >
> > db.foo.truncate()
> > db.foo.insert(name='joe')
> > db.foo.insert(name='sally')
> > db.foo.insert(name='joe')
> >
> > print "Tables"
> > print db().select(db.foo.ALL)
> >
> > db.commit()
> > ###################
> > Because I've defined the field 'name' to be unique, I expected some sort
> of
> > error to occur the second time I insert the row containing 'joe'.
> Instead, I
> > end up with a table with three rows: joe, sally, and joe. I'm using
> SqlLite.
> >
> > Thanks,
> > C Helck
>
> --
>
> You received this message because you are subscribed to the Google Groups
> "web2py-users" group.
> To post to this group, send email to web...@googlegroups.com.
> To unsubscribe from this group, send email to
> web2py+unsubscr...@googlegroups.com<web2py%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/web2py?hl=en.
>
>
>
-- You received this message because you are subscribed to the Google Groups "web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
