Connections
sqlServer
Commentary
added in 0.10.0
Connects to a SQL Server database instance.
A new transaction will be executedd every 20 ms or 500 rows, whichever happens first. You can override row size/write timing with batchConfigs. 1
Automatic table creation
By default for convenience, any tables ShadowTraffic writes to will be automatically created. This makes it easier to iterate on your generators without flipping back and forth between ShadowTraffic and SQL Server.
ShadowTraffic does this by scanning the structure of your generators and creating a suitable DDL, then executing it on your behalf.
If ShadowTraffic doesn't create the table exactly as you'd want it, you can override each column using the sqlHint function modifier. 2
If the table already exists, but you'd like to clear out any existing data, set tablePolicy to dropAndCreate. 3
Manual table control
If you don't ShadowTraffic to control your tables, you can turn this behavior off by setting tablePolicy to manual in the connection map. It'll then be up to you to make sure your tables exist before trying to write to them. 4
Generating updates and deletes
By default, ShadowTraffic only issues INSERT statements to your tables. But sometimes, it might be useful to issue UPDATE and DELETE statements too.
You can control the statement type by setting the op key on your generator. op must be one of insert, update, or delete.
For the latter two, you must also include a where key on your generator, which contains a map of column name to value. You need this because when you issue an update or a delete, you need to tell SQL Server what rows you want to change. The where map is used to check equality and select rows that match. 5
Examples
Configuring the connection
Basic configuration for SQL Server. Optionally, set batchConfigs to control how frequently transactions are written. In this example, a transaction is executed whenever 500 milliseconds pass or 5000 rows are accumulated—whichever comes first.
{
"connections": {
"sql": {
"kind": "sqlServer",
"connectionConfigs": {
"host": "localhost",
"port": 1433,
"username": "sql-server-user",
"password": "sql-server-pw",
"db": "mydb"
},
"batchConfigs": {
"lingerMs": 500,
"batchRows": 5000
}
}
}
}
Automatic table creation
By default, table sandbox doesn't need to be defined. ShadowTraffic will automatically create it for you.
{
"generators": [
{
"table": "sandbox",
"row": {
"id": {
"_gen": "uuid"
},
"level": {
"_gen": "uniformDistribution",
"bounds": [
1,
10
]
},
"active": {
"_gen": "boolean"
}
}
}
],
"connections": {
"sql": {
"kind": "sqlServer",
"connectionConfigs": {
"host": "localhost",
"port": 1433,
"username": "sql-server-user",
"password": "sql-server-pw",
"db": "mydb"
}
}
}
}
In this case, sandbox will be created like so:
Column_name | Type | Computed | Length | Prec | Scale | Nullable | TrimTrailingBlanks | FixedLenNullInSource | Collation
---------------------------------------------------------------------------------------------------
id | nvarchar(max) | no | -1 | | | yes | (n/a) | (n/a) | SQL_Latin1_General_CP1_CI_AS
level | float | no | 8 | 53 | | yes | (n/a) | (n/a) | NULL
active | bit | no | 1 | | | yes | (n/a) | (n/a) | NULL
Automatic table truncation
Use dropAndCreate to automatically clear out any rows in an existing target table.
{
"kind": "sqlServer",
"tablePolicy": "dropAndCreate",
"connectionConfigs": {
"host": "localhost",
"port": 1433,
"username": "sql-server-user",
"password": "sql-server-pw",
"db": "mydb"
}
}
Overriding column types
Use the optional sqlHint function modifier on any generator to override how its column is defined.
{
"table": "sandbox",
"row": {
"id": {
"_gen": "uuid",
"pgHint": "TEXT NOT NULL"
},
"level": {
"_gen": "uniformDistribution",
"bounds": [
1,
10
],
"pgHint": "INTEGER"
},
"active": {
"_gen": "boolean"
}
}
}
Now, sandbox will like this:
Column_name | Type | Computed | Length | Prec | Scale | Nullable | TrimTrailingBlanks | FixedLenNullInSource | Collation
---------------------------------------------------------------------------------------------------
id | nvarchar(max) | no | -1 | | | no | (n/a) | (n/a) | SQL_Latin1_General_CP1_CI_AS
level | float | no | 8 | 53 | | yes | (n/a) | (n/a) | NULL
active | bit | no | 1 | | | yes | (n/a) | (n/a) | NULL
Manual table creation
Use manual to prevent ShadowTraffic from automatically creating tables for you. When you do this, your tables must already exist before ShadowTraffic tries to write to them.
{
"kind": "sqlServer",
"tablePolicy": "manual",
"connectionConfigs": {
"host": "localhost",
"port": 1433,
"username": "sql-server-user",
"password": "sql-server-pw",
"db": "mydb"
}
}
Upserts and deletes
Use op and where to issue upserts and deletes. It's up to you to make sure that you don't try to update or delete rows that don't exist.
This example uses fork and stateMachine to generate many simultaneous rows being inserted, updated, and deleted.
In the first state, insertIt, a random value of about 50 is generated. In the second state, updateIt, the value gets updated to one of 1, 5, or 10. Finally, in deleteIt, the entire row is deleted.
id is generated once as a fork key and used as the basis for equality to perform updates and deletes.
{
"table": "sandbox",
"fork": {
"key": {
"_gen": "uuid"
}
},
"row": {
"id": {
"_gen": "var",
"var": "forkKey"
}
},
"stateMachine": {
"_gen": "stateMachine",
"initial": "insertIt",
"transitions": {
"insertIt": "updateIt",
"updateIt": "deleteIt"
},
"states": {
"insertIt": {
"row": {
"value": {
"_gen": "normalDistribution",
"mean": 50,
"sd": 15
}
}
},
"updateIt": {
"op": "update",
"where": {
"id": {
"_gen": "var",
"var": "forkKey"
}
},
"row": {
"value": {
"_gen": "oneOf",
"choices": [
1,
5,
10
]
}
}
},
"deleteIt": {
"op": "delete",
"where": {
"id": {
"_gen": "var",
"var": "forkKey"
}
}
}
}
}
}
A random snapshot of the table might look like:
> select * from sandbox limit 10;
id | value
--------------------------------------+-------
07f217c6-1c82-e1cb-6dd8-fc614ff1a6e3 | 49
83d35207-ac3f-b8a7-fd7e-c82e1bdc3b05 | 10
184a0e26-237f-915b-4d97-5dc1808d91df | 37
Specification
Connection JSON schema
{
"type": "object",
"properties": {
"kind": {
"type": "string",
"const": "sqlServer"
},
"connectionConfigs": {
"type": "object",
"properties": {
"host": {
"type": "string"
},
"port": {
"type": "integer"
},
"db": {
"type": "string"
},
"username": {
"type": "string"
},
"password": {
"type": "string"
}
},
"required": [
"host",
"port",
"db"
]
},
"batchConfigs": {
"type": "object",
"properties": {
"lingerMs": {
"type": "integer",
"minimum": 0
},
"batchRows": {
"type": "integer",
"minimum": 1
}
}
},
"tablePolicy": {
"type": "string",
"enum": [
"manual",
"create",
"dropAndCreate"
]
}
},
"required": [
"connectionConfigs"
]
}
Generator JSON schema
{
"type": "object",
"properties": {
"connection": {
"type": "string"
},
"name": {
"type": "string"
},
"table": {
"type": "string"
},
"row": {
"type": "object"
},
"op": {
"type": "string",
"enum": [
"insert",
"update",
"delete"
]
},
"where": {
"type": "object"
},
"localConfigs": {
"type": "object",
"properties": {
"throttleMs": {
"oneOf": [
{
"type": "number",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
},
"maxEvents": {
"oneOf": [
{
"type": "integer",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
},
"kafkaKeyProtobufHint": {
"type": "object",
"properties": {
"schemaFile": {
"type": "string"
},
"message": {
"type": "string"
}
},
"required": [
"schemaFile",
"message"
]
},
"jsonSchemaHint": {
"type": "object"
},
"maxBytes": {
"type": "integer",
"minimum": 1
},
"discard": {
"type": "object",
"properties": {
"rate": {
"type": "number",
"minimum": 0,
"maximum": 1
}
},
"required": [
"rate"
]
},
"repeat": {
"type": "object",
"properties": {
"rate": {
"type": "number",
"minimum": 0,
"maximum": 1
},
"times": {
"oneOf": [
{
"type": "integer",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
}
},
"required": [
"rate",
"times"
]
},
"protobufSchemaHint": {
"type": "object",
"patternProperties": {
"^.*$": {
"type": "object",
"properties": {
"schemaFile": {
"type": "string"
},
"message": {
"type": "string"
}
},
"required": [
"schemaFile",
"message"
]
}
}
},
"maxHistoryEvents": {
"type": "integer",
"minimum": 0
},
"maxMs": {
"type": "integer",
"minimum": 0
},
"time": {
"type": "integer"
},
"events": {
"type": "object",
"properties": {
"exactly": {
"oneOf": [
{
"type": "integer",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
}
}
},
"delay": {
"type": "object",
"properties": {
"rate": {
"type": "number",
"minimum": 0,
"maximum": 1
},
"ms": {
"oneOf": [
{
"type": "integer",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
}
},
"required": [
"rate",
"ms"
]
},
"history": {
"type": "object",
"properties": {
"events": {
"type": "object",
"properties": {
"max": {
"type": "integer",
"minimum": 0
}
}
}
}
},
"avroSchemaHint": {
"type": "object"
},
"throttle": {
"type": "object",
"properties": {
"ms": {
"oneOf": [
{
"type": "number",
"minimum": 0
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
}
}
},
"throughput": {
"oneOf": [
{
"type": "integer",
"minimum": 1
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
},
"timeMultiplier": {
"oneOf": [
{
"type": "number"
},
{
"type": "object",
"properties": {
"_gen": {
"type": "string"
}
},
"required": [
"_gen"
]
}
]
},
"kafkaValueProtobufHint": {
"type": "object",
"properties": {
"schemaFile": {
"type": "string"
},
"message": {
"type": "string"
}
},
"required": [
"schemaFile",
"message"
]
}
}
}
},
"required": [
"table",
"row"
],
"allOf": [
{
"if": {
"properties": {
"op": {
"const": "update"
}
}
},
"then": {
"required": [
"where"
]
}
},
{
"if": {
"properties": {
"op": {
"const": "delete"
}
}
},
"then": {
"required": [
"where"
]
}
}
]
}