SCRUD

Generate SCRUD operations.

scrud selectscrud exportscrud createscrud parsescrud db_to_dbscrud csv_to_dbscrud insertscrud updatescrud mergescrud delete

scrud select <cmId> <tableName>

Description

Generate a select SCRUD operation.

Parameters

cmId: The connection id (SQL database) - string - required
tableName: The table name - string - required

admin
scrud select "demo_cm_mysql" "products";

mentdb
script create get "demo_cm_mysql.products.select" false 1
  (param
  	(var "[id]" {true} "description ..." is_null:false is_empty:false "example ...")
  )
  "Return rows from the table 'products' in JSON."
{
	
	try {
		
		#Connection ...;
		sql connect "session1" {cm get "demo_cm_mysql"};
		
		-> "[json_result]" (sql to json "session1" "products" (concat 
			"SELECT 
				`id`,
				`name`,
				`quantity`,
				`cat`,
				`desc`,
				`dtcreate`,
				`type`,
				`subtype`,
				`price`,
				`sale`,
				`weight`
			 FROM `products` 
			 WHERE 
				`id`=" (sql encode [id]) "
			 LIMIT 0, 100;"
		));
		
		#Disconnection ...;
		sql disconnect "session1";
		
		# Return the json;
		[json_result]
		
	} {

		#Close the connection;
		try {sql disconnect "session1"} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
		
	} "[err]";
	
} "Return a JSON Object";

scrud export <cmId> <tableName>

Description

Generate an export SCRUD operation.

Parameters

cmId: The connection id (SQL database) - string - required
tableName: The table name - string - required

admin
scrud export "demo_cm_mysql" "products";

