extras/cloudsql/populate-jobs/populate-ledger-db.yaml

# Copyright 2020 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.

# kubectl create configmap ledger-schema-config --from-file=src/ledger-db/initdb/0-ledger-schema.sql --dry-run -o yaml (copy and add below)
# kubectl create configmap ledger-schema-config --from-file=src/ledger-db/initdb/1-load-testdata.sh --dry-run -o yaml (copy and add below)
apiVersion: v1
kind: ConfigMap
metadata:
  name: ledger-schema-config
data:
  wait-to-complete-sidecar.sh: |
    #!/bin/bash
    COUNTER=0
    TARGET=$1
    PROCESS=$2

    echo "Looking for $TARGET"

    sleep 10s # Give 10s to allow processes to start

    # 20 minutes (60 x 10s increments)
    while [ $COUNTER -lt 120 ]; do
        let COUNTER=$COUNTER+1

        IS_RUNNING=$(ps -A | grep "scripts/$TARGET" | grep -v grep)

        if [ "$IS_RUNNING" != "" ]; then
          echo "Attempt # ${COUNTER}: Process not completed, trying again in 10 seconds -- ${IS_RUNNING}"
          sleep 10s
        else
          echo "'${TARGET}' Process Finished, Stopping '${PROCESS}'"
          killall ${PROCESS}
          exit 0
        fi
    done

    echo "Could not determine if the import finished, killing the proxy"
    killall ${PROCESS}
    exit 0

  initialize-database.sh: |
    #!/bin/bash

    COUNTER=0
    SLEEP_TIME=60 # 1 minute
    DB_READY=0 # false
    INIT_SQL_SCRIPT="/scripts/0-ledger-schema.sql"
    TEST_SQL_SCRIPT="/scripts/1-load-testdata.sh"

    HOST=${1:-'127.0.0.1'}
    PORT=${2:-'5432'}
    DB_NAME=${3:-'default'}

    # Initial wait for sql-proxy to catch up
    sleep 20

    while [ $COUNTER -lt 10 ]; do
        let COUNTER=$COUNTER+1

        pg_isready --host=${HOST} --port=${PORT} --dbname=${DB_NAME}

        if [ $? -gt 0 ]; then
            echo "Attempt # ${COUNTER}: Database is not ready, trying again in 1 minute"
            sleep $SLEEP_TIME
        else
            echo "Database is ready to connect"
            let DB_READY=1
            break
        fi
    done

    if [ "${DB_READY}" -eq 1 ]; then
        echo "Running initialization script"
        psql --host=${HOST} --port=${PORT} --dbname=${DB_NAME} -f ${INIT_SQL_SCRIPT}
        if [ $? -gt 0 ]; then
            echo "Problems running the initialization script"
        else
            echo "Run Test Data"
            . ${TEST_SQL_SCRIPT}
        fi
    fi
  0-ledger-schema.sql: |
    /*
     * Copyright 2020, 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.
     */

    CREATE TABLE TRANSACTIONS (
        TRANSACTION_ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
        FROM_ACCT CHAR(10) NOT NULL,
        TO_ACCT CHAR(10) NOT NULL,
        FROM_ROUTE CHAR(9) NOT NULL,
        TO_ROUTE CHAR(9) NOT NULL,
        AMOUNT INT NOT NULL,
        TIMESTAMP TIMESTAMP NOT NULL
    );
    -- index account number/routing number pairs
    CREATE INDEX ON TRANSACTIONS (FROM_ACCT, FROM_ROUTE, TIMESTAMP);
    CREATE INDEX ON TRANSACTIONS (TO_ACCT, TO_ROUTE, TIMESTAMP);
    -- append only ledger; prevent updates or deletes
    CREATE RULE PREVENT_UPDATE AS
      ON UPDATE TO TRANSACTIONS
      DO INSTEAD NOTHING;
    CREATE RULE PREVENT_DELETE AS
      ON DELETE TO TRANSACTIONS
      DO INSTEAD NOTHING;
  1-load-testdata.sh: |
    #!/bin/bash
    # Copyright 2020 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.


    # Create demo transactions in the ledger for the demo user accounts.
    #
    # Gerenated transactions follow a pattern of biweekly large deposits with
    # periodic small payments to randomly choosen accounts.
    #
    # To run, set environment variable USE_DEMO_DATA="True"

    set -u


    # skip adding transactions if not enabled
    if [ -z "$USE_DEMO_DATA" ] && [ "$USE_DEMO_DATA" != "True"  ]; then
        echo "\$USE_DEMO_DATA not \"True\"; no demo transactions added"
        exit 0
    fi


    # Expected environment variables
    readonly ENV_VARS=(
      "POSTGRES_DB"
      "POSTGRES_USER"
      "LOCAL_ROUTING_NUM"
    )


    add_transaction() {
        DATE=$(date -u +"%Y-%m-%d %H:%M:%S%z" --date="@$(($6))")
        echo "adding demo transaction: $1 -> $2"
        psql -X -v ON_ERROR_STOP=1 -v fromacct="$1" -v toacct="$2" -v fromroute="$3" -v toroute="$4" -v amount="$5" --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
            INSERT INTO TRANSACTIONS (FROM_ACCT, TO_ACCT, FROM_ROUTE, TO_ROUTE, AMOUNT, TIMESTAMP)
            VALUES (:'fromacct', :'toacct', :'fromroute', :'toroute', :'amount', '$DATE');
    EOSQL
    }


    create_transactions() {
        PAY_PERIODS=3
        DAYS_BETWEEN_PAY=14
        SECONDS_IN_PAY_PERIOD=$(( 86400 * $DAYS_BETWEEN_PAY  ))
        DEPOSIT_AMOUNT=250000

        # create a UNIX timestamp in seconds since the Epoch
        START_TIMESTAMP=$(( $(date +%s) - $(( $(($PAY_PERIODS+1)) * $SECONDS_IN_PAY_PERIOD  ))  ))

        for i in $(seq 1 $PAY_PERIODS); do
            # create deposit transaction for each user
            for account in ${USER_ACCOUNTS[@]}; do
                add_transaction "$EXTERNAL_ACCOUNT" "$account" "$EXTERNAL_ROUTING" "$LOCAL_ROUTING_NUM" $DEPOSIT_AMOUNT $START_TIMESTAMP
            done

            # create 15-20 payments between users
            TRANSACTIONS_PER_PERIOD=$(shuf -i 15-20 -n1)
            for p in $(seq 1 $TRANSACTIONS_PER_PERIOD); do
                # randomly generate an amount between $10-$100
                AMOUNT=$(shuf -i 1000-10000 -n1)

                # randomly select a sender and receiver
                SENDER_ACCOUNT=${USER_ACCOUNTS[$RANDOM % ${#USER_ACCOUNTS[@]}]}
                RECIPIENT_ACCOUNT=${USER_ACCOUNTS[$RANDOM % ${#USER_ACCOUNTS[@]}]}
                # if sender equals receiver, send to a random anonymous account
                if [[ "$SENDER_ACCOUNT" == "$RECIPIENT_ACCOUNT" ]]; then
                    RECIPIENT_ACCOUNT=$(shuf -i 1000000000-9999999999 -n1)
                fi

                TIMESTAMP=$(( $START_TIMESTAMP + $(( $SECONDS_IN_PAY_PERIOD * $p / $(($TRANSACTIONS_PER_PERIOD + 1 )) )) ))

                add_transaction "$SENDER_ACCOUNT" "$RECIPIENT_ACCOUNT" "$LOCAL_ROUTING_NUM" "$LOCAL_ROUTING_NUM" $AMOUNT $TIMESTAMP
            done

            START_TIMESTAMP=$(( $START_TIMESTAMP + $(( $i * $SECONDS_IN_PAY_PERIOD  )) ))
        done
    }


    create_ledger() {
      # Account numbers for users 'testuser', 'alice', 'bob', and 'eve'.
      USER_ACCOUNTS=("1011226111" "1033623433" "1055757655" "1077441377")
      # Numbers for external account 'External Bank'
      EXTERNAL_ACCOUNT="9099791699"
      EXTERNAL_ROUTING="808889588"

      create_transactions
    }


    main() {
      # Check environment variables are set
      for env_var in ${ENV_VARS[@]}; do
        if [[ -z "${env_var}" ]]; then
          echo "Error: environment variable '$env_var' not set. Aborting."
          exit 1
        fi
      done

      create_ledger
    }


    main

---
apiVersion: batch/v1
kind: Job
metadata:
  name: populate-ledger-db
spec:
  template:
    spec:
      shareProcessNamespace: true # Important to stop all processes
      serviceAccountName: boa-ksa
      containers:
      - name: sidecar-controller
        image: bash@sha256:acbac47fb9ea642a211c35013f50481bec20bd23db852c9c2634a4d153e631f1
        command: ['bash', '-c', '. /scripts/wait-to-complete-sidecar.sh "initialize-database.sh" "cloud_sql_proxy"']
        volumeMounts:
        - name: scripts
          mountPath: "/scripts"
          readOnly: true
        resources:
          limits:
            cpu: "200m"
            memory: "100Mi"
      - name: populate-ledger-db
        image: postgres:16-alpine@sha256:acf5271bbecd4b8733f4e93959a8d2b536a57aeee6cc4b6a71890aaf646425b8
        command: ['bash', '-c','. /scripts/initialize-database.sh 127.0.0.1 5432 ledger-db']
        volumeMounts:
        - name: scripts
          mountPath: "/scripts"
          readOnly: true
        env:
        - name: PGUSER
          valueFrom:
            secretKeyRef:
              name: cloud-sql-admin
              key: username
        - name: PGPASSWORD
          valueFrom:
            secretKeyRef:
              name: cloud-sql-admin
              key: password
        # /start: Required for testing data
        - name: LOCAL_ROUTING_NUM
          valueFrom:
            configMapKeyRef:
              name: environment-config
              key: LOCAL_ROUTING_NUM
        - name: USE_DEMO_DATA
          valueFrom:
            configMapKeyRef:
              name: demo-data-config
              key: USE_DEMO_DATA
        - name: POSTGRES_DB
          value: "ledger-db"
        - name: PGHOSTADDR
          value: "127.0.0.1"
        - name: POSTGRES_USER
          valueFrom:
            secretKeyRef:
              name: cloud-sql-admin
              key: username
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: cloud-sql-admin
              key: password
      # /end: Required for testing data
      # CloudSQL Proxy
      - name: cloudsql-proxy
        resources:
          limits:
            cpu: "200m"
            memory: "100Mi"
        image: gcr.io/cloudsql-docker/gce-proxy:1.33.12@sha256:89530a19852370b176e91cfef02a24646019fcbffc7a5332cf2c9423bd5e910f
        env:
        - name: CONNECTION_NAME
          valueFrom:
            secretKeyRef:
              name: cloud-sql-admin
              key: connectionName
        command: ["/cloud_sql_proxy",
                  "-instances=$(CONNECTION_NAME)=tcp:5432"]
        securityContext:
          runAsNonRoot: true
      volumes:
      - name: scripts
        configMap:
          name: ledger-schema-config
      restartPolicy: Never
  backoffLimit: 4