Uploaded image for project: 'CUBRID APIs'
  1. CUBRID APIs
  2. APIS-518

[ADO.NET]CUBRIDSchemaProvider .GetViews can't be got.

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: ADONET-Aprium, ADONET-9.1.0
    • Fix Version/s: ADO.NET 9.2.0.0001
    • Component/s: ADO.NET
    • Labels:

      Description

      In source file(CUBRIDSchemaProvider.cs) it use this sql to find view:
      select class_name from db_class where is_system_class='NO' and [class_type='VCLASS'] and class_name LIKE 'view';
      but
      1.in system tale:class_name, it use '1' to means that it's type is view not use 'VCLASS', So the view can't be found out. So when select view the sql should has ['class_type=1']
      2.system table has no table db_class but _db_class.
      3.is_system_class's datatype is integer.

        Activity

        Hide
        cn15800 谢韦华[Bert] added a comment - - edited

        I think this is not bug, db_class and _db_class are system table

        {code}
        csql> desc db_class;

        === <Result of SELECT Command in Line 7> ===

        Field Type Null Key Default Extra
        ====================================================================================================================================
        'class_name' 'VARCHAR(255)' 'YES' '' NULL ''
        'owner_name' 'VARCHAR(255)' 'YES' '' NULL ''
        'class_type' 'VARCHAR(6)' 'YES' '' NULL ''
        'is_system_class' 'VARCHAR(3)' 'YES' '' NULL ''
        'partitioned' 'VARCHAR(3)' 'YES' '' NULL ''
        'is_reuse_oid_class' 'VARCHAR(3)' 'YES' '' NULL '' {code} {code}
        desc _db_class;

        === <Result of SELECT Command in Line 1> ===

        Field Type Null Key Default Extra
        ====================================================================================================================================
        'class_of' 'OBJECT' 'YES' '' NULL ''
        'class_name' 'VARCHAR(255)' 'YES' 'MUL' NULL ''
        'class_type' 'INTEGER' 'YES' '' NULL ''
        'is_system_class' 'INTEGER' 'YES' '' NULL ''
        'owner' 'OBJECT' 'YES' '' NULL ''
        'inst_attr_count' 'INTEGER' 'YES' '' NULL ''
        'class_attr_count' 'INTEGER' 'YES' '' NULL ''
        'shared_attr_count' 'INTEGER' 'YES' '' NULL ''
        'inst_meth_count' 'INTEGER' 'YES' '' NULL ''
        'class_meth_count' 'INTEGER' 'YES' '' NULL ''
        'collation_id' 'INTEGER' 'YES' '' NULL ''
        'sub_classes' 'SEQUENCE OF OBJECT' 'YES' '' NULL ''
        'super_classes' 'SEQUENCE OF OBJECT' 'YES' '' NULL ''
        'inst_attrs' 'SEQUENCE OF OBJECT' 'YES' '' NULL ''
        'class_attrs' 'SEQUENCE OF OBJECT' 'YES' '' NULL ''
        'shared_attrs' 'SEQUENCE OF OBJECT' 'YES' '' NULL ''
        'inst_meths' 'SEQUENCE OF OBJECT' 'YES' '' NULL ''
        'class_meths' 'SEQUENCE OF OBJECT' 'YES' '' NULL ''
        'meth_files' 'SEQUENCE OF OBJECT' 'YES' '' NULL ''
        'query_specs' 'SEQUENCE OF OBJECT' 'YES' '' NULL ''
        'indexes' 'SEQUENCE OF OBJECT' 'YES' '' NULL ''

        21 rows selected. (0.014143 sec) Committed.{code}
        Show
        cn15800 谢韦华[Bert] added a comment - - edited I think this is not bug, db_class and _db_class are system table {code} csql> desc db_class; === <Result of SELECT Command in Line 7> === Field Type Null Key Default Extra ==================================================================================================================================== 'class_name' 'VARCHAR(255)' 'YES' '' NULL '' 'owner_name' 'VARCHAR(255)' 'YES' '' NULL '' 'class_type' 'VARCHAR(6)' 'YES' '' NULL '' 'is_system_class' 'VARCHAR(3)' 'YES' '' NULL '' 'partitioned' 'VARCHAR(3)' 'YES' '' NULL '' 'is_reuse_oid_class' 'VARCHAR(3)' 'YES' '' NULL '' {code} {code} desc _db_class; === <Result of SELECT Command in Line 1> === Field Type Null Key Default Extra ==================================================================================================================================== 'class_of' 'OBJECT' 'YES' '' NULL '' 'class_name' 'VARCHAR(255)' 'YES' 'MUL' NULL '' 'class_type' 'INTEGER' 'YES' '' NULL '' 'is_system_class' 'INTEGER' 'YES' '' NULL '' 'owner' 'OBJECT' 'YES' '' NULL '' 'inst_attr_count' 'INTEGER' 'YES' '' NULL '' 'class_attr_count' 'INTEGER' 'YES' '' NULL '' 'shared_attr_count' 'INTEGER' 'YES' '' NULL '' 'inst_meth_count' 'INTEGER' 'YES' '' NULL '' 'class_meth_count' 'INTEGER' 'YES' '' NULL '' 'collation_id' 'INTEGER' 'YES' '' NULL '' 'sub_classes' 'SEQUENCE OF OBJECT' 'YES' '' NULL '' 'super_classes' 'SEQUENCE OF OBJECT' 'YES' '' NULL '' 'inst_attrs' 'SEQUENCE OF OBJECT' 'YES' '' NULL '' 'class_attrs' 'SEQUENCE OF OBJECT' 'YES' '' NULL '' 'shared_attrs' 'SEQUENCE OF OBJECT' 'YES' '' NULL '' 'inst_meths' 'SEQUENCE OF OBJECT' 'YES' '' NULL '' 'class_meths' 'SEQUENCE OF OBJECT' 'YES' '' NULL '' 'meth_files' 'SEQUENCE OF OBJECT' 'YES' '' NULL '' 'query_specs' 'SEQUENCE OF OBJECT' 'YES' '' NULL '' 'indexes' 'SEQUENCE OF OBJECT' 'YES' '' NULL '' 21 rows selected. (0.014143 sec) Committed.{code}
        Hide
        cn15800 谢韦华[Bert] added a comment -

        The function has not problem.

        Show
        cn15800 谢韦华[Bert] added a comment - The function has not problem.
        Hide
        cn15800 谢韦华[Bert] added a comment -

        test code

        {code}
        public void apis_518()
        { CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn); DataTable dt = schema.GetViews(null); DisplayData(dt); }{code}
        Show
        cn15800 谢韦华[Bert] added a comment - test code {code} public void apis_518() { CUBRIDSchemaProvider schema = new CUBRIDSchemaProvider(conn); DataTable dt = schema.GetViews(null); DisplayData(dt); }{code}
        Hide
        cn15800 谢韦华[Bert] added a comment -

        The sql is ok, but there is other problem about the function.

        {code}
        private void FindViews(DataTable schemaTable, string[] filters)
        {
        .........................
        row["TABLE_CATALOG"] = conn.Database; // should be VIEW_CATALOG
        row["TABLE_SCHEMA"] = conn.Database; // should be VIEW_SCHEMA
        row["TABLE_NAME"] = reader.GetString(0); // should be VIEW_NAME
        .........................
        }{code}
        Show
        cn15800 谢韦华[Bert] added a comment - The sql is ok, but there is other problem about the function. {code} private void FindViews(DataTable schemaTable, string[] filters) { ......................... row ["TABLE_CATALOG"] = conn.Database; // should be VIEW_CATALOG row ["TABLE_SCHEMA"] = conn.Database; // should be VIEW_SCHEMA row ["TABLE_NAME"] = reader.GetString(0); // should be VIEW_NAME ......................... }{code}
        Hide
        ryin005 Ray Yin added a comment -

        Case 1:

        CUBRIDConnection connection = new CUBRIDConnection(); // TODO: Initialize to an appropriate value
        connection.ConnectionString = DBHelper.connString;
        connection.Open();
        DBHelper.ExecuteSQL("drop view if exists test_view", connection);
        DBHelper.ExecuteSQL("create view test_view as select * from code;", connection);
        CUBRIDSchemaProvider target = new CUBRIDSchemaProvider(connection); // TODO: Initialize to an appropriate value
        string[] filters = null; // TODO: Initialize to an appropriate value
         DataTable actual;
        actual = target.GetViews(filters);
        Assert.AreEqual(1, actual.Rows.Count);
        

        Test Result: Pass

        Show
        ryin005 Ray Yin added a comment - Case 1: CUBRIDConnection connection = new CUBRIDConnection(); // TODO: Initialize to an appropriate value connection.ConnectionString = DBHelper.connString; connection.Open(); DBHelper.ExecuteSQL("drop view if exists test_view", connection); DBHelper.ExecuteSQL("create view test_view as select * from code;", connection); CUBRIDSchemaProvider target = new CUBRIDSchemaProvider(connection); // TODO: Initialize to an appropriate value string[] filters = null; // TODO: Initialize to an appropriate value DataTable actual; actual = target.GetViews(filters); Assert.AreEqual(1, actual.Rows.Count); Test Result: Pass
        Hide
        ryin005 Ray Yin added a comment - - edited

        However, the below code is not updated yet

        private void FindViews(DataTable schemaTable, string[] filters)
        {
        .........................
        row["TABLE_CATALOG"] = conn.Database;  // should be VIEW_CATALOG
        row["TABLE_SCHEMA"] = conn.Database;   // should be VIEW_SCHEMA
        row["TABLE_NAME"] = reader.GetString(0);  // should be VIEW_NAME
        .........................
        }
        

        Reopen the issue

        Show
        ryin005 Ray Yin added a comment - - edited However, the below code is not updated yet private void FindViews(DataTable schemaTable, string[] filters) { ......................... row["TABLE_CATALOG"] = conn.Database; // should be VIEW_CATALOG row["TABLE_SCHEMA"] = conn.Database; // should be VIEW_SCHEMA row["TABLE_NAME"] = reader.GetString(0); // should be VIEW_NAME ......................... } Reopen the issue
        Hide
        cn15800 谢韦华[Bert] added a comment - - edited

        The property have different meaning in different database, so there is not problem in this function in my opinion.

        mysql:

        CATALOG : not support
        SCHEMA : database name

        Oracle:

        CATALOG : not support
        SCHEMA : User id

        Sybase:

        CATALOG : database name
        SCHEMA : database name

        Show
        cn15800 谢韦华[Bert] added a comment - - edited The property have different meaning in different database, so there is not problem in this function in my opinion. mysql: CATALOG : not support SCHEMA : database name Oracle: CATALOG : not support SCHEMA : User id Sybase: CATALOG : database name SCHEMA : database name

          People

          • Assignee:
            cn15800 谢韦华[Bert]
            Reporter:
            cn14633 董志玲(Ella) (Inactive)
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: