Delete Your Code #5: Use foreign keys

db, delete your code Comments Off on Delete Your Code #5: Use foreign keys

A foreign key, this classic concept of relational databases, is extremely helpful in many ways.

To start with, it’s a watch dog of your database integrity — for example, it would be impossible to add user images records, if the user does not exist. No additional code, more control.

Another benefit is that all depending data is deleted cascadedly when the parent row gets killed. You remove a user — and voila: all images, comments, ratings and staff fly away too.

And third, it helps to autogenerate admin tools; for instance, in Symfony framework, the generator of admin interface relies on this info.

To be able to use foreign keys in your MySQL database, do 2 following steps:

  • make sure the two tables you are connecting, are driven by InnoDB
  • ensure that the columns you connect (user_id in our example) are of exactly the same type, including NULL and sign allowance.

Delete Your Code #4: Ambiguous terms

delete your code, development, ideas Comments Off on Delete Your Code #4: Ambiguous terms

Every application operates in a knowledge domain.

File browser works with files, directories and drives. Library software deals with books and editions. Geo tool must be aware of coordinates.

Idea is, developers can save time and lines of code if they agree the terms of the knowledge domain and be strict with it.

Of course, all of us use some terms — this rule is to use a well defined set of terms and to avoid synonyms.

Examples of these rule violations:

  • “folder” vs. “directory”;
  • “date” or “timestamp”;
  • “partner” a.k.a. “affiliate”;
  • “DNS Provider” as “product supplier”;
  • short for “longitude” is “lon” or “lng”?
  • does “website address” differ from “URL”?
  • “region”, “area” and “subarea” — in contrast to ADM1, ADM2 and ADM3;
  • the application I deal with at my job has: “brand”, “model”, “market” and “platform”, which are also often called “manufacturer”, “vehicle”, “locale” and “client” respectively.

If you apply this rule, you will not waste time creating the multiple mapper classes. More reusage, more OOP inheritance, more rapport. More motivation to work on a solid, well defined project with people, you talk the same language with. Isn’t this nice?

Detect if city is within area

db, development, geo 1 Comment »

We at SunnyRentals are working a lot with geo data.

The last task I was solving was inclusion of all cities in a touristic regions bounds automatically.

Yes, there are MySQL spatial data types, so region border and cities coordinates can be stored in native data types, but the processing functions are not implemented right: they only operate on MBRs (minimum bounding rectangles) to make things simpler…

So, let’s create our own MySQL function for that.

There are different approaches to distinguish if a point is within a given polygon. I prefered the way to iterate the edges of the polygon and check where the point relatively to the edge is – at right or at left; if the point is always at the same side, it lies within the border.

So we came to a fact that we need 2 functions:

  1. Distinguish the side at which the point lies from the line
  2. Check that for every edge in the polygon.

Here is the first function:

DELIMITER //

DROP FUNCTION `GetPointPositionOfLine`//
CREATE FUNCTION `GetPointPositionOfLine`( a POINT, b POINT, p POINT) RETURNS tinyint NO SQL
  COMMENT 'Distinguish the side at which the point lies from the line'
BEGIN
  DECLARE s DOUBLE;
  SET s = (X(b) - X(a))*(Y(p) - Y(a)) - (Y(b) - Y(a))*(X(p) - X(a));
  IF s > 0 THEN
    RETURN 1;
  END IF;
  IF s < 0 THEN
    RETURN -1;
  END IF;
  RETURN 0;
END //


All three params are of type POINT. Points a and b are the beginning and end of the line. Point p is the point we check.

Here is the main function:

DELIMITER //

DROP FUNCTION `IsPointWithin`//
CREATE FUNCTION `IsPointWithin`( ls LINESTRING, p POINT) RETURNS tinyint(1) NO SQL
BEGIN
  DECLARE i INT;
  DECLARE a, b POINT;
  DECLARE LineStringNumPoints INT;
  DECLARE Position, PrevPosition TINYINT;

  SET i = 1;
  SET PrevPosition = 0;
  SET LineStringNumPoints = NumPoints(ls);

  WHILE i <= LineStringNumPoints DO
    SET a = PointN( ls, i);
    IF i = LineStringNumPoints THEN
      SET b = StartPoint( ls);
    ELSE
      SET b = PointN( ls, i+1);
    END IF;

    SET Position = GetPointPositionOfLine( a, b, p);
    IF Position <> 0 THEN
      IF Position <> PrevPosition AND PrevPosition <> 0 THEN
        RETURN FALSE;
      END IF;
      SET PrevPosition = Position;
    END IF;
    SET i = i + 1;
  END WHILE;
  RETURN TRUE;
END //


It checks if a point p lies within a polygon of type LINESTRING.

Usage example. Given you have coordinates of cities in the separate float columns (to keep them readable) lat and lng of city table, and the polygon is given as a string too — but of course you can use a column of type LINESTRING:

SELECT * 
FROM `city` c
WHERE IsPointWithin(GeomFromText('LINESTRING(4 8, 15 16, 23 42)'), POINT(c.`lat`, c.`lng`));

The solution works pretty fast, especially if you first limit the cities by the rectangle that the polygon lies in.

Delete Your Code #3: shorter script and style tags

delete your code, development 1 Comment »

At my previous job a team leader was a firm believer that <script> tag must be always written in a full form like:

<script type="text/javascript">

When you have lots of javascript pieces, it becomes exhausting.

Good news, there is a way to tell to browser, that default script language is JavaScript.

To do that, inlcude a special META-tag in the HEAD of your HTML document. So if you have an inline JavaScript code for a onclick attribute of a tag, browser has no doubts it’s JavaScript.

Anyway, I include these lines in my projects HTML:

<meta http-equiv="Content-Script-Type" content="text/javascript" />

After that you can have just this:

<script>

By the way, since it’s http-equiv stuff, you can set it at server side.

P.S. There is the same trick for CSS, but it’s seems there are no alternatives to write styles, so it is useless.

Delete Your Code #2: less OOP visibility keywords

delete your code, development, ideas, php Comments Off on Delete Your Code #2: less OOP visibility keywords

Hey! I gonna describe tricks that I use to have less code. Why it is important to have less code, you ask? Less code means less bugs, less support, less developer brains waste.

Today’s trick is extremely simple — when you have a long set of public, protected or private class properties, remove the visibility keyword set to each declaration but define it once as comma separated declaration.

Same applies to class constants as well.

Example:

// Before:
class Cat {
    const KINGDOM = 'Animalia';
    const PHYLUM  = 'Chordata';
    const FAMILY  = 'Felidae';

    public $tail;
    public $whisker = '\/';
    public $head;
    public $legs = array(1,2,3,4);
}

// After:
class Cat {
    const
        KINGDOM = 'Animalia',
        PHYLUM  = 'Chordata',
        FAMILY  = 'Felidae';

    public
        $tail,
        $whisker  = '\/',
        $head,
        $legs = array(1,2,3,4);
}

One benefit is that the code looks clear and it’s much easier to scan rather then to read.

Second benefit is when you need to change the visibility for a property, you don’t have to edit the visibility keyword near the property name (which might be an error prone process when you are tired) — you just move the line up or down, which usually has a shortcut in IDE.

Known disadvantage is that most documenting engines don’t support this ferature.

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in