Friday 26 May 2017

Replicate ABAP database table definition to PostgreSQL

Recently I am doing self-study on PostgreSQL which is a very popular object-relational and open source database.After I have played around with it a while I have great interest to replicate some of my CRM table to PostgreSQL and manipulate the table there for practice purpose.

The table in PostgreSQL could be created via command line or SQL script, the latter works almost exactly the same as what we do in HANA studio, which means a developer who has HANA studio development experience can pick up PostgreSQL very quickly.
Suppose I have to create a new table in PostgreSQL which has exactly the same fields as the table COMM_PRODUCT in CRM:

ABAP Development, PostgreSQL, ABAP Database

And this link has provided a list of all supported data types in PostgreSQL, so I can write a ABAP report to parse the structure of a given database table and generate the SQL script for table creation in PostgreSQL.

The source code of report:

REPORT z.

PARAMETERS: name TYPE crmt_object_name OBLIGATORY DEFAULT 'COMM_PRODUCT'.
TYPES:
  BEGIN OF ty_clipdata,
    data TYPE c LENGTH 100,
  END   OF ty_clipdata .
TYPES:
  tt_formatted TYPE STANDARD TABLE OF ty_clipdata .


DATA: lt_export   TYPE tt_formatted,
      lv_line     TYPE ty_clipdata-data,
      lt_keyfield TYPE crmt_object_name_tab,
      ls_line     TYPE ty_clipdata,
      lv_ret      TYPE int4,
      lt_table    TYPE dd03ptab.

DEFINE append_line.
  ls_line = VALUE #( data = &1 ).
  APPEND ls_line TO lt_export.
END-OF-DEFINITION.

CALL FUNCTION 'DDIF_TABL_GET'
  EXPORTING
    name      = name
  TABLES
    dd03p_tab = lt_table.

DATA(lv_text) = |CREATE TABLE public.{ name } (|.
append_line lv_text.

LOOP AT lt_table ASSIGNING FIELD-SYMBOL(<comp>).
  CHECK <comp>-fieldname <> '.INCLUDE'.
  IF <comp>-keyflag = 'X'.
    APPEND <comp>-fieldname TO lt_keyfield.
  ENDIF.
  CASE <comp>-inttype.
    WHEN 'C'.
      lv_text = |{ <comp>-fieldname } character varying( { <comp>-intlen / 2 }) | &
      | COLLATE pg_catalog."default" NOT NULL,|.
      append_line lv_text.
    WHEN 'P'.
      IF find( val = <comp>-domname sub = 'TSTMP' case = abap_true ) <> -1.
        lv_text = |"{ <comp>-fieldname }" timestamp(0) without time zone NOT NULL,|.
      ENDIF.
      append_line lv_text.
    WHEN 'X'.
      DATA(lv_new_length) = <comp>-intlen * 2.
      lv_text = |{ <comp>-fieldname } character varying( { lv_new_length }) | &
      | COLLATE pg_catalog."default" NOT NULL,|.
      append_line lv_text.
  ENDCASE.
ENDLOOP.
lv_text = concat_lines_of( table = lt_keyfield sep = ',' ).
lv_text = |CONSTRAINT { name }_pkey PRIMARY KEY({ lv_text })|.
append_line lv_text.
append_line ')'.
append_line 'WITH ('.
append_line 'OIDS = FALSE'.
append_line ')'.
append_line 'TABLESPACE pg_default;'.
lv_text = |ALTER TABLE public.{ name } OWNER to postgres;|.
append_line lv_text.
lv_text = |GRANT ALL ON TABLE public.{ name } TO postgres;|.
append_line lv_text.
lv_text = |COMMENT ON TABLE public.{ name } IS 'Copied from CRM';|.
append_line lv_text.

cl_gui_frontend_services=>clipboard_export(
  EXPORTING
      no_auth_check        = abap_true
      IMPORTING
        data                 = lt_export
      CHANGING
        rc                   = lv_ret
      EXCEPTIONS
        cntl_error           = 1
        error_no_gui         = 2
        not_supported_by_gui = 3
    ).

WRITE: / 'export to clipboard ok:', sy-subrc.

Then I execute this report, it will generate necessary script and export to my clipboard:

ABAP Development, PostgreSQL, ABAP Database

All I need to do is to switch to PostgreSQL admin GUI, create a new Script, Ctrl+V and execute the script.

ABAP Development, PostgreSQL, ABAP Database

Once executed, the table is created successfully in PostgreSQL.

ABAP Development, PostgreSQL, ABAP Database

It has the same structure as the table in ABAP.

ABAP Development, PostgreSQL, ABAP Database

The way to insert test data into the table and query data out of table is exactly the same as in HANA studio.

ABAP Development, PostgreSQL, ABAP Database
ABAP Development, PostgreSQL, ABAP Database

No comments:

Post a Comment