####
# Testcase for mariadb geometry bug
#
# Procedure: TestDataGeometry
# - simple snippet from more complex procedure to show bug
#
# Table: testg
# - simple table to show trigger problem
#
# Trigger: testg_before_insert
# - to demonstrate the bug
#
# NOTE! The gemotry column is filled with junk, this is probably at least not good...

USE test;

DELIMITER $$
DROP PROCEDURE IF EXISTS TestDataGeometry$$
CREATE PROCEDURE TestDataGeometry(_data text, out g_ geometrycollection) COMMENT 'Sets Geometry from data string...'
BEGIN
  SET g_=GeomFromText(concat('POLYGON((',_data,'))'));
  IF g_ is null THEN SET g_=GeomFromText(concat('LINESTRING(',_data,')')); END IF;
  IF g_ is null THEN SET g_=GeomFromText(concat('MULTILINESTRING((',_data,'))')); END IF;
  IF g_ is null THEN SET g_=GeomFromText(concat('MULTIPOLYGON(((',_data,')))')); END IF;
END;$$
DELIMITER ;


#- DROP TABLE IF EXISTS testg;
CREATE TABLE IF NOT EXISTS testg (
  id int not null auto_increment,
  data text,
  g geometry,
  PRIMARY KEY (id)
) ENGINE=MyISAM COMMENT='Test Case Trigger/Geometry function';
  


##
# Trigger: testg_before_insert
DELIMITER $$
DROP TRIGGER IF EXISTS testg_before_insert$$
CREATE TRIGGER testg_before_insert BEFORE INSERT ON testg FOR EACH ROW BEGIN
  DECLARE g Geometry;

# Below line works on all mysql version I tested, but not in mariadb
  CALL TestDataGeometry(NEW.data, NEW.g);
  
# Below lines works on all mysql version I tested, but not in mariadb
#  CALL TestDataGeometry(NEW.data, g);
#  SET NEW.g=g;
  
# This is a workaround, that works both mysql and mariabd
# SET @g=null;
# CALL TestDataGeometry(NEW.data, @g);
# SET NEW.g=@g;
  
END;$$
DELIMITER ;


##
# Test by inserting some data
INSERT INTO testg (data) VALUES ('11.960453 57.565838,11.925262 57.567955,11.921486 57.555248,11.960882 57.554189,11.960453 57.565838');

##
# Look!
SELECT id,data,AsText(g) FROM testg;


