Friday, 29 December 2023

The DUAL table

I'm working with Oracle databases (version 19 for now), and I always wondered about the weird DUAL table1 that I require in my work.

Things like this:

select sysdate from dual;
select seq_s_ordertable.nextval from dual;

Well, apparently we have Charles "Chuck" Weiss2 to thank for it.

One of the reasons is that the FROM clause in Oracle SQL syntax is mandatory.

Interestingly, Charles originally created the DUAL table with two rows, hence the name DUAL. Nowadays it's one row, but the name's stuck.

It's part of the Data Dictionary of the SYS user. Changing the DUAL table will cause problems!3

References

[1] Wikipedia - DUAL table
https://en.wikipedia.org/wiki/DUAL_table
[2] Wikipedia - Charles Weiss
https://en.wikipedia.org/wiki/Charles_Weiss
[3] Ask tom - All about the DUAL table
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

Thursday, 21 December 2023

Using Java to Zip/Unzip files

There's been a ZipFile class in Java sinds a long time. But nowadays sinds JDK7 there's also a ZipFileSystem which is a bit easier to work with in some cases and can do more things.

Below are two examples, one using ZipFile and one using ZipFileSystem.

Using ZipFile

Unzipping works as follows:

  @Test
  public void testOldZip() throws URISyntaxException, IOException {
    URL resource = UnzipperTest.class.getResource("/test.zip");
    assert resource != null;
    Path path = Path.of(resource.toURI());
    try (var zipFile = new ZipFile(path.toFile())) {
      Enumeration<? extends ZipEntry> entries = zipFile.entries();
      while (entries.hasMoreElements()) {
        ZipEntry nextElement = entries.nextElement();
        if (nextElement.isDirectory()) {
          log.add("File " + nextElement.getName() + " from zipfile is a directory - skipped");
        }
        else if (nextElement.getName().contains("__MACOSX")) {
          log.add("File " + nextElement.getName() + " from zipfile is a MACOS file/dir - skipped");
        }
        else {
          log.add("File " + nextElement.getName() + " retrieved from zip.");
          var filename = nextElement.getName();
          InputStream inputStream = zipFile.getInputStream(nextElement);
          result.add(new FileContents(filename, inputStream.readAllBytes()));
        }
      }
    }
    assertThat(result).hasSize(6);
    assertThat(result.stream().map(x -> x.filename).collect(Collectors.toList())).isEqualTo(
        List.of("test/test1.txt",
            "test/test/ziptest.zip",
            "test/test/ziptest/zippem2.txt",
            "test/test/ziptest/zippem.txt",
            "test/test2/test2.txt",
            "test/test2/text3.xml"));
    assertThat(log).hasSize(11)
        .isEqualTo(List.of("File test/ from zipfile is a directory - skipped",
            "File test/test1.txt retrieved from zip.",
            "File test/test/ from zipfile is a directory - skipped",
            "File test/test/ziptest.zip retrieved from zip.",
            "File test/test/ziptest/ from zipfile is a directory - skipped",
            "File test/test/ziptest/zippem2.txt retrieved from zip.",
            "File test/test/ziptest/zippem.txt retrieved from zip.",
            "File test/test2/ from zipfile is a directory - skipped",
            "File test/test2/test2.txt retrieved from zip.",
            "File test/test2/test3/ from zipfile is a directory - skipped",
            "File test/test2/text3.xml retrieved from zip."));
  }

Using ZipFileSystem

Apparently it is also possible to mount a zip file as a FileSystem (sinds JDK 7).

It's a bit easier to work with, and has less problems with leaking resources/streams, and allows easy editing and removing items from the zip.

On the other hand, some FileSystem operations are not available in a ZipFileSystem, and then you get a UnsupportedOperationException.

  private static class ZipVisitor
      extends SimpleFileVisitor<Path> {

    private final List<FileContents> result;
    private final List<String> log;

    private ZipVisitor(List<FileContents> result, List<String> log) {
      this.result = result;
      this.log = log;
    }

    @Override
    public FileVisitResult visitFile(Path file, BasicFileAttributes attrs) throws IOException {
      if (file.toString().contains("__MACOSX")) {
        log.add("File " + file + " from zipfile is a MACOS file/dir - skipped");
        return FileVisitResult.CONTINUE;
      }
      if (Objects.equals(file.toString(), ".DS_Store")) {
        log.add("File " + file + " from zipfile is a .DS_Store file/dir - skipped");
        return FileVisitResult.CONTINUE;
      }
      log.add("File " + file + " retrieved from zip.");
      String filename = file.toString();
      // file.toFile() -> unsupported operation.
      result.add(new FileContents(filename, Files.readAllBytes(file)));
      return FileVisitResult.CONTINUE;
    }
  }

  @Test
  public void testNewZip() throws URISyntaxException, IOException {
    URL resource = UnzipperTest.class.getResource("/test.zip");
    assert resource != null;
    var path = Path.of(resource.toURI());
    try (FileSystem filesystem = FileSystems.newFileSystem(path, Collections.emptyMap())) {
      var rootDirectories = filesystem.getRootDirectories();
      rootDirectories.forEach(root ->
              //walk the zip file tree
          {
            try {
              Files.walkFileTree(root, new ZipVisitor(result, log));
            }
            catch (IOException e) {
              throw new RuntimeException(e);
            }
          }
      );
    }
    assertThat(result).hasSize(6);
    assertThat(result.stream().map(x -> x.filename).collect(Collectors.toList())).isEqualTo(
        List.of("/test/test2/text3.xml",
            "/test/test2/test2.txt",
            "/test/test/ziptest/zippem.txt",
            "/test/test/ziptest/zippem2.txt",
            "/test/test/ziptest.zip",
            "/test/test1.txt"));
    assertThat(log).hasSize(6)
        .isEqualTo(List.of("File /test/test2/text3.xml retrieved from zip.",
            "File /test/test2/test2.txt retrieved from zip.",
            "File /test/test/ziptest/zippem.txt retrieved from zip.",
            "File /test/test/ziptest/zippem2.txt retrieved from zip.",
            "File /test/test/ziptest.zip retrieved from zip.",
            "File /test/test1.txt retrieved from zip."));
  }

References

fahd.blog - Java 7: Working with Zip Files
https://fahdshariff.blogspot.com/2011/08/java-7-working-with-zip-files.html
Oracle Java Documentation - Zip File System Provider
https://docs.oracle.com/javase/8/docs/technotes/guides/io/fsp/zipfilesystemprovider.html