ALTER TABLE [dbo].[Message] WITH CHECK ADD CONSTRAINT [CK_Message] CHECK (([InReplyToId] IS NULL OR [dbo].[CheckInReplyToMessageId]([InReplyToId])>=(0))) GO ALTER TABLE [dbo].[Message] CHECK CONSTRAINT [CK_Message] CREATE FUNCTION [dbo].[CheckInReplyToMessageId] ( @MessageId BigInt ) RETURNS bit AS BEGIN DECLARE @RetVal bit SET @RetVal = (SELECT MessageId FROM Message WHERE MessageId = @MessageId) RETURN isnull(@RetVal, 0) END
Tag Archive for database
TSQL Function and Check Constraint to Ensure that a Parent Value is Present in the Table
CakePHP Model or Controller without any database Table
//### Creating a model for a table that doesn't actually exist in the database //### From cake-php.blogspot.com at : //### <a href="http://cake-php.blogspot.com/2006/09/21-things-you-must-know-about-cakephp.html" >http://cake-php.blogspot.com/2006/09/21-things-you-must-know-about-cakephp.html</a> //Add this in the Model //or in the Controller if you didn't get anymodel for your controller var $useTable = false;
Convert Database/Variable Name to Human Title
<?
class inflector extends inflector_Core {
public static function titlize($rawTitle) {
if(strlen($rawTitle) < 3) {
return strtoupper($rawTitle);
} else {
return ucwords(self::humanize($rawTitle));
}
}
}
echo inflector::titlize("news_items"); // News Items
echo inflector::titlize("id"); // ID
?>
Postal code
public static class PostalLookup
{
public static Postal Find(string countryCode, string postalCode)
{
// remove letters from postal code
postalCode = Regex.Replace(postalCode, "[^0-9]", "");
if (string.IsNullOrEmpty(postalCode) || postalCode.Length != 4)
return null;
if (string.IsNullOrEmpty(countryCode))
return null;
// create the web request to the GeoNames interface
const string geoNamesUrl = "http://ws.geonames.org/postalCodeLookupJSON?postalcode={0}&country={1}";
WebRequest geoNamesRequest = WebRequest.Create(String.Format(geoNamesUrl, postalCode, countryCode));
// make the call
WebResponse geoNamesResponse = geoNamesRequest.GetResponse();
// grab the response stream
var geoNamesReader = new StreamReader(geoNamesResponse.GetResponseStream());
// put the whole response in a string
string geoNamesContent = geoNamesReader.ReadToEnd();
try
{
JObject o = JObject.Parse(geoNamesContent);
var postal = new Postal
{
Code = (string)o["postalcodes"][0]["postalcode"],
PlaceName = (string)o["postalcodes"][0]["placeName"],
County = (string)o["postalcodes"][0]["adminName1"]
};
return postal;
}
catch
{
return null;
}
}
}
public class Postal
{
public string Code { get; set; }
public string PlaceName { get; set; }
public string County { get; set; }
}
PHP dbquery
// function dbquery()
// Call a database query
//
// The purpose of this function is to wrap a dbquery into function
//
// You do this for two reasons,
//
// 1) Make it so that if you db connection, object, etc... changes you won't be screwed.
// 2) Make Error reporting more attractive and maybe useful.
//
// $q -> the query you want to run
//
function dbquery( $q )
{
// Lets get the Databas ADO db lite object
// you probably need to change this code and do something different
//
// Actually, that whole freaking point of having this function
// wrap the database query so that if it changes you won't be screwed.
// Regardless!
// we need the connection
global $gCms;
$dbc =& $gCms->GetDb();
// Execute!
$qr = $dbc->Execute( $q );
// if all goes well then return the record set other wise...
if( $qr )
{
return $qr;
}
// something went wrong
else
{
// lets build a small backtrace and send it out so we know where this ocured
$debug = debug_backtrace();
$mesg = "";
//better error mesg
$mesg .= "<div style="color: #000000; background-color: #FFFFFF; position: absolute; top: 0px; left: 0px; width: 640px; height: 480px; overflow: auto;"><pre>
";
$mesg .= "<b>DataBase Error</b>
";
$mesg .= "There has been an error in the database statement.
";
$mesg .= "<b>SQL</b>
";
$mesg .= "<i>".$q."</i>
";
$mesg .= "<b>File/Line Stack</b>
";
foreach( $debug as $k => $v )
{
$mesg .= "FILE: ".$v['file']."
";
$mesg .= "LINE: ".$v['line']."
";
}
$mesg .= "-------------------------------------------------------------
";
// what is mysql saying?
$mesg .= mysql_error() ."
";
$mesg .= "-------------------------------------------------------------
";
$mesg .= "This error has been sent on the development team.
";
$mesg .= "There is no reason to call or email.
";
$mesg .= "</pre></div>
";
// we could also send the message to your email address here.
// or you could do what I do and just wait for them to email you anyway
// cause you know god forbid if users actually read what is on the screen
// stupid users: //{}
// die if there was a db error
// if there is an error in database calling I really don't want my
// application going further...
die( $mesg );
}
}
PHP InsertFromVals
<?php
// Function: Insert From Vals
// Take an associative array and build an insert statement
//
// $table -> the table you want to fill
// $prefix -> the prefix of the fields ( ie, auto_color -> 'auto_' )
// $vals -> the array to insert, default _POST
//
// Please note that this will work with normal database naming and not with
// special names with spaces and accents and odd stuff
//
//
function insertFromVals( $table , $prefix , $vals = null )
{
$fields = array();
$vallues = array();
if( is_null( $vals ) )
{
$vals = $_POST;
}
foreach( $vals as $k => $v )
{
if( ereg( "^".$prefix , $k ) )
{
$fields[] = mysql_escape_string( $k );
$values[] = mysql_escape_string( $v );
}
}
$fields = join( "," , $fields );
$values = "'" . join( "', '" , $values ) ."'";
$q = "INSERT INTO ".$table." (".$fields.") VALUES (".$values.")";
return $q;
}
/// testing code here:
/// probably not what you want to copy
/// illustrative purposes only
$test['user_name'] = "Doe";
$test['user_fname'] = "John";
$test['user_birthday'] = "1977-12-16 00:00:00";
$test['user_favorite_color'] = "orange";
$test['user_attempted_injection'] = "a string with a "'" can be dangerous in a db statement";
$q = insertFromVals( "users" , "user_" , $test );
echo $q;
?>
Simple DB Connection Class
////////////////////////////////////
//Simple DB Connection class.
////////////////////////////////////
class dbconnect {
//Variables.
private $host;
private $user;
private $password;
private $database;
private $lnk;
public $result;
////////////////////////////
//Constructor.
////////////////////////////
public function __construct($a, $b, $c, $d) {
$this -> host = $a;
$this -> user = $b;
$this -> password = $c;
$this -> database = $d;
}
////////////////////////////
//Public class functions.
////////////////////////////
//Query
public function func_query($qstring) {
$this -> func_connect();
mysql_query("SET NAMES 'utf8'");
$this -> result = mysql_query($qstring);
return $this -> result;
mysql_close($this -> lnk);
mysql_free_result($this -> result);
}
////////////////////////////
//Private class functions.
////////////////////////////
//Connect.
private function func_connect() {
$this -> lnk = mysql_connect($this -> host, $this -> user, $this -> password, true) OR die('Could not connect to the database - Why: '.mysql_error());
mysql_select_db($this -> database) OR die('Could not find database: '.mysql_error());
}
////////////////////////////
//Destructor.
////////////////////////////
public function __destruct() {
unset ($this -> user, $this -> password);
}
}
////////////////////////////////////
Generic superclass for dealing with io based tables
"""
Name : MyIO.py
Author : Jason Spadaro
Description : superclass for io-based classes and objects
Copyright 2009
"""
class myIO:
"""
********************************************
*Description : returns items from an external storage source, and
* stores them again later.
*******************************************
"""
def __init__(self, connectDict):
"""
********************************************
*Description : Initializes the connection information, rows
* of data. Then it makes the connection based on the myIO
* subclass.
*******************************************
"""
self.data_rows = [] #Empty array of items...
self.item_names = connectDict["item_names"] #List of item attribute names
self.connectDict = connectDict #connection information (filename, db name, etc)
self.make_connect()
def make_connect(self):
"""
********************************************
*Description : connection method
*******************************************
"""
pass
def getItem(self, i):
"""
********************************************
*Description : get's one item's worth of data.
*******************************************
"""
temp_dict = {}
for attribute, name in self.datarows[i], item_names:
temp_dict.update({name:attribute})
return temp_dict
def writeItem(self):
"""
********************************************
*Description : Writes one item worth of data
*******************************************
"""
pass
def close(self):
"""
********************************************
*Description : Cleans up io when it closes.
*******************************************
"""
pass
def dump_current_dict(self, item_dict):
"""
********************************************
*Description : Generically writes all items to io
********************************************
"""
for item_name, item in item_dict.iteritems():
print item_name + " is being written."
self.write_item(item)
def get_item_dict(self):
"""
********************************************
*Description : Getter for the aggregator
********************************************
"""
tempItemDict = itemDict()
for i in range(self.data_rows):
tempItem = getItem(i)
tempItemDict.add(tempItem)
return tempItemDict
Database All – using DB Class
$db = new Database($config['server'],$config['user'],$config['pass'],$config['database'],$config['tablePrefix']); $db->connect(); $sql ="SELECT * FROM XYZ WHERE ABC"; $all_ = $db->fetch_all_array($sql); $db->close();
Category: Uncategorized |
Tags: database