• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Clean database

 
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hey,

is there a way (i dont mind to use tool) to clean the DB.
Dont delete the tables, only delete the content.

if i do it one by one i failed with constraints error.

can i some how get the order or have a workaround or tool that will do it for me?

Thank you
 
Sheriff
Posts: 22510
122
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
As long as there are no circular references, you can find the order by trying. By following the next approach you'll set up several (overlapping) tree structures, which you can then delete from root to leaf.

If a table can be cleared (DELETE FROM tablename), then it is a root node. If it is not, you should be able to read from the error which table(s) depend(s) on that table, and those tables will be the parents of the current table. You'll just continue until a table can be deleted without problems.

You could automatically build these trees, if you can determine from the error messages which table you should try to delete next.


Alternatively, you can store all tables in a collection, and keep trying to clear one at a time, removing all successfully cleared tables, until either nothing can be removed (circular references) or the collection is empty:

 
avihai marchiano
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Too much complicated.

There is no tool or command to get the order?
 
author & internet detective
Posts: 40801
829
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Avihai,
Another approach is to:
1) Disable all constraints (This can be done through SQL - the command varies by database)
2) delete data from all tables
3) Re-enable all constraints (mirror image of the sql from step 1)
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Trunc table
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Connect to DB using Squirrel SQL, select all tables, right-click, delete records
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Drop the database and restore from a backup, excluding data?
 
avihai marchiano
Ranch Hand
Posts: 342
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot for your answers , i thought that can be an easier way.

Just a sample command.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic