Skip to content

Instantly share code, notes, and snippets.

@romaninsh romaninsh/data.sql
Last active May 23, 2019

Embed
What would you like to do?
Illustration to my article on Medium - Admin system with 4 CRUD columns.
DROP TABLE IF EXISTS `group`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `group` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `group`
--
LOCK TABLES `group` WRITE;
/*!40000 ALTER TABLE `group` DISABLE KEYS */;
INSERT INTO `group` VALUES (1,'runners'),(2,'cyclists'),(3,'swimmers'),(4,'coders'),(5,'divers');
/*!40000 ALTER TABLE `group` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `group_user`
--
DROP TABLE IF EXISTS `group_user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `group_user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`group_id` int(11) unsigned NOT NULL,
`user_id` int(11) NOT NULL,
`is_admin` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_group_user_group_idx` (`group_id`),
KEY `fk_group_user_user1_idx` (`user_id`),
CONSTRAINT `fk_group_user_group` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `fk_group_user_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `group_user`
--
LOCK TABLES `group_user` WRITE;
/*!40000 ALTER TABLE `group_user` DISABLE KEYS */;
INSERT INTO `group_user` VALUES (1,2,7,1),(2,1,6,0),(3,2,6,1),(4,2,9,0),(5,1,9,1);
/*!40000 ALTER TABLE `group_user` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_user_country1_idx` (`country_id`),
CONSTRAINT `fk_user_country1` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user`
--
LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'John54',2),(6,'Steven',2),(7,'Paul',1),(8,'Janis',3),(9,'Семен',5);
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `country`
--
DROP TABLE IF EXISTS `country`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `country` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `country`
--
LOCK TABLES `country` WRITE;
/*!40000 ALTER TABLE `country` DISABLE KEYS */;
INSERT INTO `country` VALUES (1,'UK'),(2,'US'),(3,'Latvia'),(4,'Norway'),(5,'Russia');
/*!40000 ALTER TABLE `country` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
<?php
require 'vendor/autoload.php';
use \atk4\data\Model;
$app = new \atk4\ui\App('Welcome to Agile Toolkit');
$app->initLayout('Centered');
$app->db = \atk4\data\Persistence::connect('mysql://root:root@localhost/test');
class Group extends Model {
public $table = 'group';
function init() {
parent::init();
$this->addField('name');
$this->hasMany('GroupUser', new GroupUser());
$this->setOrder('name');
}
}
class GroupUser extends Model {
public $table = 'group_user';
function init() {
parent::init();
$this->hasOne('group_id', [new Group(), 'ui'=>['visible'=>false]])->addTitle();
$this->hasOne('user_id', [new User(), 'ui'=>['visible'=>false]])->addTitle();
$this->addField('is_admin', ['type' => 'boolean']);
$this->setOrder(['group', 'is_admin desc', 'user']);
}
}
class User extends Model {
public $table = 'user';
function init() {
parent::init();
$this->addField('name');
$this->hasMany('GroupUser', new GroupUser());
$this->hasOne('country_id', [new Country(), 'ui'=>['visible'=>false]])->addTitle();
$this->setOrder('name');
}
}
class Country extends Model {
public $table = 'country';
function init() {
parent::init();
$this->addField('name');
$this->hasMany('User', new User());
$this->setOrder('name');
}
}
$c = $app->add('Columns');
$c->addColumn(3)->add(['CRUD', 'paginator'=>false])->setModel(new Group($app->db));
$c->addColumn(5)->add(['CRUD', 'paginator'=>false])->setModel(new GroupUser($app->db));
$c->addColumn(5)->add(['CRUD', 'paginator'=>false])->setModel(new User($app->db));
$c->addColumn(3)->add(['CRUD', 'paginator'=>false])->setModel(new Country($app->db));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.