This section is written primarily for developers. FreeDiams is programmed to manage multiple therapeutic databases. To add a data source, you just need to create a database data meeting the criteria described below with SQLite.
All drugs databases must use the above schema for their filename:
- No specific suffixe
- 'drugs' filename forbidden
- Mandatory extension : db
- Example : drugs-usa.db
The database should contain several tables linked together through primary keys.
There is one for each drug unique identifier number (UID). This unique identifier number may have a special name (CIS for France, PZN Germany...). This designation can be completed in the table INFORMATIONS.
The various tables are:
- DRUGS: contains the UID, the name of the drug and information on marketing;
- COMPOSITION: Contains a description of various molecules to the drug
- PACKAGING: contains the format of treatment (packaging)
So far, FreeDiams does not take into account the table PACKAGING.
Schema :
- UID : Unique Identifier for Drug, typically per-country
- NAME : Commercial, "brand" name of the drug
- FORM : Form of the drug (e.g. tablet, capsule, powder, solution, cream, soap bar)
- ROUTE : route of administration (e.g. oral, intravenous, ophthalmic, rectal, topical)
- ATC : drug's Anatomical Therapeutic Chemical classification system code
- GLOBAL_STRENGTH : contains the globale strenght of the drug. Only used for the drugs name construction. See also: INFORMATIONS.DRUGS_NAME_CONSTRUCTOR.
- TYPE_MP (optional) : marketing permission (AMM in French, Schedule in CA)
- AUTHORIZATION (optional) : type of authorisation
- MARKETED : Boolean :: Yes = 1 ; No = 0
- LINK_SPC (optional) : internet link to a "summary of product characteristics"
A drug can contain multiple molecules. One molecule can be present in a specific form and be metabilized into another (see NATURE / LK_NATURE).
Schema:
- UID : UID of the drug
- MOLECULE_FORM : form of the drug (redundant with DRUGS.FORM)
- MOLECULE_CODE : unique identifier of the molecule
- MOLECULE_NAME : name of the molecule
- DOSAGE : molecule amount (per amount of form) e.g. "325 mg"
- DOSAGE_REF : reference amount of form e.g. "1 tablet", "2 mL suspension"
- NATURE : must be one of two kinds :
- SA (can be used as default value) : name of Active substance
- FT : name of Tranformed substance
- LK_NATURE :
This table is optional.
Schema :
- UID : UID of the Drug
- PACKAGE_UID : UID of the packaging
- LABEL : label of the packaging
- STATUS :
- Active packaging -> A
- Inactive packaging -> B
- MARKETING :
- Marketing cancelled -> A
- Unknown Marketing date -> N
- Commercialized -> C
- Suspended Marketing -> S
- DATE : most recent commercial update
- OPTIONAL_CODE : can complete the MAIN_CODE
Schema :
- VERSION : Version of the db (please be read the page : Understand application version number). See this as a primary key. FreeDiams will read the first selected row of the database as database informations.
- NAME : Name of the database to display to the user. Translations are supported using syntax 'two-character ISO language code=name', unquoted, without any egal sign in the name and with line breaks delimiting multiple languages (see below).
fr=French name
en=English name
sp=Spanish
de=Deutsch
xx=All other languages
- IDENTIFIANT : unique identifiant for FreeDiams.
- This identifiant must be unique for all drugs databases provided for FreeDiams
- French AFFSSAPS database = "FR_AFSSAPS"
- FDA database = "USA_FDA";
- Canadian database = "CANADIAN";
- COMPAT_VERSION : FreeDiams application version dependency
- PROVIDER : Provider of the datas (eg FDA)
- WEBLINK : Web site of the provider
- AUTHOR : Author of the database (eg Me)
- LICENSE :
- DATE : Date of release; format MUST BE formatted : yyyy-MM-dd
- DRUG_UID_NAME : Name to use for the drug UID
- PACK_MAIN_CODE_NAME : Name to for the packaging main code
- ATC : boolean ; ATC is available for each drugs in the database
- INTERACTIONS : boolean : manages interactions
- AUTHOR_COMMENTS : place to hold your comment
- LANGUAGE_COUNTRY : language and country specificity. Format = language_country where :
- language is a lowercase, two-letter, ISO 639 language code,
- territory is an uppercase, two-letter, ISO 3166 country code,
- and codeset and modifier are ignored.
- DRUGS_NAME_CONSTRUCTOR : string to use for the drug name construction. Some tokens will be replaced by their values. You can use these tokens :
- NULL value is replaced by "NAME" only
- "NAME" : for the name of the drug ( == DRUGS.NAME)
- "FORM" : for the name of the drug ( == DRUGS.FORM)
- "ROUTE" : for the name of the drug ( == DRUGS.ROUTE)
- "GLOBAL_STRENGTH" : for the name of the drug ( == DRUGS.GLOBAL_STRENGTH)
- Eg : NAME, FORM (GLOBAL_STRENGTH) == AMOXICILLINE, TABLETS (1g)
- Eg : NAME, FORM, ROUTE -- GLOBAL_STRENGTH == AMOXICILLINE, TABLETS, ORAL -- 1g
Till now, interactions' database is french only. May be a translator team can ensure english and deutsch versions.
- IAM_ID : id of the interaction
- ID1 : id of the first interacting INN
- ID2 : id of the second interacting INN
- TYPE : nature of the risk (encoded)
- 0001 = Precaution
- 0010 = Take in account
- 0100 = Inadvisable
- 1000 = contraindication
- TEXT_IAM : Nature of the risk
- TEXT_CAT : What to do in case of an interaction
This part need to be clarified...
Drugs databases are stored inside /Resources/databases/drugs.
Name must formatted as above : drugs-XX.db where XX correspond to the two letters ISO encoded country : fr, us, de, sp...
CREATE TABLE IF NOT EXISTS `DRUGS` (
`UID` int(11) NOT NULL,
`NAME` varchar(1000) NOT NULL,
`FORM` varchar(500),
`ROUTE` varchar(100),
`ATC` varchar(7),
`GLOBAL_STRENGTH` varchar(40),
`TYPE_MP` varchar(1),
`AUTHORIZATION` varchar(1),
`MARKETED` bool NOT NULL DEFAULT 1,
`LINK_SPC` varchar(250)
);
CREATE TABLE IF NOT EXISTS `COMPOSITION` (
`UID` int(10) NOT NULL,
`MOLECULE_FORM` varchar(100),
`MOLECULE_CODE` int(11) NOT NULL,
`MOLECULE_NAME` varchar(200) NOT NULL,
`DOSAGE` varchar(100) NOT NULL,
`DOSAGE_REF` varchar(50) NOT NULL,
`NATURE` varchar(2) NOT NULL DEFAULT "SA",
`LK_NATURE` int(11) NOT NULL DEFAULT 1
);
CREATE TABLE IF NOT EXISTS `PACKAGING` (
`UID` int(11) NOT NULL,
`PACKAGE_UID` int(20) NOT NULL,
`LABEL` varchar(500) NOT NULL,
`STATUS` varchar(1),
`MARKETING` int(1) NOT NULL DEFAULT 1,
`DATE` varchar(25),
`OPTIONAL_CODE` int(20)
);
CREATE TABLE IF NOT EXISTS `INFORMATIONS` (
`VERSION` varchar(10),
`NAME` varchar(2000),
`IDENTIFIANT` varchar(50),
`COMPAT_VERSION` varchar(10),
`PROVIDER` varchar(200),
`WEBLINK` varchar(500),
`AUTHOR` varchar(200),
`LICENSE` varchar(1000),
`LICENSE_TERMS` varchar(10000),
`DATE` varchar(20),
`DRUG_UID_NAME` varchar(50),
`PACK_MAIN_CODE_NAME` varchar(50),
`ATC` bool NOT NULL,
`INTERACTIONS` bool NOT NULL DEFAULT FALSE,
`AUTHOR_COMMENTS` varchar(10000),
`LANGUAGE_COUNTRY` varchar(5),
`DRUGS_NAME_CONSTRUCTOR` varchar(200)
);
CREATE TABLE IF NOT EXISTS `IAM_DENOMINATION` (
`ID_DENOMINATION` int(10) NOT NULL ,
`DENOMINATION` varchar( 200 ) NOT NULL
);
CREATE TABLE IF NOT EXISTS `IAM_IMPORT` (
`IAM_ID` INTEGER PRIMARY KEY,
`ID1` int(10) NOT NULL ,
`ID2` int(10) NOT NULL ,
`TYPE` INT UNSIGNED NOT NULL ,
`TEXT_IAM` VARCHAR( 2000 ) NULL ,
`TEXT_CAT` VARCHAR( 2000 ) NULL
);
CREATE TABLE IF NOT EXISTS `ATC` (
`ID` INTEGER PRIMARY KEY,
`CODE` VARCHAR(7) NULL ,
`ENGLISH` VARCHAR(127) NULL ,
`FRENCH` VARCHAR(127) NULL
);
User manual - FreeDiams