Changeset 5855


Ignore:
Timestamp:
Nov 8, 2011, 3:42:17 PM (10 years ago)
Author:
Nicklas Nordborg
Message:

References #1630: Migrate from MySQL to PostgreSQL

Using a file to store which columns that are exported. This will make it easier to detect if there is a configuration mismatch between the source and destination databases (eg. different columns in some tables).

Set 'iso' datestyle when importing since the default is client-dependent.

Explicitely set 'UTF8' encoding when importing since the default is client-dependent.

Location:
trunk/src/core/net/sf/basedb/core
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/core/net/sf/basedb/core/Migration.java

    r5854 r5855  
    2626import java.io.FileNotFoundException;
    2727import java.io.IOException;
     28import java.io.InputStreamReader;
    2829import java.io.OutputStreamWriter;
     30import java.io.StringWriter;
    2931import java.io.Writer;
    3032import java.sql.Connection;
     
    298300      columns.addAll(table.getColumns());
    299301     
     302      // File to store all column names
     303      File columnsFile = getColumnsFile(table);
    300304      // Create SELECT <columns> FROM <table>
     305      Writer writer = new OutputStreamWriter(FileUtil.getOutputStream(columnsFile), "UTF-8");
    301306      Statement st = connection.createStatement();
    302307      StringBuilder select = new StringBuilder("SELECT ");
     
    306311        select.append(separator);
    307312        select.append(engine.getQuotedName(c.getName()));
     313        writer.append(separator);
     314        writer.append(c.getName());
    308315        separator = ",";
    309316        usedSqlTypes.add(c.getSqlType());
    310317      }
    311318      select.append(" FROM ").append(table.getFullQuotedTableName(engine));
     319      writer.flush();
     320      writer.close();
    312321
    313322      // Create output file
    314323      File exportFile = getDataFile(table);
    315       Writer writer = new BufferedWriter(
     324      writer = new BufferedWriter(
    316325          new OutputStreamWriter(FileUtil.getOutputStream(exportFile), "UTF-8"));
    317326
     
    385394      }
    386395      TableInfo info = new TableInfo(table, metaData);
     396      // Check that data files exists
    387397      File importFile = getDataFile(info);
    388398      if (!importFile.exists())
    389399      {
    390         throw new FileNotFoundException(importFile.getPath());
    391       }
     400        throw new FileNotFoundException(importFile.getAbsolutePath());
     401      }
     402      File columnsFile = getColumnsFile(info);
     403      if (!columnsFile.exists())
     404      {
     405        throw new FileNotFoundException(columnsFile.getAbsolutePath());
     406      }
     407     
    392408      totalFileSize += importFile.length();
    393409      tables.add(info);
     
    406422   
    407423    progress.display(0, "Found " + (tables.size()+dynamicDataFiles.size()) + " files with " +
    408         Values.formatBytes(totalFileSize, 2) + " data\n"
     424        Values.formatBytes(totalFileSize, 2) + " data"
    409425        );
    410426   
     
    426442    // Import data for each table in the static database
    427443    Statement st = connection.createStatement();
     444    // We are using ISO date format in the exported files
     445    st.executeUpdate("set datestyle TO 'iso'");
    428446    long totalImported = 0;
    429447    for (TableInfo table : tables)
    430448    {
    431449      File importFile = getDataFile(table);
     450
    432451      if (importFile.length() > 0)
    433452      {
     
    436455            tableName + ": importing " + Values.formatBytes(importFile.length(), 2) + "...");
    437456       
    438         // Get columns in sorted order
    439         SortedSet<ColumnInfo> columns =  new TreeSet<ColumnInfo>();
    440         columns.addAll(table.getColumns());
     457        // Load columns from file
     458        File columnsFile = getColumnsFile(table);
     459        StringWriter sin = new StringWriter((int)columnsFile.length());
     460        FileUtil.copy(new InputStreamReader(FileUtil.getInputStream(columnsFile), "UTF-8"), sin);
     461        String[] columns = sin.toString().split(",");
    441462       
    442463        // Create COPY (<columns>) FROM '<filename>'
     
    446467        String separator = "";
    447468        boolean hasIdColumn = false;
    448         for (ColumnInfo c : columns)
    449         {
     469        for (String col : columns)
     470        {
     471          col = col.trim();
     472          if (table.findColumn(col) == null)
     473          {
     474            throw new IOException("Column '" + col + "' is not found in table '" + tableName +
     475              "' but was exported to file: " + importFile.getAbsolutePath());
     476          }
    450477          copy.append(separator);
    451           copy.append(engine.getQuotedName(c.getName()));
     478          copy.append(engine.getQuotedName(col));
    452479          separator = ",";
    453           hasIdColumn |= "id".equals(c.getName());
     480          hasIdColumn |= "id".equals(col);
    454481        }
    455482        copy.append(") FROM '").append(importFile.getAbsolutePath() + "'");
     483        copy.append(" WITH ENCODING 'UTF8'");
    456484   
    457485        // Import the data
     
    501529        throw new AssertionError("File is not a dynamic data file: " + dynamicDataFile.getAbsolutePath());
    502530      }
     531     
    503532      int virtualDbId = Values.getInt(m.group(1));
    504533      String tableType = m.group(2).toLowerCase();
     
    532561      HibernateUtil.createVirtualTable(vdb, vTable);
    533562     
    534       // Get columns in sorted order
    535       SortedSet<String> columns =  new TreeSet<String>();
    536       for (VirtualColumn col : vTable.getColumns(vdb.getRawDataType()))
    537       {
    538         columns.add(col.getColumn());
    539       }
    540      
     563      // Load columns from file
     564      File columnsFile = new File(directory, dynamicDataFile.getName().replace(".data", ".columns"));
     565      StringWriter sin = new StringWriter((int)columnsFile.length());
     566      FileUtil.copy(new InputStreamReader(FileUtil.getInputStream(columnsFile), "UTF-8"), sin);
     567      String[] columns = sin.toString().split(",");
     568   
    541569      // Create COPY <table> (<columns>) FROM '<filename>'
    542570      StringBuilder copy = new StringBuilder("COPY ");
     
    555583      for (String col : columns)
    556584      {
     585        col = col.trim();
    557586        copy.append(separator);
    558587        copy.append(engine.getQuotedName(col));
     
    560589      }
    561590      copy.append(") FROM '").append(dynamicDataFile.getAbsolutePath() + "'");
     591      copy.append(" WITH ENCODING 'UTF8'");
    562592 
    563593      // Import the data
     
    683713  }
    684714 
     715  /**
     716    Get the data file for the given table.
     717  */
    685718  private File getDataFile(TableInfo table)
    686719  {
     
    688721  }
    689722 
     723  /**
     724    Get the columns file for the given table.
     725  */
     726  private File getColumnsFile(TableInfo table)
     727  {
     728    return new File(directory, table.getTable().getName().toLowerCase()+".columns");
     729  }
    690730 
    691731}
  • trunk/src/core/net/sf/basedb/core/dbengine/TableInfo.java

    r5854 r5855  
    415415
    416416  /**
     417    Find information about a column with the given name.
     418    @param column The name of the column
     419    @return The column information or null if no column is found
     420    @since 3.1
     421  */
     422  public ColumnInfo findColumn(String column)
     423  {
     424    for (ColumnInfo ciInfo : columns)
     425    {
     426      if (ciInfo.getName().equals(column))
     427      {
     428        return ciInfo;
     429      }
     430    }
     431    return null;
     432  }
     433 
     434  /**
    417435    Get the fully quialified and quoted table name using
    418436    the quoting rules from the given DBEngine
Note: See TracChangeset for help on using the changeset viewer.