Was there any discussion before this PR in some SIG meeting or similar? Is there any issue associated with this PR?

Was there any discussion before this PR in some SIG meeting or similar? Is there any issue associated with this PR?

yes, this is the design approach discussed within DB semconv WG: https://docs.google.com/document/d/1zTi_Z4WBisytPnXTpwtnGOK-4Gv5oSTuMHvIQ8--5hg

It partially addresses #749, clarifies parts of #718, #714

if schema is provided as a part of table name (best practice), we have duplication

wdyt of just SELECT {db.collection.name} for the span name? I'm not sure we need db.collection.namespace in the span name, sort of like HTTP spans include the route but not the server name. you can always group span names further by db.collection.namespace if that's desired

where does the schema belong?

For SQL at least:

• db.collection.name will be whatever the user has in the query (may be partially or fully namespaced, or not namespaced at all)
• so the question is whether to capture schema in db.collection.namespace or not
  • it could be useful to capture in db.collection.namespace, since it gives you the "complete namespace" for the collection, which could be useful for grouping(?)
  • https://stackoverflow.com/questions/7942520/relationship-between-catalog-schema-user-and-database-instance refers to schema as a namespace, which makes me think that it may be more intuitive to include it in db.collection.namespace

As discussed in the last WG call, looked into what we can collect in .NET.

The DbConnection ADO interface has following relevant methods:

• DbConnection.DataSource: name of the database server
• DbConnection.Database: name of the database
• DbConnection.GetSchema(string): returns a DataTable which contains metadata info about the objects in the db including schema and collection. Here is a super useful article on this.

I ran following code snippet with different DBs and libraries:

static void DisplayConnectionData(DbConnection connection)
{
    Console.WriteLine($"DataSource: {connection.DataSource}, Database: {connection.Database}");
    Console.WriteLine("============================");

    DataTable table = connection.GetSchema("Tables");
    foreach (System.Data.DataRow row in table.Rows)
    {
        foreach (System.Data.DataColumn col in table.Columns)
        {
            Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
        }
        Console.WriteLine("============================");
    }
}

Above code queries the Tables schema collection (so it only returns infos about tables). Here is a list about other collections (e.g. Procedures could be used for stored procedures).

MS SQL Server with System.Data.SqlClient

Output:

DataSource: 127.0.0.1,50312, Database: master
============================
TABLE_CATALOG = master
TABLE_SCHEMA = dbo
TABLE_NAME = spt_fallback_db
TABLE_TYPE = BASE TABLE
============================
....similar info for all tables...

MySQL with MySqlConnector

Output:

DataSource: 127.0.0.1, Database: test
============================
TABLE_CATALOG = def
TABLE_SCHEMA = information_schema
TABLE_NAME = CHARACTER_SETS
TABLE_TYPE = SYSTEM VIEW
ENGINE =
VERSION = 10
ROW_FORMAT =
TABLE_ROWS = 0
AVG_ROW_LENGTH = 0
DATA_LENGTH = 0
MAX_DATA_LENGTH = 0
INDEX_LENGTH = 0
DATA_FREE = 0
AUTO_INCREMENT =
CREATE_TIME = 12.04.2024 14:18:11
UPDATE_TIME =
CHECK_TIME =
TABLE_COLLATION =
CHECKSUM =
CREATE_OPTIONS =
TABLE_COMMENT =
============================
....similar info for all tables...

PostgreSql with Npgsql

Output:

DataSource: tcp://127.0.0.1:54251, Database: postgres
============================
table_catalog = postgres
table_schema = public
table_name = foo
table_type = BASE TABLE
============================

So, overall it seems, at least in ADO.NET we can get the schema. Issue I see with this is that this seems to query the database - not only that, but connection.GetSchema("Tables") returns the schema info for all tables, which can be very high overhead. Plus it's always a point in time info - e.g. if a table is created after the instrumentation calls connection.GetSchema("Tables"), then connection.GetSchema("Tables") needs to be called again to get the info for that table.