Firebird DB - Tobi´s Garden

⬅️ **[[$-Tools|Tools]]** *** # Firebird DB - [[SQL]] Informationen - [[DBeaver]] als OpenSource und Crossplatform Tool für [[SQL]] - Verwendung von **fdb** Module (`pip3 install fdb`) mit [[Python]] => siehe mehr Informationen unten - Verwendung für [[$-MCE]] mit [[MCE Amicron SQLs]] - [Firebird - Connecting To A Database](https://www.firebirdsql.org/pdfmanual/html/isql-connect-database.html) - Default Firebird Password: `masterkey` or `masterke` as only 8 characters are checked - [Firebird ISQL Commands](https://www.firebirdsql.org/pdfmanual/html/isql-commands.html) - [devart - Connecting to Firebird from Microsoft Excel using ODBC Driver for Firebird](https://www.devart.com/odbc/firebird/docs/excel.htm) - kostenpflichtiger Windows ODBC Driver - [Firebird Command Line Utilities](https://firebirdsql.org/manual/firebird-commandline-utilities.html) - [Firebird Interactive SQL Utility](https://www.firebirdsql.org/pdfmanual/html/isql.html) - [Firebird FAQ - Is it possible to use Firebird over Internet?](http://www.firebirdfaq.org/faq53/) - DB Size - https://stackoverflow.com/questions/2045306/firebird-database-keeps-getting-larger - [Why doesn't the database file shrink when I delete records?](http://www.firebirdfaq.org/faq41/) - [IBPhoenix - Firebird for the Database Expert: Episode 6 - Why can't I shrink my databases](https://www.ibphoenix.com/resources/documents/search/doc_23) ## Firebird with Docker [DockerHub - Firebird](https://hub.docker.com/r/jacobalberty/firebird) 1. in `hosts` Datei muss **MCE-Server** mit IP Adresse hinterlegt sein 2. `docker-compose.yml` für [[$-MCE]]: ```yml 02-firebird: image: jacobalberty/firebird:v2.5.9-sc container_name: 02-firebird volumes: - /mnt/SSD1_250/docker-data/firebird-amicron/data:/firebird/data - /mnt/SSD1_250/docker-data/firebird-amicron/restore:/firebird/restore - /mnt/SSD1_250/docker-data/firebird-amicron/log:/firebird/log ports: - 3050:3050 environment: TZ: "Europe/Berlin" ISC_PASSWORD: "masterkey" #EnableLegacyClientAuth: "true" #FIREBIRD_DATABASE: "MCE_AMICRON_DB_PRODUCTION" #FIREBIRD_USER: "sysdba" #FIREBIRD_PASSWORD: "masterkey" restart: always networks: mce-nw: aliases: - mce-firebird ``` 3. Damit Amicron auf diese DB zugreifen kann muss die `Data.ini` erweitert werden: ```bash [MD-MCE Production MCE-SERVER] DBServer=MCE-SERVER DBName=/firebird/data/MCE_AMICRON_DB_PRODUCTION.FDB ``` 4. ähnliche Informationen müssen in [[DBeaver]] angegeben werden - z.B. mit Port 3050 ## Firebird Linux Installation - [Guide - Firebird 3.0 - Ubuntu 18.04, 20.04](https://www.osradar.com/install-firebird-ubuntu-18-04/) - [Guide - Firebird 2.5 - Ubuntu](https://firebirdsql.org/manual/ubusetup.html) ## gbak für Firebird Backups and Restore - [Firebird gbak](https://firebirdsql.org/file/documentation/html/en/firebirddocs/gbak/firebird-gbak.html) - http://www.destructor.de/firebird/gbak.htm - Software: `sudo apt install firebird3.0-utils` - [Performing backups and restores using the gbak command - InterBase](https://docwiki.embarcadero.com/InterBase/2020/en/Performing_backups_and_restores_using_the_gbak_command) - https://stackoverflow.com/questions/54504387/how-to-backup-restore-a-firebird-database - Examples - Error messages ### Backup ``` **Example Backup** for Docker MCE Instance `gbak -Z -B -G -V -IG -user SYSDBA -password masterkey MCE-Server/3050:/firebird/data/MCE_AMICRON_DB_PRODUCTION.FDB ~/MCE-DB.FBK` - -Z = print Version number - **-B** = Backup - -G = ignore Garbage Collection during Backup - -V = report every action -> verbose - -IG = Ignore bad Checksum **Example Restore** for Docker MCE Instance `gbak -Z -R -V -user SYSDBA -password masterkey ~/MCE-DB.FBK MCE-Server/3050:/firebird/data/MCE_AMICRON_DB_PRODUCTION_rest.FDB` - **-R** = Restore -> create new .FDB DB instance from Backup - ... **Example Replace** for Docker MCE Instance `gbak -Z -REP -V -user SYSDBA -password masterkey ~/MCE-DB.FBK MCE-Server/3050:/firebird/data/MCE_AMICRON_DB_PRODUCTION_rest.FDB` - **-REP** = Replace -> replaces existing .FDB DB instance with Backup - ... ``` ### Restore ``` vim Dockerfile RUN apt-get update && \ apt-get install -y firebird2.5-super redis-server EXPOSE 6379 VOLUME /data WORKDIR /data CMD ["redis-server"] docker build -t tobi/gbak . docker run -d --volume=.:/data --name gbak tobi/gbak ``` ```sh ## completely remotely executed ## - -service -> where ## - Source and Target = remote paths docker exec gbak gbak -V -Z -c -user SYSDBA -password masterkey -transportable -service 192.168.74.12:service_mgr /firebird/data/Jahre/MD-MCE_201720170102071211.fbk /firebird/data/Jahre/MD-MCE_201720170102071211.FDB ## Restore local .fbk to remote service path docker exec gbak gbak -V -Z -c -user SYSDBA -password masterkey -transportable MD-MCE_201720170102071211.fbk 192.168.74.12:/firebird/data/MD-MCE_201720170102071211.FDB ``` ## [[Firebird mit Python]] ## Firebird mit Windows CMD und isql - [Firebird Command Line Utilities](https://firebirdsql.org/manual/firebird-commandline-utilities.html) - [Firebird Interactive SQL Utility](https://www.firebirdsql.org/pdfmanual/html/isql.html) - [ISQL - Commands](https://www.firebirdsql.org/pdfmanual/html/isql-commands.html) - [ISQL - Command Line Switches](https://www.firebirdsql.org/pdfmanual/html/isql-switches.html) - [ISQL - Show Commands](https://www.firebirdsql.org/pdfmanual/html/isql-show.html) - [ISQL - Set Commands](https://www.firebirdsql.org/pdfmanual/html/isql-set.html) - **Windows Umgebungsvariablen** - `ISC_USER` *SYSDBA* - `ISC_PASSWORD` *masterkey* - `PATH` mit `C:\Program Files (x86)\Firebird\Firebird_2_5\bin` damit das Tool **ISQL** von CMD ausgeführt werden kann. - von **CMD** ausführen mit `isql.exe -pag 10000 -z -q -i .\2022-01-19-TEST.sql` - **SQL Script mit Connection** unter Verwendung der Umgebungsvariablen als Credentials: ```sql CONNECT "C:\Users\tobia\Documents\MCE_Amicron_DB_Production_2021-10-28.FDB"; CONNECT "SERVER:C:\Users\Public\Documents\Amicron-Data\MCE_Amicron_DB_Production.FDB"; OUTPUT C:\Users\tobia\test.xls FORMAT csv DELIMITED BY ','; SELECT TRIM(a.ARTIKELNR) AS "Artikel-Nr.", a.BEZEICHNUNG8 AS "Kabel Kurzname", a.EKPREIS, a.VK1, LAGERBESTAND.BESTAND, CAST(a.BESTANDDATUM AS DATE) AS "Bestanddatum", CAST(a.LETZTERVERKAUF AS DATE) AS "letzter Verkauf" FROM ARTIKEL a LEFT JOIN LAGERBESTAND ON a.LFDNR=LAGERBESTAND.ARTIKELLFDNR AND a.LAGERLFDNR=LAGERBESTAND.LAGERLFDNR WHERE TRIM(a.ARTIKELNR) IN ( '1010261', -- RG 174 '101032', -- RG 179 '1010995' -- Hyperflex13 ) ORDER BY a.BEZEICHNUNG8 ASC; ``` ## dBeaver Config ![[Pasted image 20240105114235.png]] # *** Related: - [[Docker]] - [[Datenbanken]] - [[$-MCE|MCE]]