Darmowy OpenOffice Base – cz.3 (kwerendy)

W poprzedniej części pokazałem jak wprowadzić dane do stworzonej w bazie tabeli.

W tej części pokażę jak napisać proste kwerendy zwracające zawarte w tabeli dane.

Wybieramy trzecią opcję „Utwórz kwerendę SQL”. Pozwoli ona nam napisać proste zapytanie do bazy.

Wpisujemy treść zapytania. Nazwy kolumn i tabel wpisujemy w podwójnych cudzysłowach (w odróżnieniu od zapytań w MSSQL)

Zapisujemy je podając nazwę

Zapytanie pojawi się w obszarze Kwerendy

Klikając dwukrotnie w nasze zapytanie otrzymamy wynik

Zaszufladkowano do kategorii OpenOfficeBase | Dodaj komentarz

Darmowy OpenOffice Base – cz.2 (tabele)

W poprzedniej części pokazałem jak stworzyć bazę i pustą tabelę.


Klikając dwukrotnie w nazwę naszej tabeli otworzymy ją do edycji. Możemy wprowadzić do niej dane. Spróbujmy wpisać pesel dłuższy niż 11 znaków.

Pojawi się błąd


Uzupełniamy tabelkę o pozostałe dane

Klikamy w „Baza danych”. uruchomi się kreator bazy danych

Oczywiście można wpisywać dane w tabeli ale prościej będzie przeglądać i wpisywać je w formularzu. Uruchamiamy kreator tworzenia formularzy





Po utworzenie formularza możemy rozpocząć przeglądanie i wprowadzanie nowych danych do naszej tabeli.

Tak jak w poprzednim przypadku próba wprowadzenia danych niezgodnych z zadeklarowanymi w kolumnach kończy się błędem.

Nowy wiersz w tabeli został zapisany

W następnej części stworzymy zapytania do naszej tabeli.

Zaszufladkowano do kategorii OpenOfficeBase | Dodaj komentarz

Darmowy OpenOffice Base – cz.1 (tworzenie baz)

Ostatnio instalowałem darmowy pakiet Apache OpenOffice 4 i oprócz edytora tekstu i arkusza kalkulacyjnego natknąłem się na opcję bazy danych. Zaciekawiło mnie to i postanowiłem sprawdzić co to za cudak 🙂

Po uruchomieniu pakietu OpenOffice’a mamy do wyboru

Klikamy w „Baza danych”. uruchomi się kreator bazy danych


Bazy nie musimy rejestrować. Zaznaczamy opcję aby po utworzeniu baza została otworzona do edycji. Można również z tego poziomu uruchomić kreatora tabel (na razie to zostawimy niewłączone).

Wybieramy miejsce gdzie będzie zapisany plik bazy danych

W wybranym folderze pojawi się plik z domyślną nazwą czyli „Nowa Baza Danych.odb”

Po utworzeniu bazy danych otworzy się okno i możemy przystąpić do utworzenia tabeli

Możemy przystąpić do tworzenia tabeli. Wybierając drugą opcję uruchomimy kreatora tabel. Mamy możliwość wybrania z przygotowanych wcześniej szablonów tabel.

My spróbujemy stworzyć swoją tabelę więc wybieramy pierszą opcję „Utwórz projekt tabeli”

Wpisujemy kolejno nazwy kolumn wybieracjąc dla nich odpowiedni typ danych. Dla kolumny „pesel” ograniczamy rozmiar danych do 11 znaków.

Dodatkowo dla kolumny „pesel” stworzymy klucz główny.

Zapisując tabelę podajemy jej nazwę

Nasza tabela pojawi się w oknie głównym.

W następnej części pokaże co można dalej zrobić z naszymi danymi.

Zaszufladkowano do kategorii OpenOfficeBase | Dodaj komentarz

O tym jak Power Shell utworzył nam bazy z listy w pliku :)

Ostatnio podczas pracy pojawiła się potrzeba utworzenia kilkudziesięciu baz danych na serwerze SQL. Zleceniodawca podesłał nam listę baz w pliku xls.
Pokażę jak łatwo można zautomatyzować tworzenie baz bez konieczności klikania przez cały dzień w kreatorze baz danych.

Najpierw kopiujemy listę baz do pliku testowego C:\TEMP\LISTA.TXT. W przykładzie wpisałem trzy bazy jednak może być ich znacznie więcej.

BAZA_DANYCH_01
BAZA_DANYCH_02
BAZA_DANYCH_03

