27 Nisan 2023 Perşembe

JDBC DriverManager Kullanımı

Örnek
Şöyle yaparız
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class Example {
  public static void main(String[] args) throws SQLException {
    String url = "jdbc:mysql://localhost:3306/mydatabase";
    Properties props = new Properties();
    props.setProperty("user", "myusername");
    props.setProperty("password", "mypassword");
        
    try (Connection conn = DriverManager.getConnection(url, props)) {
       // Use the connection...
    }
  }
}

26 Nisan 2023 Çarşamba

Testcontainers MariaDBContainer Sınıfı

Giriş
Şu satırı dahil ederiz
import org.testcontainers.containers.MariaDBContainer;
Maven
Şu satırı dahil ederiz
<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>mariadb</artifactId>
  <version>1.18.0</version>
  <scope>test</scope>
</dependency> 

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.30</version>
  <scope>test</scope>
</dependency>
withUrlParam metodu
getJdbcUrl() çağrısı yapılınca döndürülecek URL String'ini ayarlamak içindir.
Örnek
Şöyle yaparız. MariaDBContainer root kullanıcı yaratmaya izin vermiyor
MariaDBContainer<?> container = new MariaDBContainer<>("mariadb:10.3")
  .withDatabaseName("mydb")
  .withUsername("user")
  .withUrlParam("user", "user")
  .withUrlParam("password", "test");
  container.start();
String url = container.getJdbcUrl();

25 Nisan 2023 Salı

JDBC Connection Arayüzü

getCatalog metodu
Connecton string şöyle olsun 
jdbc:mysql://localhost:3458/mydb?password=test&user=root
connection.getCatalog() metodu mydb değerini döner. Yani veri tabanı ismini döner. Veri tabanı şöyle yaratılır
CREATE DATABASE mydb
getSchema metodu
Hep null döner

Kod şöyle. this.propertySet.getEnumProperty(PropertyKey.databaseTerm).getValue() çağrısı hep DatabaseTerm.CATALOG değeri döner. 
- Dolayısıyla getSchema() hep null döner
- Dolayısıyla getCatalog() hep this.database döner
public String getSchema() throws SQLException {
  try {
    synchronized(this.getConnectionMutex()) {
      this.checkClosed();
      return this.propertySet.getEnumProperty(PropertyKey.databaseTerm).getValue() 
        == DatabaseTerm.SCHEMA ? this.database : null;
    }
  } catch (CJException var5) {
    throw SQLExceptionsMapping.translateException(var5, this.getExceptionInterceptor());
  }
}

public String getCatalog() throws SQLException {
  try {
    synchronized(this.getConnectionMutex()) {
      return this.propertySet.getEnumProperty(PropertyKey.databaseTerm).getValue() 
        == DatabaseTerm.SCHEMA ? null : this.database;
    }
  } catch (CJException var5) {
    throw SQLExceptionsMapping.translateException(var5, this.getExceptionInterceptor());
  }
}


24 Nisan 2023 Pazartesi

Sütun Tipleri - Non-Binary String Tipleri

Giriş
Açıklaması şöyle. String tipleri temelde nonbinary ve binary olarak ayrılıyorlar
The string data types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.
CHAR Tipi
Açıklaması şöyle. CHAR en fazla 255 karakter olabilir. Bu sütun tipi için illaki bir uzunluk belirtmek lazım
A fixed-length nonbinary (character) string
VARCHAR Tipi
Açıklaması şöyle. VARCHAR en fazla 65,535 karakter olabilir. Bu sütun tipi için illaki bir uzunluk belirtmek lazım
A variable-length nonbinary (character) string
CHAR vs VARCHAR
CHAR belirtilen karakter kadar yeri mutlaka alır. Açıklaması şöyle
CHAR is fixed length while VARCHAR is variable length. That means, a CHAR(x) string has exactly x characters in length, including spaces. A VARCHAR(x) string can have up to x characters and it cuts off trailing spaces, thus might be shorter than the declared length.
CHAR Ne Zaman Kullanılır?
Eğer verinin uzunluğu sabitse ve 255'ten küçükse CHAR kullanılabilir.
Örnek
Şöyle yaparız
CREATE TABLE IF NOT EXISTS ADV.GSR_FUNCTIONPERMISSION
(
  ...
  REGISTRATIONALLOWED   CHAR(1)      DEFAULT 0 NOT NULL,

);
TEXT
Açıklaması şöyle.
A small non-binary string
Açıklaması şöyle. Yani TEXT tipi standart SQL tipi değil.
In SQL, there is no specific "text" column type. However, many SQL database systems provide data types that can store large amounts of textual data, such as "VARCHAR" or "TEXT" data types.

For example, MySQL provides a "TEXT" data type that can store up to 65,535 characters, while PostgreSQL provides a "VARCHAR" data type that can store up to 1 GB of textual data.

So while "text" is not a standard SQL column type, there are commonly used data types that can be used to store textual data in SQL databases.
TINYTEXT
Açıklaması şöyle.
A very small non-binary string
MEDIUMTEXT
Açıklaması şöyle.
A medium-sized non-binary string
LONGTEXT
A large non-binary string
Metin uzunlukları şöyle
      Type | Maximum length
-----------+-------------------------------------
  TINYTEXT |           255 (2 8−1) bytes
      TEXT |        65,535 (216−1) bytes = 64 KiB
MEDIUMTEXT |    16,777,215 (224−1) bytes = 16 MiB
  LONGTEXT | 4,294,967,295 (232−1) bytes =  4 GiB

18 Nisan 2023 Salı

Status Komutu

Giriş
Açıklaması şöyle
The "status" command is a client-side command that provides basic information about the server, including the version, connection ID, current user, and current database. It is executed within the MariaDB client, and the output is displayed in the console.

