text:
-- MySQL dump 10.13  Distrib 8.0.26, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: blueolz8_blueocean
-- ------------------------------------------------------
-- Server version	8.0.26

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `childrens_day_wildlife_quiz_2021`
--

DROP TABLE IF EXISTS `childrens_day_wildlife_quiz_2021`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `childrens_day_wildlife_quiz_2021` (
  `c_id` int NOT NULL AUTO_INCREMENT,
  `c_email` varchar(255) NOT NULL,
  `c_score` varchar(255) NOT NULL,
  `c_name` varchar(255) NOT NULL,
  `c_mobile` varchar(255) NOT NULL,
  `c_address` varchar(255) DEFAULT NULL,
  `c_result` int NOT NULL,
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=812 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `childrens_day_wildlife_quiz_2021`
--

LOCK TABLES `childrens_day_wildlife_quiz_2021` WRITE;
/*!40000 ALTER TABLE `childrens_day_wildlife_quiz_2021` DISABLE KEYS */;
INSERT INTO `childrens_day_wildlife_quiz_2021` VALUES (1,'viditsaboo2005@gmail.com','4 / 20','Vidit Saboo','8718035005',NULL,0),(2,'sudhir1mishra@gmail.com','7 / 20','Sudhir Kumar Mishra','9437430812',NULL,0),(3,'aadarshsuryavanshi23@gmail.com','3 / 20','Aadarsh','7999179219',NULL,0),(4,'moulik03839@kvsrojabalpur.in','0 / 20','Moulik Verma','6265199878',NULL,0),(5,'amitav1986@gmail.com','2 / 20','Adi Arnav','7873749256',NULL,0),(6,'uno.shikha@gmail.com','1 / 20','Sarvin Singh','9617774575',NULL,0),(7,'prathamkapale08@gmail.com','11 / 20','Pratham Kapale','8770478248',NULL,0),(8,'s115493b.atharva03847@kvsrojabalpur.com','7 / 20','Atharava Verma','9479474761',NULL,0),(9,'sonalgodha@yahoo.in','2 / 20','Deepal Godha','8370000818',NULL,0),(10,'rashmibhandari.srj@gmail.com','5 / 20','Shubh Bhandari','9424458732',NULL,0),(11,'kajalkri8849@gmail.com','6 / 20','Kajal Kumari','9534129388',NULL,0),(12,'tmyrap111@gmail.com','6 / 20','Yogendra Mimrot','9826615541',NULL,0),(13,'harshchourasiay@gmail.com','3 / 20','Harsh Chourasiya','7067168006',NULL,0),(14,'rishabh25902soni@gmail.com','4 / 20','Rishabh Soni','8517885287',NULL,0),(15,'hemantsoni958@gmail.com','12 / 20','Hemant Soni','9584367698',NULL,0),(16,'amanraikwar817@gmail.com','6 / 20','Aman  Raikwar','9893915410',NULL,0),(17,'ramashankarpsc@gmail.com','3 / 20','Ramashankar Vishwakarma','8823837117',NULL,0),(18,'anushhatodiya@gmail.com','9 / 20','Anush Hatodiya','9039387398',NULL,0),(19,'talk2shalini2016@gmail.com','2 / 20','Dr. Shalini  Maheswari','8791627784',NULL,0),(20,'vasudevashish0@gmail.com','7 / 20','Sakshi Katariya','9179097794',NULL,0),(21,'shahinkhatun6268@gmail.com','5 / 20','Shahin Khatun.','8319720500',NULL,0),(22,'anvi4ani@gmail.com','5 / 20','Anvi Tiwari ','7008155327',NULL,0),(23,'narensingh5151@gmail.com','6 / 20','Narendra Mourya','7805923557',NULL,0),(24,'rakeshroutriku@gmail.com','3 / 20','Rakesh Kumar Rout','8763351171',NULL,0),(25,'twishadash913@gmail.com','7 / 20','Twisha Priyambada ','8249501830',NULL,0),(26,'kartiksolanki809@gmail.com','4 / 20','Kartik Solanki','7970240246',NULL,0),(27,'1poonamjain786@gmail.com','12 / 20','Sparsh Jain','8319768625',NULL,0),(28,'poonamjain786@gmail.com','11 / 20','Shreya Jain ','7000820553',NULL,0),(29,'hradeshtripathi1998@gmail.com','6 / 20','Bajiram Tripathi','6261528243',NULL,0),(30,'ritik2013s@gmail.com','4 / 20','Ritik Kesharwani','8707483717',NULL,0),(31,'anamikasoni1818@gmail.com','7 / 20','Anamika Soni','8839164460',NULL,0),(32,'samandsheba@gmail.com','8 / 20','Stuti Polsan','9340785286',NULL,0),(33,'agrawal.smita.14855@gmail.com','7 / 20','Smita Agrawal','7974822360',NULL,0),(34,'ankeshkumarahirwar61@gmail.com','6 / 20','Ankesh Kumar Ahirwar','9617037886',NULL,0),(35,'satyamnayak570@gmail.com','5 / 20','Satish Nayak','8269386606',NULL,0),(36,'sahanimunna438@gmail.com','4 / 20','Payal Sahani ','6232427688',NULL,0),(37,'ranumishra536@gmail.com','5 / 20','Ranu Mishra','9993510511',NULL,0),(38,'raghuof2007@gmail.com','9 / 20','Raghav Lambole','7972279423',NULL,0),(39,'narenkm3@gmail.com','10 / 20','Narendra Kumar Meshram','9039242168',NULL,0),(40,'sriramasrinivasan@gmail.com','4 / 20','Atheendhra R S','9841977580',NULL,0),(41,'poojabari91@gmail.com','4 / 20','Pooja Bansal','8058562164',NULL,0),(42,'mainamalviya321@gmail.com','12 / 20','Maina Malviya','7974936386',NULL,0),(43,'3528harjeetprakash2122@gmail.com','6 / 20','Harjeet Prakash J','9629587265',NULL,0),(44,'8210sailakshmi2122@gmail.com','5 / 20','G.S.Sailakshmi ','9843010309',NULL,0),(45,'3832@thetvsschool.org','3 / 20','Rajamuniyandi','9344115553',NULL,0),(46,'10209padmadharshini@gmail.com','4 / 20','A.R.Padma Dharshini ','9487260275',NULL,0),(47,'lakshana2122@gmail.com','4 / 20','G.Lakshana','9944276553',NULL,0),(48,'6822goutam2122@gmail.com','5 / 20','H.Goutam','8148421240',NULL,0),(49,'smuthoo@gmail.com','3 / 20','M.Nandhitha','9443772994',NULL,0),(50,'9318suchimita2122@gmail.com','4 / 20','N.Suchimita','+918220533502',NULL,0),(51,'9102angelinesahana2122@gmail.com','5 / 20','Angeline Sahana .R','8940635069',NULL,0),(52,'valuerkannan@yahoo.co.in','6 / 20','K.Meenakshi','9842642283',NULL,0),(53,'9604hemapriyadharsini@gmail.com','8 / 20','B.Hemapriyadharsini','9843613223',NULL,0),(54,'deepthikaabts@gmail.com','8 / 20','Deepthikaa N','8778502387',NULL,0),(55,'dadamanoj101@gmail.com','8 / 20','Manoj Kumar Dwivedi','8982279569',NULL,0),(56,'7409sakthiumadevi2122@gmail.com','3 / 20','S.Sakthiumadevi ','8190001120',NULL,0),(57,'10610savundharya2122@gmail.com','8 / 20','M.S.Savundharya ','6374076772',NULL,0),(58,'9401atsara2122@gmail.com','3 / 20','Atsara . P','9965131705',NULL,0),(59,'1712@thetvsschool.org','3 / 20','G.Rajalakshmi','8884041041',NULL,0),(60,'kayilsrilakshmi2020@gmail.com','2 / 20','D.Kayil Srilakshmi','7305237408',NULL,0),(61,'5714shreeja2122@gmail.com','7 / 20','S.Shreeja','9677651227',NULL,0),(62,'1308mithera2122@gmail.com','7 / 20','R.Mithera','9865855521',NULL,0),(63,'kharekhushi53@gmail.com','2 / 20','Bhumi Khare','9755458203',NULL,0),(64,'nidhishnaren@gmail.com','1 / 20','Nidhish Pandiyan Rs','9842814955',NULL,0),(65,'ilamathiharini@gmail.com','9 / 20','M.Harini','9500850246',NULL,0),(66,'amrithaamritheswari@gmail.com','5 / 20','Amritheswari V','7418983622',NULL,0),(67,'mokanapriyaa.s@gmail.com','5 / 20','Mokana Priyaa S','9790449670',NULL,0),(68,'thejeshwar2005@gmail.com','3 / 20','Thejeshwar.M ','9751117335',NULL,0),(69,'shreyatanishacookies@gmail.comcom','6 / 20','Shreya S','9790265132',NULL,0),(70,'vaaudevanravi@gmail.com','11 / 20','V.Srinithi ','8870785714',NULL,0),(71,'akashbabuselvam@gmail.com','3 / 20','Akash Bs ','9942970410',NULL,0),(72,'simrasayeed12082013@gmail.com','7 / 20','S Simra Sayeed ','9443455882',NULL,0),(73,'saravananaeee@gmail.com','5 / 20','Ragshaan','9003545779',NULL,0),(74,'hemaadvocate.v@gmail.com','10 / 20','Abinayaa Shree.V','7904110060',NULL,0),(75,'visvakvaratharajan@gmail.com','11 / 20','Visvak Rs','9087957967',NULL,0),(76,'subhashreelinu8@gmail.com','6 / 20','Medhir Sarangi','9437889872',NULL,0),(77,'vijima.cbe2009@gmail.com','3 / 20','K.Harishragav ','9790070977',NULL,0),(78,'tarounprathi@gmail.com','6 / 20','Taroun ','8754411345',NULL,0),(79,'1531nimalan2122@gmail.com','7 / 20','S.G.Nimalan','9600700666',NULL,0),(80,'5119badriprasath2122@gmail.com','8 / 20','Badri Prasath. J','09342771660',NULL,0),(81,'sumathikrishnan963@gmail.com','4 / 20','Sahana Krishnan.K','9842917678',NULL,0),(82,'9410rajmeha2122@gmail.com','7 / 20','Raj.Meha','8072492702',NULL,0),(83,'shivaniriyamariraja@gmail.com','4 / 20','M Shivani Riya','9344086348',NULL,0),(84,'7334risikesh2122@gmail.com','6 / 20','V.Risikesh ','9095674443',NULL,0),(85,'9119dharun2122@gmail.com','7 / 20','V.Dharun ','9095674443',NULL,0),(86,'subhikshaarani211@gmail.com','4 / 20','M. Subhikshaa Rani','7010769200',NULL,0),(87,'10505dharanya2122@gmail.com','4 / 20','A.Dharanya','8754035262',NULL,0),(88,'poonkodipoovalingam@gmail.com','5 / 20','P. Pranava Aadhidev','9443959266',NULL,0),(89,'Cibisubha2202@gmail.com','2 / 20','S.Yazhini','8508783158',NULL,0),(90,'kalyansamibavalli@gmail.com','7 / 20','Sandhya.S','+918489986177',NULL,0),(91,'iamaltf@gmail.com','3 / 20','Altaf','8980999395',NULL,0),(92,'5208@thetvsschool.org','4 / 20','V.C.Isha','9976585536',NULL,0),(93,'thaitamilsankar@gmail.com','1 / 20','R.Surya Praba','6380632843',NULL,0),(94,'sundharadhara@gmail.com','2 / 20','Sundharadhara.B','7010486199',NULL,0),(95,'miregeshkirti1977@gmail.com','5 / 20','P.V.Kirthi Darshni','8300017158',NULL,0),(96,'6828padmeshchellappan2122@gmail.com','7 / 20','L.Padmesh Chellappan','7010604542',NULL,0),(97,'kannangeetha1983@gmail.com','9 / 20','N.Udhaya ','9500264087',NULL,0),(98,'udhayasabi0912@gmail.com','9 / 20','R.N.Sabinath','9500271963',NULL,0),(99,'ezhil22004@gmail.com','7 / 20','Shilambu Vendan S ','9791511302',NULL,0),(100,'Shikshaawasthi12@gmail.com','5 / 20','Shiksha Awasthi ','6266503246',NULL,0),(101,'ashruti307@gmail.com','4 / 20','Shruti Awasthi','7225933246',NULL,0),(102,'12436mukundan2122@gmail.com','6 / 20','Mukundan D','7305370810',NULL,0),(103,'rashmibilgaiyannayak@gmai.lcom','4 / 20','Rashmi Nayak','9425034330',NULL,0),(104,'10735sundaramanikandan2122@gmail.com','2 / 20','T. Sundara Manikandan ','9597468538',NULL,0),(105,'lavanyaa.vishwa17@gmail.com','7 / 20','Chiranjeev','8870011707',NULL,0),(106,'senthilselvi8784@gmail.com','9 / 20','Anushya S S','9842108784',NULL,0),(107,'8135sanjit2122@gmail.com','5 / 20','P.Sanjit','9500978314',NULL,0),(108,'juhivarsha116@gmail.com','9 / 20','Juhi Varsha','8825922374',NULL,0),(109,'dhivyaprarthnapv@gmail.com','6 / 20','P V Dhivya','9943978918',NULL,0),(110,'1karthictvstyres@gmail.com','2 / 20','K.Darshan ','9944550307',NULL,0),(111,'deepurai3214@gmail.com','4 / 20','Deepika Rai ','7049466890',NULL,0),(112,'6630pranav.n2122@gmail.com','6 / 20','N.Pranav','7811884242',NULL,0),(113,'nainarkousi@yahoo.com','6 / 20','Ruvethna.N','8122492308',NULL,0),(114,'nathankit34@gmail.com','2 / 20','Ankit Nath','6263894203',NULL,0),(115,'priyakirubakaran55@gmail.com','7 / 20','K.Lakshana','9750578882',NULL,0),(116,'1701aaradhana2122@gmail.com','5 / 20','Aaradhana.R','9566217565',NULL,0),(117,'7533tharunmukesh@gmail.com','4 / 20','B.Tharun Mukesh','8300013498',NULL,0),(118,'5403harini@gmail.com','5 / 20','K.Harini','9843396980',NULL,0),(119,'8606jenyalackshme2122@gmail.com','8 / 20','Jenya Lackshme .A.R','9095035835',NULL,0),(120,'janapoornishree@gmail.com','7 / 20','J Shreenithi Shradha ','9944034785',NULL,0),(121,'raj.priyadharshini@gmail.com','6 / 20','Priyadarshini Rajendran','9486836755',NULL,0),(122,'huneyhetomoneyhe@gmail.com','4 / 20','Rajkumari Dharmendra Sahu','7987138787',NULL,0),(123,'lmnarayanan85@gmail.com','4 / 20','L.Krishaanth Sai','7708502078',NULL,0),(124,'rchandra557r@gmail.com','4 / 20','Ramesh Chandra','7037587278',NULL,0),(126,'drthiludentist@gmail.com','6 / 20','Pragathisree Bt','9600645277',NULL,0),(127,'10533rsankaranarayanan@gmail.com','5 / 20','Sankara Narayanan.R','9487743109',NULL,0),(128,'5201afsheenb@gmail.com','4 / 20','B.Afsheen','9843078658',NULL,0),(129,'vithyagopal20@gmail.com','5 / 20','Keerthiga Shree. G','9597021018',NULL,0),(130,'6426@thetvsschool.org','6 / 20','P.Kaaviyan ','9865085496',NULL,0),(131,'11544nikhilselvamani@gmail.com','8 / 20','Nikhilselvamani ','9944786839',NULL,0),(132,'shanthiomprakash7279@gmail.com','4 / 20','Ambarish Kumar .O','9790197901',NULL,0),(133,'2818vasthra2122@gmail.com','6 / 20','R.Vasthra','6381635127',NULL,0),(134,'2807devavarshini2122@gmail.com','7 / 20','V Deva Varshini','7904545951',NULL,0),(135,'qcteamworks@gmail.com','7 / 20','Tharun Krish Kg','9345831467',NULL,0),(136,'selvishwa26@gmail.com','10 / 20','Vishwa Pooja. V','9500434299',NULL,0),(137,'yuvakanna123@gmail.com','6 / 20','Yuvasri Nagendran','9445274771',NULL,0),(138,'dr.abhipan@gmail.com','6 / 20','Advik Pandey','9893497166',NULL,0),(139,'6723nithishkumar2122@gmail.com','9 / 20','Nithish Kumar.R','9698285686',NULL,0),(140,'jeyakila@gmail.com','8 / 20','A.J.Aaradhana','9751324425',NULL,0),(141,'jeyaprakash.ss@gmail.com','6 / 20','Giftson Vasanth J','9677718229',NULL,0),(142,'bir4aparna@gmail.com','5 / 20','Aparna Aparajita Pani ','8085922860',NULL,0),(143,'4619vibishna2122@gmail.com','6 / 20','S.Vibishna','8870818387',NULL,0),(144,'4123.thetvs.onmicrosoft@gmail.com','3 / 20','B. Hari Kumaran','9487410100',NULL,0),(145,'5225@thetvsschool.org','7 / 20','K.Iyngaran ','9487034169',NULL,0),(146,'7323@thetvsschool.org','4 / 20','B.Dharshan','8300007425',NULL,0),(147,'arpitpanda2012@gmail.com','3 / 20','Sivananda Arpit Panda','9098873033',NULL,0),(148,'chandrasekaran408@gmail.com','3 / 20','S.Sri Harinika','9791656912',NULL,0),(149,'6132jsanjith2122@gmail.com','7 / 20','J.Sanjith','8925140460',NULL,0),(150,'harshilalwani2007@gmail.com','4 / 20','Harshi Lalwani','9131380063',NULL,0),(151,'abiramimeenakshi5@gmail.com','4 / 20','V.Kavya Sri','9566338131',NULL,0),(152,'4313jsadhana2122@gmail.com','4 / 20','Sadhana J','9952243451',NULL,0),(153,'arunkumarjeyakumar@gmail.com','3 / 20','J. Karthik Kumar ','8056719540',NULL,0),(154,'sharalyn2007@gmail.com','11 / 20','Sharalyn Singh','8319282082',NULL,0),(155,'vaishu.rs.cse@gmail.com','4 / 20','S.Sathvik','7826075101',NULL,0),(156,'11141.yuvansundar@gmail.com','10 / 20','Yuvan Sundar S','8667070266',NULL,0),(157,'vallia720@gmail.com','6 / 20','C.K.Vijay','8438439396',NULL,0),(158,'1sasisanjaitpk@gmail.com','3 / 20','Sarveshwar','8667899157',NULL,0),(159,'8534sundaresakumarappan@gmail.com','7 / 20','B. Sundaresa Kumarappan','9487509113',NULL,0),(160,'nancyamulraj@gmail.com','0 / 20','Jessie Valentina A','9994996876',NULL,0),(161,'muthuramsanthoshkumar@gmail.com','4 / 20','Muthuraman K','8489487417',NULL,0),(162,'d.bhaitripathitejas@gmail.com','5 / 20','Tejas Tripathi ','8982101500',NULL,0),(164,'7637vishwanth@gmail.com','1 / 20','S.Vishwanth','8870951717',NULL,0),(165,'abhishekpaliwal009@gmail.com','10 / 20','Abhishek Paliwal','8446402568',NULL,0),(166,'ramashri2006@gmail.com','6 / 20','Ramashri. T','9361330584',NULL,0),(167,'sivaramsundar9@gmail.com','8 / 20','Sivaram Sundar R','6385557997',NULL,0),(168,'s11549a.atharv03903@kvsrojabalpur.in','7 / 20','Atharv Thakur','9407351746',NULL,0),(169,'5529muhammed2122@gmail.com','6 / 20','M. Muhammed Sulthan Ibrahim','9698162971',NULL,0),(170,'arunkesavh275@gmail.com','2 / 20','Arun Kesavh','9080718371',NULL,0),(171,'3829@thetvsschool.org','6 / 20','L. Mithunkarthik','7639839122',NULL,0),(172,'shah.anjal59@gmail.com','5 / 20','Kiyansh Shah','9979885788',NULL,0),(173,'mail2shanmugaraj@gmail.com','2 / 20','S.Kirthiga Sri','6380312317',NULL,0),(174,'7721adityan2122@gmail.com','6 / 20','A.Adityan','8754035262',NULL,0),(175,'dharunbalaji72@gmail.com','8 / 20','P. Dharun Balaji ','9994950799',NULL,0),(176,'7604diya2122@gmail.com','8 / 20','G.Diya','9942129890',NULL,0),(177,'9722hayagreev@gmail.com','0 / 20','Hayagreev R','8610019633',NULL,0),(178,'siva.prasad0220@gmail.com','7 / 20','Siva Prasad C','7339055387',NULL,0),(179,'ssswatisahu@gmail.com','4 / 20','Swati Sahu','6263300340',NULL,0),(180,'ananthm511@gmail.com','0 / 20','A.Dhanushri','9894281433',NULL,0),(181,'karishmika222@gmail.com','5 / 20','Karishmika Collins','7223050626',NULL,0),(182,'1335@thetvsschool.org','2 / 20','V.Prasanna','9626834585',NULL,0),(183,'meensphd@gmail.com','6 / 20','T. Bavishya ','09788559976',NULL,0),(184,'harshitsoni298@gmail.com','2 / 20','Harshit Soni','9755231872',NULL,0),(185,'abis.abi15@gmail.com','5 / 20','Mridulasree K','8300019470',NULL,0),(186,'pitchaimani.yashwanth@gmail.com','6 / 20','P.Yashwanthb','9498179823',NULL,0),(187,'1242joshika2122@gmail.com','3 / 20','Joshika S','9943216848',NULL,0),(188,'shanmugaraj.thetvs@gmail.com','3 / 20','S.Kirthiga Sri','6380312317',NULL,0),(189,'prabhameenacutes@gmail.com','5 / 20','Dhakshitha.L','9600616920',NULL,0),(190,'suryasjegan@gmail.com','5 / 20','Deeksith J','9344481082',NULL,0),(191,'7406nithar2122@gmail.com','6 / 20','D.Nitharshana','8838863556',NULL,0),(192,'harivenkat1310@gmail.com','2 / 20','B. Harish Kirthik','9994644873',NULL,0),(193,'shanmugapriyakarthigeyan@gmail.com','7 / 20','Shanmuga Priya','9443255649',NULL,0),(194,'8136sreenivasaparthasarathy@gmail.com','6 / 20','G.Sreenivasa Parthasarathy ','07871645238',NULL,0),(195,'sarojyogi03@gmail.com','4 / 20','Suman Yogi ','6263109406',NULL,0),(196,'prasannakeertha.pk@gmail.com','0 / 20','P.Aswanth ','8838181420',NULL,0),(197,'harinimdu11@gmail.com','5 / 20','V.Harini','8012277999',NULL,0),(198,'manisharudy2013@gmail.com','6 / 20','Rudra Chaudhary','9833312798',NULL,0),(199,'aditishukla483@gmail.com','4 / 20','Aditya Shukla','7415839659',NULL,0),(200,'5412roshni2122@gmail.com','1 / 20','K.Roshni','90004671',NULL,0),(201,'4809poojithasri@gmail.com','1 / 20','Poojithasri  .S','7538884538',NULL,0),(202,'aarav.jain@avmschools.ac.in','5 / 20','Aarav Jain','9820644628',NULL,0),(203,'ishikaborle@gmail.com','9 / 20','Ishika Borle','9407010907',NULL,0),(204,'rajshri6221@gmail.com','4 / 20','Raj Arvind Shrivas','7999655620',NULL,0),(205,'2724gopinath@gmail.com','4 / 20','S.Gopinath ','9345985115',NULL,0),(206,'kavithavijay07@gmail.com','5 / 20','T.V .Natchathira ','7639826801',NULL,0),(207,'shreya.shukla@avmschools.ac.in','10 / 20','Shreya Shukla ','9889594441',NULL,0),(208,'sasisanjaitpk@gmail.com','7 / 20','Sahana ','9025558936',NULL,0),(209,'11140@thetvsschool.org','4 / 20','Thamizh Vs','7598672363',NULL,0),(210,'dev.mehta@avmschools.ac.in','3 / 20','Dev Mehta','9619851757',NULL,0),(211,'maisha.kushwah@avmschools.ac.in','3 / 20','Maisha Singh Kushwah','9920801577',NULL,0),(212,'4128pranav2122@gmail.com','4 / 20','S. S. S. Pranav','9894199859',NULL,0),(213,'chitra01devi@gmail.com','3 / 20','S.C.Harshini','9942430279',NULL,0),(214,'7825muhammadlafeer@gmail.com','7 / 20','R.Muhammad Lafeer','8300966995',NULL,0),(215,'himanshimajde26@gmail.com','4 / 20','Himanshi Majde','8269887273',NULL,0),(216,'driti.070927@gmail.com','7 / 20','Driti Gundana ','9833512130',NULL,0),(217,'neev.jain@avmschools.ac.in','3 / 20','Neev Rakesh Jain','8108500301',NULL,0),(218,'sukankshamaurya@gmai.com','6 / 20','Sukanksha Maurya','9406987434',NULL,0),(219,'ayan.mohta@avmschools.ac.in','4 / 20','Ayan Mohta','8879622071',NULL,0),(220,'fathimak949@gmail.com','4 / 20','Abdul Khathar I','9788171716',NULL,0),(221,'sweet.harini@gmail.com','8 / 20','Giashri M ','9944385958',NULL,0),(222,'mridul.bihani@avmschools.ac.in','7 / 20','Mridul Bihani','888038600',NULL,0),(223,'kushwaharinku2000@gmail.com','6 / 20','Aakarshit Kushwaha','7828093474',NULL,0),(224,'gjgauravjain3@gmail.com','11 / 20','Gaurav Jain','8463803474',NULL,0),(225,'nagaraj15160@gmail.com','3 / 20','S.N Thirunavukarasu','9976882352',NULL,0),(226,'elkan.george@avmschools.ac.in','6 / 20','Elkan George ','9833198228',NULL,0),(227,'kian.shah@avmschools.ac.in','3 / 20','Kian Ketan Shah','8976817002',NULL,0),(228,'yanjali897@gmail.com','4 / 20','Anjali Yadav','8349943555',NULL,0),(229,'yuvaan.dingreja@avmschools.ac.in','5 / 20','Yuvaan Dingreja ','9930741541',NULL,0),(230,'6105divyaashree2122@gmail.com','8 / 20','S.Divyaashree','8870803903',NULL,0),(231,'mihira.nadkarni@gmail.com','6 / 20','Mihira Unmesh Nadkarni ','9820805197',NULL,0),(232,'ankitamishra090802@gmail.com','6 / 20','Ajeet Kumar Mishra','8305472602',NULL,0),(233,'diptichavda@gmail.com','11 / 20','Viaan Panjwani','9920860235',NULL,0),(234,'vyas19821@gmail.com','7


text:
DROP TABLE IF EXISTS test.stored_aggregates;

CREATE TABLE test.stored_aggregates
(
	d	Date,
	Uniq 		AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree(d, d, 8192);

INSERT INTO test.stored_aggregates
SELECT
	toDate(toUInt16(toDate('2014-06-01')) + intDiv(number, 100)) AS d,
	uniqState(intDiv(number, 10)) AS Uniq
FROM
(
	SELECT * FROM system.numbers LIMIT 1000
)
GROUP BY d;

SELECT uniqMerge(Uniq) FROM test.stored_aggregates;

SELECT d, uniqMerge(Uniq) FROM test.stored_aggregates GROUP BY d ORDER BY d;

INSERT INTO test.stored_aggregates
SELECT
	toDate(toUInt16(toDate('2014-06-01')) + intDiv(number, 100)) AS d,
	uniqState(intDiv(number + 50, 10)) AS Uniq
FROM
(
	SELECT * FROM system.numbers LIMIT 500, 1000
)
GROUP BY d;

SELECT uniqMerge(Uniq) FROM test.stored_aggregates;

SELECT d, uniqMerge(Uniq) FROM test.stored_aggregates GROUP BY d ORDER BY d;

OPTIMIZE TABLE test.stored_aggregates;

SELECT uniqMerge(Uniq) FROM test.stored_aggregates;

SELECT d, uniqMerge(Uniq) FROM test.stored_aggregates GROUP BY d ORDER BY d;

DROP TABLE test.stored_aggregates;

domain: sql


text:
DO
$do$
DECLARE
   arr integer[] := array[2014,2013,2012,2011,2010,2009,2008,2007,2006,2005,2004,2003,2002,2001,2000,1999,1998,1997,1996,1995,1994,1993,1992,1991];
   i integer;
BEGIN
   FOREACH i IN ARRAY arr
   LOOP
    SET work_mem='2GB';
    RAISE NOTICE 'Dropping view for year: %', i;
    EXECUTE 'drop materialized view IF EXISTS  offense_offense_counts_' || i::TEXT || ' CASCADE';
    RAISE NOTICE 'Creating view for year: %', i;
    EXECUTE 'create materialized view offense_offense_counts_' || i::TEXT || ' as select count(offense_id), ori, offense_name,weapon_name, method_entry_code, num_premises_entered,location_name, state_id
    from (
        SELECT DISTINCT(offense_id), ref_agency.ori, offense_name, weapon_name, method_entry_code, num_premises_entered,location_name, nibrs_offense_denorm_' || i::TEXT || '.state_id, year from nibrs_offense_denorm_' || i::TEXT || '
        JOIN ref_agency ON ref_agency.agency_id = nibrs_offense_denorm_' || i::TEXT || '.agency_id
        where year::integer = ' || i || '
        ) as temp
    GROUP BY GROUPING SETS (
        (year, offense_name, weapon_name),
        (year, offense_name, method_entry_code),
        (year, offense_name, num_premises_entered),
        (year, offense_name, location_name),
        (year, state_id, offense_name, weapon_name),
        (year, state_id, offense_name, method_entry_code),
        (year, state_id, offense_name, num_premises_entered),
        (year, state_id, offense_name, location_name),
        (year, ori, offense_name, weapon_name),
        (year, ori, offense_name, method_entry_code),
        (year, ori, offense_name, num_premises_entered),
        (year, ori, offense_name, location_name)
    );';
   END LOOP;
END
$do$;

drop materialized view IF EXISTS  offense_offense_counts_states;
create materialized view offense_offense_counts_states as
    SELECT *,2014 as year FROM offense_offense_counts_2014 WHERE ori IS NULL UNION
    SELECT *,2013 as year  FROM offense_offense_counts_2013 WHERE ori IS NULL UNION
    SELECT *,2012 as year  FROM offense_offense_counts_2012 WHERE ori IS NULL UNION
    SELECT *,2011 as year  FROM offense_offense_counts_2011 WHERE ori IS NULL UNION
    SELECT *,2010 as year  FROM offense_offense_counts_2010 WHERE ori IS NULL UNION
    SELECT *,2009 as year  FROM offense_offense_counts_2009 WHERE ori IS NULL UNION
    SELECT *,2008 as year  FROM offense_offense_counts_2008 WHERE ori IS NULL UNION
    SELECT *,2007 as year  FROM offense_offense_counts_2007 WHERE ori IS NULL UNION
    SELECT *,2006 as year  FROM offense_offense_counts_2006 WHERE ori IS NULL UNION
    SELECT *,2005 as year  FROM offense_offense_counts_2005 WHERE ori IS NULL UNION
    SELECT *,2004 as year  FROM offense_offense_counts_2004 WHERE ori IS NULL UNION
    SELECT *,2003 as year  FROM offense_offense_counts_2003 WHERE ori IS NULL UNION
    SELECT *,2002 as year  FROM offense_offense_counts_2002 WHERE ori IS NULL UNION
    SELECT *,2001 as year  FROM offense_offense_counts_2001 WHERE ori IS NULL UNION
    SELECT *,2000 as year  FROM offense_offense_counts_2000 WHERE ori IS NULL UNION
    SELECT *,1999 as year  FROM offense_offense_counts_1999 WHERE ori IS NULL UNION
    SELECT *,1998 as year  FROM offense_offense_counts_1998 WHERE ori IS NULL UNION
    SELECT *,1997 as year  FROM offense_offense_counts_1997 WHERE ori IS NULL UNION
    SELECT *,1996 as year  FROM offense_offense_counts_1996 WHERE ori IS NULL UNION
    SELECT *,1995 as year  FROM offense_offense_counts_1995 WHERE ori IS NULL UNION
    SELECT *,1994 as year  FROM offense_offense_counts_1994 WHERE ori IS NULL UNION
    SELECT *,1993 as year  FROM offense_offense_counts_1993 WHERE ori IS NULL UNION
    SELECT *,1992 as year  FROM offense_offense_counts_1992 WHERE ori IS NULL UNION
    SELECT *,1991 as year  FROM offense_offense_counts_1991 WHERE ori IS NULL;

drop materialized view IF EXISTS  offense_offense_counts_ori;
create materialized view offense_offense_counts_ori as
    SELECT *,2014 as year FROM offense_offense_counts_2014 WHERE ori IS NOT NULL UNION
    SELECT *,2013 as year  FROM offense_offense_counts_2013 WHERE ori IS NOT NULL UNION
    SELECT *,2012 as year  FROM offense_offense_counts_2012 WHERE ori IS NOT NULL UNION
    SELECT *,2011 as year  FROM offense_offense_counts_2011 WHERE ori IS NOT NULL UNION
    SELECT *,2010 as year  FROM offense_offense_counts_2010 WHERE ori IS NOT NULL UNION
    SELECT *,2009 as year  FROM offense_offense_counts_2009 WHERE ori IS NOT NULL UNION
    SELECT *,2008 as year  FROM offense_offense_counts_2008 WHERE ori IS NOT NULL UNION
    SELECT *,2007 as year  FROM offense_offense_counts_2007 WHERE ori IS NOT NULL UNION
    SELECT *,2006 as year  FROM offense_offense_counts_2006 WHERE ori IS NOT NULL UNION
    SELECT *,2005 as year  FROM offense_offense_counts_2005 WHERE ori IS NOT NULL UNION
    SELECT *,2004 as year  FROM offense_offense_counts_2004 WHERE ori IS NOT NULL UNION
    SELECT *,2003 as year  FROM offense_offense_counts_2003 WHERE ori IS NOT NULL UNION
    SELECT *,2002 as year  FROM offense_offense_counts_2002 WHERE ori IS NOT NULL UNION
    SELECT *,2001 as year  FROM offense_offense_counts_2001 WHERE ori IS NOT NULL UNION
    SELECT *,2000 as year  FROM offense_offense_counts_2000 WHERE ori IS NOT NULL UNION
    SELECT *,1999 as year  FROM offense_offense_counts_1999 WHERE ori IS NOT NULL UNION
    SELECT *,1998 as year  FROM offense_offense_counts_1998 WHERE ori IS NOT NULL UNION
    SELECT *,1997 as year  FROM offense_offense_counts_1997 WHERE ori IS NOT NULL UNION
    SELECT *,1996 as year  FROM offense_offense_counts_1996 WHERE ori IS NOT NULL UNION
    SELECT *,1995 as year  FROM offense_offense_counts_1995 WHERE ori IS NOT NULL UNION
    SELECT *,1994 as year  FROM offense_offense_counts_1994 WHERE ori IS NOT NULL UNION
    SELECT *,1993 as year  FROM offense_offense_counts_1993 WHERE ori IS NOT NULL UNION
    SELECT *,1992 as year  FROM offense_offense_counts_1992 WHERE ori IS NOT NULL UNION
    SELECT *,1991 as year  FROM offense_offense_counts_1991 WHERE ori IS NOT NULL;

DROP INDEX IF EXISTS offense_offense_counts_state_id_idx;
DROP INDEX IF EXISTS offense_offense_counts_ori_idx;
CREATE INDEX offense_offense_counts_state_id_idx ON offense_counts_states (state_id, year, offense_name);
CREATE INDEX offense_offense_counts_ori_idx ON offense_counts_ori (ori, year, offense_name);


text:
-- insert authors
INSERT INTO `author` (`age`, `name`, `genre`, `id`) VALUES (23, "Mark Janel", "Anthology", 1);
INSERT INTO `author` (`age`, `name`, `genre`, `id`) VALUES (43, "Olivia Goy", "Horror", 2);
INSERT INTO `author` (`age`, `name`, `genre`, `id`) VALUES (51, "Quartis Young", "Anthology", 3);
INSERT INTO `author` (`age`, `name`, `genre`, `id`) VALUES (34, "Joana Nimar", "History", 4);

-- insert books
INSERT INTO `book` (`isbn`, `title`, `author_id`, `id`) VALUES ("001-JN", "A History of Ancient Prague", 4, 1);
INSERT INTO `book` (`isbn`, `title`, `author_id`, `id`) VALUES ("002-JN", "A People's History", 4, 2);
INSERT INTO `book` (`isbn`, `title`, `author_id`, `id`) VALUES ("001-MJ", "The Beatles Anthology", 1, 3);
INSERT INTO `book` (`isbn`, `title`, `author_id`, `id`) VALUES ("001-OG", "Carrie", 2, 4);


text:
--liquibase formatted sql
--changeset artemov_i:ATShowScenario.sql dbms:postgresql runOnChange:true splitStatements:false stripComments:false
INSERT INTO s_mt.t_query (ck_id, cc_query, ck_provider, ck_user, ct_change, cr_type, cr_access, cn_action, cv_description) VALUES ('ATShowScenario', '/*ATShowScenario*/

select

  s.ck_id,

  s.cn_order,

  s.cv_name,

  s.cl_valid,

  s.cv_description,

  /* Поля аудита */

  s.ck_user,

  s.ct_change at time zone :sess_cv_timezone as ct_change

from s_mt.t_scenario s

where ( &FILTER )

/*##filter.ck_id*/and s.ck_id = (cast(:json as jsonb)->''filter''->>''ck_id'')::varchar/*filter.ck_id##*/

order by &SORT, s.cn_order asc

  ', 'meta', '20783', '2019-05-22 13:34:05.240505+03', 'select', 'po_session', NULL, 'Необходимо актуализировать')
on conflict (ck_id) do update set cc_query = excluded.cc_query, ck_provider = excluded.ck_provider, ck_user = excluded.ck_user, ct_change = excluded.ct_change, cr_type = excluded.cr_type, cr_access = excluded.cr_access, cn_action = excluded.cn_action, cv_description = excluded.cv_description;


text:
insert into SUPPLIERS values(49,  'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', '95460');
insert into SUPPLIERS values(101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199');
insert into SUPPLIERS values(150, 'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', '93966');
insert into SUPPLIERS values(456, 'Restaurant Supplies, Inc.', '200 Magnolia Street', 'Meadows', 'CA', '93966');
insert into SUPPLIERS values(927, 'Professional Kitchen', '300 Daisy Avenue', 'Groundsville', 'CA', '95199');

insert into COFFEES values('Colombian',          101, 7.99, 0, 0);
insert into COFFEES values('French_Roast',       49, 8.99, 0, 0);
insert into COFFEES values('Espresso',           150, 9.99, 0, 0);
insert into COFFEES values('Colombian_Decaf',    101, 8.99, 0, 0);
insert into COFFEES values('French_Roast_Decaf', 049, 9.99, 0, 0);

insert into COF_INVENTORY values(1234, 'Colombian', 101, 0, '2006-04-01 00:00:00');
insert into COF_INVENTORY values(1234, 'French_Roast', 49, 0, '2006-04-01 00:00:00');
insert into COF_INVENTORY values(1234, 'Espresso', 150, 0, '2006-04-01 00:00:00');
insert into COF_INVENTORY values(1234, 'Colombian_Decaf', 101, 0, '2006-04-01 00:00:00');

insert into MERCH_INVENTORY values(00001234, 'Cup_Large', 456, 28, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00001235, 'Cup_Small', 456, 36, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00001236, 'Saucer', 456, 64, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00001287, 'Carafe', 456, 12, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00006931, 'Carafe', 927, 3, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00006935, 'PotHolder', 927, 88, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00006977, 'Napkin', 927, 108, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00006979, 'Towel', 927, 24, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00004488, 'CofMaker', 456, 5, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00004490, 'CofGrinder', 456, 9, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00004495, 'EspMaker', 456, 4, '2006-04-01 00:00:00');
insert into MERCH_INVENTORY values(00006914, 'Cookbook', 927, 12, '2006-04-01 00:00:00');

insert into COFFEE_HOUSES values(10023, 'Mendocino', 3450, 2005, 5455);
insert into COFFEE_HOUSES values(33002, 'Seattle', 4699, 3109, 7808);
insert into COFFEE_HOUSES values(10040, 'SF', 5386, 2841, 8227);
insert into COFFEE_HOUSES values(32001, 'Portland', 3147, 3579, 6726);
insert into COFFEE_HOUSES values(10042, 'SF', 2863, 1874, 4710);
insert into COFFEE_HOUSES values(10024, 'Sacramento', 1987, 2341, 4328);
insert into COFFEE_HOUSES values(10039, 'Carmel', 2691, 1121, 3812);
insert into COFFEE_HOUSES values(10041, 'LA', 1533, 1007, 2540);
insert into COFFEE_HOUSES values(33005, 'Olympia', 2733, 1550, 4283);
insert into COFFEE_HOUSES values(33010, 'Seattle', 3210, 2177, 5387);
insert into COFFEE_HOUSES values(10035, 'SF', 1922, 1056, 2978);
insert into COFFEE_HOUSES values(10037, 'LA', 2143, 1876, 4019);
insert into COFFEE_HOUSES values(10034, 'San_Jose', 1234, 1032, 2266);
insert into COFFEE_HOUSES values(32004, 'Eugene', 1356, 1112, 2468);


text:
CREATE FUNCTION [dbo].[GetClientCategory]
(
	@ClientName NVARCHAR(128)
)
RETURNS NVARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
	IF @ClientName IS NULL
		RETURN ''

    IF	([dbo].[IsNuGetClient] (@ClientName) = 1)
        RETURN 'NuGet'

		-- WebMatrix
    IF	CHARINDEX('WebMatrix', @ClientName) > 0
        RETURN 'WebMatrix'

	IF	(
			-- NuGet Package Explorer
			CHARINDEX('NuGet Package Explorer Metro', @ClientName) > 0
		OR	CHARINDEX('NuGet Package Explorer', @ClientName) > 0
		)
		RETURN 'NuGet Package Explorer'

	IF	([dbo].[IsScriptClient] (@ClientName) = 1)
		RETURN 'Script'

	IF	([dbo].[IsCrawler] (@ClientName) = 1)
        RETURN 'Crawler'

	IF ([dbo].[IsMobileClient] (@ClientName) = 1)
		RETURN 'Mobile'

	-- Check these late in the process, because other User Agents tend to also send browser strings (e.g. PowerShell sends the Mozilla string along)
    IF ([dbo].[IsBrowser] (@ClientName) = 1)
        RETURN 'Browser'

	-- explicitly categorize unknowns, test frameworks or others that should be filtered out in the reports
	IF ([dbo].[IsUnknownClient] (@ClientName) = 1)
		RETURN 'Unknown'

	-- Return empty for all others to allow ecosystem user agents to be picked up in the reports
    RETURN ''
END


text:
SET @sName = 'bx_timeline';

DROP TABLE IF EXISTS `bx_timeline_events`;
DROP TABLE IF EXISTS `bx_timeline_handlers`;

DROP TABLE IF EXISTS `bx_timeline_photos`;
DROP TABLE IF EXISTS `bx_timeline_photos_processed`;
DROP TABLE IF EXISTS `bx_timeline_photos2events`;

DROP TABLE IF EXISTS `bx_timeline_videos`;
DROP TABLE IF EXISTS `bx_timeline_videos_processed`;
DROP TABLE IF EXISTS `bx_timeline_videos2events`;

DROP TABLE IF EXISTS `bx_timeline_links`;
DROP TABLE IF EXISTS `bx_timeline_links2events`;

DROP TABLE IF EXISTS `bx_timeline_comments`;

DROP TABLE IF EXISTS `bx_timeline_votes`;
DROP TABLE IF EXISTS `bx_timeline_votes_track`;

DROP TABLE IF EXISTS `bx_timeline_meta_keywords`;
DROP TABLE IF EXISTS `bx_timeline_meta_locations`;

DROP TABLE IF EXISTS `bx_timeline_reports`;
DROP TABLE IF EXISTS `bx_timeline_reports_track`;

-- STORAGES, TRANSCODERS, UPLOADERS
DELETE FROM `sys_objects_uploader` WHERE `object` LIKE 'bx_timeline%';
DELETE FROM `sys_objects_storage` WHERE `object`  LIKE 'bx_timeline%';
DELETE FROM `sys_objects_transcoder` WHERE `object`  LIKE 'bx_timeline%';
DELETE FROM `sys_transcoder_filters` WHERE `transcoder_object` LIKE 'bx_timeline%';

-- Forms All
DELETE FROM `sys_form_display_inputs` WHERE `display_name` IN  (SELECT `display_name` FROM `sys_form_displays` WHERE `module` = @sName);
DELETE FROM `sys_form_inputs` WHERE `module` = @sName;
DELETE FROM `sys_form_displays` WHERE `module` = @sName;
DELETE FROM `sys_objects_form` WHERE `module` = @sName;

-- STUDIO PAGE & WIDGET
DELETE FROM `tp`, `tw`, `tpw`
USING `sys_std_pages` AS `tp`, `sys_std_widgets` AS `tw`, `sys_std_pages_widgets` AS `tpw`
WHERE `tp`.`id` = `tw`.`page_id` AND `tw`.`id` = `tpw`.`widget_id` AND `tp`.`name` = @sName;


text:
--- Historical data
SELECT
 to_char("Date",'YYYY') as "Year",
 min(Case When month("Date") = 1 Then "OzoneRateLA" Else null End) as "Jan",
 min(Case When month("Date") = 2 Then "OzoneRateLA" Else null End) as "Feb",
 min(Case When month("Date") = 3 Then "OzoneRateLA" Else null End) as "Mar",
 min(Case When month("Date") = 4 Then "OzoneRateLA" Else null End) as "Apr",
 min(Case When month("Date") = 5 Then "OzoneRateLA" Else null End) as "May",
 min(Case When month("Date") = 6 Then "OzoneRateLA" Else null End) as "Jun",
 min(Case When month("Date") = 7 Then "OzoneRateLA" Else null End) as "Jul",
 min(Case When month("Date") = 8 Then "OzoneRateLA" Else null End) as "Aug",
 min(Case When month("Date") = 9 Then "OzoneRateLA" Else null End) as "Sep",
 min(Case When month("Date") =10 Then "OzoneRateLA" Else null End) as "Oct",
 min(Case When month("Date") =11 Then "OzoneRateLA" Else null End) as "Nov",
 min(Case When month("Date") =12 Then "OzoneRateLA" Else null End) as "Dec"
FROM TS_SORTED
group by to_char("Date",'YYYY')
ORDER BY 1
;

--- Components
SELECT
 KEY as "Component Name",
 CASE WHEN to_char(VALUE) ='' THEN '<none>' ELSE VALUE END as "Component Value"
FROM INDICATORS
WHERE KEY in ('Trend','Cycles','Fluctuations')
;

--- Overview
select
 ltrim(ltrim(key,'Model'),'Time') as key, value
from
 SUMMARY
where
 key in ('ModelVariableCount','ModelSelectedVariableCount','ModelRecordCount','ModelBuildDate') or
 key in ('ModelTimeSeriesFirstDate','ModelTimeSeriesLastDate','ModelTimeSeriesHorizon')
;
---- Descriptive Stats
SELECT
 VARIABLE as "Target",
 KEY as "Statistic",
 round(to_double(VALUE),4) as "Value"
FROM
 INDICATORS
WHERE
 VARIABLE = 'OzoneRateLA' AND
 KEY <> 'CategoryFrequency'
;
---- Outliers
SELECT
A."Date", A."OzoneRateLA" as "Outlier"
FROM
OPERATION_LOG L, TS_SORTED A
WHERE
MESSAGE like '%outlier has been detected at time point (%' AND
to_date(substr(substr_after(L.MESSAGE,'at time point ('),1,10),'YYYY-MM-DD') = A."Date"
;

--- Partitions
With TB as (
 select to_double(value) as TOTAL
 from SUMMARY
 where key = 'ModelRecordCount'
)
select
 ltrim(substr_after(key, 'ModelRecord'),'Count') as "Partition",
 to_double(value) as "Number of rows",
 round(to_double(value) / TB.TOTAL *100, 2) as "In %"
from
 SUMMARY, TB
where
  KEY like 'ModelRecord%' and value <> '0' and key <> 'ModelRecordCount'
order by 1
;

---- ACCURACY
select
 round(avg(Case KEY When 'L1' Then to_number(value) Else Null End),3) as "MAE",
 round(avg(Case KEY When 'MAPE' Then to_number(value) Else Null End) *100,2) as "MAPE",
 round(avg(Case KEY When 'SMAPE' Then to_number(value) Else Null End) *100,2) as "SMAPE",
 round(avg(Case KEY When 'L2' Then to_number(value) Else Null End),3) as "RMSE",
 round(avg(Case KEY When 'R2' Then to_number(value) Else Null End),4) as "R2"
from
 INDICATORS
where
  KEY in ('R2', 'L2', 'SMAPE', 'MAPE','L1');


text:
PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS user;
CREATE TABLE IF NOT EXISTS user (
  id INTEGER PRIMARY KEY ASC,
  username VARCHAR(200) UNIQUE NOT NULL,
  email VARCHAR(300) UNIQUE NOT NULL,
  password VARCHAR(30) NOT NULL,
  nom VARCHAR(60),
  prenom VARCHAR(60),
  date_naiss DATE,
  date_creation REAL DEFAULT (datetime('now', 'localtime')),
  date_connection DATE

);

DROP TABLE IF EXISTS post;
CREATE TABLE IF NOT EXISTS post(
  id INTEGER PRIMARY KEY ASC,
  titre VARCHAR(500),
  text TEXT,
  date_creation REAL DEFAULT (datetime('now', 'localtime')),
  date_publication REAL DEFAULT (datetime('now', 'localtime')),
  date_modification REAL DEFAULT (datetime('now', 'localtime')),
  user_id INTEGER NOT NULL,
  FOREIGN KEY(user_id) REFERENCES user(id)
);

DROP TABLE IF EXISTS commentaire;
CREATE TABLE IF NOT EXISTS commentaire(
  id INTEGER PRIMARY KEY ASC,
  titre VARCHAR(500),
  text TEXT,
  date_creation REAL DEFAULT (datetime('now', 'localtime')),
  date_publication REAL DEFAULT (datetime('now', 'localtime')),
  date_modification REAL DEFAULT (datetime('now', 'localtime')),
  user_id INTEGER NOT NULL,
  post_id INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id),
  FOREIGN KEY (post_id) REFERENCES post(id)
);

DROP TABLE IF EXISTS tag;

CREATE TABLE IF NOT EXISTS tag(
  id INTEGER PRIMARY KEY ASC,
  titre VARCHAR(500)

);

DROP TABLE IF EXISTS post_tag;
CREATE TABLE IF NOT EXISTS post_tag(
  id INTEGER PRIMARY KEY ASC,
  post_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  FOREIGN KEY (post_id) REFERENCES post(id),
  FOREIGN KEY (tag_id) REFERENCES tag(id),
  UNIQUE (post_id, tag_id)
);


text:
-- -*- test-case-name: txdav.caldav.datastore.test.test_sql,txdav.carddav.datastore.test.test_sql -*-

----
-- Copyright (c) 2010-2015 Apple Inc. All rights reserved.
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
----

-----------------
-- Resource ID --
-----------------

create sequence RESOURCE_ID_SEQ;

-------------------------
-- Cluster Bookkeeping --
-------------------------

-- Information about a process connected to this database.

-- Note that this must match the node info schema in twext.enterprise.queue.
create table NODE_INFO (
  HOSTNAME  varchar(255) not null,
  PID       integer      not null,
  PORT      integer      not null,
  TIME      timestamp    not null default timezone('UTC', CURRENT_TIMESTAMP),

  primary key (HOSTNAME, PORT)
);

-- Unique named locks.  This table should always be empty, but rows are
-- temporarily created in order to prevent undesirable concurrency.
create table NAMED_LOCK (
    LOCK_NAME varchar(255) primary key
);

-------------------
-- Calendar Home --
-------------------

create table CALENDAR_HOME (
  RESOURCE_ID      integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  OWNER_UID        varchar(255) not null unique,                                 -- implicit index
  DATAVERSION      integer      default 0 not null
);

--------------
-- Calendar --
--------------

create table CALENDAR (
  RESOURCE_ID integer   primary key default nextval('RESOURCE_ID_SEQ') -- implicit index
);

----------------------------
-- Calendar Home Metadata --
----------------------------

create table CALENDAR_HOME_METADATA (
  RESOURCE_ID              integer     primary key references CALENDAR_HOME on delete cascade, -- implicit index
  QUOTA_USED_BYTES         integer     default 0 not null,
  DEFAULT_EVENTS           integer     default null references CALENDAR on delete set null,
  DEFAULT_TASKS            integer     default null references CALENDAR on delete set null,
  DEFAULT_POLLS            integer     default null references CALENDAR on delete set null,
  ALARM_VEVENT_TIMED       text        default null,
  ALARM_VEVENT_ALLDAY      text        default null,
  ALARM_VTODO_TIMED        text        default null,
  ALARM_VTODO_ALLDAY       text        default null,
  AVAILABILITY             text        default null,
  CREATED                  timestamp   default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                 timestamp   default timezone('UTC', CURRENT_TIMESTAMP)
);

create index CALENDAR_HOME_METADATA_DEFAULT_EVENTS on
	CALENDAR_HOME_METADATA(DEFAULT_EVENTS);
create index CALENDAR_HOME_METADATA_DEFAULT_TASKS on
	CALENDAR_HOME_METADATA(DEFAULT_TASKS);
create index CALENDAR_HOME_METADATA_DEFAULT_POLLS on
	CALENDAR_HOME_METADATA(DEFAULT_POLLS);

-----------------------
-- Calendar Metadata --
-----------------------

create table CALENDAR_METADATA (
  RESOURCE_ID           integer      primary key references CALENDAR on delete cascade, -- implicit index
  SUPPORTED_COMPONENTS  varchar(255) default null,
  CREATED               timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED              timestamp    default timezone('UTC', CURRENT_TIMESTAMP)
);

---------------------------
-- Sharing Notifications --
---------------------------

create table NOTIFICATION_HOME (
  RESOURCE_ID integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  OWNER_UID   varchar(255) not null unique,                                -- implicit index
  DATAVERSION integer      default 0 not null
);

create table NOTIFICATION (
  RESOURCE_ID                   integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  NOTIFICATION_HOME_RESOURCE_ID integer      not null references NOTIFICATION_HOME,
  NOTIFICATION_UID              varchar(255) not null,
  NOTIFICATION_TYPE             varchar(255) not null,
  NOTIFICATION_DATA             text         not null,
  MD5                           char(32)     not null,
  CREATED                       timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                      timestamp    default timezone('UTC', CURRENT_TIMESTAMP),

  unique(NOTIFICATION_UID, NOTIFICATION_HOME_RESOURCE_ID) -- implicit index
);

create index NOTIFICATION_NOTIFICATION_HOME_RESOURCE_ID on
	NOTIFICATION(NOTIFICATION_HOME_RESOURCE_ID);

-------------------
-- Calendar Bind --
-------------------

-- Joins CALENDAR_HOME and CALENDAR

create table CALENDAR_BIND (
  CALENDAR_HOME_RESOURCE_ID integer      not null references CALENDAR_HOME,
  CALENDAR_RESOURCE_ID      integer      not null references CALENDAR on delete cascade,
  CALENDAR_RESOURCE_NAME    varchar(255) not null,
  BIND_MODE                 integer      not null, -- enum CALENDAR_BIND_MODE
  BIND_STATUS               integer      not null, -- enum CALENDAR_BIND_STATUS
  BIND_REVISION				integer      default 0 not null,
  MESSAGE                   text,
  TRANSP                    integer      default 0 not null, -- enum CALENDAR_TRANSP
  ALARM_VEVENT_TIMED        text         default null,
  ALARM_VEVENT_ALLDAY       text         default null,
  ALARM_VTODO_TIMED         text         default null,
  ALARM_VTODO_ALLDAY        text         default null,
  TIMEZONE                  text         default null,

  primary key(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_ID), -- implicit index
  unique(CALENDAR_HOME_RESOURCE_ID, CALENDAR_RESOURCE_NAME)     -- implicit index
);

create index CALENDAR_BIND_RESOURCE_ID on
	CALENDAR_BIND(CALENDAR_RESOURCE_ID);

-- Enumeration of calendar bind modes

create table CALENDAR_BIND_MODE (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_BIND_MODE values (0, 'own'  );
insert into CALENDAR_BIND_MODE values (1, 'read' );
insert into CALENDAR_BIND_MODE values (2, 'write');
insert into CALENDAR_BIND_MODE values (3, 'direct');
insert into CALENDAR_BIND_MODE values (4, 'indirect');

-- Enumeration of statuses

create table CALENDAR_BIND_STATUS (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_BIND_STATUS values (0, 'invited' );
insert into CALENDAR_BIND_STATUS values (1, 'accepted');
insert into CALENDAR_BIND_STATUS values (2, 'declined');
insert into CALENDAR_BIND_STATUS values (3, 'invalid');
insert into CALENDAR_BIND_STATUS values (4, 'deleted');

-- Enumeration of transparency

create table CALENDAR_TRANSP (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_TRANSP values (0, 'opaque' );
insert into CALENDAR_TRANSP values (1, 'transparent');

---------------------
-- Calendar Object --
---------------------

create table CALENDAR_OBJECT (
  RESOURCE_ID          integer      primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  CALENDAR_RESOURCE_ID integer      not null references CALENDAR on delete cascade,
  RESOURCE_NAME        varchar(255) not null,
  ICALENDAR_TEXT       text         not null,
  ICALENDAR_UID        varchar(255) not null,
  ICALENDAR_TYPE       varchar(255) not null,
  ATTACHMENTS_MODE     integer      default 0 not null, -- enum CALENDAR_OBJECT_ATTACHMENTS_MODE
  DROPBOX_ID           varchar(255),
  ORGANIZER            varchar(255),
  RECURRANCE_MIN       date,        -- minimum date that recurrences have been expanded to.
  RECURRANCE_MAX       date,        -- maximum date that recurrences have been expanded to.
  ACCESS               integer      default 0 not null,
  SCHEDULE_OBJECT      boolean      default false,
  SCHEDULE_TAG         varchar(36)  default null,
  SCHEDULE_ETAGS       text         default null,
  PRIVATE_COMMENTS     boolean      default false not null,
  MD5                  char(32)     not null,
  CREATED              timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED             timestamp    default timezone('UTC', CURRENT_TIMESTAMP),

  unique (CALENDAR_RESOURCE_ID, RESOURCE_NAME) -- implicit index

  -- since the 'inbox' is a 'calendar resource' for the purpose of storing
  -- calendar objects, this constraint has to be selectively enforced by the
  -- application layer.

  -- unique(CALENDAR_RESOURCE_ID, ICALENDAR_UID)
);

create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_AND_ICALENDAR_UID on
  CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, ICALENDAR_UID);

create index CALENDAR_OBJECT_CALENDAR_RESOURCE_ID_RECURRANCE_MAX on
  CALENDAR_OBJECT(CALENDAR_RESOURCE_ID, RECURRANCE_MAX);

create index CALENDAR_OBJECT_ICALENDAR_UID on
  CALENDAR_OBJECT(ICALENDAR_UID);

create index CALENDAR_OBJECT_DROPBOX_ID on
  CALENDAR_OBJECT(DROPBOX_ID);

-- Enumeration of attachment modes

create table CALENDAR_OBJECT_ATTACHMENTS_MODE (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (0, 'none' );
insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (1, 'read' );
insert into CALENDAR_OBJECT_ATTACHMENTS_MODE values (2, 'write');

-- Enumeration of calendar access types

create table CALENDAR_ACCESS_TYPE (
  ID          integer     primary key,
  DESCRIPTION varchar(32) not null unique
);

insert into CALENDAR_ACCESS_TYPE values (0, ''             );
insert into CALENDAR_ACCESS_TYPE values (1, 'public'       );
insert into CALENDAR_ACCESS_TYPE values (2, 'private'      );
insert into CALENDAR_ACCESS_TYPE values (3, 'confidential' );
insert into CALENDAR_ACCESS_TYPE values (4, 'restricted'   );

-----------------
-- Instance ID --
-----------------

create sequence INSTANCE_ID_SEQ;

----------------
-- Time Range --
----------------

create table TIME_RANGE (
  INSTANCE_ID                 integer        primary key default nextval('INSTANCE_ID_SEQ'), -- implicit index
  CALENDAR_RESOURCE_ID        integer        not null references CALENDAR on delete cascade,
  CALENDAR_OBJECT_RESOURCE_ID integer        not null references CALENDAR_OBJECT on delete cascade,
  FLOATING                    boolean        not null,
  START_DATE                  timestamp      not null,
  END_DATE                    timestamp      not null,
  FBTYPE                      integer        not null,
  TRANSPARENT                 boolean        not null
);

create index TIME_RANGE_CALENDAR_RESOURCE_ID on
  TIME_RANGE(CALENDAR_RESOURCE_ID);
create index TIME_RANGE_CALENDAR_OBJECT_RESOURCE_ID on
  TIME_RANGE(CALENDAR_OBJECT_RESOURCE_ID);

-- Enumeration of free/busy types

create table FREE_BUSY_TYPE (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into FREE_BUSY_TYPE values (0, 'unknown'         );
insert into FREE_BUSY_TYPE values (1, 'free'            );
insert into FREE_BUSY_TYPE values (2, 'busy'            );
insert into FREE_BUSY_TYPE values (3, 'busy-unavailable');
insert into FREE_BUSY_TYPE values (4, 'busy-tentative'  );

------------------
-- Transparency --
------------------

create table TRANSPARENCY (
  TIME_RANGE_INSTANCE_ID      integer      not null references TIME_RANGE on delete cascade,
  USER_ID                     varchar(255) not null,
  TRANSPARENT                 boolean      not null
);

create index TRANSPARENCY_TIME_RANGE_INSTANCE_ID on
  TRANSPARENCY(TIME_RANGE_INSTANCE_ID);

----------------
-- Attachment --
----------------

create sequence ATTACHMENT_ID_SEQ;

create table ATTACHMENT (
  ATTACHMENT_ID               integer           primary key default nextval('ATTACHMENT_ID_SEQ'), -- implicit index
  CALENDAR_HOME_RESOURCE_ID   integer           not null references CALENDAR_HOME,
  DROPBOX_ID                  varchar(255),
  CONTENT_TYPE                varchar(255)      not null,
  SIZE                        integer           not null,
  MD5                         char(32)          not null,
  CREATED                     timestamp default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                    timestamp default timezone('UTC', CURRENT_TIMESTAMP),
  PATH                        varchar(1024)     not null
);

create index ATTACHMENT_CALENDAR_HOME_RESOURCE_ID on
  ATTACHMENT(CALENDAR_HOME_RESOURCE_ID);

create index ATTACHMENT_DROPBOX_ID on
  ATTACHMENT(DROPBOX_ID);

-- Many-to-many relationship between attachments and calendar objects
create table ATTACHMENT_CALENDAR_OBJECT (
  ATTACHMENT_ID                  integer      not null references ATTACHMENT on delete cascade,
  MANAGED_ID                     varchar(255) not null,
  CALENDAR_OBJECT_RESOURCE_ID    integer      not null references CALENDAR_OBJECT on delete cascade,

  primary key (ATTACHMENT_ID, CALENDAR_OBJECT_RESOURCE_ID), -- implicit index
  unique (MANAGED_ID, CALENDAR_OBJECT_RESOURCE_ID) --implicit index
);

create index ATTACHMENT_CALENDAR_OBJECT_CALENDAR_OBJECT_RESOURCE_ID on
	ATTACHMENT_CALENDAR_OBJECT(CALENDAR_OBJECT_RESOURCE_ID);

-----------------------
-- Resource Property --
-----------------------

create table RESOURCE_PROPERTY (
  RESOURCE_ID integer      not null, -- foreign key: *.RESOURCE_ID
  NAME        varchar(255) not null,
  VALUE       text         not null, -- FIXME: xml?
  VIEWER_UID  varchar(255),

  primary key (RESOURCE_ID, NAME, VIEWER_UID) -- implicit index
);

----------------------
-- AddressBook Home --
----------------------

create table ADDRESSBOOK_HOME (
  RESOURCE_ID      				integer			primary key default nextval('RESOURCE_ID_SEQ'), -- implicit index
  ADDRESSBOOK_PROPERTY_STORE_ID	integer      	default nextval('RESOURCE_ID_SEQ') not null, 	-- implicit index
  OWNER_UID        				varchar(255) 	not null unique,                                -- implicit index
  DATAVERSION      				integer      	default 0 not null
);

-------------------------------
-- AddressBook Home Metadata --
-------------------------------

create table ADDRESSBOOK_HOME_METADATA (
  RESOURCE_ID      integer      primary key references ADDRESSBOOK_HOME on delete cascade, -- implicit index
  QUOTA_USED_BYTES integer      default 0 not null,
  CREATED          timestamp    default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED         timestamp    default timezone('UTC', CURRENT_TIMESTAMP)
);

-----------------------------
-- Shared AddressBook Bind --
-----------------------------

-- Joins sharee ADDRESSBOOK_HOME and owner ADDRESSBOOK_HOME

create table SHARED_ADDRESSBOOK_BIND (
  ADDRESSBOOK_HOME_RESOURCE_ID			integer			not null references ADDRESSBOOK_HOME,
  OWNER_HOME_RESOURCE_ID    			integer      	not null references ADDRESSBOOK_HOME on delete cascade,
  ADDRESSBOOK_RESOURCE_NAME    			varchar(255) 	not null,
  BIND_MODE                    			integer      	not null,	-- enum CALENDAR_BIND_MODE
  BIND_STATUS                  			integer      	not null,	-- enum CALENDAR_BIND_STATUS
  BIND_REVISION				   			integer      	default 0 not null,
  MESSAGE                      			text,                  		-- FIXME: xml?

  primary key (ADDRESSBOOK_HOME_RESOURCE_ID, OWNER_HOME_RESOURCE_ID), -- implicit index
  unique (ADDRESSBOOK_HOME_RESOURCE_ID, ADDRESSBOOK_RESOURCE_NAME)     -- implicit index
);

create index SHARED_ADDRESSBOOK_BIND_RESOURCE_ID on
  SHARED_ADDRESSBOOK_BIND(OWNER_HOME_RESOURCE_ID);

------------------------
-- AddressBook Object --
------------------------

create table ADDRESSBOOK_OBJECT (
  RESOURCE_ID             		integer   		primary key default nextval('RESOURCE_ID_SEQ'),    -- implicit index
  ADDRESSBOOK_HOME_RESOURCE_ID 	integer      	not null references ADDRESSBOOK_HOME on delete cascade,
  RESOURCE_NAME           		varchar(255) 	not null,
  VCARD_TEXT              		text         	not null,
  VCARD_UID               		varchar(255) 	not null,
  KIND 			  		  		integer      	not null,  -- enum ADDRESSBOOK_OBJECT_KIND
  MD5                     		char(32)     	not null,
  CREATED                 		timestamp    	default timezone('UTC', CURRENT_TIMESTAMP),
  MODIFIED                		timestamp    	default timezone('UTC', CURRENT_TIMESTAMP),

  unique (ADDRESSBOOK_HOME_RESOURCE_ID, RESOURCE_NAME), -- implicit index
  unique (ADDRESSBOOK_HOME_RESOURCE_ID, VCARD_UID)      -- implicit index
);

-----------------------------
-- AddressBook Object kind --
-----------------------------

create table ADDRESSBOOK_OBJECT_KIND (
  ID          integer     primary key,
  DESCRIPTION varchar(16) not null unique
);

insert into ADDRESSBOOK_OBJECT_KIND values (0, 'person');
insert into ADDRESSBOOK_OBJECT_KIND values (1, 'group' );
insert into ADDRESSBOOK_OBJECT_KIND values (2, 'resource');
insert into ADDRESSBOOK_OBJECT_KIND values (3, 'location');

----------------------------------
-- Revisions, forward reference --
----------------------------------

create sequence REVISION_SEQ;

---------------------------------
-- Address Book Object Members --
---------------------------------

create table ABO_MEMBERS (
    GROUP_ID              integer      not null, -- references ADDRESSBOOK_OBJECT on delete cascade,	-- AddressBook Object's (kind=='group') RESOURCE_ID
 	ADDRESSBOOK_ID		  integer      not null references ADDRESSBOOK_HOME on delete cascade,
    MEMBER_ID             integer      not null, -- references ADDRESSBOOK_OBJECT,						-- member AddressBook Object's RESOURCE_ID
  	REVISION              integer      default nextval('REVISION_SEQ') not null,
  	REMOVED               boolean      default false not null,

    primary key (GROUP_ID, MEMBER_ID, REVISION) -- implicit index
);

create index ABO_MEMBERS_ADDRESSBOOK_ID on
	ABO_MEMBERS(ADDRESSBOOK_ID);
create index ABO_MEMBERS_MEMBER_ID on
	ABO_MEMBERS(MEMBER_ID);

------------------------------------------
-- Address Book Object Foreign Members  --
------------------------------------------

create table ABO_FOREIGN_MEMBERS (
    GROUP_ID              integer      not null references ADDRESSBOOK_OBJECT on delete cascade,	-- AddressBook Object's (kind=='group') RESOURCE_ID
 	ADDRESSBOOK_ID		  integer      not null references ADDRESSBOOK_HOME on delete cascade,
    MEMBER_ADDRESS  	  varchar(255) not null, 													-- member AddressBook Object's 'calendar' address

    primary key (GROUP_ID, MEMBER_ADDRESS) -- implicit index
);

create index ABO_FOREIGN_MEMBERS_ADDRESSBOOK_ID on
	ABO_FOREIGN_MEMBERS(ADDRESSBOOK_ID);

-----------------------
-- Shared Group Bind --
-----------------------

-- Joins ADDRESSBOOK_HOME and ADDRESSBOOK_OBJECT (kind == group)

create table SHARED_GROUP_BIND (
  ADDRESSBOOK_HOME_RESOURCE_ID 		integer      not null references ADDRESSBOOK_HOME,
  GROUP_RESOURCE_ID      			integer      not null references ADDRESSBOOK_OBJECT on delete cascade,
  GROUP_ADDRESSBOOK_NAME			varchar(255) not null,
  BIND_MODE                    		integer      not null, -- enum CALENDAR_BIND_MODE
  BIND_STATUS                  		integer      not null, -- enum CALENDAR_BIND_STATUS
  BIND_REVISION				   		integer      default 0 not null,
  MESSAGE                      		text,                  -- FIXME: xml?

  primary key (ADDRESSBOOK_HOME_RESOURCE_ID, GROUP_RESOURCE_ID), -- implicit index
  unique (ADDRESSBOOK_HOME_RESOURCE_ID, GROUP_ADDRESSBOOK_NAME)  -- implicit index
);

create index SHARED_GROUP_BIND_RESOURCE_ID on
  SHARED_GROUP_BIND(GROUP_RESOURCE_ID);

---------------
-- Revisions --
---------------

-- create sequence REVISION_SEQ;

-------------------------------
-- Calendar Object Revisions --
-------------------------------

create table CALENDAR_OBJECT_REVISIONS (
  CALENDAR_HOME_RESOURCE_ID integer      not null references CALENDAR_HOME,
  CALENDAR_RESOURCE_ID      integer      references CALENDAR,


text:
{{
    config(
        materialized='incremental',
        partition_by = {'field': 'valid_starting_on', 'data_type': 'date'},
        unique_key='issue_field_day_id'
    )
}}

with combined_field_histories as (

    select *
    from {{ ref('int_jira__combine_field_histories') }}

    {% if is_incremental() %}
    where valid_starting_on >= (select max(valid_starting_on) from {{ this }} )
    {% endif %}
),

field as (

    select
        field_name,
        cast(field_id as {{ dbt_utils.type_string() }}) as field_id
    from {{ var('field') }}
),

limit_to_relevant_fields as (

-- let's remove unncessary rows moving forward and grab field names
    select
        combined_field_histories.*,
        field.field_name

    from combined_field_histories join field using(field_id)

    where
    lower(field.field_name) in ('sprint', 'status'
                                {%- for col in var('issue_field_history_columns') -%}
                                , {{ "'" ~ col ~ "'" }}
                                {%- endfor -%} )

),

-- only looking at the latest value for each day
get_latest_daily_value as (

    select *
    from (
        select
            *,

            -- want to grab last value for an issue's field for each day
            row_number() over (
                partition by valid_starting_on, issue_id, field_id
                order by valid_starting_at desc
                ) as row_num

        from limit_to_relevant_fields
    )
    where row_num = 1
),

final as (

    select
        field_id,
        issue_id,
        field_name,

        -- doing this to figure out what values are actually null and what needs to be backfilled in jira__daily_issue_field_history
        case when field_value is null then 'is_null' else field_value end as field_value,
        valid_starting_at,
        valid_ending_at,
        valid_starting_on,

        {{ dbt_utils.surrogate_key(['field_id','issue_id', 'valid_starting_at']) }} as issue_field_day_id

    from get_latest_daily_value
)

select * from final


text:
SET @sName = 'bx_acl';

-- FORMS
DELETE FROM `sys_form_inputs` WHERE `object`='bx_acl_price' AND `name` IN ('period', 'period_unit', 'price');
INSERT INTO `sys_form_inputs` (`object`, `module`, `name`, `value`, `values`, `checked`, `type`, `caption_system`, `caption`, `info`, `required`, `collapsed`, `html`, `attrs`, `attrs_tr`, `attrs_wrapper`, `checker_func`, `checker_params`, `checker_error`, `db_pass`, `db_params`, `editable`, `deletable`) VALUES
('bx_acl_price', @sName, 'period', '', '', 0, 'text', '_bx_acl_form_price_input_sys_period', '_bx_acl_form_price_input_period', '_bx_acl_form_price_input_inf_period', 1, 0, 0, '', '', '', '', '', '', 'Int', '', 1, 0),
('bx_acl_price', @sName, 'period_unit', '', '#!bx_acl_period_units', 0, 'select', '_bx_acl_form_price_input_sys_period_unit', '_bx_acl_form_price_input_period_unit', '_bx_acl_form_price_input_inf_period_unit', 1, 0, 0, '', '', '', '', '', '', 'Xss', '', 1, 0),
('bx_acl_price', @sName, 'price', '', '', 0, 'text', '_bx_acl_form_price_input_sys_price', '_bx_acl_form_price_input_price', '_bx_acl_form_price_input_inf_price', 1, 0, 0, '', '', '', '', '', '', 'Float', '', 1, 0);

-- GRIDS
UPDATE `sys_objects_grid` SET `source`='SELECT `tlp`.*, `tl`.`Name` AS `level_name`, `tl`.`Icon` AS `level_icon` FROM `bx_acl_level_prices` AS `tlp` LEFT JOIN `sys_acl_levels` AS `tl` ON `tlp`.`level_id`=`tl`.`ID` WHERE `tl`.`Active`=''yes'' AND `tl`.`Purchasable`=''yes'' ' WHERE `object`='bx_acl_view';

DELETE FROM `sys_grid_fields` WHERE `object` IN ('bx_acl_view');
INSERT INTO `sys_grid_fields` (`object`, `name`, `title`, `width`, `translatable`, `chars_limit`, `params`, `order`) VALUES
('bx_acl_view', 'level_icon', '_bx_acl_grid_column_level_icon', '5%', 0, 0, '', 1),
('bx_acl_view', 'level_name', '_bx_acl_grid_column_level_name', '25%', 1, 32, '', 2),
('bx_acl_view', 'price', '_bx_acl_grid_column_price', '10%', 0, 16, '', 3),
('bx_acl_view', 'period', '_bx_acl_grid_column_period', '15%', 0, 16, '', 4),
('bx_acl_view', 'trial', '_bx_acl_grid_column_trial', '15%', 0, 16, '', 5),
('bx_acl_view', 'actions', '', '30%', 0, '', '', 6);

DELETE FROM `sys_grid_actions` WHERE `object`='bx_acl_view' AND `name`='choose';
INSERT INTO `sys_grid_actions` (`object`, `type`, `name`, `title`, `icon`, `icon_only`, `confirm`, `order`) VALUES
('bx_acl_view', 'single', 'choose', '_bx_acl_grid_action_choose', 'check-square-o', 0, 0, 3);


text:
-- Copyright 2021 Google LLC
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     https://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

INSERT INTO DBC."TablesV" ("DatabaseName", "TableName", "LastAccessTimeStamp")
  VALUES (
    'db',
    'table',
    TIMESTAMP '2021-07-02 18:23:42' AT TIME ZONE INTERVAL '0:00' HOUR TO MINUTE
  );


text:
/*
 Navicat MySQL Data Transfer

 Source Server         : 本地
 Source Server Type    : MySQL
 Source Server Version : 50725
 Source Host           : localhost:3306
 Source Schema         :

	emersysrd_permission 用户管理数据库
	emersysrd_config	 系统配置数据库
	-----emersysrd_quartz	 任务调度数据库

 Target Server Type    : MySQL
 Target Server Version : 50725
 File Encoding         : 65001

 Date: 20/05/2019 17:51:35
*/

/******************************************/
/* 用户管理数据库 = emersysrd_permission  */
/******************************************/

DROP DATABASE IF EXISTS `emersysrd_permission`;

CREATE DATABASE  `emersysrd_permission` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

/******************************************/
/* 系统配置数据库 = emersysrd_config      */
/******************************************/

DROP DATABASE IF EXISTS `emersysrd_config`;

CREATE DATABASE  `emersysrd_config` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

/******************************************/
/* 任务调度数据库 = emersysrd_quartz      */
/******************************************/

DROP DATABASE IF EXISTS `emersysrd_quartz`;

CREATE DATABASE  `emersysrd_quartz` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

/******************************************/
/* 应急值守数据库 = emersysrd_emerduty      */
/******************************************/

DROP DATABASE IF EXISTS `emersysrd_emerduty`;

CREATE DATABASE  `emersysrd_emerduty` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


text:
/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50733
 Source Host           : localhost:3306
 Source Schema         : ibad_kasir

 Target Server Type    : MySQL
 Target Server Version : 50733
 File Encoding         : 65001

 Date: 05/01/2022 15:07:10
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for extras
-- ----------------------------
DROP TABLE IF EXISTS `extras`;
CREATE TABLE `extras`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `division_id` int(11) NULL DEFAULT NULL,
  `transaction_id` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `qty` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of extras
-- ----------------------------
INSERT INTO `extras` VALUES (1, 1, 0, 'buku tulis', '10000', '1', NULL, NULL);
INSERT INTO `extras` VALUES (2, 1, 1, 'buku gambar', '100', '1', NULL, NULL);
INSERT INTO `extras` VALUES (3, 2, 17, '1', '100000', '2', '2021-11-09 08:57:24', '2021-11-09 08:57:24');
INSERT INTO `extras` VALUES (6, 1, 19, 'buku gambar', '10000', '2', '2021-11-14 14:05:10', '2021-11-14 14:05:10');
INSERT INTO `extras` VALUES (7, 2, 19, 'buku tulis', '20000', '2', '2021-11-14 14:05:10', '2021-11-14 14:05:10');

-- ----------------------------
-- Table structure for m_bahan
-- ----------------------------
DROP TABLE IF EXISTS `m_bahan`;
CREATE TABLE `m_bahan`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `harga` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `property` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  `hidden_property` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  `id_category` int(11) UNSIGNED NULL DEFAULT NULL,
  `id_customer_type` int(11) UNSIGNED NULL DEFAULT NULL,
  `is_minimum` int(11) NULL DEFAULT NULL,
  `formula` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_m_category_m_bahan`(`id_category`) USING BTREE,
  INDEX `fk_m_customer_type_m_bahan`(`id_customer_type`) USING BTREE,
  CONSTRAINT `fk_m_category_m_bahan` FOREIGN KEY (`id_category`) REFERENCES `m_category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_m_customer_type_m_bahan` FOREIGN KEY (`id_customer_type`) REFERENCES `m_customer_type` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_bahan
-- ----------------------------
INSERT INTO `m_bahan` VALUES (1, 'Tes', '100000', '{\"panjang\":\"10\",\"lebar\":\"5\"}', '{\"panjang_min\":\"5\",\"lebar_min\":\"1\"}', 1, 1, NULL, NULL);
INSERT INTO `m_bahan` VALUES (2, 'Sticker Ritrama', '125000', '{\"panjang\":\"7000\",\"lebar\":\"100\"}', '{\"panjang_min\":\"500\",\"lebar_min\":\"25\"}', 3, 2, NULL, NULL);
INSERT INTO `m_bahan` VALUES (3, 'qw', '10000', '{\"waktu\":\"11\"}', NULL, 6, 2, NULL, NULL);
INSERT INTO `m_bahan` VALUES (4, 'Stiker A4', '50000', '{\"panjang\":\"140\",\"lebar\":\"80\"}', '{\"panjang_min\":\"10\",\"lebar_min\":\"10\"}', 3, 2, NULL, NULL);
INSERT INTO `m_bahan` VALUES (5, 'Stiker A5', '30000', '{\"panjang\":\"100\",\"lebar\":\"75\"}', '{\"panjang_min\":\"10\",\"lebar_min\":\"10\"}', 3, 2, 1, NULL);
INSERT INTO `m_bahan` VALUES (6, 'Flexi', '20000', '{\"panjang\":\"1000\",\"lebar\":\"100\"}', '{\"panjang_min\":\"1000\",\"lebar_min\":\"101\"}', 7, 1, 1, NULL);
INSERT INTO `m_bahan` VALUES (7, 'Flexi 2', '20000', '{\"panjang\":\"1000\",\"lebar\":\"200\"}', '{\"panjang_min\":\"\",\"lebar_min\":\"205\"}', 7, 1, NULL, NULL);
INSERT INTO `m_bahan` VALUES (8, 'qqq', '123', '{\"panjang\":\"200\"}', '{\"panjang_min\":\"10\"}', 4, 1, NULL, NULL);
INSERT INTO `m_bahan` VALUES (9, 'qqq', '12312', '{\"panjang\":\"150\"}', '{\"panjang_min\":\"10\"}', 4, 3, NULL, NULL);
INSERT INTO `m_bahan` VALUES (10, 'qqq', '12331', '{\"panjang\":\"100\"}', '{\"panjang_min\":\"10\"}', 4, 2, 1, NULL);
INSERT INTO `m_bahan` VALUES (11, 'Tes Bahan Dengan Tipe Customer Edit', '15000', '{\"panjang\":\"200\"}', '{\"panjang_min\":\"200\"}', 4, 3, 0, '[panjang]*10');

-- ----------------------------
-- Table structure for m_bahan_finishing
-- ----------------------------
DROP TABLE IF EXISTS `m_bahan_finishing`;
CREATE TABLE `m_bahan_finishing`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `harga` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `property` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  `id_category` int(11) UNSIGNED NULL DEFAULT NULL,
  `id_customer_type` int(11) UNSIGNED NULL DEFAULT NULL,
  `formula` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_m_customer_type_m_bahan_finishing`(`id_customer_type`) USING BTREE,
  INDEX `fk_m_category_m_bahan_finishing`(`id_category`) USING BTREE,
  CONSTRAINT `fk_m_category_m_bahan_finishing` FOREIGN KEY (`id_category`) REFERENCES `m_category` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_m_customer_type_m_bahan_finishing` FOREIGN KEY (`id_customer_type`) REFERENCES `m_customer_type` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_bahan_finishing
-- ----------------------------
INSERT INTO `m_bahan_finishing` VALUES (1, 'Tes Finishing', '5000', '{\"panjang\":\"10\",\"lebar\":\"5\",\"qty\":\"10\"}', 1, 1, '[panjang]*[lebar]*[qty]');
INSERT INTO `m_bahan_finishing` VALUES (2, 'finish him', '10000', '{\"panjang\":\"15\",\"lebar\":\"15\",\"qty\":\"15\"}', 3, 1, NULL);
INSERT INTO `m_bahan_finishing` VALUES (3, 'banner pleksi', '10000', '{\"panjang\":\"10\",\"lebar\":\"10\",\"qty\":\"1\"}', 7, 1, NULL);
INSERT INTO `m_bahan_finishing` VALUES (4, 'Tes Finising Baru', '100000', '{\"panjang\":\"100\",\"lebar\":\"100\",\"qty\":\"10\"}', 1, 1, NULL);

-- ----------------------------
-- Table structure for m_bahan_finishing_potongan
-- ----------------------------
DROP TABLE IF EXISTS `m_bahan_finishing_potongan`;
CREATE TABLE `m_bahan_finishing_potongan`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_finishing` int(11) UNSIGNED NULL DEFAULT NULL,
  `batas_bawah` int(11) UNSIGNED NULL DEFAULT NULL,
  `batas_atas` int(11) UNSIGNED NULL DEFAULT NULL,
  `potongan_harga` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_m_bahan_finishing_m_bahan_finishing_potongan`(`id_finishing`) USING BTREE,
  CONSTRAINT `fk_m_bahan_finishing_m_bahan_finishing_potongan` FOREIGN KEY (`id_finishing`) REFERENCES `m_bahan_finishing` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_bahan_finishing_potongan
-- ----------------------------
INSERT INTO `m_bahan_finishing_potongan` VALUES (1, 1, 50, 100, '2000');
INSERT INTO `m_bahan_finishing_potongan` VALUES (2, NULL, 50, 100, '1000');

-- ----------------------------
-- Table structure for m_bahan_potongan
-- ----------------------------
DROP TABLE IF EXISTS `m_bahan_potongan`;
CREATE TABLE `m_bahan_potongan`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_bahan` int(11) UNSIGNED NULL DEFAULT NULL,
  `batas_bawah` int(11) UNSIGNED NULL DEFAULT NULL,
  `batas_atas` int(11) UNSIGNED NULL DEFAULT NULL,
  `potongan_harga` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_m_bahan_m_bahan_potongan`(`id_bahan`) USING BTREE,
  CONSTRAINT `fk_m_bahan_m_bahan_potongan` FOREIGN KEY (`id_bahan`) REFERENCES `m_bahan` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_bahan_potongan
-- ----------------------------
INSERT INTO `m_bahan_potongan` VALUES (1, 1, 50, 100, '10000');
INSERT INTO `m_bahan_potongan` VALUES (2, NULL, 50, 100, '1000');
INSERT INTO `m_bahan_potongan` VALUES (3, 2, 1, 50, '25000');
INSERT INTO `m_bahan_potongan` VALUES (4, 2, 50, NULL, '50000');

-- ----------------------------
-- Table structure for m_category
-- ----------------------------
DROP TABLE IF EXISTS `m_category`;
CREATE TABLE `m_category`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_division` int(11) UNSIGNED NULL DEFAULT NULL,
  `nama` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `property` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  `hidden_property` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  `satuan` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  `validation` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_m_division_m_category`(`id_division`) USING BTREE,
  CONSTRAINT `fk_m_division_m_category` FOREIGN KEY (`id_division`) REFERENCES `m_division` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_category
-- ----------------------------
INSERT INTO `m_category` VALUES (1, 2, 'Tes', '[\"panjang\",\"lebar\",\"qty\"]', '[\"panjang_min\",\"lebar_min\"]', '[\"Cm\",\"Cm\",\"Total\"]', '[\"panjang\"]');
INSERT INTO `m_category` VALUES (2, 2, 'Category 2', '[\"qty\"]', '[]', '[\"Total\"]', NULL);
INSERT INTO `m_category` VALUES (3, 2, 'Sticker', '[\"panjang\",\"lebar\",\"qty\"]', '[\"panjang_min\",\"lebar_min\"]', '[\"Cm\",\"Cm\",\"Total\"]', '[\"panjang\",\"lebar\"]');
INSERT INTO `m_category` VALUES (4, 1, 'Bruce Banner', '[\"panjang\",\"qty\"]', '[\"panjang_min\"]', '[\"Cm\",\"Total\"]', '[\"panjang\"]');
INSERT INTO `m_category` VALUES (5, 3, 'Stiker A3', '[\"qty\"]', '[]', '[\"Total\"]', NULL);
INSERT INTO `m_category` VALUES (6, 1, 'KP', '[\"waktu\",\"qty\"]', '[\"waktu_min\"]', '[\"jam\",\"total\"]', NULL);
INSERT INTO `m_category` VALUES (7, 2, 'Banner', '[\"panjang\",\"lebar\",\"qty\"]', '[\"panjang_min\",\"lebar_min\"]', '[\"Meter\",\"Cm\",\"Total\"]', '[\"lebar\"]');

-- ----------------------------
-- Table structure for m_customer
-- ----------------------------
DROP TABLE IF EXISTS `m_customer`;
CREATE TABLE `m_customer`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `nohp` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `nokartu` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `id_limit_customer` int(11) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_m_limit_customer_m_customer`(`id_limit_customer`) USING BTREE,
  INDEX `nama`(`nama`) USING BTREE,
  INDEX `nohp`(`nohp`) USING BTREE,
  INDEX `nokartu`(`nokartu`) USING BTREE,
  CONSTRAINT `fk_m_limit_customer_m_customer` FOREIGN KEY (`id_limit_customer`) REFERENCES `m_limit_customer` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_customer
-- ----------------------------
INSERT INTO `m_customer` VALUES (1, 'Yusuf', '08123456789', '1', 1);
INSERT INTO `m_customer` VALUES (2, 'Yusuf 1', '08987654321', '2', 1);
INSERT INTO `m_customer` VALUES (3, 'Yusuf 2', '08987654321', '2', 1);
INSERT INTO `m_customer` VALUES (4, 'Yusuf 3', '08987654321', '2', 1);
INSERT INTO `m_customer` VALUES (5, 'Yusuf 4', '08987654321', '2', 1);
INSERT INTO `m_customer` VALUES (6, 'Yusuf 5', '08987654321', '2', 1);
INSERT INTO `m_customer` VALUES (7, 'Pelanggan', '08388127123', '091238', 1);

-- ----------------------------
-- Table structure for m_customer_type
-- ----------------------------
DROP TABLE IF EXISTS `m_customer_type`;
CREATE TABLE `m_customer_type`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_customer_type
-- ----------------------------
INSERT INTO `m_customer_type` VALUES (1, 'reklame');
INSERT INTO `m_customer_type` VALUES (2, 'umum');
INSERT INTO `m_customer_type` VALUES (3, 'reklame baru');

-- ----------------------------
-- Table structure for m_customer_type_division
-- ----------------------------
DROP TABLE IF EXISTS `m_customer_type_division`;
CREATE TABLE `m_customer_type_division`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_customer` int(11) UNSIGNED NULL DEFAULT NULL,
  `id_customer_type` int(11) UNSIGNED NULL DEFAULT NULL,
  `id_division` int(11) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_m_customer_m_customer_type_division`(`id_customer`) USING BTREE,
  INDEX `fk_m_customer_type_m_customer_type_division`(`id_customer_type`) USING BTREE,
  INDEX `fk_m_division_m_customer_type_division`(`id_division`) USING BTREE,
  CONSTRAINT `fk_m_customer_m_customer_type_division` FOREIGN KEY (`id_customer`) REFERENCES `m_customer` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_m_customer_type_m_customer_type_division` FOREIGN KEY (`id_customer_type`) REFERENCES `m_customer_type` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_m_division_m_customer_type_division` FOREIGN KEY (`id_division`) REFERENCES `m_division` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 74 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_customer_type_division
-- ----------------------------
INSERT INTO `m_customer_type_division` VALUES (11, 1, 1, 1);
INSERT INTO `m_customer_type_division` VALUES (12, 1, 1, 2);
INSERT INTO `m_customer_type_division` VALUES (21, 2, 1, 1);
INSERT INTO `m_customer_type_division` VALUES (22, 2, 2, 2);
INSERT INTO `m_customer_type_division` VALUES (71, 7, 2, 1);
INSERT INTO `m_customer_type_division` VALUES (72, 7, 2, 2);
INSERT INTO `m_customer_type_division` VALUES (73, 7, 2, 3);

-- ----------------------------
-- Table structure for m_design
-- ----------------------------
DROP TABLE IF EXISTS `m_design`;
CREATE TABLE `m_design`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `harga` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_design
-- ----------------------------
INSERT INTO `m_design` VALUES (1, 'Tes Design', '10000');

-- ----------------------------
-- Table structure for m_division
-- ----------------------------
DROP TABLE IF EXISTS `m_division`;
CREATE TABLE `m_division`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_division
-- ----------------------------
INSERT INTO `m_division` VALUES (1, 'Outdoor');
INSERT INTO `m_division` VALUES (2, 'Indoor');
INSERT INTO `m_division` VALUES (3, 'Print A3');
INSERT INTO `m_division` VALUES (4, 'Print A5');

-- ----------------------------
-- Table structure for m_jenis_pembayaran
-- ----------------------------
DROP TABLE IF EXISTS `m_jenis_pembayaran`;
CREATE TABLE `m_jenis_pembayaran`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_jenis_pembayaran
-- ----------------------------
INSERT INTO `m_jenis_pembayaran` VALUES (1, 'Tes Jenis Pembayaran');
INSERT INTO `m_jenis_pembayaran` VALUES (2, 'Jenis Pembayaran 2');
INSERT INTO `m_jenis_pembayaran` VALUES (3, 'Jenis Pembayaran 3');

-- ----------------------------
-- Table structure for m_limit_customer
-- ----------------------------
DROP TABLE IF EXISTS `m_limit_customer`;
CREATE TABLE `m_limit_customer`  (
  `id` int(11) UNSIGNED NOT NULL,
  `limit` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `tempo` int(3) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_limit_customer
-- ----------------------------
INSERT INTO `m_limit_customer` VALUES (1, '50000', 30);
INSERT INTO `m_limit_customer` VALUES (2, '200000', 30);

-- ----------------------------
-- Table structure for m_promo
-- ----------------------------
DROP TABLE IF EXISTS `m_promo`;
CREATE TABLE `m_promo`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `kode` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `diskon` smallint(100) UNSIGNED NULL DEFAULT NULL,
  `diskon_maks` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `expired` date NULL DEFAULT NULL,
  `nama` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `status` enum('yes','no') CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `created_at` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_promo
-- ----------------------------
INSERT INTO `m_promo` VALUES (1, 'HR15JLY21', 50, '25000', '2021-12-24', 'Tes Promo', 'yes', '2021-07-15 10:01:10');

-- ----------------------------
-- Table structure for m_promo_division
-- ----------------------------
DROP TABLE IF EXISTS `m_promo_division`;
CREATE TABLE `m_promo_division`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_promo` int(11) UNSIGNED NULL DEFAULT NULL,
  `id_division` int(11) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_m_promo_m_promo_division`(`id_promo`) USING BTREE,
  INDEX `fk_m_division_m_promo_division`(`id_division`) USING BTREE,
  CONSTRAINT `fk_m_division_m_promo_division` FOREIGN KEY (`id_division`) REFERENCES `m_division` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_m_promo_m_promo_division` FOREIGN KEY (`id_promo`) REFERENCES `m_promo` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of m_promo_division
-- ----------------------------
INSERT INTO `m_promo_division` VALUES (11, 1, 1);


text:
#standardSQL
# 14_16: Top analytics providers
SELECT
  client,
  canonicalDomain AS provider,
  COUNT(DISTINCT page) AS freq_pages,
  COUNT(0) AS freq_requests,
  total AS total_pages,
  SUM(COUNT(0)) OVER (PARTITION BY client) AS total_requests,
  ROUND(COUNT(DISTINCT page) * 100 / total, 2) AS pct_pages,
  ROUND(COUNT(0) * 100 / SUM(COUNT(0)) OVER (PARTITION BY client), 2) AS pct_requests
FROM
  `httparchive.almanac.summary_requests` r
JOIN (
  SELECT _TABLE_SUFFIX AS client, url AS page
  FROM `httparchive.technologies.2019_07_01_*`
  WHERE category = 'CMS')
USING
  (client, page)
JOIN
  `httparchive.almanac.third_parties` tp
ON
  NET.HOST(date, url) = domain
JOIN (
  SELECT _TABLE_SUFFIX AS client, COUNT(0) AS total
  FROM `httparchive.summary_pages.2019_07_01_*`
  GROUP BY _TABLE_SUFFIX)
USING
  (client)
WHERE
  r.date = '2019-07-01' AND
  tp.date = '2019-07-01' AND
  category = 'analytics'
GROUP BY
  client,
  total,
  provider
ORDER BY
  freq_requests / total DESC


text:
CREATE TABLE `google_gke_node_pool_labels` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `value` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `node_pool_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_node_pool_id_name` (`name`,`node_pool_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


text:
/*
-- Query: SELECT * FROM movies2017.movies_directors
LIMIT 0, 1000

-- Date: 2018-03-31 00:22
*/
INSERT INTO `movies_directors` (`Movies_idMovies`,`Directors_idDirectors`) VALUES (1,1);
INSERT INTO `movies_directors` (`Movies_idMovies`,`Directors_idDirectors`) VALUES (2,2);
INSERT INTO `movies_directors` (`Movies_idMovies`,`Directors_idDirectors`) VALUES (3,3);
INSERT INTO `movies2017`.`movies_directors` (`Movies_idMovies`, `Directors_idDirectors`) VALUES ('4', '4');
INSERT INTO `movies2017`.`movies_directors` (`Movies_idMovies`, `Directors_idDirectors`) VALUES ('5', '5');
INSERT INTO `movies2017`.`movies_directors` (`Movies_idMovies`, `Directors_idDirectors`) VALUES ('6', '6');