Do utworzenia skryptu do utworzenia baz użyjemy PowerShell'a.
najpierw zaczytujemy listę do zmiennej $LISTA

$dblist = get-content c:\temp\lista.txt
$dblist

BAZA_DANYCH_01
BAZA_DANYCH_02
BAZA_DANYCH_03

Następnie użyjemy składni foreach. Zaczytuje ona kolejno dane z pliku i wykonuje na nich podane polecenie:

foreach ($dbname in $dblist) {
write-host "ALTER DATABASE [$dbname]"
}

Baza danych: [BAZA_DANYCH_01]
Baza danych: [BAZA_DANYCH_02]
Baza danych: [BAZA_DANYCH_03]

Tworzymy skrypt TSQL do tworzenia bazy danych (przykładowo TESTDB01) :

CREATE DATABASE [TESTDB01]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'TESTDB01', FILENAME = N'C:\SQLData\TESTDB01.mdf' , SIZE = 262144KB , MAXSIZE = 512000KB , FILEGROWTH = 131072KB )
LOG ON
( NAME = N'TESTDB01_log', FILENAME = N'C:\SQLData\TESTDB01_log.ldf' , SIZE = 131072KB , MAXSIZE = 102400KB , FILEGROWTH = 65536KB )
GO

W powyższym skrypcie zmieniamy nazwę bazy na zmienną $DBNAME i wklejamy go do naszego skryptu PowerShell'owego


$dblist = get-content c:\temp\lista.txt
foreach ($dbname in $dblist)
{
write-host "CREATE DATABASE [$dbname]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'$dbname', FILENAME = N'C:\SQLData\$dbname.mdf' , SIZE = 262144KB , MAXSIZE = 512000KB , FILEGROWTH = 131072KB )
LOG ON
( NAME = N'$dbname_log', FILENAME = N'C:\SQLData\$dbname_log.ldf' , SIZE = 131072KB , MAXSIZE = 102400KB , FILEGROWTH = 65536KB )
GO
"
}

Wykonujemy skrypt w wyniku którego otrzymujemy skrypt SQL do utworznia trzech baz

CREATE DATABASE [BAZA_DANYCH_01]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'BAZA_DANYCH_01', FILENAME = N'C:\SQLData\BAZA_DANYCH_01.mdf' , SIZE = 262144KB , MAXSIZE = 512000KB , FILEGROWTH = 131072KB )
 LOG ON
( NAME = N'', FILENAME = N'C:\SQLData\.ldf' , SIZE = 131072KB , MAXSIZE = 102400KB , FILEGROWTH = 65536KB )
GO

CREATE DATABASE [BAZA_DANYCH_02]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'BAZA_DANYCH_02', FILENAME = N'C:\SQLData\BAZA_DANYCH_02.mdf' , SIZE = 262144KB , MAXSIZE = 512000KB , FILEGROWTH = 131072KB )
 LOG ON
( NAME = N'', FILENAME = N'C:\SQLData\.ldf' , SIZE = 131072KB , MAXSIZE = 102400KB , FILEGROWTH = 65536KB )
GO

CREATE DATABASE [BAZA_DANYCH_03]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'BAZA_DANYCH_03', FILENAME = N'C:\SQLData\BAZA_DANYCH_03.mdf' , SIZE = 262144KB , MAXSIZE = 512000KB , FILEGROWTH = 131072KB )
 LOG ON
( NAME = N'', FILENAME = N'C:\SQLData\.ldf' , SIZE = 131072KB , MAXSIZE = 102400KB , FILEGROWTH = 65536KB )
GO

Jest jeszcze druga metoda która wykorzystuje moduł SqlPS. Wykorzystuje ona składnie
set-location SQLSERVER:\SQL
i uruchomienie jej powoduje błąd

set-location : Cannot find drive. A drive with the name 'SQLSERVER' does not exist.
At line:1 char:1
+ set-location SQLSERVER:\SQL
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (SQLSERVER:String) [Set-Location], DriveNotFoundException
+ FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.SetLocationCommand

Przy próbie importu modułu również otrzymamy błąd:

Import-Module SQLServer;

Import-Module : File C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SqlPS\SqlServer.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at https:/go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ Import-Module SqlServer;
+ ~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : SecurityError: (:) [Import-Module], PSSecurityException
+ FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand

W większości przypadków jednak domyślne polisy systemu zabezpieczają przed wykonywaniem skryptów z nich użyciem, ale można to obejść usuwając klucz
ExecutionPolicy = RemoteSigned
z rejestru Komputer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps150

