Potentially large database

I'm looking for the best way to store lots of data. We have a simple product that has thousands of different possible configurations, as there are 6 size variables to it each with a potentially no limits and tiny increments.

Historically we have drawn these from scratch every time using some old .Net routine and referenced each one differently each time.

Every time one of these are drawn I want to give it a reference and store all the data somewhere. Which I can then access quickly to search, filter and query using opendcl.

I was looking at storing this data in xrecords inside a dictionary then using odbx to search when opendcl initialises.

I will need to read and write data, what in you opinion is the best way to store this data so it can be accessed and searched with great speed?

Sent from my Pixel XL using Tapatalk

Autocad can talk to MS-Acess same as Excel advantage is that Access can have a huge amount of data. It also has the advantage of index's ie fast way to find things it takes 15 tries to find a data set in 10,000 items using an index. Its not efficient to say search a TXT file of info with 10,000 lines. Its been a long time since I have played with get & put records in a binary file. 10K -> 15 -> answer found.

If you Google or use help there is a lot of info about using external databases directly from Autocad. Pretty sure they allow key matching and possibly index searching. I Googled "autocad to access" and lots of code examples came up. The one hint is SQL this is Sequence Query Language which is behind most database systems.

I do use SQL to get and store data and the search is really fast but establishing the connection has a lag. I do use excel alot for data acquisition but the lag there is even greater.

Sent from my Pixel XL using Tapatalk

SQLite for AutoLisp (by nullptr AKA Daniel) may be of interest.

Will multiple users be accessing the database at the same time?

Connecting to a DB or an Excel file via ADODB can be faster and does not require MS Office. But why the focus on speed?

Reading a CSV file and turning it into a global (or black board) assoc list could perhaps work?

CSV file:

idx,name
1,"John"
2,"Peter"

Assoc list:

(
 ((idx . 1) (name . "John"))
 ((idx . 2) (name . "Peter"))
)

Obviously the key with lisp is using lists as output.

Heres my opinion:

If the data is drawing dependent: I'd use dictionares/xrecords/xdata to store it inside the dwg or to the required ACAD objects.

About size: I'd try to avoid dictionaries/xrecords/registry if the data amount is too much.

How the data is distributed? :

• Tree - then the list would become multi-nested and to understand its hierarchy (using a multi-nested list or manually) perhaps your data and subdata should be stored like folders and subfolders
  and the atoms might represent a .txt files.
  This is somewhat related to this idea.
   
  
• Matrix Table - then the list would be evenly nested, and the data would be easily maintained through excel (and with lisp you could access the data thru a matrix list).
  Just like Roy pointed out, but I'd avoid dotted pairs and use rather sublists (although they provide faster and simplier accessing, they might ruin your visualisation on how an item should be accessed, which would lead to mind-boggling errors).
   
  
• Combined - multi-nested list as a hierarchy (where the data is stored in folders and subfolders) but the atoms represent excel files that contain matrix-list information inside.
  

Well you could also save the whole matrix or multi-nested list in a .txt file (which could be maintaned easily) and read'n'evaluate it as a valid list with lisp.

About speed: I don't know what to answer, because I never had such task nor experience.

So just think'n'imagine what would be the best way (sticking up to the KISS principle) and then try to create the required subfunctions.

connection has a lag

What is the lag ? a second, 1 minute? Is it from the cloud etc

Can you supply some more detail.

@Grrr: You say you want to avoid dotted pairs. Does this mean you prefer this?:

(
 ((idx 1) (name "John"))
 ((idx 2) (name "Peter"))
)

Something like this, Roy:

CSV file:

idx,name,phone,address
1,"Alan",7676,"Washington DC"
2,"Brian",2424,"Boston"
3,"Chad",3232,"NY"

Matrix assoc list:

(setq L
 '(
   (idx name phone address)
   (1 "Alan" 7676 "Washington DC")
   (2 "Brian" 2424 "Boston")
   (3 "Chad" 3232 "NY")
 )
)

And some search function like this:

