I do not know. You need to try that. I think if you use keyed tables with primary_key=None most of it should work and if not should be easy to fix. Please if you make these patches use dal.py, not sql.py.
On Feb 5, 8:51 am, kralin <[email protected]> wrote: > you're welcome. > since I'm dealing with tables without primarykeys in legacy > is there some web2py "standard" behaviour for this case? > do you think that returning None instead of an id upon a table insert > is appropriate? is it going to break something else? > > On 5 Feb, 15:42, mdipierro <[email protected]> wrote: > > > Thank you Andrea! these will definitively go in. > > > Massimo > > > On Feb 5, 5:58 am, kralin <[email protected]> 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 > > -- You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