Przy ponownym uruchomieniu Power Shella nie powinno być już błędu.

Można też skorzystać z polecenia zmieniającego ustawienia

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope LocalMachine

Execution Policy Change
The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose
you to the security risks described in the about_Execution_Policies help topic at
https:/go.microsoft.com/fwlink/?LinkID=135170. Do you want to change the execution policy?
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): A

Teraz możemy zaimportować moduł i wykonać polecenie Set-Location:

set-location SQLSERVER:\SQL

PS SQLSERVER:\SQL> dir

MachineName
-----------
DELL_MICEK1968

Widać że moduł działa prawidłowo

Wykorzystując polecenie FOREACH tworzymy skrypt:

$dblist = get-content c:\temp\lista.txt
$inst = "localhost"
foreach ($dbname in $dblist) {
set-location SQLSERVER:\SQL\$inst
$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -ArgumentList $inst, $dbname
$db.Create()
$db.SetOwner("sa")
$db.BrokerEnabled = $false
$db.alter()
set-location D:\
write-host "utworzono bazę danych $dbname"
}

W wyniku jego zostaną utworzone trzy bazy danych zapisane w pliku LISTA.TXT a w power shellu otrzymamy komunikat:

utworzono baz danych BAZA_DANYCH_01
utworzono baz danych BAZA_DANYCH_02
utworzono baz danych BAZA_DANYCH_03
Zaszufladkowano do kategorii Microsoft SQL, SQL PowerShell | Dodaj komentarz

Oczyszczanie bazy MSDB z „Mail Message”

Kiedy na serwerze SQL używamy usługi SQLMail (opisanej już wcześniej) służącej do wysyłania maili wszystkie wysyłane informacje są zapisywane w tabeli w bazie MSDB. Jeśli systematycznie nie czyścimy baza MSDB może nam się mocno rozrosnąć.

Najpierw podejrzyjmy nasze dane w bazie MSDB

SELECT
mailitem_id,
sent_status,
send_request_date
FROM msdb.dbo.sysmail_allitems;

mailitem_id sent_status send_request_date
----------- ----------- -----------------------
8           failed      2021-12-09 19:50:42.290
9           failed      2021-12-09 19:56:59.917
10          failed      2021-12-09 20:00:18.480
11          failed      2021-12-09 20:01:19.750
14          sent        2021-12-09 20:16:09.453
...
41          sent        2021-12-09 22:41:51.117
42          sent        2021-12-09 22:43:22.227
43          sent        2021-12-09 23:03:46.337
44          sent        2021-12-09 23:05:19.650
45          sent        2021-12-09 23:07:11.370

(38 rows affected)

Do usuwania zapisanych w tabeli danych służy procedura msdb.dbo.sysmail_delete_mailitems_sp
Przykładowo aby usunąć wpisy starsze niż okręslona data należy wykonać:

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
@sent_before = '2020-08-25';

Przykłądowe usunięcie błędnych wysyłek:

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
@sent_status = 'failed';

(6 rows affected)

I usunięcie wszystkiego

DECLARE @GETDATE datetime
SET @GETDATE = GETDATE();
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
@sent_before = @GETDATE;

(32 rows affected)

SELECT
mailitem_id,
sent_status,
send_request_date
FROM msdb.dbo.sysmail_allitems;

mailitem_id sent_status send_request_date
----------- ----------- -----------------------

(0 rows affected)

UWAGA! Powyższa procedura nie usuwa zapisów z Database Mail Log. Do tego należy użyć procedury sysmail_delete_log_sp.

Czyli analogicznie jak w poprzedniej procedurze:
wyświetlenie danych:
SELECT * FROM msdb.dbo.sysmail_event_log;

