Topic: [XDRx-SQL]LISP Operation SQLite database

[xdcad]

What Is SQLite?

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. More Information...

Allowing AUTOLISP to support SQLITE will greatly improve the functions of LISP. Many operations can be enriched and efficiently performed with the help of SQL database query statements.
Such as LISP LIST keyword query, sorting, Table insert, delete, update...
Natural handling of LISP LIST table structures


XDRX API wraps SQLite.
The functions supported by XDRX API for SQLITE database are:

Code: [Select]

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


Code: [Select]

(setq db "d:\\xdsoft\\mysqlite.db")
1.xdrx-sqlite-open
Open database

(xdrx-sqlite-opendb)

2.xdrx-sqlite-close
Close database

(xdrx-sqlite-close db)

3.xdrx-sqlite-closeall
Close all open databases

(xdrx-sqlite-closeall)

Sample code:

Code - Auto/Visual Lisp: [Select]

1. (defun createdatabse ()
2.   (chkerr (xdrx-sqlite-open mydb))
3.   (chkerr (xdrx-sqlite-close mydb))
4. )
5.  
6. (defun c:enablekeys ( / a db)
7.   (setq db mydb)
8.   (chkerr (xdrx-sqlite-open db))
9.   (xdrx-sqlite-query db "pragma foreign_keys = on;")
10.   (setq a (xdrx-sqlite-query db "pragma foreign_keys;"))
11.   (chkerr (xdrx-sqlite-close db))
12.   a
13. )

4. xdrx-sqlite-query
Data table query function

(xdrx-sqlite-query db query)

For example:

Code - Auto/Visual Lisp: [Select]

1. (xdrx-sqlite-query db "pragma foreign_keys = on;")
2. (xdrx-sqlite-query db "select * from mytable;")

5.xdrx-sqlite-assocquery
After replacing %d, %f... of the query string with parameters, perform the query

For example:

Code - Auto/Visual Lisp: [Select]

1. (xdrx-sqlite-assocquery db "select * from mytable where age=%d;" 101)

6.xdrx-sqlite-dml
Database editing functions, supporting insert, update, delete...

Code - Auto/Visual Lisp: [Select]

1. (xdrx-sqlite-dml db "insert into mytable values (2, 'mickey', 'mouse' , 101);")

The above is the main function. The above function can basically do a lot of work. The database mainly performs operations such as opening, closing, creating, querying, inserting, updating, and deleting.

Code - Auto/Visual Lisp: [Select]

1. ;;; create a new table
2. (defun c:createtable ( / db)
3.   (setq db mydb)
4.   (chkerr (xdrx-sqlite-open db))
5.   (chkerr (xdrx-sqlite-dml db "create table mytable (no int, firstname char(64), lastname char(64), age int);"))
6.   (chkerr (xdrx-sqlite-close db))
7. )
8.  
9. ;;; insert data
10. (defun c:addstuff (/ db)
11.   (setq db mydb)
12.   (chkerr (xdrx-sqlite-open db))
13.   (chkerr (xdrx-sqlite-dml db "insert into mytable values (1, 'donald', 'luck ' , 99);"))
14.   (chkerr (xdrx-sqlite-dml db "insert into mytable values (2, 'mickey', 'mouse' , 101);"))
15.   (chkerr (xdrx-sqlite-dml db "insert into mytable values (3, 'minni' , 'mouse' , 29);"))
16.   (chkerr (xdrx-sqlite-close db))
17. )

After executing createtable and addstuff on the above command line, use database editing software to open the mysqlite.db file.

Code - Auto/Visual Lisp: [Select]

1. ;;; get data, note all queries are returned in the format
2. ;;; ((col1 name col2 name col3 name)(data1 data2 data3)....)
3. ;;; exeptions are return as
4. ;;; (nil . error message)
5. (defun c:getme ( / db)
6.   (setq db mydb)
7.   (chkerr (xdrx-sqlite-open db))
8.   (setq a (xdrx-sqlite-query db "select * from mytable;"))
9.   (chkerr (xdrx-sqlite-close db))
10.   a
11. )

After executing getme on the command line, we get:

(("no" "firstname" "lastname" "age") (1 "donald" "luck " 99) (2 "mickey" "mouse" 101) (3 "minni" "mouse" 29))

Query eligible records with age greater than 30:

Code - Auto/Visual Lisp: [Select]

1. (defun c:getage>30 ( / db)
2.   (setq db mydb)
3.   (chkerr (xdrx-sqlite-open db))
4.   (setq a (xdrx-sqlite-query db "select * from mytable where age > 30;"))
5.   (chkerr (xdrx-sqlite-close db))
6.   a
7. )

Command: GETAGE>30
(("no" "firstname" "lastname" "age") (1 "donald" "luck " 99) (2 "mickey" "mouse" 101))

The following is the usage of auxiliary functions:

Code - Auto/Visual Lisp: [Select]

