domain_reseller¶
Features for Domains Registered via a Reseller
Stores additional details for dns.registered domains. Also supports storing contact informations (handles).
- This module sends the following signals:
- domain_reseller/handle
- domain_registered/managed
- domain_registered/unmanaged
Schema Contents
- Tables
- Functions
domain_reseller.del_handle
domain_reseller.fwd_handle_id
domain_reseller.fwd_registered_status
domain_reseller.ins_handle
domain_reseller.ins_registered
domain_reseller.sel_handle
domain_reseller.sel_registered
domain_reseller.sel_reseller
domain_reseller.srv_handle
domain_reseller.srv_registered
domain_reseller.upd_handle
domain_reseller.upd_registered
Tables¶
domain_reseller.handle
¶
Handles (Domain Contacts)
Domain contacts that can be used as owner, admin-c, tech-c or zone-c.
- Primary key
- alias
- Foreign keys
Reference service entity
- Local Columns
- service_entity_name
- service
- Referenced Columns
Reference subservice entity
- Local Columns
- service_entity_name
- service
- subservice
- Referenced Columns
- Columns
service_entity_name
dns.t_hostnameService entity name
service
commons.t_keyService (e.g. email, jabber)
subservice
commons.t_keySubservice (e.g. account, alias)
owner
user.t_userOwner
References user.user.owner
On Update: CASCADE
backend_status
NULL | backend.t_statusStatus of database entry in backend. NULL: nothing pending, ‘ins’: entry not present on backend client, ‘upd’: update pending on backend client, ‘del’: deletion peding on backend client.
- Default
'ins'
alias
varcharFree choosable alias
id
NULL | varcharInternal id at reseller
fname
varcharFirst name
lname
varcharLast name
address
varcharAddress
pcode
varcharPostcode
city
varcharCity
country
varcharCountry
state
varcharState
email
email.t_addressEmail
phone
varcharPhone
organization
NULL | varcharOrganization
fax
NULL | varcharFax
mobile_phone
NULL | varcharMobile phone
domain_reseller.registered
¶
Addtional informations to those stored in dns.registered
- Primary key
- domain
- Columns
domain
dns.t_hostnameDomain
References dns.registered.domain
On Delete: CASCADE
registrant
varcharRegistrant (Owner)
References domain_reseller.handle.alias
admin_c
varcharAdmin-C
References domain_reseller.handle.alias
tech_c
NULL | varcharTech-C
References domain_reseller.handle.alias
zone_c
NULL | varcharZone-C
References domain_reseller.handle.alias
payable
NULL | timestampPayable
period
NULL | integerRenewal period (years)
registrar_status
NULL | varcharRegistrar status
registry_status
NULL | varcharRegistry status
last_status
NULL | varcharLast update status
Functions¶
domain_reseller.del_handle
¶
Deletes handle
- Parameters
p_alias
varchar
- Variables defined for body
v_service_entity_name
dns.t_hostnamev_owner
user.t_user
- Returns
- void
- Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude
BEGIN
-- perform DELETE to trigger potential foreign key errors
DELETE FROM domain_reseller.handle
WHERE
alias = p_alias AND
owner = v_owner;
-- if not failed yet, emulate rollback of DELETE
RAISE transaction_rollback;
EXCEPTION
WHEN transaction_rollback THEN
UPDATE domain_reseller.handle
SET backend_status = 'del'
WHERE
alias = p_alias AND
owner = v_owner
RETURNING service_entity_name INTO v_service_entity_name;
PERFORM backend._conditional_notify_service_entity_name(
FOUND, v_service_entity_name, 'domain_reseller', 'handle'
);
END;
domain_reseller.fwd_handle_id
¶
Insert handle id
- Parameters
- Returns
- void
- Execute privilege
PERFORM backend._get_login();
UPDATE domain_reseller.handle
SET id = p_id
WHERE alias = p_alias;
domain_reseller.fwd_registered_status
¶
Update status
- Parameters
- Returns
- void
- Execute privilege
PERFORM backend._get_login();
UPDATE domain_reseller.registered
SET
payable = p_payable,
period = p_period,
registrar_status = p_registrar_status,
registry_status = p_registry_status,
last_status = p_last_status
WHERE domain = p_domain;
domain_reseller.ins_handle
¶
Inserts handle
- Parameters
- Variables defined for body
v_owner
user.t_user
- Returns
- void
- Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude
INSERT INTO domain_reseller.handle
(
service_entity_name,
service,
subservice,
owner,
alias,
fname,
lname,
address,
pcode,
city,
country,
state,
email,
phone,
organization,
fax,
mobile_phone
)
VALUES
(
p_service_entity_name,
'domain_reseller',
'handle',
v_owner,
p_alias,
p_fname,
p_lname,
p_address,
p_pcode,
p_city,
p_country,
p_state,
p_email,
p_phone,
p_organization,
p_fax,
p_mobile_phone
);
PERFORM backend._notify_service_entity_name(p_service_entity_name, 'domain_reseller', 'handle');
domain_reseller.ins_registered
¶
Inserts details for registered domain
- Parameters
p_domain
dns.t_hostnamep_registrant
varcharp_admin_c
varchar
- Variables defined for body
v_owner
user.t_user
- Returns
- void
- Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude
INSERT INTO domain_reseller.registered
(domain, registrant, admin_c)
VALUES
(p_domain, p_registrant, p_admin_c);
domain_reseller.sel_handle
¶
Selects handles
- Parameters
p_hide_foreign
bool
- Variables defined for body
v_owner
user.t_user
- Returns
- SETOF domain_reseller.”handle”
- Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude
RETURN QUERY
SELECT * FROM domain_reseller.handle
WHERE
owner=v_owner OR (owner="user"._login_user() AND NOT p_hide_foreign)
ORDER BY backend_status, fname, lname, alias;
domain_reseller.sel_registered
¶
Selects details for registered domains
- Parameters
- None
- Variables defined for body
v_owner
user.t_user
- Returns
- TABLE
- Returned columns
- Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude
RETURN QUERY
SELECT
t.domain,
t.registrant,
t.admin_c,
t.tech_c,
t.zone_c,
t.payable,
t.period,
t.registrar_status,
t.registry_status,
t.last_status,
s.backend_status
FROM domain_reseller.registered AS t
JOIN dns.registered AS s
USING (domain)
WHERE
s.owner = v_owner
ORDER BY backend_status, domain
;
domain_reseller.sel_reseller
¶
Selects available resellers
- Parameters
- None
- Variables defined for body
v_owner
user.t_user
- Returns
- TABLE
- Returned columns
subservice
commons.t_keyservice_entity_name
dns.t_hostname
- Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude
RETURN QUERY
SELECT
COALESCE(t.subservice, s.subservice) AS subservice,
COALESCE(t.service_entity_name, s.service_entity_name) AS service_entity_name
FROM system._effective_contingent() AS t
FULL OUTER JOIN system._effective_contingent_domain() AS s
USING (service, subservice, service_entity_name, owner)
WHERE
COALESCE(t.service, s.service) = 'domain_reseller' AND
COALESCE(t.owner, s.owner) = v_owner
ORDER BY subservice, service_entity_name
;
domain_reseller.srv_handle
¶
Serves handles
- Parameters
p_include_inactive
boolean
- Returns
- SETOF domain_reseller.”handle”
- Execute privilege
PERFORM backend._get_login();
RETURN QUERY
WITH
-- DELETE
d AS (
DELETE FROM domain_reseller.handle AS t
WHERE
backend._machine_priviledged_service(t.service, t.service_entity_name) AND
backend._deleted(t.backend_status)
),
-- UPDATE
s AS (
UPDATE domain_reseller.handle AS t
SET backend_status = NULL
WHERE
backend._machine_priviledged_service(t.service, t.service_entity_name) AND
backend._active(t.backend_status)
)
SELECT * FROM domain_reseller.handle AS t
WHERE
backend._machine_priviledged_service(t.service, t.service_entity_name) AND
(backend._active(t.backend_status) OR p_include_inactive);
domain_reseller.srv_registered
¶
Serves details for registered domains
- Parameters
p_include_inactive
boolean
- Returns
- TABLE
- Returned columns
domain
dns.t_hostnameregistrant
varcharregistrant_id
varcharadmin_c
varcharadmin_c_id
varchartech_c
varchartech_c_id
varcharzone_c
varcharzone_c_id
varcharbackend_status
backend.t_status
- Execute privilege
PERFORM backend._get_login();
RETURN QUERY
SELECT
t.domain,
t.registrant,
(SELECT id FROM domain_reseller.handle WHERE alias = t.registrant),
t.admin_c,
(SELECT id FROM domain_reseller.handle WHERE alias = t.admin_c),
t.tech_c,
(SELECT id FROM domain_reseller.handle WHERE alias = t.tech_c),
t.zone_c,
(SELECT id FROM domain_reseller.handle WHERE alias = t.zone_c),
s.backend_status
FROM domain_reseller.registered AS t
JOIN dns.registered AS s USING (domain)
WHERE
backend._machine_priviledged_service(s.service, s.service_entity_name) AND
(backend._active(s.backend_status) OR p_include_inactive);
domain_reseller.upd_handle
¶
Updates handle
- Parameters
- Variables defined for body
v_service_entity_name
dns.t_hostnamev_owner
user.t_user
- Returns
- void
- Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude
UPDATE domain_reseller.handle
SET
backend_status = 'upd',
address = p_address,
pcode = p_pcode,
city = p_city,
country = p_country,
state = p_state,
email = p_email,
phone = p_phone,
organization = p_organization,
fax = p_fax,
mobile_phone = p_mobile_phone
WHERE
alias = p_alias AND
owner = v_owner
RETURNING service_entity_name INTO v_service_entity_name;
PERFORM backend._conditional_notify_service_entity_name(
FOUND, v_service_entity_name, 'domain_reseller', 'handle'
);
domain_reseller.upd_registered
¶
Updates details for registered domain
- Parameters
p_domain
dns.t_hostnamep_admin_c
varchar
- Variables defined for body
v_nameserver
dns.t_hostnamev_managed
commons.t_keyv_owner
user.t_user
- Returns
- void
- Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude
UPDATE domain_reseller.registered AS t
SET
admin_c = p_admin_c
FROM dns.registered AS s
WHERE
s.domain = t.domain AND
s.owner = v_owner AND
t.domain = p_domain;
UPDATE dns.registered AS t
SET backend_status = 'upd'
WHERE
t.owner = v_owner AND
t.domain = p_domain AND
-- don't change domains that are in some transition status
(t.backend_status = 'upd' OR t.backend_status IS NULL)
RETURNING t.service_entity_name, t.subservice
INTO v_nameserver, v_managed;
PERFORM backend._conditional_notify_service_entity_name(
FOUND, v_nameserver, 'domain_registered', v_managed
);