Open source website builder and Webflow alternative. Webstudio is an advanced visual builder that connects to any headless CMS, supports all CSS properties, and can be hosted anywhere, including with us.
TypeScript accessibilityalternativecloudflarecloudflare-workersdesignfreefrontendopen-sourceradix-uireactremixweb-developmentwebflowwebsite-builderwebsite-developmentwebsiteswebstudio
BEGIN;
SET
LOCAL search_path = pgtap,
public;
-- Initialize the testing environment without planning any specific number of tests
SELECT
no_plan();
--------------------------------------------------------------------------------
-- Setup: Insert Initial Data
--------------------------------------------------------------------------------
-- Insert a new user into the User table
INSERT INTO
"public"."User" ("id", "createdAt", "email", "username")
VALUES
(
'user1',
'2023-01-01 00:00:00+00',
'[email protected]',
'user1'
);
-- Insert projects associated with the user
INSERT INTO
"public"."Project" (
"id",
"title",
"domain",
"userId",
"isDeleted",
"createdAt"
)
VALUES
(
'project1',
'Project One',
'517cce32-9af3-project1-domain1',
'user1',
false,
'2023-01-01 00:00:00+00'
),
(
'project2',
'Project Two',
'517cce32-9af3-project2-domain1',
'user1',
false,
'2023-01-01 00:00:00+00'
);
-- Insert builds with different deployment formats
INSERT INTO
"public"."Build" (
"id",
"createdAt",
"pages",
"projectId",
"deployment",
"updatedAt",
"publishStatus"
)
VALUES
-- Old deployment format: includes projectDomain
(
'build1',
'2023-01-01 00:00:00+00',
'home',
'project1',
'{"projectDomain": "517cce32-9af3-project1-domain1", "domains": [""]}' :: text,
'2023-01-01 00:00:00+00',
'PUBLISHED'
),
(
'build1-old',
'2022-01-01 00:00:00+00',
'home',
'project1',
'{"projectDomain": "517cce32-9af3-project1-domain1", "domains": [""]}' :: text,
'2022-01-01 00:00:00+00',
'PUBLISHED'
),
(
'build1-newest-wrong-domain',
'2024-01-01 00:00:00+00',
'home',
'project1',
'{"projectDomain": "project-wrong", "domains": [""]}' :: text,
'2024-01-01 00:00:00+00',
'PUBLISHED'
),
-- New deployment format: domains array only
(
'build2',
'2023-01-02 00:00:00+00',
'home',
'project2',
'{"domains": ["517cce32-9af3-project2-domain1"]}' :: text,
'2023-01-02 00:00:00+00',
'PENDING'
),
(
'build2-old',
'2022-01-02 00:00:00+00',
'home',
'project2',
'{"domains": ["517cce32-9af3-project2-domain1"]}' :: text,
'2022-01-02 00:00:00+00',
'PENDING'
);
--------------------------------------------------------------------------------
-- Test Case 1: Verify Latest Build Retrieval Using Old Deployment Format (projectDomain)
--------------------------------------------------------------------------------
SELECT
is (
(
SELECT
ARRAY ["buildId", "domain"]
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
ARRAY ['build1', '517cce32-9af3-project1-domain1'],
'Test Case 1.1: Should return the latest build for project1 with domain matching projectDomain.'
);
SELECT
is (
(
SELECT
ARRAY ["buildId", "domain"]
FROM
"public"."latestProjectDomainBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
ARRAY ['build1', '517cce32-9af3-project1-domain1'],
'Test Case 1.2: Should return the latest build for project1 with domain matching projectDomain.'
);
--------------------------------------------------------------------------------
-- Test Case 2: Verify Latest Build Retrieval Using New Deployment Format (domains array)
--------------------------------------------------------------------------------
SELECT
is (
(
SELECT
"buildId"
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project2'
)
)
),
'build2',
'Test Case 2.1: Should return the latest build for project2 with domain present in domains array.'
);
SELECT
is (
(
SELECT
"buildId"
FROM
"public"."latestProjectDomainBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project2'
)
)
),
'build2',
'Test Case 2.2: Should return the latest build for project2 domain with domain present in domains array.'
);
--------------------------------------------------------------------------------
-- Test Case 3: Update Project Domain and Verify No Build Exists for the New Domain
--------------------------------------------------------------------------------
-- Update project1's domain to a new domain
UPDATE
"public"."Project"
SET
"domain" = 'project1-domain2'
WHERE
"id" = 'project1';
-- Verify that no build exists for the updated domain
SELECT
is (
(
SELECT
COUNT(*) :: integer
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
0,
'Test Case 3.1: Should return 0 as no build exists for the updated domain project1-domain2.'
);
SELECT
is (
(
SELECT
COUNT(*) :: integer
FROM
"public"."latestProjectDomainBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
0,
'Test Case 3.2: Should return 0 as no build exists for the updated domain project1-domain2.'
);
--------------------------------------------------------------------------------
-- Test Case 4: Insert a New Build with the Updated Domain and Verify Retrieval
--------------------------------------------------------------------------------
-- Insert a new build associated with the updated domain
INSERT INTO
"public"."Build" (
"id",
"createdAt",
"pages",
"projectId",
"deployment",
"updatedAt",
"publishStatus"
)
VALUES
(
'build1-for-domain2',
'2023-01-01 00:00:00+00',
'home',
'project1',
'{"domains": ["some-other-domain.com", "project1-domain2"]}' :: text,
'2023-01-01 00:00:00+00',
'PUBLISHED'
);
-- Verify that the latest build now reflects the updated domain
SELECT
is (
(
SELECT
ARRAY ["buildId", "domain"]
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
ARRAY ['build1-for-domain2','project1-domain2'],
'Test Case 4.1: Should return the latest build for project1 with the updated domain in domains array.'
);
SELECT
is (
(
SELECT
ARRAY ["buildId", "domain"]
FROM
"public"."latestProjectDomainBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
ARRAY ['build1-for-domain2','project1-domain2'],
'Test Case 4.2: Should return the latest build for project1 domain with the updated domain in domains array.'
);
--------------------------------------------------------------------------------
-- Test Case 5: Register Custom Domains and Verify Latest Build for a Custom Domain
--------------------------------------------------------------------------------
-- Insert custom domains
INSERT INTO
"public"."Domain" (
"id",
"domain",
"createdAt",
"status",
"updatedAt"
)
VALUES
(
'project-1-custom-domain-1',
'517cce32-9af3-project-1-custom-domain-1.com',
'2023-01-01 00:00:00+00',
'INITIALIZING',
'2023-01-01 00:00:00+00'
),
(
'project-1-custom-domain-2',
'517cce32-9af3-project-1-custom-domain-2.com',
'2023-01-01 00:00:00+00',
'INITIALIZING',
'2023-01-01 00:00:00+00'
);
-- Establish relationships between project1 and custom domains
INSERT INTO
"public"."ProjectDomain" (
"projectId",
"domainId",
"createdAt",
"txtRecord",
"cname"
)
VALUES
(
'project1',
'project-1-custom-domain-1',
'2023-01-01 00:00:00+00',
'txtRecord1',
'cname1'
),
(
'project1',
'project-1-custom-domain-2',
'2023-01-01 00:00:00+00',
'txtRecord2',
'cname2'
);
-- Insert a build associated with a custom domain
INSERT INTO
"public"."Build" (
"id",
"createdAt",
"pages",
"projectId",
"deployment",
"updatedAt",
"publishStatus"
)
VALUES
(
'build1-for-custom-domain-1',
'2023-01-02 00:00:00+00',
'home',
'project1',
'{"domains": ["some-other-domain.com", "517cce32-9af3-project-1-custom-domain-1.com"]}' :: text,
'2023-01-02 00:00:00+00',
'PUBLISHED'
);
-- Verify that the latest build reflects the custom domain association
SELECT
is (
(
SELECT
ARRAY ["buildId", "domain"]
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
ARRAY ['build1-for-custom-domain-1','517cce32-9af3-project-1-custom-domain-1.com'],
'Test Case 5.1: Should return the latest build for project1 with a registered custom domain in domains array.'
);
-- Ensure the latest project domain build has not changed
-- The difference between latestProjectDomainBuildVirtual and latestBuildVirtual is that the first returns data only for the project domain
SELECT
is (
(
SELECT
ARRAY ["buildId", "domain"]
FROM
"public"."latestProjectDomainBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
ARRAY ['build1-for-domain2','project1-domain2'],
'Test Case 5.2: Should return the latest build for project1 domain and not affected by custom domains'
);
--------------------------------------------------------------------------------
-- Test Case 6: Publish a Preview Domain and Verify Latest Build Retrieval
--------------------------------------------------------------------------------
-- Insert a build for the preview domain using the new deployment format
INSERT INTO
"public"."Build" (
"id",
"createdAt",
"pages",
"projectId",
"deployment",
"updatedAt",
"publishStatus"
)
VALUES
(
'build1-for-domain2-new',
'2023-01-03 00:00:00+00',
'home',
'project1',
'{"domains": ["517cce32-9af3-project-1-custom-domain-1.com", "project1-domain2"]}' :: text,
'2023-01-03 00:00:00+00',
'PUBLISHED'
);
-- Verify that the latest build reflects the preview domain
SELECT
is (
(
SELECT
ARRAY ["buildId", "domain"]
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
ARRAY ['build1-for-domain2-new', 'project1-domain2'],
'Test Case 6.1: Should return the latest build for project1 with the preview domain in domains array.'
);
SELECT
is (
(
SELECT
ARRAY ["buildId", "domain"]
FROM
"public"."latestProjectDomainBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
ARRAY ['build1-for-domain2-new', 'project1-domain2'],
'Test Case 6.2: Should return the latest build for project1 with the preview domain in domains array.'
);
--------------------------------------------------------------------------------
-- Test Case 7: Publish a New Build for a Custom Domain, Delete the Custom Domain, and Verify Latest Build Update
--------------------------------------------------------------------------------
-- Insert a new build for the custom domain
INSERT INTO
"public"."Build" (
"id",
"createdAt",
"pages",
"projectId",
"deployment",
"updatedAt",
"publishStatus"
)
VALUES
(
'build1-for-custom-domain-1-new',
'2023-01-04 00:00:00+00',
'home',
'project1',
'{"domains": ["some-other-domain.com", "517cce32-9af3-project-1-custom-domain-1.com"]}' :: text,
'2023-01-04 00:00:00+00',
'PUBLISHED'
);
-- Verify that the latest build reflects the newly published build for the custom domain
SELECT
is (
(
SELECT
"buildId"
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
'build1-for-custom-domain-1-new',
'Test Case 7a: Should return the latest build after publishing a new build for a custom domain.'
);
-- Delete the custom domain association
DELETE FROM
"public"."ProjectDomain"
WHERE
"projectId" = 'project1'
AND "domainId" = 'project-1-custom-domain-1';
-- Verify that the latest build reverts to the previous latest build after deletion
SELECT
is (
(
SELECT
"buildId"
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
'build1-for-domain2-new',
'Test Case 7b: Should return the latest build after deleting the custom domain, reverting to the previous latest build.'
);
--------------------------------------------------------------------------------
-- Test Case 8: Publish a New Build for a Custom Domain, Move the Custom Domain, and Verify Latest Build Update
--------------------------------------------------------------------------------
-- Re-establish the custom domain association to revert Test Case 7
INSERT INTO
"public"."ProjectDomain" (
"projectId",
"domainId",
"createdAt",
"txtRecord",
"cname"
)
VALUES
(
'project1',
'project-1-custom-domain-1',
'2023-01-01 00:00:00+00',
'txtRecord1',
'cname1'
);
-- Verify that the latest build is updated after re-establishing the custom domain
SELECT
is (
(
SELECT
"buildId"
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
'build1-for-custom-domain-1-new',
'Test Case 8a: Should return the latest build after re-publishing a new build for a custom domain.'
);
-- Move the custom domain association from project1 to project2
UPDATE
"public"."ProjectDomain"
SET
"projectId" = 'project2'
WHERE
"projectId" = 'project1'
AND "domainId" = 'project-1-custom-domain-1';
-- Verify that the latest build reverts to the previous latest build after moving the custom domain
SELECT
is (
(
SELECT
"buildId"
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project1'
)
)
),
'build1-for-domain2-new',
'Test Case 8b: Should return the latest build after moving the custom domain, reverting to the previous latest build.'
);
--------------------------------------------------------------------------------
-- Test Case 9: Verify updatedAt Field is Returned (Selection is Still by createdAt)
--------------------------------------------------------------------------------
-- Insert builds with different createdAt and updatedAt
-- NOTE: "Latest build" is determined by createdAt (when published), not updatedAt
INSERT INTO
"public"."Build" (
"id",
"createdAt",
"pages",
"projectId",
"deployment",
"updatedAt",
"publishStatus"
)
VALUES
-- Older createdAt but newer updatedAt
(
'build-updated-recently',
'2023-01-01 00:00:00+00',
'home',
'project2',
'{"domains": ["517cce32-9af3-project2-domain1"]}' :: text,
'2024-01-01 00:00:00+00',
'PUBLISHED'
),
-- Newer createdAt but older updatedAt - SHOULD be selected as "latest"
(
'build-created-recently',
'2023-06-01 00:00:00+00',
'home',
'project2',
'{"domains": ["517cce32-9af3-project2-domain1"]}' :: text,
'2023-01-15 00:00:00+00',
'PUBLISHED'
);
-- Verify that the build with newest createdAt is selected (not newest updatedAt)
SELECT
is (
(
SELECT
"buildId"
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project2'
)
)
),
'build-created-recently',
'Test Case 9.1: Should select build with newest createdAt (selection is by createdAt, not updatedAt).'
);
-- Verify that updatedAt field is returned (even though it is not the newest)
SELECT
is (
(
SELECT
"updatedAt"
FROM
"public"."latestBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project2'
)
)
),
'2023-01-15 00:00:00+00' :: timestamp with time zone,
'Test Case 9.2: updatedAt field from the selected build should be returned in latestBuildVirtual result.'
);
SELECT
is (
(
SELECT
"updatedAt"
FROM
"public"."latestProjectDomainBuildVirtual"(
(
SELECT
(p.*) :: "Project"
FROM
"public"."Project" p
WHERE
p."id" = 'project2'
)
)
),
'2023-01-15 00:00:00+00' :: timestamp with time zone,
'Test Case 9.3: updatedAt field from the selected build should be returned in latestProjectDomainBuildVirtual result.'
);
--------------------------------------------------------------------------------
-- Test Case 10: Verify All Columns Are Returned in Correct Order
--------------------------------------------------------------------------------
-- Check that all 7 columns are present in the result
SELECT
ok(
(
SELECT
COUNT(*) = 7
FROM
information_schema.columns
WHERE
table_name = 'latestBuildVirtual'
),
'Test Case 10: latestBuildVirtual should have exactly 7 columns (buildId, projectId, domainsVirtualId, domain, createdAt, publishStatus, updatedAt).'
);
-- Verify column names match expected structure
SELECT
bag_eq(
$$
SELECT
column_name :: text
FROM
information_schema.columns
WHERE
table_name = 'latestBuildVirtual' $$,
$$
VALUES
('buildId'),
('projectId'),
('domainsVirtualId'),
('domain'),
('createdAt'),
('publishStatus'),
('updatedAt') $$,
'Test Case 10.1: All expected columns should be present in latestBuildVirtual table.'
);
-- Finalize the tests
SELECT
finish();
ROLLBACK;