mirror of
https://github.com/Wisser/Jailer.git
synced 2026-05-25 03:00:43 -05:00
rwisser
e7814697a9
git-svn-id: https://svn.code.sf.net/p/jailer/code/trunk@1254 3dd849cd-670e-4645-a7cd-dd197c8d0e81
253 lines
9.8 KiB
HTML
253 lines
9.8 KiB
HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
|
|
<html><head><title>Jailer - Deleting Data</title>
|
|
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
|
|
<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="deleting-data-Dateien/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="0" cellspacing="0">
|
|
<tbody>
|
|
<tr>
|
|
<td><img src="deleting-data-Dateien/logo.png" hspace="10"></td>
|
|
<td class="slogan" width="100%">Database Subsetting Tool</td>
|
|
<td style="text-align: right; width: 100%;">
|
|
<table border="0" cellpadding="0" cellspacing="0">
|
|
<tbody>
|
|
<tr>
|
|
<td><a href="http://sourceforge.net/projects/jailer/"><img src="http://sflogo.sourceforge.net/sflogo.php?group_id=197260&type=1" alt="SourceForge.net Logo" style="border: 0px solid ;"></a></td>
|
|
</tr>
|
|
<tr>
|
|
<td><a href="http://www.java.net"><img src="javanet_button_90.gif" alt="java.net Member Button: 90px wide" border="0" height="25" width="90"></a></td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</td>
|
|
</tr>
|
|
<tr>
|
|
<td colspan="2" class="nav1background" height="33" 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 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" height="100%" valign="top" width="14%">
|
|
<div align="center">
|
|
<center>
|
|
<table border="0" cellpadding="0" cellspacing="0" width="100%">
|
|
<tbody>
|
|
<tr>
|
|
<td width="100%"><br>
|
|
</td>
|
|
</tr>
|
|
</tbody>
|
|
</table>
|
|
</center>
|
|
</div>
|
|
<ul>
|
|
<li><small>Basic</small></li><ul><li><a href="exporting-data.htm" target="_self" class="llink">SQL
|
|
Export</a></li></ul>
|
|
|
|
<ul><li><a href="exporting-xml.html" target="_self" class="llink">XML
|
|
Export</a></li><li><a href="deleting-data.htm" target="_self" class="llinkA">Deleting Data</a></li></ul>
|
|
<li><small>Advanced</small></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></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%"> <br>
|
|
</td>
|
|
</tr>
|
|
<tr>
|
|
<td class="content" height="520" valign="top" width="100%"> <small><small> </small></small><br>
|
|
This tutorial shows you how to delete rows from database without
|
|
violating integrity of the remaining data.<br>
|
|
<br>
|
|
Deleting data <span style="font-style: italic;"></span>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>
|
|
<h3> </h3>
|
|
<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>
|
|
<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><span style="font-family: monospace;"></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;"></span><span style="color: rgb(255, 0, 0); font-family: monospace;"></span><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>
|
|
</p>
|
|
<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> |