mirror of
https://github.com/Wisser/Jailer.git
synced 2026-01-13 10:47:10 -06:00
296 lines
15 KiB
HTML
296 lines
15 KiB
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
|
|
<head>
|
|
|
|
<title>Jailer - Deleting Data</title>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
|
|
<meta name="description" content="Data Export Tool" />
|
|
<meta name="keywords" content="data export referential integrity java jdbc dbms" />
|
|
<link rel="stylesheet" type="text/css" href="styles.css" />
|
|
<link rel="shortcut icon" href="favicon.ico" />
|
|
</head>
|
|
|
|
<body left-margin="0" top-margin="0">
|
|
<div align="left">
|
|
<table style="text-align: left; position: absolute; left: 0pt; top: 0pt;" border="0"
|
|
cellpadding="0" cellspacing="0" height="407" width="100%">
|
|
<tbody>
|
|
<tr>
|
|
<td colspan="2" class="bannerbackground" height="33" width="100%">
|
|
<table border="0" cellpadding="4" cellspacing="0" width="100%">
|
|
<tbody>
|
|
<tr>
|
|
<td><a href="https://github.com/Wisser/Jailer"><img src="logo.png" hspace="10" /></td> <td class="slogan" width="100%"></a>
|
|
|
|
|
|
|
|
<td style="text-align: right; width: 100%;">
|
|
<table border="0" cellpadding="0" cellspacing="0">
|
|
<tbody>
|
|
<tr>
|
|
<td><a href="https://github.com/Wisser/Jailer"><img src="GitHub-Mark-64px.png" title="GitHub" /></a> </td></tr><tr style="height: 100%;"><td> </td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</td>
|
|
</tr>
|
|
|
|
<tr>
|
|
<td colspan="2" class="nav1background" width="100%">
|
|
<b><font color="#FFFFFF"><a href="home.htm" target="_self" class=
|
|
"tlink"> Home </a> <a href="quicktour.htm" target="_self"
|
|
class="tlink"> Quick Tour </a> <a href="exporting-data.htm"
|
|
target="_self" class="tlinkA"> Tutorial </a> <a href=
|
|
"data-browsing.html" target="_self" class="tlink"> Data Browser </a> <a class="tlink" href="videos.html" target="_self"> Videos </a>
|
|
<a href="faq.html" target="_self" class="tlink"> FAQ </a>
|
|
<a href="api.html" target="_self" class="tlink"> API </a>
|
|
<a href="design.htm" target="_self" class=
|
|
"tlink"> Documentation </a> <a href=
|
|
"http://sourceforge.net/forum/?group_id=197260" target="_self" class=
|
|
"tlink"> Forum </a> <a href=
|
|
"http://sourceforge.net/project/showfiles.php?group_id=197260" target="_self"
|
|
class="tlink"> Download </a>
|
|
</font></b></td>
|
|
</tr>
|
|
|
|
<tr>
|
|
<td colspan="2" class="spacer" width="100%"></td> </tr><tr><td class="lmenucontainer"> </td>
|
|
</tr>
|
|
|
|
<tr>
|
|
<td class="lmenucontainer" height="100%" valign="top" width="14%">
|
|
<div align="center">
|
|
<center>
|
|
<table border="0" cellpadding="0" cellspacing="0" width="100%">
|
|
<tbody>
|
|
<tr>
|
|
<td height="6px" width="100%"> </td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</center>
|
|
</div>
|
|
|
|
<ul>
|
|
<li><a href="preparation.htm" target="_self" class="mlink">Preparation</a> </li><br>
|
|
<li>Basic</li>
|
|
|
|
<ul>
|
|
<li><a href="exporting-data.htm" target="_self" class="llink">Exporting Data</a> </li>
|
|
</ul>
|
|
|
|
<ul>
|
|
<li><a href="deleting-data.htm" target="_self" class="llinkA">Deleting
|
|
Data</a> </li>
|
|
</ul>
|
|
<br>
|
|
<li>Advanced</li>
|
|
|
|
<ul>
|
|
<li><a href="filters.html" target="_self" class=
|
|
"llink">Filters</a> </li>
|
|
</ul>
|
|
|
|
<ul>
|
|
<li><a href="subset-by-example.html" target="_self" class=
|
|
"llink">Subset By Example</a> </li>
|
|
<li><a href="exporting-xml.html" target="_self" class="llink">XML Export</a> </li>
|
|
</ul>
|
|
</ul>
|
|
|
|
<table border="0" cellpadding="5" cellspacing="0" width="100%">
|
|
<tbody>
|
|
<tr>
|
|
<td class="content2background"><img style="width: 160px; height: 1px;"
|
|
alt="" src="architecture-Dateien/vgradp.gif" /></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</td>
|
|
|
|
<td class="contentbackground" height="418" valign="top" width="86%">
|
|
<div align="right">
|
|
<table border="0" cellpadding="0" cellspacing="0" height="542" width="98%">
|
|
<tbody>
|
|
<tr>
|
|
<td class="contentbackground" height="21" width="100%"> </td>
|
|
</tr>
|
|
|
|
<tr>
|
|
<td class="content" height="520" valign="top" width="100%">
|
|
|
|
This tutorial shows you how to delete rows from database without
|
|
violating integrity of the remaining data.<br />
|
|
<br />
|
|
Deleting data is closely coupled with data exportation. The same
|
|
restrictions are applied, no row will be deleted which is not also
|
|
exported. This way you can always restore deleted rows by
|
|
re-importing them.<br />
|
|
<br />
|
|
A <span style="font-style: italic;">delete-script</span> contains
|
|
DELETE statements for all exported rows which are not associated
|
|
with any non-exported row according to the restricted data model.
|
|
Additionally tables can be excluded from deletion so that their
|
|
rows will never be deleted (see step 2)<br />
|
|
|
|
<h4><br />
|
|
Objective</h4>Delete all projects which have ended before
|
|
2008-01-01. Delete all associated participations too but no role
|
|
and no employee.<br />
|
|
<br />
|
|
|
|
<h4>Step 1. Delete projects<br /></h4>Create a new model. Select
|
|
table PROJECT as subject table and enter T.END_DATE<'2008-01-01'
|
|
as subject condition.<br />
|
|
<br />
|
|
In order to prevent the deletion of a PROJECT_PARTICIPATION, which
|
|
is not associated with an old project but with a role of a
|
|
participation in an old project, disable the association from ROLE
|
|
to PROJECT_PARTICIPATION. Do the same with the association from
|
|
EMPLOYEE to PROJECT_PARTICIPATION.<br />
|
|
<br />
|
|
Disable all associations to EMPLOYEE too. This will reduce the set
|
|
of exported employee-rows to those employees who have participated
|
|
in an old project.<br />
|
|
<br />
|
|
<img alt="" src="delt1.gif" /><br />
|
|
<br />
|
|
<br />
|
|
Open the data export dialog. Enter <span style=
|
|
"font-style: italic;">delete-old-projects.sql</span> as name for
|
|
the delete-script and start the exportation.<br />
|
|
|
|
<p><img src="deleting-data-Dateien/screen17.png" /><br /></p>
|
|
|
|
<p><img alt="" src="delt2.gif" /><br /></p>
|
|
|
|
<p><br />
|
|
Jailer has generated a script for the deletion of the two old
|
|
projects and the six associated participations, but also for the
|
|
deletion of six employees and one role. This role is the role of an
|
|
participation in an old project, but not of a remaining one. The
|
|
six employees have participated in an old project but in none of
|
|
the remaining projects.<br />
|
|
<br /></p>
|
|
|
|
<h4>Step 2. Prevent employees and roles from being
|
|
deleted<br /></h4>Open the data model editor (<span style=
|
|
"font-style: italic;">Tools->Data Model Editor</span>). Edit the
|
|
table EMPLOYEE and switch on the check-box "<span style=
|
|
"font-style: italic;">exclude from deletion</span>". Do the same
|
|
with table ROLE.<br />
|
|
(Since version 6.7 it's also possible to do this via the table's
|
|
context menu)<br />
|
|
<br />
|
|
<img alt="" src="delt3.gif" style=
|
|
"width: 600px; height: 350px;" /><br />
|
|
|
|
<p><br />
|
|
<span style="font-style: italic;">Export Data</span> again.
|
|
Employees and roles are now excluded from deletion:<br />
|
|
<br /></p>
|
|
|
|
<table style=
|
|
"background-color: rgb(51, 102, 255); text-align: left;" border="0"
|
|
cellpadding="2" cellspacing="0">
|
|
<tbody>
|
|
<tr>
|
|
<td style=
|
|
"vertical-align: top; height: 24px; background-color: rgb(202, 225, 235);">
|
|
<big><span style="font-family: monospace;"><span style=
|
|
"font-weight: bold;"><span style=
|
|
"font-family: monospace;">delete-old-projects.sql</span></span></span>
|
|
</big><br /></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
|
|
<table style="width: 100%; text-align: left;" border="0"
|
|
cellpadding="0" cellspacing="0">
|
|
<tbody>
|
|
<tr>
|
|
<td style=
|
|
"vertical-align: top; background-color: rgb(202, 225, 235);"
|
|
colspan="2"><span style="font-family: monospace;">--
|
|
generated by Jailer at Wed Fri May 02 12:37:07 CEST
|
|
2016 from wisser@desktop<br />
|
|
--<br />
|
|
-- extraction model: EMPLOYEE where NAME='SCOTT'
|
|
(extractionmodel/scott.csv)<br />
|
|
-- database URL:
|
|
jdbc:db2://localhost/wisser<br />
|
|
-- database user: scott<br />
|
|
-- Exported Entities: 25<br />
|
|
--
|
|
DEPARTMENT
|
|
2<br />
|
|
--
|
|
EMPLOYEE
|
|
8<br />
|
|
--
|
|
PROJECT
|
|
2<br />
|
|
--
|
|
PROJECT_PARTICIPATION
|
|
6<br />
|
|
--
|
|
ROLE
|
|
3<br />
|
|
--
|
|
SALARYGRADE
|
|
4<br />
|
|
--<br />
|
|
-- Tabu-tables: { EMPLOYEE, ROLE }<br />
|
|
--<br />
|
|
-- Deleted Entities: 8<br />
|
|
--
|
|
DEPARTMENT 0
|
|
(-2)<br />
|
|
--
|
|
PROJECT
|
|
2<br />
|
|
--
|
|
PROJECT_PARTICIPATION
|
|
6<br />
|
|
-- SALARYGRADE 0
|
|
(-4)<br />
|
|
<br />
|
|
<br />
|
|
Delete from PROJECT_PARTICIPATION Where (PROJECTNO, EMPNO,
|
|
START_DATE) in (values (1001, 7369, '2006-01-01'), (1001,
|
|
7788, '2006-05-15'), (1001, 7902, '2006-01-01'), (1002, 7782,
|
|
'2006-08-22'), (1002, 7876, '2006-08-22'), (1002, 7934,
|
|
'2007-01-01'));<br />
|
|
Delete from PROJECT Where PROJECTNO in (1001, 1002);<br />
|
|
<br /></span></td>
|
|
</tr>
|
|
</tbody>
|
|
</table><br />
|
|
<br />
|
|
|
|
<p><br />
|
|
<br /></p>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
|
|
<p> </p>
|
|
</td>
|
|
</tr>
|
|
|
|
<tr>
|
|
<td height="12" valign="top" width="14%"><br /></td>
|
|
|
|
<td><br /></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</div>
|
|
</body>
|
|
</html>
|