please open a ticket and append the patch. Perhaps this was an oversight.
Massimo
On Monday, 5 March 2012 09:55:13 UTC-6, Bruce Dickey wrote:
>
>
> Hi all,
>
> I'm using PostgreSQL with schemas w/ web2py 1.97. The patch shown here
> from 2 years ago made that work.
>
> I just downloaded v1.99.7 and noticed that this patch is not implemented
> in /gluon/dal.py PostgreSQLAdapter.lastrowid().
>
> So I'm wondering what the outcome of this discussion was -- was some other
> change made? Or will this be added in the future?
>
> Thanks,
> Bruce
>
>
>
> On Friday, February 5, 2010 4:58:56 AM UTC-7, kralin wrote:
>>
>> since in the same schema there are many to many tables with no primary
>> key,
>> a further improvement to the one described above can permitt the DAL
>> to write also this table
>> with the side effect of returning None on an insert instead of the
>> inserted record id (since there is no one)
>>
>>
>> this write to all tables:
>>
>> def lastrowid(self,tablename):
>> self.execute("""SELECT column_default from
>> information_schema.columns where table_name='%s'; """%tablename)
>> column_default=self.cursor.fetchone()
>> if column_default[0]:#if there is no sequence in the table
>> first column
>> get_currval_string ='select ' +
>> column_default[0].replace('nextval', 'currval')
>> self.execute(get_currval_string)
>> return int(self.cursor.fetchone()[0])
>> else:
>> return None #or the appropriate value
>>
>> On 5 Feb, 12:22, kralin <[email protected]> wrote:
>> > Hi,
>> > In the last days I've been trying to use the web2py DAL to access a
>> > public DB shema widely used in bioinformatics (BioSQL,www.biosql.org).
>> >
>> > this schema does not follow web2py constraints, however most of the
>> > tables have a primarykey that can be set as the 'id' fields.
>> >
>> > howver web2py DAL as it is is only able to read data, but fails to
>> > write because it assumes that in the postgresql db, each sequence for
>> > the 'id' field is named
>> >
>> > ''%s_id_Seq''%tablename
>> >
>> > while this is the default postgresql behaviour, and works most of the
>> > time, it fails if the sequence has a different name in this case:
>> >
>> > ''%s_pk_Seq''%tablename
>> >
>> > so after a lot o googleing I end up to made the following modification
>> > to the PostgreSQLAdapter class in gluon.dal
>> >
>> > orginal:
>> >
>> > def lastrowid(self,tablename):
>> > self.execute("select currval('%s_id_Seq')" % tablename)
>> > return int(self.cursor.fetchone()[0])
>> >
>> > improved:
>> >
>> > def lastrowid(self,tablename):
>> > self.execute("""select column_default from
>> > information_schema.columns where table_name='%s'; """%tablename)
>> > get_currval_string ='select ' + self.cursor.fetchone()
>> > [0].replace('nextval', 'currval')
>> > self.execute(get_currval_string)
>> > return int(self.cursor.fetchone()[0])
>> >
>> > this way, the sequence name is dynamically retrieved from the db, and
>> > should work on any situation.
>> >
>> > Hope this help!
>> >
>> > Andrea
>>
>>
> On Friday, February 5, 2010 4:58:56 AM UTC-7, kralin wrote:
>>
>> since in the same schema there are many to many tables with no primary
>> key,
>> a further improvement to the one described above can permitt the DAL
>> to write also this table
>> with the side effect of returning None on an insert instead of the
>> inserted record id (since there is no one)
>>
>>
>> this write to all tables:
>>
>> def lastrowid(self,tablename):
>> self.execute("""SELECT column_default from
>> information_schema.columns where table_name='%s'; """%tablename)
>> column_default=self.cursor.fetchone()
>> if column_default[0]:#if there is no sequence in the table
>> first column
>> get_currval_string ='select ' +
>> column_default[0].replace('nextval', 'currval')
>> self.execute(get_currval_string)
>> return int(self.cursor.fetchone()[0])
>> else:
>> return None #or the appropriate value
>>
>> On 5 Feb, 12:22, kralin <[email protected]> wrote:
>> > Hi,
>> > In the last days I've been trying to use the web2py DAL to access a
>> > public DB shema widely used in bioinformatics (BioSQL,www.biosql.org).
>> >
>> > this schema does not follow web2py constraints, however most of the
>> > tables have a primarykey that can be set as the 'id' fields.
>> >
>> > howver web2py DAL as it is is only able to read data, but fails to
>> > write because it assumes that in the postgresql db, each sequence for
>> > the 'id' field is named
>> >
>> > ''%s_id_Seq''%tablename
>> >
>> > while this is the default postgresql behaviour, and works most of the
>> > time, it fails if the sequence has a different name in this case:
>> >
>> > ''%s_pk_Seq''%tablename
>> >
>> > so after a lot o googleing I end up to made the following modification
>> > to the PostgreSQLAdapter class in gluon.dal
>> >
>> > orginal:
>> >
>> > def lastrowid(self,tablename):
>> > self.execute("select currval('%s_id_Seq')" % tablename)
>> > return int(self.cursor.fetchone()[0])
>> >
>> > improved:
>> >
>> > def lastrowid(self,tablename):
>> > self.execute("""select column_default from
>> > information_schema.columns where table_name='%s'; """%tablename)
>> > get_currval_string ='select ' + self.cursor.fetchone()
>> > [0].replace('nextval', 'currval')
>> > self.execute(get_currval_string)
>> > return int(self.cursor.fetchone()[0])
>> >
>> > this way, the sequence name is dynamically retrieved from the db, and
>> > should work on any situation.
>> >
>> > Hope this help!
>> >
>> > Andrea
>>
>>
> On Friday, February 5, 2010 4:58:56 AM UTC-7, kralin wrote:
>>
>> since in the same schema there are many to many tables with no primary
>> key,
>> a further improvement to the one described above can permitt the DAL
>> to write also this table
>> with the side effect of returning None on an insert instead of the
>> inserted record id (since there is no one)
>>
>>
>> this write to all tables:
>>
>> def lastrowid(self,tablename):
>> self.execute("""SELECT column_default from
>> information_schema.columns where table_name='%s'; """%tablename)
>> column_default=self.cursor.fetchone()
>> if column_default[0]:#if there is no sequence in the table
>> first column
>> get_currval_string ='select ' +
>> column_default[0].replace('nextval', 'currval')
>> self.execute(get_currval_string)
>> return int(self.cursor.fetchone()[0])
>> else:
>> return None #or the appropriate value
>>
>> On 5 Feb, 12:22, kralin <[email protected]> wrote:
>> > Hi,
>> > In the last days I've been trying to use the web2py DAL to access a
>> > public DB shema widely used in bioinformatics (BioSQL,www.biosql.org).
>> >
>> > this schema does not follow web2py constraints, however most of the
>> > tables have a primarykey that can be set as the 'id' fields.
>> >
>> > howver web2py DAL as it is is only able to read data, but fails to
>> > write because it assumes that in the postgresql db, each sequence for
>> > the 'id' field is named
>> >
>> > ''%s_id_Seq''%tablename
>> >
>> > while this is the default postgresql behaviour, and works most of the
>> > time, it fails if the sequence has a different name in this case:
>> >
>> > ''%s_pk_Seq''%tablename
>> >
>> > so after a lot o googleing I end up to made the following modification
>> > to the PostgreSQLAdapter class in gluon.dal
>> >
>> > orginal:
>> >
>> > def lastrowid(self,tablename):
>> > self.execute("select currval('%s_id_Seq')" % tablename)
>> > return int(self.cursor.fetchone()[0])
>> >
>> > improved:
>> >
>> > def lastrowid(self,tablename):
>> > self.execute("""select column_default from
>> > information_schema.columns where table_name='%s'; """%tablename)
>> > get_currval_string ='select ' + self.cursor.fetchone()
>> > [0].replace('nextval', 'currval')
>> > self.execute(get_currval_string)
>> > return int(self.cursor.fetchone()[0])
>> >
>> > this way, the sequence name is dynamically retrieved from the db, and
>> > should work on any situation.
>> >
>> > Hope this help!
>> >
>> > Andrea
>>
>>
> On Friday, February 5, 2010 4:58:56 AM UTC-7, kralin wrote:
>>
>> since in the same schema there are many to many tables with no primary
>> key,
>> a further improvement to the one described above can permitt the DAL
>> to write also this table
>> with the side effect of returning None on an insert instead of the
>> inserted record id (since there is no one)
>>
>>
>> this write to all tables:
>>
>> def lastrowid(self,tablename):
>> self.execute("""SELECT column_default from
>> information_schema.columns where table_name='%s'; """%tablename)
>> column_default=self.cursor.fetchone()
>> if column_default[0]:#if there is no sequence in the table
>> first column
>> get_currval_string ='select ' +
>> column_default[0].replace('nextval', 'currval')
>> self.execute(get_currval_string)
>> return int(self.cursor.fetchone()[0])
>> else:
>> return None #or the appropriate value
>>
>> On 5 Feb, 12:22, kralin <[email protected]> wrote:
>> > Hi,
>> > In the last days I've been trying to use the web2py DAL to access a
>> > public DB shema widely used in bioinformatics (BioSQL,www.biosql.org).
>> >
>> > this schema does not follow web2py constraints, however most of the
>> > tables have a primarykey that can be set as the 'id' fields.
>> >
>> > howver web2py DAL as it is is only able to read data, but fails to
>> > write because it assumes that in the postgresql db, each sequence for
>> > the 'id' field is named
>> >
>> > ''%s_id_Seq''%tablename
>> >
>> > while this is the default postgresql behaviour, and works most of the
>> > time, it fails if the sequence has a different name in this case:
>> >
>> > ''%s_pk_Seq''%tablename
>> >
>> > so after a lot o googleing I end up to made the following modification
>> > to the PostgreSQLAdapter class in gluon.dal
>> >
>> > orginal:
>> >
>> > def lastrowid(self,tablename):
>> > self.execute("select currval('%s_id_Seq')" % tablename)
>> > return int(self.cursor.fetchone()[0])
>> >
>> > improved:
>> >
>> > def lastrowid(self,tablename):
>> > self.execute("""select column_default from
>> > information_schema.columns where table_name='%s'; """%tablename)
>> > get_currval_string ='select ' + self.cursor.fetchone()
>> > [0].replace('nextval', 'currval')
>> > self.execute(get_currval_string)
>> > return int(self.cursor.fetchone()[0])
>> >
>> > this way, the sequence name is dynamically retrieved from the db, and
>> > should work on any situation.
>> >
>> > Hope this help!
>> >
>> > Andrea
>>
>>
