Changeset 5852


Ignore:
Timestamp:
Nov 7, 2011, 9:16:54 AM (10 years ago)
Author:
Nicklas Nordborg
Message:

References #1630: Migrate from MySQL to PostgreSQL

Implemented the export functionality. It will currently only work on a MySQL database and generates a data file for each table. Hopefully, the files can be read by PostgreSQL, but we need to be able to create an empty database without foreign keys first...

Location:
trunk/src
Files:
7 edited

Legend:

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

    r5429 r5852  
    207207    return sql;
    208208  }
    209 
     209  /**
     210    Put quotes (") around the name.
     211  */
     212  @Override
     213  public String getQuotedName(String name)
     214  {
     215    return "\"" + name + "\"";
     216  }
    210217  // -------------------------------------------
    211218
  • trunk/src/core/net/sf/basedb/core/dbengine/DbEngine.java

    r5756 r5852  
    4242public interface DbEngine
    4343{
     44  /**
     45    Quote the name with the database specific quote character.
     46   
     47    @param name The name of an object in the database
     48    @return A quoted name
     49    @since 3.1
     50  */
     51  public String getQuotedName(String name);
    4452 
    4553  /**
  • trunk/src/core/net/sf/basedb/core/dbengine/DefaultDbEngine.java

    r5756 r5852  
    150150  // -------------------------------------------
    151151
    152   /**
    153     Put quotes (") around the name.
    154   */
    155   private String getQuotedName(String name)
    156   {
    157     return "\"" + name + "\"";
    158   }
     152
    159153}
  • trunk/src/core/net/sf/basedb/core/dbengine/MySQLEngine.java

    r5756 r5852  
    221221    return "varchar(" + length + ") CHARACTER SET utf8 COLLATE utf8_bin";
    222222  }
     223  /**
     224    Put quotes (`) around the name.
     225  */
     226  @Override
     227  public String getQuotedName(String name)
     228  {
     229    return "`" + name + "`";
     230  }
    223231  // -------------------------------------------
    224  
    225   /**
    226     Put quotes (`) around the name.
    227   */
    228   private String getQuotedName(String name)
    229   {
    230     return "`" + name + "`";
    231   }
     232
    232233}
  • trunk/src/core/net/sf/basedb/core/dbengine/PostgresDbEngine.java

    r5756 r5852  
    258258  // -------------------------------------------
    259259 
    260   /**
    261     Put quotes (") around the name.
    262   */
    263   private String getQuotedName(String name)
    264   {
    265     return "\"" + name + "\"";
    266   }
    267260
    268261}
  • trunk/src/core/net/sf/basedb/core/dbengine/TableInfo.java

    r5384 r5852  
    2222package net.sf.basedb.core.dbengine;
    2323
     24import java.sql.Connection;
    2425import java.sql.DatabaseMetaData;
    2526import java.sql.ResultSet;
    2627import java.sql.SQLException;
     28import java.sql.Statement;
    2729import java.sql.Types;
    2830import java.util.Arrays;
     
    3234import java.util.Iterator;
    3335import java.util.LinkedHashSet;
     36import java.util.LinkedList;
    3437import java.util.List;
    3538import java.util.Map;
     
    6366{
    6467
    65   private Table table;
     68  /**
     69    List all tables that are present in the given catalog and/or schema.
     70    @param metaData A metadata object that is connected to a database
     71    @param catalog The name of the catalog, or null
     72    @param schema The name of the schema, or null
     73    @return A list of tables
     74    @throws SQLException
     75    @since 3.1
     76  */
     77  public static List<Table> listTables(DatabaseMetaData metaData, String catalog, String schema)
     78    throws SQLException
     79  {
     80    List<Table> tables = new LinkedList<Table>();
     81    ResultSet result = metaData.getTables(catalog, schema, null, null);
     82    while (result.next())
     83    {
     84      String tableName = result.getString("TABLE_NAME");
     85      String tableCatalog = result.getString("TABLE_CAT");
     86      String tableSchema = result.getString("TABLE_SCHEM");
     87      Table table = new Table();
     88      table.setName(tableName);
     89      table.setCatalog(tableCatalog);
     90      table.setSchema(tableSchema);
     91      tables.add(table);
     92    }
     93    return tables;
     94  }
     95
     96 
     97  private final Table table;
    6698  private Dialect dialect;
    6799 
     
    70102  private Set<ForeignKeyInfo> foreignKeys;
    71103  private Set<IndexInfo> indexes;
     104  private long numberOfRows = -1;
     105 
    72106 
    73107 
     
    260294    }
    261295
     296  }
     297 
     298  /**
     299    Get the table this information is representing.
     300    @return The table object
     301    @since 3.1
     302  */
     303  public Table getTable()
     304  {
     305    return table;
    262306  }
    263307 
     
    371415  }
    372416
     417  /**
     418    Get the fully quialified and quoted table name using
     419    the quoting rules from the given DBEngine
     420    @param engine A DbEngine
     421    @return The fully quoted table name
     422    @since 3.1
     423  */
     424  public String getFullQuotedTableName(DbEngine engine)
     425  {
     426    StringBuilder name = new StringBuilder();
     427    if (table.getCatalog() != null)
     428    {
     429      name.append(engine.getQuotedName(table.getCatalog())).append(".");
     430    }
     431    if (table.getSchema() != null)
     432    {
     433      name.append(engine.getQuotedName(table.getSchema())).append(".");
     434    }
     435    name.append(engine.getQuotedName(table.getName()));
     436    return name.toString();
     437  }
     438 
     439  /**
     440    Count the number of rows in the table.
     441    @param connection The connection to user for counting
     442    @since 3.1
     443  */
     444  public long getRowCount(DbEngine engine, Connection connection)
     445    throws SQLException
     446  {
     447    if (numberOfRows < 0)
     448    {
     449     
     450      Statement st = null;
     451      ResultSet result = null;
     452      try
     453      {
     454        st = connection.createStatement();
     455        StringBuilder sql = new StringBuilder("SELECT count(*) FROM ");
     456        sql.append(getFullQuotedTableName(engine));
     457        result = st.executeQuery(sql.toString());
     458        result.next();
     459        numberOfRows = result.getLong(1);
     460      }
     461      finally
     462      {
     463        if (result != null) result.close();
     464        if (st != null) st.close();
     465      }
     466    }
     467    return numberOfRows;
     468  }
     469 
    373470  private static final Set<Integer> STRING_TYPE = new HashSet<Integer>(
    374471      Arrays.asList(
     
    381478  */
    382479  public static class ColumnInfo
     480    implements Comparable<ColumnInfo>
    383481  {
    384482    private final String name;
     
    449547        (isUnique ? " UNIQUE" : "");
    450548    }
     549
     550    @Override
     551    public int compareTo(ColumnInfo o)
     552    {
     553      return name.compareTo(o.name);
     554    }
    451555  }
    452556 
  • trunk/src/install/net/sf/basedb/install/InitDB.java

    r5833 r5852  
    3232import net.sf.basedb.core.HibernateUtil;
    3333import net.sf.basedb.core.Install;
     34import net.sf.basedb.core.Migration;
    3435import net.sf.basedb.core.ProgressReporter;
    3536import net.sf.basedb.core.Update;
     
    125126        Application.stop();
    126127      }
     128      else if ("migrate".equals(cmd))
     129      {
     130        // args[1] = 'import' or 'export'
     131        // args[2] = path to output dir
     132        if (args.length != 3)
     133        {
     134          showUsage(cmd);
     135          return;
     136        }
     137        boolean export = "export".equals(args[1]);
     138       
     139        showDbInfo(null);
     140        String path = args[2];
     141        if (export)
     142        {
     143          Application.start(false);
     144          Migration.exportAll(path, new ConsoleProgressReporter(false));
     145        }
     146        else
     147        {
     148          // Create empty database without indexes and foreign keys
     149          // Import all data
     150          // Add indexes, foreign keys, etc. to database
     151        }
     152        Application.stop();
     153      }
    127154      else
    128155      {
     
    151178      System.out.println("       root password: The password to give to the root user account");
    152179    }
    153    
     180    else if ("migrate".equals(cmd))
     181    {
     182      System.out.println("Usage: migrate.sh <cmd> <path>");
     183      System.out.println("       cmd    : export | import");
     184      System.out.println("       path   : The path to a directory where data files should be saved/read");
     185    }
    154186  }
    155187
Note: See TracChangeset for help on using the changeset viewer.