Add aws_codestar_notificaton_rule table

[gertvv]

I recently discovered this project and it is a life saver! I was missing the ability to query CodeStar notification rules (used e.g. to set up notifications of CodePipeline build status) so had a go at implementing this.

This is my first time working with Go so I don't expect this PR is ready to be merged and at this point could use some input - e.g. what tests to add, perhaps how to reshape some of the columns (e.g. targets), and what to do about the tags.

Integration test logs

I haven't run these yet but would be happy to if you think this PR is worth developing further

Example query results

Results

``` select * from aws_codestar_notification_rule; -[ RECORD 1 ]--------------------------------------------------------------------------- arn | arn:aws:codestar-notifications:eu-central-1:***:notificationrule/1725915dbd009519cd82707d18d8506e5f5d2926 name | some-pipeline-notification resource | arn:aws:codepipeline:eu-central-1:***:some-pipeline detail_type | FULL status | ENABLED event_types | [{"EventTypeId":"codepipeline-pipeline-pipeline-execution-succeeded","EventTypeName":"Pipeline execution: Succeeded","ResourceType":"Pipeline","ServiceName":"CodePipeline"},{"EventTypeId":"codepipeline-pipeline-stage-execution-canceled","EventTypeName":"Stage execution: Canceled","ResourceType":"Pipeline","ServiceName":"CodePipeline"},{"EventTypeId":"codepipeline-pipeline-action-execution-failed","EventTypeName":"Action execution: Failed","ResourceType":"Pipeline","ServiceName":"CodePipeline"},{"EventTypeId":"codepipeline-pipeline-manual-approval-needed","EventTypeName":"Manual approval: Needed","ResourceType":"Pipeline","ServiceName":"CodePipeline"}] targets | [{"TargetAddress":"arn:aws:sns:eu-central-1:***:my-topic","TargetStatus":"ACTIVE","TargetType":"SNS"}] created_by | *** created_timestamp | 2024-05-21T22:13:57+02:00 last_modified_timestamp | 2024-05-21T22:13:57+02:00 tags_src | {} partition | aws region | eu-central-1 account_id | *** sp_connection_name | aws sp_ctx | {"connection_name":"aws","steampipe":{"sdk_version":"5.10.0"}} _ctx | {"connection_name":"aws","steampipe":{"sdk_version":"5.10.0"}} select rule.name as rule_name, rule.resource, target->>'TargetAddress' as target, topic.title as topic from aws_codestar_notification_rule as rule cross join jsonb_array_elements(rule.targets) target left join aws_sns_topic as topic on target->>'TargetAddress' = topic.topic_arn; -[ RECORD 1 ]--------------------------------------------------------------------------- rule_name | some-pipeline-notification resource | arn:aws:codepipeline:eu-central-1:***:some-pipeline target | arn:aws:sns:eu-central-1:***:my-topic topic | my-topic ```

[gertvv]

Excellent, thanks for the comments! I have some travel coming up but will try to update the PR when I can.

(Update 15 June: travel concludes today, hope to get to this soon)

[gertvv]

I rebased the original commit to main and implemented the suggestions. I hope the choice of "Name" for title and "Arn" for akas makes sense.

[gertvv]

Thanks for the detailed comments!

[gertvv]

I'm also wondering because it is quite natural to want to do joins on the "targets" - is it possible for the targets to be exposed as a secondary table somehow? The JSON join works but is quite inconvenient.

I added another example query to the documentation that at least makes this more manageable.

[ParthaI]

I'm also wondering because it is quite natural to want to do joins on the "targets" - is it possible for the targets to be exposed as a secondary table somehow? The JSON join works but is quite inconvenient.

I added another example query to the documentation that at least makes this more manageable.

• According to our table development standards, we can create a separate table if the API supports pagination for retrieving the column value.
• In our case, if there is a specific API call to get the targets only and it supports pagination.
• Otherwise, we will leave them as they are.

The example query looks great to me. However, I have a few minor suggestions:

• Please include the example query in both PostgreSQL and SQLite formats, as we have for other tables.
• Please fix the indentation of the example queries. We can use the tool https://codebeautify.org/sqlformatter

Thank you!

[gertvv]

* According to our table development standards, we can create a separate table if the API supports pagination for retrieving the column value.

Although there is an endpoint for listing targets it doesn't quite do what we'd want here so I'll leave it as is.

The example query looks great to me. However, I have a few minor suggestions:

* Please include the example query in both PostgreSQL and SQLite formats, as we have for other tables.

I couldn't find instructions for building the plugin for SQLite. How do I do that?

[ParthaI]

Hmm, It is difficult to build the plugin to work with SQLite in our local.

Can we use online tools like pg-to-sqlite and RebaseData to format our existing query into SQLite format?

On another note, the changes look good to me. We can merge them into a feature branch in our plugin and then make the remaining changes in that branch.

Thanks!

[gertvv]

Awesome, thank you! :+1: