[SOLVED] Re-Initializing a SQLite3 Database

[1HuntnMan]

How would you correctly re-initialize a database?
I could just delete the Database and recreate everything.  Or drop then re-create all the tables, the same routine with the indexes. My issue is in developing the application and testing with made up records throughout the database tables, etc., I want to wipe-out all this test data. I could just delete the database file and recreate everything. But, thinking just drop the tables one-by-one and recreate all the tables like I did when I first created the database in the first place, still have that unit that I did in the beginning of the project. Then drop the indexes and recreate.

Question, when you drop the table are the indexes still okay as long as you recreate the tables with the exact same names? Or it appears to drop the table, drop it's indexes and then recreate the tables and indexes. I have the original unit I wrote that created the database in the beginning.

[JanRoza]

Why DROP a table and do everything over again?
Use the SQL DELETE command to delete all records in a table, that way the structure of the table stays intact.
See the SQLite homepage for a complete overview of all SQL commands (https://www.sqlite.org).

[1HuntnMan]

I want the autoincrement key field to be reset to 0...

[1HuntnMan]

I've looked at DELETE and VACUUM but can't figure out if after DELETE, weather any of the auto-increment fields are reset.

[cdbc]

Hi
· rename your db-file to e.g.: 'testdb.db3'
· use your unit, to create the whole 'shebang' in a new file with your db-name.
· now you have the chance to inspect your creation-sqls and compare the new
  db-file to the old saved working one with the test data.
· if you think of new features, you can always test them on your test-db --
  just change the databasename in object-inspector...
Anyway, that's what I'd do...
Regards Benny

[1HuntnMan]

cdbc, cool, I'll try that and let you know.  Thanks...

[GAN]

Hi, may be this help you.

https://www.designcise.com/web/tutorial/how-to-reset-autoincrement-number-sequence-in-sqlite

[Hansvb]

With SQllite you can reset the autoincrement fields so that they start again with 1 when you have deleted all records. I do that as follows. The table was created with ID as the autoincrement field:

Code: Pascal  [Select][+][-]

1.   SQL_CREATE_ITEMS = 'create table if not exists ' + ITEMS + ' (' +
2.                     'ID                 INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, ' +   <---- Autoincrement field
3.                     ...
4.  
5. Then I clean up the autoincrement fields with:
6.  
7. [code=pascal]procedure TAppDatabaseMaintainer.ResetTableAutoIncrement(const TableName:string);
8. begin
9.   try
10.     FSQLiteConnection.DatabaseName:= ConnectionFile;
11.     FSQLiteConnection.Open;
12.     FSQLTransaction.Active:= True;
13.  
14.     FSQLQuery.SQL.Text:= 'DELETE FROM sqlite_sequence WHERE name = :TABLE_NAME';  <--- here: this does the trick. see: https://sqlite.org/autoinc.html
15.     FSQLQuery.Params.ParamByName('TABLE_NAME').AsString:= TableName;
16.     FSQLQuery.ExecSQL;
17.  
18.     FSQLTransaction.Commit;
19.     FSQLiteConnection.Close;
20.  
21.     AddMessage(amtInfo, 'Reset auto-increment for table: ' + TableName);
22.   except
23.     on E:Exception do begin
24.       AddMessage(amtError, 'Failed to reset auto-increment: ' + E.Message);
25.     end;
26.   end;
27. end;  

[Hansvb]

Some more,

if you want to clean up all autoincrement:

Code: Pascal  [Select][+][-]

1. procedure TAppDatabaseMaintainer.ResetAllAutoIncrements;
2. begin
3.   try
4.     FSQLiteConnection.DatabaseName:= ConnectionFile;
5.     FSQLiteConnection.Open;
6.     FSQLTransaction.Active:= True;
7.  
8.     FSQLQuery.SQL.Text:= 'DELETE FROM sqlite_sequence';    <--- Here
9.     FSQLQuery.ExecSQL;
10.  
11.     FSQLTransaction.Commit;
12.     FSQLiteConnection.Close;
13.  
14.     AddMessage(amtInfo, 'Reset all auto-increment counters');
15.   except
16.     on E:EDatabaseError do begin
17.       AddMessage(amtError, 'Failed to reset auto-increments: ' + E.Message);
18.       MessageDlg('Error', 'Failed to reset auto-increments', mtError, [mbOK], 0);
19.     end;
20.   end;
21. end;

[Lutz Mändle]

Deleting a table from a SQLite3 database with resetting the autoincrement counter and compressing the file works for me with the following code sequence:

Code: Pascal  [Select][+][-]

1. ...
2.   VarConn.ExecuteDirect(Format('DELETE FROM "%s"',[sVarTableName]));
3.   VarConn.ExecuteDirect(Format('UPDATE "sqlite_sequence" SET "seq"=0 WHERE "name"=''%s''',[sVarTableName]));
4.   VarConn.ExecuteDirect('End Transaction');
5.   VarConn.ExecuteDirect('VACUUM');
6.   VarConn.ExecuteDirect('Begin Transaction');
7. ...
8.  

The string variable sVarTableName contains the actual name of the table to delete. VarConn is a TSQLConnector with the property ConnectorType set to SQLite3, a TSQLite3Connection should work alike.

[1HuntnMan]

Awesome, thanks all, just what I was looking for and the examples! GAN, thanks for the link.  Got it!

[Zvoni]

I want the autoincrement key field to be reset to 0...

Don't use AUTOINCREMENT in your CREATE TABLE-Statement.

As easy as that.

And yes: If you don't pass a Value for your ID in your INSERT, Sqlite will still count up automagically as long as your Primary Key is of Type Integer

Bottom Line: Don't use AUTOINCREMENT for your Primary Key ID on CREATE TABLE
You don't need it
It only creates overhead