Source

dragonfly-newlisp / example-site / views / dragonfly_sqlite3.html

<%
(activate-plugin "db")
(display-partial "doctype")
%>
<head>
	<% (display-partial "header") %>
</head>

<body>

<div id="wrap">
	<div id="header">
		<% (display-partial "navigation") %>	
		<div class="clear"></div>
	</div>

	<div id="content">
				
		<div class="title nomargin">
			<p><% (title "| Dragonfly web framework") %><p>
		</div>
		
		<h1>Using DF.DB</h1>
		<p>
			<%
			(setf db (instantiate Sqlite3 ":memory:"))
			(db:execute-update "CREATE TABLE people (name TEXT, age INTEGER)")
			(dolist (person '(("Lizzy" 8) ("Sally" 10) ("Betty" 7)))
				(db:execute-update "INSERT INTO people VALUES (?,?)" person)
			)
			%>
			<table>
				<tr class="header"><td>ID</td><td>Name</td><td>Age</td></tr>
				<% (fn-query-with-db db "SELECT rowid,name,age FROM people" (fn (row) %>
					<tr>
						<td><%=(<- "rowid" row)%></td>
						<td><%=(<- "name" row)%></td>
						<td><%=(<- "age" row)%></td>
					</tr>
				<% )) %>
			</table>
			<% (setf obj (find-dbobj db "people" '("name" "age") 1)) %>
			<p>
				I've found a person called <%=(obj:name)%> in the table '<%=obj:table%>' at location <%=obj:finder%>, this person is <%=(obj:age)%> years old.
			</p>
			<p>
				Let's set their age to 10.<%(obj:age 10) (dbobj-save obj)%>
			</p>
			<p>
				They are now <%=(obj:age)%> years old.
			</p>
			<p>
				Further, I can create a new person. See:
				<% (release obj) (setf obj (create-dbobj db "people" '("Sue" 57))) %>
			</p>
			<p>
			<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" %>
					<tr>
						<td><%=ROWID%></td>
						<td><%=NAME%></td>
						<td><%=AGE%></td>
					</tr>
				<% ) %>
			</table>
			</p>
		</p>
		<p>
			The table above was created like so:
		</p>
		<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>


		<% (benchmark-result) %>
		
		<div class="line-solid"></div>
		
		
	</div><!-- END CONTENT -->
	
</div><!-- END WRAP -->
<% (display-partial "footer") %>