
/*
 __________ap__
 | id         |__
 | bssid      |  \
 |            |   \   ___________link
 |____________|    \  | float level |
                    \_| fk_ap       |
 __________loc_     / | fk_loc      |
 | id         |    /  |_____________|
 | location   |   /
 |            |__/
 |____________|
*/

CREATE TABLE ap (id INTEGER AUTOINCREMENT PRIMARY KEY, bssid VARCHAR);
CREATE TABLE loc (id INTEGER AUTOINCREMENT PRIMARY KEY, name VARCHAR);
CREATE TABLE linkLocAp (lvl FLOAT, id INTEGER AUTOINCREMENT,
                        fk_ap INT, fk_loc INT, 
                        FOREIGN KEY (fk_ap) REFERENCES ap(id),
                        FOREIGN KEY (fk_loc) REFERENCES loc(id));

CREATE VIEW linkView AS 
SELECT ap.bssid, loc.name, linkLocAp.lvl
FROM ap, loc, linkLocAp
WHERE linkLocAp.fk_ap=ap.id AND
      linkLocAp.fk_loc=loc.id;


/*test*/
insert into ap (bssid) values('mama');
insert into ap (bssid) values('papa');
insert into ap (bssid) values('colis');

insert into loc (name) values('ici');
insert into loc (name) values('labas');
insert into loc (name) values('kekpart');

insert into linkLocAp(lvl,fk_ap,fk_loc) values
      (2, (select id from ap where bssid='mama'),
          (select id from loc where name='ici'));
insert into linkLocAp(lvl,fk_ap,fk_loc) values
      (4, (select id from ap where bssid='mama'),
          (select id from loc where name='labas'));
insert into linkLocAp(lvl,fk_ap,fk_loc) values
      (6, (select id from ap where bssid='papa'),
          (select id from loc where name='kekpart'));
insert into linkLocAp(lvl,fk_ap,fk_loc) values
      (7, (select id from ap where bssid='papa'),
          (select id from loc where name='labas'));
insert into linkLocAp(lvl,fk_ap,fk_loc) values
      (0.5,(select id from ap where bssid='colis'),
          (select id from loc where name='kekpart')) ;

