Xataface Forums

Hi there!

I have a strange problem. I have two three tables defined like this:

Code: Select all

CREATE TABLE IF NOT EXISTS `komputery` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Oddzial` int(11) DEFAULT NULL,
  `Dzial` int(11) DEFAULT NULL,
  `Lokalizacja` int(11) DEFAULT NULL,
  `Typ` enum('Stacjonarny','Laptop') COLLATE utf8_polish_ci DEFAULT NULL,
  `Nazwa` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Model` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Procesor` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Ram` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Dysk` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Grafika` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Naped` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `NumerInwentarzowy` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `PN` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `SN` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Dostawca` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `fk_Komputery_Oddzial1_idx` (`Oddzial`),
  KEY `fk_Komputery_Dzial1_idx` (`Dzial`),
  KEY `fk_Komputery_Lokalizacja1_idx` (`Lokalizacja`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=3 ;

Code: Select all

CREATE TABLE IF NOT EXISTS `komputery_oprogramowanie` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `KomputerId` int(11) DEFAULT NULL,
  `OprogramowanieId` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `OprogramowanieId` (`OprogramowanieId`),
  KEY `fk_Komputery_Oprogramowanie_Komputery1_idx` (`KomputerId`),
  KEY `fk_Komputery_Oprogramowanie_Oprogramowanie1_idx` (`OprogramowanieId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=3 ;

--
-- Ograniczenia dla zrzutów tabel
--

--
-- Ograniczenia dla tabeli `komputery_oprogramowanie`
--
ALTER TABLE `komputery_oprogramowanie`
  ADD CONSTRAINT `fk_Komputery_Oprogramowanie_Komputery1` FOREIGN KEY (`KomputerId`) REFERENCES `komputery` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_Komputery_Oprogramowanie_Oprogramowanie1` FOREIGN KEY (`OprogramowanieId`) REFERENCES `oprogramowanie` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Code: Select all

CREATE TABLE IF NOT EXISTS `oprogramowanie` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Nazwa` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Rodzaj` int(11) DEFAULT NULL,
  `Typ` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Bity` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Klucz` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `Cal` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  `PN` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `fk_Oprogramowanie_Oprogramowanie_Rodzaj1_idx` (`Rodzaj`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=3 ;

--
-- Ograniczenia dla zrzutów tabel
--

--
-- Ograniczenia dla tabeli `oprogramowanie`
--
ALTER TABLE `oprogramowanie`
  ADD CONSTRAINT `fk_Oprogramowanie_Oprogramowanie_Rodzaj1` FOREIGN KEY (`Rodzaj`) REFERENCES `oprogramowanie_rodzaj` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Then in Komputery folder I have a relationship.ini file:

Code: Select all

[Oprogramowanie]
   Komputery_Oprogramowanie.KomputerId="$Id"
   Komputery_Oprogramowanie.OprogramowanieId=Oprogramowanie.Id
   action:label="Oprogramowanie"
   vocabulary:existing="Komp_Opro"



And in Oprogramowanie folder i have a valuelist.ini file:

Code: Select all

[Komputer]
   __sql__ = "SELECT Id,Nazwa from Komputery ORDER BY Nazwa"

[Rodzaj]
   __sql__ = "select Id, Rodzaj from Oprogramowanie_Rodzaj"
   
[Komp_Opro]
   __sql__ = "SELECT a.Id,a.Nazwa from Oprogramowanie a WHERE (SELECT COUNT('x') FROM Komputery_Oprogramowanie b WHERE a.Id=b.OprogramowanieId)=0 ORDER BY a.Nazwa"


Vocabulary is designed in such a way to prevent from adding Oprogramowanie record to more than one Komputery record. All seems to work at first glance.

Problem is that although vocabulary returns correct data (lets say Oprogramowanie with id 2) it tries to add to komputery record a Oprogramowanie record with Id 1 which was previously inserted in another record. I looked into source of page and I can clearly see id 2 there. Why it tries to add id 1 instead.

I haven't had a chance to test this but have added an issue to the issue tracker.
http://bugs.weblite.ca/view.php?id=1195

Are you saying that the record that you select in the add existing record form is being ignored, and another record is added to the relationship instead?

Exactly.

Lets say I have:
Computer(Komputer) with id 1.
Software (Oprogramowanie) with name for ex. first and id 1
Software (Oprogramowanie) with name for ex. second and id 2
Nothing is added so far to Computer.
Valuelist shows both Software records and when I add software with id 1 to computer then all is fine.
When I try to add second software to computer then valuelist shows correctly list with only one existing software
with name second (which is correct). Also in source I can see that this reaming software has a id 2 (also correct).
But when I try to actually add it then nothing happens. Nothing new is added and software with id 2 is still available.
Funny thing is that I do get message that record is added successfully.

I hope that everything above is still clear

Regards and big thanks !!
Gwynnbleid1

Yes. This definitely sounds like a bug. It is possible that it is tripping on the fact that the name of the id field is same for each table... but it *should* be able to still work despite that. I will need to test this out on my end, but don't know when I'll have a chance to do that.

shannah wrote:Yes. This definitely sounds like a bug. It is possible that it is tripping on the fact that the name of the id field is same for each table... but it *should* be able to still work despite that. I will need to test this out on my end, but don't know when I'll have a chance to do that.

I will try with diffrent id then and post the results. Thanks for looking into this

I just confirmed that problem lies(as you thought) in Id column names being identical. Changing those solved problem, but bug remains.

I forgot to ask. What version of Xataface are you using?

Xataface 2.0alpha1