; _$ (FindItem 'name "John" L) -> nil
; _$ (FindItem 'name "Brian" L) -> (2 "Brian" 2424 "Boston")
; _$ (FindItem 'phone 3232 L) -> (3 "Chad" 3232 "NY")
(defun FindItem ( key val L / aL n )
 (setq aL (apply 'mapcar (cons 'list L)))
 (if (setq n (vl-position val (assoc key aL)))
   (mapcar (function (lambda (x) (nth n x))) aL)
 )
)

EDIT:

I agree that its slower than the classical (cdr (assoc ...)) that Roy suggested, but the list is far more readable, as you don't have to break each item into a dotted pair.

Also heres another function that would act like a search:

(setq L
 '(
   (idx name phone address)
   (1 "Alan" 7676 "Washington DC")
   (2 "Brian" 2424 "Boston")
   (3 "Chad" 3232 "NY")
   (4 "Derek" 0321 "Florida")
   (5 "Amber" 3021 "Boston")
   (6 "Cortney" 1210 "Florida")
   (7 "Andrew" 9292 "Texas")
 )
)

; _$ (FindItems 'name "C*" L) -> ((3 "Chad" 3232 "NY") (6 "Cortney" 1210 "Florida"))
; _$ (FindItems 'address "B*" L) -> ((2 "Brian" 2424 "Boston") (5 "Amber" 3021 "Boston"))
; _$ (FindItems 'phone "3*" L) -> ((3 "Chad" 3232 "NY") (4 "Derek" 321 "Florida") (5 "Amber" 3021 "Boston"))
; _$ (FindItems 'name "H*" L) -> nil
(defun FindItems ( key val L / ToStr i aL nL )
 (setq ToStr (lambda (x) (strcase (cond ((eq 'STR (type x)) x) ((vl-prin1-to-string x))))))
 (setq val (ToStr val))
 (setq aL (apply 'mapcar (cons 'list L)))
 (setq i 0)
 (if 
   (setq nL
     (apply 'append 
       (mapcar 
         (function 
           (lambda (x)
             (setq i (1+ i))
             (if (wcmatch (ToStr x) val)
               (list i)
             )
           )
         )
         (cdr (assoc key aL))
       )
     )
   )
   (mapcar (function (lambda (x) (nth x L))) nL)
 ) 
); defun FindItems

its a bit buggy with the 'phone key - maybe would be beter if all atoms are strings?

(setq LstA
 '(
   ((idx . 1) (name . "Alan")  (phone . 7676) (address . "NY"))
   ((idx . 2) (name . "Brian") (phone . 2424) (address . "Boston"))
   ((idx . 3) (name . "Chad")  (phone . 3232) (address . "NY"))
 )
)

; (FindItemsA 'address (lambda (str) (= "NY" str)) lstA)
; (FindItemsA 'phone (lambda (num) (< num 6000)) lstA)
(defun FindItemsA (key valPred lst)
 (vl-remove
   nil
   (mapcar '(lambda (sub) (if (valPred (cdr (assoc key sub))) sub)) lst)
 )
)

(setq LstA

'(

((idx . 1) (name . "Alan") (phone . 7676) (address . "NY"))

((idx . 2) (name . "Brian") (phone . 2424) (address . "Boston"))

((idx . 3) (name . "Chad") (phone . 3232) (address . "NY"))

)

)

(defun foo (key pat lst / l n )
 (foreach x lst
   (if
     (wcmatch  (if (numberp (setq n (cdr (assoc key x)))) (itoa n) n) pat)
      (setq l (cons x l))
   )

 )
 l
)


;Roy's data format
(foo 'address "*on*" LstA)
;(((IDX . 2) (NAME . "Brian") (PHONE . 2424) (ADDRESS . "Boston"))) 
(foo 'name "C*" LstA)
;(((IDX . 3) (NAME . "Chad") (PHONE . 3232) (ADDRESS . "NY"))) 
(foo 'phone "*24*" LstA)
;(((IDX . 2) (NAME . "Brian") (PHONE . 2424) (ADDRESS . "Boston"))) 

(setq L

'(

(idx name phone address)

(1 "Alan" 7676 "Washington DC")

(2 "Brian" 2424 "Boston")

(3 "Chad" 3232 "NY")

(4 "Derek" 0321 "Florida")

(5 "Amber" 3021 "Boston")

(6 "Cortney" 1210 "Florida")

(7 "Andrew" 9292 "Texas")

)

)

;;;Grrr's data format
(defun bar (key pat lst / l n)
 (foreach x (cdr lst)
   (if	(wcmatch
  (if (numberp (setq n (nth (vl-position key (car lst)) x)))
    (itoa n)
    n
  )
  pat
)
     (setq l (cons x l))
   )
 )
 l
)

(bar 'name "*ew*" L)
;((7 "Andrew" 9292 "Texas")) 
(bar 'phone "*32*" L)
;((4 "Derek" 321 "Florida") (3 "Chad" 3232 "NY")) 
(bar 'address "*da*" L)
;((6 "Cortney" 1210 "Florida") (4 "Derek" 321 "Florida"))

my $0.02 how about XML?