Switching sqlite/postgres

[Paolo]

Hi all,
I am getting several errors switching from sqlite to postgres
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

even with a new and fresh welcome app.
The worst thing is that after receiving this error, the application is
blocked and I have to restart web2py killing its process.

I am using web2py trunk with postgres 9.1 (ubuntu)

what should I do?
regards
--
Paolo

[Johann Spies]

On 13 June 2012 10:55, Paolo <ilv...@inventati.org> 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)

[Paolo]

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 <ilv...@inventati.org [1]> 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

[Johann Spies]

On 13 June 2012 14:06, Paolo <ilv...@inventati.org> 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 <tablename>

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]

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 <module>
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 <ilv...@inventati.org [1]> 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 <tablename>
>
> 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

[Niphlod]

yes, you're using "end" as column name....this 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']

[pbreit]

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 <module> 
     format='%(name)s (%(id)s)' 

It might be missing a comma or parentheses.

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 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
);

[Paolo]

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

[Massimo Di Pierro]

Do you have a 

db.define_table('pictures',....)

?

[Paolo]

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]'),

> Links:
> ------
> [1] http://route.name

[Massimo Di Pierro]

Sorry. I asked a stupid question. Not sure what is wrong. I am looking into possibilities.

[Ovidio Marinho]

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
             ovid...@gmail.com
          ovidio...@itjp.net.br
                 ITJP - itjp.net.br
               83   8826 9088 - Oi
               83   9334 0266 - Claro
                        Brasil



2012/6/13 Massimo Di Pierro <massimo....@gmail.com>:

[Cliff Kachinske]

Gabriella,

Does the problem go away if you define the pictures table before the route table?

[Niphlod]

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.

[Paolo]

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

[Niphlod]

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.

[Paolo]

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

[Niphlod]

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)

[Paolo]

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:-)

[Niphlod]

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.....

[pbreit]

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

[Cliff Kachinske]

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