github.com/voedger/voedger@v0.0.0-20240520144910-273e84102129/design/0-inv/20220414-slack-wdocs/slack-sql-syntax.md (about)

     1  ### Types
     2  
     3  ```sql
     4  
     5  // ENUM: https://dev.mysql.com/doc/refman/8.0/en/enum.html
     6  ENUM reaction ('accept', 'reject', 'smile', 'sorrow')
     7  
     8  // FIELDSET: https://www.w3schools.com/tags/tag_fieldset.asp
     9  FIELDSET common_fields (
    10      author sys.auto.creator /* `auto` means that field value is set by system */
    11      created_at sys.auto.created_timestamp_ms
    12      updated_at sys.auto.updated_timestamp_ms
    13  )
    14  ```
    15  
    16  ### Tables
    17  
    18  ```sql
    19  
    20  /* WITH: https://cassandra.apache.org/doc/latest/cassandra/cql/ddl.html#create-table-statement */
    21  
    22  TABLE messages (
    23      
    24      /* common_fields */
    25  
    26      author sys.auto.creator /* `auto` means that field value is set by system */
    27      created_at sys.auto.created_timestamp_ms
    28      updated_at sys.auto.updated_timestamp_ms
    29  
    30      /* message fields */
    31  
    32      is_channel_message boolean
    33      text nvarchar(170)
    34      attachments [10]blob
    35  
    36      /* constraits */
    37  
    38      CONSTRAINT presence (
    39              is_channel_message                                          /* Visible at least in channel */
    40              OR (sys.parent.id != 0 AND sys.parent.sys.parent.id = 0)    /* Visible in thread  */
    41      )
    42  
    43  ) WITH parent = messages
    44  
    45  
    46  TABLE reactions (
    47      /* common_fields */
    48  
    49      author sys.auto.creator /* `auto` means that field value is set by system */
    50      created_at sys.auto.created_timestamp_ms
    51      updated_at sys.auto.updated_timestamp_ms
    52  
    53      /* table fields */
    54  
    55      kind reaction
    56      
    57      /* constraits */
    58  
    59      UNIQUE (parent, author, kind)
    60      
    61  ) WITH parent = messages
    62  
    63  /*  TODO  ??? sys.parent.sys.parent_id = 0 */
    64  /*  TODO  ??? [10]blob https://www.ibm.com/docs/en/db2-for-zos/11?topic=type-arrays-in-sql-statements */
    65  ```
    66  
    67  ### Views
    68  
    69  [Subqueries (SQL Server)](https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver15)
    70  
    71  ```sql
    72  MATERIALIZED VIEW channel_messages AS SELECT is_channel_message
    73     ,(SELECT kind, COUNT() FROM reactions GROUP BY kind) AS reactions
    74     ,(SELECT FIRST(10) author DISTINCT(author) FROM reactions) AS firstReactors
    75     ,(SELECT COUNT() FROM threads) AS replies
    76     ,(SELECT LAST(3) author DISTINCT(author) FROM messages) AS lastRepliers
    77  FROM messages 
    78  WHERE messages.is_channel_message = true
    79  ORDER BY messages.id
    80  
    81  
    82  MATERIALIZED VIEW thread_messages AS SELECT messages.id 
    83     ,(SELECT kind, COUNT() FROM reactions GROUP BY kind) AS reactions
    84     ,(SELECT FIRST(10) author DISTINCT(author) FROM reactions) AS firstReactors
    85  FROM messages
    86  WHERE messages.parent.id != 0
    87  ORDER BY messages.parent.id, messages.id
    88  ```
    89  
    90  ### Use View
    91  
    92  ```sql
    93  
    94  /*  Read TOP 10 records from channel_messages */
    95  
    96  SELECT TOP 10 
    97      ROWID, 
    98      is_channel_message,     /* by value, materialized */
    99      text                    /* by reference, not included into view */
   100  FROM channel_messages
   101  
   102  /*  Read TOP 10 records using FRAME */
   103  
   104  SELECT FRAME(10, TOP, 0)
   105      ROWID, 
   106      is_channel_message,     /* by value, materialized */
   107      text                    /* by reference, not included into view */
   108  FROM channel_messages
   109  
   110  
   111  SELECT PREV 10 TOP, NEXT 10 TOP,
   112      ROWID, 
   113      is_channel_message,     /* by value, materialized */
   114      text                    /* by reference, not included into view */
   115  FROM channel_messages
   116  
   117  /* Read frame (кадр) around known ROWID-value */
   118  
   119  SELECT FRAME(10, <ROWID-value>,  10)
   120      ROWID, 
   121      is_channel_message,     /* by value, materialized */
   122      text                    /* by reference, not included into view */
   123  FROM channel_messages
   124  
   125  /* Dereference author  */
   126  
   127  SELECT TOP 10 ROWID, is_channel_message, text, firstReactors.author.Name FROM channel_messages
   128  
   129  
   130  /* Top 10 messages and all reactors where exists a reactor with name John  */
   131  
   132  SELECT TOP 10 
   133      ROWID,
   134      is_channel_message,
   135      text, 
   136      SELECT author.name, author.created_at FROM firstReactors
   137  FROM channel_messages
   138  WHERE EXISTS SELECT FROM firstReactors WHERE author.Name = 'John'
   139  
   140  
   141  /* Top 10 messages and John reactor where exists a reactor with name John  */
   142  
   143  SELECT TOP 10 
   144      ROWID,
   145      is_channel_message,
   146      text, 
   147      SELECT author.name, author.created_at FROM firstReactors WHERE author.Name = 'John'
   148  FROM channel_messages
   149  WHERE EXISTS SELECT FROM firstReactors WHERE author.Name = 'John'
   150  
   151  /* smm  does not like: Top 10 messages and John reactor where exists a reactor with name John  */
   152  
   153  SELECT TOP 10 
   154      ROWID,
   155      is_channel_message,
   156      text, 
   157      SELECT author.name, author.created_at FROM firstReactors
   158  FROM channel_messages
   159  WHERE firstReactors.author.Name = 'John'
   160  
   161  /* Top 10 messages, only John reactions are shown*/
   162  
   163  SELECT TOP 10 
   164      ROWID,
   165      is_channel_message,
   166      text, 
   167      SELECT author.name, author.created_at FROM firstReactors WHERE author.Name = 'John'
   168  FROM channel_messages
   169  
   170  ```
   171  
   172  #### graphql
   173  
   174  ```graphql
   175  channel_messages{
   176      id
   177      is_channel_message
   178      text
   179      reactions{
   180          kind
   181          count
   182      }
   183      firstReactors{
   184          author(name: "John"){
   185              id
   186              name
   187              created_at
   188          }
   189      }
   190      replies
   191      lastRepliers{
   192          author{
   193              id
   194              name
   195              created_at
   196          }
   197      }
   198  }
   199  
   200  thread_messages{
   201      id
   202      is_channel_message
   203      text
   204      reactions{
   205          kind
   206          count
   207      }
   208      firstReactors{
   209          author{
   210              id
   211              name
   212              created_at
   213          }
   214      }
   215  }
   216  
   217  ```
   218  
   219  gojq -M .rows.[].firstReactors.[].author.Name
   220  ```json
   221  {
   222    "rows": [
   223     {
   224          "ROWID":"asjk:123",
   225          "is_channel_message": true,
   226          "text": "my message",
   227          "firstReactors": [
   228              {
   229                  "author": {
   230                      "Name": "John"
   231                  }
   232              },
   233              {
   234                  "author": {
   235                      "Name": "Jack"
   236                  }
   237              }
   238          ]
   239      }
   240    ]
   241  }
   242  ```
   243