Metadaten - smiffy.de

Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Metadaten
• Metadaten sind Daten über Daten
• Data-Dictionary speichert Informationen über die Struktur der Daten, z.B.:
• Tabellen, Spalten, Datentypen
• Primär- und Fremdschlüssel, Konsistenzbedingungen
• Zugriffsberechtigungen, Benutzer
• Quellcode zu Stored Procedures, Trigger
• ...
• Einsatz z.B. im Rahmen von
• IDEs wie SQLDeveloper, Hora, Toad, ...
• generative Programmierung (Generierung von Oberflächen, ...)
• SQL 2 generate SQL
Andreas Schmidt
Metadaten 1/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Data-Dictionary von Oracle
• Übersicht über Data-Dictionary ist die Tabelle DICTIONARY
• Beispiel:
select *
from dictionary
where lower(comments) like '%column%'
and lower(comments) like '%table%'
• Ausgabe:
Andreas Schmidt
Metadaten 2/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Übersicht Data-Dictionary
• Welche Tabellen/Views/Synonyme/... gibt es denn ?
- ALL_OBJECTS1, USER_OBJECTS
• Welche Spalten hat eine Tabelle/View?
- ALL_TAB_COLUMNS, USER_TAB_COLUMNS
• Welche Constraints gibt es ?
- ALL_CONSTRAINTS, ...
• Auf welchen Spalten sind Constraints definiert ?
- ALL_CONS_COLUMNS, ...
• Quellcode
- ALL_TRIGGERS, ALL_SOURCE, ...
• ...
1. USER_<x> entspricht ALL_<x> mit Ausnahme, dass die Spalte OWNER fehlt und nur die Informationen für den aktuell angemeldeten User angezeigt
werden.
Andreas Schmidt
Metadaten 3/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Beispiele
• Übersicht über die Tabellen und Spalten des Users MONDIAL
select owner, table_name, column_name, data_type, data_length, nullable
from all_tab_columns
where owner ='MONDIAL';
Andreas Schmidt
Metadaten 4/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Constraints der Tabellen
COUNTRY, PROVINCE und CITY
select *
from all_constraints
where owner='MONDIAL'
and table_name in
('COUNTRY',
'CITY',
'PROVINCE')
order by table_name;
Andreas Schmidt
Metadaten 5/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Fremdschlüssel der Tabelle COUNTRY
select *
from all_cons_columns
where owner='MONDIAL'
and table_name in ('COUNTRY',
'PROVINCE',
'CITY');
Andreas Schmidt
Metadaten 6/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
SQL to generate SQL
• Motivation: Für den User SCAN0004 soll der lesende Zugriff auf die Tabellen der Mondial
Datenbank erlaubt werden
• DCL-Statements (ca. 28 Stück):
grant select on BORDER to SCAN0004;
grant select on COUNTRY to SCAN0004;
...
grant select on ORGANISATION to SCAN0004;
• Beobachtung: Statements sind bis auf Tabellenname identisch, Namen der Tabellen stehen
im Data-Dictionary
Andreas Schmidt
Metadaten 7/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
SQL to generate SQL
• SQLPlus Frontend kann mittels SPOOL-Kommando Ausgaben in Dateien umlenken
• Beispiel:
spool c:/sonstwohin/hauptstaedte.txt
select capital
from mondial.country
order by capital;
spool off
• SQLPlus kann mittels @-Kommando Dateien laden und die darin befindlichen SQL-Kommandos ausführen
• Beispiel:
SQL > @ delete-tables.sql
Andreas Schmidt
Metadaten 8/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
• Die Tabellen im Schema MONDIAL sollen von allen Studenten gelesen werden können:
select 'grant select on '|| table_name ||' to student;' generated_sql
from all_tables
where owner='MONDIAL';
• Ausgabe:
GENERATED_SQL
---------------------------------------------------------grant select on COUNTRY to student;
grant select on CITY to student;
...
grant select on BORDERS to student;
GENERATED_SQL
---------------------------------------------------------grant select on ENCOMPASSES to student;
grant select on ISLAND to student;
28 rows selected.
Andreas Schmidt
Metadaten 9/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
• alles störende raus ...
set feedback off
set pagesize 0
• alles zusammen:
set feedback off
set pagesize 0
spool tmp/grant-mondial.sql
select 'grant select on '|| table_name ||' to student;' generated_sql
from user_tables;
spool off
@ tmp/grant-mondial.sql
host rm tmp/grant-mondial.sql
quit
Andreas Schmidt
Metadaten 10/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
MySQL auto_increment Mechanismus mittels
Trigger simulieren
create table AI_Test (
id number primary key
value varchar(40)
);
create sequence SEQ_AI_Test;
create or replace trigger TRG_AI_Test
before insert on AI_Test
for each row
begin
:new.id := seq_AI_Test.nextval;
end;
/
show errors
insert into AI_Test(value)
values(’ein Test’);
Andreas Schmidt
Metadaten 11/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Automatisierung ...
• Anfrage an Data-Dictionary: Gib die Namen aller Tabellen zurück, die mit dem Präfix AI_
beginnen.
• Query:
select table_name
-- alle Tabellen deren Name mit AI_ anfängt
from user_tables
where table_name like 'AI#_%' escape ’#’
’_’ ist das Wildcardzeichen
für ein beliebiges Zeichen und muss
deshalb ’escaped’ werden
Andreas Schmidt
Metadaten 12/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
select 'create sequence SEQ_'||table_name||';'||chr(10)||
'create or replace trigger TRG_'||table_name||chr(10)||
' before insert on '||table_name||chr(10)||
'for each row'||chr(10)||
'begin'||chr(10)||
'
:new.id := SEQ_'||table_name||'.nextval;'||chr(10)||
Zeilenumbruch
'end;'||chr(10)||
'/'||chr(10)||chr(10)||
'show errors'||chr(10) sql_statement
from (select table_name
-- Statement von vorheriger Seite
from user_tables
where table_name like 'AI#_%' escape ’#’
);
Andreas Schmidt
Metadaten 13/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Übung I
• Schau dir die Dictionary-Tabelle user_objects an und schreib ein Skript,
das dein Schema komplett leert(*).
(*)
Wenn du noch Interesse an deinen bisherigen Tabellen/Prozeduren/... hast, dann
solltest du diese vorher sichern (mittels Oracle Tool exp.exe), das geht so:
prompt> exp euer-account-name@oracledbwi file=ein-beliebiger-dateiname
zurückspielen mittels imp.exe (ins leere Schema):
prompt> imp euer-account-name@oracledbwi full=y file=ein-beliebiger-dateiname
Andreas Schmidt
Metadaten 14/15
Fakultät für Informatik & Wirtschaftsinformatik
DB & IS II - SS 2017
Übung II
• Schreibe ein Skript, das alle Tabellen löscht, die den selben Namen haben wie eine
Tabelle im Mondial Schema.
• Lege dafür dann in deinem Schema die entsprechenden Synonyme für die Tabellen aus
dem Mondial Schema an.
Andreas Schmidt
Metadaten 15/15