| 
UID5280积分9539精华贡献 威望 活跃度 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
 
 
 
 
 
 | 
 |