# Operations (sample payloads)

## Main operations

### Count rows

Counts the number of rows that meet a given set of conditions.
**Sample Input**

```json
{
    "host": "redshift-cluster-1.abc123xyz789.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!",
    "table": "customers",
    "conditions": [
        \{
            "field": "country",
            "operator": "equal to",
            "value": "USA"
        \},
        \{
            "field": "age",
            "operator": "greater than",
            "value": 18
        \}
    ]
}
```

**Sample Output**

```json
{
    "count": 1250
}
```

### Delete rows

Deletes rows in your database based on criteria of your choosing.
**Sample Input**

```json
{
    "host": "redshift-cluster-1.abc123xyz789.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!",
    "table": "customers",
    "conditions": [
        \{
            "field": "last_purchase_date",
            "operator": "less than",
            "value": "2022-01-01"
        \},
        \{
            "field": "status",
            "operator": "equal to",
            "value": "inactive"
        \}
    ]
}
```

**Sample Output**

```json
\{
    "count": 15,
    "deleted": true
\}
```

### Find rows

Find rows in your database based on criteria of your choosing.
**Sample Input**

```json
{
    "host": "redshift-cluster-1.abc123xyz.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "ticketdb",
    "schema": "public",
    "user": "admin",
    "password": "SecurePassword123!",
    "table": "listings",
    "fields": [
        "listid",
        "sellerid",
        "eventid",
        "numtickets",
        "totalprice"
    ],
    "conditions": [
        \{
            "field": "eventid",
            "operator": "equal to",
            "value": 1234
        \},
        \{
            "field": "numtickets",
            "operator": "greater than",
            "value": 2
        \}
    ],
    "limit": 10,
    "offset": 0
}
```

**Sample Output**

```json
{
    "rows": [
        \{
            "listid": 5001,
            "sellerid": 101,
            "eventid": 1234,
            "numtickets": 4,
            "totalprice": "240.00"
        \},
        \{
            "listid": 5002,
            "sellerid": 102,
            "eventid": 1234,
            "numtickets": 3,
            "totalprice": "180.00"
        \},
        \{
            "listid": 5003,
            "sellerid": 103,
            "eventid": 1234,
            "numtickets": 5,
            "totalprice": "300.00"
        \}
    ]
}
```

### Insert new rows

Insert one or more rows into your Redshift database.
**Sample Input**

```json
{
    "host": "redshift-cluster-1.abc123xyz789.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!",
    "table": "customers",
    "data": [
        \{
            "customer_id": 1001,
            "first_name": "John",
            "last_name": "Doe",
            "email": "john.doe@example.com",
            "phone": "+1-555-123-4567",
            "created_at": "2023-04-15T10:30:00Z"
        \},
        \{
            "customer_id": 1002,
            "first_name": "Jane",
            "last_name": "Smith",
            "email": "jane.smith@example.com",
            "phone": "+1-555-987-6543",
            "created_at": "2023-04-15T11:45:00Z"
        \}
    ]
}
```

**Sample Output**

```json
\{
    "count": 2,
    "inserted": true
\}
```

### Run SQL query

Execute raw specified SQL code on the chosen database
**Sample Input**

```json
\{
    "host": "redshift-cluster-1.abc123xyz789.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!",
    "sql": "SELECT * FROM public.users WHERE age > $1 AND city = $2",
    "sql_parameters": [
        30,
        "New York"
    ]
\}
```

**Sample Output**

```json
{
    "count": 3,
    "result": [
        \{
            "id": 1,
            "name": "John Doe",
            "age": 35,
            "city": "New York"
        \},
        \{
            "id": 2,
            "name": "Jane Smith",
            "age": 42,
            "city": "New York"
        \},
        \{
            "id": 3,
            "name": "Bob Johnson",
            "age": 38,
            "city": "New York"
        \}
    ]
}
```

### Update rows

Updates rows in your database based on criteria of your choosing.
**Sample Input**

```json
{
    "host": "redshift-cluster-1.abc123xyz.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!",
    "table": "customers",
    "data": \{
        "email": "johndoe@example.com",
        "phone": "+1-555-123-4567"
    \},
    "conditions": [
        \{
            "field": "customer_id",
            "operator": "equal to",
            "value": 1001
        \}
    ]
}
```

**Sample Output**

```json
\{
    "count": 1,
    "updated": true
\}
```

## DDL operations

### List table fields (DDL)

> **Warning:** Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping

**Sample Input**

```json
\{
    "host": "redshift-cluster-1.abcdefghijkl.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydatabase",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!"
\}
```

**Sample Output**

```json
{}
```

### List tables (DDL)

> **Warning:** Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping

**Sample Input**

```json
\{
    "host": "redshift-cluster-1.abc123xyz789.us-west-2.redshift.amazonaws.com",
    "port": 5439,
    "database": "mydb",
    "schema": "public",
    "user": "admin",
    "password": "MySecurePassword123!"
\}
```

**Sample Output**

```json
{}
```