CODEWITHSUNDEEP
javaandroidsqlite
CrossV4
CrossV4

Reputation: 13

Android Studio Sqllite autoincrement reset

db.execSQL("CREATE TABLE " +DBTable0+ "("+ROW2+" INTEGER PRIMARY KEY AUTOINCREMENT, "+ROW3+" VARCHAR NOT NULL, "+ROW0+" TEXT NOT NULL, "+ROW1+" VARCHAR NOT NULL)");

Here is my datebase's create table command.

I want to reset ROW2's values.

For Example i have 5 record on this table.

1, save1, This is a Save1, 11.25

2, save2, This is a Save2, 23.48

3, save3, This is a Save3, 09.45

4, save4, This is a Save4, 11.55

5, save5, This is a Save5, 21.00

I want to delete save2. When i delete it or other delete i want to reset ROW2 indexes, so

1, save1, This is a Save1, 11.25

2, save3, This is a Save3, 09.45

3, save4, This is a Save4, 11.55

4, save5, This is a Save5, 21.00

Like this ...

How i can do it .?

Upvotes: 0

Views: 244

Answers (2)

MikeT
MikeT

Reputation: 56983

To reset the rowid assigned to compensate for deleted row's when AUTOINCREMENT is used you have to make two changes:-

  1. You would have to alter (UPDATE) the rowid or an alias of the rowid of each row that is out of sequence to following the new sequence and
  2. then you would have to alter (UPDATE) the respective row in the system table sqlite_sequence to the highest rowid allocated.

This is not at all advisable and will greatly compound the inefficiencies of using AUTOINCREMENT (when there is a very good chance that you do not need to use AUTOINCREMENT).

  • SQLite Autoincrement includes

  • The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

In fact it is inadvisable to ever rely upon the rowid being anything other than what SQlite assigns

  • (<column_name> INTEGER PRIMARY KEY (with or without AUTOINCREMENT) makes <column_name> and alias of the rowid).

###You say :-

I want to reset ROW2's values.

I would suggest re-evaluating your want to only wanting this if there is in fact a need, which in all likliehood, there isn't.

###You then say

I'll select the records via ListViews. Then i can delete and update the records on i selected item. So when i delete any records in the middless. the id's are mixing and deleting false records

If you use a CursorAdapter, e.g. SimpleCursorAdapter, the 4th parameter of onItemClick and onItemLongClick is the id.

Noting that to use a CursorAdapter a column named _id must be present in the Cursor that is the source of the ListView and that column should contain the rowid. You can use BaseColumns._ID, which is a constant that has the value _id.

Generally you'd define the column in the table to be _id INTEGER PRIMARY KEY

Alternately you could use rowid AS _id, * in which case the column _id would be additional to all the other columns.

this and other options/explantions for other adapters can be found here

##Working Example

However, if you insist then the following example does what I believe you are asking.

Note this example does the same (effectively) for 2 tables.

  • The first table table1 uses AUTOINCREMENT
  • The second table table2 doesn't have AUTOINCREMENT coded but the results are identical (other than sqlite_sequence is not amended as there is no need as there is no row in sqlite_sequence due to AUTOINCREMENT nor being coded).

The main code is in the Database Helper (subclass of SQLiteOpenHelper) namely DBHelper.java :-