mentdb
script create exe "demo_cm_mysql.products.export" false 1
  (param
  	(var "[directory]" {true} "The directory" is_null:false is_empty:false "/Users/jimmitry/Desktop")
  	(var "[filename]" {true} "The file name" is_null:false is_empty:false "test")
	(var "[format]" {type is_enum [format] "json,xml,csv,excel,excelx,html,pdf"} "the format (json|xml|csv|excel|excelx|html|pdf)" is_null:false is_empty:false "json")
  	(var "[id]" {true} "description ..." is_null:false is_empty:false "example ...")
  )
  "Export the table 'products'."
{
	
	try {
		
		#Connection ...;
		sql connect "session1" {cm get "demo_cm_mysql"};
		
		-> "[query]" (concat "SELECT 
				`id`,
				`name`,
				`quantity`,
				`cat`,
				`desc`,
				`dtcreate`,
				`type`,
				`subtype`,
				`price`,
				`sale`,
				`weight`
			 FROM `products` 
			 WHERE 
				`id`=" (sql encode [id]) "
			 LIMIT 0, 100;");
		
		switch ([format])
			("json") {
				file create (concat [directory] "/" [filename] ".json") (sql to json "session1" "products" (concat [query]));
			}
			("xml") {
				file create (concat [directory] "/" [filename] ".xml") (sql to xml "session1" "products" (concat [query]));
			}
			("csv") {
				file create (concat [directory] "/" [filename] ".csv") (sql to csv "session1" "products" (concat [query]) "," "'");
			}
			("excel") {
				sql to excel "session1" "products" (concat [query]) (concat [directory] "/" [filename] ".xls");
			}
			("excelx") {
				sql to excelx "session1" "products" (concat [query]) (concat [directory] "/" [filename] ".xlsx");
			}
			("html") {
				file create (concat [directory] "/" [filename] ".html") (sql to html "session1" "products" (concat [query]));
			}
			("pdf") {
				sql to pdf "session1" "products" (concat [query]) (concat [directory] "/" [filename] ".pdf");
			}
			{exception (1) ("Sorry, the export format must be 'json|xml|csv|excel|excelx|html|pdf'.");}
		;
		
		#Disconnection ...;
		sql disconnect "session1";
		
	} {

		#Close the connection;
		try {sql disconnect "session1"} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
		
	} "[err]";
	
} "Return nothing";

scrud create <cmId> <tableName>

Description

Generate a create SCRUD operation.

Parameters

cmId: The connection id (SQL database) - string - required
tableName: The table name - string - required

admin
scrud create "demo_cm_mysql" "products";

mentdb
CREATE TABLE `products` (
	`id` bigint(11) NOT NULL auto_increment,
	`name` varchar(45) NOT NULL ,
	`quantity` int(11) NOT NULL ,
	`cat` varchar(45) NULL DEFAULT NULL,
	`desc` longtext NULL DEFAULT NULL,
	`dtcreate` datetime NULL DEFAULT NULL,
	`type` char(1) NULL DEFAULT 'A',
	`subtype` enum('1','R','T') NULL DEFAULT NULL,
	`price` decimal(10,2) NULL DEFAULT NULL,
	`sale` binary(1) NULL DEFAULT NULL,
	`weight` double NULL DEFAULT NULL, 
	PRIMARY KEY (
	`id` 
	)
);

scrud parse <cmId> <tableName>

Description

Generate a select SCRUD operation to parse.

Parameters

cmId: The connection id (SQL database) - string - required
tableName: The table name - string - required

admin
scrud parse "demo_cm_mysql" "products";

mentdb
script create exe "demo_cm_mysql.products.parse_and_action" false 1
  (param
  	(var "[id]" {true} "description ..." is_null:false is_empty:false "example ...")
  )
  "Execute MQL action on the table 'products'."
{
	
	try {
		
		#Connection ...;
		sql connect "session1" {cm get "demo_cm_mysql"};
		
		-> "[json_result]" (sql parse "session1" "T" (concat 
			"SELECT 
				`id`,
				`name`,
				`quantity`,
				`cat`,
				`desc`,
				`dtcreate`,
				`type`,
				`subtype`,
				`price`,
				`sale`,
				`weight`
			 FROM `products` 
			 WHERE 
				`id`=" (sql encode [id]) "
			 LIMIT 0, 100;") {
			
			#Here the fields ...;
			[T_name];
			[T_quantity];
			[T_cat];
			[T_desc];
			[T_dtcreate];
			[T_type];
			[T_subtype];
			[T_price];
			[T_sale];
			[T_weight];
			
			#Here your MQL code ...;
			
		
		});
		
		#Disconnection ...;
		sql disconnect "session1";
		
		# Return the json;
		[json_result]
		
	} {

		#Close the connection;
		try {sql disconnect "session1"} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
		
	} "[err]";
	
} "Return a JSON result with all return lines.";

scrud db_to_db <cmId> <tableName>

Description

Generate SCRUD operations from db to db.

Parameters

cmId: The connection id (SQL database) - string - required
tableName: The table name - string - required

admin
scrud db_to_db "demo_cm_mysql" "products";

mentdb
...

scrud csv_to_db <cmId> <tableName> <csv_filePath> <csv_columnSeparator> <csv_quoteChar> <csv_forceColumnNames>

Description

Generate SCRUD operations from csv to db.

Parameters

cmId: The connection id (SQL database) - string - required
tableName: The table name - string - required
csv_filePath: The file path - string - required
csv_columnSeparator: The column separator - string - required
csv_quoteChar: The quote char - string - required
csv_forceColumnNames: To force the column name (can be empty) - string - required

admin
scrud csv_to_db "demo_cm_mysql" "products" "/Users/jimmitry/Desktop/file.csv" "," "'" "A,B,C";

mentdb
...

scrud insert <cmId> <tableName>

Description

Generate an insert SCRUD operation.

Parameters

cmId: The connection id (SQL database) - string - required
tableName: The table name - string - required

admin
scrud insert "demo_cm_mysql" "products";

mentdb
script create post "demo_cm_mysql.products.insert" false 1
  (param
  	(var "[id]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[name]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[quantity]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[cat]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[desc]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[dtcreate]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[type]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[subtype]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[price]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[sale]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[weight]" {true} "description ..." is_null:false is_empty:false "example ...")
  )
  "Insert a new element into the table 'products'."
{
	
	try {
		
		#Connection ...;
		sql connect "session1" {cm get "demo_cm_mysql"};
		
		-> "[result]" (sql dml "session1" (concat 
			"INSERT INTO `products` (
				`id`,
				`name`,
				`quantity`,
				`cat`,
				`desc`,
				`dtcreate`,
				`type`,
				`subtype`,
				`price`,
				`sale`,
				`weight`
			) VALUES (
				" (sql encode [id]) " ,
				" (sql encode [name]) " ,
				" (sql encode [quantity]) " ,
				" (sql encode [cat]) " ,
				" (sql encode [desc]) " ,
				" (sql encode [dtcreate]) " ,
				" (sql encode [type]) " ,
				" (sql encode [subtype]) " ,
				" (sql encode [price]) " ,
				" (sql encode [sale]) " ,
				" (sql encode [weight]) "
			);"
		));
		
		#Disconnection ...;
		sql disconnect "session1";
		
		# Return the json;
		[result]
		
	} {

		#Close the connection;
		try {sql disconnect "session1"} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
		
	} "[err]";
	
} "Return the number of impacted lines.";

scrud update <cmId> <tableName>

Description

Generate an update SCRUD operation.

Parameters

cmId: The connection id (SQL database) - string - required
tableName: The table name - string - required

admin
scrud update "demo_cm_mysql" "products";

