1. Version Inroduction
[Release date: November 17, 2023]
IvorySQL 3.0 is based on PostgreSQL 16.0 includes various fixes from PostgreSQL 16.0, for more detailed updates and bug fixes in PostgreSQL 16.0, see the official PostgreSQL 16.0 Release Notes .
2. Updates on Version 3.0 vs 2.3
IvorySQL 3.0 represents a significant architectural evolution, marking a departure from the usage patterns of version 2.3. It’s important to note that some functionalities available in 2.3 are not yet supported in the new version. Here’s a breakdown of the key feature differences between these two versions:
Functional modules |
function |
IvorySQL-2.3 |
IvorySQL-3.0 |
Built-in data types |
char |
not support |
support |
varchar |
support |
support |
|
varchar2 |
support |
support |
|
number |
not support |
support |
|
binary_float |
not support |
support |
|
binary_double |
not support |
support |
|
date |
support |
support |
|
timestamp |
not support |
support |
|
timestamp with time zone |
not support |
support |
|
timestamp with local time zone |
not support |
support |
|
interval year to month |
not support |
support |
|
interval day to second |
not support |
support |
|
raw |
not support |
support |
|
long |
not support |
support |
|
Built-in functions |
char |
not support |
support |
sysdate |
support |
support |
|
systimestamp |
support |
support |
|
add_months |
support |
support |
|
last_day |
support |
support |
|
next_day |
support |
support |
|
months_between |
support |
support |
|
current_date |
not support |
support |
|
current_timestamp |
not support |
support |
|
new_time |
support |
support |
|
tz_offset |
not support |
support |
|
trunc |
support |
support |
|
instr |
not support |
support |
|
instrb |
not support |
support |
|
substr |
not support |
support |
|
substrb |
support |
support |
|
trim |
not support |
support |
|
ltrim |
not support |
support |
|
rtrim |
not support |
support |
|
length |
not support |
support |
|
lengthb |
not support |
support |
|
rawtohex |
not support |
support |
|
replace |
not support |
support |
|
regexp_replace |
not support |
support |
|
regexp_substr |
not support |
support |
|
regexp_instr |
not support |
support |
|
regexp_like |
not support |
support |
|
to_number |
support |
support |
|
to_char |
support |
support |
|
to_date |
support |
support |
|
to_timestamp |
support |
support |
|
to_timestamp_tz |
support |
support |
|
to_yminterval |
not support |
support |
|
to_dsinterval |
support |
support |
|
numtodsinterval |
support |
support |
|
numtoyminterval |
support |
support |
|
localtimestamp |
not support |
support |
|
new_time |
not support |
support |
|
from_tz |
support |
support |
|
sys_extract_utc |
support |
support |
|
sessiontimezone |
support |
support |
|
hextoraw |
not support |
support |
|
uid |
not support |
support |
|
USERENV |
not support |
support |
|
NLS parameter |
NLS_LENGTH_SEMANTICS |
not support |
support |
NLS_DATE_FORMAT |
not support |
support |
|
NLS_TIMESTAMP_FORMAT |
not support |
support |
|
NLS_TIMESTAMP_TZ_FORMAT |
not support |
support |
|
Function |
Syntax compatibility is supported, and the OUT parameter is supported |
support |
support |
Procedure |
Syntax compatibility is supported, and the OUT parameter is supported |
support |
support |
Anonymous block |
Syntax compatibility is supported, and the OUT parameter is supported |
not support |
support |
Nested subroutines |
Nested stored procedures, functions, and so on are supported |
not support |
support |
Merge |
The Merge function of PG and the Merge function compatible with Oralce syntax are supported |
not support |
support |
q` |
Compatible escapes are supported |
support |
support |
Keyword processing |
Supports the processing of keywords in the database |
not support |
support |
Object case conversion |
All uppercase plus double quotation marks are converted to lowercase |
not support |
support |
All lowercase plus double quotation marks are converted to uppercase |
not support |
support |
|
The mixed case plus double quotation marks remain the same |
not support |
support |
|
Without double quotation marks (default), all are lowercase |
not support |
support |
|
Search Path |
In compatibility mode, the default search is sys mode, and then the pg_catalog mode |
not support |
support |
Empty strings |
Oracle-compatible conversion of empty strings to NULL is supported |
not support |
support |
Lexical parser separation |
Part of the 3.0 framework |
not support |
support |
package |
support |
not support |
|
Globally unique indexes |
support |
support |
|
GUC Switch to oracle or pg |
support |
support |
|
Hierarchical queries |
support |
not support |
|
NANVL |
support |
not support |
|
GREATEST |
support |
not support |
|
LEAST |
support |
not support |
|
ADD_DAYS_TO_TIMESTAMP |
support |
not support |
|
DAYS_BETWEEN |
support |
not support |
|
DAYS_BETWEEN_TMTZ |
support |
not support |
|
DBTIMEZONE |
support |
not support |
|
TO_MULTI_BYTE |
support |
not support |
|
TO_SINGLE_BYTE |
support |
not support |
|
INTERVAL_TO_SECONDS |
support |
not support |
|
HEX_TO_DECIMAL |
support |
not support |
|
TO_BINARY_DOUBLE |
support |
not support |
|
TO_BINARY_FLOAT |
support |
not support |
|
BIN_TO_NUM |
support |
not support |
4. Enhancements
4.1. ivorysql frame
-
Add double Parser to support different database parsers Problem details
-
Added dual ports to support different database port numbers Problem details
-
Add initdb -m to support postgres mode or oracle mode Problem details
4.2. Compatible with SQL
-
Compatible with Oracle Merge Command Problem details
-
Compatible with Oracle Q escaping Problem details
-
Compatible with oracle like Problem details
4.3. Compatible with PL/SQL
-
Addresses an issue with PL/SQL creation functions/stored procedures Problem details
-
Compatible with Oracle anonymous blocks Problem details
-
Creating a function or procedure in SQL parser supports nested subprocedures Problem details
-
Nested child processes and functions IS/As do not need to be declared Problem details
4.4. Other
-
Add meson compilation to action Problem details
-
Support for meson compilation Problem details
-
Add compatible test cases Problem details
-
Add contrib regression Problem details
-
Compatible with btree_gist indexes Problem details
-
Compatible with btree_gin indexes Problem details
-
Add Oracle data type GIN indexing operations Problem details
-
Add the Oracle data type Gist Index Operation Problem details
-
Compatible with Oracle built-in data types and built-in functions Problem details
-
Add the plisql extension Problem details
Description:For more information about the new features, please refer to the feature list in this document center
5. Fixed issue
-
After compiling with meson, the initdb execution fails Problem details
-
The operator result for a character type null value is incorrect Problem details
-
An error occurred while restoring the backup Problem details
-
ivorysql_ora some test cases fail Problem details
-
The NLS parameter specifies that under the three relationships between the ff precision and the accuracy specified in the table, the data processing exceeding the length is inconsistent Problem details
-
The data processing after the data in the DD HH.MI, SS AM data in the date format is inconsistent with Oracle Problem details
-
There are problems with the date format, the partial digit check for each part Problem details
-
NLS related parameter verification issues Problem details
-
Solve the problem that the NLS parameter is set to 12-hour clock, and the default rule for completing AM/PM keywords is inconsistent with Oracle Problem details
-
The DEFAULTED field value in the function/stored procedure xx_arguments view created with default values is an issue of N Problem details
-
Functions/stored procedures without permissions can be viewed in all_procedures/all_arguments/all_source views Problem details
-
When the self-incrementing column type is numer type and the precision is specified, when a null value is inserted by default on null, it is not the inserted concrete sequence value, but the inserted null value Problem details
6. Source Code
IvorySQL contains 2 main code repositories, the database IvorySQL code bin, and the IvorySQL web bin.
-
IvorySQL code bin: https://github.com/IvorySQL/IvorySQL
-
IvorySQL web bin: https://github.com/IvorySQL/Ivory-www
7. Contributors
The following individuals have contributed to this release as patch authors, committers, reviewers, testers, or issue reporters.
-
IvorySQL Pro development & testing team
-
Yang Tan
-
Jie Wang
-
Shuainan Mu
-
Hongyuan Zhang
-
Cary Huang
-
Grant Zhou
-
David Zhang
-
Shoubo Wang
-
Jiao Ren
-
Zheng Liu
-
Zhekai Xiao
-
Huajian Jin
-
Lily Wang
-
Jinzhou Song
-
Leo X.M. Zeng
-
Shaoan Yan
-
M.Imran Zaheer
-
Yunhe Xu
-
Hao Wang
-
Miss Dong
-
Weibo Han