Emptying a SQLite table

[Uncle Reg]

I have a SQLite database with 2 tables that I will periodically need to delete all records from the tables in preparation for importing updated data. Is there an easy way to empty the tables while maintaining the table structure (field definitions)? Something similar to emptying a Dbf table using the Delphi emptytable function or the FoxPro Delete All/Pack or Zap commands.

[Leledumbo]

Standard SQL:

Code: [Select]

TRUNCATE TABLE <Table name here>;

[Uncle Reg]

SQLScript1 in the procedure below is a TSQLScript from the SQLdb package.

When I click the button, I get an error message that says: near "truncate": syntax error

procedure TForm1.TestBtnClick(Sender: TObject);
begin
  SQLScript1.Script.Text:='truncate albums; truncate songs';
  SQLScript1.Execute;
end;

[taazz]

http://www.tutorialspoint.com/sqlite/sqlite_truncate_table.htm

[BigChimp]

Yes, taazz, seems not everybody supports SQL2008 (if I can believe Wikipedia on that). AFAIR, Firebird doesn't support it either.

[BigChimp]

So what taazz suggested:

Code: [Select]

procedure TForm1.TestBtnClick(Sender: TObject);
begin
  SQLScript1.Script.Text:='delete from albums; delete from songs;';
  SQLScript1.Execute;
