API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 9th, 2018
328
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 21.64 KB | None | 0 0
raw download clone embed print report
  1. CREATE DATABASE IF NOT EXISTS `db_ecommerce` /*!40100 DEFAULT CHARACTER SET utf8 */;
  2. USE `db_ecommerce`;
  3. -- MySQL dump 10.13 Distrib 5.7.17, for Win64 (x86_64)
  4. --
  5. -- Host: 127.0.0.1 Database: db_ecommerce
  6. -- ------------------------------------------------------
  7. -- Server version 5.5.5-10.1.19-MariaDB
  8.  
  9. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  10. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  11. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  12. /*!40101 SET NAMES utf8 */;
  13. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  14. /*!40103 SET TIME_ZONE='+00:00' */;
  15. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  16. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  17. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  18. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  19.  
  20. --
  21. -- Table structure for table `tb_addresses`
  22. --
  23.  
  24. DROP TABLE IF EXISTS `tb_addresses`;
  25. /*!40101 SET @saved_cs_client = @@character_set_client */;
  26. /*!40101 SET character_set_client = utf8 */;
  27. CREATE TABLE `tb_addresses` (
  28. `idaddress` int(11) NOT NULL AUTO_INCREMENT,
  29. `idperson` int(11) NOT NULL,
  30. `desaddress` varchar(128) NOT NULL,
  31. `descomplement` varchar(32) DEFAULT NULL,
  32. `descity` varchar(32) NOT NULL,
  33. `desstate` varchar(32) NOT NULL,
  34. `descountry` varchar(32) NOT NULL,
  35. `nrzipcode` int(11) NOT NULL,
  36. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  37. PRIMARY KEY (`idaddress`),
  38. KEY `fk_addresses_persons_idx` (`idperson`),
  39. CONSTRAINT `fk_addresses_persons` FOREIGN KEY (`idperson`) REFERENCES `tb_persons` (`idperson`) ON DELETE NO ACTION ON UPDATE NO ACTION
  40. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  41. /*!40101 SET character_set_client = @saved_cs_client */;
  42.  
  43. --
  44. -- Dumping data for table `tb_addresses`
  45. --
  46.  
  47. LOCK TABLES `tb_addresses` WRITE;
  48. /*!40000 ALTER TABLE `tb_addresses` DISABLE KEYS */;
  49. /*!40000 ALTER TABLE `tb_addresses` ENABLE KEYS */;
  50. UNLOCK TABLES;
  51.  
  52. --
  53. -- Table structure for table `tb_carts`
  54. --
  55.  
  56. DROP TABLE IF EXISTS `tb_carts`;
  57. /*!40101 SET @saved_cs_client = @@character_set_client */;
  58. /*!40101 SET character_set_client = utf8 */;
  59. CREATE TABLE `tb_carts` (
  60. `idcart` int(11) NOT NULL,
  61. `dessessionid` varchar(64) NOT NULL,
  62. `iduser` int(11) DEFAULT NULL,
  63. `idaddress` int(11) DEFAULT NULL,
  64. `vlfreight` decimal(10,2) DEFAULT NULL,
  65. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  66. PRIMARY KEY (`idcart`),
  67. KEY `FK_carts_users_idx` (`iduser`),
  68. KEY `fk_carts_addresses_idx` (`idaddress`),
  69. CONSTRAINT `fk_carts_addresses` FOREIGN KEY (`idaddress`) REFERENCES `tb_addresses` (`idaddress`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  70. CONSTRAINT `fk_carts_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
  71. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  72. /*!40101 SET character_set_client = @saved_cs_client */;
  73.  
  74. --
  75. -- Dumping data for table `tb_carts`
  76. --
  77.  
  78. LOCK TABLES `tb_carts` WRITE;
  79. /*!40000 ALTER TABLE `tb_carts` DISABLE KEYS */;
  80. /*!40000 ALTER TABLE `tb_carts` ENABLE KEYS */;
  81. UNLOCK TABLES;
  82.  
  83. --
  84. -- Table structure for table `tb_cartsproducts`
  85. --
  86.  
  87. DROP TABLE IF EXISTS `tb_cartsproducts`;
  88. /*!40101 SET @saved_cs_client = @@character_set_client */;
  89. /*!40101 SET character_set_client = utf8 */;
  90. CREATE TABLE `tb_cartsproducts` (
  91. `idcartproduct` int(11) NOT NULL AUTO_INCREMENT,
  92. `idcart` int(11) NOT NULL,
  93. `idproduct` int(11) NOT NULL,
  94. `dtremoved` datetime NOT NULL,
  95. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  96. PRIMARY KEY (`idcartproduct`),
  97. KEY `FK_cartsproducts_carts_idx` (`idcart`),
  98. KEY `FK_cartsproducts_products_idx` (`idproduct`),
  99. CONSTRAINT `fk_cartsproducts_carts` FOREIGN KEY (`idcart`) REFERENCES `tb_carts` (`idcart`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  100. CONSTRAINT `fk_cartsproducts_products` FOREIGN KEY (`idproduct`) REFERENCES `tb_products` (`idproduct`) ON DELETE NO ACTION ON UPDATE NO ACTION
  101. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  102. /*!40101 SET character_set_client = @saved_cs_client */;
  103.  
  104. --
  105. -- Dumping data for table `tb_cartsproducts`
  106. --
  107.  
  108. LOCK TABLES `tb_cartsproducts` WRITE;
  109. /*!40000 ALTER TABLE `tb_cartsproducts` DISABLE KEYS */;
  110. /*!40000 ALTER TABLE `tb_cartsproducts` ENABLE KEYS */;
  111. UNLOCK TABLES;
  112.  
  113. --
  114. -- Table structure for table `tb_categories`
  115. --
  116.  
  117. DROP TABLE IF EXISTS `tb_categories`;
  118. /*!40101 SET @saved_cs_client = @@character_set_client */;
  119. /*!40101 SET character_set_client = utf8 */;
  120. CREATE TABLE `tb_categories` (
  121. `idcategory` int(11) NOT NULL AUTO_INCREMENT,
  122. `descategory` varchar(32) NOT NULL,
  123. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  124. PRIMARY KEY (`idcategory`)
  125. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  126. /*!40101 SET character_set_client = @saved_cs_client */;
  127.  
  128. --
  129. -- Dumping data for table `tb_categories`
  130. --
  131.  
  132. LOCK TABLES `tb_categories` WRITE;
  133. /*!40000 ALTER TABLE `tb_categories` DISABLE KEYS */;
  134. /*!40000 ALTER TABLE `tb_categories` ENABLE KEYS */;
  135. UNLOCK TABLES;
  136.  
  137. --
  138. -- Table structure for table `tb_orders`
  139. --
  140.  
  141. DROP TABLE IF EXISTS `tb_orders`;
  142. /*!40101 SET @saved_cs_client = @@character_set_client */;
  143. /*!40101 SET character_set_client = utf8 */;
  144. CREATE TABLE `tb_orders` (
  145. `idorder` int(11) NOT NULL AUTO_INCREMENT,
  146. `idcart` int(11) NOT NULL,
  147. `iduser` int(11) NOT NULL,
  148. `idstatus` int(11) NOT NULL,
  149. `vltotal` decimal(10,2) NOT NULL,
  150. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  151. PRIMARY KEY (`idorder`),
  152. KEY `FK_orders_carts_idx` (`idcart`),
  153. KEY `FK_orders_users_idx` (`iduser`),
  154. KEY `fk_orders_ordersstatus_idx` (`idstatus`),
  155. CONSTRAINT `fk_orders_carts` FOREIGN KEY (`idcart`) REFERENCES `tb_carts` (`idcart`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  156. CONSTRAINT `fk_orders_ordersstatus` FOREIGN KEY (`idstatus`) REFERENCES `tb_ordersstatus` (`idstatus`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  157. CONSTRAINT `fk_orders_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
  158. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  159. /*!40101 SET character_set_client = @saved_cs_client */;
  160.  
  161. --
  162. -- Dumping data for table `tb_orders`
  163. --
  164.  
  165. LOCK TABLES `tb_orders` WRITE;
  166. /*!40000 ALTER TABLE `tb_orders` DISABLE KEYS */;
  167. /*!40000 ALTER TABLE `tb_orders` ENABLE KEYS */;
  168. UNLOCK TABLES;
  169.  
  170. --
  171. -- Table structure for table `tb_ordersstatus`
  172. --
  173.  
  174. DROP TABLE IF EXISTS `tb_ordersstatus`;
  175. /*!40101 SET @saved_cs_client = @@character_set_client */;
  176. /*!40101 SET character_set_client = utf8 */;
  177. CREATE TABLE `tb_ordersstatus` (
  178. `idstatus` int(11) NOT NULL AUTO_INCREMENT,
  179. `desstatus` varchar(32) NOT NULL,
  180. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  181. PRIMARY KEY (`idstatus`)
  182. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  183. /*!40101 SET character_set_client = @saved_cs_client */;
  184.  
  185. --
  186. -- Dumping data for table `tb_ordersstatus`
  187. --
  188.  
  189. LOCK TABLES `tb_ordersstatus` WRITE;
  190. /*!40000 ALTER TABLE `tb_ordersstatus` DISABLE KEYS */;
  191. INSERT INTO `tb_ordersstatus` VALUES (1,'Em Aberto','2017-03-13 03:00:00'),(2,'Aguardando Pagamento','2017-03-13 03:00:00'),(3,'Pago','2017-03-13 03:00:00'),(4,'Entregue','2017-03-13 03:00:00');
  192. /*!40000 ALTER TABLE `tb_ordersstatus` ENABLE KEYS */;
  193. UNLOCK TABLES;
  194.  
  195. --
  196. -- Table structure for table `tb_persons`
  197. --
  198.  
  199. DROP TABLE IF EXISTS `tb_persons`;
  200. /*!40101 SET @saved_cs_client = @@character_set_client */;
  201. /*!40101 SET character_set_client = utf8 */;
  202. CREATE TABLE `tb_persons` (
  203. `idperson` int(11) NOT NULL AUTO_INCREMENT,
  204. `desperson` varchar(64) NOT NULL,
  205. `desemail` varchar(128) DEFAULT NULL,
  206. `nrphone` bigint(20) DEFAULT NULL,
  207. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  208. PRIMARY KEY (`idperson`)
  209. ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
  210. /*!40101 SET character_set_client = @saved_cs_client */;
  211.  
  212. --
  213. -- Dumping data for table `tb_persons`
  214. --
  215.  
  216. LOCK TABLES `tb_persons` WRITE;
  217. /*!40000 ALTER TABLE `tb_persons` DISABLE KEYS */;
  218. INSERT INTO `tb_persons` VALUES (1,'João Rangel','admin@hcode.com.br',2147483647,'2017-03-01 03:00:00'),(7,'Suporte','suporte@hcode.com.br',1112345678,'2017-03-15 16:10:27');
  219. /*!40000 ALTER TABLE `tb_persons` ENABLE KEYS */;
  220. UNLOCK TABLES;
  221.  
  222. --
  223. -- Table structure for table `tb_products`
  224. --
  225.  
  226. DROP TABLE IF EXISTS `tb_products`;
  227. /*!40101 SET @saved_cs_client = @@character_set_client */;
  228. /*!40101 SET character_set_client = utf8 */;
  229. CREATE TABLE `tb_products` (
  230. `idproduct` int(11) NOT NULL,
  231. `desproduct` varchar(64) NOT NULL,
  232. `vlprice` decimal(10,2) NOT NULL,
  233. `vlwidth` decimal(10,2) NOT NULL,
  234. `vlheight` decimal(10,2) NOT NULL,
  235. `vllength` decimal(10,2) NOT NULL,
  236. `vlweight` decimal(10,2) NOT NULL,
  237. `desurl` varchar(128) NOT NULL,
  238. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  239. PRIMARY KEY (`idproduct`)
  240. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  241. /*!40101 SET character_set_client = @saved_cs_client */;
  242.  
  243. --
  244. -- Dumping data for table `tb_products`
  245. --
  246.  
  247. LOCK TABLES `tb_products` WRITE;
  248. /*!40000 ALTER TABLE `tb_products` DISABLE KEYS */;
  249. INSERT INTO `tb_products` VALUES (1,'Smartphone Android 7.0',999.95,75.00,151.00,80.00,167.00,'smartphone-android-7.0','2017-03-13 03:00:00'),(2,'SmartTV LED 4K',3925.99,917.00,596.00,288.00,8600.00,'smarttv-led-4k','2017-03-13 03:00:00'),(3,'Notebook 14\" 4GB 1TB',1949.99,345.00,23.00,30.00,2000.00,'notebook-14-4gb-1tb','2017-03-13 03:00:00');
  250. /*!40000 ALTER TABLE `tb_products` ENABLE KEYS */;
  251. UNLOCK TABLES;
  252.  
  253. --
  254. -- Table structure for table `tb_productscategories`
  255. --
  256.  
  257. DROP TABLE IF EXISTS `tb_productscategories`;
  258. /*!40101 SET @saved_cs_client = @@character_set_client */;
  259. /*!40101 SET character_set_client = utf8 */;
  260. CREATE TABLE `tb_productscategories` (
  261. `idcategory` int(11) NOT NULL,
  262. `idproduct` int(11) NOT NULL,
  263. PRIMARY KEY (`idcategory`,`idproduct`),
  264. KEY `fk_productscategories_products_idx` (`idproduct`),
  265. CONSTRAINT `fk_productscategories_categories` FOREIGN KEY (`idcategory`) REFERENCES `tb_categories` (`idcategory`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  266. CONSTRAINT `fk_productscategories_products` FOREIGN KEY (`idproduct`) REFERENCES `tb_products` (`idproduct`) ON DELETE NO ACTION ON UPDATE NO ACTION
  267. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  268. /*!40101 SET character_set_client = @saved_cs_client */;
  269.  
  270. --
  271. -- Dumping data for table `tb_productscategories`
  272. --
  273.  
  274. LOCK TABLES `tb_productscategories` WRITE;
  275. /*!40000 ALTER TABLE `tb_productscategories` DISABLE KEYS */;
  276. /*!40000 ALTER TABLE `tb_productscategories` ENABLE KEYS */;
  277. UNLOCK TABLES;
  278.  
  279. --
  280. -- Table structure for table `tb_users`
  281. --
  282.  
  283. DROP TABLE IF EXISTS `tb_users`;
  284. /*!40101 SET @saved_cs_client = @@character_set_client */;
  285. /*!40101 SET character_set_client = utf8 */;
  286. CREATE TABLE `tb_users` (
  287. `iduser` int(11) NOT NULL AUTO_INCREMENT,
  288. `idperson` int(11) NOT NULL,
  289. `deslogin` varchar(64) NOT NULL,
  290. `despassword` varchar(256) NOT NULL,
  291. `inadmin` tinyint(4) NOT NULL DEFAULT '0',
  292. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  293. PRIMARY KEY (`iduser`),
  294. KEY `FK_users_persons_idx` (`idperson`),
  295. CONSTRAINT `fk_users_persons` FOREIGN KEY (`idperson`) REFERENCES `tb_persons` (`idperson`) ON DELETE NO ACTION ON UPDATE NO ACTION
  296. ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
  297. /*!40101 SET character_set_client = @saved_cs_client */;
  298.  
  299. --
  300. -- Dumping data for table `tb_users`
  301. --
  302.  
  303. LOCK TABLES `tb_users` WRITE;
  304. /*!40000 ALTER TABLE `tb_users` DISABLE KEYS */;
  305. INSERT INTO `tb_users` VALUES (1,1,'admin','$2y$12$YlooCyNvyTji8bPRcrfNfOKnVMmZA9ViM2A3IpFjmrpIbp5ovNmga',1,'2017-03-13 03:00:00'),(7,7,'suporte','$2y$12$HFjgUm/mk1RzTy4ZkJaZBe0Mc/BA2hQyoUckvm.lFa6TesjtNpiMe',1,'2017-03-15 16:10:27');
  306. /*!40000 ALTER TABLE `tb_users` ENABLE KEYS */;
  307. UNLOCK TABLES;
  308.  
  309. --
  310. -- Table structure for table `tb_userslogs`
  311. --
  312.  
  313. DROP TABLE IF EXISTS `tb_userslogs`;
  314. /*!40101 SET @saved_cs_client = @@character_set_client */;
  315. /*!40101 SET character_set_client = utf8 */;
  316. CREATE TABLE `tb_userslogs` (
  317. `idlog` int(11) NOT NULL AUTO_INCREMENT,
  318. `iduser` int(11) NOT NULL,
  319. `deslog` varchar(128) NOT NULL,
  320. `desip` varchar(45) NOT NULL,
  321. `desuseragent` varchar(128) NOT NULL,
  322. `dessessionid` varchar(64) NOT NULL,
  323. `desurl` varchar(128) NOT NULL,
  324. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  325. PRIMARY KEY (`idlog`),
  326. KEY `fk_userslogs_users_idx` (`iduser`),
  327. CONSTRAINT `fk_userslogs_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
  328. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  329. /*!40101 SET character_set_client = @saved_cs_client */;
  330.  
  331. --
  332. -- Dumping data for table `tb_userslogs`
  333. --
  334.  
  335. LOCK TABLES `tb_userslogs` WRITE;
  336. /*!40000 ALTER TABLE `tb_userslogs` DISABLE KEYS */;
  337. /*!40000 ALTER TABLE `tb_userslogs` ENABLE KEYS */;
  338. UNLOCK TABLES;
  339.  
  340. --
  341. -- Table structure for table `tb_userspasswordsrecoveries`
  342. --
  343.  
  344. DROP TABLE IF EXISTS `tb_userspasswordsrecoveries`;
  345. /*!40101 SET @saved_cs_client = @@character_set_client */;
  346. /*!40101 SET character_set_client = utf8 */;
  347. CREATE TABLE `tb_userspasswordsrecoveries` (
  348. `idrecovery` int(11) NOT NULL AUTO_INCREMENT,
  349. `iduser` int(11) NOT NULL,
  350. `desip` varchar(45) NOT NULL,
  351. `dtrecovery` datetime DEFAULT NULL,
  352. `dtregister` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  353. PRIMARY KEY (`idrecovery`),
  354. KEY `fk_userspasswordsrecoveries_users_idx` (`iduser`),
  355. CONSTRAINT `fk_userspasswordsrecoveries_users` FOREIGN KEY (`iduser`) REFERENCES `tb_users` (`iduser`) ON DELETE NO ACTION ON UPDATE NO ACTION
  356. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
  357. /*!40101 SET character_set_client = @saved_cs_client */;
  358.  
  359. --
  360. -- Dumping data for table `tb_userspasswordsrecoveries`
  361. --
  362.  
  363. LOCK TABLES `tb_userspasswordsrecoveries` WRITE;
  364. /*!40000 ALTER TABLE `tb_userspasswordsrecoveries` DISABLE KEYS */;
  365. INSERT INTO `tb_userspasswordsrecoveries` VALUES (1,7,'127.0.0.1',NULL,'2017-03-15 16:10:59'),(2,7,'127.0.0.1','2017-03-15 13:33:45','2017-03-15 16:11:18'),(3,7,'127.0.0.1','2017-03-15 13:37:35','2017-03-15 16:37:12');
  366. /*!40000 ALTER TABLE `tb_userspasswordsrecoveries` ENABLE KEYS */;
  367. UNLOCK TABLES;
  368.  
  369. --
  370. -- Dumping routines for database 'db_ecommerce'
  371. --
  372. /*!50003 DROP PROCEDURE IF EXISTS `sp_userspasswordsrecoveries_create` */;
  373. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  374. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  375. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  376. /*!50003 SET character_set_client = utf8 */ ;
  377. /*!50003 SET character_set_results = utf8 */ ;
  378. /*!50003 SET collation_connection = utf8_general_ci */ ;
  379. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  380. /*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  381. DELIMITER ;;
  382. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_userspasswordsrecoveries_create`(
  383. piduser INT,
  384. pdesip VARCHAR(45)
  385. )
  386. BEGIN
  387.  
  388. INSERT INTO tb_userspasswordsrecoveries (iduser, desip)
  389. VALUES(piduser, pdesip);
  390.  
  391. SELECT * FROM tb_userspasswordsrecoveries
  392. WHERE idrecovery = LAST_INSERT_ID();
  393.  
  394. END ;;
  395. DELIMITER ;
  396. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  397. /*!50003 SET character_set_client = @saved_cs_client */ ;
  398. /*!50003 SET character_set_results = @saved_cs_results */ ;
  399. /*!50003 SET collation_connection = @saved_col_connection */ ;
  400. /*!50003 DROP PROCEDURE IF EXISTS `sp_usersupdate_save` */;
  401. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  402. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  403. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  404. /*!50003 SET character_set_client = utf8 */ ;
  405. /*!50003 SET character_set_results = utf8 */ ;
  406. /*!50003 SET collation_connection = utf8_general_ci */ ;
  407. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  408. /*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  409. DELIMITER ;;
  410. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_usersupdate_save`(
  411. piduser INT,
  412. pdesperson VARCHAR(64),
  413. pdeslogin VARCHAR(64),
  414. pdespassword VARCHAR(256),
  415. pdesemail VARCHAR(128),
  416. pnrphone BIGINT,
  417. pinadmin TINYINT
  418. )
  419. BEGIN
  420.  
  421. DECLARE vidperson INT;
  422.  
  423. SELECT idperson INTO vidperson
  424. FROM tb_users
  425. WHERE iduser = piduser;
  426.  
  427. UPDATE tb_persons
  428. SET
  429. desperson = pdesperson,
  430. desemail = pdesemail,
  431. nrphone = pnrphone
  432. WHERE idperson = vidperson;
  433.  
  434. UPDATE tb_users
  435. SET
  436. deslogin = pdeslogin,
  437. despassword = pdespassword,
  438. inadmin = pinadmin
  439. WHERE iduser = piduser;
  440.  
  441. SELECT * FROM tb_users a INNER JOIN tb_persons b USING(idperson) WHERE a.iduser = piduser;
  442.  
  443. END ;;
  444. DELIMITER ;
  445. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  446. /*!50003 SET character_set_client = @saved_cs_client */ ;
  447. /*!50003 SET character_set_results = @saved_cs_results */ ;
  448. /*!50003 SET collation_connection = @saved_col_connection */ ;
  449. /*!50003 DROP PROCEDURE IF EXISTS `sp_users_delete` */;
  450. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  451. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  452. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  453. /*!50003 SET character_set_client = utf8 */ ;
  454. /*!50003 SET character_set_results = utf8 */ ;
  455. /*!50003 SET collation_connection = utf8_general_ci */ ;
  456. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  457. /*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  458. DELIMITER ;;
  459. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_users_delete`(
  460. piduser INT
  461. )
  462. BEGIN
  463.  
  464. DECLARE vidperson INT;
  465.  
  466. SELECT idperson INTO vidperson
  467. FROM tb_users
  468. WHERE iduser = piduser;
  469.  
  470. DELETE FROM tb_users WHERE iduser = piduser;
  471. DELETE FROM tb_persons WHERE idperson = vidperson;
  472.  
  473. END ;;
  474. DELIMITER ;
  475. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  476. /*!50003 SET character_set_client = @saved_cs_client */ ;
  477. /*!50003 SET character_set_results = @saved_cs_results */ ;
  478. /*!50003 SET collation_connection = @saved_col_connection */ ;
  479. /*!50003 DROP PROCEDURE IF EXISTS `sp_users_save` */;
  480. /*!50003 SET @saved_cs_client = @@character_set_client */ ;
  481. /*!50003 SET @saved_cs_results = @@character_set_results */ ;
  482. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  483. /*!50003 SET character_set_client = utf8 */ ;
  484. /*!50003 SET character_set_results = utf8 */ ;
  485. /*!50003 SET collation_connection = utf8_general_ci */ ;
  486. /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
  487. /*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
  488. DELIMITER ;;
  489. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_users_save`(
  490. pdesperson VARCHAR(64),
  491. pdeslogin VARCHAR(64),
  492. pdespassword VARCHAR(256),
  493. pdesemail VARCHAR(128),
  494. pnrphone BIGINT,
  495. pinadmin TINYINT
  496. )
  497. BEGIN
  498.  
  499. DECLARE vidperson INT;
  500.  
  501. INSERT INTO tb_persons (desperson, desemail, nrphone)
  502. VALUES(pdesperson, pdesemail, pnrphone);
  503.  
  504. SET vidperson = LAST_INSERT_ID();
  505.  
  506. INSERT INTO tb_users (idperson, deslogin, despassword, inadmin)
  507. VALUES(vidperson, pdeslogin, pdespassword, pinadmin);
  508.  
  509. SELECT * FROM tb_users a INNER JOIN tb_persons b USING(idperson) WHERE a.iduser = LAST_INSERT_ID();
  510.  
  511. END ;;
  512. DELIMITER ;
  513. /*!50003 SET sql_mode = @saved_sql_mode */ ;
  514. /*!50003 SET character_set_client = @saved_cs_client */ ;
  515. /*!50003 SET character_set_results = @saved_cs_results */ ;
  516. /*!50003 SET collation_connection = @saved_col_connection */ ;
  517. /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
  518.  
  519. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  520. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  521. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  522. /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  523. /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  524. /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  525. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
  526.  
  527. -- Dump completed on 2017-04-24 11:50:48
  528.  
  529. index.php
  530. <?php
  531.  
  532. require_once("vendor/autoload.php");
  533.  
  534. $app = new \Slim\Slim();
  535.  
  536. $app->config('debug', true);
  537.  
  538. $app->get('/', function() {
  539.  
  540. $sql = new gustavo\DB\Sql();
  541.  
  542. $results = $sql->select("SELECT * FROM tb_users");
  543.  
  544. echo json_encode($results);
  545.  
  546. });
  547.  
  548. $app->run();
  549.  
  550. ?>
  551. sql.php
  552.  
  553. <?php
  554.  
  555. namespace DB;
  556.  
  557. class Sql {
  558.  
  559. const HOSTNAME = "127.0.0.1:3306";
  560. const USERNAME = "root";
  561. const PASSWORD = "123456";
  562. const DBNAME = "db_ecommerce";
  563.  
  564. private $conn;
  565.  
  566. public function __construct()
  567. {
  568.  
  569. $this->conn = new \PDO(
  570. "mysql:dbname=".Sql::DBNAME.";host=".Sql::HOSTNAME,
  571. Sql::USERNAME,
  572. Sql::PASSWORD
  573. );
  574.  
  575. }
  576.  
  577. private function setParams($statement, $parameters = array())
  578. {
  579.  
  580. foreach ($parameters as $key => $value) {
  581.  
  582. $this->bindParam($statement, $key, $value);
  583.  
  584. }
  585.  
  586. }
  587.  
  588. private function bindParam($statement, $key, $value)
  589. {
  590.  
  591. $statement->bindParam($key, $value);
  592.  
  593. }
  594.  
  595. public function query($rawQuery, $params = array())
  596. {
  597.  
  598. $stmt = $this->conn->prepare($rawQuery);
  599.  
  600. $this->setParams($stmt, $params);
  601.  
  602. $stmt->execute();
  603.  
  604. }
  605.  
  606. public function select($rawQuery, $params = array()):array
  607. {
  608.  
  609. $stmt = $this->conn->prepare($rawQuery);
  610.  
  611. $this->setParams($stmt, $params);
  612.  
  613. $stmt->execute();
  614.  
  615. return $stmt->fetchAll(\PDO::FETCH_ASSOC);
  616.  
  617. }
  618.  
  619. }
  620.  
  621. ?>
  622.  
  623. composer.json
  624. {
  625. "name": "gustavo/ecommerce",
  626. "authors": [
  627. {
  628. "name": "gustavo solution",
  629. "email": "gustavonarede1@gmail.com"
  630. }
  631. ],
  632. "require": {
  633. "phpmailer/phpmailer":"5.2.22",
  634. "slim/slim":"2.0",
  635. "rain/raintpl":"3.0.0"
  636. },
  637. "autoload": {
  638. "psr-4": {
  639. "gustavo\\": "vendor\\gustavo\\php-classes\\src"
  640. }
  641. }
  642. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!