Ho to maintain the sequence serial numbers for table row id's even if we delete any row from table.+

[sense red]

Hi Everyone,

   I am facing the issue like.

I have created the database table with
db.define_table('testcases',Field('Testcase_Name',requires=IS_NOT_EMPTY()),
                Field('Time_stamp','datetime',requires=IS_NOT_EMPTY()))

controller code:

def testcases():
    form = SQLFORM(db.testcases).process()
    grid = SQLFORM.smartgrid(db.testcases)
    return locals()

I have inserted 10 testcases in to above "testcase" table, If I delete the 5th testcase then the sequence of the row id's are missing for example:(1,2,3,4,5,6,7,8,9,10) after deleting the 5th row then it was displaying as (1,2,3,4,6,7,8,9,10)
my requirement is to display as(1,2,3,4,5,6,7,8,9).

Please give me the solution to resolve the issue. 

[Encompass solutions]

The id field sequences like this so that any referencing items don't get screwed up.

You would need to renumber the sequence, that's a bad thing.  What is it your wanting to do actually?

If you simply query the items they are sorted by id value.

[Anthony]

The id field in the table is an auto-incrementing integer field used as the table's primary key, so we want to leave that alone (in any case, every time you delete a record, you wouldn't want to have to update all records that come later in the sequence). So, rather than rely on data in the records themselves, you would want to generate sequence numbers in the display only. Unfortunately, the grid does not provide an easy way to do this, as the "links" argument as well as virtual fields (both of which can be used to add custom columns to the grid) have no access to the sequence within the current grid display. So, if you need this and want to use the grid, you would have to manipulate the grid after it has been created via (a) the server-side DOM or (b) Javascript.

Another option would be to replace the table inside the grid DOM with your own custom table, which you can construct using grid.rows (which is the Rows object returned by the database query).

Finally, note that there are some SQL tricks you can use to have the database generate sequence numbers for the query results, but the web2py grid provides no way for you to specify the necessary custom SQL code, so you wouldn't be able to use the grid with this method (unless you use this method to create a temporary table or view for which you create a DAL model, and then use that model as the basis for the grid).

So, the bottom line is that it's complicated, so you should decide whether you really need sequence numbers in the grid. If you just don't like seeing the non-sequential ids in the grid, you can simply suppress the display of the id column altogether.

Anthony