end;
(code untested, don't know if it compiles)
or you could use a regular sqlquery and .execsql it - once for each delete command

[Leledumbo]

http://www.tutorialspoint.com/sqlite/sqlite_truncate_table.htm

Good finding. I never thought TRUNCATE TABLE is unsupported in SQLite3.

[Uncle Reg]

So what taazz suggested:

Code: [Select]

procedure TForm1.TestBtnClick(Sender: TObject);
begin
  SQLScript1.Script.Text:='delete from albums; delete from songs;';
  SQLScript1.Execute;
end;
(code untested, don't know if it compiles)
or you could use a regular sqlquery and .execsql it - once for each delete command

Yes, I had already done that after reading that tutorial website, and it did compile OK, but it didn't do anything until I added another line:

  SQLScript1.Script.Text:='delete from albums; delete from songs;';
  SQLScript1.Execute;
  SQLTransaction1.Commit;

After I ran that, the tables appeared to be empty when I examined the database with SQLite Studio, but the file was still exactly the same size (approx 3.5Mb) as it was before all the records were deleted.

Then I remembered reading on that tutorial website about using the vacuum command after using the delete from table command to clear unused space, so I changed it to this:

  SQLScript1.Script.Text:='delete from albums; delete from songs; vacuum;';    <--- added the vacuum command to the script
  SQLScript1.Execute;
  SQLTransaction1.Commit;

That compiled OK too, but when I ran it, I got an error message: Cannot VACUUM from within a transaction.

When I googled that error message, I got a hit from an earlier post on this forum that listed a solution to that error, so I added it:

  SQLScript1.Script.Text:='delete from albums; delete from songs;';    <--- removed the vacuum command from the script
  SQLScript1.Execute;
  SQLTransaction1.Commit;
  albumsConnection.ExecuteDirect('End Transaction');
  albumsConnection.ExecuteDirect('vacuum');               <--- and added it back here
  albumsConnection.ExecuteDirect('Begin Transaction');

That worked perfectly. The file is now only 3072 bytes, the same size I get if I use SQLite Studio to erase all the data and vacuum the database.

So now it's on to figuring out how to import the updated data. From what I've looked at, it looks like the TSqlite3Dataset component might be easier to use. It looks like I can just use 2 datasets (and 2 datasources) to connect directly to the tables, then parse the updated data and use Append and Post to add new records to the tables.

Is it possible to mix TSqlite3Dataset and  the SQLdb components that I used above?

Would I have to completely disable the SQLdb components before connecting the TSqlite3Datasets, or could I connect them to the tables at design time and do the emptying procedure listed above using the SQLdb components while the tables were connected to the TSqlite3Datasets?

Or even better, could I do the above table emptying procedure also with TSqlite3Datasets? It does have a SQL property, and a ExecSQL procedure, and it looks like it might even have the ExecuteDirect procedure. That would eliminate having to use the SQLdb stuff at all.

Thanks to everybody for all the help so far, and I would very much appreciate any opinions that anybody might have on the above questions.

[taazz]

Use either TSQLiteDataset or sqlDB not both at the same to avoid collisions. SQLite is a single access library once a file is opened can not be opened again before the first one is closed. Those two libraries know nothing of each other and they will stable one upon the other.

[BigChimp]

Apart from taazz' remark
Don't know about sqlite, but all dbs I know will reuse any reserved but unused space.

Vacuuming/compressing/"cleaning" will remove that reserved space, yes, but if you are going to import new data, your db will expand again, making a vacuum after truncate/before import a bit useless.
Perhaps vacuum after the insertion of new data is done could be more useful... if it is needed at all?

[Caravelle]

SQLite is a single access library once a file is opened can not be opened again before the first one is closed.

Can you explain what you mean in more detail please ?  I have an sqlite database open 24/7 collecting data from a radio receiver using a commercial program.   While this is going on I can read and write to active tables in the database using several additional programs, my own and general sqlite utilities like SqliteExpert.  I can even do this across a network. They all access the database at the same time.   It's my understanding that sqlite is not a single access library except when it is actually being written to, and then access is managed by transactions.  See http://www.sqlite.org/faq.html#q5.  I have several Delphi programs which take advantage of this (using Disqlite3).  They have been doing this for over 5 years, day in, day out.  The odd thing is that I can't seem to get the Lazarus sqlite components to work the same way.  I get endless "database is locked" errors and I want to get to the bottom of this.   I really wish Disqlite3 could be ported to Lazarus.

I don't want to get involved in an argument, I'm just trying to convert my Delphi programs and need a better understanding of the issues, it seems.

Thanks

Caravelle

[LacaK]

In case of sqlDB, when you commit read or write operation(s) then locks are released.
So other application can access same file in same time as far as they do not access them in exactly one time.
I do not know TSQLite3Dataset, so I can not say how it handles transactions.

[mse]

Please don't forget to set <TSqlQuery>.packetrecords to -1.

[taazz]

SQLite is a single access library once a file is opened can not be opened again before the first one is closed.

Can you explain what you mean in more detail please ? 

A single access database is one that locks the database when a client has a transactions or a connection open. In most embedded databases that is the connection ee which client opened the file first, in SQLite it seems that it is the transaction. Any way the idea behind a single access database is that you open you read the data in to memory or write the data to the database you close so other processes can have access. I do not know when the sqlite allow multiple process to read from the same database the last time I checked I got the impression that they did not allow multiple processes to open the same file.

[Caravelle]

Taazz

Thank you for that explanation.

As the official sqlite site says at the url I quoted:

SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.

It has been this way at least since I started using sqlite3 in 2006.  Maybe sqlite 2 was different? Multiple programs can read data from the database simultaneously without using transactions. Writing requires a lock, and if you try to read during a period of writing you get a BUSY response - the answer is to detect this response and try again after a brief delay, as many times as it takes.   Components used to access sqlite need simple ways to detect and read sqlite's responses and act accordingly and reading should not require a transaction which locks up the database so there should be a simple  transaction-less facility for SELECT statements.

I'm sorry if this is bad news but it seems fundamental to the way sqlite3 works.  I am no expert, just an amateur who writes programs for specific personal tasks but I hope this helps in some way.  I keep reading in various messages here that sqlite is a single-access system  but all the while this is accepted as true the Lazarus sqlite components will never be developed to take advantage of sqlite's special features - and work well with existing multi-access systems like the one I use, which is at the heart of the devices produced by http://www.kinetic-avionics.co.uk/.  These feed data into the database while at the same time the tables can be edited while being displayed on screen as both text and as a virtual radar screen.  3rd party programs also interface with it - all at the same time.

Caravelle

Caravelle