UX数据库 数据库初始化及数据库适配

数据库初始化

1、设置密码

[uxdb@kylin bin]$ ./initdb -W -D dbhome002/ -r compatible
The files belonging to this database system will be owned by user "UXDB".
This user must also own the server process.

The databse cluster user will be set to "UXDB".
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.
Full Database encryption is disabled.

Enter new superuser password: 
Enter it again: 

creating directory dbhome002 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    ./uxdb -D dbhome002/
or
    ./ux_ctl -D dbhome002/ -l logfile start
and
start client using:

    ./uxsql -d UXDB -U UXDB
or
    ./uxsql -d UXDB --running-mode=compatible

2、配置端口

在 # - Connection Settings - 位置下配置端口即可，此处端口为4444

[uxdb@kylin bin]$ vi dbhome002/uxsinodb.conf 
# -----------------------------
# UXsinoDB configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the UXsinoDB documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "ux_ctl reload", or execute
# "SELECT ux_reload_conf()".  Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "uxdb -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                TB = terabytes                     h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or UXDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'           # use data in another directory
                                        # (change requires restart)
#hba_file = 'ConfigDir/ux_hba.conf'     # host-based authentication file
                                        # (change requires restart)
#ident_file = 'ConfigDir/ux_ident.conf' # ident configuration file
                                        # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = ''                 # write an extra PID file
                                        # (change requires restart)


#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 4444                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/tmp'       # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
"dbhome002/uxsinodb.conf" 781L, 27881C written

3、启动数据库

[uxdb@kylin bin]$ ux_ctl -D dbhome002 start
waiting for server to start....................2022-07-12 11:31:04.800 CST [91977] LOG:  starting UXsinoDB 2.1.1.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
2022-07-12 11:31:04.801 CST [91977] LOG:  listening on IPv4 address "0.0.0.0", port 4444
2022-07-12 11:31:04.801 CST [91977] LOG:  listening on IPv6 address "::", port 4444
2022-07-12 11:31:04.803 CST [91977] LOG:  listening on Unix socket "/tmp/.s.UXSQL.4444"
2022-07-12 11:31:04.820 CST [91977] LOG:  redirecting log output to logging collector process
2022-07-12 11:31:04.820 CST [91977] HINT:  Future log output will appear in directory "log".
uxmaster status starting
uxmaster status starting
uxmaster status starting
uxmaster status starting
uxmaster status starting
uxmaster status starting
uxmaster status ready   
 done
server started
[uxdb@kylin bin]$

4、删除库

[uxdb@kylin bin]$ ./removedb dbhome002/
removedb: lock file "uxmaster.pid" exists
Is a server running?  If not, delete the lock file and try again.
[uxdb@kylin bin]$ ux_ctl -D dbhome002 stop
waiting for server to shut down.... done
server stopped

[uxdb@kylin bin]$ ./removedb dbhome002/
Are you sure to remove database cluster dbhome002/?(Y/N): y

removed database cluster dbhome002 ... ok

5、使用数据库

5.1、使用默认数据库（DEFAULT）

[uxdb@kylin bin]$ ./uxsql -U uxdb -d uxdb -p 4444
Password for user uxdb: 
uxsql: error: could not connect to server: FATAL:  password authentication failed for user "uxdb"
[uxdb@kylin bin]$ ./uxsql -U UXDB -d UXDB -p 4444
Password for user UXDB: 
uxsql (2.1.1.4)
Type "help" for help.

UXDB=# 
UXDB=# 
UXDB=# \d
Did not find any relations.
UXDB=# 
UXDB=# 
UXDB=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 UXDB      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

UXDB=# create table t(t clob);
CREATE TABLE
UXDB=# \q

[uxdb@kylin bin]$ ./removedb dbhome001/
removedb: lock file "uxmaster.pid" exists
Is a server running?  If not, delete the lock file and try again.
[uxdb@kylin bin]$ ux_ctl -D dbhome001 stop
waiting for server to shut down.... done
server stopped
[uxdb@kylin bin]$ ./removedb dbhome001/
Are you sure to remove database cluster dbhome001/?(Y/N): y

removed database cluster dbhome001 ... ok

5.2、使用Oralce兼容数据库（compatible）

[uxdb@kylin bin]$ ./initdb -W -D dbhome002/ -r compatible

