Verse SQL Generator

Overview

Verse is to help avoid writing literal SQL inline. The best way to begin a Verse is to ask the database for a fresh one. This will return back a new Verse object pre-configured to that connection.

$SQL = (
	Nether\Database::Get()
	->NewVerse()
	->Select('Users')
	->Where('ID=:ID')
	->Limit(1)
);

$Result = $SQL->Query([ ':ID' => 42 ]);
$Row = $Result->Next();

It is also able to use the Table Class attributes to prepare itself.

$SQL = (
	Nether\Database::Get()
	->NewMetaInsert('User')
	->Fields([ 'Name' => ':Name' ])
);

$Result = $SQL->Query([ ':Name' => 'Bob' ]);
$User = User::GetByID($Result->LastInsertID());

For that simple user table it is not that big of a deal but where it really helps is when you start using things like InsertUpdate or InsertReuseUnique attributes, as then Verse is able to generate better SQL to make that behaviour work automatically. The following example assumes the table UserBookmarks has a UNIQUE index across both UserID and URL which would normally cause the 2nd INSERT to fail. After adding the InsertReuseUnique attribute to the UserBookmark class, both attempts to INSERT will return the same row.

$SQL = (
	Nether\Database::Get()
	->NewVerseInsert('UserBookmark')
	->Fields([
		'UserID' => ':UserID',
		'URL'    => ':URL'
	])
);

// this insert will succeed.

$Result = $SQL->Query([ ':UserID' => 42, ':URL' => 'https://pegasusgate.net' ]);
$Bookmark1 = $Result->GetInsertID();

// this insert would have failed, but instead will return the id of the previous insert.

$Result = $SQL->Query([ ':UserID' => 42, ':URL' => 'https://pegasusgate.net' ]);
$Bookmark2 = $Result->GetInsertID();

Without Verse automatically reading the Table Class Annotations, you would have needed to manually do some foolery to append something like ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID) to your query prior to executing it.

API Reference

todo