Commits

Greg Slepak committed b1216c4

added fn-query-with-db function

  • Participants
  • Parent commits abe9f78

Comments (0)

Files changed (3)

   * added documentation for MAX_POST_LENGTH in request.lsp
   * fix to Jeff's JSON.lsp plugin (switched to " instead of ' for strings)
   * DF:activate-plugin can now be used to load all lisp files in a directory
+  * added a dragonfly_utils.lsp file to the DF.DB folder, includes handy fn-query-with-db function.
 
 Version 0.6
 

File example-site/dragonfly-framework/plugins-inactive/db/database_utils.lsp

 ;; @version 1.0
 ;; @author Greg Slepak
 
+; this cannot be used because newlisp's 'caller' function is broken.
 ; define-smacro defined in utils.lsp (part of Dragonfly's core functions)
-(define-smacro (for-query-with-db db query)
-	(letn (db (eval db) sql (db:prepare-sql (eval query)) keys '() ctx (prefix (caller)))
-		;(println ctx " vs " (prefix (caller)))
-		(dotimes (i (sql:col-count))
-			;(push (sym (upper-case (sql:col-name i)) (prefix (caller))) keys -1)
-			;(push (sym (upper-case (sql:col-name i)) ctx) keys -1)
-			(push (sym (upper-case (sql:col-name i)) Dragonfly) keys -1)
-		)
+; (define-smacro (for-query-with-db db query )
+; 	(letn (db (eval db) sql (db:prepare-sql (eval query)) keys '() ctx (prefix (caller)))
+; 		;(println ctx " vs " (prefix (caller)))
+; 		(dotimes (i (sql:col-count))
+; 			;(push (sym (upper-case (sql:col-name i)) (prefix (caller))) keys -1)
+; 			;(push (sym (upper-case (sql:col-name i)) ctx) keys -1)
+; 			(push (sym (upper-case (sql:col-name i)) Dragonfly) keys -1)
+; 		)
+; 		(push-autorelease-pool) ; in case we have blobs
+; 		(while (list? (setf values (sql:next-row)))
+; 			(eval (expand (cons 'begin $args) (unify keys values)))
+; 		)
+; 		(pop-autorelease-pool)
+; 	)
+; )
+
+(define (fn-query-with-db db query func)
+	(let (sql (db:prepare-sql query) keys '())
+		(setf keys (map sql:col-name (sequence 0 (-- (sql:col-count)))))
 		(push-autorelease-pool) ; in case we have blobs
 		(while (list? (setf values (sql:next-row)))
-			(eval (expand (cons 'begin $args) (unify keys values)))
+			(func (transpose (list keys values)))
 		)
 		(pop-autorelease-pool)
 	)
 )
+(global 'fn-query-with-db)

File example-site/views/dragonfly_sqlite3.html

 			%>
 			<table>
 				<tr class="header"><td>ID</td><td>Name</td><td>Age</td></tr>
-				<% (for-query-with-db db "SELECT rowid,name,age FROM people" %>
+				<% (fn-query-with-db db "SELECT rowid,name,age FROM people" (fn (row) %>
 					<tr>
-						<td><%=ROWID%></td>
-						<td><%=NAME%></td>
-						<td><%=AGE%></td>
+						<td><%=(lookup "rowid" row)%></td>
+						<td><%=(lookup "name" row)%></td>
+						<td><%=(lookup "age" row)%></td>
 					</tr>
-				<% ) %>
+				<% )) %>
 			</table>
 		</p>
 		<p>
 			The table above was created like so:
 		</p>
-		<pre class="textmate-source" style="overflow:visible"><span class="text text_html text_html_basic"><span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">table</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span>
-<span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">tr</span> <span class="entity entity_other entity_other_attribute-name entity_other_attribute-name_html">class</span>=<span class="string string_quoted string_quoted_double string_quoted_double_html"><span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_begin punctuation_definition_string_begin_html">"</span>header<span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_end punctuation_definition_string_end_html">"</span></span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span>ID<span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;/</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span>Name<span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;/</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span>Age<span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;/</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;/</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">tr</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span>
-<span class="source source_newlisp source_newlisp_embedded source_newlisp_embedded_html">&lt;% <span class="entity entity_paren entity_paren_lisp">(</span><span class="entity entity_name entity_name_function entity_name_function_lisp">for-query-with-db</span> <span class="entity entity_symbol entity_symbol_lisp">db</span> <span class="string string_quoted string_quoted_double string_quoted_double_lisp"><span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_begin punctuation_definition_string_begin_lisp">"</span>SELECT rowid,name,age FROM people<span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_end punctuation_definition_string_end_lisp">"</span></span> %&gt;</span>
-    <span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">tr</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span>
-        <span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span><span class="source source_newlisp source_newlisp_embedded source_newlisp_embedded_html">&lt;%=<span class="entity entity_symbol entity_symbol_lisp">ROWID</span>%&gt;</span><span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;/</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span>
-        <span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span><span class="source source_newlisp source_newlisp_embedded source_newlisp_embedded_html">&lt;%=<span class="entity entity_symbol entity_symbol_lisp">NAME</span>%&gt;</span><span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;/</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span>
-        <span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span><span class="source source_newlisp source_newlisp_embedded source_newlisp_embedded_html">&lt;%=<span class="entity entity_symbol entity_symbol_lisp">AGE</span>%&gt;</span><span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;/</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">td</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span>
-    <span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;/</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">tr</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span>
-<span class="source source_newlisp source_newlisp_embedded source_newlisp_embedded_html">&lt;% <span class="entity entity_paren entity_paren_lisp">)</span> %&gt;</span>
-<span class="meta meta_tag meta_tag_inline meta_tag_inline_any meta_tag_inline_any_html"><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_begin punctuation_definition_tag_begin_html">&lt;/</span><span class="entity entity_name entity_name_tag entity_name_tag_inline entity_name_tag_inline_any entity_name_tag_inline_any_html">table</span><span class="punctuation punctuation_definition punctuation_definition_tag punctuation_definition_tag_end punctuation_definition_tag_end_html">&gt;</span></span></span></pre>
+		<pre class="textmate-source" style="overflow:visible; width: 500px"><span class="source source_newlisp">&lt;<span class="entity entity_symbol entity_symbol_lisp">table&gt;</span>
+&lt;<span class="entity entity_symbol entity_symbol_lisp">tr</span> <span class="entity entity_symbol entity_symbol_lisp">class=</span><span class="string string_quoted string_quoted_double string_quoted_double_lisp"><span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_begin punctuation_definition_string_begin_lisp">"</span>header<span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_end punctuation_definition_string_end_lisp">"</span></span>&gt;&lt;<span class="entity entity_symbol entity_symbol_lisp">td&gt;ID&lt;</span>/<span class="entity entity_symbol entity_symbol_lisp">td&gt;&lt;td&gt;Name&lt;</span>/<span class="entity entity_symbol entity_symbol_lisp">td&gt;&lt;td&gt;Age&lt;</span>/<span class="entity entity_symbol entity_symbol_lisp">td&gt;&lt;</span>/<span class="entity entity_symbol entity_symbol_lisp">tr&gt;</span>
+&lt;% <span class="entity entity_paren entity_paren_lisp">(</span><span class="entity entity_name entity_name_function entity_name_function_lisp">fn-query-with-db</span> <span class="entity entity_symbol entity_symbol_lisp">db</span> <span class="string string_quoted string_quoted_double string_quoted_double_lisp"><span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_begin punctuation_definition_string_begin_lisp">"</span>SELECT rowid,name,age FROM people<span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_end punctuation_definition_string_end_lisp">"</span></span> <span class="entity entity_paren entity_paren_lisp">(</span><span class="entity entity_name entity_name_function entity_name_function_lisp">fn</span> <span class="entity entity_paren entity_paren_lisp">(</span><span class="entity entity_name entity_name_function entity_name_function_lisp">row</span><span class="entity entity_paren entity_paren_lisp">)</span> %&gt;
+    &lt;<span class="entity entity_symbol entity_symbol_lisp">tr&gt;</span>
+        &lt;<span class="entity entity_symbol entity_symbol_lisp">td&gt;&lt;</span>%=<span class="entity entity_paren entity_paren_lisp">(</span><span class="entity entity_name entity_name_function entity_name_function_lisp">lookup</span> <span class="string string_quoted string_quoted_double string_quoted_double_lisp"><span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_begin punctuation_definition_string_begin_lisp">"</span>rowid<span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_end punctuation_definition_string_end_lisp">"</span></span> <span class="entity entity_symbol entity_symbol_lisp">row</span><span class="entity entity_paren entity_paren_lisp">)</span>%&gt;&lt;/<span class="entity entity_symbol entity_symbol_lisp">td&gt;</span>
+        &lt;<span class="entity entity_symbol entity_symbol_lisp">td&gt;&lt;</span>%=<span class="entity entity_paren entity_paren_lisp">(</span><span class="entity entity_name entity_name_function entity_name_function_lisp">lookup</span> <span class="string string_quoted string_quoted_double string_quoted_double_lisp"><span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_begin punctuation_definition_string_begin_lisp">"</span>name<span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_end punctuation_definition_string_end_lisp">"</span></span> <span class="entity entity_symbol entity_symbol_lisp">row</span><span class="entity entity_paren entity_paren_lisp">)</span>%&gt;&lt;/<span class="entity entity_symbol entity_symbol_lisp">td&gt;</span>
+        &lt;<span class="entity entity_symbol entity_symbol_lisp">td&gt;&lt;</span>%=<span class="entity entity_paren entity_paren_lisp">(</span><span class="entity entity_name entity_name_function entity_name_function_lisp">lookup</span> <span class="string string_quoted string_quoted_double string_quoted_double_lisp"><span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_begin punctuation_definition_string_begin_lisp">"</span>age<span class="punctuation punctuation_definition punctuation_definition_string punctuation_definition_string_end punctuation_definition_string_end_lisp">"</span></span> <span class="entity entity_symbol entity_symbol_lisp">row</span><span class="entity entity_paren entity_paren_lisp">)</span>%&gt;&lt;/<span class="entity entity_symbol entity_symbol_lisp">td&gt;</span>
+    &lt;/<span class="entity entity_symbol entity_symbol_lisp">tr&gt;</span>
+&lt;% <span class="entity entity_paren entity_paren_lisp">))</span> %&gt;
+&lt;/<span class="entity entity_symbol entity_symbol_lisp">table&gt;</span></span></pre>
 		
 		<div class="line-dotted"></div>