mpsql command

Commands for working with SQL databases.

Main Argument

DATABASE

Required. Database name to connect to.

Main Options

These main options provide the information needed to establish a connection to the database.

-u <STRING>, --user=<STRING>

Username to connect to the database. Environment variable is MACPIE_MYSQL_USER.

-p <STRING>, --password=<STRING>

Password to connect to the database. Environment variable is MACPIE_MYSQL_PWD.

-P <INTEGER>, --port=<INTEGER>

Port number to use for connection. Default is 3306. Environment variable is MYSQL_TCP_PORT.

-h <STRING>, --host=<STRING>

Host address of the database. Default is localhost. Environment variable is MYSQL_HOST.

mpsql createtable

This command generates the DROP TABLE and CREATE TABLE statements for the specified database table.

Usage

$ mpsql -u USERNAME -p DATABASE createtable faq

Remember you can also set environment variables so that you don’t have to include certain parameters in the command each time you use it. For example, if you set the MACPIE_MYSQL_USER and MACPIE_MYSQL_PWD environment variables, you can simplify the above command like so:

$ mpsql DATABASE createtable TABLENAME

Arguments

TABLENAME

Required. The table for which you want to generate DROP TABLE and CREATE TABLE statements.

Output

DROP TABLE and CREATE TABLE statements for the specified table.

Examples

1. To generate the drop/create statements for the FAQ table:

   $ mpsql lava_mac_dev createtable faq
   

   Output:

   DROP TABLE IF EXISTS `faq`;
   CREATE TABLE `faq` (
   `InstrID` int(11) NOT NULL,
   `Checks` smallint(6) DEFAULT NULL,
   `Tax` smallint(6) DEFAULT NULL,
   `Shop` smallint(6) DEFAULT NULL,
   `Play` smallint(6) DEFAULT NULL,
   `Stove` smallint(6) DEFAULT NULL,
   `Meal` smallint(6) DEFAULT NULL,
   `Events` smallint(6) DEFAULT NULL,
   `Attn` smallint(6) DEFAULT NULL,
   `Appt` smallint(6) DEFAULT NULL,
   `Travel` smallint(6) DEFAULT NULL,
   `FAQTot` smallint(6) DEFAULT NULL,
   `CareID` int(11) DEFAULT NULL,
   `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   `TotalOnly` tinyint(1) NOT NULL DEFAULT '0',
   `Audit_Created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `Audit_EffDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `Audit_ExpDate` datetime NOT NULL DEFAULT '9999-09-09 00:00:00',
   `Audit_HostName` varchar(50) DEFAULT NULL,
   `Audit_Username` varchar(50) DEFAULT NULL,
   PRIMARY KEY (`InstrID`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

API

mpsql createtable

mpsql createtable [OPTIONS] TABLENAME

Arguments

TABLENAME

Required argument

mpsql createproc

This command generates the DROP PROCEDURE and CREATE PROCEDURE statements for the specified database table.

Usage

$ mpsql -u USERNAME -p DATABASE createproc PROCNAME

Arguments

PROCNAME

Required. The stored procedure name for which you want to generate DROP PROCEDURE and CREATE PROCEDURE statements.

Output

DROP PROCEDURE and CREATE PROCEDURE statements for the specified proc.

Examples

1. To generate the drop/create statements for the lq_set_linkdata_multiple stored proc:

   $ mpsql lava_mac_dev createproc lq_set_linkdata_multiple
   

   Output:

   -- -----------------------------------------------------------------
   -- procedure lq_set_linkdata_multiple
   -- -----------------------------------------------------------------
   
   DROP PROCEDURE IF EXISTS `lq_set_linkdata_multiple`;
   
   DELIMITER $$
   
   CREATE DEFINER=`root`@`localhost` PROCEDURE `lq_set_linkdata_multiple`(user_name varchar(50), host_name varchar(25), linkdata_list varchar(16000))
   BEGIN
   
   -- Ex. call lq_set_linkdata_multiple(null,null,'(1,"2019-03-02",2),(3,"2018-04-05",4),(0,"9999-9-9",0),');
   
   SET @s = CONCAT('INSERT INTO `temp_linkdata1` (`pidn`, `link_date`, `link_id`) VALUES ', linkdata_list, ';');
   PREPARE stmt1 FROM @s;
   EXECUTE stmt1;
   DEALLOCATE PREPARE stmt1;
   
   END
   
   $$
   DELIMITER ;
   

API

mpsql createproc

mpsql createproc [OPTIONS] PROCNAME

Arguments

PROCNAME

Required argument