Mssql To Postgresql Converter

[Armanda Kicks]

Asan organization, we are struggling economically, so I want to at least save us some cost. I realized that we have an RDS instance running SQL server standard edition which cost us up to 6.5k USD/month. I have realized that the same instance size cost half if it's running a different database engine like Postgresql.

So, before proposing such a migration, think about you testing plan to make sure that this migration does not alter your business results. I would suggest you to read this article to inventory all areas to deal with in preparing such a journey: -sql-server-mssql-vs-postgresql-comparison-details-what-differences

The Web Profile of the GlassFish Server supports the EJB 3.1 Lite specification, which allows enterprise beans within web applications, among other features. The full GlassFish Server supports the entire EJB 3.1 specification. For details, see JSR 318.

Full support for the J2EE v1.4 specification's CMP model. Extensive information on CMP is contained in chapters 10, 11, and 14 of the Enterprise JavaBeans Specification, v2.1. This includes the following:

Mapping refers to the ability to tie an object-based model to a relational model of data, usually the schema of a relational database. The CMP implementation provides the ability to tie a set of interrelated beans containing data and associated behaviors to the schema. This object representation of the database becomes part of the Java application. You can also customize this mapping to optimize these beans for the particular needs of an application. The result is a single data model through which both persistent database information and regular transient program data are accessed.

glassfish-ejb-jar.xml - The GlassFish Server standard file for assembling enterprise beans. For a detailed description, see "The glassfish-ejb-jar.xml File" in Oracle GlassFish Server Application Deployment Guide.

sun-cmp-mappings.xml - The mapping deployment descriptor file, which describes the mapping of CMP beans to tables in a database. For a detailed description, see "The sun-cmp-mappings.xml File" in Oracle GlassFish Server Application Deployment Guide.

The sun-cmp-mappings.xml file maps CMP fields and CMR fields (relationships) to the database. A primary table must be selected for each CMP bean, and optionally, multiple secondary tables. CMP fields are mapped to columns in either the primary or secondary table(s). CMR fields are mapped to pairs of column lists (normally, column lists are the lists of columns associated with primary and foreign keys).

The mapping information is developed in conjunction with the database schema (.dbschema) file, which can be automatically captured when you deploy the bean (see Automatic Database Schema Capture). You can manually generate the schema using the capture-schema utility (Using the capture-schema Utility).

Use of join tables in the database schema is supported for all types of relationships, not just many-to-many relationships. For general information about relationships, see section 10.3.7 of the Enterprise JavaBeans Specification, v2.1.

The GlassFish Server supports automatic primary key generation for EJB 1.1, 2.0, and 2.1 CMP beans. To specify automatic primary key generation, give the prim-key-class element in the ejb-jar.xml file the value java.lang.Object. CMP beans with automatically generated primary keys can participate in relationships with other CMP beans. The GlassFish Server does not support database-generated primary key values.

If the database schema is not created during deployment, the primary key column in the mapped table must be of type NUMERIC with a precision of 19 or more, and must not be mapped to any CMP field. The GlassFish Server generates unique values for the primary key column at runtime.

If an existing database table has a primary key column in which the values vary in length, but the type is CHAR instead of VARCHAR, the GlassFish Server automatically trims any extra spaces when retrieving primary key values. It is not a good practice to use a fixed length CHAR column as a primary key. Use this feature with schemas that cannot be changed, such as a schema inherited from a legacy application.

A managed field is a CMP or CMR field that is mapped to the same database column as another CMP or CMR field. CMP fields mapped to the same column and CMR fields mapped to exactly the same column lists always have the same value in memory. For CMR fields that share only a subset of their mapped columns, changes to the columns affect the relationship fields in memory differently. Basically, the GlassFish Server always tries to keep the state of the objects in memory synchronized with the database.

A managed field can have any fetched-with subelement. If the fetched-with subelement is , the -DAllowManagedFieldsInDefaultFetchGroup flag must be set to true. See Default Fetch Group Flags and "fetched-with" in Oracle GlassFish Server Application Deployment Guide.