[uxdb@kylin bin]$ ./uxsql -U UXDB -d UXDB -p 4444
Password for user UXDB: 
uxsql (2.1.1.4)
Type "help" for help.

UXDB=# 

以下为测试 oracle的一些字段类型及函数，可参考

UXDB=# \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 PUBLIC | T    | table | UXDB
(1 row)

UXDB=# drop table t;
DROP TABLE
UXDB=# create table t(t blob)

UXDB=# create table  a(a date);
CREATE TABLE

UXDB=# CREATE  INDEX "IDX_ARCH_TOCHAR_BIRTHDAY" ON a(a);
CREATE INDEX
UXDB=# CREATE  INDEX "IDX_ARCH_TOCHAR_BIRTHDAY" ON a ("TO_CHAR(a","'yyyy-MM-dd')");
ERROR:  column "TO_CHAR(a" does not exist
UXDB=# \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 PUBLIC | A    | table | UXDB
 PUBLIC | T    | table | UXDB
(2 rows)
UXDB=# \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 PUBLIC | A    | table | UXDB
 PUBLIC | T    | table | UXDB
(2 rows)

UXDB=# drop table a;
DROP TABLE
UXDB=# drop table t;
DROP TABLE
UXDB=# \d
Did not find any relations.
UXDB=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 UXDB      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

UXDB=# \dn
   List of schemas
     Name     | Owner 
--------------+-------
 DBMS_ALERT   | UXDB
 DBMS_ASSERT  | UXDB
 DBMS_OUTPUT  | UXDB
 DBMS_PIPE    | UXDB
 DBMS_RANDOM  | UXDB
 DBMS_UTILITY | UXDB
 ORACLE       | UXDB
 PLUNIT       | UXDB
 PLVCHR       | UXDB
 PLVDATE      | UXDB
 PLVLEX       | UXDB
 PLVSTR       | UXDB
 PLVSUBST     | UXDB
 PUBLIC       | UXDB
 SYS          | UXDB
 UTL_FILE     | UXDB
(16 rows)

UXDB=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 TEMPLATE0 | UXDB  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/UXDB          +
           |       |          |             |             | UXDB=CTc/UXDB
 TEMPLATE1 | UXDB  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/UXDB          +
           |       |          |             |             | UXDB=CTc/UXDB
 UXDB      | UXDB  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(3 rows)

UXDB=# 

6、初始化命令 ./initdb

[uxdb@kylin bin]$ ./initdb --help
initdb initializes a UXsinoDB database cluster.

Usage:
  initdb [OPTION]... [DATADIR]

Options:
  -A, --auth=METHOD         default authentication method for local connections
      --auth-host=METHOD    default authentication method for local TCP/IP connections
      --auth-local=METHOD   default authentication method for local-socket connections
 [-D, --uxdata=]DATADIR     location for this database cluster
  -E, --encoding=ENCODING   set default encoding for new databases
  -g, --allow-group-access  allow group read/execute on data directory
      --locale=LOCALE       set default locale for new databases
      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE
                            set default locale in the respective category for
                            new databases (default taken from environment)
      --no-locale           equivalent to --locale=C
      --pwfile=FILE         read password for the new superuser from file
  -T, --text-search-config=CFG
                            default text search configuration
  -U, --username=NAME       database superuser name
  -W, --pwprompt            prompt for a password for the new superuser
  -X, --waldir=WALDIR       location for the write-ahead log directory
      --wal-segsize=SIZE    size of WAL segments, in megabytes

Less commonly used options:
  -d, --debug               generate lots of debugging output
  -k, --data-checksums      use data page checksums
  -L DIRECTORY              where to find the input files
  -n, --no-clean            do not clean up after errors
  -N, --no-sync             do not wait for changes to be written safely to disk
  -s, --show                show internal settings
  -S, --sync-only           only sync data directory
  -I                        Initial Database level auditing
  -M [uxcrypto is default]  Enable Full database level encryption
      --encrypted-pwfile=FILE
                            read password for the encrypted database
      --ignore-case         The database cluster is case insensitive
  -r, --running-mode        Database running mode, support [standard] and [compatible] (default: standard)

Other options:
  -V, --version             output version information, then exit
  -?, --help                show this help, then exit

