1 Reply Latest reply on Apr 29, 2008 8:33 PM by Jason Long

    Create MySQL dump

    Kevin T. Newbie

      Hello,


      it's possible to create mysql-dumps in seam?


      It should be an easy way to show the last database content on a site p.e. in a textfield. (like export in phpmyadmin)


      p.e.


      CREATE TABLE customer (
        username varchar(15) NOT NULL,
        name varchar(100) NOT NULL,
        passwort varchar(15) NOT NULL,
        PRIMARY KEY  (username)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
      
      INSERT INTO customer (username, name, password) VALUES
      ('gavin', 'Gavin King', 'foobar'),
      ('demo', 'Demo User', 'demo');
      ...



      Are there experiences? Have someone snippets or tutorials?


      THX 4 HELP


      Kev

        • 1. Re: Create MySQL dump
          Jason Long Novice

          I did the following to call native commands from a Seam component.  I saved the output of these commands in a seam component for display.


          I am not sure if this is what you are looking for.  This code is quite old and not in use currently, but did work at the time.



          @Stateless
          @Name("dbMaintenanceDAO")
          public class DBMaintenanceDAOBean extends GenericEjb3DAO<DBMaintenance, Long> implements DBMaintenanceDAO {
          
              public DBMaintenanceDAOBean() { super(DBMaintenance.class); }
          
          
          
              public void testJob() {
                  try {
                      Scheduler sched = getQuartzScheduler();
                      CronTrigger trigger = new CronTrigger("test", "test", "0,15,30,45 * * ? * *");
                      JobDetail jobDetail = new JobDetail("test", Scheduler.DEFAULT_GROUP, TestJob.class);
                      sched.scheduleJob(jobDetail, trigger);
                      System.out.println("Job test scheduled now ..");
                  } catch (ObjectAlreadyExistsException e) {
                  } catch (Exception e) {
                      e.printStackTrace();               
                  }
              }
              
              public void scheduleVacuum(String basePath) {
                  try {
                      Scheduler sched = getQuartzScheduler();
                /*      
                      for(String str : sched.getJobNames("DEFAULT")) {
                          JobDetail jd = sched.getJobDetail("backupDB","DEFAULT");
                          System.out.println(str);
                      }
                 */     
                      CronTrigger trigger = new CronTrigger("vacuumDB", "DBMaintenance", "0 15 * ? * *");
                      JobDetail jobDetail = new JobDetail("vacuumDB", Scheduler.DEFAULT_GROUP, VacuumDBJob.class);
                      jobDetail.getJobDataMap().put("BASE_PATH", basePath);
                      sched.scheduleJob(jobDetail, trigger);
                      System.out.println("Job vacuumDB scheduled now ..");
                  } catch (ObjectAlreadyExistsException e) {
                  } catch (Exception e) {
                      e.printStackTrace();               
                  }
              }
              
              public void scheduleBackup(String basePath) {
                  try {
                      Scheduler sched = getQuartzScheduler();
                      CronTrigger trigger = new CronTrigger("backupDB", "DBMaintenance", "0 0 * ? * *");
                      JobDetail jobDetail = new JobDetail("backupDB", Scheduler.DEFAULT_GROUP, BackupDBJob.class);
                      jobDetail.getJobDataMap().put("BASE_PATH", basePath);
                      sched.scheduleJob(jobDetail, trigger);
                      System.out.println("Job backupDB scheduled now ..");
                  } catch (ObjectAlreadyExistsException e) {
                  } catch (Exception e) {
                      e.printStackTrace();               
                  }
              }
          
              public void vacuum(String name, String basePath) {
                  try {
                      DBMaintenance dbm = new DBMaintenance();
                      String[] cmd = { "cmd", "/c", basePath + "util\\" + "vacuumdb.exe -f -v -z -e -p 5432 -U postgres -d mydb_name" };
                      String[] results = execRuntime(cmd);
                      dbm.setName(name);
                      dbm.setStreamError(results[0]);
                      dbm.setStreamInput(results[1]);
                      dbm.setDateCompleted(new Date());
                      makePersistent(dbm);
                  } catch (Exception e) {
                      e.printStackTrace();               
                  }
              }
              
              public String[] execRuntime(String[] cmd) {
                  String[] strArry = new String[2];
                  Runtime runtime = Runtime.getRuntime();   
                  try {
                      Process p = runtime.exec(cmd);
                      BufferedReader err = new BufferedReader(new InputStreamReader(p.getErrorStream()));
                      String lineErr = null;
                      StringBuffer sbErrorStream = new StringBuffer();
                      while ((lineErr = err.readLine()) != null) {
                          sbErrorStream.append(lineErr).append(System.getProperty("line.separator"));
                      }            
                      
                      BufferedReader in = new BufferedReader(new InputStreamReader(p.getInputStream()));
                      String lineIn = null;
                      StringBuffer sbInputStream = new StringBuffer();
                      while ((lineIn = in.readLine()) != null) {
                        sbErrorStream.append(lineIn).append(System.getProperty("line.separator"));
                      }           
          
                      strArry[0] = sbErrorStream.toString();
                      strArry[1] = sbInputStream.toString();
                  } catch (Exception e) {
                      e.printStackTrace();
                  }
                  return strArry;
          
              }
              
              @SuppressWarnings("unchecked")
              public List<DBMaintenance> getRecentDBMaintenances() {
                  List<DBMaintenance> result = null;
                  result = entityManager.createNamedQuery("pipetracker.model.db.maintenance.list.recent")
                             .setMaxResults(250)
                             .getResultList();
                  for(DBMaintenance dbm : result) {
                      if(dbm.getDateCompleted() == null) dbm.setDateCompleted(dbm.getDateStarted());
                      dbm.setTime((dbm.getDateCompleted().getTime()-dbm.getDateStarted().getTime())/1000.0);
                  }
              return result;        
              }
          
          
          
              public void backup(String name, String basePath) {
                try {
                  Date date = new Date();
                  String fileName = date.getTime()+".backup";
                  String filePath = basePath + "backup\\" + fileName;
                  DBMaintenance dbm = new DBMaintenance();
                  dbm.setName(name);
                  dbm.setFileName(fileName);
                  String[] cmd = { "cmd", "/c", basePath + "util\\" + "pg_dump.exe -i -h localhost -p 5432 -U postgres -F c -b -v -f " + filePath + " mydb_name" };
                  String[] results = execRuntime(cmd);
                  dbm.setStreamError(results[0]);
                  dbm.setStreamInput(results[1]);
                  File file = new File(filePath);
                  dbm.setFileSize(file.length());
                  dbm.setDateCompleted(new Date());
                  makePersistent(dbm);
                  
              } catch (Exception e) {
                  e.printStackTrace();               
              }
              }
          }