You are right. sqlite cannot drop columns and this means web2py cannot
change the contraint from unique=False to unique=False. Although it
will change the validator and it will enforce the contraint in forms.
On Jan 5, 9:43 pm, Christopher Helck <christopher.he...@gmail.com>
wrote:
> 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.
