awscraper/test.sql at main · thiagosanches/awscraper · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
CREATE TABLE IF NOT EXISTS artifact (
    Id              INTEGER NOT NULL,
    ParentId        INTEGER,
    TypeId          INTEGER NOT NULL,
    Identifier      TEXT NOT NULL,
    Removed         INTEGER NOT NULL,
    LastFetchTime   TEXT NOT NULL,
    PRIMARY KEY (
        Id AUTOINCREMENT
    ),
    FOREIGN KEY (
        ParentId
    ) REFERENCES artifact(Id)
);


DELETE FROM artifact;
UPDATE `sqlite_sequence` SET `seq` = 0 WHERE `name` = 'artifact';
INSERT INTO artifact (TypeId, Identifier, Removed, LastFetchTime) VALUES (1, 'xyz-project-one', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (1, 2, 'lambda1', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (1, 2, 'lambda2', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (1, 2, 'lambda3', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (1, 2, 'lambda4', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (1, 2, 'lambda5', 'false', CURRENT_TIMESTAMP);

INSERT INTO artifact (TypeId, Identifier, Removed, LastFetchTime) VALUES (1, 'xyz-project-two', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (7, 1, 'lambdaA', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (7, 1, 'lambdaB', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (7, 1, 'lambdaX', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (7, 1, 'lambdaY', 'false', CURRENT_TIMESTAMP);
        INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (11, 1, 'lambdaY', 'false', CURRENT_TIMESTAMP);
            INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (23, 1, 'athena', 'false', CURRENT_TIMESTAMP);
            INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (23, 1, 'rds', 'false', CURRENT_TIMESTAMP);
            INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (23, 1, 'sqs', 'false', CURRENT_TIMESTAMP);
            INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (23, 1, 'sns', 'false', CURRENT_TIMESTAMP);
            INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (23, 1, 's3', 'false', CURRENT_TIMESTAMP);

INSERT INTO artifact (TypeId, Identifier, Removed, LastFetchTime) VALUES (1, 'xyz-project-three', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (12, 1, 'lambda01', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (12, 1, 'lambda02', 'false', CURRENT_TIMESTAMP);

INSERT INTO artifact (TypeId, Identifier, Removed, LastFetchTime) VALUES (1, 'xyz-project-four', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (15, 1, 'lambda00001', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (15, 1, 'lambda00002', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (15, 1, 'lambda00003', 'false', CURRENT_TIMESTAMP);

INSERT INTO artifact (TypeId, Identifier, Removed, LastFetchTime) VALUES (1, 'xyz-project-five', 'false', CURRENT_TIMESTAMP);
    INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (19, 1, 'lambda0', 'false', CURRENT_TIMESTAMP);
        INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (20, 1, 'athena', 'false', CURRENT_TIMESTAMP);
            INSERT INTO artifact (ParentId, TypeId, Identifier, Removed, LastFetchTime) VALUES (21, 1, 'glue', 'false', CURRENT_TIMESTAMP);


WITH RECURSIVE artifact_hierarchy AS (
    SELECT ParentId, Id, Identifier
    FROM artifact
    WHERE ParentId IS NULL

    UNION ALL

    SELECT a.ParentId, a.Id, a.Identifier
    FROM artifact a
    JOIN artifact_hierarchy ah ON a.ParentId = ah.Id
) SELECT * FROM artifact_hierarchy;

WITH RECURSIVE ArtifactHierarchy AS (
    SELECT *
    FROM artifact
    WHERE Identifier = 'your_desired_identifier'

    UNION ALL

    SELECT a.*
    FROM artifact a
    JOIN ArtifactHierarchy ah ON a.Id = ah.ParentId
)
SELECT * FROM ArtifactHierarchy;