XDRX-API 操作 SQLite 数据库函数介绍
本帖最后由 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');" 93.14159 "welcome to the xdcad.net")
;(xdrx-sqlite-printf "insert into test4 values (%d, %.15g, '%s');" 13.14159 "welcome to the xdcad.net")
;(xdrx-sqlite-getformat (entget(car(entsel))))
;(xdrx-sqlite-printfx "insert into test4 values (%d, %.15g, '%s');" 13.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');" 83.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-dmldb "commit transaction;"))
(chkerr (xdrx-sqlite-closedb))
(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');" 93.14159 "welcome to the xdcad.net")
;(xdrx-sqlite-printf "insert into test4 values (%d, %.15g, '%s');" 13.14159 "welcome to the xdcad.net")
;(xdrx-sqlite-getformat (entget(car(entsel))))
;(xdrx-sqlite-printfx "insert into test4 values (%d, %.15g, '%s');" 13.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');" 83.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
https://sqlitebrowser.org/images/screenshot.png
[*]DB.Browser.for.SQLite-3.12.1-win32-v2.msi - Standard (MSI) installer for Win32 and WinXP
[*]DB.Browser.for.SQLite-3.12.1-win32.zip - .zip (no installer) for Win32 and WinXP
[*]DB.Browser.for.SQLite-3.12.1-win64-v2.msi - Standard (MSI) installer for Win64
[*]DB.Browser.for.SQLite-3.12.1-win64.zip - .zip (no installer) for Win64
[*]DB.Browser.for.SQLite-3.12.1-v2.dmg - For macOS
数据库管理软件Navicat_Premium15x64的解压密码是啥 高大上，终于sql了 感谢楼主的分享， 太厉害了， 给力！
页:
[1]