1. ;(xdrx-sqlite-printf "-%x-" 16777215)
2. ;(xdrx-sqlite-getformat (entget(car(entsel))))
3. ;(xdrx-sqlite-printf "(%ld)(%s)(%ld)(%s)(%s)(%d)(%s)(%s)(%s)(%.15g,%.15g,%.15g)(%.15g,%.15g,%.15g)(%.15g,%.15g,%.15g)" (entget(car(entsel))))
4. ;(xdrx-sqlite-getformatx (entget(car(entsel))))
5. ;(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"
6. ;                      "g,%.15g)(%d,%.15g,%.15g,%.15g)(%d,%.15g,%.15g,%.15g)") (entget(car(entsel))))
7. ;(xdrx-sqlite-dml "d:\\xdsoft\\mysqlite.db" "insert into test4 values (%d, %.15g, '%s');" 9  3.14159 "welcome to the xdcad.net")
8. ;(xdrx-sqlite-printf "insert into test4 values (%d, %.15g, '%s');" 1  3.14159 "welcome to the xdcad.net")
9. ;(xdrx-sqlite-getformat (entget(car(entsel))))
10. ;(xdrx-sqlite-printfx "insert into test4 values (%d, %.15g, '%s');" 1  3.14159 "welcome to the xdcad.net")
11. ;(xdrx-sqlite-getformatx (entget(car(entsel))))
12. ;(xdrx-sqlite-printf "%s %s" t nil)
13. (xdrx-sqlite-printf "insert into test4 values (%d, %.15g, '%s');" 8  3.14159 "welcome to the xdcad.net")
14. ;(xdrx-sqlite-open  "d:\\xdsoft\\mysqlite.db")
15. ;(chkerr(xdrx-sqlite-dml "d:\\xdsoft\\mysqlite.db" "create table 5(no int, num1 int, num2 int, num3 float);"))
16. ;(xdrx-sqlite-close "d:\\xdsoft\\mysqlite.db")

Here is a sample application:

Code - Auto/Visual Lisp: [Select]

