Database Layer - HL Vanilla Community
<main> <article class="userContent"> <div class="embedExternal embedImage display-large float-none"> <div class="embedExternal-content"> <a class="embedImage-link" href="https://us.v-cdn.net/6030677/uploads/GS97ZL8ZDVCE/microsoftteams-image-288-29.png" rel="nofollow noreferrer noopener ugc" target="_blank"> <img class="embedImage-img" src="https://us.v-cdn.net/6030677/uploads/GS97ZL8ZDVCE/microsoftteams-image-288-29.png" alt="MicrosoftTeams-image (8).png" height="108" width="1356" loading="lazy" data-display-size="large" data-float="none"></img></a> </div> </div> <p>Vanilla only supports MySQL. It has a generic SQL driver implementation built on top of PDO to potentially allow for other databases (which you can see in <code class="code codeInline" spellcheck="false" tabindex="0">/library/databases</code>). However, at this time, the Vanilla team has no plans to support additional databases.</p><p>The best way to access the database is via existing <a href="https://docs.vanillaforums.com/developer/framework/models" rel="nofollow noreferrer ugc">models</a>. For instance, to get a list of discussions, use the <code class="code codeInline" spellcheck="false" tabindex="0">get</code> method in the <code class="code codeInline" spellcheck="false" tabindex="0">DiscussionModel</code>. You can rely on model-based access to already be optimized for performance and utilize caching if it’s available.</p><h2 data-id="building-queries">Building queries</h2><p>The <code class="code codeInline" spellcheck="false" tabindex="0">SQL</code> object supports chaining. You can call it with <code class="code codeInline" spellcheck="false" tabindex="0">Gdn::sql()</code>.</p><p>Here’s a simple example that gets a single discussion by its ID. We write its pieces in the order of a SQL statement, but they can be called in any order up to the <code class="code codeInline" spellcheck="false" tabindex="0">get()</code>. The <code class="code codeInline" spellcheck="false" tabindex="0">get()</code> is the call that fires the built query.</p><pre class="code codeBlock" spellcheck="false" tabindex="0">Gdn::sql() ->select('*') ->from('Discussion') ->where('DiscussionID', $discussionID) ->get(); </pre><p>Note that this is an impractical query to use in your addon, because this functionality already exists in a model:</p><pre class="code codeBlock" spellcheck="false" tabindex="0">$discussionModel = new DiscussionModel(); $discussionModel->getID($discussionID); </pre><p>Always use pre-existing calls in models when they are available for better performance and forward-compatibility.</p><p>Here’s an example of a complex select that pulls out all the stops:</p><pre class="code codeBlock" spellcheck="false" tabindex="0">Gdn::sql() ->select('cm.*') ->select('iu.Name', '', 'InsertName') ->from('ConversationMessage cm') ->join('Conversation c', 'cm.ConversationID = c.ConversationID') ->join('UserConversation uc', 'c.ConversationID = uc.ConversationID and uc.UserID = '.$viewingUserID, 'left') ->join('User iu', 'cm.InsertUserID = iu.UserID', 'left') ->beginWhereGroup() ->where('uc.DateCleared is null') ->orWhere('uc.DateCleared <', 'cm.DateInserted', true, false) ->endWhereGroup() ->where('cm.ConversationID', $conversationID) ->orderBy('cm.DateInserted', 'asc') ->limit($limit, $offset) ->get(); </pre><p>Notice the use of limit, offset, where groups, where conditions including less than & null, aliasing, and multiple joins.</p><h2 data-id="updates-and-inserts">Updates and inserts</h2><p>An insert is a single step that takes the table name and an array of values to insert as parameters:</p><pre class="code codeBlock" spellcheck="false" tabindex="0">Gdn::sql()->insert('UserConversation', array( 'ConversationID' => $conversationID, 'UserID' => $targetUserID )); </pre><p>An update requires setting the table in <code class="code codeInline" spellcheck="false" tabindex="0">update</code>, ends with a <code class="code codeInline" spellcheck="false" tabindex="0">put</code> (much like the select’s ending <code class="code codeInline" spellcheck="false" tabindex="0">get</code>):</p><pre class="code codeBlock" spellcheck="false" tabindex="0">Gdn::sql()->update('Conversation') ->set('LastMessageID', $messageID) ->where('ConversationID', $conversationID) ->put(); </pre><h3 data-id="direct-queries">Direct queries</h3><p>The <code class="code codeInline" spellcheck="false" tabindex="0">query()</code> method allows for sending unfiltered SQL queries to the database. This is strongly discouraged because it can easily cause security flaws, performance problems, and compatibility problems.</p><pre class="code codeBlock" spellcheck="false" tabindex="0">Gdn::sql()->query("select * from GDN_Comments"); </pre><h2 data-id="structure">Structure</h2><p>Vanilla allows you to define database structures in code. Use the <code class="code codeInline" spellcheck="false" tabindex="0">Gdn::structure()</code> method to use this object. Here we’ll look at part of the definition of the User table as an example:</p><pre class="code codeBlock" spellcheck="false" tabindex="0">Gdn::structure() ->primaryKey('UserID') ->column('Name', 'varchar(50)', false, 'key') ->column('Password', 'varbinary(100)') ->column('ShowEmail', 'tinyint(1)', '0') ->column('Gender', array('u', 'm', 'f'), 'u') ->column('Preferences', 'text', true) ->column('DateOfBirth', 'datetime', true) ->column('Score', 'float', NULL) ->set(); </pre><p><code class="code codeInline" spellcheck="false" tabindex="0">column()</code> takes 4 parameters: name, type, nulldefault (<code class="code codeInline" spellcheck="false" tabindex="0">true</code> to allow nulls, <code class="code codeInline" spellcheck="false" tabindex="0">false</code> to not - any other value becomes the default with disallowed nulls), and keytype (‘primary’, ‘key’, ‘index’, ‘unique’, or ‘fulltext’ - defaults to false).</p><p><code class="code codeInline" spellcheck="false" tabindex="0">primaryKey()</code> creates an auto-incrementing column. The Gender column uses an array to create an <code class="code codeInline" spellcheck="false" tabindex="0">enum</code> type; the rest are self-explanatory.</p><p>The <code class="code codeInline" spellcheck="false" tabindex="0">set()</code> method takes 2 parameters which should nearly <em>always</em> be false, which is their default. The first is <code class="code codeInline" spellcheck="false" tabindex="0">$explicit</code> which is whether to force the structure of the table to match <em>exactly</em> the definition above. The second is <code class="code codeInline" spellcheck="false" tabindex="0">$drop</code> which is whether to drop and recreate the table.</p> </article> </main>