Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23545

Add Trigger-View capability

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      This would be somewhat comparable to Oracle Database Continuous Query Notification, SQL Server waitfor/receive, and Postgresql Listen / Notify; but better.

      Instead of https://jira.mariadb.org/browse/MDEV-16590, this feature would provide a simpler and more developer-friendly SQL based API to be notified quickly when new records become available.

      The experience may be like the following

      select * from StreamView_Sales_Inserted where SaleAmount > 10000;
      

      Where an external process could use a client driver like this

      public async Task ProcessEventData(SqlCommand com, int cutIn)
      {
      	//The following has already beed processed occurred:
          //$@"Select * from Sales where SaleAmount > 10000 and SaleDate >= '{cutIn.ToString("yyyy-MM-dd HH:mm:ss.fff")}' 
      	
          com.CommandText = 
      		"select * from StreamView_Sales_Inserted where ID > cutIn and SaleAmount > 10000";
      	com.CommandTimeout = 0;
          while (true)
          {
      		try
      		{
      			using (var reader = await com.ExecuteReaderAsync())
      			{
      				while (await reader.ReadAsync()) //Blocks if there are no rows
      				{
      					var sale = MaterializeSale(reader);
                                              cutIn = sale.ID;
      					await SendBigSaleEmail(sale);
      				}
      			}
      		}
      		catch (NetworkException nex)
      		{
      			//Some sort of disconnect
      		}
      		catch (TimeoutException tex)
      		{
      			//These shouldn't happen
      		}
      		catch (Exception Ex)
      		{
      			//Sort of unexpected
      			await Task.Delay(10000);
      		}
      	}
      }
      

      I am not familiar with the source code for MySQL.

      Perhaps implementation could effectively be like a long-poll or cursor. I'm not sure if the internal mechanisms would require a complete overhaul though. At the highest database driver level, the client library is waiting for another network packet with more data for that resultset, so there should be no significant resources used up, except potentially for the resultset context on the serverside.

      System-defined Views would be available per table, or may be defined using new keywords like `WITH Trigger(Sales, Insert)`.

      Such Views may be joined to other tables, but MUST BE the first FROM-SOURCE of the View, and the only Trigger-View.

      Learning from SQL Server, perhaps these should be Queue-Views, but simpler, where triggers are enqueuing to active Views at the time; not requiring a broker.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              merarischroeder Todd Hubers
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.