Khaos :: KhACL

[eluser]adamp1[/eluser]
I have a change I think which would be better. Basically it upgrades the database scheme. I have used InnoDB tables instead due to the ability to have foreign keys. I have also looked at your code which you used to perform the selects and added more INDEX's and UNQIUE INDEX's to speed up the fetching of data.

Code:

--
-- Table structure for table `khacl_axos`
--

CREATE TABLE IF NOT EXISTS `khacl_axos` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(254) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `khacl_aros`
--

CREATE TABLE IF NOT EXISTS `khacl_aros` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `lft` int(10) unsigned NOT NULL default '0',
  `rgt` int(10) unsigned NOT NULL default '0',
  `name` varchar(254) NOT NULL,
  `link` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `lft` (`lft`),
  KEY `rgt` (`rgt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `khacl_access`
--

CREATE TABLE IF NOT EXISTS `khacl_access` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `aro_id` int(10) unsigned NOT NULL default '0',
  `aco_id` int(10) unsigned NOT NULL default '0',
  `allow` char(1) default NULL,
  PRIMARY KEY  (`id`),
  KEY `aro_id` (`aro_id`),
  KEY `aco_id` (`aco_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `khacl_access_actions`
--

CREATE TABLE IF NOT EXISTS `khacl_access_actions` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `access_id` int(10) unsigned NOT NULL default '0',
  `axo_id` int(10) unsigned NOT NULL default '0',
  `allow` char(1) default NULL,
  PRIMARY KEY  (`id`),
  KEY `access_id` (`access_id`),
  KEY `axo_id` (`axo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `khacl_acos`
--

CREATE TABLE IF NOT EXISTS `khacl_acos` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `lft` int(10) unsigned NOT NULL default '0',
  `rgt` int(10) unsigned NOT NULL default '0',
  `name` varchar(254) NOT NULL,
  `link` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `lft` (`lft`),
  KEY `rgt` (`rgt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `khacl_access`
--
ALTER TABLE `khacl_access`
  ADD CONSTRAINT `khacl_access_ibfk_2` FOREIGN KEY (`aco_id`) REFERENCES `khacl_acos` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `khacl_access_ibfk_1` FOREIGN KEY (`aro_id`) REFERENCES `khacl_aros` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `khacl_access_actions`
--
ALTER TABLE `khacl_access_actions`
  ADD CONSTRAINT `khacl_access_actions_ibfk_2` FOREIGN KEY (`axo_id`) REFERENCES `khacl_axos` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `khacl_access_actions_ibfk_1` FOREIGN KEY (`access_id`) REFERENCES `khacl_access` (`id`) ON DELETE CASCADE;

Another thing I have done is changed the varchar lengths from 255 to 254 since some server software doesn't support 255 chars, I suppose I just do it to try and make my database design as easy for someone to use elsewhere.

I don't know if using the InnoDB would be hugely slower, but since when you do a check() you pull all the records and loop through iteratively I don't think it will be too much slower.

EDIT: One more thing, using InnoDB tables will also mean that some of the DELETE queries can be removed, making the library simpler, and of course can use transactions.