log_id      event_type  log_date                description                                                                                                                                                                                                                                                      process_id  mailitem_id account_id  last_mod_date           last_mod_user
----------- ----------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ----------------------- --------------------------------------------------------------------------------------------------------------------------------
33          information 2021-12-09 20:28:44.817 DatabaseMail process is started                                                                                                                                                                                                                                  1948        NULL        NULL        2021-12-09 20:28:44.817 ZARZĄDZANIE NT\SYSTEM
34          information 2021-12-09 20:38:44.853 DatabaseMail process is shutting down                                                                                                                                                                                                                            1948        NULL        NULL        2021-12-09 20:38:44.853 ZARZĄDZANIE NT\SYSTEM
35          information 2021-12-09 21:08:22.000 DatabaseMail process is started                                                                                                                                                                                                                                  8444        NULL        NULL        2021-12-09 21:08:22.000 ZARZĄDZANIE NT\SYSTEM
36          information 2021-12-09 21:39:13.210 DatabaseMail process is shutting down                                                                                                                                                                                                                            8444        NULL        NULL        2021-12-09 21:39:13.210 ZARZĄDZANIE NT\SYSTEM
37          information 2021-12-09 22:25:16.810 DatabaseMail process is started                                                                                                                                                                                                                                  7136        NULL        NULL        2021-12-09 22:25:16.810 ZARZĄDZANIE NT\SYSTEM
38          information 2021-12-09 22:39:42.383 DatabaseMail process is shutting down                                                                                                                                                                                                                            7136        NULL        NULL        2021-12-09 22:39:42.383 ZARZĄDZANIE NT\SYSTEM
39          information 2021-12-09 22:39:47.283 DatabaseMail process is started                                                                                                                                                                                                                                  9932        NULL        NULL        2021-12-09 22:39:47.283 ZARZĄDZANIE NT\SYSTEM
40          information 2021-12-09 22:53:22.277 DatabaseMail process is shutting down                                                                                                                                                                                                                            9932        NULL        NULL        2021-12-09 22:53:22.277 ZARZĄDZANIE NT\SYSTEM
41          information 2021-12-09 23:03:46.600 DatabaseMail process is started                                                                                                                                                                                                                                  11768       NULL        NULL        2021-12-09 23:03:46.600 ZARZĄDZANIE NT\SYSTEM
42          information 2021-12-09 23:17:11.430 DatabaseMail process is shutting down                                                                                                                                                                                                                            11768       NULL        NULL        2021-12-09 23:17:11.430 ZARZĄDZANIE NT\SYSTEM
43          information 2022-09-16 21:23:55.687 Mail items deletion is initiated by user "DELL_MICEK1968\micek1968". 0 items deleted.                                                                                                                                                                            NULL        NULL        NULL        2022-09-16 21:23:55.687 DELL_MICEK1968\micek1968
44          information 2022-09-16 21:24:11.873 Mail items deletion is initiated by user "DELL_MICEK1968\micek1968". 6 items deleted.                                                                                                                                                                            NULL        NULL        NULL        2022-09-16 21:24:11.873 DELL_MICEK1968\micek1968
45          information 2022-09-16 21:24:57.950 Mail items deletion is initiated by user "DELL_MICEK1968\micek1968". 32 items deleted.                                                                                                                                                                           NULL        NULL        NULL        2022-09-16 21:24:57.950 DELL_MICEK1968\micek1968

(13 rows affected)

usunięcie wpisów do konkretnej daty:
EXECUTE msdb.dbo.sysmail_delete_log_sp
@logged_before = '2020-08-25';

usunięcie wpisów podanego typu:
EXECUTE msdb.dbo.sysmail_delete_log_sp
@event_type = 'information';

usunięcie wszystkich danych z tabeli:
EXECUTE msdb.dbo.sysmail_delete_log_sp;

(13 rows affected)
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Przenoszenie danych między serwerami SQL za pomocą wygenerowanych skryptów.

Dziś pokażę jak wykonać przeniesienie danych pomiędzy serwerami z różnymi wersjami SQL. Jeśli chcielibyśmy przenieść ze starszego serwera na nowszy nie powinno być z tym większego problemu. Problemy pojawiają się jeśli chcemy przenieść dane (bazy) z serwera o wyższej wersji do serwera z niższą wersją. Niestety tu napotkamy na problemy (zarówno próbując wykonać Attach plików MDF i LDF z nowszej wersji, jak i próbując odzyskać backup wykonany na wyższej wersji)

Możemy skorzystać z exportu a następnie importu danych znajdujących się w bazie. Ja jednak pokażę jak wygenerować skrypty pozwalające na migrację danych.

Załóżmy że mamy dwa serwery (SQL2019 i SQL2016) i chcemy przenieść całą bazę z wyższej wersji (2019) do wersji starszej (2016).
Łączymy się do serwera źródłowego. Wybieramy bazę którą chcemy przenieść i z menu wybieramy opcję GENERATE SCRIPTS

Mamy do wybory dwie opcje. Pierwsza pozwala na wygenerowanie skryptu dla całej bazy.

Drugi dla pojedynczych obiektów bazy (tabele, widoki lub procedury składowane).

Przy wyborze Single Script File (jeden skrypt) wybieramy Ścieżkę i nazwę skryptu. Możemy również m.in. wybrać od razu wczytanie skryptu do okna SQL Management Studio.

