Never been to DZone Snippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

« Newer Snippets
Older Snippets »
Showing 1-10 of 19 total  RSS 

Claves Foraneas / Foreign key

Integridad de datos mediantes claves Foraneas, script que genera las SQL necesarios para garantizar la integridad entre tablas. Poco a poco vamos relacionando parejas de padre e hijo y podemos realizar un arbol de claves foraneas, cuando si borramos un registro del padre, borra y actualiza a los hijos en cascada, si no me explico bien decirme en los comentarios.

<?php
require_once("cabezera/funciones.php");
conectar_mysql();
require_once("cabezera/scripts/constantes.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2" />
<title>Untitled Document</title>
</head>

<body>
<?php
$ejecutar = isset($_GET['ejecutar']) && $_GET['ejecutar']=='si';
$tabla_padre = "album";
$tabla_hijo = "fotos";
$campo_padre = "idAlbum";
$campo_hijo = "idAlbum";

$sinIntegridad = 0;

$sql="select * from ".$tabla_hijo;
$resultado = mysql_query($sql);
while($fila = mysql_fetch_array($resultado))
{
	$sql2 = "select * from ".$tabla_padre." where ".$campo_padre." = '".$fila[$campo_hijo]."'";
	//echo $sql2."<br/>";
	$resultado2 = mysql_query($sql2);
	if(mysql_num_rows($resultado2) <= 0)
	{
		$sql3 = "DELETE FROM `".$tabla_hijo."` WHERE `".$campo_hijo."` = '".$fila[$campo_hijo]."' LIMIT 1;";
		if($ejecutar)
		{
			if(mysql_query($sql3)) echo "OK query : ".$sql3."<br />";
			else echo "Error en query : ".$sql3."<br />";
		}
		else
		{
			echo $sql3."<br/>";
		}
		$sinIntegridad++;
	}
}

$sql1="ALTER TABLE `".$tabla_padre."` ADD INDEX ( `".$campo_padre."` ) ";
$sql2="ALTER TABLE `".$tabla_hijo."` ADD INDEX ( `".$campo_hijo."` ) ";
$sql3="ALTER TABLE ".$tabla_hijo." ADD FOREIGN KEY(".$campo_hijo.") REFERENCES ".$tabla_padre." (".$campo_padre.") ON DELETE CASCADE ON UPDATE CASCADE";
if($ejecutar)
{
	if(mysql_query($sql1)) echo "OK query : ".$sql1."<br />";
	else echo "Error en query : ".$sql1."<br />";
	
	if(mysql_query($sql2)) echo "OK query : ".$sql2."<br />";
	else echo "Error en query : ".$sql2."<br />";
	
	if(mysql_query($sql3)) echo "OK query : ".$sql3."<br />";
	else echo "Error en query : ".$sql3."<br />";
}
else
{
	echo $sql1."<br/>";
	echo $sql2."<br/>";
	echo $sql3."<br/>";
}

echo "<br/>".$sinIntegridad." filas sin integridad.<br/>";
?>
<form id="form1" name="form1" method="get" action="">
  <label>
  <input name="ejecutar" type="hidden" id="ejecutar" value="si" />
  <input name="Button" type="button" onclick="location.href=location.href" value="Recargar sin ejecutar nada" />
  <input type="submit" name="button" id="button" value="Ejecutar SQL!" />
  </label>
</form>
<?php
/*
ALTER TABLE fotos_nueva_version ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE relacion_usuario_localidad ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE


ALTER TABLE relacion_usuario_actividad ADD FOREIGN KEY(idUsuario) REFERENCES usuarios_portalcocinas (idUsuario) ON DELETE CASCADE ON UPDATE CASCADE


ALTER TABLE fotos_nueva_version DROP FOREIGN KEY idUsuario_FK;

SHOW CREATE TABLE fotos_nueva_version;
*/
?>
</body>
</html>
to mysql php key foreign Claves Foraneas by Ricardo on Jul 16, 2008

Push your public key to a server

If you've already created your ssh keys locally do this to push the public key to a server so you won't have to login to the server everytime you ssh or cap deploy. 

 cat .ssh/id_rsa.pub | ssh deploy@myserver.com "cat >> .ssh/authorized_keys2"
to ssh server key capistrano by humblefrog on Jul 07, 2008

Get a Jaiku personal_key given a username and password

Logs into Jaiku and then gets the personal_key for using the API.

def GetJaikuPersonalKey(user, password):
  """Finds the Jaiku API personal_key from a username and password.

  One day I'll learn to use urllib2 properly and cookie parsing and stuff.
  """

  # login and find a cookie
  login_url = "http://jaiku.com/login"
  request_body = urllib.urlencode({'log': user,
                                   'pwd': password,
                                   'rememberme': '1'})
  # Open a connection to the authentication server.
  auth_connection = httplib.HTTPConnection('jaiku.com')
  # Begin the POST request to the client login service.
  auth_connection.putrequest('POST', '/login')
  # Set the required headers for an Account Authentication request.
  auth_connection.putheader('Content-type',
                            'application/x-www-form-urlencoded')
  auth_connection.putheader('Content-Length', str(len(request_body)))
  auth_connection.endheaders()
  auth_connection.send(request_body)
  auth_response = auth_connection.getresponse()
  if auth_response.status == 303:
    cookie_str = auth_response.getheader("set-cookie")
    # TODO(ark) parse this properly!
    res = re.search("(jaikuuser_[^;]*).*(jaikupass_[^;]*)", cookie_str)
    if res:
      auth_cookie = "%s; %s" % (res.group(1), res.group(2))
      apikey_url = "http://api.jaiku.com/key"
      req = urllib2.Request(url=apikey_url)
      req.add_header('Cookie', auth_cookie)
      f = urllib2.urlopen(req)
      return f.read()
to python key password api jaiku by ark on May 25, 2008

Reference of keyCodes 

    switch (oEvent.keyCode) {
       case 38: //up arrow  
       case 40: //down arrow
       case 37: //left arrow
       case 39: //right arrow
       case 33: //page up  
       case 34: //page down  
       case 36: //home  
       case 35: //end                  
       case 13: //enter  
       case 9: //tab  
       case 27: //esc  
       case 16: //shift  
       case 17: //ctrl  
       case 18: //alt  
       case 20: //caps lock
       case 8: //backspace  
       case 46: //delete
           return true;
           break;

       default:

Note: When capturing combination keys there is dedicated boolean attributes for each of the special keys (CTRL, SHIFT, ALT).
Reference: Make Life Easy With Autocomplete Textboxes [JavaScript & AJAX Tutorials] [sitepoint.com]
to javascript dom key capture code event keyCode by jrobertson on Apr 02, 2008

Copy Public Key To Host In One Line

ssh username@host "echo `cat ~/.ssh/id_dsa.pub` >> ~/.ssh/authorized_keys"
to ssh copy key scp publickey by timmorgan on Mar 19, 2008

Random Characters with Ruby

Use for a password or salt or whatever...

(0..25).inject('') { |r, i| r << rand(93) + 33 }
to ruby random key password salt by timmorgan on Oct 17, 2007

Api key (or any kind of key) generator

Generates a random key for API neatness.

class KeyGenerator
  require "digest/sha1"
  def self.generate(length = 10)
    Digest::SHA1.hexdigest(Time.now.to_s + rand(12341234).to_s)[1..length]
  end
end
to ruby random key by leethal on Oct 15, 2007

Recursively dump imbricated Map of Maps

// description of your code here

    public void dumpMapOfMap(Map map) {
        Set s = map.entrySet();
        Iterator sit = s.iterator();
        boolean isFirst = true;

        while (sit.hasNext()) {
            Map.Entry elem = (Map.Entry)sit.next();
            String key = (String)elem.getKey();
            Object value = elem.getValue();

            if (value instanceof String) {
                // recursivity stop condition
                System.out.print(key);
                System.out.print(" : ");
                System.out.println(value);
            } else {
                if (!isFirst) {
                    System.out.println("");
                } else {
                    isFirst = false;
                }
                System.out.println(key);
                Map valueMap = (Map)elem.getValue();
                dumpMapOfMap(valueMap);
            }
        }
    }
to recursive iterate key map value by Archanciel on Sep 12, 2007

Using 64 bit primary keys with migrations

I've got an application which may need more IDs than the standard 32 bit ones typically provide. MySQL has a BIGINT variable type, but it is a little tricky to convince Rails and Migrations to use it. The instructions are for MySQL, but should be easily transferable to other DBs.

1. Monkeypatch ActiveRecord::ConnectionAdapters::MysqlAdapter#native_database_types by appending it to config/environment.rb and add two new types. I've named them int64 and int64_pk

class ActiveRecord::ConnectionAdapters::MysqlAdapter
  def native_database_types #:nodoc:
    {
      :primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
      :int64_pk    => "bigint DEFAULT NULL auto_increment PRIMARY KEY",
      :int64       => { :name => "bigint" },
      :string      => { :name => "varchar", :limit => 255 },
      :text        => { :name => "text" },
      :integer     => { :name => "int", :limit => 11 },
      :float       => { :name => "float" },
      :decimal     => { :name => "decimal" },
      :datetime    => { :name => "datetime" },
      :timestamp   => { :name => "datetime" },
      :time        => { :name => "time" },
      :date        => { :name => "date" },
      :binary      => { :name => "blob" },
      :boolean     => { :name => "tinyint", :limit => 1 },
    }
  end
end


2. In the table creation migration, create the table WITHOUT a primary key column, and then add the column manually, referencing int64_pk above:

create_table :slices, :id => false do |t|
       t.column :cheese_id, :integer
       t.column :plant, :string
       t.column :date, :datetime
     end
    add_column :events, :id, :int64_pk
end



3. In tables referencing this one, mark the foreign_id fields as int64:

create_table :fondues do |t|
      t.column :cheese_id, :int64
      t.column :party_id, :integer
      t.column :kirsch_id, :integer
end



4. It turns out that the c-based mysql bindings do NOT do let you set primary keys via the sequence because it coerces the
insert_id into 32 bits at mysql.c:532. There are two way to fix this:

a. Use the pure-ruby gem (ie, remove the c-gem)

b. Patch and rebuild the c-gem by changing line 352 (in version 2.7):

   return INT2NUM(mysql_insert_id(GetHandler(obj)));


Extending this to use LL2NUM solves the issue:

   return LL2NUM(mysql_insert_id(GetHandler(obj)));



5. One last thing, in environment.rb:

  # Use SQL instead of Active Record's schema dumper when creating the test database.
  # This is necessary if your schema can't be completely dumped by the schema dumper,
  # like if you have constraints or database-specific column types
  config.active_record.schema_format = :sql


We need to deal with sql, rather than ruby, schemas.

----------------------

Note that I've decided that ONLY the cheese table needs 64bit IDs -- other references are still integers. If you wanted ALL primary keys to be 64 bits, you could have the monkeypatch look like:

class ActiveRecord::ConnectionAdapters::MysqlAdapter
  def native_database_types #:nodoc:
    {
      :primary_key    => "bigint DEFAULT NULL auto_increment PRIMARY KEY",
.
.
.

to rails mysql key rake migrations primary by sanj on Aug 15, 2007 2 Comments

Simulating a foreign key constraint in ActiveRecord (Ruby on Rails)

Say for example we have products and a business rule that every product must have a category. Before ActiveRecord we would have done this with a NOT NULL foreign key constraint at the database level.

It wasn't entirely obvious (to me) how to achieve this in an ActiveRecord model - you need *both* of the following two lines:

  validates_presence_of :category, :message => " must be specified" 
  validates_associated :category


Hope this helps someone.
to ruby rails key activerecord model foreign constraint by eddiewould on Jan 31, 2007
« Newer Snippets
Older Snippets »
Showing 1-10 of 19 total  RSS