4 Replies Latest reply on May 29, 2014 10:44 AM by Steven Hawkins

    Parsing DDL query

    Suman m Newbie

      I need to get a reference to org.teiid.query.sql.lang.Command or org.teiid.query.sql.lang.Query object on a "create view" query

       

      But QueryParser or SQLParser is unable to parse the string and is throwing an exception

       

      Below is the sample code ..

       

      String query = "CREATE VIEW test ( c1 VARCHAR, c2 VARCHAR ) AS SELECT 'a' as c1, 'c' as c2;"

      Command command = QueryParser.getQueryParser().parseCommand(query);

       

      Exception:

      org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "CREATE [*]VIEW[*] vtest (" at line 1, column 8.

      Was expecting: "foreign" | "local" | "procedure" | "temporary" | "trigger" | "virtual"

        at org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:214)

        at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:164)

        at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:140)

        at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:112)

       

       

      In my case the view is already created so I cannot use the method parseDDL(MetadataFactory factory, Reader ddl)

      which anyhow does not return anything. Other methods also try to validate the query against the grammar of "create trigger" and fail.

       

      Does the API have support to parse "create view" statements.

        • 1. Re: Parsing DDL query
          Steven Hawkins Master

          > I need to get a reference to org.teiid.query.sql.lang.Command or org.teiid.query.sql.lang.Query object on a "create view" query

           

          Unfortunately that is not possible.  DDL statements are not represented by command objects.

           

          > In my case the view is already created so I cannot use the method parseDDL(MetadataFactory factory, Reader ddl)

           

          The MetadatFactory effectively stores the Schema object that is being modified as the DDL is parsed.  So it's not that something is explicitly returned, but modified as part of parsing.

          • 2. Re: Parsing DDL query
            Ramesh Reddy Master

            Suman,

             

            I can not seem to understand what you are trying to achieve, can you elaborate the usecase, may be I can suggest an alternative?

             

            Ramesh..

            • 3. Re: Parsing DDL query
              Suman m Newbie

              Hi Ramesh,

               

              Our use case is as follows.

               

              Some of our views contain velocity templates and we use a custom metadata repository to read these views

              and parse the velocity template.

               

              Here is a sample model that contains a view with velocity code.

               

              <model name="test_model" type="VIRTUAL">

                      <metadata type="VELOCITY_VIEW">

                      CREATE VIEW test (

                c1 VARCHAR,

                c2 VARCHAR

                          )

                          AS 

                #if ($db.count("select count(*) from sys.tables") > 0)

                             SELECT 'a' as c1, 'b' as c2;

                #else

                SELECT 'x' as c1, 'y' as c2;

                          #end   

                       </metadata>

              </model>

               

              Sample code inside the custom metadata repository

               

                String originalView = factory.getRawMetadata();

                String modifiedView = VelocityGenerator.generate(originalView,connection);

                factory.parse(new StringReader(modifiedView));

               

              Since the content of the view is dynamic in nature, we have a service that runs

              periodically and determines if the view definition has changed or not. If the view

              definition has changed then alter view statement is issued on that view.

               

              To run the alter view we need to extract the name of the view and the select statement

              from the create view string.

               

              To extract these values i thought of obtaining the Command reference on the create view string

              and use a visitor to extract the required values. So this was our use case.

               

              We do have an alternate way of doing the above task by parsing the string using regex. But initially we thought

              using a visitor on the command object would be a better of doing it.

              • 4. Re: Parsing DDL query
                Steven Hawkins Master

                So there are a couple of possibilities here:

                 

                1. Assuming that you're operating server side, use the memory form of the metadata - that is get a reference to the Schema (there are several ways to do that, although unless it's at the translator level they are not well supported) and get the Table reference from there to check the query string.

                2. You can go the route of using a MetadataFactory parsing routine.  To get the view definition just call MetadataFactory.getSchema().getTable("table name").getSelectTransformation()

                3. There has been some looking for the ability to lookup view/procedure definitions from the system metadata, but we don't currently expose it.  We probably should be exposing this information via a system admin table, which would require an enhancement.

                1 of 1 people found this helpful