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:
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:
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:
All I need to do is to switch to PostgreSQL admin GUI, create a new Script, Ctrl+V and execute the script.
Once executed, the table is created successfully in PostgreSQL.
It has the same structure as the table in ABAP.
The way to insert test data into the table and query data out of table is exactly the same as in HANA studio.
No comments:
Post a Comment