public class DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;

    public static final String TBL_TABLE1 = "table1";
    public static final String TBL_TABLE2 = "table2";
    public static final String COL_TABLE_COL1 = "col1";
    public static final String COL_TABLE_COL2 = "col2";
    public static final String COL_TABLE_COL3 = "col3";

    private static final String crt_table1_sql = "CREATE TABLE IF NOT EXISTS " + TBL_TABLE1 + "(" +
            COL_TABLE_COL1 + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            COL_TABLE_COL2 + " TEXT NOT NULL," +
            COL_TABLE_COL3 + " TEXT NOT NULL" +
            ")";
    private static final String crt_table2_sql = "CREATE TABLE IF NOT EXISTS " + TBL_TABLE2 + "(" +
            COL_TABLE_COL1 + " INTEGER PRIMARY KEY," +
            COL_TABLE_COL2 + " TEXT NOT NULL," +
            COL_TABLE_COL3 + " TEXT NOT NULL" +
            ")";

    SQLiteDatabase mDB;

    public DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.disableWriteAheadLogging();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(crt_table1_sql);
        db.execSQL(crt_table2_sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    public void insert(String col2, String col3) {
        ContentValues cv = new ContentValues();
        cv.put(COL_TABLE_COL2,col2);
        cv.put(COL_TABLE_COL3,col3);
        mDB.beginTransaction();
        mDB.insert(TBL_TABLE1,null,cv);
        mDB.insert(TBL_TABLE2,null,cv);
        mDB.setTransactionSuccessful();
        mDB.endTransaction();
    }

    public void deleteByValues(String col2, String col3) {
        String whereclause = COL_TABLE_COL2 + "=? AND " + COL_TABLE_COL3 + "=?";
        String[] args = new String[]{col2,col3};
        mDB.beginTransaction();
        mDB.delete(TBL_TABLE1,whereclause,args);
        mDB.delete(TBL_TABLE2,whereclause,args);
        rationaliseCol1Values();
        mDB.setTransactionSuccessful();
        mDB.endTransaction();
    }

    private void rationaliseCol1Values() {
        ContentValues cv = new ContentValues();
        Cursor csr = mDB.query(TBL_TABLE1,null,null,null,null,null,COL_TABLE_COL1 + " ASC");

        int rowcount = csr.getCount();
        long expected_id = 1;
        long current_id;
        String where_clause = COL_TABLE_COL1 + "=?";
        String[] args = new String[1];

        while (csr.moveToNext()) {
            current_id = csr.getLong(csr.getColumnIndex(COL_TABLE_COL1));
            if (current_id != expected_id) {
                cv.clear();
                cv.put(COL_TABLE_COL1,expected_id);
                args[0] = String.valueOf(current_id);
                mDB.update(TBL_TABLE1,cv,where_clause,args);
                mDB.update(TBL_TABLE2,cv,where_clause,args);
            }
            expected_id++;
        }
        csr.close();
        // Now adjust sqlite_sequence
        where_clause = "name=?";
        args[0] = TBL_TABLE1;
        cv.clear();
        cv.put("seq",String.valueOf(rowcount));
        mDB.update("sqlite_sequence",cv,where_clause,args);
    }

    public void logTableRows(int stage) {

        String tablenamne_column = "tablename";    
        Cursor[] csr = new Cursor[] {
                mDB.query(TBL_TABLE1,new String[]{"'table1' AS " + tablenamne_column + ",*"},null,null,null,null,null),
                mDB.query(TBL_TABLE2,new String[]{"'table2' AS " + tablenamne_column + ",*"}, null,null,null,null,null)
        };
        MergeCursor csr3 = new MergeCursor(csr);
        StringBuilder sb = new StringBuilder("Data in both tables consists of " + String.valueOf(csr3.getCount()) + " rows :-");

        while (csr3.moveToNext()) {
            sb.append(
                    "\n\tTableName = " + csr3.getString(csr3.getColumnIndex(tablenamne_column)
                    )
            ).append(" " + COL_TABLE_COL1 + " value is " + csr3.getString(csr3.getColumnIndex(COL_TABLE_COL1))
            ).append(" " + COL_TABLE_COL2 + " value is " + csr3.getString(csr3.getColumnIndex(COL_TABLE_COL2))
            ).append(" " + COL_TABLE_COL3 + " value is" + csr3.getString(csr3.getColumnIndex(COL_TABLE_COL3))
            );
        }
        Log.d("DATA4STAGE" + String.valueOf(stage),sb.toString());
    }
}
  • The core manipulation of the rowid (alias therefore i.e. COL1) is undertaken by the rationaliseCol1Values() method. This itself is invoked as part of the deleteByValues method.
    • if there were a method to delete according to id aka COL1 then this too would invoke the rationaliseCol1Values() method).
  • The logTableRows just exists so that the tables can be output to the log.

The invoking activity used for testing the above was :-

public class MainActivity extends AppCompatActivity {

