CreateMusicTables.sql

/*
 * Copyright 2019 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

START BATCH DDL;

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
  BirthDate  DATE
) PRIMARY KEY(SingerId);

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);

CREATE VIEW SingersView SQL SECURITY INVOKER AS
SELECT s.SingerId AS SingerId, s.FirstName AS FirstName, s.LastName AS LastName
FROM Singers s
ORDER BY s.LastName, s.FirstName;

CREATE TABLE Albums (
  SingerId        INT64 NOT NULL,
  AlbumId         INT64 NOT NULL,
  AlbumTitle      STRING(MAX),
  MarketingBudget INT64
) PRIMARY KEY(SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget);

CREATE TABLE Songs (
  SingerId  INT64 NOT NULL,
  AlbumId   INT64 NOT NULL,
  TrackId   INT64 NOT NULL,
  SongName  STRING(MAX),
  Duration  INT64,
  SongGenre STRING(25)
) PRIMARY KEY(SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE UNIQUE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC), INTERLEAVE IN Albums;

CREATE INDEX SongsBySongName ON Songs(SongName);

CREATE TABLE Concerts (
  VenueId      INT64 NOT NULL,
  SingerId     INT64 NOT NULL,
  ConcertDate  DATE NOT NULL,
  BeginTime    TIMESTAMP,
  EndTime      TIMESTAMP,
  TicketPrices ARRAY<INT64>,
  CONSTRAINT Fk_Concerts_Singer FOREIGN KEY (SingerId) REFERENCES Singers (SingerId)
) PRIMARY KEY(VenueId, SingerId, ConcertDate);

CREATE TABLE TableWithAllColumnTypes (
  ColInt64		INT64		NOT NULL,
  ColFloat64	FLOAT64		NOT NULL,
  ColBool		BOOL		NOT NULL,
  ColString		STRING(100) NOT NULL,
  ColStringMax	STRING(MAX)	NOT NULL,
  ColBytes		BYTES(100)	NOT NULL,
  ColBytesMax	BYTES(MAX)	NOT NULL,
  ColDate		DATE		NOT NULL,
  ColTimestamp	TIMESTAMP	NOT NULL,
  ColCommitTS	TIMESTAMP	NOT NULL OPTIONS (allow_commit_timestamp=true),
  ColNumeric	NUMERIC		NOT NULL,
  ColJson		JSON		NOT NULL,
  
  ColInt64Array		ARRAY<INT64>,
  ColFloat64Array	ARRAY<FLOAT64>,
  ColBoolArray		ARRAY<BOOL>,
  ColStringArray	ARRAY<STRING(100)>,
  ColStringMaxArray	ARRAY<STRING(MAX)>,
  ColBytesArray		ARRAY<BYTES(100)>,
  ColBytesMaxArray	ARRAY<BYTES(MAX)>,
  ColDateArray		ARRAY<DATE>,
  ColTimestampArray	ARRAY<TIMESTAMP>,
  ColNumericArray	ARRAY<NUMERIC>,
  ColJsonArray		ARRAY<JSON>,
  
  ColComputed STRING(MAX) AS (CONCAT(COALESCE(ColString, ''), ' ', COALESCE(ColStringMax, ''))) STORED,
) PRIMARY KEY (ColInt64)
;

CREATE TABLE all_nullable_types (
    ColInt64	    INT64,
    ColFloat64	    FLOAT64,
    ColBool		    BOOL,
    ColString		STRING(100),
    ColBytes		BYTES(100),
    ColDate		    DATE,
    ColTimestamp	TIMESTAMP,
    ColNumeric	    NUMERIC,
    ColJson		    JSON,
    
    ColInt64Array		ARRAY<INT64>,
    ColFloat64Array     ARRAY<FLOAT64>,
    ColBoolArray		ARRAY<BOOL>,
    ColStringArray	    ARRAY<STRING(100)>,
    ColBytesArray		ARRAY<BYTES(100)>,
    ColDateArray		ARRAY<DATE>,
    ColTimestampArray	ARRAY<TIMESTAMP>,
    ColNumericArray	    ARRAY<NUMERIC>,
    ColJsonArray		ARRAY<JSON>,
) PRIMARY KEY (ColInt64)
;

CREATE TABLE TableWithRef (
  Id          INT64       NOT NULL,
  RefFloat    FLOAT64     NOT NULL,
  RefString   STRING(100) NOT NULL,
  RefDate     DATE        NOT NULL,
  CONSTRAINT Fk_TableWithRef_TableWithAllColumnTypes
    FOREIGN KEY (RefFloat, RefString, RefDate)
    REFERENCES TableWithAllColumnTypes (ColFloat64, ColString, ColDate)
) PRIMARY KEY (Id)
;

RUN BATCH;