1. ;xdrx-sqlite-open
2. ;xdrx-sqlite-close
3. ;xdrx-sqlite-closeall
4. ;xdrx-sqlite-query
5. ;xdrx-sqlite-assocquery
6. ;xdrx-sqlite-dml
7. ;xdrx-sqlite-scalar
8. ;xdrx-sqlite-scalar
9. ;xdrx-sqlite-cmpstmt
10. ;xdrx-sqlite-stmtbind
11. ;xdrx-sqlite-stmtfnl
12. ;xdrx-sqlite-ver
13. ;xdrx-sqlite-sqlitever
14. ;xdrx-sqlite-printf
15. ;xdrx-sqlite-getformat
16. ;xdrx-sqlite-printfx
17. ;xdrx-sqlite-getformatx
18. ;xdrx-sqlite-loadext
19. ;xdrx-sqlite-lasterr
20. ;xdrx-sqlite-dumperr
21. ;xdrx-sqlite-keywords
22.  
23.  
24. (defun chkerr (cmd)
25.   (if (not cmd)
26.     (progn
27.       (princ (xdrx-sqlite-lasterr))
28.       nil
29.     )
30.    t
31.   )
32. )
33.  
34.  
35. (setq mydb "d:\\xdsoft\\mysqlite.db")
36.  
37. (defun createdatabse ()
38.   (chkerr (xdrx-sqlite-open mydb))
39.   (chkerr (xdrx-sqlite-close mydb))
40. )
41.  
42. (defun c:enablekeys ( / a db)
43.   (setq db mydb)
44.   (chkerr (xdrx-sqlite-open db))
45.   (xdrx-sqlite-query db "pragma foreign_keys = on;")
46.   (setq a (xdrx-sqlite-query db "pragma foreign_keys;"))
47.   (chkerr (xdrx-sqlite-close db))
48.   a
49. )
50.  
51. ;;; create a new table
52. (defun c:createtable ( / db)
53.   (setq db mydb)
54.   (chkerr (xdrx-sqlite-open db))
55.   (chkerr (xdrx-sqlite-dml db "create table mytable (no int, firstname char(64), lastname char(64), age int);"))
56.   (chkerr (xdrx-sqlite-close db))
57. )
58.  
59. ;;; insert data
60. (defun c:addstuff (/ db)
61.   (setq db mydb)
62.   (chkerr (xdrx-sqlite-open db))
63.   (chkerr (xdrx-sqlite-dml db "insert into mytable values (1, 'donald', 'luck ' , 99);"))
64.   (chkerr (xdrx-sqlite-dml db "insert into mytable values (2, 'mickey', 'mouse' , 101);"))
65.   (chkerr (xdrx-sqlite-dml db "insert into mytable values (3, 'minni' , 'mouse' , 29);"))
66.   (chkerr (xdrx-sqlite-close db))
67. )
68.  
69. ;;; create a table json
70. (defun c:createtablej ( / db)
71.   (setq db mydb)
72.   (chkerr (xdrx-sqlite-open db))
73.   (chkerr (xdrx-sqlite-dml db "create table user (name, phone);"))
74.   (chkerr (xdrx-sqlite-close db))
75. )
76.  
77. ;;; insert data json
78. (defun c:addstuffj (/ db)
79.   (setq db mydb)
80.   (chkerr (xdrx-sqlite-open db))
81.   (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('jenny', json('{\"cell\":\"+491765\", \"home\":\"704-8675309\"}'))"))
82.   (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('oz', json('{\"cell\":\"+491765\", \"home\":\"704-498973\"}'))"))
83.   (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('mick', json('{\"cell\":\"+591765\", \"home\": \"705-598973\"}'))"))
84.   (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('dude', json('{\"cell\":\"+691765\", \"home\":\"704-698973\"}'))"))
85.   (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('sally', json('{\"cell\":\"+591765\", \"home\": \"705-578973\"}'))"))
86.   (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('vicky', json('{\"cell\":\"+691765\", \"home\":\"704-698973\"}'))"))
87.   (chkerr (xdrx-sqlite-close db))
88. )
89.  
90. ;;; get data json
91. (defun c:getmej ( / db)
92.   (setq db mydb)
93.   (chkerr (xdrx-sqlite-open db))
94.   (setq a (xdrx-sqlite-query db "select user.name from user, json_each(user.phone) where json_each.value like '704-%';"))
95.   (chkerr (xdrx-sqlite-close db))
96.   a
97. )
98.  
99. ;;(("name") ("jenny") ("oz") ("dude") ("vicky"))
100.  
101. ;;; get data, note all queries are returned in the format
102. ;;; ((col1 name col2 name col3 name)(data1 data2 data3)....)
103. ;;; exeptions are return as
104. ;;; (nil . error message)
105. (defun c:getme ( / db)
106.   (setq db mydb)
107.   (chkerr (xdrx-sqlite-open db))
108.   (setq a (xdrx-sqlite-query db "select * from mytable;"))
109.   (chkerr (xdrx-sqlite-close db))
110.   a
111. )
112.  
113. (defun c:getme2 ( / a db)
114.   (setq db mydb)
115.   (chkerr (xdrx-sqlite-open db))
116.   (setq a(xdrx-sqlite-assocquery db "select * from mytable where age=%d;" 101))
117.   (chkerr (xdrx-sqlite-close db))
118.   a
119. )
120.  
121. (defun c:testme ( / a db)
122.   (setq db mydb)
123.   (chkerr (xdrx-sqlite-open db))
124.   (setq a (xdrx-sqlite-query db "select avg(age) from mytable;"))
125.   (chkerr (xdrx-sqlite-close db))
126.   a
127. )
128.  
129.  
130. (defun c:getmea ( / a db)
131.   (setq db mydb)
132.   (chkerr (xdrx-sqlite-open db))
133.   (setq a (xdrx-sqlite-assocquery db "select * from mytable;"))
134.   (chkerr (xdrx-sqlite-close db))
135.   a
136. )
137.  
138. ;;; update a record,
139. (defun c:changeme ( / db)
140.   (setq db mydb)
141.   (chkerr(xdrx-sqlite-open db))
142.   (chkerr(xdrx-sqlite-dml db "update mytable set lastname='duck' where no=1;"))
143.   (chkerr(xdrx-sqlite-close db))
144. )
145. ;;; run getme to see the changes
146.  
147. ;;; a compiled statement example
148. (defun c:compilestmt ( / db i)
149.   (setq i 0)  
150.   (setq db mydb)
151.  
152.   ; create a new db
153.   (chkerr(xdrx-sqlite-open db))  
154.  
155.   ; make a new table
156.   (chkerr(xdrx-sqlite-dml db "create table test2(no int, num1 int, num2 float, name char(64));"))
157.  
158.   ; start a transaction
159.   (chkerr(xdrx-sqlite-dml db "begin transaction;"))
160.  
161.   ; this is our compiled statement, we will bind data to ?
162.   (chkerr(xdrx-sqlite-cmpstmt db "insert into test2 values (?, ?, ?, ?);"))
163.  
164.   ; add our values
165.   (repeat 100
166.     (chkerr(xdrx-sqlite-stmtbind (setq i (1+ i)) 87 12.012 "helloworld"))
167.   )  
168.  
169.   ; commit the transaction
170.   (chkerr(xdrx-sqlite-dml db "commit transaction;"))
171.  
172.   ; we must call this to clear the compiled statement
173.   ; and finalize the transaction
174.   (chkerr(xdrx-sqlite-stmtfnl))
175.  
176.   ;

Database management software can go to https://www.navicat.com/en/products/navicat-for-sqlite

SQLite database management software DB Browser:
https://github.com/sqlitebrowser/sqlitebrowser/

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

=============

The above LISP code uses the XDRX-API, which can be downloaded from https://github.com/xdcad/XDrx-API and is updated at any time.

The XDRX API encapsulates AcDb, AcEd, AcGe, AcBr... C++ library, using C++ methods to develop LISP programs.Thousands of Lisp functions are available.
Modify message

[It's Alive!]

Code - Auto/Visual Lisp: [Select]

1. (chkerr (xdrx-sqlite-dml db "insert into user (name, phone) values('jenny', json('{\"cell\":\"+491765\", \"home\":\"704-8675309\"}'))"))
2.  

I wonder if she changed her number?