On the other hand, the "show status" command is a server-side command that displays a list of server status variables and their values. It provides detailed information about the server's internal state, such as the number of queries executed, the number of connections, and the amount of memory used.
Örnek
Şöyle yaparız
> status
--------------
mysql  Ver 15.1 Distrib 10.9.3-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.9.3-MariaDB-1:10.9.3+maria~ubu2204 mariadb.org binary distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /run/mysqld/mysqld.sock
Uptime:                 11 min 43 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 17  Open tables: 10  Queries per second avg: 0.008
--------------

mysql.user Sistem Tablosu

Örnek
Şöyle yaparız
> select user from mysql.user;
+-------------+
| User        |
+-------------+
| root        |
| user1       |
| mariadb.sys |
| root        |
+-------------+

Docker ve MariaDB

Örnek
Şöyle yaparız
$ docker run -d 
  --name mariadb1093 
  -p 3316:3306 
  -e MARIADB_USER=user1 
  -e MARIADB_PASSWORD=upassw1 
  -e MARIADB_ROOT_PASSWORD=rpassw1 
  -v $HOME/DOCKER/dockerMariaDB/datafiles1093:/var/lib/mysql 
  mariadb:latest


MariaDB

Giriş
Açıklaması şöyle. Yani Oracle ile aynı söz dizimini kullanabiliyor
Oracle syntax compatibility mode for running Oracle Database applications without change.
- Detailed guide section on migrating from SQL Server to MariaDB.
JSON Data type and rich set of JSON functions
- Selection of a specific storage engine such as InnoDB, Aria, and MyISAM

14 Nisan 2023 Cuma

Floating-Point Sütun Tipleri - Approximate Value

Giriş
Şöyledir
FLOAT 4 Byte
REAL 8 Byte Synonym for DOUBLE DOUBLE 8 Byte
Açıklaması şöyle
MySQL treats REAL as a synonym for DOUBLE, unless the REAL_AS_FLOAT SQL mode is enabled
Burada MySQL tanımlı SQL standardından farklı davranabilyior. Çünkü SQL standardına göre REAL aslında FLOAT ile aynı şey. Açıklaması şöyle
- The REAL data type is an approximate number with floating point numeric data.
- REAL value is approximate which means not all range of values can be represented exactly.
- REAL is equivalent to FLOAT(24).

Java karşılıkları şöyle
MySQL Type Java Type
CHAR, VARCHAR, LONGVARCHAR String
TEXT String
TINYINT byte
SMALLINT short
INT (INTEGER) int
BIGINT long
NUMERIC, DECIMAL java.math.BigDecimal
REAL float
FLOAT, DOUBLE double
BIT boolean
BINARY, VARBINARY, LONGVARBINARY    byte[]
BLOB, MEDIUMBLOB, LARGEBLOB    byte[]
DATE java.sql.Date
TIME java.sql.Time
DATETIME, TIMESTAMP java.sql.Timestamp

13 Nisan 2023 Perşembe

Orchestrator

Giriş
Orchestrator şu işleri yapar. Orchestrator için adres http://localhost:3000
Orchestrator supports:
Discovery: Orchestrator actively crawls through your topologies and maps them. It reads basic MySQL info such as replication status and configuration, and provides a slick visualisation of your topologies, including replication problems, even in the face of failures.

Refactoring: Orchestrator understands replication rules. It knows about binlog file:position, GTID, Pseudo GTID, Binlog Servers.

Recovery: Orchestrator uses a holistic approach to detect master and intermediate master failures. Based on information gained from the topology itself, it recognises a variety of failure scenarios.
Şeklen şöyle


Şu hakları vermek gerekir
CREATE USER 'orc_client_user'@'172.20.0.10' IDENTIFIED BY 'orc_client_password';
GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orc_client_user'@'172.20.0.10';
GRANT SELECT ON mysql.slave_master_info TO 'orc_client_user'@'172.20.0.10';

Phantom Read

Örnek
Bir toplantı salonuna rezervasyon yapmak isteyelim. İki kullanıcı da 
1. SELECT cümlesi kullanarak salonun boş olduğunu gördüler.
2. INSERT cümlesi ile salonu rezerve etmek istediler.
Açıklaması şöyle
There is a meeting room system that provides users to reserve a meeting room, and when the user has successfully reserved the meeting room, a new corresponding data will be added in the table as follows.
...
The problem occurs when two users want to occupy the same time slot in the same meeting room simultaneously, and they can both pass the first SELECT validation, so they can both insert a reservation, and a conflict occurs. And such a situation can not be solved by adding a lock, because there is no row to lock at the beginning.
Çözüm - Materialize Conflicts
Açıklaması şöyle. Burada time_slots isimli bir tabloya SELECT FOR UPDATE ile kilit atılır. Belirli bir saat arasını temsil eden satır kilitli olduğu için ikinci kullanıcı rezervasyon yapamaz.
To solve such phantom reads, the developer must use some tricks to reveal conflicts hidden under the same table.

One way is to create a new table and pre-fill it with data to act as a coordinator for simultaneous operations. In the case of this meeting room system, we can create a table time_slots that lists all time slots in advance as follows.

When the meeting room is to be reserved, we not only execute SELECT on the original booking, but also SELECT on time_slots, and we can add FOR UPDATE because the data already exists. It is worth noting that the new SELECT FOR UPDATE is executed before the original SELECT.

In that case, when the expected time slots of two simultaneous users overlap, they will be blocked by the exclusive lock and become one after the other, and the latter will fail directly because it sees the result of the previous completion.

CREATE EVENT - Scheduled Task İçindir

Örnek Şöyle yaparız CREATE EVENT myevent     ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR     DO       UPDATE myschema.mytable SET myc...