adding a related record adds a spurious related record

19 views
Skip to first unread message

airdrummer

unread,
Mar 11, 2021, 8:46:31 AMMar 11
to Xataface
after i installed your fix for mobile view instead of add record, i have been getting spurious related records added when adding a different related record:-\ it's probably related (sorry;-) to my abuse of views;-}

i have a view of properties, with a relationship to the lotOwners table:

[lotOwnership]
lotOwnership.lotNum = "$lotNum"

and a relationship to the transactions table:

[transactions]
__sql__ = "SELECT * FROM transactions WHERE AccountID = '$lotNum' and date >= '$dateAquired' order by transactions.Date"

adding a transaction also results in a record being added to the lotOwners table:-\
my daily backup shows:

INSERT INTO `transactions` VALUES (110,'2021-03-10 20:19:39',135.00,2,4242);
INSERT INTO `transactions` VALUES (23,'2021-03-10 20:34:35',138.07,2,4243);
INSERT INTO `dataface__mtimes` VALUES ('transactions',1615408475);
and
INSERT INTO `lotOwnership` VALUES (110,0,'0000-00-00');
INSERT INTO `lotOwnership` VALUES (23,0,'0000-00-00');
INSERT INTO `dataface__mtimes` VALUES ('lotOwnership',1615408475);

glad i enabled history;-) this isn't a showstopper, as the spurious records have null ownerID and dateAquired...both are specified as not null in the d/b, so i guess xataface casts nulls to zeroes...and thus don't interfere with my business logic, but still...

Steve Hannah

unread,
Mar 11, 2021, 11:54:08 AMMar 11
to xata...@googlegroups.com
That's odd.  I don't see anywhere in the transactions relationship definition where it references the lotOwnership table.  What is the "source" table of the transactions relationship?

--
You received this message because you are subscribed to the Google Groups "Xataface" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xataface+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/f827813c-7a95-49db-9425-3dd9a064f244n%40googlegroups.com.


--
Steve Hannah
Web Lite Solutions Corp.

airdrummer

unread,
Mar 11, 2021, 12:28:37 PMMar 11
to Xataface
CREATE TABLE  transactions (
    AccountID  INT NOT NULL,
    Date  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, # DATE NOT NULL,
    Amount  DECIMAL(10,2) NOT NULL,
    transactionType  INT NOT NULL,
    transID  INTEGER  NOT NULL PRIMARY KEY AUTO_INCREMENT,
    INDEX (AccountID),
    INDEX (transactionType),
    FOREIGN KEY (AccountID)
      REFERENCES lots(lotNum)
      ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (transactionType)
      REFERENCES transactionTypes(TransactionType)
      ON UPDATE CASCADE ON DELETE RESTRICT
);

i've probably over-constrained...

Steve Hannah

unread,
Mar 11, 2021, 12:45:05 PMMar 11
to xata...@googlegroups.com
Could be one of those "ON UPDATE CASCADE" rules.

airdrummer

unread,
Mar 11, 2021, 5:45:48 PMMar 11
to Xataface
i'll try removing them...

airdrummer

unread,
Mar 11, 2021, 5:58:08 PMMar 11
to Xataface
just did a dump w/meta, guess that on update stuff (from my original sql) has gotten stripped in the several migrations i've done:-}

