

grant select, update on hindi_user to www;



create table hindi_dict (
  hd_deleted       timestamp(0)  not null,
  hd_id            integer       not null,
  hd_word_hindi    varchar(200)  not null,
  hd_type_hindi    integer       not null,
  hd_word_german   varchar(200)  not null,
  hd_word_english  varchar(200),
  hd_comment       varchar(500),
  hd_flags         integer       not null,
  hu_name          varchar(20)   not null,
  constraint hindi_dict_pk primary key ( hd_deleted, hd_id )
);

create unique index hindi_dict_uk on hindi_dict (
    hd_word_hindi, hd_type_hindi, hd_word_german, hd_deleted
);

create index hindi_dict_kidx on hindi_dict ( hd_id, hd_deleted );

grant select, insert, update, delete on hindi_dict to www;



create table hindi_tag (
  ht_id            integer       not null,
  ht_name          varchar(200)  not null,
  constraint hindi_tag_pk primary key ( ht_id )
);

create unique index hindi_tag_uk on hindi_tag (
    ht_name
);

grant select, insert, update, delete on hindi_tag to www;

insert into hindi_tag values ( 1000, 'Farbe' );
insert into hindi_tag values ( 1001, 'Essware' );
insert into hindi_tag values ( 1002, 'Gewuerz' );
insert into hindi_tag values ( 1003, 'Koerperteil' );
insert into hindi_tag values ( 1004, 'Tier' );
insert into hindi_tag values ( 1005, 'Sprache' );
insert into hindi_tag values ( 1006, 'Kleidung' );



create table hindi_dict_tag (
  hd_id            integer       not null,
  ht_id            integer       not null,
  constraint hindi_dict_tag_pk primary key ( hd_id, ht_id )
);

create index hindi_dict_tag_uk on hindi_dict_tag (
    ht_id, hd_id
);

grant select, insert, update, delete on hindi_dict_tag to www;



create table hindi_learn (
  hd_id            integer       not null,
  hu_name          varchar(20)   not null,
  hl_level         integer       not null,
  hl_last_learned  timestamp(0)  not null,
  constraint hindi_learn_pk primary key ( hd_id, hu_name )
);


select
  d.hd_id,
  case when l.hl_level is null then 0 else l.hl_level end as hl_level
from
  hindi_dict d
    left join hindi_learn l on
	  d.hd_id = l.hd_id and
	  l.hu_name = 'mf'
order by
  hl_level
;


select d1.hd_id, d2.hd_id, d1.hd_word_german, d1.hd_type_hindi from
hindi_dict d1, hindi_dict d2 where
d1.hd_word_hindi = d2.hd_word_hindi and
d1.hd_type_hindi = d2.hd_type_hindi and
d1.hd_word_german = d2.hd_word_german and
d1.hd_id != d2.hd_id;