If the data directory is not specified, the environment variable UXDATA
is used.

Report bugs to <uxsql-bugs@lists.uxsino.com>.

Oracle到Uxdb适配的对应关系
（目录关系： uxdb-migration-v3.0.6-RELEASE\configoracleToUxdb.properties）

oracle.type.ARRAY=ARRAY
oracle.type.BFILE=BLOB
oracle.type.BIGINT=BIGINT
oracle.type.BINARY=BLOB
oracle.type.BINARY_DOUBLE=FLOAT
oracle.type.BINARY_FLOAT=FLOAT
oracle.type.BIT=BIT
oracle.type.BITVARYING=BITSTRING
oracle.type.BLOB=BLOB
oracle.type.BOOLEAN=BOOLEAN
oracle.type.BOX=GEOMETRIC
oracle.type.BYTEA=BYTEA
oracle.type.CHAR=CHAR
oracle.type.CHARACTER=VARCHAR
oracle.type.CHARACTERVARYING=VARCHAR
oracle.type.CIDR=NETWORKADDRESS
oracle.type.CIRCLE=GEOMETRIC
oracle.type.CLOB=CLOB
oracle.type.DATE=TIMESTAMP
oracle.type.DATERANGE=RANGE_TYPES
oracle.type.DATETIME=TIMESTAMP
oracle.type.DATETIME2=TIMESTAMP
oracle.type.DATETIMEOFFSET=TIMESTAMP
oracle.type.DECIMAL=NUMERIC
oracle.type.DOUBLE=FLOAT
oracle.type.DOUBLEPRECISION=FLOAT
oracle.type.FLOAT=FLOAT
oracle.type.INET=NETWORKADDRESS
oracle.type.INT=INTEGER
oracle.type.INTEGER=INTEGER
oracle.type.INTERVAL=INTERVAL
oracle.type.INT4=INTEGER
oracle.type.INT4RANGE=RANGE_TYPES
oracle.type.INT8RANGE=RANGE_TYPES
oracle.type.JSON=JSON
oracle.type.JSONB=JSONB
oracle.type.LINE=GEOMETRIC
oracle.type.LONG=CLOB
oracle.type.LONGRAW=BLOB
oracle.type.LONGVARCHAR=CLOB
oracle.type.LSEG=GEOMETRIC
oracle.type.MACADDR=NETWORKADDRESS
oracle.type.MEDIUMINT=INTEGER
oracle.type.MONEY=MONEY
oracle.type.NAME=NAME
oracle.type.NCHAR=VARCHAR
oracle.type.NCLOB=CLOB
oracle.type.NUMBER=NUMERIC
oracle.type.NUMERIC=NUMERIC
oracle.type.NUMRANGE=RANGE_TYPES
oracle.type.NVARCHAR=VARCHAR
oracle.type.NVARCHAR2=VARCHAR
oracle.type.OID=OID
oracle.type.PATH=GEOMETRIC
oracle.type.PG_LSN=LSN
oracle.type.POINT=GEOMETRIC
oracle.type.POLYGON=GEOMETRIC
oracle.type.RAW=BYTEA
oracle.type.REAL=FLOAT
oracle.type.ROWID=VARCHAR
oracle.type.SMALLINT=INTEGER
oracle.type.SMALLMONEY=MONEY
oracle.type.TEXT=TEXT
oracle.type.TIME=TIME
oracle.type.TIMESTAMP=TIMESTAMP
oracle.type.TIMEWITHOUTTIMEZONE=TIME
oracle.type.TIMEWITHTIMEZONE=TIME
oracle.type.TINYINT=INTEGER
oracle.type.TSQUERY=TS_TYPES
oracle.type.TSRANGE=RANGE_TYPES
oracle.type.TSTZRANGE=RANGE_TYPES
oracle.type.TSVECTOR=TS_TYPES
oracle.type.UNIQUEIDENTIFIER=GUID
oracle.type.UROWID=VARCHAR
oracle.type.USER-DEFINED=USERDEFINED
oracle.type.UUID=GUID
oracle.type.VARBINARY=BLOB
oracle.type.VARCHAR=VARCHAR
oracle.type.VARCHAR2=VARCHAR
oracle.type.XML=XML
oracle.type.XMLTYPE=XML