Skip to content

Latest commit

 

History

History
70 lines (60 loc) · 3.34 KB

README.md

File metadata and controls

70 lines (60 loc) · 3.34 KB
Raw

SQLite Server (SQLited)

A high performance SQLite server engine based on the client/server architecture and org.xerial sqlite-jdbc project.

  • Inherit all characteristics of SQLite that is small, fast, self-contained, high-reliability, full-featured, and ACID complete SQL database engine
  • Implement a subset(PgServer) of postgreSQL frontend/backend protocol for supporting pgjdbc , psql, or ODBC
  • Support md5(default), password and trust authentication methods in PgServer
  • High performance(1000+ read/write transactions per second, "select count(*)... where NON_INDEX_COLUMN condition" returns in 1s on 10000000+ rows table, insert 50,000 ~ 100,000+ rows per second in wal & normal mode)
  • Add a feature of user, privilege and database management for SQLite server engine
  • Use NIO infrastructure for supporting many connections by a few threads, SQLite server workers is default CPU cores
  • Added some SQL statements that include "create user", "alter user", "drop user", "show users", "grant", "revoke", "show grants", "create database", "show databases", "drop database", "show [full] processlist", "kill [connection|query] processor_id" for administrative purposes
  • Added some SQL functions such as "user()", "current_user()", "version()", "server_version()", "database()", "current_database()", "start_time()", "sysdate()", "clock_timestamp()", "sleep(N)"
  • Added "begin read only | read write", "set {transaction | session characteristics as transaction} read only | read write" to support read only or read write transaction in a connection
  • Support "insert into ...{values()... | select ...} returning ..." statement of PostgreSQL style
  • Added "select ... for update" for support pessimistic lock by SQLite reserved lock
  • Added "show tables", "show columns", "show indexes", "show create table", "show create index" statements for querying schema information
  • Added "truncate [table] tbl_name" statement

Documents

Examples

  1. Standalone SQLite server

Console 1 Start SQLite server

$./bin/initdb.sh -D ./data -p 123456 -d test
$./bin/startup.sh -D ./data &
2019-12-08 15:15:59.024 [SQLite PG server] INFO  org.sqlite.server.SQLiteServer.listen(565) - 
Ready for connections on localhost:3272, version 8.2.23-SQLited 0.3.29

Console 2 Connect to SQLite server then execute query

$psql -U root -p 3272 test
The user root's password:
psql (11.0, Server 8.2.23)
Input "help" for help information.

test.db=> \timing on
Timing on
test.db=> select count(*) from accounts;
 count(*)
----------
 32011001
(Rows 1)


Time: 338.081 ms
test.db=> select balance, count(*) from accounts where balance > 1000 group by balance limit 2;
 balance | count(*)
---------+----------
    1001 |      321
    1002 |      321
(Rows 2)


Time: 9592.378 ms (00:09.592)
test.db=>
  1. Embedded SQLite server
String[] args = {"-p", "123456", "-d", "test"};
SQLiteServer server = SQLiteServer.create(args);
server.initdb(args);
server.close();

server = SQLiteServer.create();
server.bootAsync();