mentdb
script create put "demo_cm_mysql.products.update" false 1
  (param
  	(var "[id]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[name]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[quantity]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[cat]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[desc]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[dtcreate]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[type]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[subtype]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[price]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[sale]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[weight]" {true} "description ..." is_null:false is_empty:false "example ...")
  )
  "Update an element into the table 'products'."
{
	
	try {
		
		#Connection ...;
		sql connect "session1" {cm get "demo_cm_mysql"};
		
		-> "[result]" (sql dml "session1" (concat 
			"UPDATE `products` SET 
				`name`=" (sql encode [name]) " ,
				`quantity`=" (sql encode [quantity]) " ,
				`cat`=" (sql encode [cat]) " ,
				`desc`=" (sql encode [desc]) " ,
				`dtcreate`=" (sql encode [dtcreate]) " ,
				`type`=" (sql encode [type]) " ,
				`subtype`=" (sql encode [subtype]) " ,
				`price`=" (sql encode [price]) " ,
				`sale`=" (sql encode [sale]) " ,
				`weight`=" (sql encode [weight]) "
			WHERE 
				`id`=" (sql encode [id]) "
			;"
		));
		
		#Disconnection ...;
		sql disconnect "session1";
		
		# Return the json;
		[result]
		
	} {

		#Close the connection;
		try {sql disconnect "session1"} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
		
	} "[err]";
	
} "Return the number of impacted lines.";

scrud merge <cmId> <tableName>

Description

Generate a merge SCRUD operation.

Parameters

cmId: The connection id (SQL database) - string - required
tableName: The table name - string - required

admin
scrud merge "demo_cm_mysql" "products";

mentdb
script create post "demo_cm_mysql.products.merge" false 1
  (param
  	(var "[id]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[name]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[quantity]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[cat]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[desc]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[dtcreate]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[type]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[subtype]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[price]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[sale]" {true} "description ..." is_null:false is_empty:false "example ...")
  	(var "[weight]" {true} "description ..." is_null:false is_empty:false "example ...")
  )
  "Insert a new element into the table 'products'."
{
	
	try {
		
		#Connection ...;
		sql connect "session1" {cm get "demo_cm_mysql"};
		
		-> "[result]" (sql dml "session1" (concat 
			"INSERT INTO `products` (
				`id`,
				`name`,
				`quantity`,
				`cat`,
				`desc`,
				`dtcreate`,
				`type`,
				`subtype`,
				`price`,
				`sale`,
				`weight`
			) VALUES (
				" (sql encode [id]) " ,
				" (sql encode [name]) " ,
				" (sql encode [quantity]) " ,
				" (sql encode [cat]) " ,
				" (sql encode [desc]) " ,
				" (sql encode [dtcreate]) " ,
				" (sql encode [type]) " ,
				" (sql encode [subtype]) " ,
				" (sql encode [price]) " ,
				" (sql encode [sale]) " ,
				" (sql encode [weight]) "
			) ON DUPLICATE KEY UPDATE 
				`name`=" (sql encode [name]) " ,
				`quantity`=" (sql encode [quantity]) " ,
				`cat`=" (sql encode [cat]) " ,
				`desc`=" (sql encode [desc]) " ,
				`dtcreate`=" (sql encode [dtcreate]) " ,
				`type`=" (sql encode [type]) " ,
				`subtype`=" (sql encode [subtype]) " ,
				`price`=" (sql encode [price]) " ,
				`sale`=" (sql encode [sale]) " ,
				`weight`=" (sql encode [weight]) ";"
		));
		
		#Disconnection ...;
		sql disconnect "session1";
		
		# Return the json;
		[result]
		
	} {

		#Close the connection;
		try {sql disconnect "session1"} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
		
	} "[err]";
	
} "Return the number of impacted lines.";

scrud delete <cmId> <tableName>

Description

Generate a delete SCRUD operation.

Parameters

cmId: The connection id (SQL database) - string - required
tableName: The table name - string - required

admin
scrud delete "demo_cm_mysql" "products";

mentdb
script create delete "demo_cm_mysql.products.delete" false 1
  (param
  	(var "[id]" {true} "description ..." is_null:false is_empty:false "example ...")
  )
  "Delete an element from the table 'products'."
{
	
	try {
		
		#Connection ...;
		sql connect "session1" {cm get "demo_cm_mysql"};
		
		-> "[result]" (sql dml "session1" (concat 
			"DELETE FROM `products`
			WHERE 
				`id`=" (sql encode [id]) "
			;"
		));
		
		#Disconnection ...;
		sql disconnect "session1";
		
		# Return the json;
		[result]
		
	} {

		#Close the connection;
		try {sql disconnect "session1"} {} "[sub_err]";

		#Generate an error;
		exception (1) ([err]);
		
	} "[err]";
	
} "Return the number of impacted lines.";