Po naciśnięciu przycisku ADVANCED mamy sporą listę parametrów dotyczących tworzonych skryptów.

Na głównie będą interesowały poniższe czyli wybór czy w skrypcie mają być zawarte dane znajdujące się w tabelach czy tylko skrypty tworzące strukturę czy jedno i drugie:

Wersja docelowej wersji SQL-a:

Edycja docelowej wersji SQL-a:

Możemy również wybrać opcję w której wygenerowane zostaną skrypty dla poszczególnych obiektów (np. tabel). Wskazujemy w takim przypadku tylko folder gdzie mają się znaleźć skrypty a nazwy zostaną wygenerowane automatycznie

Małe podsumowanie i naciśnięcie FINISH spowoduje uruchomienie tworzenia skryptów.

Skrypty utworzone.

Teraz nie pozostaje nic innego jak skopiować je na docelowy serwer i uruchomić.
Powinniśmy zacząć od skryptu tworzącego nasza bazę (w naszym przypadku Northwind.Database.sql). Przy tworzeniu bazy nalezy zwrócić uwagę na ścieżki do plików bazy czy jest wpisana odpowiednia ścieżka.
Następnie wykonujemy skrypty dla poszczególnych obiektów (najpierw tabel, potem widoków, procedur składowanych itd).
Jak widać poniżej skrypt dla tabeli dbo.Categories.Table składa się z sekcji tworzącej tabele (CREATE TABLE) wraz z indeksami jak i jej napełnienie (INSERT dla każdego wiersza osobny)

PROCEDURA

Inną metoda jest zastosowanie procedury za pomocą której wygenerujemy skrypt to wprowadzenia danych do naszej tabeli. Link do wkryptu sp_generate_inserts.sql

