Integrate nozbe with MySQL

nozbe?

Nozbe is a project management software. It has both free and premium versions. There are 2 types of accounts, a personal account where the user can have up to 5 projects and a business account where the user can have up to 30 projects.

It is an online task management system that syncs with your mobile devices. It is designed to keep your team organized and productive.

It is available in iOS and Android, but it also has third-party integrations with Slack, and Evernote.

MySQL?

MySQL is a relational database management system (RDBMS. that runs as a server providing multi-user access to a number of databases. MySQL was founded by Michael Widenius and David Axmark in 1995. Originally developed by Widenius's company MySQL AB, which was later acquired by Sun Microsystems and now owned by Oracle Corporation.

The latest version of MySQL is 8.0.12. The first stable release of MySQL was 1.0. Its current version is 5.7.15.

Integration of nozbe and MySQL

Nozbe uses MySQL as its back-end database. Nozbe uses MySQL for storing its data and configuring other settings. Before integrating nozbe and MySQL, it requires that you have backed up your MySQL database already. This is important because during the process of integrating nozbe and MySQL, your data will be changed or removed altogether. The steps for integrating nozbe and MySQL can be summarized into 5 major steps:

Create a new Nozbe App in MySQL Create a new User for Nozbe Create a new Nozbe Application in nozbe Setup Nozbe App in Nozbe Setup Nozbe Application in Nozbe's Settings Form

Step 1. Create a new Nozbe App in the MySQL Database

To create an app in the MySQL database, you need to use the fplowing SQL query:

CREATE DATABASE IF NOT EXISTS nozbe_app DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; USE nozbe_app; CREATE TABLE tasks ( id int(11. NOT NULL AUTO_INCREMENT, owner_id int(11. NOT NULL, task_name varchar(255. NOT NULL, comment varchar(255. DEFAULT NULL, due_date date DEFAULT NULL, status varchar(20. DEFAULT NULL, percentage smallint(5. DEFAULT NULL, completed tinyint(1. DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY unique_name (task_name,`owner_id`,`status`,`completed`,`percentage`. USING BTREE . ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; INSERT INTO tasks VALUES (NULL,1,'New feature','Done',NULL,'100',NULL); INSERT INTO tasks VALUES (NULL,1,'Bug Fix','Done',NULL,'90',NULL); INSERT INTO tasks VALUES (NULL,1,'Documentation','Not Started',NULL,'0',NULL); SELECT * FROM tasks; DROP TABLE IF EXISTS nozbe_account.`accounts`; CREATE TABLE IF NOT EXISTS nozbe_account.`accounts` ( id bigint(20. unsigned NOT NULL AUTO_INCREMENT, name varchar(255. NOT NULL, email varchar(255. NOT NULL, password varchar(255. NOT NULL, user_type varchar(10. DEFAULT NULL, date_registered datetime default NULL, last_login_at datetime default NULL, last_login_ip varchar(15. DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY email (email,`user_type`,`date_registered`,`last_login_at`,`last_login_ip`, LENGTH(email)), INDEX index_email__unique_name__date_registered__last_login_at__last_login_ip__length__user_type__varchar__BIGINT___BIGINT___BIGINT___BIGINT___varchar__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__BIGINT__varchar___BIGINT___BIGINT___BIGINT___varchar___BIGIN',PRIMARY); SELECT * FROM `tasks;

Step 2. Create a new User for Nozbe

You have to use the fplowing SQL query to create a user:

CREATE USER 'nozbe'@'localhost' IDENTIFIED BY 'mypassword'; GRANT USAGE ON . TO 'nozbe'@'localhost'; FLUSH PRIVILEGES; SELECT * FROM nozbe-accounts; GRANT ALL ON nozbe.* TO 'nobody'@'localhost'; FLUSH PRIVILEGES; UPDATE nozbe-accounts SET user_type = 'default' WHERE id = 0; ALTER TABLE nozbe-accounts ADD unique index email (email); ALTER TABLE nozbe-accounts ADD unique index name (name); ALTER TABLE nozbe-accounts ADD unique index user_type (user_type); ALTER TABLE nozbe-accounts ADD unique index password (password); ALTER TABLE nozbe-accounts ADD unique index date_registered (date_registered); ALTER TABLE nozbe-accounts ADD unique index last_login (last_login); ALTER TABLE nozbe-accounts ADD unique index last_login_ip (last_login_ip); CREATE TRIGGER insertTrigger ON tasks AFTER INSERT AS SELECT id FROM nozbe-accounts WHERE id = NEW.ownerId; UPDATE nozbe-accounts SET id = NEW.id WHERE id = NEW.ownerId; UPDATE nozbe-accounts SET created = NOW(. WHERE id = NEW.ownerId; UPDATE nozbe-accounts SET lastLoginAt = CURRENT_TIMESTAMP WHERE id = NEW.ownerId; UPDATE nozbe-accounts SET lastLoginAt = CURRENT_TIMESTAMP WHERE id = NEW.id; UPDATE nozbe-accounts SET username = NEW.username WHERE id = NEW.ownerId; UPDATE nozbe-accounts SET firstName = NEW.firstName WHERE id = NEW.ownerId; UPDATE nozbe-accounts SET lastName = NEW.lastName WHERE id = NEW.ownerId; UPDATE nozbe-accounts SET fullName = ucase(NEW.fullName. || '.' || lcase(NEW.lastName. WHERE id = NEW.ownerId; UPDATE costumes SET costumeID = NEW.id WHERE creatorID = NEW.ownerId AND costumeID != NEW.id AND costumeID != 0; UPDATE costumes SET creatorID = NEW.id WHERE costumeID = NEW.id AND costumeID != NEW.id AND costumeID != 0; UPDATE costumes SET creatorID = NEW.creatorID WHERE costumeID = NEW.id AND costumeID != NEW.id AND costumeID != 0; UPDATE costumes SET createdAt = CURRENT_TIMESTAMP WHERE costumeID = NEW.id AND costumeID != NEW.id AND costumeID != 0; UPDATE costumes SET lastAccessedAt = CURRENT_TIMESTAMP WHERE costumeID = NEW.id AND costumeID != NEW.id AND costumeID != 0; UPDATE tasks SET ownerID = NEW.id WHERE ownerID = 1 AND taskStatus != 'done'; COMMIT; SELECT * FROM tasks ORDER BY ownerId DESC LIMIT 20; DR

The process to integrate nozbe and MySQL may seem complicated and intimidating. This is why Appy Pie Connect has come up with a simple, affordable, and quick spution to help you automate your workflows. Click on the button below to begin.