I couldn't find anything in the PG docs. If I were to add another postgres
adapter that specifically handles databases created like this, is that
something you'd be interested in adding to web2py?
On Friday, March 11, 2011 4:42:11 PM UTC-5, Massimo Di Pierro wrote:
>
> Then web2py cannot do this (yet). You would need to create a view for
> your tables with lowercase table names or perhaps there is some
> obscure postgresql parameters to make the queries case sensitive
> (mysql has it).
>
> On Mar 11, 2:10 pm, Ross Peoples <ross.p...@gmail.com> wrote:
> > I should have mentioned that I already did this, so now my table
> definition matches the one you gave. It was after correcting the definition
> that I get this message. Again, the tables and fields were created using
> double-quotes (e.g. CREATE TABLE "GlobalSettings"...), so PostgreSQL
> requires that all queries use double-quotes when referring to tables and
> fields created this way.
> >
> > If I do a db()._select(db.GlobalSettings.ALL)...notice the underscore, I
> get:
> >
> > SELECT GlobalSettings.settingID, GlobalSettings.settingName,
> GlobalSettings.settingValue FROM GlobalSettings;
> >
> > But for PostgreSQL to accept the query, it would have to be:
> >
> > SELECT "GlobalSettings"."settingID", "GlobalSettings"."settingName",
> "GlobalSettings"."settingValue" FROM "GlobalSettings";
> >
> > This is because unless you specifically wrap table and field names with
> double-quotes, PostgreSQL automatically converts all table and field names
> to lowercase.
> >
> > On Mar 11, 2011, at 3:02 PM, Massimo Di Pierro wrote:
> >
> >
> >
> >
> >
> >
> >
> > > It cannot be
> >
> > > Field('"settingName"', length=255, unique=True),
> >
> > > must be
> >
> > > Field('settingName', length=255, unique=True),
> >
> > > you cannot have quotes in the file name.
> > > So it should be
> >
> > > db.define_table('GlobalSettings',
> > > Field('settingName', length=255, unique=True),
> > > Field('settingValue', 'text'),
> > > Field('settingID', 'id'),
> > > sequence_name='GlobalSettings_settingID_seq',
> > > migrate=False)
> >
> > > Case must match DB. migrate=False because it exists. I am not 100%
> > > sure but I think this should work.
> >
> > > On Mar 11, 12:56 pm, Ross Peoples <ross.p...@gmail.com> wrote:
> > >> After also adding migrate=False to the table definition, I now get
> this
> > >> error:
> >
> > >> ProgrammingError: relation "globalsettings" does not exist
> > >> LINE 1: ...ngs.settingName, GlobalSettings.settingValue FROM
> GlobalSett...
> >
> > >> On Thursday, March 10, 2011 3:02:25 PM UTC-5, Richard wrote:
> >
> > >>> remove the " from web2py model and add the sequence name that
> postgres
> > >>> create by default with a different name then web2py expect like this
> :
> >
> > >>> db.define_table('GlobalSettings',
> > >>> Field('settingName', length=255, unique=True),
> > >>> Field('settingValue', 'text'),
> > >>> Field('settingID', 'id'),
> > >>> sequence_name='GlobalSettings_settingID_seq'
> > >>> )
> >
> > >>> It should works...
> >
> > >>> Richard
> >
> > >>> On Thu, Mar 10, 2011 at 2:42 PM, Ross Peoples <ross...@gmail.com>
> wrote:
> >
> > >>>> I have a legacy PostgreSQL database that has its tables and field
> names
> > >>>> created using a case-sensitive means. Whenever I try to do a
> select(), it
> > >>>> returns no rows, and an insert() fails. This is the error that
> web2py gives:
> >
> > >>>> ProgrammingError: relation "globalsettings_id_seq" does not exist
> >
> > >>>> LINE 1: select currval('GlobalSettings_id_Seq')
> >
> > >>>> This is the actual table definition from pgAdmin3:
> >
> > >>>> CREATE TABLE "GlobalSettings"
> > >>>> (
> > >>>> "settingName" character varying(255) NOT NULL,
> > >>>> "settingValue" text NOT NULL,
> > >>>> "settingID" serial NOT NULL,
> > >>>> CONSTRAINT "GlobalSettings_pkey" PRIMARY KEY ("settingID")
> > >>>> )
> >
> > >>>> Attempting to define my table in web2py using double-quotes between
> > >>>> single-quotes, like this:
> >
> > >>>> db.define_table('"GlobalSettings"',
> > >>>> Field('"settingName"', length=255, unique=True),
> > >>>> Field('"settingValue"', 'text'),
> > >>>> Field('"settingID"', 'id')
> > >>>> )
> >
> > >>>> results in the following error message: SyntaxError: only
> [0-9a-zA-Z_] allowed
> > >>>> in table and field names, received "settingName"