CREATE TABLE `transactions` (
  `AccountID` int(11) DEFAULT NULL,
  `Date` timestamp NOT NULL DEFAULT current_timestamp(),
  `Amount` decimal(10,2) NOT NULL,
  `transactionType` int(11) DEFAULT NULL,
  `transID` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `transactions` (`AccountID`, `Date`, `Amount`, `transactionType`, `transID`) VALUES
(74, '2013-06-01 04:00:00', -7.50, 3, 16),
...

ALTER TABLE `transactions`
  ADD PRIMARY KEY (`transID`),
  ADD KEY `AccountID` (`AccountID`),
  ADD KEY `transactionType` (`transactionType`);
ALTER TABLE `transactions`
  MODIFY `transID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4244;

so not that...

airdrummer

unread,
Mar 11, 2021, 6:08:59 PMMar 11
to Xataface
CREATE VIEW `currentLotOwners`  AS
  select `owners`.`ownerID` AS `ownerID`,
           `owners`.`firstNAME` AS `firstNAME`,
           `owners`.`LASTNAME` AS `LASTNAME`,
           `owners`.`role` AS `role`,
           `lots`.`lotNum` AS `lotNum`,
           `lots`.`NUM` AS `num`,
           `streets`.`name` AS `name`,
           `lotOwnership`.`dateAquired` AS `dateAquired`,
           `owners`.`email` AS `email`,
           `owners`.`phone` AS `phone`,
           `owners`.`eDeliveryConsent` AS `eDeliveryConsent`
  from (((`lots`
       join `streets`
          on(`lots`.`streetID` = `streets`.`ID`))
       join `owners`
           on(`owners`.`ownerID` = (
               select `lotOwnership`.`ownerID`
                   from `lotOwnership`
                       where `lotOwnership`.`lotNum` = `lots`.`lotNum`
                        order by `lotOwnership`.`dateAquired` desc limit 1)))
       join `lotOwnership`
           on(`owners`.`ownerID` = `lotOwnership`.`ownerID` and `lotOwnership`.`lotNum` = `lots`.`lotNum`))
       order by `streets`.`name`,`lots`.`NUM` ;

airdrummer

unread,
Mar 11, 2021, 8:30:18 PMMar 11
to Xataface
i can't seemto replicate the problem with debug_sgl:-\ but i did see a problem with the transactions order by:
as i posted above the relationship is:

[transactions]
__sql__ = "SELECT * FROM transactions WHERE AccountID = '$lotNum' and date >= '$dateAquired' order by transactions.Date"

but the sql xataface generates is:

Performing query: 'SELECT COUNT(*) as num from `transactions` where `AccountID` = '141' and `date` >= '2013-12-20' order by `transactions`.`Date` asc'

Performing query: 'select `transactions`.`AccountID`, ifnull(convert_tz(`transactions`.`Date`,'SYSTEM','UTC'), `transactions`.`Date`) as `Date`, `transactions`.`transactionType`, `transactions`.`Amount`, `transactions`.`transID` from `transactions` where `AccountID` = '141' and `date` >= '2013-12-20' order by `transID` asc LIMIT 0,30'

airdrummer

unread,
Mar 12, 2021, 8:48:49 AMMar 12
to Xataface
sorry wrong view...the problem exists in the duesMailing view:

[transactions]
__sql__ = "SELECT * FROM transactions  
 WHERE transactions.AccountID = '$lotNum'
 AND transactions.Date >=
     (SELECT lotOwnership.dateAquired from lotOwnership
        WHERE lotOwnership.lotNum = '$lotNum'                         
             order by dateAquired DESC LIMIT 1)
order by transactions.Date ASC"
action:label = "current ledger"

and it does break my business logic when a related transaction is deleted...the lotownership record is also deleted:-( now _that's_ what i call a showstopper:-(

On Thursday, March 11, 2021 at 11:54:08 AM UTC-5 Steve Hannah wrote:

Steve Hannah

unread,
Mar 12, 2021, 8:54:22 AMMar 12
to xata...@googlegroups.com
Is that a relationship definition?  If so, Xataface just isn't that smart (to handle a nested subquery).  I'm surprised it didn't just crash on the add record.


--
You received this message because you are subscribed to the Google Groups "Xataface" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xataface+u...@googlegroups.com.

airdrummer

unread,
Mar 12, 2021, 8:59:46 AMMar 12
to Xataface
also hindering  debugging is the lack of sql for anything other than selects...no add/delete:
here is all the output after selecting a transaction to delete:

Are you sure you want to remove the following records from the relationship 'current ledger'?

    Performing query: 'SELECT length(`duesMailing`.`ownerID`) as `__ownerID_length`,`duesMailing`.`ownerID`,length(`duesMailing`.`firstNAME`) as `__firstNAME_length`,`duesMailing`.`firstNAME`,length(`duesMailing`.`LASTNAME`) as `__LASTNAME_length`,`duesMailing`.`LASTNAME`,length(`duesMailing`.`role`) as `__role_length`,`duesMailing`.`role`,length(`duesMailing`.`addr`) as `__addr_length`,`duesMailing`.`addr`,length(`duesMailing`.`city`) as `__city_length`,`duesMailing`.`city`,length(`duesMailing`.`st`) as `__st_length`,`duesMailing`.`st`,length(`duesMailing`.`zip`) as `__zip_length`,`duesMailing`.`zip`,length(`duesMailing`.`email`) as `__email_length`,`duesMailing`.`email`,length(`duesMailing`.`eDeliveryConsent`) as `__eDeliveryConsent_length`,`duesMailing`.`eDeliveryConsent`,length(`duesMailing`.`lotNum`) as `__lotNum_length`,`duesMailing`.`lotNum`,length(`duesMailing`.`NUM`) as `__NUM_length`,`duesMailing`.`NUM`,length(`duesMailing`.`name`) as `__name_length`,`duesMailing`.`name`,length(`duesMailing`.`AccountID`) as `__AccountID_length`,`duesMailing`.`AccountID`,length(`duesMailing`.`balance`) as `__balance_length`,`duesMailing`.`balance` FROM `duesMailing` WHERE `duesMailing`.`AccountID` = '141' LIMIT 1' 
    Performing query: 'SELECT COUNT(*) as num from `transactions` where (`transactions`.`AccountID` = '141' and `transactions`.`Date` >= (select `lotOwnership`.`dateAquired` from `lotOwnership` where `lotOwnership`.`lotNum` = '141' order by `dateAquired` desc limit 1)) and `transID` = '4247' order by `transactions`.`Date` asc' 
    Performing query: 'select `transactions`.`AccountID`, ifnull(convert_tz(`transactions`.`Date`,'SYSTEM','UTC'), `transactions`.`Date`) as `Date`, `transactions`.`transactionType`, `transactions`.`Amount`, `transactions`.`transID` from `transactions` where (`transactions`.`AccountID` = '141' and `transactions`.`Date` >= (select `lotOwnership`.`dateAquired` from `lotOwnership` where `lotOwnership`.`lotNum` = '141' order by `dateAquired` desc limit 1)) and `transID` = '4247' order by `transID` asc LIMIT 0,30' 
    Performing query: 'SELECT * from selectTransType;' 
  • 2: payment
after deletion,   the spurious lotownership record was also deleted

airdrummer

unread,
Mar 12, 2021, 9:00:49 AMMar 12
to Xataface
ok, i'll try to redo my relationship...

airdrummer

unread,
Mar 12, 2021, 9:18:58 AMMar 12
to Xataface
good news! i replaced lotOwnership in the relationship with currentLotOwners (a view which encapsulates the lotownership logic) and everything is shipshape now!-)

on deletion of a related transaction, the deletion goes thru, with this reassuring msg(-:

Errors occurred trying to remove records
Failed to delete record 'Ross 16 Barnswallow Ct' because of an SQL error: '' : 'DELETE FROM `currentLotOwners` WHERE `currentLotOwners`.`lotNum` = '141''

but i do wish debug+sql would output more than selects...

airdrummer

unread,
Mar 12, 2021, 9:44:06 AMMar 12
to Xataface

airdrummer

unread,
Mar 12, 2021, 9:51:36 AMMar 12
to Xataface

all these issues are probably just artifacts of my convoluted views, not anything wrong with xataface...i hacked these together while teaching myself sql, before ever trying xataface...it's a testament to xataface that it works as well as it does given my fuzzing attack;-)
Reply all
Reply to author
Forward
0 new messages