Lazarus / Firebird question lazarus / 16-Databases ---------------------------------------------------------------------- fjabouley · 2016-09-09 07:36 Hi all !! First of all, sorry for my English... I have a lazarus client that connects to a firebird database. I gave a friend of mine the software, and installed the same database on his computer. I frequently change tables on the first database (adding, modifying triggers....) I wonder if there is a way to modify my friend's database schema/metadata (I added / modified some tables) without modifying its data ? (I managed to do that by making my friend's soft to run SQL commands (like ALTER/ADD...) but it's really complicated, and user unfriendly... I also formerly posted to know if there was a way to backup/restore metadata only (without altering data), but I didn't find a solution yet..) Thanks very much guys ---------------------------------------------------------------------- LacaK · 2016-09-09 07:41 I have never used it, but probably you can look at : http://fbexport.sourceforge.net/ FBCopy /A ---------------------------------------------------------------------- rvk · 2016-09-09 08:29 Quote from: LacaK on September 09, 2016, 09:41:46 amFBCopy /A It's a beginning but I doubt it will change existing fields or change existing triggers. If the changes are complex you'll need to do it manually. (even an advanced program wouldn't know if you changed a field-name to something else so pumping data over to a new database isn't an option either.) The way I do it... I have a create database-script in my resource so a new database can always be created.The database has a admin-table with one record (added in the script) in which I specify a version-number as default value.I also have a change-script (multiple versions separated by a specific line and version number) in my resource.When I change the main database, I change the main-script to reflect those changes (also changing the default version-number in case of a new database, 1.47 in the example) but I also add all the ALTER-commands (including the changing of that one record, also 1.47 in the example) in the change- script.When starting my program I check if the change-script has new version-numbers beyond my current DB_VERSION in the database and execute all commands in those new-version sections.A simple example... The main-script (only to be executed for a new database): Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i /********** ********************************************************************/S ET TERM ^ ;SET SQL DIALECT 3^SET AUTODDL ON^ CREATE TABLE ADMIN(  ID                    BIGINT,  DB_VERSION              NUMERIC(4,2) DEFAULT 1.47 NOT NULL,)^ /******************************************** **********************************//* other tables and commands                                                *//************************* *****************************************************/ COMMIT WORK;COM MIT^/***************************************************************** *************/ the change-script (only execute the lines below PASS=current version): Code: MySQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i /********** ********************************************************************// * PASS=1.45   23-02-2016                                                   *//************************************************************* *****************/SET TERM ^ ;ALTER TABLE TABLE_Y ADD FIELD_Y INTEGER^COMMIT^UPDATE ADMIN SET DB_VERSION=1.46^COMMIT^ /************* *****************************************************************//* PASS=1.46   03-05-2016                                                   *//*************************************************************** ***************/SET TERM ^ ;ALTER TABLE TABLE_X ADD FIELD_X INTEGER^COMMIT^CREATE TRIGGER TR_R_SELECTION FOR R_SELECTION ACTIVE BEFORE INSERT POSITION 0 ASBEGIN  IF ((NEW.ID is NULL) or (NEW.ID = 0)) THEN      NEW.ID = GEN_ID(GEN_R_SELECTION, 1);END^COMMIT^UPDATE ADMIN SET DB_VERSIE=1.47^COMMIT^ /************************************ ******************************************//* PASS=99  //Done                                                          *//***************** *************************************************************/ I know, I know... it's rather complicated but if you set it up once it is user friendly If you only have one other user (your friend) you could simplify things somewhat and supply an external script with all changes (in a file named by version) and execute this manually but with more users this will become quit confusing (to keep track who has done what version-changes already). ---------------------------------------------------------------------- ttomas · 2016-09-09 09:43 I use this tool http://www.clevercomponents.com/downloads/dbc/dbcdownload.asp  to create script of db difference that can be executed with isql or embed in your update procedure. Keeping db version in some admin table in database is also useful. ---------------------------------------------------------------------- rvk · 2016-09-09 10:09 A tool could be useful to get started to create a change-list and if, like I said, you only have one or two other users it is manageable. But some tasks that I came across would need special attention: When you add a field to a table and have a view on that table with a WHERE clause, that extra field is not available in the view until you recreate that view (even if you used "SELECT *" in the view).You can't change field-names of fields that are referenced in a view. You need to remove the view, rename the field and recreate the view.You can't change field-names of fields that are referenced in an index.You can't change field-names of fields that are referenced in a trigger or SP. (Oe... that one changed in 2.5. Now it is possible but can break your trigger so you shouldn't forget to change that one too.)... I'm sure there are more ...I don't think Database Compare-tools will take these points in account (but I could be wrong). So if you use one to create a change-script, make sure you test it beforehand on a test-database for errors.