Author Topic: [XDRx-SQL]LISP Operation SQLite database  (Read 849 times)

0 Members and 1 Guest are viewing this topic.

xdcad

  • Bull Frog
  • Posts: 485
[XDRx-SQL]LISP Operation SQLite database
« on: November 18, 2023, 04:14:44 PM »
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
« Last Edit: November 18, 2023, 04:26:49 PM by xdcad »
The code I wrote uses XDRX-API,which can be downloaded from github.com and is updated at any time.
===================================
https://github.com/xdcad
https://sourceforge.net/projects/xdrx-api-zip/
http://bbs.xdcad.net

It's Alive!

  • Retired
  • Needs a day job
  • Posts: 8696
  • AKA Daniel
Re: [XDRx-SQL]LISP Operation SQLite database
« Reply #1 on: November 18, 2023, 04:57:22 PM »
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?  :laugh: