Hex, Bugs and More Physics | Emre S. Tasci

(-Right now, I’m going to define a new category: the MySQL category!-)

Suppose that, you have a database which contains a huge number of entries about the materials (like, for instance, the Pauling Database). Let it have 163 different properties we can query about. It is optimized for queries, so, the values are enumerated and the labels for these are kept in "pauling.dblxxx" tables, which may be something like this for the "Chemical System" property:

The values are kept in "pauling.valxxx" tables:

(Where the first one is the EntryCode, the PRIMARY key that relates all the tables and the second value is the enumeration for the value. For example, 1422 for the 13. property is actually Ho-Ir 🙂

and there is one more set of tables, the paulingv2.valxxx tables which are stored and keyed in val order, so:

• if we want to find the EntryCodes corresponding to a given property, we query the paulingv2.valxxx tables
• if we want to find the property that is corresponding to a given EntryCode we query the pauling.valxxx tables
• if we want to "translate" the property’s enumeration, we query the pauling.dblxxx tables.

Here is the thing: Let’s say that we want the Structure Types that have an Atomic Enviroment Type (AET) of a rhombic dodecahedron , with a/b ratio 1, alpha=beta=90^o. The property numbers for these are:

Structure Type : 32
AET : 86
a/b ratio : 44
alpha : 41
beta : 42

Since the last three properties are numeric, we don’t enumerate them and the enumeration corresponding to the rhombic dodecahedron for AET is 6. So, fasten your seat belts, we are about to lift off! :

SELECT id,pauling.dbl032.val FROM pauling.dbl032
  INNER JOIN
  (
   SELECT DISTINCT val FROM val032
   INNER JOIN
   (
   SELECT v AS EntryCode FROM
   (
    (
     SELECT val001.val AS v FROM val001
     INNER JOIN paulingv2.val086
     USING (EntryCode)
     WHERE paulingv2.val086.val=6
    ) AS A
    INNER JOIN
    (
     (
      SELECT val001.val AS v FROM val001
      INNER JOIN paulingv2.val044
      USING (EntryCode)
      WHERE paulingv2.val044.val=1
     ) AS B
     INNER JOIN
     (
      # 41=90 && 42= 90
      (
       SELECT val001.val AS v FROM val001
       INNER JOIN paulingv2.val041
       USING (EntryCode)
       WHERE paulingv2.val041.val=90
      ) AS C
      INNER JOIN
      (
       SELECT val001.val AS v FROM val001
       INNER JOIN paulingv2.val042
       USING (EntryCode)
       WHERE paulingv2.val042.val=90
      ) AS D
      USING (v)
     )
     USING (v)
    )
    USING (v)
   )
   ) AS G
   USING (EntryCode)
  )
  AS Q ON (id = Q.val) ORDER BY val;

Later addition: Assuming 86=6; we don’t really need the other constraints 44=1, 42=90, 41=90 – do we?… So it’s just the boring:

SELECT id FROM pauling.dbl032
  INNER JOIN
  (
   SELECT DISTINCT val FROM val032
   INNER JOIN
   (
   SELECT v AS EntryCode FROM
   (
    (
     SELECT val001.val AS v FROM val001
     INNER JOIN paulingv2.val086
     USING (EntryCode)
     WHERE paulingv2.val086.val=6
    ) AS A
   )
   ) AS G
   USING (EntryCode)
  )
  AS Q ON (id = Q.val) ORDER BY Q.val;

The result of this is something like this:

To be honest, it is actually something like this :

The strange symbols are the price we pay for using non-standard charsets! 😉

So, to tidy up, I import this  to a table with the following structure:

CREATE TABLE IF NOT EXISTS `bcc` (
  `id` smallint(5) unsigned NOT NULL default ‘0’,
  `val` varchar(254) NOT NULL default ”,
  `usagecount` smallint(5) unsigned NOT NULL default ‘0’,
  `val1` varchar(30) NOT NULL default ‘0’,
  `val2` varchar(6) default NULL,
  `val3` varchar(3) default NULL,
  `val1t` varchar(30) NOT NULL,
  `val2sp` smallint(5) unsigned NOT NULL,
  `SG` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `val` (`val`),
  KEY `val1` (`val1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You have already met the id and val columns. "usagecount" will be imported from the pauling.dbl013 table; val1, val2, val3 are the seperated structure type information (ie, for "CuTi,tp4,129", val1="CuTi", val2="tp4" and val3="129"); val1t is the "translated" version of val1 which is the readeable one (ie, "(Ag¡•¦§Zn¡•¥¥)©Zn" is translated as "(Ag0.56Zn0.44)8Zn"). The translation is done via the following simpe php function:

function translate_symbols($string,$f_tr2symb=true)
{
$symbol_array = array("•","¡","¢","£","¤","¥","¦","§","¨","©","ª");//subscript values
$transl_array = array(".","0","1","2","3","4","5","6","7","8","9");//subscript values

if(!$f_tr2symb)return str_replace($transl_array,$symbol_array,$string);
else return str_replace($symbol_array,$transl_array,$string);
}

"val2sp" is the sliced numeric value from the Pearson Symbol stored in the val2 column.

To tidy up:

$query = "SELECT id, val1, val2 FROM bcc";
$qresult = mysql_query($query);

while($result = mysql_fetch_array($qresult))
{
 $query2 = "UPDATE bcc SET val1t=\"".translate_symbols($result["val1"])."\", val2sp=SUBSTRING(val2,3,20) WHERE id = ".$result["id"]." LIMIT 1";
 //echo $query2."\n";
 $q2result = mysql_query($query2);
 echo mysql_error();
}

You can refer to my previous entry for slicing up the "val" column. I had already done this while constructing the pauling.dblxxx tables, so in fact the actual view of the pauling.dbl032 table is the following one

meaning, I can just import them using the id’s of my new table:

UPDATE bcc, pauling.dbl032 SET
bcc.val = pauling.dbl032.val,
bcc.usagecount = pauling.dbl032.usagecount,
bcc.val1 = pauling.dbl032.val1,
bcc.val2 = pauling.dbl032.val2,
bcc.val3 = pauling.dbl032.val3
WHERE bcc.id = pauling.dbl032.id

Now we have something like:

We still have some work to do. Let’s take the two structures Ni₂Al and Ni₂In. Say that we are looking for superstructures, which have the property that the "atoms occupy atomic positions according to the parent crystal structure". The AET for Ni₂Al is given as 14-b;14-b;14-b; whereas the AET for Ni₂In is given as 11-a;11-a;14-b; . We want every atom to have the parent crystal structure (14-b – the rhombic dodecahedron for bcc), so we will eliminate those ones that have other AET.

mysql_query("USE pauling");
$ids_q = mysql_query("SELECT id FROM s07pt.bcc");
while($ids = mysql_fetch_row($ids_q))
{
 $id= $ids[0];
 $query = "
 SELECT COUNT(DISTINCT val) FROM pauling.val032
 INNER JOIN
  (
         SELECT v AS EntryCode FROM
          (
                 SELECT pauling.val001.val AS v FROM pauling.val001
                 INNER JOIN paulingv2.val032
                 USING (EntryCode)
                 WHERE paulingv2.val032.val = ".$id."
          ) AS A
         INNER JOIN
          (
                 SELECT pauling.val001.val AS v FROM pauling.val001
                 INNER JOIN paulingv2.val086
                 USING (EntryCode)
                 WHERE paulingv2.val086.val != 6
          ) AS B
  USING (v)
 ) as C USING (EntryCode)";

 $query = mysql_query($query);
 $result = mysql_fetch_row($query);
 $result = mysql_result($query,0);
 $result = ($result+1)%2;
 $query = "UPDATE s07pt.bcc SET incl_theo = $result where id = $id LIMIT 1";
 $j++;
 echo $j.".\t".$query."\n";
 mysql_query($query);
}

"incl_theo" is the column which is equal to 1 if the structure in question contains no AET other than 14-b, 0 otherwise. This gives us smt. like:

Renderu – LaTeX parsing WordPress plugin

Adapting from Titus Barik’s “LaTeX Equations and Graphics in PHP” titled article from the Linux Journal magazine, I was able to write a plugin for WordPress that enables me to include LaTeX-type

 $\sum\limits_{j = 1}^m {p\left( X \right)H\left( {Y|X = x_i } \right)} $

in the form of a PNG file format as :

(Although there is problem with multi lined entries such as matrix)

Barik’s article explained the method for Linux, but it can easily be run on a Windows environment (as it is been running on my computer at this very moment). Just install a LaTeX distribution like MikTeX and an image manipulation package like ImageMagick and it’s ready!