wumpz
commented
8 years ago There are so many details that are not yet implemented within JSqlParser. Especially ddl scripts parsing are far from perfect. It would be better to file separate issues for each concrete problem.
Parsing the script bellow results in multiple errors. Please check.
-- PostgreSQL database dump
-- Dumped from database version 9.6beta1 -- Dumped by pg_dump version 9.6beta1
-- Started on 2016-10-07 13:49:18 BST
SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET row_security = off;
-- TOC entry 18 (class 2615 OID 86906)
-- Name: string10_stitch4; Type: SCHEMA; Schema: -; Owner: postgres
CREATE SCHEMA string10_stitch4;
ALTER SCHEMA string10_stitch4 OWNER TO postgres;
SET search_path = string10_stitch4, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
-- TOC entry 503 (class 1259 OID 86907)
-- Name: chemical_chemical; Type: TABLE; Schema: string10_stitch4; Owner: postgres
CREATE TABLE chemical_chemical ( chemical1 integer NOT NULL, chemical2 integer NOT NULL, similarity integer, experimental integer, database integer, textmining integer, combined_score integer );
ALTER TABLE chemical_chemical OWNER TO postgres;
-- TOC entry 3591 (class 0 OID 0) -- Dependencies: 503
-- Name: TABLE chemical_chemical; Type: COMMENT; Schema: string10_stitch4; Owner: postgres
COMMENT ON TABLE chemical_chemical IS 'Links between two small molecules from STITCH 4 based on the file chemical_chemical.links.detailed.v4.0.tsv';
-- TOC entry 504 (class 1259 OID 86910)
-- Name: chemical_protein; Type: TABLE; Schema: string10_stitch4; Owner: postgres
CREATE TABLE chemical_protein ( chemical integer NOT NULL, protein character varying NOT NULL, experimental integer, prediction integer, database integer, textmining integer, combined_score integer );
ALTER TABLE chemical_protein OWNER TO postgres;
-- TOC entry 3592 (class 0 OID 0) -- Dependencies: 504
-- Name: TABLE chemical_protein; Type: COMMENT; Schema: string10_stitch4; Owner: postgres
COMMENT ON TABLE chemical_protein IS 'Small molecule to protein links from STITCH 4 based on the file 9606.protein_chemical.links.detailed.v4.0.tsv';
-- TOC entry 505 (class 1259 OID 86916)
-- Name: chemicals; Type: TABLE; Schema: string10_stitch4; Owner: postgres
CREATE TABLE chemicals ( cid integer NOT NULL, name character varying, molecular_weight double precision, smiles character varying );
ALTER TABLE chemicals OWNER TO postgres;
-- TOC entry 3593 (class 0 OID 0) -- Dependencies: 505
-- Name: TABLE chemicals; Type: COMMENT; Schema: string10_stitch4; Owner: postgres
COMMENT ON TABLE chemicals IS 'CID id numbers to detailed chemical descriptions from STITCH 4 based on the file chemicals.v4.0.tsv';
-- TOC entry 3594 (class 0 OID 0) -- Dependencies: 505
-- Name: COLUMN chemicals.cid; Type: COMMENT; Schema: string10_stitch4; Owner: postgres
COMMENT ON COLUMN chemicals.cid IS 'PubChem Compound ID number';
-- TOC entry 3595 (class 0 OID 0) -- Dependencies: 505
-- Name: COLUMN chemicals.name; Type: COMMENT; Schema: string10_stitch4; Owner: postgres
COMMENT ON COLUMN chemicals.name IS 'MeSH name of the chemical compound';
-- TOC entry 3596 (class 0 OID 0) -- Dependencies: 505
-- Name: COLUMN chemicals.molecular_weight; Type: COMMENT; Schema: string10_stitch4; Owner: postgres
COMMENT ON COLUMN chemicals.molecular_weight IS 'The molecular weight of the compound';
-- TOC entry 3597 (class 0 OID 0) -- Dependencies: 505
-- Name: COLUMN chemicals.smiles; Type: COMMENT; Schema: string10_stitch4; Owner: postgres
COMMENT ON COLUMN chemicals.smiles IS 'SMILES string representation of the chemical structure';
-- TOC entry 506 (class 1259 OID 86922)
-- Name: gene_chemical; Type: MATERIALIZED VIEW; Schema: string10_stitch4; Owner: con-mo8
CREATE MATERIALIZED VIEW gene_chemical AS SELECT g.gene_id, c.cid, avg(pc.combined_score) AS score FROM ((((chemical_protein pc JOIN chemicals c ON ((pc.chemical = c.cid))) JOIN ref.protein p ON (((pc.protein)::text = (p.name)::text))) JOIN ref.transcript t USING (transcript_id)) JOIN ref.gene g USING (gene_id)) GROUP BY g.gene_id, c.cid ORDER BY g.gene_id, c.cid WITH NO DATA;
ALTER TABLE gene_chemical OWNER TO "con-mo8";
-- TOC entry 507 (class 1259 OID 86930)
-- Name: protein_protein; Type: TABLE; Schema: string10_stitch4; Owner: postgres
CREATE TABLE protein_protein ( protein1 character varying NOT NULL, protein2 character varying NOT NULL, neighborhood integer, fusion integer, coocurence integer, coexpression integer, experimental integer, database integer, textmining integer, combined_score integer );
ALTER TABLE protein_protein OWNER TO postgres;
-- TOC entry 3598 (class 0 OID 0) -- Dependencies: 507
-- Name: TABLE protein_protein; Type: COMMENT; Schema: string10_stitch4; Owner: postgres
COMMENT ON TABLE protein_protein IS 'Protein to protein network from STRING 10 based on the file 9606.protein.links.detailed.v10.txt';
-- TOC entry 508 (class 1259 OID 86936)
-- Name: gene_gene; Type: MATERIALIZED VIEW; Schema: string10_stitch4; Owner: con-mo8
CREATE MATERIALIZED VIEW gene_gene AS SELECT g1.gene_id AS gene1_id, g2.gene_id AS gene2_id, avg(pp.neighborhood) AS neighborhood, avg(pp.fusion) AS fusion, avg(pp.coocurence) AS coocurence, avg(pp.coexpression) AS coexpression, avg(pp.experimental) AS experimental, avg(pp.database) AS database, avg(pp.textmining) AS textmining, avg(pp.combined_score) AS combined_score FROM ((((((protein_protein pp JOIN ref.protein p1 ON (((pp.protein1)::text = (p1.name)::text))) JOIN ref.transcript t1 ON ((p1.transcript_id = t1.transcript_id))) JOIN ref.gene g1 ON ((t1.gene_id = g1.gene_id))) JOIN ref.protein p2 ON (((pp.protein2)::text = (p2.name)::text))) JOIN ref.transcript t2 ON ((p2.transcript_id = t2.transcript_id))) JOIN ref.gene g2 ON ((t2.gene_id = g2.gene_id))) GROUP BY g1.gene_id, g2.gene_id ORDER BY g1.gene_id, g2.gene_id WITH NO DATA;
ALTER TABLE gene_gene OWNER TO "con-mo8";
-- TOC entry 509 (class 1259 OID 86944)
-- Name: gene_degree; Type: MATERIALIZED VIEW; Schema: string10_stitch4; Owner: con-mo8
CREATE MATERIALIZED VIEW gene_degree AS SELECT gene_gene.gene1_id AS gene_id, count(*) AS degree FROM gene_gene GROUP BY gene_gene.gene1_id ORDER BY gene_gene.gene1_id WITH NO DATA;
ALTER TABLE gene_degree OWNER TO "con-mo8";
-- TOC entry 3429 (class 2606 OID 86949)
-- Name: chemical_chemical chemical_chemical_pk; Type: CONSTRAINT; Schema: string10_stitch4; Owner: postgres
ALTER TABLE ONLY chemical_chemical ADD CONSTRAINT chemical_chemical_pk PRIMARY KEY (chemical1, chemical2);
-- TOC entry 3432 (class 2606 OID 86951)
-- Name: chemical_protein chemical_protein_pk; Type: CONSTRAINT; Schema: string10_stitch4; Owner: postgres
ALTER TABLE ONLY chemical_protein ADD CONSTRAINT chemical_protein_pk PRIMARY KEY (chemical, protein);
-- TOC entry 3434 (class 2606 OID 86953)
-- Name: chemicals chemicals_cid_pk; Type: CONSTRAINT; Schema: string10_stitch4; Owner: postgres
ALTER TABLE ONLY chemicals ADD CONSTRAINT chemicals_cid_pk PRIMARY KEY (cid);
-- TOC entry 3440 (class 2606 OID 86955)
-- Name: protein_protein protein_protein_pk; Type: CONSTRAINT; Schema: string10_stitch4; Owner: postgres
ALTER TABLE ONLY protein_protein ADD CONSTRAINT protein_protein_pk PRIMARY KEY (protein1, protein2);
-- TOC entry 3435 (class 1259 OID 86958)
-- Name: chemicals_name_idx; Type: INDEX; Schema: string10_stitch4; Owner: postgres
CREATE INDEX chemicals_name_idx ON chemicals USING btree (name);
-- TOC entry 3437 (class 1259 OID 86959)
-- Name: gene_chemical_cid_idx; Type: INDEX; Schema: string10_stitch4; Owner: con-mo8
CREATE INDEX gene_chemical_cid_idx ON gene_chemical USING btree (cid);
-- TOC entry 3438 (class 1259 OID 86960)
-- Name: gene_chemical_gene_id_idx; Type: INDEX; Schema: string10_stitch4; Owner: con-mo8
CREATE INDEX gene_chemical_gene_id_idx ON gene_chemical USING btree (gene_id);
-- TOC entry 3443 (class 1259 OID 86961)
-- Name: gene_degree_gene_idx; Type: INDEX; Schema: string10_stitch4; Owner: con-mo8
CREATE INDEX gene_degree_gene_idx ON gene_degree USING btree (gene_id);
-- TOC entry 3441 (class 1259 OID 86962)
-- Name: gene_gene_gene1_id_idx; Type: INDEX; Schema: string10_stitch4; Owner: con-mo8
CREATE INDEX gene_gene_gene1_id_idx ON gene_gene USING btree (gene1_id);
-- TOC entry 3442 (class 1259 OID 86963)
-- Name: gene_gene_gene2_id_idx; Type: INDEX; Schema: string10_stitch4; Owner: con-mo8
CREATE INDEX gene_gene_gene2_id_idx ON gene_gene USING btree (gene2_id);
-- TOC entry 3430 (class 1259 OID 95103)
-- Name: idx_chemical_chemical; Type: INDEX; Schema: string10_stitch4; Owner: postgres
CREATE INDEX idx_chemical_chemical ON chemical_chemical USING btree (chemical1);
-- TOC entry 3436 (class 1259 OID 95111)
-- Name: idx_chemicals; Type: INDEX; Schema: string10_stitch4; Owner: postgres
CREATE INDEX idx_chemicals ON chemicals USING btree (cid);
-- Completed on 2016-10-07 13:49:18 BST
-- PostgreSQL database dump complete
Below are the errors on import I get within dbSchema, the tables appear with the correct columns but they have weird index definitions and all of the primary keys are missing.
SET statement_timeout = 0
Not implemented net.sf.jsqlparser.statement.SetStatement
SET lock_timeout = 0
Not implemented net.sf.jsqlparser.statement.SetStatement
SET client_encoding = 'UTF8'
Not implemented net.sf.jsqlparser.statement.SetStatement
SET standard_conforming_strings = on
Encountered " "ON" "on "" at line 1, column 35. Was expecting one of: "NULL" ... "CASE" ... "INTERVAL" ... "GROUP_CONCAT" ...