SQLite Server โดยใช้ PostgreSQL Wire Protocol 🙃
ทำ… ทำไม? (ผมก็ไม่รู้เหมือนกัน 5555555) เอาเป็นว่าอ่าน ๆ ไปเหอะ
Introduction
ในวันหนึ่งที่ผมกำลังนั่งหา project website ใหม่มานั่งทำเล่นแล้ว deploy บน VPS เล็ก ๆ ที่ผมเช่าไว้เพื่อลองอะไรต่าง ๆ อยู่ดี ๆ project ที่ผมอยากทำต้องใช้ database ในการเก็บข้อมูลของ user
คำถามมันจึงเกิดขึ้น “ใช้ database ไหนดีหว่า???”…….
ผมไม่อยากเสียเงินมาก cloud database จึงไม่ใช่ตัวเลือก แต่!!!! database server อื่นๆก็ไม่เหมาะเหมือนกันเพราะ VPS ที่ผมเช่าอยู่มี แรม อยู่ 2GB ตอนแรกผมก็อยากใช้ Postgres แต่สุดท้ายมันก็ใหญ่เกินไป คือผมอยากใช้ Postgres Client เพราะมันมีอยู่ในทุกภาษา แต่ไม่อยากใช้ Postgres
ทำยังไงดีล่ะ
คำตอบง่ายมาก เราเลือก impractical way ถ้าเรา somehow เอา Postgres protocol + SQLite ได้ละก็…. เราก็จะได้สิ่งที่เราต้องการ 🤣🤣🤣🤣
ตอนนี้เรามี BBB, Big black box ที่ต้องเชื่อมต่อระหว่าง Postgres client และ SQLite ของเรา ซึ่ง Postgres wire protocol นั้น based อยู่บน tcp protocol ดังนั้น สิ่งที่เราต้องการก็คือ Proxy
ว่าแต่ Proxy มันคืออะไรอ่ะ?
ง่ายๆตาม concept แล้ว proxy คือคนกลางระหว่าง network คอยทำสิ่งที่คนกลาง ซึ่ง network ในที่นี้คืออะไรก็ตามที่คุยกันผ่าน ภาษาสักอย่าง หมายความว่า proxy อาจเป็น คนแปลภาษาก็ได้
แล้ว Postgres client คุยภาษาอะไร คำตอบคือ TCP ครับ ส่วน SQLite เราคุย high-level disk I/O [whatever that is].
It’s time for the juicy part
It’s development time……👍 มาถึงกันแล้วส่วนที่ทุกคนรอคอย ส่วนของการสร้าง program นี้ขึ้นมา แล้วเราต้องทำ อะไรบ้างหล่ะ ขอดู workflow หน่อย
- จะเห็นได้ว่าเราต้องรับการสั่งข้อมูลจาก TCP protocol ของ Postgres client ที่ encoded เป็น pg wire protocol ดังนั้นเราจึงต้องสร้าง TCP server ขึ้นมาเพื่ออ่านข้อมูลและจัดการเรื่อง authentication ให้กับ client ที่ connect เข้ามา
- เราต้องแปลงคำสั่งจาก pg client (postgres client) เป็นข้อมูลที่เราเข้าใจได้ (ภาษา SQL) เพื่อจะได้ไปสั่ง SQLite ต่อ
- นำคำสั่งที่เราได้ไปแปลงเป็นคำสั่งของ SQLite (ภาษา SQL)
เห็นมะขั้นตอนในการสร้าง Proxy ของเรานั้นการสร้างค่อนข้าง straightforward [ตรงไปตรงมา] มาเริ่มทำกันเลยดีกว่า
โดย ภาษาที่ผมจะใช้คือ Rust
The best programming language for those who want more suffering after trying C lang. [boon4681] 29/1/2024
TCP server
TCP server ใน rust สร้างง่ายมากครับ ด้วยความสุดยอดของ tokio ที่ไม่ว่าใครที่ทำ backend มาก็ต้องพูดว่าเขียนง่ายมาก ล้อเล่น!!!! ผมสร้างมันด้วยการ googling ต่างหาก 55555555555
async fn proxy(client: &str) -> io::Result<()> {
let listener = TcpListener::bind(client).await?;
loop {
match listener.accept().await {
Ok((stream, _)) => {
tokio::spawn(
// function for handle connection
);
}
Err(e) => eprintln!("Error accepting connection: {}", e),
}
}
}
#[tokio::main]
async fn main() -> io::Result<()> {
let client = "::1:6000";
proxy(client).await
}เห้ยง่ายจังเลยเรามาต่อกันด้วยตัวอ่าน Postgres wire protocol ดีกว่าซึ่งถ้าใครอยากปวดหัวเล่นนะครับ ผมมี document ใน website postgres มาฝากด้วย
Postgres wire protocol ขอให้สนุกกับการอ่านนะ
Postgres wire protocol decoder
มาต่อในการการสร้าง Postgres wire protocol decoder กัน…….. การจะสร้างมันได้นั้นเราจำเป็นต้องรู้ก่อนว่า ถ้าจะเชื่อมต่อเข้า postgresql server ได้นั้นเราต้องคุยอะไรบ้างผ่าน TCP protocol เพื่อที่จะได้ใช้คำสั่ง SQL ได้
โดยผมจะแบ่ง ส่วนของการเขียน Decoder นี้เป็น 3 steps
Step 1 : ทำอะไรก็ได้สักอย่างนึง ที่มันพอจะใช้งานได้ก่อน
และแน่นอน เมื่อพูดถึงอะไรก็ได้ที่ใช้ได้ ผมหมายถึง ผมจะใช้ programmer program ที่ใช้ run ภาษา programming ตัวนึงชื่อ javascript ใช่แล้ว It’s time for the big boi nodejs. [bunjs suc*k, boon4681]
แล้วทำไมไม่ใช้ rust ต่อหล่ะ?
ผมเกลียด string ใน rust ไหนจะ array อีกทำไมต้องทำให้มันใช้ยากหว่า, boon4681 said ตอนที่ยังเขียน rust ไม่เป็น [และตอนนี้ก็ยังเขียนไม่เป็นอยู่ :D ]
Let’s go…….
อย่างแรกที่อยากจะพูดก็คือในตอนแรกที่กำลังทำ decoder อยู่ผมไม่รู้ว่าตัวเองกำลังทำอะไรด้วยซ้ำดังนั้น It needs a bit of Googling. จนไปเจอกับ documentations สำหรับการ implements packets decoder และ how client communicate with postgres server, นี่ link สำหรับ docs
เยี่ยมนี่แหละสิ่งที่ผมต้องการ คำอธิบายสำหรับ protocol แต่เดี๋ยวนะแล้วไหนวิธี encode packets วะ นี่ไงหล่ะเจอสักที
https://www.postgresql.org/docs/current/protocol-message-formats.html
แล้วผมจะรู้ไปกับพรี่มั้ยครับว่ามัน encode ยังไงหะะะะะะ แล้วไหน reference สำหรับ oid เห้อออออออ
เมื่อเรามาเจอความยากระดับนี้ในการพัฒนา project มันถึงเวลาที่จะต้องใช้ เคล็ดลับที่ developer ทุกคนนั้นมี ใช่แล้ว และผมหมายถึง CTRL+C #*)$(*__)+^$@… ซะที่ไหนล่ะ
ผมโหลด pg package จาก npm และ wireshark มาใช้ โดยใช้ pg packet connect ไปยัง postgres server ในเครื่องผมแล้วก็ใช้ wireshark ดัก packets ที่ส่งกันระหว่าง network ฉลาด!!!! in a impractical way
ต้องนับถือความสุดยอดและความพยายามของคนที่เขียน postgres client ขึ้นมาจริงๆครับ good job for such a magical code that somehow works and i don’t understand it.
ผมไม่รอช้าที่จะ google ว่า pq nodejs github. CTRL+C, CTRL+V wow…. code ของผมใช้งานได้แล้ว เย่ย……….
แล้วก็ถึงเวลาต้องเขียนใหม่อีก รอบในภาษา rust ถถถถถถถ
และนี่คือ i32 to 4*8bit กับ i16 to 2*8bit ใน rust ที่ผมเขียนครับ
fn i32_to_4u8(i: i32) -> [u8; 4] {
let mut buf: [u8; 4] = [0; 4];
buf[0] = (i >> 24) as u8 & 0xff;
buf[1] = (i >> 16) as u8 & 0xff;
buf[2] = (i >> 8) as u8 & 0xff;
buf[3] = (i >> 0) as u8 & 0xff;
return buf;
}
fn i16_to_2u8(i: i16) -> [u8; 2] {
let mut buf: [u8; 2] = [0; 2];
buf[0] = (i >> 8) as u8 & 0xff;
buf[1] = (i >> 0) as u8 & 0xff;
return buf;
}และนี่ PacketReader สำหรับการอ่าน message จาก packets ครับ
impl PacketReader {
pub fn new(buf: &[u8]) -> PacketReader {
return PacketReader {
index: 0,
length: buf.len() as i32,
buffer: Vec::from(buf),
};
}
pub fn shift(&mut self, i: i32) {
self.index += i;
}
pub fn read(&mut self, length: i32) -> String {
ensureSize!(self, length);
let mut str: String = "".to_owned();
for i in (self.index as usize)..(self.index + length) as usize {
str.push(self.buffer[i] as char)
}
str
}
pub fn read_shift(&mut self, length: i32) -> String {
let result = self.read(length);
self.shift(length);
result
}
pub fn read_i32_be(&mut self) -> i32 {
ensureSize!(self, 4);
let i: usize = self.index as usize;
let mut result: i32 = 0;
result += (self.buffer[i] as i32) << 24;
result += (self.buffer[i + 1] as i32) << 16;
result += (self.buffer[i + 2] as i32) << 8;
result += (self.buffer[i + 3] as i32) << 0;
result
}
pub fn read_i16_be(&mut self) -> i16 {
ensureSize!(self, 2);
let i: usize = self.index as usize;
let mut result: i16 = 0;
result += (self.buffer[i] as i16) << 8;
result += (self.buffer[i + 1] as i16) << 0;
result
}
pub fn read_i32_shift(&mut self) -> i32 {
let result = self.read_i32_be();
self.shift(4);
result
}
pub fn read_i16_shift(&mut self) -> i16 {
let result = self.read_i16_be();
self.shift(2);
result
}
pub fn len(&mut self) -> i32 {
self.buffer.len() as i32
}
}ส่วนนี่คือ PacketBuilder เพื่อ encode message
impl PacketBuilder {
pub fn new(prefix: &char) -> PacketBuilder {
return PacketBuilder {
prefix: *prefix,
length: 0,
buffer: Vec::new(),
};
}
pub fn add_i32(&mut self, i: i32) -> &mut PacketBuilder {
self.length += 4;
self.buffer.append(&mut Vec::from(i32_to_4u8(i)));
self
}
pub fn add_i16(&mut self, i: i16) -> &mut PacketBuilder {
self.length += 2;
self.buffer.append(&mut Vec::from(i16_to_2u8(i)));
self
}
pub fn add_i1(&mut self, i: u8) -> &mut PacketBuilder {
self.length += 1;
self.buffer.push(i);
self
}
pub fn add_string(&mut self, string: &str) -> &mut PacketBuilder {
let bytes = string.as_bytes();
self.length += bytes.len() as i32;
self.buffer.append(&mut Vec::from(bytes));
self
}
pub fn add_string_zero(&mut self, string: &str) -> &mut PacketBuilder {
let bytes = string.as_bytes();
self.length += bytes.len() as i32 + 1;
self.buffer.append(&mut Vec::from(bytes));
self.buffer.push(0);
self
}
pub fn build(&mut self) -> Box<[u8]> {
let mut buf: Vec<u8> = Vec::new();
buf.push(self.prefix as u8);
buf.append(&mut Vec::from(i32_to_4u8(self.length + 4)));
buf.append(&mut self.buffer);
buf.into_boxed_slice()
}
}Step 2: ทำระบบ Authentication และลองเขียน Simple Query workflow response ครับ
implements ตาม docs เลยครับ
https://www.postgresql.org/docs/current/protocol-flow.html
และแน่นอนครับด้วย docs ที่คุณภาพสูงแบบนี้ (ถ้าคุณใช้ notepad เป็น IDE)
ก็ไม่แปลกเลยครับที่จะไม่เข้าใจว่า RowDescription ที่ server ตอบกลับตอนที่ client ใช้ SQL select นั้นต้อง encode ยังไง
เอาเลยครับมันต้อง pull the trigger แล้ว….. เรียก ป่ะป๊าเร็วลูก it chat jippitey
ทีนี้เราก็รู้แล้วครับว่า RowDescription ทำงานยังไงแต่เรายังติดอยู่อีก 1 ปัญหานั่นคือตอนเราตอบกลับหรืออ่าน data ที่ถูก insert เข้ามา postgres ระบุว่าคุณจำเป็นต้องมี type ของ data ระบุไว้เสมอ และนั่นคือ OID ครับ
OID Object Identifier [HUH?]
ง่าย ๆ คือ เอาไว้ระบุ type ของ data แต่ก็ นั่นแหละเราจะหา id ของ types ได้ที่ไหน
นี่ครับคำตอบ
https://www.postgresql.org/message-id/Pine.LNX.4.44.0605171051150.23959-100000%40matrix.gatewaynet.com
ส่วนนี่ตัวอย่างของ oid ที่ผมดึงออกมา
"oid","typname"
16,"bool"
17,"bytea"
18,"char"
19,"name"
20,"int8"
21,"int2"
22,"int2vector"
23,"int4"
24,"regproc"
25,"text"
26,"oid"
27,"tid"
28,"xid"
29,"cid"
30,"oidvector"
114,"json"
142,"xml"ใช้ประโยชน์จาก ป่ะป๊า (ChadJippitey) กันเต็มที่แล้ว หลังจากเราเขียน code implements แล้วเราก็มาต่อกันใน step ที่ 3
Step 3: เอา Step 2 ต่อกับ SQLite
มาถึงในช่วงโค้งสุดท้ายแล้วกับการเอา code ใน step 2 ของเราไปต่อกับ SQLite engine โดยส่วนใหญ่ที่สำคัญคือการ encode data ที่ client request เช่นคำสั่ง SELECT * นั้น server จะต้องส่งกลับ table data
คำถามคือ แล้วจะใช้ SQLite engine ตัวไหนดีหล่ะ?
คำตอบคือ ตัวไหนก็ได้ที่สบายใจครับ ผมสบายใจกับ duckdb เพราะมี rust API ให้
Boom!! …. table encoder done.
ทีนี้เราก็สามารถส่ง table data กลับไปหา client ได้แล้วเย่………. ถือเป็นอันเสร็จ
let first = query.split(";").next().unwrap().trim();
let qtype = first.split_whitespace().next().unwrap().to_owned();
let result: Result<Table, String>;
{
let stmt = conn.prepare(first);
if stmt.is_ok() {
let mut statement = stmt.unwrap();
let mut rows = statement.query([]).unwrap();
let names: Vec<String> =
rows.as_ref().unwrap().column_names().clone();
let mut table: Table = Table {
names,
rows: Vec::new(),
};
loop {
let row = match rows.next().unwrap() {
Some(e) => e,
None => break,
};
let mut i = 0;
let mut result_row: Vec<Data> = Vec::new();
loop {
let wrap_data = match row.get_ref(i) {
Ok(e) => e,
Err(_) => break,
};
let data: Data = match wrap_data {
ValueRef::Null => bytes!(0, ""),
ValueRef::Boolean(e) => bytes!(16, e),
ValueRef::TinyInt(e) => bytes!(21, e),
ValueRef::SmallInt(e) => bytes!(21, e),
ValueRef::Int(e) => bytes!(23, e),
ValueRef::BigInt(e) => bytes!(20, e),
ValueRef::HugeInt(e) => bytes!(20, e),
ValueRef::UTinyInt(e) => bytes!(21, e),
ValueRef::USmallInt(e) => bytes!(21, e),
ValueRef::UInt(e) => bytes!(23, e),
ValueRef::UBigInt(e) => bytes!(20, e),
ValueRef::Float(e) => bytes!(700, e),
ValueRef::Double(e) => bytes!(701, e),
ValueRef::Decimal(e) => bytes!(701, e),
ValueRef::Timestamp(_, _) => todo!(),
ValueRef::Text(e) => Data {
typ: 25,
data: Vec::from(e),
},
ValueRef::Blob(e) => Data {
typ: 17,
data: Vec::from(e),
},
ValueRef::Date32(e) => bytes!(1083, e),
ValueRef::Time64(_, _) => todo!(),
};
i += 1;
result_row.push(data);
}
table.rows.push(result_row);
}
result = Ok(table);
} else {
result = Err(stmt.unwrap_err().to_string());
}
};
match result {
Ok(table) => {
writesock!(stream, row_description(&table));
for row in &table.rows {
writesock!(stream, data_row(row));
}
writesock!(
stream,
command_complete(
&(qtype + " " + &table.rows.len().to_string())
)
);
writesock!(stream, ready_for_query());
}
Err(e) => {
// println!("{}", e);
writesock!(
stream,
error_response(query.to_string(), e.clone())
);
writesock!(stream, ready_for_query());
// writesock!(stream, ready_for_query());
}
}ก็จบกันไปแล้วครับสำหรับ blog ในครั้งนี้แล้วเจอกันใหม่ในครั้งหน้า
Github project นี้ครับใครสนใจก็ไปอ่าน code กันต่อได้นะ