    DBHelper mDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDBHlpr = new DBHelper(this);
        manipulateSomeData();
    }

    private void manipulateSomeData() {
        mDBHlpr.logTableRows(0);
        mDBHlpr.insert("TEST001","TESTING001");
        mDBHlpr.insert("TEST002","TESTING001");
        mDBHlpr.insert("TEST003","TESTING001");
        mDBHlpr.insert("TEST004","TESTING001");
        mDBHlpr.insert("TEST005","TESTING001");
        mDBHlpr.insert("TEST006","TESTING001");
        mDBHlpr.insert("TEST007","TESTING001");
        mDBHlpr.insert("TEST008","TESTING001");
        mDBHlpr.insert("TEST009","TESTING001");
        mDBHlpr.logTableRows(1);

        mDBHlpr.deleteByValues("TEST005","TESTING001");
        mDBHlpr.logTableRows(2);
        mDBHlpr.deleteByValues("TEST008","TESTING001");
        mDBHlpr.logTableRows(3);
        mDBHlpr.deleteByValues("TEST003","TESTIN001");
        mDBHlpr.logTableRows(4);
    }
}
  • This :-
    1. lists the rows (none when first run).
    2. Adds 9 rows with COL2 being unique (again only when first run).
    3. list all the 9 rows (1st run).
    4. deletes any rows that have values "TEST005" in COL2 AND "TESTING001" in COL3.
    5. lists the rows (note how COL1's sequence is maintained).
    6. deletes any rows that have values "TEST008" in COL2 AND "TESTING001" in COL3.
  1. lists the rows (note how COL1's sequence is maintained).
  2. deletes any rows that have values "TEST003" in COL2 AND "TESTIN001" in COL3. None do due to the typo (missing G)
  3. lists the rows (note how COL1's sequence is maintained).

The Results output to the log being :-

2018-12-31 12:43:21.618 2269-2269/so53976714.so53976714 D/DATA4STAGE0: Data in both tables consists of 0 rows :-
2018-12-31 12:43:21.657 2269-2269/so53976714.so53976714 D/DATA4STAGE1: Data in both tables consists of 18 rows :-
        TableName = table1 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table1 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table1 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table1 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table1 col1 value is 5 col2 value is TEST005 col3 value isTESTING001
        TableName = table1 col1 value is 6 col2 value is TEST006 col3 value isTESTING001
        TableName = table1 col1 value is 7 col2 value is TEST007 col3 value isTESTING001
        TableName = table1 col1 value is 8 col2 value is TEST008 col3 value isTESTING001
        TableName = table1 col1 value is 9 col2 value is TEST009 col3 value isTESTING001
        TableName = table2 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table2 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table2 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table2 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table2 col1 value is 5 col2 value is TEST005 col3 value isTESTING001
        TableName = table2 col1 value is 6 col2 value is TEST006 col3 value isTESTING001
        TableName = table2 col1 value is 7 col2 value is TEST007 col3 value isTESTING001
        TableName = table2 col1 value is 8 col2 value is TEST008 col3 value isTESTING001
        TableName = table2 col1 value is 9 col2 value is TEST009 col3 value isTESTING001
2018-12-31 12:43:21.666 2269-2269/so53976714.so53976714 D/DATA4STAGE2: Data in both tables consists of 16 rows :-
        TableName = table1 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table1 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table1 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table1 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table1 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table1 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table1 col1 value is 7 col2 value is TEST008 col3 value isTESTING001
        TableName = table1 col1 value is 8 col2 value is TEST009 col3 value isTESTING001
        TableName = table2 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table2 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table2 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table2 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table2 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table2 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table2 col1 value is 7 col2 value is TEST008 col3 value isTESTING001
        TableName = table2 col1 value is 8 col2 value is TEST009 col3 value isTESTING001
2018-12-31 12:43:21.675 2269-2269/so53976714.so53976714 D/DATA4STAGE3: Data in both tables consists of 14 rows :-
        TableName = table1 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table1 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table1 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table1 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table1 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table1 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table1 col1 value is 7 col2 value is TEST009 col3 value isTESTING001
        TableName = table2 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table2 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table2 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table2 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table2 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table2 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table2 col1 value is 7 col2 value is TEST009 col3 value isTESTING001
2018-12-31 12:43:21.681 2269-2269/so53976714.so53976714 D/DATA4STAGE4: Data in both tables consists of 14 rows :-
        TableName = table1 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table1 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table1 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table1 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table1 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table1 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table1 col1 value is 7 col2 value is TEST009 col3 value isTESTING001
        TableName = table2 col1 value is 1 col2 value is TEST001 col3 value isTESTING001
        TableName = table2 col1 value is 2 col2 value is TEST002 col3 value isTESTING001
        TableName = table2 col1 value is 3 col2 value is TEST003 col3 value isTESTING001
        TableName = table2 col1 value is 4 col2 value is TEST004 col3 value isTESTING001
        TableName = table2 col1 value is 5 col2 value is TEST006 col3 value isTESTING001
        TableName = table2 col1 value is 6 col2 value is TEST007 col3 value isTESTING001
        TableName = table2 col1 value is 7 col2 value is TEST009 col3 value isTESTING001

#NOTE using the above is NOT recommended

Upvotes: 1

Shawn
Shawn

Reputation: 52449

The whole point of the AUTOINCREMENT keyword is to never re-use a rowid. It stores the highest assigned one for a table, and when inserting a new row that doesn't have a rowid explicitly given in the insert statement, picks a number higher than that stored one. Without AUTOINCREMENT, it just chooses a number higher than the current maximum rowid in the table (And if the maximum is the largest possible signed 64-bit integer, it picks a few smaller ones at random hoping for a missing one before giving up).

If you want to recycle rowid numbers, you either have to find the missing ones manually and use them explicitly in UPDATE statements, or not have an INTEGER PRIMARY KEY column, and run VACUUM after deleting rows. Without the INTEGER PRIMARY KEY column aliasing the rowid, sqlite will rearrange them in that case. Or you could copy the table's rows, minus the INTEGER PRIMARY KEY column, into a new table to get new rowids. All of these are horribly inefficient approaches and not desirable. Live with gaps from deleted rows.

Mandatory reading to understand rowid, INTEGER PRIMARY KEY columns, and AUTOINCREMENT:

https://www.sqlite.org/rowidtable.html

https://www.sqlite.org/lang_createtable.html#rowid

https://www.sqlite.org/autoinc.html

I'll select the records via ListViews. Then i can delete and update the records on i selected item. So when i delete any records in the middless. the id's are mixing and deleting false records

Sounds like you're assuming something's position a list is the same as a row's rowid? No wonder you're having problems. Keep explicit track of the rowid and use that when deleting the requested data. Classic case of an XY problem.

Upvotes: 2

Related Questions