Procedurę uruchamiamy poleceniem (przykładowo dla bazy Nortwind i tabeli dbo.Customers

EXEC sp_generate_inserts 'Customers'

Aby ograniczyć wielkość pliku uruchomimy to z parametrem określającym wygenerowanie skryptu dla TOP(5) wierszy tabeli

EXEC sp_generate_inserts 'Customers', @top = 5

--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
 
SET NOCOUNT ON
 
PRINT 'Inserting values into [Customers]'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('ALFKI','Alfreds Futterkiste','Maria Anders','Sales Representative','Obere Str. 57','Berlin',NULL,'12209'
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('ANATR','Ana Trujillo Emparedados y helados','Ana Trujillo','Owner','Avda. de la Constitución 2222','Méxi
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('ANTON','Antonio Moreno Taquería','Antonio Moreno','Owner','Mataderos  2312','México D.F.',NULL,'05023','
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('AROUT','Around the Horn','Thomas Hardy','Sales Representative','120 Hanover Sq.','London',NULL,'WA1 1DP'
INSERT INTO [Customers] ([CustomerID],[CompanyName],[ContactName],[ContactTitle],[Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax])VALUES('BERGS','Berglunds snabbköp','Christina Berglund','Order Administrator','Berguvsvägen  8','Luleå',NULL,'S

PRINT 'Done'

SET NOCOUNT OFF

Po zakończeniu prac usuwamy procedure poleceniem
drop proc sp_generate_inserts

Więcej informacji dotyczącej procedury sp_generate_inserts :
https://vyaskn.tripod.com/code.htm#inserts

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

FireBird (cz.3) – Bóbr dobiera się do naszej bazy :)

Jeżeli jesteśmy przyzwyczajeni do graficznych programów do obsługi baz danych to możemy przy obsłudze bazy FireBird skorzystać z darmowego programu DBeaver w wersji portable czyli bez konieczności instalacji (https://dbeaver.io/download/)

Po ściągnięciu pliku ZIP rozpakowujemy go i uruchamiamy program dbeaver.exe
Jak widać program obsługuje sporą ilość baz (MSSQL, Oracle, MySQL, PostgreSQL, DB2, itd).

Po wybraniu FireBirda wciskamy „next” i konfigurujemy połączenie do naszej bazy.
Może pojawić się komunikat o konieczności ściągnięcia sterowników (oczywiście akceptujemy)

Podłączyliśmy się do bazy.

Zaszufladkowano do kategorii FireBird | Dodaj komentarz

FireBird (cz.2) – Pierwsze kroki

Po instalacji FireBird’a (opisane w artykule https://micek1968.pl/?p=4338) wraz z silnikiem bazy danych zostało zainstalowane oprogramowanie „FireBird ISQL Tool”.

Jest to proste okno poleceń za pomocą którego możemy pracować na naszych tabelach w bazach FireBird.
Możemy uruchamiać ISQL-a z linii poleceń. Program znajduje się w folderze z binarkami bazy danych (C:\Program Files (x86)\Firebird\Firebird_4_0)
Uruchamiamy CMD (jako ADMINISTRATOR !!!) i uruchamiamy program ISQL.EXE

Prościej jest jednak uruchomić to z menu startowego.

Tu także uruchamiamy ISQL w trybie administratora. Uruchomienie w trybie zwykłego usera spowoduje, że nie będziemy mieli dostępu do baz systemowych (np. listy userów baz).

Zgodnie z informację podaną w oknie najpierw musimy podłączyć się do bazy lub ją stworzyć (Use CONNECT or CREATE DATABASE to specify a database)
Jako że nie mamy jeszcze żadnej bazy w pierwszej kolejności ją utworzymy.

UTWORZENIE BAZY DANYCH

SQL> CREATE DATABASE "C:\FireBirdDB\BazaDanych.FDB";
Server version:
WI-V4.0.1.2692 Firebird 4.0

W folderze utworzył się plik FDB.

Po utworzeniu bazy danych, wylogowujemy się za pomocą polecenia QUIT.

PODŁĄCZENIE DO BAZY DANYCH

Aby podłączyć się do bazy danych należałoby wydać polecenie:

SQL> CONNECT "C:\FireBirdDB\BazaDanych.FDB";
Server version:
WI-V4.0.1.2692 Firebird 4.0
Database: "C:\FireBirdDB\BazaDanych.FDB", User: MICEK1968

Możemy sprawdzić listę użytkowników:

SQL> SHOW USERS;
Users in the database
  1 #MICEK1968

Przy aktualnie podłączonym użytkowniku znajduje się znak "#".

Informacje o naszej aktualnej bazie danych (do której jesteśmy podłączeni)

SQL> SHOW DATABASE;
Database: C:\FireBirdDB\BazaDanych.FDB
        Owner: MICEK1968
PAGE_SIZE 8192
Number of DB pages allocated = 240
Number of DB pages used = 224
Number of DB pages free = 16
Sweep interval = 20000
Forced Writes are ON
Transaction - oldest = 6
Transaction - oldest active = 7
Transaction - oldest snapshot = 7
Transaction - Next = 12
ODS = 13.0
Database not encrypted
Embedded connection
Creation date: Feb 2, 2022 18:25:07 GMT
Default Character set: NONE

To samo uzyskamy poprzez "SHOW DB".

UTWORZENIE TABELI i WPROWADZENIE DANYCH

Jeśli już podłączyliśmy się do bazy, wyświetlimy listę tabel

SQL> SHOW TABLE;
There are no tables in this database

W bazie nie ma żadnych tabel więc ją utworzymy.

SQL> CREATE TABLE ADRESY
CON> (
CON> nazwisko varchar(40),
CON> adres varchar(50),
CON> telefon varchar(9)
CON> );
SQL> COMMIT;

Można też stworzyć plik tekstowy zawierający treść skryptu a następnie wykonać polecenie

SQL> IN c:\FireBirdDB\create_table_adresy.sql;
SQL> 

Ponownie sprawdzamy listę tabel

SQL> SHOW TABLE;
ADRESY

Wyświetlamy dane w tabeli

SQL> SELECT * FROM ADRESY;
SQL>

Nic nie zwróciło gdyż tabela jest pusta. Wprowadzimy do niej jeden wiersz.

SQL> INSERT INTO ADRESY
CON> (Nazwisko, Adres, Telefon)
CON> VALUES ('Nowak Jan','Szczecin','997997997');
SQL>

Ponowne wyświetlenie danych zwraca dane

SQL> SELECT * FROM ADRESY;

NAZWISKO                  ADRES           TELEFON
========================= =============== =========
Nowak Jan                 Szczecin        997997997

Możemy do każdej tabeli dodać komentarz w którym opiszemy co baza zawiera

SQL> COMMENT ON TABLE adresy IS 'Ta tabela zawiera adresy i telefony.';

Zapisany komentarz wyświetlamy

SQL> show comment;
COMMENT ON TABLE        ADRESY IS Ta tabela zawiera adresy i telefony.;

USUNIĘCIE TABELI

Po zakończeniu prac możemy (ale na razie tego nie róbmy) usunąć naszą tabelę Adresy

SQL> DROP TABLE adresy;
SQL>

USUNIĘCIE BAZY DANYCH

Usuwanie bazy danych jest bardzo proste (Również proszę na razie tego nie wykonywać). Należy jednak uważać, bo FireBird nie prosi o potwierdzenie tylko bezpowrotnie usuwa z dysku bazę do której aktualnie jesteśmy podłączeni.

SQL> DROP DATABASE;
SQL>

Dlatego zalecane jest logować się do bazy zwykłym użytkownikiem o odpowiednich uprawnieniach 🙂

LOGOWANIE DO BAZY JAKO SYSADMIN

Jeśli chcielibyśmy utworzyć bazę jako SYSADMIN i następnie przyznawać użytkownikom odpowiednie uprawnienia (jako administrator) należało by to zrobić poleceniem:

SQL> CREATE DATABASE "C:\FireBirdDB\BazaDanych.FDB" USER "SYSDBA" PASSWORD "masterkey";
Server version:
WI-V4.0.1.2692 Firebird 4.0

Aby zalogować się do bazy jako SYSADMIN którego hasło ustawialiśmy podczas instalacji FireBirda nalezy wykonać polecenie:

SQL> CONNECT "C:\FireBirdDB\BazaDanych.FDB" user 'SYSDBA' password 'masterkey';
Commit current transaction (y/n)?y
Committing.
Server version:
WI-V4.0.1.2692 Firebird 4.0
Database: "C:\FireBirdDB\BazaDanych.FDB", User: SYSDBA

Oczywiście jako administrator mamy dostęp do wszystkich obiektów bazy.

SQL> select * from adresy;

NAZWISKO                                 ADRES                                              TELEFON
======================================== ================================================== =========
Nowak Jan                                Szczecin                                           997997997

Natomiast logując się na konto innego usera niż MICEK1968 (właściciel bazy) i SYSADMIN otrzymamy błąd dostępu:

SQL> CONNECT "C:\FireBirdDB\BazaDanych.FDB" user 'user123';
Commit current transaction (y/n)?y
Committing.
Server version:
WI-V4.0.1.2692 Firebird 4.0
Database: "C:\FireBirdDB\BazaDanych.FDB", User: USER123

SQL> select * from adresy;
Statement failed, SQLSTATE = 28000
no permission for SELECT access to TABLE ADRESY
-Effective user is USER123

i przełączamy się na MICEK1968

SQL> CONNECT "C:\FireBirdDB\BazaDanych.FDB" user 'micek1968';
Commit current transaction (y/n)?y
Committing.
Server version:
WI-V4.0.1.2692 Firebird 4.0
Database: "C:\FireBirdDB\BazaDanych.FDB", User: MICEK1968

SQL> select * from adresy;

NAZWISKO                                 ADRES                                              TELEFON
======================================== ================================================== =========
Nowak Jan                                Szczecin                                           997997997
Zaszufladkowano do kategorii FireBird | Dodaj komentarz

FireBird (cz.1) – Instalacja

Firebird – system zarządzania relacyjnymi bazami danych zgodny ze standardem ANSI SQL-92; obok MySQL oraz PostgreSQL jest jednym z trzech najpopularniejszych wolnodostępnych systemów zarządzania bazą danych. (według Wikipedii).

Na początku pobieramy najnowszą wersję FireBirda ze strony https://firebirdsql.org/en/server-packages/

Uruchamiamy program instalacyjny i przechodzimy kolejne kroki instalacji.
Starsze wersje posiadały na liście język polski jednak obecnie producent zrezygnował i jest tylko kilka podstawowych. Wybieramy oczywiście język angielski 😉











FireBird zostały zainstalowany.
Sinik FireBirda jest uruchomiony jako serwis (usługa). Jego stan możemy sprawdzić na liście usług.

W menu startowym pojawiło się

Po jego uruchomieniu pojawia się proste narzędzie do podłączenia się do bazy.

FireBird jest gotowy do pracy.

Zaszufladkowano do kategorii FireBird | Dodaj komentarz

Extended Events czyli szukanie zapytań w SQL

Załóżmy, że codziennie wieczorem ktoś uruchamia jakieś zapytania do naszej bazy powodując np. zwiększone obciążenie serwera. Aby znaleźć winowajcę możemy wykorzystać narzędzie Extended Events.
Sposób konfiguracji opisałem w oddzielnym artykule (http://micek1968.pl/?p=3632).
Dziś skupimy się na konfiguracji aby wyszukiwało wykonywane „duże” zapytania.

Wchodzimy we właściwości naszej sesji Extended Events.


Przechodzimy do zakładki Event. proponuję w tym miejscu wybrać z biblioteki zdarzeń tylko „SQL_STATEMENT_COMPLETE”. W zupełności to wystarczy do naszych poszukiwań.

Przechodzimy w prawo do konfiguracji zdarzeń, a następnie do zakładki filtrowania.

Interesują nas tylko „duze” zapytania więc wybieramy zapytania które zwracają więcej niż 50 wierszy. Wybieramy z listy rozwijanej „LAST_ROW_COUNT”

a następnie podajemy wartość powyżej której ma wyszukiwać.

Przy ponownym otwarciu ustawień znak „>” zmienia się automatycznie w wartość „greater_than_uint64”

Zatwierdzamy ustawienia i uruchamiamy Sesję Extended Events

Następnie wykonujemy (testowo) kilka zapytań na bazie Northwind. Nie ma tam dużo wierszy ale wszystkie zapytania zwracające więcej niż 50 wierszy zostaną zarejestrowane.

SELECT * FROM [Northwind].[DBO].[ORDER_DETAILS] 
SELECT * FROM [Northwind].[DBO].[ORDERS]
SELECT * FROM [Northwind].[DBO].[PRODUCTS]
SELECT * FROM [Northwind].[DBO].[CUSTOMERS]

Następnie zamykamy sesję

I wyświetlamy zapisane dane

Jak widać nasze zapytania zostały zarejestrowane:

Widać kto to wykonywał, z jakiej komputera, ile wierszy zwróciło, treść zapytania i wiele innych rzeczy.
Wyszukiwanie możemy za pomocą filtra oczywiście ograniczyć do jednej bazy i wielu innych różnych parametrów.

Problemem się staje kiedy to puściliśmy rejestrację zdarzeń do Extended Events np na cały dzień i zarejestrowało nam kilkaset tysiecy zapytań do bazy. Jak znaleźć wśród tego interesujące nas zapytanie.
Możemy się wspomóc odczytem zarejestrowanych danych z plików poprzez zapytanie t-sql.

W naszym przykładzie zdarzenia są zapisane w pięciu plikach, ale może być ich więcej i o większej pojemności.

Najprostszym zapytanie zwracającym zarejestrowane wyniki będzie

select * from sys.fn_xe_file_target_read_file('C:\Temp\MySession_*.xel', null, null, null)

Zwróci to nam wszystkie zarejestrowane zdarzenia. Mozemy ograniczyć się do pojedynczych plików wpisując ich nazwę.

Jeśli np wiemy, że zapytanie dotyczy konkretnej bazy mozemy dodać warunek
WHERE event_data like '%Orders%'
Zapytanie zwróci tyle zdarzenia dotyczące tabeli ORDERS.

Wszelkie dane dotyczące danego zdarzenia znajdują się w kolumnie EVENT_DATA i EVENT_DATA_XML (zapisane w formacie XML).

Możemy bardziej uporządkować wyświetlane dane

SELECT
object_name,
file_name,
file_offset,
event_data,
'CLICK_NEXT_CELL_TO_BROWSE_XML RESULTS!'
AS [CLICK_NEXT_CELL_TO_BROWSE_XML_RESULTS],
CAST(event_data AS XML) AS [event_data_XML]
-- TODO: In ssms.exe results grid, double-click this xml cell!
FROM
sys.fn_xe_file_target_read_file(
--'C:\Temp\MySession_1_0_132878711812050000.xel',
'C:\Temp\MySession_1_0_*.xel',
null, null, null
)
WHERE event_data like '%Orders%'

Poniższe zapytanie zwraca nam zapisane zdarzenia w bardziej czytelnej postaci.

select
n.value('(@name)[1]', 'varchar(50)') as event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name,
n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end
from (select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('C:\Temp\MySession_*.xel', null, null, null)) ed
cross apply ed.event_data.nodes('event') as q(n)
-- WHERE n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') like '%Orders%'

Jeśli nas interesuje tylko tabela ORDERS musimy odznaczyć ostatni wiersz z warunkiem WHERE.

A na na koniec taka mała porada, że jeśli chcielibyśmy zarejestrować tylko zapytania wykonywane w określonym przedziale czasowym (np między 2 i 3 w nocy najprościej powinniśmy utworzyli dwa joby: jeden uruchamiający a drugi zatrzymujący naszą sesję (nie ma możliwości ustawienia tego we właściwościach sesji).
W kroku joba nalezy umieścić polecenie uruchamiające/zatrzymujące naszą sesję

ALTER EVENT SESSION event_session_name ON SERVER STATE = {START | STOP}

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz