Posted on: 2012-11-02 17:24:39.000
Today's function turned out to be well suited for a macro. (create-record) takes a couple of arguments. The first is a string which holds the name of the table you are updating. The rest is kind of cool. You put in a bunch of variable names--any number is fine--and what you do is make the variable name the same as the FIELD name in your table. Then the macro takes the names and values of these variables and constructs a valid SQL statement (after filtering each value for SQL injection issues)
So, for example, to add a post in this blog, I just have to write:
(create-record "Posts" Id PosterId PostDate PostSubject PostContent)
and it generates a valid SQLite query to insert the values into the field names that are the variables Id, PosterId, etc.
The magic comes from newLISPs (sym) function, which converts a string into a valid symbol, and the (args) feature of newLISP macros, which allow a dynamic number of arguments to be passed to the macro.
Here's the code that converts the variable names into symbols:
(dolist (s (rest (args)))
(set 'temp-index-num (string 'idx)) ; we need to number the symbols to keep them in the correct order
(if (= (length temp-index-num) 1) (set 'temp-index-num (string "0" temp-index-num))) ; leading 0 keeps the max at 100.
(sym (string temp-index-num s) 'DB))
I have to prepend a numeric value to the symbols to ensure they get posted into the list (symbols 'DB) in the correct order-- right now the limit is 100 fields but I could easily bump this to 1000 if I need to, or even higher... I want to keep this limit in here for now as a kind of sanity check though.
So, the C part of CRUD is done.. just three more parts to go!