....I..... [ZBXNEXT-6285] modified Oracle initial scheme creating and image loading in to the BLOB fields

3 files changed, 63 insertions, 22 deletions

diff --git a/misc/images/images_oracle_end.txt b/misc/images/images_oracle_end.txt
index feb1540d88f..70cf2f6e6a0 100644
--- a/misc/images/images_oracle_end.txt
+++ b/misc/images/images_oracle_end.txt
@@ -1,6 +1,3 @@
 END;
 /
-
-DROP PROCEDURE LOAD_IMAGE;
-
-DROP DIRECTORY image_dir;
+DROP FUNCTION base64decode;
\ No newline at end of file
diff --git a/misc/images/images_oracle_start.txt b/misc/images/images_oracle_start.txt
index 99fadb4bdbf..ba289d0a134 100644
--- a/misc/images/images_oracle_start.txt
+++ b/misc/images/images_oracle_start.txt
@@ -1,19 +1,49 @@
-CREATE OR REPLACE DIRECTORY image_dir AS '/home/zabbix/zabbix/create/output_png'
-/
+CREATE OR REPLACE FUNCTION base64decode(p_clob CLOB) RETURN BLOB
+    IS
+        C_CHUNK_SIZE CONSTANT INTEGER := 12000; -- should be a multiple of 4
+        C_NON_BASE64_SYM_PATTERN CONSTANT VARCHAR2(20) := '[^A-Za-z0-9+/]';
+        l_chunk_buf VARCHAR2(12000);
+        l_chunk_b64_buf RAW(9000);
+        l_chunk_offset INTEGER := 1;
+        l_chunk_size INTEGER;
+        l_blob BLOB;
 
-CREATE OR REPLACE PROCEDURE LOAD_IMAGE (IMG_ID IN NUMBER, IMG_TYPE IN NUMBER, IMG_NAME IN VARCHAR2, FILE_NAME IN VARCHAR2)
-IS
-	TEMP_BLOB BLOB := EMPTY_BLOB();
-	BFILE_LOC BFILE;
-BEGIN
-	DBMS_LOB.CREATETEMPORARY(TEMP_BLOB,TRUE,DBMS_LOB.SESSION);
-	BFILE_LOC := BFILENAME('IMAGE_DIR', FILE_NAME);
-	DBMS_LOB.FILEOPEN(BFILE_LOC);
-	DBMS_LOB.LOADFROMFILE(TEMP_BLOB, BFILE_LOC, DBMS_LOB.GETLENGTH(BFILE_LOC));
-	DBMS_LOB.FILECLOSE(BFILE_LOC);
-	INSERT INTO IMAGES VALUES (IMG_ID, IMG_TYPE, IMG_NAME, TEMP_BLOB);
-	COMMIT;
-END LOAD_IMAGE;
-/
+        FUNCTION get_next_full_base64_chunk(l_data CLOB, p_cur_pos IN OUT INTEGER, p_desired_size INTEGER, p_cur_size IN OUT INTEGER) RETURN VARCHAR2 IS
+            l_res VARCHAR2(12000);
+            l_tail_desired_size INTEGER;
+        BEGIN
+            l_res := dbms_lob.substr(l_data, p_desired_size, p_cur_pos);
+            p_cur_pos := p_cur_pos + p_desired_size;
+            IF l_res IS NULL THEN
+                RETURN NULL;
+            END IF;
+
+            l_res := regexp_replace(l_res, C_NON_BASE64_SYM_PATTERN, '');
+            p_cur_size := p_cur_size + length(l_res);
 
+            l_tail_desired_size := 4 - mod(p_cur_size, 4);
+            IF l_tail_desired_size = 4 THEN
+                RETURN l_res;
+            ELSE
+                RETURN l_res || get_next_full_base64_chunk(l_data, p_cur_pos, l_tail_desired_size, p_cur_size);
+            END IF;
+        END;
+
+    BEGIN
+        dbms_lob.createtemporary(l_blob, false);
+
+        WHILE true
+        LOOP
+            l_chunk_size := 0;
+            l_chunk_buf := get_next_full_base64_chunk(p_clob, l_chunk_offset, C_CHUNK_SIZE, l_chunk_size);
+            EXIT WHEN l_chunk_buf IS NULL;
+            l_chunk_b64_buf := utl_encode.base64_decode(utl_raw.cast_to_raw(l_chunk_buf));
+            dbms_lob.writeappend(l_blob, utl_raw.length(l_chunk_b64_buf), l_chunk_b64_buf);
+        END LOOP;
+
+        RETURN l_blob;
+    END;
+/
+DECLARE
+	l_clob CLOB;
 BEGIN
diff --git a/misc/images/png_to_sql.sh b/misc/images/png_to_sql.sh
index 935a2525761..4f3d594ffb4 100755
--- a/misc/images/png_to_sql.sh
+++ b/misc/images/png_to_sql.sh
@@ -13,6 +13,9 @@ imagefile_pgsql="$sqlbasedir/postgresql/$imagefile"
 imagefile_sqlite3="$sqlbasedir/sqlite3/$imagefile"
 imagefile_oracle="$sqlbasedir/oracle/$imagefile"
 
+oracle_sring_max=2048
+oracle_line_max=15
+
 for imagefile in "$imagefile_mysql" "$imagefile_pgsql" "$imagefile_sqlite3" "$imagefile_oracle"; do
 	[[ -s "$imagefile" ]] && {
 		echo "Non-empty $imagefile already exists, stopping"
@@ -32,16 +35,27 @@ for imagefile in $pngdir/*.png; do
 	((imagesdone++))
 	imagename="$(basename "${imagefile%.png}")"
 	image_data=$(hexdump -ve '"" 1/1 "%02X"' "$imagefile")
+	base64 -w$oracle_sring_max "$imagefile" > tmp_b64
+	split -l$oracle_line_max tmp_b64 tmp_b64
+	rm -rf tmp_b64
+
 	# ----- MySQL
-	echo "INSERT INTO images (imageid,imagetype,name,image) VALUES ($imagesdone,1,'$imagename',0x$image_data);" >> "$imagefile_mysql"
+	echo "INSERT INTO 'images' ('imageid','imagetype','name','image') VALUES ($imagesdone,1,'$imagename',0x$image_data);" >> "$imagefile_mysql"
 	# ----- PostgreSQL
 	echo "INSERT INTO images (imageid,imagetype,name,image) VALUES ($imagesdone,1,'$imagename',decode('$image_data','hex'));" >> "$imagefile_pgsql"
 	# ----- Oracle
-	echo -e "\tLOAD_IMAGE($imagesdone,1,'$imagename','$imagefile');" >> "$imagefile_oracle"
+	echo -e "\tl_clob := EMPTY_CLOB();" >> "$imagefile_oracle"
+	for oracle_imagefile in tmp_b64*; do
+		image_data_oracle=$(cat "$oracle_imagefile")
+		echo -e "\tl_clob := l_clob || '$image_data_oracle';" >> "$imagefile_oracle"
+		rm -rf $oracle_imagefile
+	done
+	echo -e "\tINSERT INTO images VALUES ($imagesdone,1,'$imagename',base64decode(l_clob));"  >> "$imagefile_oracle"
 	# ----- SQLite
 	echo "INSERT INTO images (imageid,imagetype,name,image) VALUES ($imagesdone,1,'$imagename','$image_data');" >> "$imagefile_sqlite3"
 
 	echo -ne "\b\b\b\b$[$imagesdone*100/$imagecount]% "
+	
 done
 cat images_oracle_end.txt >> "$imagefile_oracle"
 echo