[FEATURE] extract primary keys from ALTER TABLE .. ADD CONSTRAINT.. . PRIMARY KEY (...);

[ndepomereu]

Hi,

When parsing a CREATE TABLE, we have built-in functions to retrieve the PRIMARY KEYS in JSQLParser.

But in the case where the CREATE TABLE does not contain the PRIMARY KEYS, which are added later with an ALTER TABLE:

CREATE TABLE public.staff (
    staff_id integer DEFAULT nextval('public.staff_staff_id_seq'::regclass) NOT NULL,
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    address_id smallint NOT NULL,
    email character varying(50),
    store_id smallint NOT NULL,
    active boolean DEFAULT true NOT NULL,
    username character varying(16) NOT NULL,
    password character varying(40),
    last_update timestamp without time zone DEFAULT now() NOT NULL,
    picture bytea
);

Done later in file:

ALTER TABLE ONLY public.staff
    ADD CONSTRAINT staff_pkey PRIMARY KEY (staff_id);

I can't figure out how to retrieve the PRIMARY KEYS without doing manual parsing of the ALTER TABLE.
Is there a way in JSQLParser?

I have tried this attached code, but it prints null values.
PrimaryKeyExtractorTest.java.txt

(I can't in my case just move the PRIMARY KEYS creation in the staff table, because I write a schema displayer that takes in new schemas texts that I know nothing about).

Thanks,
Nicolas

[manticore-projects]

Greetings.

Unfortunately I am not sure if I understand your concern correctly:

1. You have a CREATE TABLE statement without a primary key

2. Then you amend the table and add the primary key using an ALTER TABLE statement

3. now you want to get the information about the primary key, but without parsing the ALTER TABLE statement?!
   Or do you mean, you will parse the ALTER TABLE statement using JSQLParser but want explicit access to the PRIMARY KEY clause?

Truth is: both the CREATE TABLE and also the ALTER TABLE will provide only skeleton access to the statements and will loop through many of the column/index related information as a list of strings (simply because its impossible to parse all the RDBMS flavors).

In general, my very personal sentiment is: JSQLParser should be able to parse any Query and DML, while support for the various DDL is a nice to have. DDL would be much better understood by accessing the MetaData via JDBC and or the standardized Information Schemas.

[manticore-projects]

Please have a look at the RR Diagram: https://manticore-projects.com/JSQLParser/syntax_snapshot.html#alterexpression
You should be able to access an Alter Expression for the Primary Key.

And here is the API: https://manticore-projects.com/JSQLParser/javadoc_snapshot.html#alterexpression

[manticore-projects]

And please don't say that this implementation is bad -- we know that :-D but did not find the resources for a proper rewrite.
The DDL part of JSQLParser is pretty weak in my opinion and needs so major rework. (But then, who really parses DDLs.)