- UID
- 5280
- 积分
- 9466
- 精华
- 贡献
-
- 威望
-
- 活跃度
-
- D豆
-
- 在线时间
- 小时
- 注册时间
- 2002-5-18
- 最后登录
- 1970-1-1
|
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有账号?立即注册
×
本帖最后由 newer 于 2021-1-28 23:06 编辑
XDRX API 对 SQLITE 数据库支持的函数有:
- xdrx-sqlite-open
- xdrx-sqlite-close
- xdrx-sqlite-closeall
- xdrx-sqlite-query
- xdrx-sqlite-assocquery
- xdrx-sqlite-dml
- xdrx-sqlite-scalar
- xdrx-sqlite-scalar
- xdrx-sqlite-cmpstmt
- xdrx-sqlite-stmtbind
- xdrx-sqlite-stmtfnl
- xdrx-sqlite-ver
- xdrx-sqlite-sqlitever
- xdrx-sqlite-printf
- xdrx-sqlite-getformat
- xdrx-sqlite-printfx
- xdrx-sqlite-getformatx
- xdrx-sqlite-loadext
- xdrx-sqlite-lasterr
- xdrx-sqlite-dumperr
- xdrx-sqlite-keywords
复制代码
- (setq db "d:\\xdsoft\\mysqlite.db")
1、xdrx-sqlite-open
打开数据库
(xdrx-sqlite-open db)
2、xdrx-sqlite-close
关闭数据库
(xdrx-sqlite-close db)
3、xdrx-sqlite-closeall
关闭所有打开的数据库
(xdrx-sqlite-closeall)
示例代码:
- (defun createdatabse ()
- (chkerr (xdrx-sqlite-open mydb))
- (chkerr (xdrx-sqlite-close mydb))
- )
- (defun c:enablekeys ( / a db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (xdrx-sqlite-query db "pragma foreign_keys = on;")
- (setq a (xdrx-sqlite-query db "pragma foreign_keys;"))
- (chkerr (xdrx-sqlite-close db))
- a
- )
4、xdrx-sqlite-query
数据表查询函数
(xdrx-sqlite-query db query)
例如:
- (xdrx-sqlite-query db "pragma foreign_keys = on;")
- (xdrx-sqlite-query db "select * from mytable;")
5、xdrx-sqlite-assocquery
将参数替换查询字符串的 %d, %f......后,进行查询
例如:
- (xdrx-sqlite-assocquery db "select * from mytable where age=%d;" 101)
6、xdrx-sqlite-dml
数据库编辑函数,支持 insert,update,delete......
- (xdrx-sqlite-dml db "insert into mytable values (2, 'mickey', 'mouse' , 101);")
上面是主要函数,上面的函数基本上能做很多工作了,数据库主要是打开,关闭,创建,查询,插入,更新,删除这些操作
- ;;; create a new table
- (defun c:createtable ( / db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (chkerr (xdrx-sqlite-dml db "create table mytable (no int, firstname char(64), lastname char(64), age int);"))
- (chkerr (xdrx-sqlite-close db))
- )
- ;;; insert data
- (defun c:addstuff (/ db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (chkerr (xdrx-sqlite-dml db "insert into mytable values (1, 'donald', 'luck ' , 99);"))
- (chkerr (xdrx-sqlite-dml db "insert into mytable values (2, 'mickey', 'mouse' , 101);"))
- (chkerr (xdrx-sqlite-dml db "insert into mytable values (3, 'minni' , 'mouse' , 29);"))
- (chkerr (xdrx-sqlite-close db))
- )
命令行执行完createtable, addstuff后,用数据库编辑软件打开mysqlite.db文件
- ;;; get data, note all queries are returned in the format
- ;;; ((col1 name col2 name col3 name)(data1 data2 data3)....)
- ;;; exeptions are return as
- ;;; (nil . error message)
- (defun c:getme ( / db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (setq a (xdrx-sqlite-query db "select * from mytable;"))
- (chkerr (xdrx-sqlite-close db))
- a
- )
命令行执行getme后得到:
(("no" "firstname" "lastname" "age") (1 "donald" "luck " 99) (2 "mickey" "mouse" 101) (3 "minni" "mouse" 29))
查询年龄大于30的符合条件的记录:
- (defun c:getage>30 ( / db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (setq a (xdrx-sqlite-query db "select * from mytable where age > 30;"))
- (chkerr (xdrx-sqlite-close db))
- a
- )
命令: GETAGE>30
(("no" "firstname" "lastname" "age") (1 "donald" "luck " 99) (2 "mickey" "mouse" 101))
下面是辅助函数的用法:
- ;(xdrx-sqlite-printf "-%x-" 16777215)
- ;(xdrx-sqlite-getformat (entget(car(entsel))))
- ;(xdrx-sqlite-printf "(%ld)(%s)(%ld)(%s)(%s)(%d)(%s)(%s)(%s)(%.15g,%.15g,%.15g)(%.15g,%.15g,%.15g)(%.15g,%.15g,%.15g)" (entget(car(entsel))))
- ;(xdrx-sqlite-getformatx (entget(car(entsel))))
- ;(xdrx-sqlite-printfx (strcat "(%d,%ld)(%d,%s)(%d,%ld)(%d,%s)(%d,%s)(%d,%d)(%d,%s)(%d,%s)(%d,%s)(%d,%.15g,%.15"
- ; "g,%.15g)(%d,%.15g,%.15g,%.15g)(%d,%.15g,%.15g,%.15g)") (entget(car(entsel))))
- ;(xdrx-sqlite-dml "d:\\xdsoft\\mysqlite.db" "insert into test4 values (%d, %.15g, '%s');" 9 3.14159 "welcome to the xdcad.net")
- ;(xdrx-sqlite-printf "insert into test4 values (%d, %.15g, '%s');" 1 3.14159 "welcome to the xdcad.net")
- ;(xdrx-sqlite-getformat (entget(car(entsel))))
- ;(xdrx-sqlite-printfx "insert into test4 values (%d, %.15g, '%s');" 1 3.14159 "welcome to the xdcad.net")
- ;(xdrx-sqlite-getformatx (entget(car(entsel))))
- ;(xdrx-sqlite-printf "%s %s" t nil)
- (xdrx-sqlite-printf "insert into test4 values (%d, %.15g, '%s');" 8 3.14159 "welcome to the xdcad.net")
- ;(xdrx-sqlite-open "d:\\xdsoft\\mysqlite.db")
- ;(chkerr(xdrx-sqlite-dml "d:\\xdsoft\\mysqlite.db" "create table 5(no int, num1 int, num2 int, num3 float);"))
- ;(xdrx-sqlite-close "d:\\xdsoft\\mysqlite.db")
下面是示例应用:
- ;xdrx-sqlite-open
- ;xdrx-sqlite-close
- ;xdrx-sqlite-closeall
- ;xdrx-sqlite-query
- ;xdrx-sqlite-assocquery
- ;xdrx-sqlite-dml
- ;xdrx-sqlite-scalar
- ;xdrx-sqlite-scalar
- ;xdrx-sqlite-cmpstmt
- ;xdrx-sqlite-stmtbind
- ;xdrx-sqlite-stmtfnl
- ;xdrx-sqlite-ver
- ;xdrx-sqlite-sqlitever
- ;xdrx-sqlite-printf
- ;xdrx-sqlite-getformat
- ;xdrx-sqlite-printfx
- ;xdrx-sqlite-getformatx
- ;xdrx-sqlite-loadext
- ;xdrx-sqlite-lasterr
- ;xdrx-sqlite-dumperr
- ;xdrx-sqlite-keywords
- (defun chkerr (cmd)
- (if (not cmd)
- (progn
- (princ (xdrx-sqlite-lasterr))
- nil
- )
- t
- )
- )
- (setq mydb "d:\\xdsoft\\mysqlite.db")
- (defun createdatabse ()
- (chkerr (xdrx-sqlite-open mydb))
- (chkerr (xdrx-sqlite-close mydb))
- )
- (defun c:enablekeys ( / a db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (xdrx-sqlite-query db "pragma foreign_keys = on;")
- (setq a (xdrx-sqlite-query db "pragma foreign_keys;"))
- (chkerr (xdrx-sqlite-close db))
- a
- )
- ;;; create a new table
- (defun c:createtable ( / db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (chkerr (xdrx-sqlite-dml db "create table mytable (no int, firstname char(64), lastname char(64), age int);"))
- (chkerr (xdrx-sqlite-close db))
- )
- ;;; insert data
- (defun c:addstuff (/ db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (chkerr (xdrx-sqlite-dml db "insert into mytable values (1, 'donald', 'luck ' , 99);"))
- (chkerr (xdrx-sqlite-dml db "insert into mytable values (2, 'mickey', 'mouse' , 101);"))
- (chkerr (xdrx-sqlite-dml db "insert into mytable values (3, 'minni' , 'mouse' , 29);"))
- (chkerr (xdrx-sqlite-close db))
- )
- ;;; create a table json
- (defun c:createtablej ( / db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (chkerr (xdrx-sqlite-dml db "create table user (name, phone);"))
- (chkerr (xdrx-sqlite-close db))
- )
- ;;; insert data json
- (defun c:addstuffj (/ db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('jenny', json('{\"cell\":\"+491765\", \"home\":\"704-8675309\"}'))"))
- (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('oz', json('{\"cell\":\"+491765\", \"home\":\"704-498973\"}'))"))
- (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('mick', json('{\"cell\":\"+591765\", \"home\": \"705-598973\"}'))"))
- (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('dude', json('{\"cell\":\"+691765\", \"home\":\"704-698973\"}'))"))
- (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('sally', json('{\"cell\":\"+591765\", \"home\": \"705-578973\"}'))"))
- (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('vicky', json('{\"cell\":\"+691765\", \"home\":\"704-698973\"}'))"))
- (chkerr (xdrx-sqlite-close db))
- )
- ;;; get data json
- (defun c:getmej ( / db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (setq a (xdrx-sqlite-query db "select user.name from user, json_each(user.phone) where json_each.value like '704-%';"))
- (chkerr (xdrx-sqlite-close db))
- a
- )
- ;;(("name") ("jenny") ("oz") ("dude") ("vicky"))
- ;;; get data, note all queries are returned in the format
- ;;; ((col1 name col2 name col3 name)(data1 data2 data3)....)
- ;;; exeptions are return as
- ;;; (nil . error message)
- (defun c:getme ( / db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (setq a (xdrx-sqlite-query db "select * from mytable;"))
- (chkerr (xdrx-sqlite-close db))
- a
- )
- (defun c:getme2 ( / a db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (setq a(xdrx-sqlite-assocquery db "select * from mytable where age=%d;" 101))
- (chkerr (xdrx-sqlite-close db))
- a
- )
- (defun c:testme ( / a db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (setq a (xdrx-sqlite-query db "select avg(age) from mytable;"))
- (chkerr (xdrx-sqlite-close db))
- a
- )
- (defun c:getmea ( / a db)
- (setq db mydb)
- (chkerr (xdrx-sqlite-open db))
- (setq a (xdrx-sqlite-assocquery db "select * from mytable;"))
- (chkerr (xdrx-sqlite-close db))
- a
- )
- ;;; update a record,
- (defun c:changeme ( / db)
- (setq db mydb)
- (chkerr(xdrx-sqlite-open db))
- (chkerr(xdrx-sqlite-dml db "update mytable set lastname='duck' where no=1;"))
- (chkerr(xdrx-sqlite-close db))
- )
- ;;; run getme to see the changes
- ;;; a compiled statement example
- (defun c:compilestmt ( / db i)
- (setq i 0)
- (setq db mydb)
- ; create a new db
- (chkerr(xdrx-sqlite-open db))
- ; make a new table
- (chkerr(xdrx-sqlite-dml db "create table test2(no int, num1 int, num2 float, name char(64));"))
- ; start a transaction
- (chkerr(xdrx-sqlite-dml db "begin transaction;"))
- ; this is our compiled statement, we will bind data to ?
- (chkerr(xdrx-sqlite-cmpstmt db "insert into test2 values (?, ?, ?, ?);"))
- ; add our values
- (repeat 100
- (chkerr(xdrx-sqlite-stmtbind (setq i (1+ i)) 87 12.012 "helloworld"))
- )
- ; commit the transaction
- (chkerr(xdrx-sqlite-dml db "commit transaction;"))
- ; we must call this to clear the compiled statement
- ; and finalize the transaction
- (chkerr(xdrx-sqlite-stmtfnl))
- ; close the db
- (chkerr(xdrx-sqlite-close db))
- )
- ;;; check our data
- ;progecad 2.000,
- ;;; lets run a performance test, create a table and insert 10,000 records
- (defun c:bench1 ( / db end i start)
- (setq i 0)
- (setvar "cmdecho" 0)
- (setq start (getvar "tdusrtimer"))
- (setq db mydb)
- (chkerr(xdrx-sqlite-open db))
- (chkerr(xdrx-sqlite-dml db "create table test1(no int, name char(64));"))
- (chkerr(xdrx-sqlite-dml db "begin transaction;"))
- (repeat 1000
- (chkerr(xdrx-sqlite-dml db
- (strcat "insert into test1 values ("
- (itoa (setq i (1+ i))) ", 'welcome to XDCAD.Net');")))
- )
- (chkerr (xdrx-sqlite-dml db "commit transaction;"))
- (chkerr (xdrx-sqlite-close db))
- (setq end (* 86400.0 (- (getvar "tdusrtimer") start)))
- (princ "\n")
- (princ end)
- (princ)
- )
- ;;; autocad = 1.515 , bricscad = 0.967969 seconds on my old paint
- ;1.00000
- (defun c:bench2 ( / db end i pi start)
- (setq i 0)
- (setvar "cmdecho" 0)
- (setq start (getvar "tdusrtimer"))
- (setq db mydb)
- (chkerr(xdrx-sqlite-open db))
- (chkerr(xdrx-sqlite-dml db "create table test3(no int, num1 int, num2 int, num3 float);"))
- (chkerr(xdrx-sqlite-cmpstmt db "insert into test3 values (?, ?, ?, ?);"))
- (chkerr(xdrx-sqlite-dml db "begin transaction;"))
- (repeat 10000
- (chkerr(xdrx-sqlite-stmtbind (setq i (1+ i)) 87 12 3.13))
- )
- (chkerr (xdrx-sqlite-dml db "commit transaction;"))
- (xdrx-sqlite-stmtfnl)
- (chkerr (xdrx-sqlite-close db))
- (setq end (* 86400.0 (- (getvar "tdusrtimer") start)))
- (princ "\n")
- (princ end)
- (princ)
- )
- (defun c:bench3 ( / db end i pi start)
- (setq i 0)
- (setvar "cmdecho" 0)
- (setq start (getvar "tdusrtimer"))
- (setq db mydb)
- (chkerr(xdrx-sqlite-open db))
- (repeat 100
- (setq q(xdrx-sqlite-query mydb "select * from test3;"))
- )
- (chkerr (xdrx-sqlite-close db))
- (setq end (* 86400.0 (- (getvar "tdusrtimer") start)))
- (princ "\n")
- (princ end)
- (princ)
- )
- ;;; autocad = 1.703 seconds on my old paint
- (defun c:bench4 ( / db end i start)
- (setq i 0)
- (setvar "cmdecho" 0)
- (setq start (getvar "tdusrtimer"))
- (setq db mydb)
- (chkerr(xdrx-sqlite-open db))
- (chkerr(xdrx-sqlite-dml db "create table test4(no int, num float ,name char(64));"))
- (chkerr(xdrx-sqlite-dml db "begin transaction;"))
- (repeat 10000
- (chkerr(xdrx-sqlite-dml mydb "insert into test4 values (%d, %.15g, '%s');" (setq i (1+ i)) 3.14159 "welcome to the xdcad.net"))
- )
- (chkerr (xdrx-sqlite-dml db "commit transaction;"))
- (chkerr (xdrx-sqlite-close db))
- (setq end (* 86400.0 (- (getvar "tdusrtimer") start)))
- (princ "\n")
- (princ end)
- (princ)
- )
- ;(xdrx-sqlite-printf "-%x-" 16777215)
- ;(xdrx-sqlite-getformat (entget(car(entsel))))
- ;(xdrx-sqlite-printf "(%ld)(%s)(%ld)(%s)(%s)(%d)(%s)(%s)(%s)(%.15g,%.15g,%.15g)(%.15g,%.15g,%.15g)(%.15g,%.15g,%.15g)" (entget(car(entsel))))
- ;(xdrx-sqlite-getformatx (entget(car(entsel))))
- ;(xdrx-sqlite-printfx (strcat "(%d,%ld)(%d,%s)(%d,%ld)(%d,%s)(%d,%s)(%d,%d)(%d,%s)(%d,%s)(%d,%s)(%d,%.15g,%.15"
- ; "g,%.15g)(%d,%.15g,%.15g,%.15g)(%d,%.15g,%.15g,%.15g)") (entget(car(entsel))))
- ;(xdrx-sqlite-dml "d:\\xdsoft\\mysqlite.db" "insert into test4 values (%d, %.15g, '%s');" 9 3.14159 "welcome to the xdcad.net")
- ;(xdrx-sqlite-printf "insert into test4 values (%d, %.15g, '%s');" 1 3.14159 "welcome to the xdcad.net")
- ;(xdrx-sqlite-getformat (entget(car(entsel))))
- ;(xdrx-sqlite-printfx "insert into test4 values (%d, %.15g, '%s');" 1 3.14159 "welcome to the xdcad.net")
- ;(xdrx-sqlite-getformatx (entget(car(entsel))))
- ;(xdrx-sqlite-printf "%s %s" t nil)
- (xdrx-sqlite-printf "insert into test4 values (%d, %.15g, '%s');" 8 3.14159 "welcome to the xdcad.net")
- ;(xdrx-sqlite-open "d:\\xdsoft\\mysqlite.db")
- ;(chkerr(xdrx-sqlite-dml "d:\\xdsoft\\mysqlite.db" "create table 5(no int, num1 int, num2 int, num3 float);"))
- ;(xdrx-sqlite-close "d:\\xdsoft\\mysqlite.db")
数据库管理软件 Navicat Premium 15.02注册版
SQLite数据库管理软件 DB Browser
|
|