proullon / ramsql Goto Github PK

View Code? Open in Web Editor NEW

861.0 10.0 66.0 526 KB

In-memory SQL engine in Go sql/driver for testing purpose

License: BSD 3-Clause "New" or "Revised" License

Go 99.84% Makefile 0.16%

sql go memory-sql-engine imdb database

ramsql's People

Contributors

Stargazers

Watchers

Forkers

simonwaldherr haggerty-william hypnoglow devopsmi jmptrader chronos-tachyon zjtheone jiajieyao afro-q bestform netyum tuhochi donmmi yangmain geoeee glaucusio free1139 ggilley mlhoyt lamielle harkce iwanbk yomiji byxiangfei moneytech rinor yonasadiel gtrevg robinsound zhewang1005 jyno12 mihail-popovkin vulgarnykarlson iamnikolie pranayhere developgo mallowfields poyoyoo archeopternix julwong jeremywangjun illidantwister fontoura modasi piotrkowalczuk ncrypthic voidense jordan-bonecutter 4cecoder myus4a getlantern zacharyestep rizalgowandy shinroo joematpal iq-scm luigitni fatelei aramcodz trakhimenok gobatis gonnafaraway blink-go

ramsql's Issues

Check that column in COUNT actually exists

CREATE TABLE foo (id BIGSERIAL, bar TEXT)
SELECT COUNT(project_id) FROM foo WHERE project.projectKey = $1

Expected :
column "project_id" does not exist

Got :
no error

`SELECT EXISTS (...)` syntax not supported

The following SQL throws an error:

row := r.db.QueryRow(`
    SELECT EXISTS (
        SELECT
            1
        FROM
            users
        WHERE
            id = ?
    );
`, id)

