[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Gnumed-devel] gmDemographicRecord.py re-re-factored
|
From: |
Ian Haywood |
|
Subject: |
[Gnumed-devel] gmDemographicRecord.py re-re-factored |
|
Date: |
Tue, 21 Dec 2004 05:30:40 +1100 |
|
User-agent: |
Mozilla Thunderbird 0.9 (X11/20041124) |
This is gmDemographicRecord now based around gmBusinessDBObject, the
basic unit tests pass.
I have also tweaked the demographic backend schema, to expose
identity.xmin and a few other minor things.
One thing I noticed: the schema allows for multiple occupations.
Personally I think this is a mistake as it is not possible to do
interesting things (like use the right referral form) automatically
based on the occupation, as their is no way to know which is the 'real'
one, and we should just have identity.fk_occupation.
Ian
"""GnuMed demographics object.
This is a patient object intended to let a useful client-side
API crystallize from actual use in true XP fashion.
license: GPL
"""
#============================================================
# $Source:
/cvsroot/gnumed/gnumed/gnumed/client/business/gmDemographicRecord.py,v $
# $Id: gmDemographicRecord.py,v 1.54 2004/08/18 09:05:07 ncq Exp $
__version__ = "$Revision: 1.54 $"
__author__ = "K.Hilbert <address@hidden>, I.Haywood <address@hidden>"
# access our modules
import sys, os.path, time, string
from Gnumed.pycommon import gmLog, gmExceptions, gmPG, gmSignals, gmDispatcher,
gmMatchProvider, gmI18N, gmBusinessDBObject
from Gnumed.business import gmMedDoc
from Gnumed.pycommon.gmPyCompat import *
_log = gmLog.gmDefLog
_log.Log(gmLog.lInfo, __version__)
# 3rd party
import mx.DateTime as mxDT
#============================================================
# map gender abbreviations in a GnuMed demographic service
# to a meaningful localised string
map_gender_gm2long = {
'm': _('male'),
'f': _('female'),
'tf': _('transsexual, female phenotype'),
'tm': _('transsexual, male phenotype')
}
#===================================================================
#class cComm (gmBusinessDBObject.cBusinessDBObject):
#===================================================================
#class cAddress (gmBusinessDBObject.cBusinessObject):
# to be honest, I'm not really convinced it means much sense to be able
to
# change addresses and comms. When someone 'changes' their address,
really they are unbinding
# from the old address and binding to a new one,
# so I'm going back to having addresses and comms as plain dictionaries
#===================================================================
class cOrg (gmBusinessDBObject.cBusinessDBObject):
"""
Organisations
This is also the common ancestor of cIdentity, self._table is used to
hide the difference.
The aim is to be able to sanely write code which doesn't care whether
its talking to an organisation or an individual"""
_table = "org"
_cmd_fetch_payload = "select *, xmin from org where id=%s"
_cmds_lock_rows_for_update = ["select 1 from v_org where id=%(id)s and
xmin=%(xmin)s"]
_cmds_store_payload = ["update org set description=%(description)s,
id_category=(select id from org_category where description=%(occupation)s)
where id=%(id)s"]
_updateable_fields = ["description", "occupation"]
_service = 'personalia'
#------------------------------------------------------------------
def export_demographics (self):
if not self.__cache.has_key ('addresses'):
self['addresses']
if not self.__cache.has_key ('comms'):
self['comms']
return self.__cache
#------------------------------------------------------------------
def get_addresses (self):
"""Returns a list of address dictionaries. Fields are
- id
- number
- addendum
- street
- city
- postcode
- type"""
cmd = """select
vba.id,
vba.number,
vba.addendum,
vba.street,
vba.city,
vba.postcode,
at.name
from
v_basic_address vba,
lnk_person_org_address lpoa,
address_type at
where
lpoa.id_address = vba.id
and lpoa.id_type = at.id
and lpoa.id_%s = %%s
""" % self._table # this operator, then
passed to SQL layer
rows, idx = gmPG.run_ro_query('personalia', cmd, 1,
[self['id']])
if rows is None:
return []
elif len(rows) == 0:
return []
else:
return [{'id':i[0], 'number':i[1], 'addendum':i[2],
'street':i[3], 'city':i[4], 'postcode':i[5],
'type':i[6]} for i in rows]
#--------------------------------------------------------------------
def get_members (self):
"""
Returns a list of (address dict, cIdentity) tuples
"""
cmd = """select
vba.id,
vba.number,
vba.addendum,
vba.street,
vba.city,
vba.postcode,
at.name,
vbp.i_id as id,
title,
firstnames,
lastnames,
dob,
cob,
gender,
pupic,
fk_marital_status,
marital_status,
karyotype,
xmin_identity,
preferred
from
v_basic_address vba,
lnk_person_org_address lpoa,
address_type at,
v_basic_person vbp
where
lpoa.id_address = vba.id
and lpoa.id_type = at.id
and lpoa.id_identity = vbp.i_id
and lpoa.id_org = %%s
"""
rows, idx = gmPG.run_ro_query('personalia', cmd, 1, self['id'])
if rows is None:
return []
elif len(rows) == 0:
return []
else:
return [({'id':i[0], 'number':i[1], 'addendum':i[2],
'street':i[3], 'city':i[4], 'postcode':i[5], 'type':i[6]}, cIdentity (row =
{'data':i[7:], 'id':idx[7:], 'pk_field':'id'})) for i in rows]
#------------------------------------------------------------
def set_member (self, person, address):
"""
Binds a person to this organisation at this address
"""
cmd = "insert into lnk_person_org_address (id_type, id_address,
id_org, id_identity) values ((select id from address_types where
type=%(type)s), create_address (%(number)s, %(addendum)s, %(street)s, %(city)s,
%(postcode)s), %(org_id)s, %(i_id)s)"
address['i_id'] = person['id']
address['org_id'] = self['id']
if not id_addr:
return (False, None)
return gmPG.run_commit2 ('personalia', [(cmd, [address])])
#------------------------------------------------------------
def unlink_person (self, person):
cmd = "delete from lnk_person_org_address where id_org = %s and
id_identity = %s"
return gmPG.run_commit2 ('personalia', [(cmd, [self['id'],
person['id']])])
#------------------------------------------------------------
def unlink_address (self, address):
cmd = "delete from lnk_person_org_address where id_address = %s
and id_%s = %%s" % self._table
return gmPG.run_commit2 ('personalia', [(cmd, [address['id'],
self['id']])])
#------------------------------------------------------------
def get_ext_ids (self):
"""
Returns a list of dictionaries of external IDs
Fields:
- origin [the origin]
- comment [a user comment]
- external_id [the actual external ID]
"""
cmd = """select
enum_ext_id_types.name, comment, external_id
from lnk_%s2ext_id where id_%s = %%s""" % (self._table,
self._table)
rows = gmPG.run_ro_query ('personalia', cmd, None, self['id'])
if rows is None:
return []
return [{'origin':row[0], 'comment':row[1],
'external_id':row[2]} for row in rows]
#----------------------------------------------------------------
def set_ext_id (self, fk_origin, ext_id, comment=None):
"""
@param fk_origin the origin type ID as returned by
GetExternalIDs
@param ext_id the external ID, a free string as far as GNUMed
is concerned.[1]
Set ext_id to None to delete an external id
@param comment distinguishes several IDs of one origin
<b>[1]</b> But beware, language extension packs are entitled to
add backend triggers to check for validity of external IDs.
"""
to_commit = []
if comment:
cmd = """delete from lnk_%s2ext_id where
id_%s = %%s and fk_origin = %%s
and comment = %%s""" % (self._table, self._table)
to_commit.append ((cmd, [self.__cache['id'], fk_origin,
comment]))
else:
cmd = """delete from lnk_%s2ext_id where
id_%s = %%s and fk_origin = %%s""" % (self._table,
self._table)
to_commit.append ((cmd, [self.__cache['id'],
fk_origin]))
if ext_id:
cmd = """insert into lnk_%s2ext_id (id_%s, fk_origin,
comment)
values (%%s, %%s, %%s)""" % (self._table, self._table)
to_commit.append ((cmd, [self.__cache['id'], ext_id,
comment]))
return gmPG.run_commit2 ('personalia', to_commit)
#-------------------------------------------------------
def delete_ext_id (self, fk_origin, comment=None):
self.set_ext_id (fk_origin, None, comment)
#-------------------------------------------------------
def get_comms (self):
"""A list of ways to communicate"""
cmd = """select
ect.descripton,
lcc.url,
lcc.is_confidential
from
lnk_%s_comm_channel lcc,
enum_comm_types ect
where
lcc.id_%s = %%s and
lcc.id_type = ect.id
""" (self._table, self._table)
rows, idx = gmPG.run_ro_query('personalia', cmd, 1, self['id'])
if rows is None:
return []
elif len(rows) == 0:
return []
else:
return [{'url':i[1],'type':i[0], 'is_confidential':[2]}
for i in rows]
#--------------------------------------------------------------
def set_comm (self, id_type, url, is_confidential):
"""
@param id_type is the ID of the comms type from
getCommChannelTypes
"""
cmd1 = """delete from lnk_%s2comm_channel where
id_%s = %%s and url = %%s""" % (self._table, self._table)
cmd2 = """insert into lnk_%s2ext_id (id_%s, id_type, url,
is_confidential)
values (%%s, %%s, %%s, %%s)""" % (self._table, self._table)
return gmPG.run_commit2 ('personalia', [(cm1, [self['id'],
url]), (cm2, [self['id'], id_type, url, is_confidential])])
#-------------------------------------------------------
def delete_comm (self, url):
cmd = """delete from lnk_%s2comm_channel where
id_%s = %%s and url = %%s""" % (self._table, self._table)
return gmPG.run_commit2 ('personalia', [(cmd, [self['id'],
url])])
#==============================================================================
class cIdentity (cOrg):
_table = "identity"
_cmd_fetch_payload = "select * from v_basic_person where i_id=%s"
_cmds_lock_rows_for_update = ["select 1 from identity where id=%(id)s
and xmin_identity = %(xmin_identity)"]
_cmds_store_payload = ["""
update identity set title=%(title)s, dob=%(dob)s, cob=%(cob)s,
gender=%(gender)s,
fk_marital_status = %(fk_marital_status)s, karyotype = %(karyotype)s,
pupic = %(pupic)s where id=%(id)s"""]
_updateable_fields = ["title", "dob", "cob", "gender",
"fk_marital_status", "karyotype", "pupic"]
#--------------------------------------------------------
def get_all_names(self):
cmd = """
select n.firstnames, n.lastnames, i.title
from names n, identity i
where n.id_identity=%s and i.id=%s"""
rows, idx = gmPG.run_ro_query('personalia', cmd, 1,
self._cache['id'], self.__cache['id'])
if rows is None:
return None
if len(rows) == 0:
return [{'first': '**?**', 'last': '**?**', 'title':
'**?**'}]
else:
names = []
for row in rows:
names.append({'first': row[0], 'last': row[1],
'title': row[2]})
return names
#--------------------------------------------------------
def get_description (self):
"""
Again, allow code reuse where we don't care whther we are
talking to a person
or organisation"""
return _("%(title)s %(firstnames)s %(lastnames)s") % self
#--------------------------------------------------------
def add_name(self, firstnames, lastnames, active=True):
"""Add a name """
cmd = "select add_name(%s, %s, %s, %s)"
if active:
# junk the cache appropriately
if self._ext_cache.has_key ('description'):
del self._ext_cache['description']
self._payload[self._idx['firstnames']] = firstnames
self._payload[self._idx['lastnames']] = lastnames
if self._ext_cache['all_names']:
del self._ext_cache['all_names']
active = (active and 't') or 'f'
return gmPG.run_commit2 ('personalia', [(cmd, [self['id'],
firstnames, lastnames, active])])
#------------------------------------------------------------
def add_address (self, address):
"""
Binds an address to this person
"""
cmd = "insert into lnk_person_org_address ((select id from
address_types where type = %s), id_address, id_identity) values (%(type)s,
create_address (%(number)s, %(addendum)s, %(street)s, %(city)s, %(postcode)s),
%(i_id)s)"
address["i_id"] = self['id']
return gmPG.run_commit2 ('personalia', [(cmd, [address])])
#---------------------------------------------------------------------
def get_occupation (self):
cmd = "select o.name from occupation o, lnk_job2person lj2p
where o.id = lj2p.id_occupation and lj2p.id_identity = %s"
data = gmPG.run_ro_query ('personalia', cmd, None, self.ID)
return data and [i[0] for i in data]
#--------------------------------------------------------------------
def add_occupation (self, occupation):
# does occupation already exist ? -> backend can sort this out
cmd = "insert into lnk_job2person (id_identity, id_occupation)
values (%s, create_occupation (%s))"
if self._ext_cache.has_key ('occupation'):
self._ext_cache['occupation'].append (occupation)
return gmPG.run_commit2 ('personalia', [(cmd, [self['id'],
occupation])])
#----------------------------------------------------------------------
def delete_occupation (self, occupation):
if self._ext_cache.has_key ('occupation'):
del self._ext_cache[self._ext_cache.index (occupation)]
cmd = """
delete from
lnk_job2person
where
id_identity = %s and id_occupation = (select id from occupation
where name = %s)"""
return gmPG.run_commit2 ('personalia', [(cmd, [self['id'],
occupation])])
#----------------------------------------------------------------------
def get_relatives(self):
cmd = """
select
t.description, v.id as id, title, firstnames, lastnames, dob, cob,
gender, karyotype, pupic, fk_marital_status,
marital_status, xmin_identity, preferred
from
v_basic_person v, relation_types t, lnk_person2relative l
where
(l.id_identity = %s and
v.id = l.id_relative and
t.id = l.id_relation_type) or
(l.id_relation = %s and
v.id = i.id_identity and
t.inverse = l.id_relation_type)
"""
data, idx = gmPG.run_ro_query('personalia', cmd, 1,
[self['id'], self['id']])
if data is None:
return []
if len(data) == 0:
return []
return [(r[0], cIdentity (row = {'data':r[1:], 'idx':idx,
'pk_field':'id'})) for r in data ]
#--------------------------------------------------------
def set_relative(self, rel_type, relation):
"""
@param rel_type the relationship type, None to delete
relationships
@type rel_type string
@param relation the cIdentity of the relation
"""
cmd1 = """delete from lnk_person2relative where
(id_identity = %s and id_relative = %s) or (id_identity = %s
and id_relative = %s)"""
# and link the two
cmd2 = """
insert into lnk_person2relative (
id_identity, id_relative, id_relation_type
) values (
%s, %s, (select id from relation_types where
description = %s)
)"""
if self._ext_cache.has_key ('relatives'):
del self._ext_cache['relatives']
if rel_type:
return gmPG.run_commit2 ('personalia', [(cmd1,
[self['id'], relation['id'], relation['id'], aelf['id']]),
(cmd2,
[relation['id'], self['id'], rel_type])])
else:
return gmPG.run_commit2 ('personalia', [(cmd1,
[self['id'], relation['id'], relation['id'], aelf['id']])])
#----------------------------------------------------------------------
def delete_relative(self, relation):
self.set_relative (None, relation)
#----------------------------------------------------------------------
def get_medical_age(self):
dob = self['dob']
if dob is None:
return '??'
return dob2medical_age(dob)
#----------------------------------------------------------------------
#================================================================
# convenience functions
#================================================================
def dob2medical_age(dob):
"""format patient age in a hopefully meaningful way"""
age = mxDT.Age(mxDT.now(), dob)
if age.years > 0:
return "%sy%sm" % (age.years, age.months)
weeks = age.days / 7
if weeks > 4:
return "%sm%sw" % (age.months, age.weeks)
if weeks > 1:
return "%sd" % age.days
if age.days > 1:
return "%sd (%sh)" % (age.days, age.hours)
if age.hours > 3:
return "%sh" % age.hours
if age.hours > 0:
return "%sh%sm" % (age.hours, age.minutes)
if age.minutes > 5:
return "%sm" % (age.minutes)
return "%sm%ss" % (age.minutes, age.seconds)
#----------------------------------------------------------------
def getAddressTypes():
"""Gets a dictionary mapping address type names to the ID"""
row_list = gmPG.run_ro_query('personalia', "select name, id from
address_type")
if row_list is None:
return {}
if len(row_list) == 0:
return {}
return dict (row_list)
#----------------------------------------------------------------
def getMaritalStatusTypes():
"""Gets a dictionary matching marital status types to their internal
ID"""
row_list = gmPG.run_ro_query('personalia', "select name, pk from
marital_status")
if row_list is None:
return {}
if len(row_list) == 0:
return {}
return dict (row_list)
#------------------------------------------------------------------
def getExtIDTypes (context = 'p'):
"""Gets dictionary mapping ext ID names to internal code from the
backend for the given context
"""
# FIXME: error handling
rl = gmPG.run_ro_query('personalia', "select name, pk from
enum_ext_id_types where context = %s", None, context)
if rl is None:
return {}
return dict (rl)
#----------------------------------------------------------------
def getCommChannelTypes():
"""Gets the dictionary of comm channel types to internal ID"""
row_list = gmPG.run_ro_query('personalia', "select description, id from
enum_comm_types")
if row_list is None:
return None
if len (row_list) == 0:
return None
return dict(row_list)
EMAIL=1
FAX=2
HOME_PHONE=3
WORK_PHONE=4
MOBILE=5
WEB=6
JABBER=7
#----------------------------------------------------------------
def guess_state_country( urb, postcode):
"""parameters are urb.name, urb.postcode;
outputs are urb.id_state, country.code"""
cmd = """
select state.code, state.country
from urb, state
where
lower(urb.name) = lower(%s) and
upper(urb.postcode) = upper(%s) and
urb.id_state = state.id
"""
data = gmPG.run_ro_query ('personalia', cmd, None, urb, postcode)
if data is None:
return "", ""
if len(data) == 0:
return '', ''
return data[0]
#----------------------------------------------------------------
def getPostcodeForUrbId(urb_id):
# FIXME: get from views
cmd = "select postcode from urb where id = %s"
data = gmPG.run_ro_query ('personalia', cmd, None, urb_id)
if data is None:
_log.Log(gmLog.lErr, 'cannot get postcode for urb [%s]' %
urb_id)
return None
if len(data) == 0:
return ''
return data[0][0]
#----------------------------------------------------------------
def getUrbsForPostcode(pcode=None):
cmd = "select name from urb where postcode=%s"
data = gmPG.run_ro_query('personalia', cmd, None, pcode)
if data is None:
_log.Log(gmLog.lErr, 'cannot get urbs from postcode [%s]' %
pcode)
return None
if len(data) == 0:
return []
return [x[0] for x in data]
#----------------------------------------------------------------
class PostcodeMP (gmMatchProvider.cMatchProvider_SQL):
"""Returns a list of valid postcodes,
Accepts two contexts : "urb" and "street" being the **IDs** of urb and
street
"""
def __init__ (self):
# we search two tables here, as in some jurisdictions (UK,
Germany, US)
# postcodes are tied to streets or small groups of streets,
# and in others (Australia) postcodes refer to an entire town
# reviewers' comments:
# - pk this will be the data return to the id_callback()
function passed
# as gmPhrasewheel.__init__ last parameter , so the event
data will be
# the postcode for urb or street , not the id of those tables.
# This is in the cMatchProvider.__findMatches code.
source = [{
'column':'postcode',
'pk':'postcode',
'limit':10,
'table':'urb',
'extra conditions':{'urb':'id = %s',
'default':'postcode is not null'}
, 'service': 'personalia'
},{
'column':'postcode',
'table':'street',
'limit':10,
'pk':'postcode',
'extra conditions':{'urb':'id_urb = %s', 'street': 'id
= %s', 'default':'postcode is not null'}
, 'service': 'personalia'
}]
gmMatchProvider.cMatchProvider_SQL.__init__(self, source)
#----------------------------------------------------------------
class StreetMP (gmMatchProvider.cMatchProvider_SQL):
"""Returns a list of streets
accepts "urb" and "postcode" contexts
e.g.
using cMatchProvider_SQL's self.setContext("urb",...)
"""
def __init__ (self):
source = [{
'service': 'personlia',
'table': 'street',
'column': 'name',
'limit': 10,
'extra conditions': {
'urb': 'id_urb = %s',
'postcode': 'postcode = %s or postcode is null'
}
}]
gmMatchProvider.cMatchProvider_SQL.__init__(self, source)
#------------------------------------------------------------
class MP_urb_by_zip (gmMatchProvider.cMatchProvider_SQL):
"""Returns a list of urbs
accepts "postcode" context
"""
def __init__ (self):
source = [{
'service': 'personalia',
'table': 'urb',
'column': 'name',
'limit': 15,
'extra conditions': {'postcode': '(postcode = %s or
postcode is null)'}
}]
gmMatchProvider.cMatchProvider_SQL.__init__(self, source)
class CountryMP (gmMatchProvider.cMatchProvider_SQL):
"""
Returns a list of countries
"""
def __init__ (self):
source = [{
'service':'personalia',
'table':'country',
'pk':'code',
'column':'name',
'limit':5
}]
gmMatchProvider.cMatchProvider_SQL.__init__ (self, source)
class OccupationMP (gmMatchProvider.cMatchProvider_SQL):
"""
Returns a list of occupations
"""
def __init__ (self):
source = [{
'service':'personalia',
'table':'occupation',
'pk':'id',
'column':'name',
'limit':7
}]
gmMatchProvider.cMatchProvider_SQL.__init__ (self, source)
class NameMP (gmMatchProvider.cMatchProvider):
"""
List of names
"""
def getMatches (self, fragment):
cmd = "select search_identity (%s)"
data, idx = gmPG.run_ro_query ('personalia', cmd, 1, fragment)
if data is None:
_log.Log(gmLog.lErr, "cannot search for identity")
return None
return [{'data':cIdentity (idx, i), 'label':"%s %s %s" %
(i[idx['title']], i[idx['firstnames']], i[idx['lastnames']])} for i in data]
#------------------------------------------------------------
class OrgCategoryMP (gmMatchProvider.cMatchProvider_SQL):
"""
List of org categories.
"""
def __init__(self):
source = [ {
'service': 'personalia',
'table' : 'org_category',
'pk' : 'id',
'column': 'description',
'result': 'description' ,
'limit' : 5,
}]
gmMatchProvider.cMatchProvider_SQL.__init__(self, source)
#------------------------------------------------------------
#============================================================
# callbacks
#------------------------------------------------------------
def _patient_selected(**kwargs):
print "received patient_selected notification"
print kwargs['kwds']
#============================================================
if __name__ == "__main__":
_log.SetAllLogLevels(gmLog.lData)
gmDispatcher.connect(_patient_selected, gmSignals.patient_selected())
while 1:
pID = raw_input('a patient: ')
if pID == '':
break
try:
myPatient = cIdentity (aPK_obj = pID)
except:
_log.LogException('Unable to set up patient with ID
[%s]' % pID, sys.exc_info())
print "patient", pID, "can not be set up"
continue
print "ID ", myPatient['id']
print "name ", myPatient['description']
print "title ", myPatient['title']
print "dob ", myPatient['dob']
print "med age ", myPatient['medical_age']
for adr in myPatient['addresses']:
print "address ", adr
print "--------------------------------------"
#============================================================
# $Log: gmDemographicRecord.py,v $
# Revision 1.54 2004/08/18 09:05:07 ncq
# - just some cleanup, double-check _ is defined for epydoc
#
# Revision 1.53 2004/07/26 14:34:49 sjtan
#
# numbering correction from labels in gmDemograpics.
#
# Revision 1.52 2004/06/25 12:37:19 ncq
# - eventually fix the import gmI18N issue
#
# Revision 1.51 2004/06/21 16:02:08 ncq
# - cleanup, trying to make epydoc fix do the right thing
#
# Revision 1.50 2004/06/21 14:48:25 sjtan
#
# restored some methods that gmContacts depends on, after they were booted
# out from gmDemographicRecord with no home to go , works again ;
# removed cCatFinder('occupation') instantiating in main module scope
# which was a source of complaint , as it still will lazy load anyway.
#
# Revision 1.49 2004/06/20 15:38:00 ncq
# - remove import gettext/_ = gettext.gettext
# - import gmI18N handles that if __main__
# - else the importer of gmDemographicRecord has
# to handle setting _
# - this is the Right Way as per the docs !
#
# Revision 1.48 2004/06/20 06:49:21 ihaywood
# changes required due to Epydoc's OCD
#
# Revision 1.47 2004/06/17 11:36:12 ihaywood
# Changes to the forms layer.
# Now forms can have arbitrary Python expressions embedded in @..@ markup.
# A proper forms HOWTO will appear in the wiki soon
#
# Revision 1.46 2004/05/30 03:50:41 sjtan
#
# gmContacts can create/update org, one level of sub-org, org persons, sub-org
persons.
# pre-alpha or alpha ? Needs cache tune-up .
#
# Revision 1.45 2004/05/29 12:03:47 sjtan
#
# OrgCategoryMP for gmContact's category field
#
# Revision 1.44 2004/05/28 15:05:10 sjtan
#
# utility functions only called with exactly 2 args in order to fulfill
function intent, but do some checking for invalid args.
#
# Revision 1.43 2004/05/26 12:58:14 ncq
# - cleanup, error handling
#
# Revision 1.42 2004/05/25 16:18:13 sjtan
#
# move methods for postcode -> urb interaction to gmDemographics so gmContacts
can use it.
#
# Revision 1.41 2004/05/25 16:00:34 sjtan
#
# move common urb/postcode collaboration to business class.
#
# Revision 1.40 2004/05/19 11:16:08 sjtan
#
# allow selecting the postcode for restricting the urb's picklist, and resetting
# the postcode for unrestricting the urb picklist.
#
# Revision 1.39 2004/04/15 09:46:56 ncq
# - cleanup, get_lab_data -> get_lab_results
#
# Revision 1.38 2004/04/11 10:15:56 ncq
# - load title in get_names() and use it superceding getFullName
#
# Revision 1.37 2004/04/10 01:48:31 ihaywood
# can generate referral letters, output to xdvi at present
#
# Revision 1.36 2004/04/07 18:43:47 ncq
# - more gender mappings
# - *comm_channel -> comm_chan
#
# Revision 1.35 2004/03/27 04:37:01 ihaywood
# lnk_person2address now lnk_person_org_address
# sundry bugfixes
#
# Revision 1.34 2004/03/25 11:01:45 ncq
# - getActiveName -> get_names(all=false)
# - export_demographics()
#
# Revision 1.33 2004/03/20 19:43:16 ncq
# - do import gmI18N, we need it
# - gm2long_gender_map -> map_gender_gm2long
# - gmDemo* -> cDemo*
#
# Revision 1.32 2004/03/20 17:53:15 ncq
# - cleanup
#
# Revision 1.31 2004/03/15 15:35:45 ncq
# - optimize getCommChannel() a bit
#
# Revision 1.30 2004/03/10 12:56:01 ihaywood
# fixed sudden loss of main.shadow
# more work on referrals,
#
# Revision 1.29 2004/03/10 00:09:51 ncq
# - cleanup imports
#
# Revision 1.28 2004/03/09 07:34:51 ihaywood
# reactivating plugins
#
# Revision 1.27 2004/03/04 10:41:21 ncq
# - comments, cleanup, adapt to minor schema changes
#
# Revision 1.26 2004/03/03 23:53:22 ihaywood
# GUI now supports external IDs,
# Demographics GUI now ALPHA (feature-complete w.r.t. version 1.0)
# but happy to consider cosmetic changes
#
# Revision 1.25 2004/03/03 05:24:01 ihaywood
# patient photograph support
#
# Revision 1.24 2004/03/02 10:21:09 ihaywood
# gmDemographics now supports comm channels, occupation,
# country of birth and martial status
#
# Revision 1.23 2004/02/26 17:19:59 ncq
# - setCommChannel: arg channel -> channel_type
# - setCommChannel: we need to read currval() within
# the same transaction as the insert to get consistent
# results
#
# Revision 1.22 2004/02/26 02:12:00 ihaywood
# comm channel methods
#
# Revision 1.21 2004/02/25 09:46:20 ncq
# - import from pycommon now, not python-common
#
# Revision 1.20 2004/02/18 15:26:39 ncq
# - fix dob2medical_age()
#
# Revision 1.19 2004/02/18 06:36:04 ihaywood
# bugfixes
#
# Revision 1.18 2004/02/17 10:30:14 ncq
# - enhance GetAddresses() to return all types if addr_type is None
#
# Revision 1.17 2004/02/17 04:04:34 ihaywood
# fixed patient creation refeential integrity error
#
# Revision 1.16 2004/02/14 00:37:10 ihaywood
# Bugfixes
# - weeks = days / 7
# - create_new_patient to maintain xlnk_identity in historica
#
# Revision 1.15 2003/12/01 01:01:41 ncq
# - get_medical_age -> dob2medical_age
#
# Revision 1.14 2003/11/30 01:06:21 ncq
# - add/remove_external_id()
#
# Revision 1.13 2003/11/23 23:32:01 ncq
# - some cleanup
# - setTitle now works on identity instead of names
#
# Revision 1.12 2003/11/23 14:02:40 sjtan
#
# by setting active=false first, then updating values, side effects from
triggers can be avoided; see also
# F_active_name trigger function in server sql script,which fires only if
new.active = true .
#
# Revision 1.11 2003/11/22 14:44:17 ncq
# - setTitle now only operates on active names and also doesn't set the name
# to active which would trigger the trigger
#
# Revision 1.10 2003/11/22 14:40:59 ncq
# - setActiveName -> addName
#
# Revision 1.9 2003/11/22 12:53:48 sjtan
#
# modified the setActiveName and setTitle so it works as intended in the face
of insurmountable triggers ;)
#
# Revision 1.8 2003/11/20 07:45:45 ncq
# - update names/identity, not v_basic_person in setTitle et al
#
# Revision 1.7 2003/11/20 02:10:50 sjtan
#
# remove 'self' parameter from functions moved into global module namespace.
#
# Revision 1.6 2003/11/20 01:52:03 ncq
# - actually, make that **?** for good measure
#
# Revision 1.5 2003/11/20 01:50:52 ncq
# - return '?' for missing names in getActiveName()
#
# Revision 1.4 2003/11/18 16:44:24 ncq
# - getAddress -> getAddresses
# - can't verify mxDateTime bug with missing time tuple
# - remove getBirthYear, do getDOB() -> mxDateTime -> format
# - get_relative_list -> get_relatives
# - create_dummy_relative -> link_new_relative
# - cleanup
#
# Revision 1.3 2003/11/17 10:56:34 sjtan
#
# synced and commiting.
#
# Revision 1.2 2003/11/04 10:35:22 ihaywood
# match providers in gmDemographicRecord
#
# Revision 1.1 2003/11/03 23:59:55 ncq
# - renamed to avoid namespace pollution with plugin widget
#
# Revision 1.6 2003/10/31 23:21:20 ncq
# - remove old history
#
# Revision 1.5 2003/10/27 15:52:41 ncq
# - if aFormat is None in getDOB() return datetime object, else return
formatted string
#
# Revision 1.4 2003/10/26 17:35:04 ncq
# - conceptual cleanup
# - IMHO, patient searching and database stub creation is OUTSIDE
# THE SCOPE OF gmPerson and gmDemographicRecord
#
# Revision 1.3 2003/10/26 16:35:03 ncq
# - clean up as discussed with Ian, merge conflict resolution
#
# Revision 1.2 2003/10/26 11:27:10 ihaywood
# gmPatient is now the "patient stub", all demographics stuff in gmDemographics.
#
# Ergregious breakages are fixed, but needs more work
#
# Revision 1.1 2003/10/25 08:48:06 ihaywood
# Split from gmTmpPatient
#
# old gmTmpPatient log
# Revision 1.41 2003/10/19 10:42:57 ihaywood
# extra functions
#
# Revision 1.40 2003/09/24 08:45:40 ihaywood
# NewAddress now functional
#
# Revision 1.39 2003/09/23 19:38:03 ncq
# - cleanup
# - moved GetAddressesType out of patient class - it's a generic function
#
? diff
? gmMeasurements2.sql
? sql.diff
Index: gmDemographics-GIS-views.sql
===================================================================
RCS file:
/cvsroot/gnumed/gnumed/gnumed/server/sql/gmDemographics-GIS-views.sql,v
retrieving revision 1.13
diff -B -u -r1.13 gmDemographics-GIS-views.sql
--- gmDemographics-GIS-views.sql 15 Dec 2004 09:24:49 -0000 1.13
+++ gmDemographics-GIS-views.sql 20 Dec 2004 18:28:52 -0000
@@ -14,8 +14,7 @@
-- ===================================================================
-- if you suffer from performance problems when selecting from this view,
--- implement it as a real table and create rules / triggers for insert,
--- update and delete that will update the underlying tables accordingly
+-- implement it as a real table
create view v_basic_address as
select
adr.id as id,
@@ -25,7 +24,7 @@
urb.name as city,
adr.number as number,
str.name as street,
- adr.addendum as street2
+ adr.addendum as addendum
from
address adr,
state s,
@@ -38,186 +37,67 @@
and
urb.id_state = s.id;
--- added IH 8/3/02
--- insert, delete, and update rules on this table
--- problem: the street table had better contain the street.
--- solution: function to auto-create street records on demand.
-
-create view v_home_address as
-select
- lp2a.id_identity as id,
- s.country as country,
- s.code as state,
- coalesce (str.postcode, urb.postcode) as postcode,
- urb.name as city,
- adr.number as number,
- str.name as street,
- adr.addendum as street2
-from
- address adr,
- state s,
- urb,
- street str,
- lnk_person2address lp2a
-where
- adr.id_street = str.id
- and
- str.id_urb = urb.id
- and
- urb.id_state = s.id
- and
- lp2a.id_address = adr.id
- and
- lp2a.id_type = 1; -- home address
-
-
-- ===================================================================
--- finds the state for a given postcode in a given country
+-- this function guarantees an ID for a given address, it will create
+-- streets and addresses if required. It insists that the urb exists.
+-- it will set street.postcode if urb.postcode is NULL.
\unset ON_ERROR_STOP
-DROP function find_state(text, text);
-DROP function find_street(text, integer);
+DROP function create_address (text, text, text, text, text);
\set ON_ERROR_STOP 1
-create function find_state (text, text) RETURNS text AS '
+create function create_address (text, text, text, text, text) RETURNS text AS '
DECLARE
- pcode ALIAS FOR $1; -- post code
- ccode ALIAS FOR $2; -- country code
- s RECORD;
- retval text := NULL;
+ p_number alias for $1;
+ p_addendum alias for $2;
+ p_street alias for $3;
+ city alias for $4;
+ pcode alias for $5;
+ r_addr address%ROWTYPE;
+ r_urb urb%ROWTYPE;
+ r_street street%ROWTYPE;
+ street_id integer;
+
BEGIN
- SELECT INTO s * FROM state WHERE
- id = (SELECT id_state from urb where postcode like pcode||''%''
limit 1)
- AND
- country=ccode;
- IF FOUND THEN
- retval := s.code;
+ SELECT INTO r_urb * FROM urb WHERE name ilike city;
+ IF NOT FOUND THEN
+ SELECT INTO r_urb * FROM urb WHERE postcode ilike pcode;
+ IF NOT FOUND THEN
+ RAISE EXCEPTION ''No such urb %'', city;
+ END IF;
END IF;
- RETURN retval;
-END;' LANGUAGE 'plpgsql';
-
--- ===================================================================
--- This function returns the id of street, BUT if the street does not
--- exist, it is created.
-create function find_street (text, integer) RETURNS integer AS '
-DECLARE
- s_name ALIAS FOR $1;
- s_id_urb ALIAS FOR $2;
- s RECORD;
-BEGIN
- SELECT INTO s * FROM street WHERE
- name = s_name
- AND
- id_urb = s_id_urb;
- IF FOUND THEN
- RETURN s.id;
+ IF r_urb.postcode IS NULL THEN
+ SELECT INTO r_street * FROM street WHERE
+ name = street
+ AND
+ id_urb = r_urb.id
+ AND
+ postcode = pcode;
ELSE
- INSERT INTO street (id_urb, name) VALUES (s_id_urb, s_name);
- RETURN currval (''street_id_seq'');
+ SELECT INTO r_street * FROM street WHERE
+ name = p_street
+ AND
+ id_urb = r_urb.id;
END IF;
-END;' LANGUAGE 'plpgsql';
-
-
--- This function returns the id of a state, BUT if the state does not
--- exist, it is created.
-\unset ON_ERROR_STOP
-DROP function find_or_create_state(text, text);
-\set ON_ERROR_STOP 1
-create function find_or_create_state(text, text) RETURNS integer AS '
-DECLARE
- s_code ALIAS FOR $1;
- s_country ALIAS FOR $2;
- s RECORD;
-BEGIN
- SELECT INTO s * FROM state
- WHERE code = s_code
- AND country = s_country;
- IF FOUND THEN
- RETURN s.id;
- ELSE
- INSERT INTO state (code, country) VALUES (s_code, s_country);
- RETURN currval (''state_id_seq'');
- END IF;
-END;' LANGUAGE 'plpgsql';
-
-
-
--- This function returns the id of urb, BUT if the urb does not
--- exist, it is created.
-\unset ON_ERROR_STOP
-DROP function find_urb(text, text, text, text);
-\set ON_ERROR_STOP 1
-create function find_urb (text, text, text, text) RETURNS integer AS '
-DECLARE
- u_country ALIAS FOR $1;
- u_state ALIAS FOR $2;
- u_postcode ALIAS FOR $3;
- u_name ALIAS FOR $4;
- u RECORD;
- state_code INTEGER;
-BEGIN
- state_code = find_or_create_state(u_state, u_country);
- SELECT INTO u *
- FROM urb
- WHERE
- id_state = state_code
- AND
- postcode = u_postcode
- AND
- name = u_name;
-
IF FOUND THEN
- RETURN u.id;
+ street_id := r_street.id;
ELSE
- INSERT INTO urb (id_state, postcode, name)
- VALUES (state_code, u_postcode, u_name);
- RETURN currval (''urb_id_seq'');
+ IF r_urb.postcode IS NULL THEN
+ INSERT INTO street (id_urb, name, postcode) VALUES
(r_urb.id, p_street, pcode);
+ ELSE
+ INSERT INTO street (id_urb, name) VALUES (r_urb.id,
p_street);
+ END IF;
+ street_id := currval (''street_id_seq'');
+ INSERT INTO address (number, addendum, id_street) VALUES
(p_number, p_addendum, street_id);
+ RETURN currval (''address_id_seq'');
END IF;
-END;' language 'plpgsql';
-
--- ====================================================
-\unset ON_ERROR_STOP
-drop rule insert_address on v_basic_address;
-drop rule delete_address on v_basic_address;
-drop rule update_address on v_basic_address;
-\set ON_ERROR_STOP 1
-
-CREATE RULE insert_address AS ON INSERT TO v_basic_address DO INSTEAD
- INSERT INTO address (id_street, number, addendum)
- VALUES (
- find_street(
- NEW.street,
- find_urb(NEW.country, NEW.state, NEW.postcode, NEW.city)
- ),
- NEW.number,
- NEW.street2
- );
-
-CREATE RULE delete_address AS ON DELETE TO v_basic_address DO INSTEAD
- DELETE FROM address
- WHERE address.id = OLD.id
-;
-
--- updates the basic address data as identified by it's unique id
-CREATE RULE update_address AS ON UPDATE TO v_basic_address DO INSTEAD
- UPDATE
- address
- SET
- number = NEW.number,
- addendum = NEW.street2,
- id_street = find_street (
- NEW.street,
- (SELECT urb.id
- FROM urb, state
- WHERE
- urb.name = NEW.city AND
- urb.postcode = NEW.postcode AND
- urb.id_state = state.id AND
- state.code = NEW.state AND
- state.country = NEW.country
- )
- )
- WHERE
- address.id=OLD.id
-;
+ SELECT INTO r_addr * FROM address WHERE
+ number = p_number AND addendum = p_addendum AND id_street =
street_id;
+ IF FOUND THEN
+ RETURN r_addr.id;
+ ELSE
+ INSERT INTO address (number, addendum, id_street) VALUES
(p_number, p_addendum, street_id);
+ RETURN currval (''address_id_seq'');
+ END IF;
+END;' LANGUAGE 'plpgsql';
-- ===================================================================
\unset ON_ERROR_STOP
@@ -357,7 +237,6 @@
GRANT select ON
v_basic_address,
- v_home_address,
v_zip2street,
v_zip2urb,
v_zip2data
Index: gmDemographics-Grants.sql
===================================================================
RCS file: /cvsroot/gnumed/gnumed/gnumed/server/sql/gmDemographics-Grants.sql,v
retrieving revision 1.5
diff -B -u -r1.5 gmDemographics-Grants.sql
--- gmDemographics-Grants.sql 20 Jul 2004 07:12:16 -0000 1.5
+++ gmDemographics-Grants.sql 20 Dec 2004 18:28:52 -0000
@@ -27,8 +27,6 @@
address_type,
address_type_id_seq,
enum_comm_types,
- comm_channel,
- comm_channel_id_seq,
enum_ext_id_types,
lnk_identity2ext_id,
lnk_identity2ext_id_id_seq,
@@ -39,8 +37,8 @@
address_info_id_seq,
lnk_person_org_address,
lnk_person_org_address_id_seq,
- lnk_identity2comm_chan,
- lnk_identity2comm_chan_id_seq,
+ lnk_identity2comm_channel,
+ lnk_identity2comm_channel_id_seq,
relation_types,
lnk_person2relative,
lnk_person2relative_id_seq,
Index: gmDemographics-Org-views.sql
===================================================================
RCS file:
/cvsroot/gnumed/gnumed/gnumed/server/sql/gmDemographics-Org-views.sql,v
retrieving revision 1.1
diff -B -u -r1.1 gmDemographics-Org-views.sql
--- gmDemographics-Org-views.sql 5 Aug 2003 09:25:45 -0000 1.1
+++ gmDemographics-Org-views.sql 20 Dec 2004 18:28:52 -0000
@@ -1,2 +1,2 @@
--v_org
---v_org_AU
+--_org_AU
Index: gmDemographics-Person-data.sql
===================================================================
RCS file:
/cvsroot/gnumed/gnumed/gnumed/server/sql/gmDemographics-Person-data.sql,v
retrieving revision 1.3
diff -B -u -r1.3 gmDemographics-Person-data.sql
--- gmDemographics-Person-data.sql 15 Dec 2004 09:25:53 -0000 1.3
+++ gmDemographics-Person-data.sql 20 Dec 2004 18:28:52 -0000
@@ -12,16 +12,23 @@
-- ================================================
-- please do NOT alter the sequence !!
-insert into relation_types(biological, description) values(true,
i18n('parent'));
-insert into relation_types(biological, description) values(true,
i18n('sibling'));
-insert into relation_types(biological, description) values(true,
i18n('halfsibling'));
-insert into relation_types(biological, description) values(false,
i18n('stepparent'));
-insert into relation_types(biological, description) values(false,
i18n('married'));
-insert into relation_types(biological, description) values(false, i18n('de
facto'));
-insert into relation_types(biological, description) values(false,
i18n('divorced'));
-insert into relation_types(biological, description) values(false,
i18n('separated'));
-insert into relation_types(biological, description) values(false, i18n('legal
guardian'));
-
+BEGIN;
+insert into relation_types(biological, description, inverse) values(true,
i18n('parent'), NULL);
+insert into relation_types(biological, description, inverse) values(true,
i18n('child'), 1);
+update relation_types set inverse=2 where id=1;
+insert into relation_types(biological, description, inverse) values(true,
i18n('sibling'), 3);
+insert into relation_types(biological, description, inverse) values(true,
i18n('halfsibling'), 4);
+insert into relation_types(biological, description, inverse) values(false,
i18n('stepparent'), NULL);
+insert into relation_types(biological, description, inverse) values(false,
i18n('stepchild'), 5);
+update relation_types set inverse=6 where id=5;
+insert into relation_types(biological, description, inverse) values(false,
i18n('married'), 7);
+insert into relation_types(biological, description, inverse) values(false,
i18n('de facto'), 8);
+insert into relation_types(biological, description, inverse) values(false,
i18n('divorced'), 9);
+insert into relation_types(biological, description, inverse) values(false,
i18n('separated'), 10);
+insert into relation_types(biological, description, inverse) values(false,
i18n('legal guardian'), NULL);
+insert into relation_types(biological, description, inverse) values(false,
i18n('ward'), 11);
+update relation_types set inverse=12 where id=11;
+COMMIT;
insert into marital_status(name) values (i18n ('unknown'));
insert into marital_status(name) values (i18n ('single'));
Index: gmDemographics-Person-views.sql
===================================================================
RCS file:
/cvsroot/gnumed/gnumed/gnumed/server/sql/gmDemographics-Person-views.sql,v
retrieving revision 1.25
diff -B -u -r1.25 gmDemographics-Person-views.sql
--- gmDemographics-Person-views.sql 15 Dec 2004 09:30:48 -0000 1.25
+++ gmDemographics-Person-views.sql 20 Dec 2004 18:28:52 -0000
@@ -133,6 +133,26 @@
END;' language 'plpgsql';
-- ==========================================================
+
+\unset ON_ERROR_STOP
+drop function create_occupation (text);
+\set ON_ERROR_STOP 1
+
+CREATE FUNCTION create_occupation (text) RETURNS integer AS '
+DECLARE
+ occ_name alias for $1;
+ occ_id integer;
+ n_rec RECORD;
+BEGIN
+ select into n_rec * from occupation where name = occ_name;
+ if FOUND then
+ return n_rec.id;
+ else
+ insert into occupation (name) values (occ_name);
+ return currval (''occupation_id_seq'');
+ end if;
+END;' LANGUAGE 'plpgsql';
+
\unset ON_ERROR_STOP
drop function new_pupic();
\set ON_ERROR_STOP 1
@@ -161,8 +181,11 @@
i.cob as cob,
i.gender as gender,
i.karyotype as karyotype,
+ i.pupic as pupic,
ms.name as marital_status,
- n.preferred as preferred
+ fk_marital_status as fk_marital_status,
+ n.preferred as preferred,
+ i.xmin as xmin_identity
from
identity i,
names n,
@@ -250,52 +273,38 @@
FROM lnk_person_org_address;
-- ==========================================================
-\unset ON_ERROR_STOP
-drop view v_person_comms cascade;
-drop view v_person_comms; -- cascade doesn't work on 7.1
+\unset ON_ERROR_STOP -- cascade doesn't work on 7.1
drop view v_person_comms_flat cascade;
drop view v_person_comms_flat;
\set ON_ERROR_STOP 1
-create view v_person_comms as
-select *
-from
- (select i.id as pk_identity, ect.id as pk_comm_type
- from (enum_comm_types ect cross join identity i)
- where exists( select id from lnk_identity2comm_chan l where
l.id_identity=i.id)
- ) as ic
- left join
- (select li2cc.id_identity as pk_identity, cc.url as url, cc.id_type as
pk_comm_type
- from lnk_identity2comm_chan li2cc, comm_channel cc
- where li2cc.id_comm = cc.id) as l_comm
- using (pk_identity, pk_comm_type) ;
-
create view v_person_comms_flat as
-select distinct on (pk_identity)
- v1.pk_identity as pk_identity,
+select distinct on (id_identity)
+ v1.id_identity as id_identity,
v1.url as email,
v2.url as fax,
v3.url as homephone,
v4.url as workphone,
v5.url as mobile
from
- v_person_comms v1,
- v_person_comms v2,
- v_person_comms v3,
- v_person_comms v4,
- v_person_comms v5
+ lnk_identity2comm_channel v1,
+ lnk_identity2comm_channel v2,
+ lnk_identity2comm_channel v3,
+ lnk_identity2comm_channel v4,
+ lnk_identity2comm_channel v5
where
- v1.pk_identity = v2.pk_identity
- and v2.pk_identity = v3.pk_identity
- and v3.pk_identity = v4.pk_identity
- and v4.pk_identity = v5.pk_identity
- and v1.pk_comm_type = 1
- and v2.pk_comm_type = 2
- and v3.pk_comm_type = 3
- and v4.pk_comm_type = 4
- and v5.pk_comm_type = 5 ;
+ v1.id_identity = v2.id_identity
+ and v2.id_identity = v3.id_identity
+ and v3.id_identity = v4.id_identity
+ and v4.id_identity = v5.id_identity
+ and v1.id_type = 1
+ and v2.id_type = 2
+ and v3.id_type = 3
+ and v4.id_type = 4
+ and v5.id_type = 5 ;
+-- =========================================================
-- ==========================================================
\unset ON_ERROR_STOP
@@ -312,8 +321,6 @@
v_staff
, lnk_person2address
, lnk_org2address
- , v_person_comms
- , v_person_comms_flat
TO GROUP "gm-doctors";
GRANT SELECT, INSERT, UPDATE, DELETE ON
Index: gmDemographics.sql
===================================================================
RCS file: /cvsroot/gnumed/gnumed/gnumed/server/sql/gmDemographics.sql,v
retrieving revision 1.37
diff -B -u -r1.37 gmDemographics.sql
--- gmDemographics.sql 15 Dec 2004 09:33:16 -0000 1.37
+++ gmDemographics.sql 20 Dec 2004 18:28:52 -0000
@@ -172,23 +172,6 @@
description text unique not null
);
-create table comm_channel (
- id serial primary key,
- id_type integer not null
- references enum_comm_types(id)
- on update cascade
- on delete restrict,
- url text not null,
- unique(id_type, url)
-);
-
-comment on table comm_channel is
- 'stores reachability information';
-comment on column comm_channel.id_type is
- 'the id specifying the type of communication channel e.g. phone, email
address, pager number, etc.';
-comment on column comm_channel.url is
- 'the connection detail i.e. the phone number, email address, pager
number, etc.';
-
-- ===================================================================
-- the following table still needs a lot of work.
@@ -335,7 +318,7 @@
references identity(id)
on update cascade
on delete cascade,
- active boolean default false,
+ active boolean default true,
lastnames text not null,
firstnames text not null,
preferred text,
@@ -354,18 +337,16 @@
'preferred first name, the name a person is usually called (nickname)';
-- ==========================================================
-create table lnk_identity2comm_chan (
+create table lnk_identity2comm_channel (
id serial primary key,
id_identity integer not null
references identity(id)
on update cascade
on delete cascade,
- id_comm integer not null
- references comm_channel(id)
- on update cascade
- on delete cascade,
+ url text,
+ id_type integer references enum_comm_types,
is_confidential bool not null default false,
- unique (id_identity, id_comm)
+ unique (id_identity, url)
);
-- ==========================================================
@@ -378,6 +359,7 @@
create table relation_types (
id serial primary key,
+ inverse integer references relation_types (id),
biological boolean not null,
biol_verified boolean default false,
description text
@@ -526,12 +508,10 @@
references org(id)
on update cascade
on delete cascade,
- id_comm integer not null
- references comm_channel(id)
- on update cascade
- on delete cascade,
+ url text,
+ id_type integer references enum_comm_types (id),
is_confidential bool not null default false,
- unique (id_org, id_comm)
+ unique (id_org, url)
);
-- =====================================================================
@@ -555,7 +535,6 @@
id_type integer references address_type (id) default 1,
address_source text,
id_org integer references org (id),
- id_occupation integer references occupation (id),
unique(id_identity, id_address),
unique(id_org, id_address),
unique(id_identity, id_org, id_occupation)
signature.asc
Description: OpenPGP digital signature
- [Gnumed-devel] gmDemographicRecord.py re-re-factored,
Ian Haywood <=