-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathaddress_format_config.sql
More file actions
101 lines (82 loc) · 5.96 KB
/
Copy pathaddress_format_config.sql
File metadata and controls
101 lines (82 loc) · 5.96 KB
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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
-- ============================================================================
-- Address Format Configuration Schema
-- ============================================================================
--
-- Maps generic fields in `international_addresses` to country-specific form
-- rendering: labels, ordering, input types, validation, and dropdown options.
--
-- ============================================================================
DROP TABLE IF EXISTS address_field_config;
-- ---------------------------------------------------------------------------
-- Table: address_field_config
-- One row per generic field per country. Drives form rendering and validation.
-- ---------------------------------------------------------------------------
CREATE TABLE address_field_config (
config_id INT AUTO_INCREMENT PRIMARY KEY,
country_code CHAR(2) NOT NULL,
generic_field_name VARCHAR(50) NOT NULL, -- column name in international_addresses
display_label VARCHAR(100) NOT NULL, -- user-facing label, e.g. "ZIP Code"
display_order INT NOT NULL, -- render order on the form
is_required BOOLEAN NOT NULL DEFAULT FALSE,
input_type VARCHAR(20) NOT NULL DEFAULT 'text', -- text | select | textarea
placeholder VARCHAR(255),
validation_pattern VARCHAR(500), -- regex for client-side validation
validation_message VARCHAR(255), -- error shown when pattern fails
max_length INT,
UNIQUE KEY uq_country_field (country_code, generic_field_name)
);
-- ============================================================================
-- SEED DATA: United States (US)
-- ============================================================================
INSERT INTO address_field_config
(country_code, generic_field_name, display_label, display_order, is_required, input_type, placeholder, validation_pattern, validation_message, max_length)
VALUES
('US', 'street_number', 'Street Number', 1, FALSE, 'text', '123', NULL, NULL, 20),
('US', 'street_name', 'Street Name', 2, TRUE, 'text', 'Main St', NULL, NULL, 255),
('US', 'address_line2', 'Apt / Suite / Unit', 3, FALSE, 'text', 'Apt 4B', NULL, NULL, 255),
('US', 'city', 'City', 4, TRUE, 'text', 'Seattle', NULL, NULL, 100),
('US', 'administrative_area', 'State', 5, TRUE, 'select', NULL, NULL, 'Please select a state', NULL),
('US', 'postal_code', 'ZIP Code', 6, TRUE, 'text', '98101', '^\\d{5}(-\\d{4})?$', 'Enter a valid ZIP (e.g. 98101 or 98101-1234)', 10);
-- ============================================================================
-- SEED DATA: Canada (CA)
-- ============================================================================
INSERT INTO address_field_config
(country_code, generic_field_name, display_label, display_order, is_required, input_type, placeholder, validation_pattern, validation_message, max_length)
VALUES
('CA', 'street_number', 'Street Number', 1, FALSE, 'text', '123', NULL, NULL, 20),
('CA', 'street_name', 'Street Name', 2, TRUE, 'text', 'Maple Ave', NULL, NULL, 255),
('CA', 'address_line2', 'Apt / Suite / Unit', 3, FALSE, 'text', 'Unit 5', NULL, NULL, 255),
('CA', 'city', 'City', 4, TRUE, 'text', 'Toronto', NULL, NULL, 100),
('CA', 'administrative_area', 'Province / Territory', 5, TRUE, 'select', NULL, NULL, 'Please select a province or territory', NULL),
('CA', 'postal_code', 'Postal Code', 6, TRUE, 'text', 'K1A 0B1', '^[A-Za-z]\\d[A-Za-z]\\s?\\d[A-Za-z]\\d$', 'Enter a valid postal code (e.g. K1A 0B1)', 7);
-- ============================================================================
-- SEED DATA: India (IN)
-- ============================================================================
INSERT INTO address_field_config
(country_code, generic_field_name, display_label, display_order, is_required, input_type, placeholder, validation_pattern, validation_message, max_length)
VALUES
('IN', 'street_number', 'House / Bldg No', 1, FALSE, 'text', '44', NULL, NULL, 20),
('IN', 'street_name', 'Street Name', 2, TRUE, 'text', 'Marine Drive', NULL, NULL, 255),
('IN', 'address_line2', 'Locality / Area', 3, FALSE, 'text', 'Near City Mall, Sector 5', NULL, NULL, 255),
('IN', 'address_line3', 'Colony / District', 4, FALSE, 'text', 'MIDC Colony', NULL, NULL, 255),
('IN', 'city', 'City / Town', 5, TRUE, 'text', 'Mumbai', NULL, NULL, 100),
('IN', 'administrative_area', 'State', 6, TRUE, 'select', NULL, NULL, 'Please select a state or union territory', NULL),
('IN', 'postal_code', 'PIN Code', 7, TRUE, 'text', '400001', '^\\d{6}$', 'Enter a valid 6-digit PIN code', 6);
-- ============================================================================
-- EXAMPLE QUERIES
-- ============================================================================
-- 1. Get form field configuration for a given country
-- Returns one row per field, ordered for rendering.
SELECT
generic_field_name,
display_label,
display_order,
is_required,
input_type,
placeholder,
validation_pattern,
validation_message,
max_length
FROM address_field_config
WHERE country_code = 'US'
ORDER BY display_order;