Syntax error near select exists (

I tried the exact same code on a real MySQL database, and it worked fine.

GoLang version: 1.20
In go.mod: github.com/proullon/ramsql v0.0.0-20230224205054-8ff679dbf7aa

fatal error: sync: unlock of unlocked mutex

error happens on the line marked "ERROR" below (mvdb.go:36), re v0.0.0-20181213202341-817cee58a244 (btw, it would be great for this repo to be tagged with its own semantic version releases instead):

		db, err := sql.Open("ramsql", "")
		if err != nil {
			return err
		}
		defer db.Close()
		if err := db.Ping(); err != nil {
			return err
		}
		if _, err := db.Exec("CREATE TABLE address (id int, street text)"); err != nil {
			return err
		}
		stmt, err := db.Prepare("insert into address (id,street) values (?,?)")
		if err != nil {
			return err
		}
		for i := 0; i < 10; i++ {
			if _, err := stmt.Exec(i, fmt.Sprintf("%d park ave", 100+i)); err != nil { // ERROR
				return err
			}
		}

and the stack trace is as follows:

goroutine 1 [running]:
runtime.throw(0x17ffa41, 0x1e)
	/Users/mra/go/src/runtime/panic.go:774 +0x72 fp=0xc0007118e0 sp=0xc0007118b0 pc=0x102f742
sync.throw(0x17ffa41, 0x1e)
	/Users/mra/go/src/runtime/panic.go:760 +0x35 fp=0xc000711900 sp=0xc0007118e0 pc=0x102f6c5
sync.(*Mutex).unlockSlow(0xc000208000, 0xc0ffffffff)
	/Users/mra/go/src/sync/mutex.go:196 +0xd6 fp=0xc000711928 sp=0xc000711900 pc=0x1069b36
sync.(*Mutex).Unlock(0xc000208000)
	/Users/mra/go/src/sync/mutex.go:190 +0x48 fp=0xc000711948 sp=0xc000711928 pc=0x1069a48
github.com/proullon/ramsql/driver.(*Stmt).Exec(0xc000208260, 0xc000208500, 0x2, 0x2, 0x1a0f480, 0xc000208720, 0x0, 0x0)
	/Users/mra/gopath/pkg/mod/github.com/proullon/[email protected]/driver/stmt.go:108 +0x400 fp=0xc000711a48 sp=0xc000711948 pc=0x1676950
database/sql.ctxDriverStmtExec(0x1a17f00, 0xc0000d6030, 0x1a18180, 0xc000208260, 0xc000238e60, 0x2, 0x2, 0x2, 0x2, 0x0, ...)
	/Users/mra/go/src/database/sql/ctxutil.go:77 +0x165 fp=0xc000711ac0 sp=0xc000711a48 pc=0x1437985
database/sql.resultFromStatement(0x1a17f00, 0xc0000d6030, 0x1a15680, 0xc000208000, 0xc0000f8400, 0xc000711da8, 0x2, 0x2, 0x0, 0x0, ...)
	/Users/mra/go/src/database/sql/sql.go:2435 +0x156 fp=0xc000711b80 sp=0xc000711ac0 pc=0x1442e56
database/sql.(*Stmt).ExecContext(0xc0001e0900, 0x1a17f00, 0xc0000d6030, 0xc000711da8, 0x2, 0x2, 0x0, 0x0, 0x0, 0x0)
	/Users/mra/go/src/database/sql/sql.go:2411 +0x1f9 fp=0xc000711c60 sp=0xc000711b80 pc=0x1442ae9
database/sql.(*Stmt).Exec(...)
	/Users/mra/go/src/database/sql/sql.go:2423
github.com/xoba/ds/a/mv.glob..func10(0xc0000d4360, 0x0, 0x0, 0x0, 0x0, 0x0)
	/Users/mra/ds/a/mv/mvdb.go:36 +0x374 fp=0xc000711e18 sp=0xc000711c60 pc=0x16789e4
github.com/xoba/ds/a/cmd.RunCommand(0x17e9dc0, 0x3, 0x180e01c, 0x33, 0x185dc78, 0x0, 0x0, 0x0, 0xc000711ef0, 0x103d28a)
	/Users/mra/ds/a/cmd/cmd.go:66 +0xe9 fp=0xc000711e80 sp=0xc000711e18 pc=0x111daa9
github.com/xoba/ds/a/zexe.RunAsMain(0x17e9dc0, 0x3, 0x180e01c, 0x33, 0x185dc78)
	/Users/mra/ds/a/zexe/zexe.go:18 +0x84 fp=0xc000711f00 sp=0xc000711e80 pc=0x1678e74
main.main()
	/Users/mra/ds/a/zexe/mdb/mdb_exe.go:13 +0x8f fp=0xc000711f60 sp=0xc000711f00 pc=0x1678fef
runtime.main()
	/Users/mra/go/src/runtime/proc.go:203 +0x21e fp=0xc000711fe0 sp=0xc000711f60 pc=0x10310ce
runtime.goexit()
	/Users/mra/go/src/runtime/asm_amd64.s:1357 +0x1 fp=0xc000711fe8 sp=0xc000711fe0 pc=0x105bce1

goroutine 50 [select]:
database/sql.(*DB).connectionOpener(0xc000228000, 0x1a17ec0, 0xc00025b900)
	/Users/mra/go/src/database/sql/sql.go:1052 +0xe8
created by database/sql.OpenDB
	/Users/mra/go/src/database/sql/sql.go:722 +0x15d

goroutine 51 [select]:
database/sql.(*DB).connectionResetter(0xc000228000, 0x1a17ec0, 0xc00025b900)
	/Users/mra/go/src/database/sql/sql.go:1065 +0xfb
created by database/sql.OpenDB
	/Users/mra/go/src/database/sql/sql.go:723 +0x193

goroutine 52 [select]:
github.com/proullon/ramsql/engine.(*Engine).listen(0xc0002067e0)
	/Users/mra/gopath/pkg/mod/github.com/proullon/[email protected]/engine/engine.go:111 +0x13b
created by github.com/proullon/ramsql/engine.(*Engine).start
	/Users/mra/gopath/pkg/mod/github.com/proullon/[email protected]/engine/engine.go:64 +0x3f

goroutine 53 [chan receive]:
github.com/proullon/ramsql/engine/protocol.(*ChannelEngineEndpoint).Accept(0xc000010018, 0xc000485fb0, 0xc000010028, 0x0, 0x0)
	/Users/mra/gopath/pkg/mod/github.com/proullon/[email protected]/engine/protocol/channel.go:81 +0x45
github.com/proullon/ramsql/engine.(*Engine).listen.func1(0xc0002067e0, 0xc0000aeb40)
	/Users/mra/gopath/pkg/mod/github.com/proullon/[email protected]/engine/engine.go:100 +0x63
created by github.com/proullon/ramsql/engine.(*Engine).listen
	/Users/mra/gopath/pkg/mod/github.com/proullon/[email protected]/engine/engine.go:98 +0x7c

goroutine 54 [chan receive]:
github.com/proullon/ramsql/engine/protocol.(*ChannelEngineConn).ReadStatement(0xc000010028, 0xc0002086c0, 0x1, 0x1, 0x1a1b000)
	/Users/mra/gopath/pkg/mod/github.com/proullon/[email protected]/engine/protocol/channel.go:109 +0x52
github.com/proullon/ramsql/engine.(*Engine).handleConnection(0xc0002067e0, 0x1a1b000, 0xc000010028)
	/Users/mra/gopath/pkg/mod/github.com/proullon/[email protected]/engine/engine.go:127 +0x3b
created by github.com/proullon/ramsql/engine.(*Engine).listen
	/Users/mra/gopath/pkg/mod/github.com/proullon/[email protected]/engine/engine.go:113 +0xb7

Deadlock while using named arguments

While following a slight deviation to the example on https://pkg.go.dev/database/sql, I encountered a deadlock. For example,

example.go

package myproject

import (
    "database/sql"

    _ "github.com/proullon/ramsql/driver"
)

db, _ := sql.Open("ramsql", ":inmemory:")
defer db.Close()

var name string

// sql: expected 0 arguments, got 1
db.QueryRowContext(context.TODO(), "select p.name from people as p where p.id = :id;", sql.Named("id", "1234")).Scan(&name) 

// deadlock 🤔
db.ExecContext(context.TODO(), "INSERT INTO people (id,name) VALUES (?,?)", "1234", "Ramone")

go.mod

module github.com/myorg/myproject

go 1.17

require github.com/proullon/ramsql v0.0.0-20211120092837-c8d0a408b939

Implement SCHEMA

Was just giving this a try for testing a project and our schema creation sql looks something like:

CREATE SCHEMA foo;

CREATE TABLE "foo"."bar"
{
  ...
}
...

ramsql seems to fail on both SCHEMA and the compound table name, ie it wants to create table foo and treats ."bar" as incorrect.

Support FOREIGN KEY constraints

Add support for FOREIGN KEY.

Ex/

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

As documented here:
https://www.w3schools.com/sql/sql_foreignkey.asp

ASC and DESC

Hello.

Am I wrong that you have misplaced DESC and ASC tokens?

ramsql/engine/parser/lexer.go

Lines 240 to 246 in 06a3b16

    
           func (l *lexer) MatchAscToken() bool { 
        
           	return l.Match([]byte("desc"), DescToken) 
        
           } 
        
           func (l *lexer) MatchDescToken() bool { 
        
           	return l.Match([]byte("asc"), AscToken) 
        
           }

JSON

Hey,

I was playing around ramsql today and I've got a json column

db, err = sql.Open("ramsql", "store")
if err != nil {
	panic(fmt.Sprintf("sql.Open : Error : %s\n", err))
}
db.Exec(`CREATE TABLE test (sequence_number BIGSERIAL PRIMARY KEY, aggregate_id UUID, type TEXT, data JSON, created_at TIMESTAMP);`)

Insertion seems to work but unfortunately everything I put into the data column gets an extra level of escaping when I retrieve it for example:
{\"id\":\"c05d13bd-9d9b-4ea1-95f2-9b11ed3a7d38\",\"name\":\"\"}

Which breaks the json.Unmarshal process. This behaviour isn't consistent with postgresql.

Implement OUTER JOIN

Scientific Notation

We sqlx sends over values to the ramsql it doesn't support scientific notation #78

possible to do a ttl? or lru?

possible to do a ttl? or lru? for each items inserted

Use sql.ErrNoRows

Is ramsql threadsafe?

Go tests run in parallel, will each test have its own instance of ramsql?

Handling of brackets within where clause

Currently, where clauses that have open / closing curly braces in them are not supported.

If one modifies TestSelectStar to parser_test.go

func TestSelectStar(t *testing.T) {
query := SELECT * FROM account WHERE (email = '[email protected]')
parse(query, 1, t)
}

one gets

parser_test.go:232: Cannot parse tokens from 'SELECT * FROM account WHERE (email = '[email protected]')': Syntax error near where ( email

like operator doesnt seem to work.

I don't know if this is an issue or just user error.
I have been trying to get the following query to execute: select message from logs where message like $1

This is the table schema: CREATE TABLE logs (id BIGSERIAL PRIMARY KEY, logTime timestamp, ip text, machine text, agent text, name text, level text, code SMALLINT, message text);

The query parameter is "%Ack complete%".
The error message is: table "like" does not exist

Support Insert multiple rows in a single command

INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99);

Support 'not equals' comparison

It seems that the driver currently does not support the not equals (<>) comparison operator.

In order to fully support basic SQL queries, this should be supported.

panic on unlock of unlocked mutex

Hi @proullon

Receiving panic from using sql.Db#Begin, sql.Db.Commit(), sql.Db.Rollback

2023/08/02 23:01:39 incoming messages 32345, elapsed seconds since start   41, routes 0
fatal error: sync: unlock of unlocked mutex

goroutine 596 [running]:
sync.fatal({0x7ff616d2d069?, 0x20?})
        C:/Users/Users/sdk/go1.20.3/src/runtime/panic.go:1031 +0x1e
sync.(*Mutex).unlockSlow(0xc00010e000, 0xffffffff)
        C:/Users/Users/sdk/go1.20.3/src/sync/mutex.go:229 +0x3c
sync.(*Mutex).Unlock(0x1?)
        C:/Users/Users/sdk/go1.20.3/src/sync/mutex.go:223 +0x29
github.com/proullon/ramsql/driver.(*Stmt).Exec(0xc0008fa240, {0xc00078b7a0?, 0xc0008fa240?, 0x0?})
        C:/Users/Users/go/pkg/mod/github.com/proullon/[email protected]/driver/stmt.go:108 +0x32e
database/sql.ctxDriverStmtExec({0x7ff616dbade8, 0xc00001c040}, {0x7ff616dbafa8, 0xc0008fa240}, {0xc000928480?, 0x3, 0x3})
        C:/Users/Users/sdk/go1.20.3/src/database/sql/ctxutil.go:77 +0x1f9
database/sql.resultFromStatement({0x7ff616dbade8, 0xc00001c040}, {0x7ff616dba840, 0xc00010e000}, 0xc0000a2440, {0xc0013bd738, 0x3, 0x3})
        C:/Users/Users/sdk/go1.20.3/src/database/sql/sql.go:2635 +0x145
database/sql.(*Stmt).ExecContext.func1(0x8?)
        C:/Users/Users/sdk/go1.20.3/src/database/sql/sql.go:2609 +0xa7
database/sql.(*DB).retry(0x20?, 0xc0013bd548)
        C:/Users/Users/sdk/go1.20.3/src/database/sql/sql.go:1538 +0x47
database/sql.(*Stmt).ExecContext(0xc001552064?, {0x7ff616dbade8?, 0xc00001c040?}, {0xc0013bd738?, 0x0?, 0x0?})
        C:/Users/Users/sdk/go1.20.3/src/database/sql/sql.go:2603 +0x17c
database/sql.(*Stmt).Exec(...)
        C:/Users/Users/sdk/go1.20.3/src/database/sql/sql.go:2623
main.insertRoute(0xc000554050?, 0xc0006064b0)
        C:/Code/backend_backend/simple/repository.go:67 +0x665
main.(*ramsqlRepository).Insert(0xc00010e2e0, 0xc0000bf0e0?)
        C:/Code/backend_backend/simple/ramsql.go:36 +0x7e
main.shipReachedDestination.func1({0x7ff616d0fda0?, 0x35520e6829?}, 0xc0008e4200)
        C:/Code/backend_backend/simple/main.go:316 +0x388
net/http.HandlerFunc.ServeHTTP(0xc00007c800?, {0x7ff616dbaba0?, 0xc000918540?}, 0xc0013bd938?)
        C:/Users/Users/sdk/go1.20.3/src/net/http/server.go:2122 +0x2f
net/http.(*ServeMux).ServeHTTP(0xc0013bd950?, {0x7ff616dbaba0, 0xc000918540}, 0xc0008e4200)
        C:/Users/Users/sdk/go1.20.3/src/net/http/server.go:2500 +0x149
net/http.StripPrefix.func1({0x7ff616dbaba0, 0xc000918540}, 0xc0008e4100)
        C:/Users/Users/sdk/go1.20.3/src/net/http/server.go:2165 +0x332
net/http.HandlerFunc.ServeHTTP(0x7ff6167f7460?, {0x7ff616dbaba0?, 0xc000918540?}, 0x7ff6167f6ddd?)
        C:/Users/Users/sdk/go1.20.3/src/net/http/server.go:2122 +0x2f
net/http.(*ServeMux).ServeHTTP(0x3b?, {0x7ff616dbaba0, 0xc000918540}, 0xc0008e4100)
        C:/Users/Users/sdk/go1.20.3/src/net/http/server.go:2500 +0x149
main.countIncomingRequests.func1({0x7ff616dbaba0, 0xc000918540}, 0xc0013bdad0?)
        C:/Code/backend_backend/simple/main.go:116 +0x68
net/http.HandlerFunc.ServeHTTP(0x0?, {0x7ff616dbaba0?, 0xc000918540?}, 0x7ff616abef34?)
        C:/Users/Users/sdk/go1.20.3/src/net/http/server.go:2122 +0x2f

CREATE UNIQUE INDEX

I have tried to use this library for testing but it fails when try to do this:

const creatArtist string = `
  CREATE TABLE IF NOT EXISTS artist (
  	id VARCHAR(255) NOT NULL PRIMARY KEY,
	name VARCHAR(255) NOT NULL
  );`
const createIndex string = `CREATE UNIQUE INDEX IF NOT EXISTS nameindex ON artist(name COLLATE NOCASE);`

// Open the in memory DB
	db, err := sql.Open("ramsql", "TestRead")
	if err != nil {
		t.Fatalf("sql.Open : Error : %s\n", err)
	}
	defer db.Close()

if _, err := db.Exec(creatArtist); err != nil {
		log.Fatal("Error creating DB %v", err)
		return nil, err
	}

	if _, err := db.Exec(createIndex); err != nil {
		log.Fatal("Error creating Index %v", err)
		return nil, err
	}

and then got error saying "error near to UNIQUE" when creating index.

That index creating is standard and works fine with several SQL DB flavors.

Interpolation of []byte arguments fails

Call

db.Exec(`INSERT INTO table (binary_column) VALUES (?)`, []byte{42})

Expected

"\x2a" is inserted into the database

Actual

Query parsing fails at ... ([42]) because driver/stmt.go expects fmt.Sprintf("%v", args[i]) to produce parseable SQL.

Suggested fixes

One option: interpolate []byte as "$$" + string(byteArray) + "$$" and hope byteArray doesn't contain any dollar signs
Another option: teach engine/parser/lexer.go to lex interpolated byte arrays, i.e. [num num num ...] where each num is 0 ≤ num ≤ 255
Third option (my favorite): come up with an interpolation syntax that's more similar to standard SQL (e.g. 0x48656c6c6f for "Hello") and have driver/stmt.go format []byte as that

Can not use NOT IN in SELECT statements

SELECT statements do not accept the NOT IN syntax.

If a SELECT statement is like the following:

SELECT foo
FROM bar
WHERE baz NOT IN ('qux', 'shme')

Then the parser issues an error like the following:

Syntax error near baz not in

Sometimes ORM wrap columns with quotes

Hi @proullon ,

Some ORM wrap columns with quotes as in the example of GORM:

UPDATE "challenges" SET "deleted_at"='2022-07-11 14:52:45.804' WHERE "challenges"."deleted_at" IS NULL

But I get this error:

Syntax error near . \" deleted_at

Note that when testing the followings it works perfectly:

UPDATE "challenges" SET "deleted_at"='2022-07-11 14:52:45.804' WHERE "challenges".deleted_at IS NULL

UPDATE "challenges" SET "deleted_at"='2022-07-11 14:52:45.804' WHERE challenges.deleted_at IS NULL

Is it possible you accept quotes for columns as you do for tables?

Thank you,

Handling brackets in WHERE clauses

Hi,

Same as the old issue #27 that is closed.

The error I get is:

Syntax error near where ( created_at

With the query:

SELECT * FROM "users" WHERE (created_at < '2019-12-31 00:00:00')

(I had a more complex query but I reduced it to show it comes from brackets even with no nested conditions)

@proullon as you mentioned in #62 (comment) I really do understand the pain of handling all parsing cases. Any news on handling the SQL BNF? Maybe you could add the comment you wrote on the README.md so that people see it?

(in the meantime I will go with a sidecar container of my DB to handle all complex cases with GORM)

Thank you for your work, it's still useful to test common cases without dealing with external binary!

Syntax error for AUTO_INCREMENT

CREATE TABLE address (id bigint AUTO_INCREMENT PRIMARY KEY)

Syntax error near bigint AUTO_INCREMENT primary

Implement transactions

parsing error near unique not null

with ramsql i get the above error on this statement,

create table tables(name text unique not null)

which works fine if i use the sqlite3 driver instead.

fails to Scan() a TIMESTAMP or TIMESTAMPTZ value into a time.Time variable

While running my tests I get this error:

sql: Scan error on column index 5, name "created_at": unsupported Scan, storing driver.Value type []uint8 into type *time.Time

which happens when this runs:

	p := entity.Property{}

	query := `
		SELECT id, street, city, state, zip, created_at
		FROM properties WHERE id = $1
	`

	err := db.QueryRowContext(ctx, query, id).Scan(
		&p.ID, &p.Street, &p.City,
		&p.StateCode, &p.Zip, &p.CreatedAt,
	)

During my test, the table is created via:



func loadMigrations(db *sql.DB) error {
	query := `
	  CREATE TABLE IF NOT EXISTS properties (
			id         VARCHAR(36)  PRIMARY KEY,
			street     VARCHAR(255),
			city       VARCHAR(32),
			state      VARCHAR(32),
			zip        VARCHAR(10),
			created_at TIMESTAMPTZ
		)
	`
	_, err = db.Exec(query)
	return err
}

I have the same tests run passing in a sql.DB from github.com/ory/dockertest and they all pass just fine, but because it creates a real postgres db inside a docker container for the test, it is a bit slow. I was hoping to put a build tag on those for integration tests and use ramsql as a much faster way to test my repository.

Support for default values in TIMESTAMP WITH TIME ZONE column

CREATE TABLE IF NOT EXISTS "my_table" (id BIGSERIAL PRIMARY KEY, name TEXT, last_modified TIMESTAMP WITH TIME ZONE DEFAULT statement_timestamp())

would be awesome !

return id

When I have a table created like this:

CREATE TABLE myTable (id SERIAL PRIMARY KEY, field varchar);

and I do a query like this:

INSERT INTO myTable (field) VALUES (:field) RETURNING id;

I expect the id to be filled with the inserted id of the new row.

will porting to lmdb / boltdb backend be on the roadmap?

parsing error near <OFFSET>

this query doesn't work for me under ramsql . it does work if i import "github.com/mattn/go-sqlite3" instead. or if i delete the "OFFSET 0".

SELECT * FROM mytable LIMIT 1 OFFSET 0

Implement Mathematical Operators

See http://www.postgresql.org/docs/8.0/static/functions-math.html#FUNCTIONS-MATH-OP-TABLE

Implement GROUP BY

Implement LIMIT

If you use GORM, please use Postgres driver

Hi,

Just here to note that if you intend to use ramsql with the GORM ORM, you should use the GORM Postgres driver, not the MySQL one otherwise you will get an error.

A working example would be:

	sqlDB, err := sql.Open("ramsql", "Test")
	...

	db, err := gorm.Open(postgres.New(postgres.Config{
		Conn: sqlDB,
	}), &gorm.Config{})

Hope it helps 👍

Clubbing with gorm throws error on Automigrate

Getting error while using with gorm.

Error

DB.AutoMigrate(&VSettings{}) throws

2021/05/26 13:35:13 src/ramsql.go:83 Syntax error near BIGINT UNSIGNED not
[0.094ms] [rows:0] CREATE TABLE `vsettings` (`id` BIGINT UNSIGNED NOT NULL,`vid` BIGINT UNSIGNED NOT NULL,`tid` BIGINT UNSIGNED NOT NULL,`settings` JSON NOT NULL,`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`))
2021/05/26 13:35:13 Syntax error near BIGINT UNSIGNED not

My implementation for clubbing with gorm

import (
	"database/sql"
	"encoding/json"
	_ "github.com/proullon/ramsql/driver"
	"gorm.io/datatypes"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"log"
	"time"
)

type ID uint64

// VSettings [...]
type VSettings struct {
	ID        ID             `gorm:"id;type:BIGINT UNSIGNED NOT NULL;primaryKey;autoIncrement;"`
	VID       ID             `gorm:"vid;type:BIGINT UNSIGNED NOT NULL;"`
	TID       ID             `gorm:"tid;type:BIGINT UNSIGNED NOT NULL;"`
	Settings  datatypes.JSON `gorm:"settings;type:JSON;NOT NULL" json:"settings"`
	CreatedAt time.Time      `gorm:"created_at;type:TIMESTAMP;default:CURRENT_TIMESTAMP;"`
	UpdatedAt time.Time      `gorm:"updated_at;type:TIMESTAMP;default:CURRENT_TIMESTAMP;OnUpdate:CURRENT_TIMESTAMP;"`
	//DeletedAt *time.Time     `gorm:"deleted_at;type:TIMESTAMP"`
}

// TableName get sql table name.
func (m *VSettings) TableName() string {
	return "vsettings"
}

func main() {
	sqlDB, err := sql.Open("ramsql", dbDSN)

	if err != nil {
		log.Fatalf("sql.Open : Error : %v\n", err)
	}

	defer func(db *sql.DB) {
		err := db.Close()
		if err != nil {
			log.Fatalf("sqlDB.Close : Error : %v\n", err)
		}
	}(sqlDB)

	DB, err := gorm.Open(
		mysql.New(
			mysql.Config{
				Conn:                      sqlDB,
				SkipInitializeWithVersion: true,
			},
		),
		&gorm.Config{
			DisableForeignKeyConstraintWhenMigrating: true,
		},
	)

	if err != nil {
		log.Fatalf("gorm.Open : Error : %v\n", err)
	}

	err = DB.AutoMigrate(&VSettings{})
	if err != nil {
		log.Println(err)
		log.Fatalf("AutoMigrate VSettings : Error : %v\n", err)
	}
}

Can not set column to NULL in UPDATE statements

The UPDATE statements do not allow setting columns to NULL.

If an UPDATE statement is like the following:

UPDATE foo
SET bar = NULL
WHERE baz = 'qux'

Then the parser issues an error like the following:

Syntax error near = null ,

what happens if oom?

what happens if oom? is that possible?

how do u specify memory size so that it doesnt oom?

Implement UNIQUE

Implement Order By on multiple column

http://www.postgresql.org/docs/9.4/static/queries-order.html

Used with GORM resulting in errors for simple queries. May be relate to column name not lowercase

result := db.First(&Request{}, "Token = ?", request.Token)

Results in: Syntax error near . " token"

Handle RETURNING on UPDATE query

Support true and false for values in argument values for Exec, Query

db.Exec("INSERT INTO table (id, hasBool) VALUES(?,?)", 0, true)

returns with no strconv error.

Support character varying

When exec'd, character varying(255) errors with cannot execute : Syntax error near character varchar ( while varchar(32) works fine.

Please add support for character varying, which is the ANSISQL standard alias for varchar.

Deadlock with multi-statement exec

If you do an exec call with multiple statements, it creates a deadlock. Any help debugging this would be appreciated.

(You can add this function to driver_test.go to see the failure)

`
func TestBigBatch(t *testing.T) {
log.UseTestLogger(t)

batch :=
	`CREATE TABLE address (id BIGSERIAL PRIMARY KEY, street TEXT, street_number INT);
	CREATE TABLE user_addresses (address_id INT, user_id INT);
	INSERT INTO address (street, street_number) VALUES ('rue victor hugo', 32);
	INSERT INTO address (street, street_number) VALUES ('boulevard de la république', 23);
	INSERT INTO address (street, street_number) VALUES ('rue charles martel', 5);
	INSERT INTO address (street, street_number) VALUES ('rue victoire', 323);
	INSERT INTO address (street, street_number) VALUES ('boulevard de la liberté', 2);
	INSERT INTO address (street, street_number) VALUES ('avenue des champs', 12);
	INSERT INTO user_addresses (address_id, user_id) VALUES (2, 1);
	INSERT INTO user_addresses (address_id, user_id) VALUES (4, 1);
	INSERT INTO user_addresses (address_id, user_id) VALUES (2, 2);
	INSERT INTO user_addresses (address_id, user_id) VALUES (2, 3);
	INSERT INTO user_addresses (address_id, user_id) VALUES (4, 4);
	INSERT INTO user_addresses (address_id, user_id) VALUES (4, 5);`

db, err := sql.Open("ramsql", "TestBigBatch")
if err != nil {
	t.Fatalf("sql.Open : Error : %s\n", err)
}
defer db.Close()

_, err = db.Exec(batch)
if err != nil {
	t.Fatalf("sql.Exec: Error: %s\n", err)
}

addresses, err := LoadUserAddresses(db, 1)
if err != nil {
	t.Fatalf("Too bad! unexpected error: %s", err)
}

if len(addresses) != 2 {
	t.Fatalf("Expected 2 addresses, got %d", len(addresses))
}

}
`

Insert: check attribute type

Provide MySQL compatible implementation

Is this pretty challenging, or even possible? I do love Postgres, but MySQL compatibility would be a big plus to this library.

select/update/insert : check existence of attributes

Check at the begining of the query that used attributes exists

update set not support add

s.db.Exec(UPDATE players SET wins = wins + 1 WHERE name = $1;, name)

2019/02/28 17:12:26 [WARNING] Cannot lex <UPDATE players SET wins = wins + 1 WHERE name = $$Pepper$$;>, stuck at pos 31 -> [+]
panic: Cannot lex instruction. Syntax error near + 1 WHERE name = $$Pepper$$; [recovered]
panic: Cannot lex instruction. Syntax error near + 1 WHERE name = $$Pepper$$;

	func (l *lexer) MatchAscToken() bool {
	return l.Match([]byte("desc"), DescToken)
	}

	func (l *lexer) MatchDescToken() bool {
	return l.Match([]byte("asc"), AscToken)
	}