Binary Large Object (BLOB) is a data type used to store values that do not correspond to other types such as numbers, strings, or dates. Java fields whose types implement java.io.Serializable or are represented as byte[] can be stored as BLOBs.

If a CMP field is defined as Serializable, it is serialized into a byte[] before being stored in the database. Similarly, the value fetched from the database is deserialized. However, if a CMP field is defined as byte[], it is stored directly instead of being serialized and deserialized when stored and fetched, respectively.

To enable BLOB support in the GlassFish Server environment, define a CMP field of type byte[] or a user-defined type that implements the java.io.Serializable interface. If you map the CMP bean to an existing database schema, map the field to a column of type BLOB.

For a list of the JDBC drivers currently supported by the GlassFish Server, see the Oracle GlassFish Server Release Notes. For configurations of supported and other drivers, see "Configuration Specifics for JDBC Drivers" in Oracle GlassFish Server Administration Guide.

For automatic mapping, you might need to change the default BLOB column length for the generated schema using the schema-generator-properties element in glassfish-ejb-jar.xml. See your database vendor documentation to determine whether you need to specify the length. For example:

To enable CLOB support in the GlassFish Server environment, define a CMP field of type java.lang.String. If you map the CMP bean to an existing database schema, map the field to a column of type CLOB.

For automatic mapping, you might need to change the default CLOB column length for the generated schema using the schema-generator-properties element in glassfish-ejb-jar.xml. See your database vendor documentation to determine whether you need to specify the length. For example:

The automatic schema generation feature provided in the GlassFish Server defines database tables based on the fields in entity beans and the relationships between the fields. This insulates developers from many of the database related aspects of development, allowing them to focus on entity bean development. The resulting schema is usable as-is or can be given to a database administrator for tuning with respect to performance, security, and so on.

Automatic schema generation is supported on an all-or-none basis: it expects that no tables exist in the database before it is executed. It is not intended to be used as a tool to generate extra tables or constraints.

Deployment won't fail if all tables are not created, and undeployment won't fail if not all tables are dropped. This is done to allow you to investigate the problem and fix it manually. You should not rely on the partially created database schema to be correct for running the application.

CMP supports a set of JDBC data types that are used in mapping Java data fields to SQL types. Supported JDBC data types are as follows: BIGINT, BIT, BLOB, CHAR, CLOB, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER, NUMERIC, REAL, SMALLINT, TIME, TIMESTAMP, TINYINT, VARCHAR.

Java types assigned to CMP fields must be restricted to Java primitive types, Java Serializable types, java.util.Date, java.sql.Date, java.sql.Time, or java.sql.Timestamp. An entity bean local interface type (or a collection of such) can be the type of a CMR field.

The following table contains the mappings of JDBC types to database vendor-specific types when automatic mapping is used. For a list of the JDBC drivers currently supported by the GlassFish Server, see the Oracle GlassFish Server Release Notes. For configurations of supported and other drivers, see "Configuration Specifics for JDBC Drivers" in Oracle GlassFish Server Administration Guide.

Automatic schema generation is not supported for beans with version column consistency checking. Instead, work with your database administrator to create the schema and add the required triggers. See Version Column Consistency Checking.

The following optional data subelements of the cmp-resource element in the glassfish-ejb-jar.xml file control the automatic creation of database tables at deployment. For more information about the cmp-resource element, see "cmp-resource" in Oracle GlassFish Server Application Deployment Guide and Configuring the CMP Resource.

Specifies the name of the database vendor for which tables are created. Allowed values are javadb, db2, mssql, mysql, oracle, postgresql, pointbase, derby (also for CloudScape), and sybase, case-insensitive.

If no value is specified, a connection is made to the resource specified by the jndi-name subelement of the cmp-resource element in the glassfish-ejb-jar.xml file, and the database vendor name is read. If the connection cannot be established, or if the value is not recognized, SQL-92 compliance is presumed.

3a8082e126