Re: [web2py] Switching sqlite/postgres
Paolo,
You said, " I cannot move the pictures table declaration because both off
them have references to each other (mutual reference?) "
Why would the tables have mutual references? What kind of relation would
cause that?
Here are the kinds of relations I know about.
One to many; put the reference to the table with one in the table with many.
db.define_table('dog', field('name'))
db define_table('flea', field('dog', db.dog), field('gender'))
Many to many: create a third table to embody the relationship; reference
both tables.
db.define_table('dog', field('name'))
db.define_table('human', field('name'))
db.define_table('dog_human', field('dog', db.dog), field('human', db.human))
One to one: put the reference in the table less likely to hold data.
db.define_table('human', field('name'))
db.define_table('doctor', field('human', db.human), field('specialty'))
On Thursday, June 14, 2012 8:41:45 AM UTC-4, Paolo wrote:
>
> Hi all,
> Actually
> In route the field photo_id references pictures and in pictures the
> field route_id references route.
>
> --
> Paolo
>
> Il 14.06.2012 09:56 Niphlod ha scritto:
> > don't know precisely how dal works, but if a table have a field
> > referenced in a table defined after that, wouldn't that cause a
> > problem ?
> >
> > i.e. db.define_table('route',
> > Field('photo_id', 'reference pictures', readable=False,
> > writable=False)
> > )
> > db.define_table('pictures',
> > Field('name'))
> >
> > does not work, where
> > db.define_table('pictures',
> > Field('name'))
> > db.define_table('route',
> > Field('photo_id', 'reference pictures', readable=False,
> > writable=False)
> > )
> >
> > instead works ?
> >
> > Not using web2py, for normal sql development, you have to create
> > "child" tables before declaring a "parent" with references to it.
>
>
Re: [web2py] Switching sqlite/postgres
I thought the 'reference mytable' version (vs db.mytable) was supposed to get around this issue? Or is that only for self-referencing: http://web2py.com/books/default/chapter/29/6#Self-Reference-and-aliases
Re: [web2py] Switching sqlite/postgres
No problem at all. PS: sql.log shows what web2py tries to do when creating tables with the status of each command if you want to "trace" this kind of errors in the future, just start from an empty database and use a normal database client to "replay" sql.log, statement after statement.
Re: [web2py] Switching sqlite/postgres
I am paolo! I've just discovered that on google groups it is written gabriella :-( I will try to fix it, maybe because I am not using a google mail? Anyway, you are right! thanks again for the feedbacks paolo:-) Il 14.06.2012 15:35 Niphlod ha scritto: Gabriella, or Paolo. Maybe it's because I "was born" speaking of jobs in the SQL department, but have you ever tried "manually" to create two tables and set a mutual relationship within those two without first creating the tables without references? When you create the route table, how can the db know to reference a column in a table that doesn't exist ? (The same goes by if you create the pictures table first). Reference fields in web2py work very well it's just mutual references that are not handled (it's quite a complex logic)
Re: [web2py] Switching sqlite/postgres
Gabriella, or Paolo. Maybe it's because I "was born" speaking of jobs in the SQL department, but have you ever tried "manually" to create two tables and set a mutual relationship within those two without first creating the tables without references? When you create the route table, how can the db know to reference a column in a table that doesn't exist ? (The same goes by if you create the pictures table first). Reference fields in web2py work very well it's just mutual references that are not handled (it's quite a complex logic)
Re: [web2py] Switching sqlite/postgres
Hi Niphlod, again thanks for your suggestion, it worked very well.
I removed all the reference fields, I run once and then again with all
the reference fields without the former errors.
To me is a bit strange that the reference type field doesn't work with
an empty db :(
Regards,
--
Paolo
Il 14.06.2012 14:56 Niphlod ha scritto:
hehe poor web2py (and maybe db schema)
You should let create the two tables without references and then set
the references to each other.
I think that during the first creation if a foreign key is requested
but there is no foreign table the db (rightfully) will prevent you to
create such foreign key.
Il giorno giovedì 14 giugno 2012 14:41:45 UTC+2, Gabriella Canavesi
ha scritto:
Hi all,
Actually I cannot move the pictures table declaration because both
off
them have references to each other (mutual reference?)
In route the field photo_id references pictures and in pictures the
field route_id references route.
--
Paolo
Il 14.06.2012 09:56 Niphlod ha scritto:
don't know precisely how dal works, but if a table have a field
referenced in a table defined after that, wouldn't that cause a
problem ?
i.e. db.define_table('route',
Field('photo_id', 'reference pictures', readable=False,
writable=False)
)
db.define_table('pictures',
Field('name'))
does not work, where
db.define_table('pictures',
Field('name'))
db.define_table('route',
Field('photo_id', 'reference pictures', readable=False,
writable=False)
)
instead works ?
Not using web2py, for normal sql development, you have to create
"child" tables before declaring a "parent" with references to it.
Re: [web2py] Switching sqlite/postgres
hehe poor web2py (and maybe db schema)
You should let create the two tables without references and then set the
references to each other.
I think that during the first creation if a foreign key is requested but
there is no foreign table the db (rightfully) will prevent you to create
such foreign key.
Il giorno giovedì 14 giugno 2012 14:41:45 UTC+2, Gabriella Canavesi ha
scritto:
>
> Hi all,
> Actually I cannot move the pictures table declaration because both off
> them have references to each other (mutual reference?)
> In route the field photo_id references pictures and in pictures the
> field route_id references route.
>
> --
> Paolo
>
> Il 14.06.2012 09:56 Niphlod ha scritto:
> > don't know precisely how dal works, but if a table have a field
> > referenced in a table defined after that, wouldn't that cause a
> > problem ?
> >
> > i.e. db.define_table('route',
> > Field('photo_id', 'reference pictures', readable=False,
> > writable=False)
> > )
> > db.define_table('pictures',
> > Field('name'))
> >
> > does not work, where
> > db.define_table('pictures',
> > Field('name'))
> > db.define_table('route',
> > Field('photo_id', 'reference pictures', readable=False,
> > writable=False)
> > )
> >
> > instead works ?
> >
> > Not using web2py, for normal sql development, you have to create
> > "child" tables before declaring a "parent" with references to it.
>
>
Re: [web2py] Switching sqlite/postgres
Hi all,
Actually I cannot move the pictures table declaration because both off
them have references to each other (mutual reference?)
In route the field photo_id references pictures and in pictures the
field route_id references route.
--
Paolo
Il 14.06.2012 09:56 Niphlod ha scritto:
don't know precisely how dal works, but if a table have a field
referenced in a table defined after that, wouldn't that cause a
problem ?
i.e. db.define_table('route',
Field('photo_id', 'reference pictures', readable=False,
writable=False)
)
db.define_table('pictures',
Field('name'))
does not work, where
db.define_table('pictures',
Field('name'))
db.define_table('route',
Field('photo_id', 'reference pictures', readable=False,
writable=False)
)
instead works ?
Not using web2py, for normal sql development, you have to create
"child" tables before declaring a "parent" with references to it.
Re: [web2py] Switching sqlite/postgres
don't know precisely how dal works, but if a table have a field referenced
in a table defined after that, wouldn't that cause a problem ?
i.e. db.define_table('route',
Field('photo_id', 'reference pictures', readable=False,
writable=False)
)
db.define_table('pictures',
Field('name'))
does not work, where
db.define_table('pictures',
Field('name'))
db.define_table('route',
Field('photo_id', 'reference pictures', readable=False,
writable=False)
)
instead works ?
Not using web2py, for normal sql development, you have to create "child"
tables before declaring a "parent" with references to it.
Re: [web2py] Switching sqlite/postgres
Gabriella,
Does the problem go away if you define the pictures table before the route
table?
On Wednesday, June 13, 2012 6:38:07 PM UTC-4, Gabriella Canavesi wrote:
>
> Yes off course :-)
> It is just below the route table definition, here:
>
> db.define_table('pictures',
> Field("picture", "upload", requires=(IS_NOT_EMPTY(),
> IS_IMAGE())),
> Field("normal", "upload", IS_IMAGE()),
> Field("thumbnail", "upload", IS_IMAGE()),
> Field('route_id', db.route, default=db.route.id),
> Field('user_id', db.auth_user, default=auth.user_id),
> Field('created_on', 'datetime', default=request.now),
> Field('description', 'text', requires=[IS_TRIM(),
> IS_LENGTH(140,error_message=T('Warning, description too long'))])
> )
>
> paolo
>
> Il 14.06.2012 00:34 Massimo Di Pierro ha scritto:
> > Do you have a
> >
> > db.define_table('pictures',)
> >
> > ?
> >
> > On Wednesday, 13 June 2012 17:21:52 UTC-5, Gabriella Canavesi wrote:
> >
> >> Hi Niphlod, thanks you for the suggestion I solved even that error
> >> and
> >> I added
> >> check_reserved=['common','postgres', 'sqlite'] to avoid future
> >> problems.
> >>
> >> Unfortunately now I am getting an error saying:
> >>
> >> relation "pictures" does not exist
> >>
> >> which is the first field set as reference.
> >> the table definition is the following:
> >>
> >> db.define_table('route',
> >> Field('name', 'string',
> >> requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'route.name [1]'),
> >> IS_CAPITALIZE()]),
> >> Field('user_id', db.auth_user, default=auth.user_id),
> >> Field("slug", "string",
> >> requires=[IS_SLUG(),IS_NOT_IN_DB(db,'route.slug')]),
> >> Field('length','double', default=random.randint(0, 50)),
> >> Field('height','integer', default=random.randint(0, 50)),
> >> Field('max_elevation','integer', default=random.randint(0,
> >> 50)),
> >> Field('max_elevation_lat', 'double'),
> >> Field('max_elevation_lgt', 'double'),
> >> Field('min_elevation','integer', default=random.randint(0,
> >> 50)),
> >> Field('min_elevation_lat', 'double'),
> >> Field('min_elevation_lgt', 'double'),
> >> Field('start', 'string'),
> >> Field('endC', 'string'),
> >> Field('photo_id', 'reference pictures', readable=False,
> >> writable=False),
> >> Field("signs", 'reference signs', readable=False,
> >> writable=False),
> >> Field("kml", "upload",
> >> uploadfolder=request.folder+'uploads/kml', uploadseparate=True),
> >> Field("svg_altitude", 'string', readable=False,
> >> writable=False),
> >> Field("desc_it",'reference description', readable=False,
> >> writable=False ),
> >> Field("desc_en",'reference description', readable=False,
> >> writable=False ),
> >> Field("desc_de",'reference description', readable=False,
> >> writable=False ),
> >> Field("desc_es",'reference description', readable=False,
> >> writable=False ),
> >> Field("categories", 'list:reference route_category'),
> >> Field("status", 'string', requires=IS_IN_SET(['public',
> >> 'review', 'work_in_progress']), default='work_in_progress'),
> >> auth.signature,
> >> format='%(name)s (%(id)s)'
> >> )
> >>
> >> what should I do?
> >>
> >> --
> >> Paolo
> >>
> >> Il 13.06.2012 20:48 Niphlod ha scritto:
> >>> yes, you're using "end" as column namethis is not allowed in
> >>> postgres and oracle, and also on mssql.
> >>>
> >>> Bites me every time, but actually a good practice would be to
> >> create
> >>> the model within a connection made this way:
> >>>
> >>> db = DAL(uri, check_reserved_keyword=['all'])
> >>>
> >>> it will stop your model creation if find some tablename/columnname
> >>
> >>> that is not allowed in all db engines, so you can have a
> >> "portable"
> >>> db
> >>> schema between different db engines. Better start with a
> >> "universally
> >>> accepted" model also in local developments with sqlite than having
> >>
> >>> those errors when trying to migrate to your production server.
> >>>
> >>> PS: check_reserved_keyword take a list of db engines to check
> >>> against,
> >>> so if you're worried only to "be portable" within postgres and
> >>> sqlite,
> >>> you can replace ['all'] with ['common', 'sqlite', 'postgres']
> >
> >
> > Links:
> > --
> > [1] http://route.name
>
Re: [web2py] Switching sqlite/postgres
In my long walks migrate sqlite to postgres, I detected that the
import_csv has problems when there is this relationship and the table
with no records, made the importation into the hand he behaves well.
but definitely we know and know that Massimo has import_csv problems
sqlite -> to <- Postgresql.
Ovidio Marinho Falcao Neto
Web Developer
ovidio...@gmail.com
ovidiomari...@itjp.net.br
ITJP - itjp.net.br
83 8826 9088 - Oi
83 9334 0266 - Claro
Brasil
2012/6/13 Massimo Di Pierro :
> Sorry. I asked a stupid question. Not sure what is wrong. I am looking into
> possibilities.
>
> On Wednesday, 13 June 2012 17:38:07 UTC-5, Gabriella Canavesi wrote:
>>
>> Yes off course :-)
>> It is just below the route table definition, here:
>>
>> db.define_table('pictures',
>> Field("picture", "upload", requires=(IS_NOT_EMPTY(),
>> IS_IMAGE())),
>> Field("normal", "upload", IS_IMAGE()),
>> Field("thumbnail", "upload", IS_IMAGE()),
>> Field('route_id', db.route, default=db.route.id),
>> Field('user_id', db.auth_user, default=auth.user_id),
>> Field('created_on', 'datetime', default=request.now),
>> Field('description', 'text', requires=[IS_TRIM(),
>> IS_LENGTH(140,error_message=T('Warning, description too long'))])
>> )
>>
>> paolo
>>
>> Il 14.06.2012 00:34 Massimo Di Pierro ha scritto:
>> > Do you have a
>> >
>> > db.define_table('pictures',)
>> >
>> > ?
>> >
>> > On Wednesday, 13 June 2012 17:21:52 UTC-5, Gabriella Canavesi wrote:
>> >
>> >> Hi Niphlod, thanks you for the suggestion I solved even that error
>> >> and
>> >> I added
>> >> check_reserved=['common','postgres', 'sqlite'] to avoid future
>> >> problems.
>> >>
>> >> Unfortunately now I am getting an error saying:
>> >>
>> >> relation "pictures" does not exist
>> >>
>> >> which is the first field set as reference.
>> >> the table definition is the following:
>> >>
>> >> db.define_table('route',
>> >> Field('name', 'string',
>> >> requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'route.name [1]'),
>> >> IS_CAPITALIZE()]),
>> >> Field('user_id', db.auth_user, default=auth.user_id),
>> >> Field("slug", "string",
>> >> requires=[IS_SLUG(),IS_NOT_IN_DB(db,'route.slug')]),
>> >> Field('length','double', default=random.randint(0, 50)),
>> >> Field('height','integer', default=random.randint(0, 50)),
>> >> Field('max_elevation','integer', default=random.randint(0,
>> >> 50)),
>> >> Field('max_elevation_lat', 'double'),
>> >> Field('max_elevation_lgt', 'double'),
>> >> Field('min_elevation','integer', default=random.randint(0,
>> >> 50)),
>> >> Field('min_elevation_lat', 'double'),
>> >> Field('min_elevation_lgt', 'double'),
>> >> Field('start', 'string'),
>> >> Field('endC', 'string'),
>> >> Field('photo_id', 'reference pictures', readable=False,
>> >> writable=False),
>> >> Field("signs", 'reference signs', readable=False,
>> >> writable=False),
>> >> Field("kml", "upload",
>> >> uploadfolder=request.folder+'uploads/kml', uploadseparate=True),
>> >> Field("svg_altitude", 'string', readable=False,
>> >> writable=False),
>> >> Field("desc_it",'reference description', readable=False,
>> >> writable=False ),
>> >> Field("desc_en",'reference description', readable=False,
>> >> writable=False ),
>> >> Field("desc_de",'reference description', readable=False,
>> >> writable=False ),
>> >> Field("desc_es",'reference description', readable=False,
>> >> writable=False ),
>> >> Field("categories", 'list:reference route_category'),
>> >> Field("status", 'string', requires=IS_IN_SET(['public',
>> >> 'review', 'work_in_progress']), default='work_in_progress'),
>> >> auth.signature,
>> >> format='%(name)s (%(id)s)'
>> >> )
>> >>
>> >> what should I do?
>> >>
>> >> --
>> >> Paolo
>> >>
>> >> Il 13.06.2012 20:48 Niphlod ha scritto:
>> >>> yes, you're using "end" as column namethis is not allowed in
>> >>> postgres and oracle, and also on mssql.
>> >>>
>> >>> Bites me every time, but actually a good practice would be to
>> >> create
>> >>> the model within a connection made this way:
>> >>>
>> >>> db = DAL(uri, check_reserved_keyword=['all'])
>> >>>
>> >>> it will stop your model creation if find some tablename/columnname
>> >>
>> >>> that is not allowed in all db engines, so you can have a
>> >> "portable"
>> >>> db
>> >>> schema between different db engines. Better start with a
>> >> "universally
>> >>> accepted" model also in local developments with sqlite than having
>> >>
>> >>> those errors when trying to migrate to your production server.
>> >>>
>> >>> PS: check_reserved_keyword take a list of db engines to check
>> >>> against,
>> >>> so if you're worried only to "be portable" within postgres and
>> >>> sqlite,
>> >>> you can replace ['all'] with ['common', 'sqlite', 'postgres']
>> >
>> >
>> > Links:
>> > --
>> > [1] http://route.name
Re: [web2py] Switching sqlite/postgres
Sorry. I asked a stupid question. Not sure what is wrong. I am looking into
possibilities.
On Wednesday, 13 June 2012 17:38:07 UTC-5, Gabriella Canavesi wrote:
>
> Yes off course :-)
> It is just below the route table definition, here:
>
> db.define_table('pictures',
> Field("picture", "upload", requires=(IS_NOT_EMPTY(),
> IS_IMAGE())),
> Field("normal", "upload", IS_IMAGE()),
> Field("thumbnail", "upload", IS_IMAGE()),
> Field('route_id', db.route, default=db.route.id),
> Field('user_id', db.auth_user, default=auth.user_id),
> Field('created_on', 'datetime', default=request.now),
> Field('description', 'text', requires=[IS_TRIM(),
> IS_LENGTH(140,error_message=T('Warning, description too long'))])
> )
>
> paolo
>
> Il 14.06.2012 00:34 Massimo Di Pierro ha scritto:
> > Do you have a
> >
> > db.define_table('pictures',)
> >
> > ?
> >
> > On Wednesday, 13 June 2012 17:21:52 UTC-5, Gabriella Canavesi wrote:
> >
> >> Hi Niphlod, thanks you for the suggestion I solved even that error
> >> and
> >> I added
> >> check_reserved=['common','postgres', 'sqlite'] to avoid future
> >> problems.
> >>
> >> Unfortunately now I am getting an error saying:
> >>
> >> relation "pictures" does not exist
> >>
> >> which is the first field set as reference.
> >> the table definition is the following:
> >>
> >> db.define_table('route',
> >> Field('name', 'string',
> >> requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'route.name [1]'),
> >> IS_CAPITALIZE()]),
> >> Field('user_id', db.auth_user, default=auth.user_id),
> >> Field("slug", "string",
> >> requires=[IS_SLUG(),IS_NOT_IN_DB(db,'route.slug')]),
> >> Field('length','double', default=random.randint(0, 50)),
> >> Field('height','integer', default=random.randint(0, 50)),
> >> Field('max_elevation','integer', default=random.randint(0,
> >> 50)),
> >> Field('max_elevation_lat', 'double'),
> >> Field('max_elevation_lgt', 'double'),
> >> Field('min_elevation','integer', default=random.randint(0,
> >> 50)),
> >> Field('min_elevation_lat', 'double'),
> >> Field('min_elevation_lgt', 'double'),
> >> Field('start', 'string'),
> >> Field('endC', 'string'),
> >> Field('photo_id', 'reference pictures', readable=False,
> >> writable=False),
> >> Field("signs", 'reference signs', readable=False,
> >> writable=False),
> >> Field("kml", "upload",
> >> uploadfolder=request.folder+'uploads/kml', uploadseparate=True),
> >> Field("svg_altitude", 'string', readable=False,
> >> writable=False),
> >> Field("desc_it",'reference description', readable=False,
> >> writable=False ),
> >> Field("desc_en",'reference description', readable=False,
> >> writable=False ),
> >> Field("desc_de",'reference description', readable=False,
> >> writable=False ),
> >> Field("desc_es",'reference description', readable=False,
> >> writable=False ),
> >> Field("categories", 'list:reference route_category'),
> >> Field("status", 'string', requires=IS_IN_SET(['public',
> >> 'review', 'work_in_progress']), default='work_in_progress'),
> >> auth.signature,
> >> format='%(name)s (%(id)s)'
> >> )
> >>
> >> what should I do?
> >>
> >> --
> >> Paolo
> >>
> >> Il 13.06.2012 20:48 Niphlod ha scritto:
> >>> yes, you're using "end" as column namethis is not allowed in
> >>> postgres and oracle, and also on mssql.
> >>>
> >>> Bites me every time, but actually a good practice would be to
> >> create
> >>> the model within a connection made this way:
> >>>
> >>> db = DAL(uri, check_reserved_keyword=['all'])
> >>>
> >>> it will stop your model creation if find some tablename/columnname
> >>
> >>> that is not allowed in all db engines, so you can have a
> >> "portable"
> >>> db
> >>> schema between different db engines. Better start with a
> >> "universally
> >>> accepted" model also in local developments with sqlite than having
> >>
> >>> those errors when trying to migrate to your production server.
> >>>
> >>> PS: check_reserved_keyword take a list of db engines to check
> >>> against,
> >>> so if you're worried only to "be portable" within postgres and
> >>> sqlite,
> >>> you can replace ['all'] with ['common', 'sqlite', 'postgres']
> >
> >
> > Links:
> > --
> > [1] http://route.name
>
Re: [web2py] Switching sqlite/postgres
Yes off course :-)
It is just below the route table definition, here:
db.define_table('pictures',
Field("picture", "upload", requires=(IS_NOT_EMPTY(),
IS_IMAGE())),
Field("normal", "upload", IS_IMAGE()),
Field("thumbnail", "upload", IS_IMAGE()),
Field('route_id', db.route, default=db.route.id),
Field('user_id', db.auth_user, default=auth.user_id),
Field('created_on', 'datetime', default=request.now),
Field('description', 'text', requires=[IS_TRIM(),
IS_LENGTH(140,error_message=T('Warning, description too long'))])
)
paolo
Il 14.06.2012 00:34 Massimo Di Pierro ha scritto:
Do you have a
db.define_table('pictures',)
?
On Wednesday, 13 June 2012 17:21:52 UTC-5, Gabriella Canavesi wrote:
Hi Niphlod, thanks you for the suggestion I solved even that error
and
I added
check_reserved=['common','postgres', 'sqlite'] to avoid future
problems.
Unfortunately now I am getting an error saying:
relation "pictures" does not exist
which is the first field set as reference.
the table definition is the following:
db.define_table('route',
Field('name', 'string',
requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'route.name [1]'),
IS_CAPITALIZE()]),
Field('user_id', db.auth_user, default=auth.user_id),
Field("slug", "string",
requires=[IS_SLUG(),IS_NOT_IN_DB(db,'route.slug')]),
Field('length','double', default=random.randint(0, 50)),
Field('height','integer', default=random.randint(0, 50)),
Field('max_elevation','integer', default=random.randint(0,
50)),
Field('max_elevation_lat', 'double'),
Field('max_elevation_lgt', 'double'),
Field('min_elevation','integer', default=random.randint(0,
50)),
Field('min_elevation_lat', 'double'),
Field('min_elevation_lgt', 'double'),
Field('start', 'string'),
Field('endC', 'string'),
Field('photo_id', 'reference pictures', readable=False,
writable=False),
Field("signs", 'reference signs', readable=False,
writable=False),
Field("kml", "upload",
uploadfolder=request.folder+'uploads/kml', uploadseparate=True),
Field("svg_altitude", 'string', readable=False,
writable=False),
Field("desc_it",'reference description', readable=False,
writable=False ),
Field("desc_en",'reference description', readable=False,
writable=False ),
Field("desc_de",'reference description', readable=False,
writable=False ),
Field("desc_es",'reference description', readable=False,
writable=False ),
Field("categories", 'list:reference route_category'),
Field("status", 'string', requires=IS_IN_SET(['public',
'review', 'work_in_progress']), default='work_in_progress'),
auth.signature,
format='%(name)s (%(id)s)'
)
what should I do?
--
Paolo
Il 13.06.2012 20:48 Niphlod ha scritto:
yes, you're using "end" as column namethis is not allowed in
postgres and oracle, and also on mssql.
Bites me every time, but actually a good practice would be to
create
the model within a connection made this way:
db = DAL(uri, check_reserved_keyword=['all'])
it will stop your model creation if find some tablename/columnname
that is not allowed in all db engines, so you can have a
"portable"
db
schema between different db engines. Better start with a
"universally
accepted" model also in local developments with sqlite than having
those errors when trying to migrate to your production server.
PS: check_reserved_keyword take a list of db engines to check
against,
so if you're worried only to "be portable" within postgres and
sqlite,
you can replace ['all'] with ['common', 'sqlite', 'postgres']
Links:
--
[1] http://route.name
Re: [web2py] Switching sqlite/postgres
Do you have a
db.define_table('pictures',)
?
On Wednesday, 13 June 2012 17:21:52 UTC-5, Gabriella Canavesi wrote:
>
> Hi Niphlod, thanks you for the suggestion I solved even that error and
> I added
> check_reserved=['common','postgres', 'sqlite'] to avoid future
> problems.
>
> Unfortunately now I am getting an error saying:
>
> relation "pictures" does not exist
>
> which is the first field set as reference.
> the table definition is the following:
>
> db.define_table('route',
> Field('name', 'string',
>requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'route.name'),
> IS_CAPITALIZE()]),
> Field('user_id', db.auth_user, default=auth.user_id),
> Field("slug", "string",
> requires=[IS_SLUG(),IS_NOT_IN_DB(db,'route.slug')]),
> Field('length','double', default=random.randint(0, 50)),
> Field('height','integer', default=random.randint(0, 50)),
> Field('max_elevation','integer', default=random.randint(0,
> 50)),
> Field('max_elevation_lat', 'double'),
> Field('max_elevation_lgt', 'double'),
> Field('min_elevation','integer', default=random.randint(0,
> 50)),
> Field('min_elevation_lat', 'double'),
> Field('min_elevation_lgt', 'double'),
> Field('start', 'string'),
> Field('endC', 'string'),
> Field('photo_id', 'reference pictures', readable=False,
> writable=False),
> Field("signs", 'reference signs', readable=False,
> writable=False),
> Field("kml", "upload",
> uploadfolder=request.folder+'uploads/kml', uploadseparate=True),
> Field("svg_altitude", 'string', readable=False,
> writable=False),
> Field("desc_it",'reference description', readable=False,
> writable=False ),
> Field("desc_en",'reference description', readable=False,
> writable=False ),
> Field("desc_de",'reference description', readable=False,
> writable=False ),
> Field("desc_es",'reference description', readable=False,
> writable=False ),
> Field("categories", 'list:reference route_category'),
> Field("status", 'string', requires=IS_IN_SET(['public',
> 'review', 'work_in_progress']), default='work_in_progress'),
> auth.signature,
> format='%(name)s (%(id)s)'
> )
>
> what should I do?
>
> --
> Paolo
>
>
> Il 13.06.2012 20:48 Niphlod ha scritto:
> > yes, you're using "end" as column namethis is not allowed in
> > postgres and oracle, and also on mssql.
> >
> > Bites me every time, but actually a good practice would be to create
> > the model within a connection made this way:
> >
> > db = DAL(uri, check_reserved_keyword=['all'])
> >
> > it will stop your model creation if find some tablename/columnname
> > that is not allowed in all db engines, so you can have a "portable"
> > db
> > schema between different db engines. Better start with a "universally
> > accepted" model also in local developments with sqlite than having
> > those errors when trying to migrate to your production server.
> >
> > PS: check_reserved_keyword take a list of db engines to check
> > against,
> > so if you're worried only to "be portable" within postgres and
> > sqlite,
> > you can replace ['all'] with ['common', 'sqlite', 'postgres']
>
>
>
Re: [web2py] Switching sqlite/postgres
Hi Niphlod, thanks you for the suggestion I solved even that error and
I added
check_reserved=['common','postgres', 'sqlite'] to avoid future
problems.
Unfortunately now I am getting an error saying:
relation "pictures" does not exist
which is the first field set as reference.
the table definition is the following:
db.define_table('route',
Field('name', 'string',
requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'route.name'),
IS_CAPITALIZE()]),
Field('user_id', db.auth_user, default=auth.user_id),
Field("slug", "string",
requires=[IS_SLUG(),IS_NOT_IN_DB(db,'route.slug')]),
Field('length','double', default=random.randint(0, 50)),
Field('height','integer', default=random.randint(0, 50)),
Field('max_elevation','integer', default=random.randint(0,
50)),
Field('max_elevation_lat', 'double'),
Field('max_elevation_lgt', 'double'),
Field('min_elevation','integer', default=random.randint(0,
50)),
Field('min_elevation_lat', 'double'),
Field('min_elevation_lgt', 'double'),
Field('start', 'string'),
Field('endC', 'string'),
Field('photo_id', 'reference pictures', readable=False,
writable=False),
Field("signs", 'reference signs', readable=False,
writable=False),
Field("kml", "upload",
uploadfolder=request.folder+'uploads/kml', uploadseparate=True),
Field("svg_altitude", 'string', readable=False,
writable=False),
Field("desc_it",'reference description', readable=False,
writable=False ),
Field("desc_en",'reference description', readable=False,
writable=False ),
Field("desc_de",'reference description', readable=False,
writable=False ),
Field("desc_es",'reference description', readable=False,
writable=False ),
Field("categories", 'list:reference route_category'),
Field("status", 'string', requires=IS_IN_SET(['public',
'review', 'work_in_progress']), default='work_in_progress'),
auth.signature,
format='%(name)s (%(id)s)'
)
what should I do?
--
Paolo
Il 13.06.2012 20:48 Niphlod ha scritto:
yes, you're using "end" as column namethis is not allowed in
postgres and oracle, and also on mssql.
Bites me every time, but actually a good practice would be to create
the model within a connection made this way:
db = DAL(uri, check_reserved_keyword=['all'])
it will stop your model creation if find some tablename/columnname
that is not allowed in all db engines, so you can have a "portable"
db
schema between different db engines. Better start with a "universally
accepted" model also in local developments with sqlite than having
those errors when trying to migrate to your production server.
PS: check_reserved_keyword take a list of db engines to check
against,
so if you're worried only to "be portable" within postgres and
sqlite,
you can replace ['all'] with ['common', 'sqlite', 'postgres']
Re: [web2py] Switching sqlite/postgres
Need to see the code near this line which is causing the error: "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py", line 252, in format='%(name)s (%(id)s)' It might be missing a comma or parentheses. On Wednesday, June 13, 2012 7:08:51 AM UTC-7, Gabriella Canavesi wrote: > > Johann thanks, it worked! > but now I got a new error :( > > Ticket: > Traceback (most recent call last): >File "/home/paolo/Desktop/git/web2py/gluon/restricted.py", line 205, > in restricted > exec ccode in environment >File > "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py", line > 252, in > format='%(name)s (%(id)s)' >File "/home/paolo/Desktop/git/web2py/gluon/dal.py", line 6731, in > define_table > polymodel=polymodel) >File "/home/paolo/Desktop/git/web2py/gluon/dal.py", line 795, in > create_table > self.create_sequence_and_triggers(query,table) >File "/home/paolo/Desktop/git/web2py/gluon/dal.py", line 2175, in > create_sequence_and_triggers > self.execute(query) >File "/home/paolo/Desktop/git/web2py/gluon/dal.py", line 1489, in > execute > return self.log_execute(*a, **b) >File "/home/paolo/Desktop/git/web2py/gluon/dal.py", line 1483, in > log_execute > ret = self.cursor.execute(*a, **b) > ProgrammingError: syntax error at or near "end" > LINE 15: end VARCHAR(512), > > The sql.log is attached. > > Paolo > > > Il 13.06.2012 15:53 Johann Spies ha scritto: > > On 13 June 2012 14:06, Paolo wrote: > > > >> Hi Johann, > >> Yes, I started the db in postgres is totally empty. > >> As y suggested I've removed all the files in databases/ but I am > >> still getting the same error. > >> > >> What do you mean with "drop all the app-related-tables" ? > > > > in pgadmin3 or in psql: > > > > drop table > > > > for all the tables in the database that can be linked to your app. > > > > But don't do this if you have data in there that you want to keep. > > > > Did you try the migrate = False option? or fake-migrate = True? > > > > Regards > > Johann > > -- > Paolo > > timestamp: 2012-06-13T16:02:38.294192 > CREATE TABLE auth_user( > id SERIAL PRIMARY KEY, > first_name VARCHAR(128), > last_name VARCHAR(128), > email VARCHAR(512), > password VARCHAR(512), > registration_key VARCHAR(512), > reset_password_key VARCHAR(512), > registration_id VARCHAR(512), > nickname VARCHAR(512), > full_name VARCHAR(512), > city VARCHAR(512), > country VARCHAR(512), > picture VARCHAR(32768), > caption_picture TEXT, > thumbnail VARCHAR(32768), > slugNickname VARCHAR(512), > slugFullname VARCHAR(512), > language TEXT, > bio VARCHAR(512), > ui_notifications CHAR(1), > blog_url VARCHAR(512), > public CHAR(1), > show_community CHAR(1), > show_multimedia CHAR(1), > show_review CHAR(1), > show_question CHAR(1) > ); > success! > timestamp: 2012-06-13T16:02:38.492421 > CREATE TABLE auth_group( > id SERIAL PRIMARY KEY, > role VARCHAR(512), > description TEXT > ); > success! > timestamp: 2012-06-13T16:02:38.599654 > CREATE TABLE auth_membership( > id SERIAL PRIMARY KEY, > user_id INTEGER REFERENCES auth_user(id) ON DELETE CASCADE, > group_id INTEGER REFERENCES auth_group(id) ON DELETE CASCADE > ); > success! > timestamp: 2012-06-13T16:02:38.668436 > CREATE TABLE auth_permission( > id SERIAL PRIMARY KEY, > group_id INTEGER REFERENCES auth_group(id) ON DELETE CASCADE, > name VARCHAR(512), > table_name VARCHAR(512), > record_id INTEGER > ); > success! > timestamp: 2012-06-13T16:02:38.752233 > CREATE TABLE auth_event( > id SERIAL PRIMARY KEY, > time_stamp TIMESTAMP, > client_ip VARCHAR(512), > user_id INTEGER REFERENCES auth_user(id) ON DELETE CASCADE, > origin VARCHAR(512), > description TEXT > ); > success! > timestamp: 2012-06-13T16:02:38.835278 > CREATE TABLE auth_cas( > id SERIAL PRIMARY KEY, > user_id INTEGER REFERENCES auth_user(id) ON DELETE CASCADE, > created_on TIMESTAMP, > service VARCHAR(512), > ticket VARCHAR(512), > renew CHAR(1) > ); > success! > timestamp: 2012-06-13T16:02:38.930963 > CREATE TABLE route( > id SERIAL PRIMARY KEY, > name VARCHAR(512), > user_id INTEGER REFERENCES auth_user(id) ON DELETE CASCADE, > slug VARCHAR(512), > length FLOAT8, > height INTEGER, > max_elevation INTEGER, > max_elevation_lat FLOAT8, > max_elevation_lgt FLOAT8, > min_elevation INTEGER, > min_elevation_lat FLOAT8, > min_elevation_lgt FLOAT8, > start VARCHAR(512), > end VARCHAR(512), > photo_id INTEGER REFERENCES pictures(id) ON DELETE CASCADE, > signs INTEGER REFERENCES signs(id) ON DELETE CASCADE, > kml VARCHAR(512), > svg_altitude VARCHAR(512), > desc_it INTEGER REFERENCES description(id) ON DELETE CASCADE, > desc_en INTEGER REFERENCES descri
Re: [web2py] Switching sqlite/postgres
yes, you're using "end" as column namethis is not allowed in postgres and oracle, and also on mssql. Bites me every time, but actually a good practice would be to create the model within a connection made this way: db = DAL(uri, check_reserved_keyword=['all']) it will stop your model creation if find some tablename/columnname that is not allowed in all db engines, so you can have a "portable" db schema between different db engines. Better start with a "universally accepted" model also in local developments with sqlite than having those errors when trying to migrate to your production server. PS: check_reserved_keyword take a list of db engines to check against, so if you're worried only to "be portable" within postgres and sqlite, you can replace ['all'] with ['common', 'sqlite', 'postgres']
Re: [web2py] Switching sqlite/postgres
Johann thanks, it worked! but now I got a new error :( Ticket: Traceback (most recent call last): File "/home/paolo/Desktop/git/web2py/gluon/restricted.py", line 205, in restricted exec ccode in environment File "/home/paolo/Dropbox/git/web2py/applications/bikend/models/db.py", line 252, in format='%(name)s (%(id)s)' File "/home/paolo/Desktop/git/web2py/gluon/dal.py", line 6731, in define_table polymodel=polymodel) File "/home/paolo/Desktop/git/web2py/gluon/dal.py", line 795, in create_table self.create_sequence_and_triggers(query,table) File "/home/paolo/Desktop/git/web2py/gluon/dal.py", line 2175, in create_sequence_and_triggers self.execute(query) File "/home/paolo/Desktop/git/web2py/gluon/dal.py", line 1489, in execute return self.log_execute(*a, **b) File "/home/paolo/Desktop/git/web2py/gluon/dal.py", line 1483, in log_execute ret = self.cursor.execute(*a, **b) ProgrammingError: syntax error at or near "end" LINE 15: end VARCHAR(512), The sql.log is attached. Paolo Il 13.06.2012 15:53 Johann Spies ha scritto: On 13 June 2012 14:06, Paolo wrote: Hi Johann, Yes, I started the db in postgres is totally empty. As y suggested I've removed all the files in databases/ but I am still getting the same error. What do you mean with "drop all the app-related-tables" ? in pgadmin3 or in psql: drop table for all the tables in the database that can be linked to your app. But don't do this if you have data in there that you want to keep. Did you try the migrate = False option? or fake-migrate = True? Regards Johann -- Paolotimestamp: 2012-06-13T16:02:38.294192 CREATE TABLE auth_user( id SERIAL PRIMARY KEY, first_name VARCHAR(128), last_name VARCHAR(128), email VARCHAR(512), password VARCHAR(512), registration_key VARCHAR(512), reset_password_key VARCHAR(512), registration_id VARCHAR(512), nickname VARCHAR(512), full_name VARCHAR(512), city VARCHAR(512), country VARCHAR(512), picture VARCHAR(32768), caption_picture TEXT, thumbnail VARCHAR(32768), slugNickname VARCHAR(512), slugFullname VARCHAR(512), language TEXT, bio VARCHAR(512), ui_notifications CHAR(1), blog_url VARCHAR(512), public CHAR(1), show_community CHAR(1), show_multimedia CHAR(1), show_review CHAR(1), show_question CHAR(1) ); success! timestamp: 2012-06-13T16:02:38.492421 CREATE TABLE auth_group( id SERIAL PRIMARY KEY, role VARCHAR(512), description TEXT ); success! timestamp: 2012-06-13T16:02:38.599654 CREATE TABLE auth_membership( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES auth_user(id) ON DELETE CASCADE, group_id INTEGER REFERENCES auth_group(id) ON DELETE CASCADE ); success! timestamp: 2012-06-13T16:02:38.668436 CREATE TABLE auth_permission( id SERIAL PRIMARY KEY, group_id INTEGER REFERENCES auth_group(id) ON DELETE CASCADE, name VARCHAR(512), table_name VARCHAR(512), record_id INTEGER ); success! timestamp: 2012-06-13T16:02:38.752233 CREATE TABLE auth_event( id SERIAL PRIMARY KEY, time_stamp TIMESTAMP, client_ip VARCHAR(512), user_id INTEGER REFERENCES auth_user(id) ON DELETE CASCADE, origin VARCHAR(512), description TEXT ); success! timestamp: 2012-06-13T16:02:38.835278 CREATE TABLE auth_cas( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES auth_user(id) ON DELETE CASCADE, created_on TIMESTAMP, service VARCHAR(512), ticket VARCHAR(512), renew CHAR(1) ); success! timestamp: 2012-06-13T16:02:38.930963 CREATE TABLE route( id SERIAL PRIMARY KEY, name VARCHAR(512), user_id INTEGER REFERENCES auth_user(id) ON DELETE CASCADE, slug VARCHAR(512), length FLOAT8, height INTEGER, max_elevation INTEGER, max_elevation_lat FLOAT8, max_elevation_lgt FLOAT8, min_elevation INTEGER, min_elevation_lat FLOAT8, min_elevation_lgt FLOAT8, start VARCHAR(512), end VARCHAR(512), photo_id INTEGER REFERENCES pictures(id) ON DELETE CASCADE, signs INTEGER REFERENCES signs(id) ON DELETE CASCADE, kml VARCHAR(512), svg_altitude VARCHAR(512), desc_it INTEGER REFERENCES description(id) ON DELETE CASCADE, desc_en INTEGER REFERENCES description(id) ON DELETE CASCADE, desc_de INTEGER REFERENCES description(id) ON DELETE CASCADE, desc_es INTEGER REFERENCES description(id) ON DELETE CASCADE, categories TEXT, status VARCHAR(512), is_active CHAR(1), created_on TIMESTAMP, created_by INTEGER REFERENCES auth_user(id) ON DELETE CASCADE, modified_on TIMESTAMP, modified_by INTEGER REFERENCES auth_user(id) ON DELETE CASCADE );
Re: [web2py] Switching sqlite/postgres
On 13 June 2012 14:06, Paolo wrote: > Hi Johann, > Yes, I started the db in postgres is totally empty. > As y suggested I've removed all the files in databases/ but I am still > getting the same error. > > What do you mean with "drop all the app-related-tables" ? > in pgadmin3 or in psql: drop table for all the tables in the database that can be linked to your app. But don't do this if you have data in there that you want to keep. Did you try the migrate = False option? or fake-migrate = True? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
Re: [web2py] Switching sqlite/postgres
Hi Johann,
Yes, I started the db in postgres is totally empty.
As y suggested I've removed all the files in databases/ but I am still
getting the same error.
What do you mean with "drop all the app-related-tables" ?
currently I am saving the db with db.export_to_csv_file()
Regards,
paolo
Il 13.06.2012 12:08 Johann Spies ha scritto:
On 13 June 2012 10:55, Paolo wrote:
Hi all,
I am getting several errors switching from sqlite to postgres
Are you starting out on PostgreSQL with an empty database?
my connection string for sqlite was:
db = DAL('sqlite://storage.sqlite', migrate=True)
while for postgres is:
db = DAL('postgres://web2py:web2py@localhost:5432/mydb',
migrate=True)
so far, I got tickets saying
ProgrammingError: relation "auth_user" already exists
Which means that DAL could not create the table 'auth_user' because
there was one already.
If you start with an empty database, drop all the app-related-tables
in Postgresql and remove all the files in
/web2py/applications/your_app/database
Otherwise if all the tables already in the PostgreSQL-database, try
starting your database connection with 'migrate = False' or specify
the migrate option per table.
Regards
Johann
--
Paolo
Re: [web2py] Switching sqlite/postgres
On 13 June 2012 10:55, Paolo wrote:
> Hi all,
> I am getting several errors switching from sqlite to postgres
>
Are you starting out on PostgreSQL with an empty database?
> my connection string for sqlite was:
> db = DAL('sqlite://storage.sqlite', migrate=True)
> while for postgres is:
> db = DAL('postgres://web2py:web2py@**localhost:5432/mydb', migrate=True)
>
> so far, I got tickets saying
> ProgrammingError: relation "auth_user" already exists
>
>
Which means that DAL could not create the table 'auth_user' because there
was one already.
If you start with an empty database, drop all the app-related-tables in
Postgresql and remove all the files in
/web2py/applications/your_app/database
Otherwise if all the tables already in the PostgreSQL-database, try
starting your database connection with 'migrate = False' or specify the
migrate option per table.
Regards
Johann
--
Because experiencing your loyal love is better than life itself,
my lips will praise you. (Psalm 63:3)
