René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"[FEATURE] AWS Cloud Control Simplification","articleBody":"**Feature Description**\r\nLooking to combine singular resources (like `aws.s3.bucket`) and plural resources (like `aws.s3.buckets`) into one resource `buckets` with overloaded methods  \r\n\r\n__requires__:\r\n- overloaded views (reusable for other providers)\r\n- `select from where in ...` subquery (reusable for other providers)\r\n\r\n__advantages__:\r\n- much better user experience (one resource instead of two)\r\n- document rationalization/simplification\r\n\r\n**Possible Approaches or Libraries to Consider**\r\n\r\ncurrent state:\r\n\r\n```yaml\r\n    buckets:\r\n      name: buckets\r\n      id: aws.s3.buckets\r\n      x-cfn-schema-name: Bucket\r\n      x-cfn-type-name: AWS::S3::Bucket\r\n      x-identifiers:\r\n        - BucketName\r\n      x-type: list\r\n      methods:\r\n        create_resource:\r\n          config:\r\n            requestBodyTranslate:\r\n              algorithm: naive_DesiredState\r\n          operation:\r\n            $ref: '#/paths/~1?Action=CreateResource\u0026Version=2021-09-30\u0026__Bucket\u0026__detailTransformed=true/post'\r\n          request:\r\n            mediaType: application/x-amz-json-1.0\r\n            base: |-\r\n              {\r\n                \"TypeName\": \"AWS::S3::Bucket\"\r\n              }\r\n          response:\r\n            mediaType: application/json\r\n            openAPIDocKey: '200'\r\n        delete_resource:\r\n          operation:\r\n            $ref: '#/paths/~1?Action=DeleteResource\u0026Version=2021-09-30/post'\r\n          request:\r\n            mediaType: application/x-amz-json-1.0\r\n            base: |-\r\n              {\r\n                \"TypeName\": \"AWS::S3::Bucket\"\r\n              }\r\n          response:\r\n            mediaType: application/json\r\n            openAPIDocKey: '200'\r\n      sqlVerbs:\r\n        insert:\r\n          - $ref: '#/components/x-stackQL-resources/buckets/methods/create_resource'\r\n        delete:\r\n          - $ref: '#/components/x-stackQL-resources/buckets/methods/delete_resource'\r\n      config:\r\n        views:\r\n          select:\r\n            predicate: sqlDialect == \"sqlite3\"\r\n            ddl: |-\r\n              SELECT\r\n              region,\r\n              JSON_EXTRACT(Properties, '$.BucketName') as bucket_name\r\n              FROM aws.cloud_control.resources WHERE data__TypeName = 'AWS::S3::Bucket'\r\n              AND region = 'us-east-1'\r\n            fallback:\r\n              predicate: sqlDialect == \"postgres\"\r\n              ddl: |-\r\n                SELECT\r\n                region,\r\n                json_extract_path_text(Properties, 'BucketName') as bucket_name\r\n                FROM aws.cloud_control.resources WHERE data__TypeName = 'AWS::S3::Bucket'\r\n                AND region = 'us-east-1'\r\n    bucket:\r\n      name: bucket\r\n      id: aws.s3.bucket\r\n      x-cfn-schema-name: Bucket\r\n      x-cfn-type-name: AWS::S3::Bucket\r\n      x-identifiers:\r\n        - BucketName\r\n      x-type: get\r\n      methods:\r\n        update_resource:\r\n          operation:\r\n            $ref: '#/paths/~1?Action=UpdateResource\u0026Version=2021-09-30/post'\r\n          request:\r\n            mediaType: application/x-amz-json-1.0\r\n            base: |-\r\n              {\r\n                \"TypeName\": \"AWS::S3::Bucket\"\r\n              }\r\n          response:\r\n            mediaType: application/json\r\n            openAPIDocKey: '200'\r\n      sqlVerbs:\r\n        update:\r\n          - $ref: '#/components/x-stackQL-resources/bucket/methods/update_resource'\r\n      config:\r\n        views:\r\n          select:\r\n            predicate: sqlDialect == \"sqlite3\"\r\n            ddl: |-\r\n              SELECT\r\n              region,\r\n              data__Identifier,\r\n              JSON_EXTRACT(Properties, '$.AccelerateConfiguration') as accelerate_configuration,\r\n              JSON_EXTRACT(Properties, '$.AccessControl') as access_control,\r\n              JSON_EXTRACT(Properties, '$.AnalyticsConfigurations') as analytics_configurations,\r\n              JSON_EXTRACT(Properties, '$.BucketEncryption') as bucket_encryption,\r\n              JSON_EXTRACT(Properties, '$.BucketName') as bucket_name,\r\n              JSON_EXTRACT(Properties, '$.CorsConfiguration') as cors_configuration,\r\n              JSON_EXTRACT(Properties, '$.IntelligentTieringConfigurations') as intelligent_tiering_configurations,\r\n              JSON_EXTRACT(Properties, '$.InventoryConfigurations') as inventory_configurations,\r\n              JSON_EXTRACT(Properties, '$.LifecycleConfiguration') as lifecycle_configuration,\r\n              JSON_EXTRACT(Properties, '$.LoggingConfiguration') as logging_configuration,\r\n              JSON_EXTRACT(Properties, '$.MetricsConfigurations') as metrics_configurations,\r\n              JSON_EXTRACT(Properties, '$.NotificationConfiguration') as notification_configuration,\r\n              JSON_EXTRACT(Properties, '$.ObjectLockConfiguration') as object_lock_configuration,\r\n              JSON_EXTRACT(Properties, '$.ObjectLockEnabled') as object_lock_enabled,\r\n              JSON_EXTRACT(Properties, '$.OwnershipControls') as ownership_controls,\r\n              JSON_EXTRACT(Properties, '$.PublicAccessBlockConfiguration') as public_access_block_configuration,\r\n              JSON_EXTRACT(Properties, '$.ReplicationConfiguration') as replication_configuration,\r\n              JSON_EXTRACT(Properties, '$.Tags') as tags,\r\n              JSON_EXTRACT(Properties, '$.VersioningConfiguration') as versioning_configuration,\r\n              JSON_EXTRACT(Properties, '$.WebsiteConfiguration') as website_configuration,\r\n              JSON_EXTRACT(Properties, '$.Arn') as arn,\r\n              JSON_EXTRACT(Properties, '$.DomainName') as domain_name,\r\n              JSON_EXTRACT(Properties, '$.DualStackDomainName') as dual_stack_domain_name,\r\n              JSON_EXTRACT(Properties, '$.RegionalDomainName') as regional_domain_name,\r\n              JSON_EXTRACT(Properties, '$.WebsiteURL') as website_url\r\n              FROM aws.cloud_control.resource WHERE data__TypeName = 'AWS::S3::Bucket'\r\n              AND data__Identifier = '\u003cBucketName\u003e'\r\n              AND region = 'us-east-1'\r\n            fallback:\r\n              predicate: sqlDialect == \"postgres\"\r\n              ddl: |-\r\n                SELECT\r\n                region,\r\n                data__Identifier,\r\n                json_extract_path_text(Properties, 'AccelerateConfiguration') as accelerate_configuration,\r\n                json_extract_path_text(Properties, 'AccessControl') as access_control,\r\n                json_extract_path_text(Properties, 'AnalyticsConfigurations') as analytics_configurations,\r\n                json_extract_path_text(Properties, 'BucketEncryption') as bucket_encryption,\r\n                json_extract_path_text(Properties, 'BucketName') as bucket_name,\r\n                json_extract_path_text(Properties, 'CorsConfiguration') as cors_configuration,\r\n                json_extract_path_text(Properties, 'IntelligentTieringConfigurations') as intelligent_tiering_configurations,\r\n                json_extract_path_text(Properties, 'InventoryConfigurations') as inventory_configurations,\r\n                json_extract_path_text(Properties, 'LifecycleConfiguration') as lifecycle_configuration,\r\n                json_extract_path_text(Properties, 'LoggingConfiguration') as logging_configuration,\r\n                json_extract_path_text(Properties, 'MetricsConfigurations') as metrics_configurations,\r\n                json_extract_path_text(Properties, 'NotificationConfiguration') as notification_configuration,\r\n                json_extract_path_text(Properties, 'ObjectLockConfiguration') as object_lock_configuration,\r\n                json_extract_path_text(Properties, 'ObjectLockEnabled') as object_lock_enabled,\r\n                json_extract_path_text(Properties, 'OwnershipControls') as ownership_controls,\r\n                json_extract_path_text(Properties, 'PublicAccessBlockConfiguration') as public_access_block_configuration,\r\n                json_extract_path_text(Properties, 'ReplicationConfiguration') as replication_configuration,\r\n                json_extract_path_text(Properties, 'Tags') as tags,\r\n                json_extract_path_text(Properties, 'VersioningConfiguration') as versioning_configuration,\r\n                json_extract_path_text(Properties, 'WebsiteConfiguration') as website_configuration,\r\n                json_extract_path_text(Properties, 'Arn') as arn,\r\n                json_extract_path_text(Properties, 'DomainName') as domain_name,\r\n                json_extract_path_text(Properties, 'DualStackDomainName') as dual_stack_domain_name,\r\n                json_extract_path_text(Properties, 'RegionalDomainName') as regional_domain_name,\r\n                json_extract_path_text(Properties, 'WebsiteURL') as website_url\r\n                FROM aws.cloud_control.resource WHERE data__TypeName = 'AWS::S3::Bucket'\r\n                AND data__Identifier = '\u003cBucketName\u003e'\r\n                AND region = 'us-east-1'\r\n```\r\n__desired state:__\r\n\r\n```yaml\r\n    buckets:\r\n      name: buckets\r\n      id: aws.s3.buckets\r\n      x-cfn-schema-name: Bucket\r\n      x-cfn-type-name: AWS::S3::Bucket\r\n      x-identifiers:\r\n        - BucketName\r\n      methods:\r\n        create_resource:\r\n          config:\r\n            requestBodyTranslate:\r\n              algorithm: naive_DesiredState\r\n          operation:\r\n            $ref: '#/paths/~1?Action=CreateResource\u0026Version=2021-09-30\u0026__Bucket\u0026__detailTransformed=true/post'\r\n          request:\r\n            mediaType: application/x-amz-json-1.0\r\n            base: |-\r\n              {\r\n                \"TypeName\": \"AWS::S3::Bucket\"\r\n              }\r\n          response:\r\n            mediaType: application/json\r\n            openAPIDocKey: '200'\r\n        update_resource:\r\n          operation:\r\n            $ref: '#/paths/~1?Action=UpdateResource\u0026Version=2021-09-30/post'\r\n          request:\r\n            mediaType: application/x-amz-json-1.0\r\n            base: |-\r\n              {\r\n                \"TypeName\": \"AWS::S3::Bucket\"\r\n              }\r\n          response:\r\n            mediaType: application/json\r\n            openAPIDocKey: '200'            \r\n        delete_resource:\r\n          operation:\r\n            $ref: '#/paths/~1?Action=DeleteResource\u0026Version=2021-09-30/post'\r\n          request:\r\n            mediaType: application/x-amz-json-1.0\r\n            base: |-\r\n              {\r\n                \"TypeName\": \"AWS::S3::Bucket\"\r\n              }\r\n          response:\r\n            mediaType: application/json\r\n            openAPIDocKey: '200'\r\n      sqlVerbs:\r\n        insert:\r\n          - $ref: '#/components/x-stackQL-resources/buckets/methods/create_resource'\r\n        update:\r\n          - $ref: '#/components/x-stackQL-resources/buckets/methods/update_resource'\r\n        delete:\r\n          - $ref: '#/components/x-stackQL-resources/buckets/methods/delete_resource'\r\n        views:\r\n          select:\r\n            - method_name: list_buckets\r\n              params:\r\n                - region\r\n              predicate: sqlDialect == \"sqlite3\"\r\n              ddl: |-\r\n                SELECT\r\n                region,\r\n                JSON_EXTRACT(Properties, '$.BucketName') as bucket_name\r\n                FROM aws.cloud_control.resources WHERE data__TypeName = 'AWS::S3::Bucket'\r\n                AND region = 'us-east-1'\r\n              fallback:\r\n                predicate: sqlDialect == \"postgres\"\r\n                ddl: |-\r\n                  SELECT\r\n                  region,\r\n                  json_extract_path_text(Properties, 'BucketName') as bucket_name\r\n                  FROM aws.cloud_control.resources WHERE data__TypeName = 'AWS::S3::Bucket'\r\n                  AND region = 'us-east-1'\r\n            - method_name: get_bucket\r\n              params:\r\n                - region\r\n                - data__Identifier\r\n              predicate: sqlDialect == \"sqlite3\"\r\n              ddl: |-\r\n                  SELECT\r\n                  region,\r\n                  data__Identifier,\r\n                  JSON_EXTRACT(Properties, '$.AccelerateConfiguration') as accelerate_configuration,\r\n                  JSON_EXTRACT(Properties, '$.AccessControl') as access_control,\r\n                  JSON_EXTRACT(Properties, '$.AnalyticsConfigurations') as analytics_configurations,\r\n                  JSON_EXTRACT(Properties, '$.BucketEncryption') as bucket_encryption,\r\n                  JSON_EXTRACT(Properties, '$.BucketName') as bucket_name,\r\n                  JSON_EXTRACT(Properties, '$.CorsConfiguration') as cors_configuration,\r\n                  JSON_EXTRACT(Properties, '$.IntelligentTieringConfigurations') as intelligent_tiering_configurations,\r\n                  JSON_EXTRACT(Properties, '$.InventoryConfigurations') as inventory_configurations,\r\n                  JSON_EXTRACT(Properties, '$.LifecycleConfiguration') as lifecycle_configuration,\r\n                  JSON_EXTRACT(Properties, '$.LoggingConfiguration') as logging_configuration,\r\n                  JSON_EXTRACT(Properties, '$.MetricsConfigurations') as metrics_configurations,\r\n                  JSON_EXTRACT(Properties, '$.NotificationConfiguration') as notification_configuration,\r\n                  JSON_EXTRACT(Properties, '$.ObjectLockConfiguration') as object_lock_configuration,\r\n                  JSON_EXTRACT(Properties, '$.ObjectLockEnabled') as object_lock_enabled,\r\n                  JSON_EXTRACT(Properties, '$.OwnershipControls') as ownership_controls,\r\n                  JSON_EXTRACT(Properties, '$.PublicAccessBlockConfiguration') as public_access_block_configuration,\r\n                  JSON_EXTRACT(Properties, '$.ReplicationConfiguration') as replication_configuration,\r\n                  JSON_EXTRACT(Properties, '$.Tags') as tags,\r\n                  JSON_EXTRACT(Properties, '$.VersioningConfiguration') as versioning_configuration,\r\n                  JSON_EXTRACT(Properties, '$.WebsiteConfiguration') as website_configuration,\r\n                  JSON_EXTRACT(Properties, '$.Arn') as arn,\r\n                  JSON_EXTRACT(Properties, '$.DomainName') as domain_name,\r\n                  JSON_EXTRACT(Properties, '$.DualStackDomainName') as dual_stack_domain_name,\r\n                  JSON_EXTRACT(Properties, '$.RegionalDomainName') as regional_domain_name,\r\n                  JSON_EXTRACT(Properties, '$.WebsiteURL') as website_url\r\n                  FROM aws.cloud_control.resource WHERE data__TypeName = 'AWS::S3::Bucket'\r\n                  AND data__Identifier = '\u003cBucketName\u003e'\r\n                  AND region = 'us-east-1'\r\n              fallback:\r\n                predicate: sqlDialect == \"postgres\"\r\n                ddl: |-\r\n                    SELECT\r\n                    region,\r\n                    data__Identifier,\r\n                    json_extract_path_text(Properties, 'AccelerateConfiguration') as accelerate_configuration,\r\n                    json_extract_path_text(Properties, 'AccessControl') as access_control,\r\n                    json_extract_path_text(Properties, 'AnalyticsConfigurations') as analytics_configurations,\r\n                    json_extract_path_text(Properties, 'BucketEncryption') as bucket_encryption,\r\n                    json_extract_path_text(Properties, 'BucketName') as bucket_name,\r\n                    json_extract_path_text(Properties, 'CorsConfiguration') as cors_configuration,\r\n                    json_extract_path_text(Properties, 'IntelligentTieringConfigurations') as intelligent_tiering_configurations,\r\n                    json_extract_path_text(Properties, 'InventoryConfigurations') as inventory_configurations,\r\n                    json_extract_path_text(Properties, 'LifecycleConfiguration') as lifecycle_configuration,\r\n                    json_extract_path_text(Properties, 'LoggingConfiguration') as logging_configuration,\r\n                    json_extract_path_text(Properties, 'MetricsConfigurations') as metrics_configurations,\r\n                    json_extract_path_text(Properties, 'NotificationConfiguration') as notification_configuration,\r\n                    json_extract_path_text(Properties, 'ObjectLockConfiguration') as object_lock_configuration,\r\n                    json_extract_path_text(Properties, 'ObjectLockEnabled') as object_lock_enabled,\r\n                    json_extract_path_text(Properties, 'OwnershipControls') as ownership_controls,\r\n                    json_extract_path_text(Properties, 'PublicAccessBlockConfiguration') as public_access_block_configuration,\r\n                    json_extract_path_text(Properties, 'ReplicationConfiguration') as replication_configuration,\r\n                    json_extract_path_text(Properties, 'Tags') as tags,\r\n                    json_extract_path_text(Properties, 'VersioningConfiguration') as versioning_configuration,\r\n                    json_extract_path_text(Properties, 'WebsiteConfiguration') as website_configuration,\r\n                    json_extract_path_text(Properties, 'Arn') as arn,\r\n                    json_extract_path_text(Properties, 'DomainName') as domain_name,\r\n                    json_extract_path_text(Properties, 'DualStackDomainName') as dual_stack_domain_name,\r\n                    json_extract_path_text(Properties, 'RegionalDomainName') as regional_domain_name,\r\n                    json_extract_path_text(Properties, 'WebsiteURL') as website_url\r\n                    FROM aws.cloud_control.resource WHERE data__TypeName = 'AWS::S3::Bucket'\r\n                    AND data__Identifier = '\u003cBucketName\u003e'\r\n                    AND region = 'us-east-1'\r\n            - method_name: list_buckets_with_details\r\n              params:\r\n                - region\r\n                - all_properties\r\n              predicate: sqlDialect == \"sqlite3\"\r\n              ddl: |-\r\n                  SELECT\r\n                  region,\r\n                  data__Identifier,\r\n                  JSON_EXTRACT(Properties, '$.AccelerateConfiguration') as accelerate_configuration,\r\n                  JSON_EXTRACT(Properties, '$.AccessControl') as access_control,\r\n                  JSON_EXTRACT(Properties, '$.AnalyticsConfigurations') as analytics_configurations,\r\n                  JSON_EXTRACT(Properties, '$.BucketEncryption') as bucket_encryption,\r\n                  JSON_EXTRACT(Properties, '$.BucketName') as bucket_name,\r\n                  JSON_EXTRACT(Properties, '$.CorsConfiguration') as cors_configuration,\r\n                  JSON_EXTRACT(Properties, '$.IntelligentTieringConfigurations') as intelligent_tiering_configurations,\r\n                  JSON_EXTRACT(Properties, '$.InventoryConfigurations') as inventory_configurations,\r\n                  JSON_EXTRACT(Properties, '$.LifecycleConfiguration') as lifecycle_configuration,\r\n                  JSON_EXTRACT(Properties, '$.LoggingConfiguration') as logging_configuration,\r\n                  JSON_EXTRACT(Properties, '$.MetricsConfigurations') as metrics_configurations,\r\n                  JSON_EXTRACT(Properties, '$.NotificationConfiguration') as notification_configuration,\r\n                  JSON_EXTRACT(Properties, '$.ObjectLockConfiguration') as object_lock_configuration,\r\n                  JSON_EXTRACT(Properties, '$.ObjectLockEnabled') as object_lock_enabled,\r\n                  JSON_EXTRACT(Properties, '$.OwnershipControls') as ownership_controls,\r\n                  JSON_EXTRACT(Properties, '$.PublicAccessBlockConfiguration') as public_access_block_configuration,\r\n                  JSON_EXTRACT(Properties, '$.ReplicationConfiguration') as replication_configuration,\r\n                  JSON_EXTRACT(Properties, '$.Tags') as tags,\r\n                  JSON_EXTRACT(Properties, '$.VersioningConfiguration') as versioning_configuration,\r\n                  JSON_EXTRACT(Properties, '$.WebsiteConfiguration') as website_configuration,\r\n                  JSON_EXTRACT(Properties, '$.Arn') as arn,\r\n                  JSON_EXTRACT(Properties, '$.DomainName') as domain_name,\r\n                  JSON_EXTRACT(Properties, '$.DualStackDomainName') as dual_stack_domain_name,\r\n                  JSON_EXTRACT(Properties, '$.RegionalDomainName') as regional_domain_name,\r\n                  JSON_EXTRACT(Properties, '$.WebsiteURL') as website_url\r\n                  FROM aws.cloud_control.resource WHERE data__TypeName = 'AWS::S3::Bucket'\r\n                  AND data__Identifier IN \r\n                  (\r\n                    SELECT\r\n                    JSON_EXTRACT(Properties, '$.BucketName') as bucket_name\r\n                    FROM aws.cloud_control.resources\r\n                    /* WHERE params in inner query are inherited from the outer query (region and data__TypeName), can we overwritten */\r\n                  )\r\n                  AND region = 'us-east-1'                \r\n              fallback:\r\n...                 \r\n```\r\n\r\n__usage:__  \r\n\r\nto get a simple list of bucket names\r\n\r\n```sql\r\nSELECT * FROM aws.s3.buckets WHERE region = 'us-east-1';\r\n```\r\n\r\nto get a details for a specific bucket:\r\n\r\n```sql\r\nSELECT * FROM aws.s3.buckets WHERE region = 'us-east-1' and data__Identifier = 'my-bucket-name';\r\n```\r\n\r\nto get a details for all buckets in a region (list retrival in `WHERE` subquery followed by asnyc get queries):\r\n\r\n```sql\r\nSELECT * FROM aws.s3.buckets WHERE region = 'us-east-1' and all_properties = true;\r\n```","author":{"url":"https://github.com/jeffreyaven","@type":"Person","name":"jeffreyaven"},"datePublished":"2024-05-15T01:29:30.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/415/stackql/issues/415"}