Delphi backup SQLite in-Memory to DB in file using TFDSQLiteBackup class
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
var MyPhysSQLiteDriverLink: TFDPhysSQLiteDriverLink; MyFDConnectionDisk : TFDConnection; MyFDConnectionMem : TFDConnection;
// // by default im using "all default classes configurations" //
procedure TForm1.FDSQLiteBackup1AfterExecute(Sender: TObject); begin Memo1.Lines.Add('after execute backup'); end;
procedure TForm1.FDSQLiteBackup1BeforeExecute(Sender: TObject); begin Memo1.Lines.Add('before execute backup'); end;
procedure TForm1.FDSQLiteBackup1Error(ASender, AInitiator: TObject; var AException: Exception); begin Memo1.Lines.Add('error backup: ' + AException.Message); end;
procedure TForm1.FDSQLiteBackup1Progress(ASender: TFDPhysDriverService; const AMessage: string); begin Memo1.Lines.Add(AMessage); end;
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
procedure TForm1.Button3Click(Sender: TObject); begin if MyFDConnectionMem.InTransaction then begin MyFDConnectionMem.Commit; // ShowMessage('mem Commited'); end; end;
procedure TForm1.BtnBackupDatabaseClick(Sender: TObject); var MyFDSQLiteBackup: TFDSQLiteBackup; begin if not(MyFDConnectionMem.CliObj = nil) then begin MyFDSQLiteBackup := TFDSQLiteBackup.Create(Self); try MyFDConnectionDisk.Close; // close me please! // MyFDSQLiteBackup.DriverLink := MyPhysSQLiteDriverLink; // MyFDSQLiteBackup.DatabaseObj := MyFDConnectionMem.CliObj; // in-memory // MyFDSQLiteBackup.DestDatabase := SQLiteDBDiskName; MyFDSQLiteBackup.DestMode := TSQLiteDatabaseMode.smCreate; // if not "exists", create! // // MyFDSQLiteBackup.WaitForLocks := true; // wait me ... by default! // MyFDSQLiteBackup.BusyTimeout := 100; // 10.000 default! // MyFDSQLiteBackup.Backup; // MyFDConnectionDisk.Open; MyFDConnectionDisk.GetTableNames('', '', '', ListBox2.Items); // // ListBox2.Items.Add('time: ' + TimeToStr(now)); // FDQuery3.Open('select * from MyTable1'); FDQuery4.Open('select * from MyTable2'); finally MyFDSQLiteBackup.Free; end; end; end;
end.
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
No "incremental backup", just "inserts"!
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
for update like "incremental", try another approach...
1) SQLite commands, like: INSERT OR REPLACE INTO table(column_list) VALUES(value_list);
UPDATE tablex SET fieldx=valuex WHERE <<conditional>> by SQL default
SELECT column_1 FROM table_1 WHERE column_1 = ( SELECT column_1 FROM table_2 );
2) for "catch" only new records, try: SELECT select_list1 FROM table1
INTERSECT
SELECT select_list2 FROM table2
3) for "catch" only records not contained in "right-table_list2"
SELECT select_list1 FROM table1 EXCEPT SELECT select_list2 FROM table2
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
NOTE: --- your "Backup resulted" will be always a "SQLite" db, then you can use it any other db!!!
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
1) use the "FDConnection.GetXXXXXX" functions to "walk" in your structure
GetCatalogNames - the catalog list; GetSchemaNames - the schema list; GetTableNames - the table and view list; GetFieldNames - the table field list; GetKeyFieldNames - the table primary key list; GetGeneratorNames - the generator / sequence list; GetPackageNames - the package list; GetStoredProcNames - the stored procedure list.
so, you can use "FOR ..." looping to create your SQL script
in SQLite exists this function: sqlite3_backup_init()
sqlite3_backup *sqlite3_backup_init( sqlite3 *pDest, /* Destination database handle */ const char *zDestName, /* Destination database name */ sqlite3 *pSource, /* Source database handle */ const char *zSourceName /* Source database name */ ); int sqlite3_backup_step(sqlite3_backup *p, int nPage); int sqlite3_backup_finish(sqlite3_backup *p); int sqlite3_backup_remaining(sqlite3_backup *p); int sqlite3_backup_pagecount(sqlite3_backup *p);
---------- uses sqlite3backup;
procedure BackupSQL(Conn: TSQLite3Connection; FileName: string); var f: string; BK: TSQLite3Backup; begin f := ChangeFileEXT(FileName, '') + ' [Backup ' + FormatDateTime('yyyy.mm.dd hh-nn-ss', Now) + '].sql'; BK := TSQLite3Backup.Create; BK.Backup(Conn, f); BK.Free; end
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
According to SQLite site this is not recommended (using CopyFile) https://www.sqlite.org/backup.html Quote Historically, backups (copies) of SQLite databases have been created using the following method: 1.Establish a shared lock on the database file using the SQLite API (i.e. the shell tool). 2.Copy the database file using an external tool (for example the unix 'cp' utility or the DOS 'copy' command). 3.Relinquish the shared lock on the database file obtained in step 1.
This procedure works well in many scenarios and is usually very fast. However, this technique has the following shortcomings: *Any database clients wishing to write to the database file while a backup is being created must wait until the shared lock is relinquished. *It cannot be used to copy data to or from in-memory databases. *If a power failure or operating system failure occurs while copying the database file the backup database may be corrupted following system recovery.
The Online Backup API was created to address these concerns. The online backup API allows the contents of one database to be copied into another database, overwriting the original contents of the target database. The copy operation may be done incrementally, in which case the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from. This allows other database users to continue uninterrupted while a backup of an online database is made.
And According to FPC wiki http://wiki.freepascal.org/SQLite#sqlite3backup
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3
int sqlite3_db_dump( sqlite3 *db, /* The database connection */ //不用说了绝壁可以用内存的 const char *zSchema, /* Which schema to dump. Usually "main". */ const char *zTable, /* Which table to dump. NULL means everything. */ int (*xCallback)(const char*,void*), /* Output sent to this callback */ void *pArg /* Second argument of the callback */ )
filedb open /home/make1/test.db success memdb open /home/make1/test.db success filedb creat table suc memdb creat table suc PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE testTable(ID TEXT,value); INSERT INTO testTable(rowid,ID,value) VALUES(1,'1234',500); INSERT INTO testTable(rowid,ID,value) VALUES(2,'1234',501); INSERT INTO testTable(rowid,ID,value) VALUES(3,'1234',502); INSERT INTO testTable(rowid,ID,value) VALUES(4,'1234',503); INSERT INTO testTable(rowid,ID,value) VALUES(5,'1234',504); INSERT INTO testTable(rowid,ID,value) VALUES(6,'1234',505); INSERT INTO testTable(rowid,ID,value) VALUES(7,'1234',506); INSERT INTO testTable(rowid,ID,value) VALUES(8,'1234',507); INSERT INTO testTable(rowid,ID,value) VALUES(9,'1234',508); INSERT INTO testTable(rowid,ID,value) VALUES(10,'1234',509); INSERT INTO testTable(rowid,ID,value) VALUES(11,'1234',510); INSERT INTO testTable(rowid,ID,value) VALUES(12,'1234',511); INSERT INTO testTable(rowid,ID,value) VALUES(13,'1234',512); INSERT INTO testTable(rowid,ID,value) VALUES(14,'1234',513); INSERT INTO testTable(rowid,ID,value) VALUES(15,'1234',514); INSERT INTO testTable(rowid,ID,value) VALUES(16,'1234',515); INSERT INTO testTable(rowid,ID,value) VALUES(17,'1234',516); INSERT INTO testTable(rowid,ID,value) VALUES(18,'1234',517); INSERT INTO testTable(rowid,ID,value) VALUES(19,'1234',518); INSERT INTO testTable(rowid,ID,value) VALUES(20,'1234',519); INSERT INTO testTable(rowid,ID,value) VALUES(21,'1234',520); INSERT INTO testTable(rowid,ID,value) VALUES(22,'1234',521); INSERT INTO testTable(rowid,ID,value) VALUES(23,'1234',522); INSERT INTO testTable(rowid,ID,value) VALUES(24,'1234',523); INSERT INTO testTable(rowid,ID,value) VALUES(25,'1234',524); INSERT INTO testTable(rowid,ID,value) VALUES(26,'1234',525); INSERT INTO testTable(rowid,ID,value) VALUES(27,'1234',526); INSERT INTO testTable(rowid,ID,value) VALUES(28,'1234',527); INSERT INTO testTable(rowid,ID,value) VALUES(29,'1234',528); INSERT INTO testTable(rowid,ID,value) VALUES(30,'1234',529); INSERT INTO testTable(rowid,ID,value) VALUES(31,'1234',530); INSERT INTO testTable(rowid,ID,value) VALUES(32,'1234',531); INSERT INTO testTable(rowid,ID,value) VALUES(33,'1234',532); INSERT INTO testTable(rowid,ID,value) VALUES(34,'1234',533); INSERT INTO testTable(rowid,ID,value) VALUES(35,'1234',534); INSERT INTO testTable(rowid,ID,value) VALUES(36,'1234',535); INSERT INTO testTable(rowid,ID,value) VALUES(37,'1234',536); INSERT INTO testTable(rowid,ID,value) VALUES(38,'1234',537); INSERT INTO testTable(rowid,ID,value) VALUES(39,'1234',538); INSERT INTO testTable(rowid,ID,value) VALUES(40,'1234',539); INSERT INTO testTable(rowid,ID,value) VALUES(41,'1234',540); INSERT INTO testTable(rowid,ID,value) VALUES(42,'1234',541); INSERT INTO testTable(rowid,ID,value) VALUES(43,'1234',542); INSERT INTO testTable(rowid,ID,value) VALUES(44,'1234',543); INSERT INTO testTable(rowid,ID,value) VALUES(45,'1234',544); INSERT INTO testTable(rowid,ID,value) VALUES(46,'1234',545); INSERT INTO testTable(rowid,ID,value) VALUES(47,'1234',546); INSERT INTO testTable(rowid,ID,value) VALUES(48,'1234',547); INSERT INTO testTable(rowid,ID,value) VALUES(49,'1234',548); INSERT INTO testTable(rowid,ID,value) VALUES(50,'1234',549); INSERT INTO testTable(rowid,ID,value) VALUES(51,'1234',550); INSERT INTO testTable(rowid,ID,value) VALUES(52,'1234',551); INSERT INTO testTable(rowid,ID,value) VALUES(53,'1234',552); INSERT INTO testTable(rowid,ID,value) VALUES(54,'1234',553); INSERT INTO testTable(rowid,ID,value) VALUES(55,'1234',554); INSERT INTO testTable(rowid,ID,value) VALUES(56,'1234',555); INSERT INTO testTable(rowid,ID,value) VALUES(57,'1234',556); INSERT INTO testTable(rowid,ID,value) VALUES(58,'1234',557); INSERT INTO testTable(rowid,ID,value) VALUES(59,'1234',558); INSERT INTO testTable(rowid,ID,value) VALUES(60,'1234',559); INSERT INTO testTable(rowid,ID,value) VALUES(61,'1234',560); INSERT INTO testTable(rowid,ID,value) VALUES(62,'1234',561); INSERT INTO testTable(rowid,ID,value) VALUES(63,'1234',562); INSERT INTO testTable(rowid,ID,value) VALUES(64,'1234',563); INSERT INTO testTable(rowid,ID,value) VALUES(65,'1234',564); INSERT INTO testTable(rowid,ID,value) VALUES(66,'1234',565); INSERT INTO testTable(rowid,ID,value) VALUES(67,'1234',566); INSERT INTO testTable(rowid,ID,value) VALUES(68,'1234',567); INSERT INTO testTable(rowid,ID,value) VALUES(69,'1234',568); INSERT INTO testTable(rowid,ID,value) VALUES(70,'1234',569); INSERT INTO testTable(rowid,ID,value) VALUES(71,'1234',570); INSERT INTO testTable(rowid,ID,value) VALUES(72,'1234',571); INSERT INTO testTable(rowid,ID,value) VALUES(73,'1234',572); INSERT INTO testTable(rowid,ID,value) VALUES(74,'1234',573); INSERT INTO testTable(rowid,ID,value) VALUES(75,'1234',574); INSERT INTO testTable(rowid,ID,value) VALUES(76,'1234',575); INSERT INTO testTable(rowid,ID,value) VALUES(77,'1234',576); INSERT INTO testTable(rowid,ID,value) VALUES(78,'1234',577); INSERT INTO testTable(rowid,ID,value) VALUES(79,'1234',578); INSERT INTO testTable(rowid,ID,value) VALUES(80,'1234',579); INSERT INTO testTable(rowid,ID,value) VALUES(81,'1234',580); INSERT INTO testTable(rowid,ID,value) VALUES(82,'1234',581); INSERT INTO testTable(rowid,ID,value) VALUES(83,'1234',582); INSERT INTO testTable(rowid,ID,value) VALUES(84,'1234',583); INSERT INTO testTable(rowid,ID,value) VALUES(85,'1234',584); INSERT INTO testTable(rowid,ID,value) VALUES(86,'1234',585); INSERT INTO testTable(rowid,ID,value) VALUES(87,'1234',586); INSERT INTO testTable(rowid,ID,value) VALUES(88,'1234',587); INSERT INTO testTable(rowid,ID,value) VALUES(89,'1234',588); INSERT INTO testTable(rowid,ID,value) VALUES(90,'1234',589); INSERT INTO testTable(rowid,ID,value) VALUES(91,'1234',590); INSERT INTO testTable(rowid,ID,value) VALUES(92,'1234',591); INSERT INTO testTable(rowid,ID,value) VALUES(93,'1234',592); INSERT INTO testTable(rowid,ID,value) VALUES(94,'1234',593); INSERT INTO testTable(rowid,ID,value) VALUES(95,'1234',594); INSERT INTO testTable(rowid,ID,value) VALUES(96,'1234',595); INSERT INTO testTable(rowid,ID,value) VALUES(97,'1234',596); INSERT INTO testTable(rowid,ID,value) VALUES(98,'1234',597); INSERT INTO testTable(rowid,ID,value) VALUES(99,'1234',598); INSERT INTO testTable(rowid,ID,value) VALUES(100,'1234',599); COMMIT; delete suc
----------------------------------------------
[alias] co = clone --recurse-submodules up = submodule update --init --recursiveupd = pullinfo = statusrest = reset --hard懒鬼提速https://www.cctry.com/>http://qalculate.github.io/downloads.htmlhttps://www.cctry.com/