JSON Configuration File ~ Notes

AWS uses JSON extensively for configuration as do many other modern platforms.

JSON is pretty straight forward.

http://json.org

Here are some JSON examples including what the examples look like in JSON compared to XML:

http://json.org/example

If you want some Java objects to parse JSON:

http://json.org/java/

This nifty class can convert XML to JSON and vice versa:

https://github.com/douglascrockford/JSON-java/blob/master/XML.java

There are no comments in JSON. Your comments are the data but you could create some generic comment field/value pair that your application ignores.

Google has a JSON style guide:

http://google-styleguide.googlecode.com/svn/trunk/jsoncstyleguide.xml

JSON Simple is a JSON Java toolkit:

http://code.google.com/p/json-simple/

Nice example of parsing a JSON file with Java:

http://answers.oreilly.com/topic/257-how-to-parse-json-in-java/

Parse web request with line breaks:

        HttpResponse response; // some response object
        BufferedReader reader = new BufferedReader
            (new InputStreamReader(response.getEntity().getContent(), 
               "UTF-8"));
        StringBuilder builder = new StringBuilder();
        for (String line = null; (line = reader.readLine()) != null;) {
            builder.append(line).append("\n");
        }
        JSONTokener tokener = new JSONTokener(builder.toString());
        JSONArray finalResult = new JSONArray(tokener);

Without line breaks:

        HttpResponse response; // some response object
        BufferedReader reader = new BufferedReader
          (new InputStreamReader(response.getEntity().getContent(), 
            "UTF-8"));
        String json = reader.readLine();
        JSONTokener tokener = new JSONTokener(json);
        JSONArray finalResult = new JSONArray(tokener); 

Decoupled Design for AWS Applications

When designing an application that uses AWS (or any third party components) you'll want to consider how your code will work if that component is unavailable or you want to switch it out for something else.

Reasons to consider decoupling when designing your AWS application:

• You might want to develop some code on AWS because you have quick access to servers, unlike the traditional large corporate environment where you put in a request and wait for weeks or months. It might be that you are still required to use the in-house corporate servers for production but you want to get started on development in the AWS environment. Decoupling your design allows for minimal changes and code that can switch from one environment to the other.

• Provide flexibility for the possibility (heaven forbid!) that someday you move off AWS to some other platform. You'll want to minimize the changes you have to make to your application should that become necessary for one or more of the AWS components you are using.

• On the flip side you might want to build and internal application that has the flexibility to scale to AWS servers when necessary and run with AWS IAM roles when in the cloud. You want your application to be dynamic enough to run in either location.

• Finally there is the best practice of writing unit tests which might need to run with or without AWS resources being available. By designing to interfaces or decoupling your application from AWS using proxy classes that can run with or without AWS you can run unit tests without access to AWS.

 So how do you go about decoupling your design? There are various ways you can do this, some more complicated than others.

One way to decouple your design is to create a set of proxy services that get called and forward requests to AWS. This is more work and possibly more error prone due to network issues. It creates complexity that will make it harder to maintain the application and requires more resources, but can provide separation of duties in the case where you want some people writing your AWS code and other people writing the applications that use the AWS code but not accessing AWS components directly to maintain decoupled applications. This option is nice because no changes to your application code or recompilation is required should whatever is behind the proxy change.

Another option is to create a set of interfaces used by your code. You could have the AWS objects extend these interfaces so you can use polymorphism and some configuration file to determine which class to use at the time the program runs.  The down side of this and why I don't really recommend it is because I prefer not to get in and tweak vendor files unless I'm going to completely take ownership of that code and maintain it going forward. Amazon is going to keep changing their libraries and I want to keep using those libraries.

Another option would be to create wrapper or proxy classes that interact with AWS components. Put all your proxy classes in a separate project and create a separate library that has a dependency on the AWS components. This is nice because none of your application directly calls AWS code and should not include any AWS libraries to prevent any developers from inadvertently creating dependencies.

As for configuration you have a few different options to tell your application which classes to use:

• Define your classes in configuration files similar to the way Spring defines the actual class to use and gives it a name so you can swap out classes behind the scenes. Have a configuration file for AWS and running without AWS where you use mock objects or something else.
• Define a switch in your config file which says "use AWS - yes or no" basically. Then create a factory that loads the appropriate object based on this switch - this would allow you to change your factory to extend your application or to easily swap out classes used should you introduce new vendors or functionality.

If you're trying to write an application that scales to the cloud, and additionally support online/offline development, you might want to consider dynamically determining if your application is on an AWS EC2 instance or not. That way you can instantiate the correct AWSCredentialsProvider on the fly and take advantage of the additional security provided by using IAM roles for applications running on EC2 instances.

These are just a few ideas. There are many ways to solve this problem. Just something to keep in mind if you are design applications on AWS.

Connecting to MySQL with Java Driver

Set up  a user with permissions to connect:

Add a user to your database if you need to, or use root (not recommended) user name and password to connect.

Create a new user and grant privileges to access database. For my test I'm giving full access to the user but you'll obviously want to be more selective on live systems and limit what your user can do with more restrictive GRANT statement.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newuser';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost'
        WITH GRANT OPTION;

You can test out your user by connecting to your database on command line or using MySQL workbench to make sure that's working independent of any Java code.

Download MySQL Java driver

Go to the Oracle download page for the MySQL Java driver.

You will probably need to create a user name and password on Oracle site to access this.

http://dev.mysql.com/downloads/connector/j/

I changed the "select platform" to platform independent and chose zip file.

Expand the zip file.

In the root of the expanded folder find this jar (or something like it if there's a newer version by the time you read this): mysql-connector-java-5.1.27-bin.jar

If you want to you can move this jar to a different location where you store third party jars accessible to your Eclipse projects (a repository, a folder, etc) or just leave it where it is.

Set up Java project in Eclipse and connect to database using driver

Open eclipse and create a new Java project.

Right click on the project name and click properties.

Choose Java Build Path in the left menu of the dialog box.

Click on the Libraries tab.

Click on "Add external jars..." on right side of screen.

Navigate to and select the driver jar we located and/or moved above  mysql-connector-java-5.1.27-bin.jar

Click open. Click OK.

Add a new class to your java project that makes the connection to the database. Some people will probably not like my error handling. Whatever. It's a test and will save that argument for later.

This class below assumes you are connecting to "localhost" which is the machine you're typing this all on and running an instance of MySQL on that machine on the default port (3306).

The things you may need to change if connecting to a database elsewhere are the things in red below:

localhost = IP address or domain name of server on which database is located
3306 =  port your database is listening on
yourdatabasename = name of the database to which you want to connect
newuser, newuser = user name + password

If you have problems connecting to a remote database might be easier to test the code locally first. If connecting to a database on an EC2 or RDS you'll need to make sure your VPC allows connections from your IP address on the specified port. If a database on EC2 you'll also need to make sure the OS firewall allows this connection. If you don't want to allow connections from the Internet set up a bastion host and upload your Java code to that machine to test the connection.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectToMySql {
   
    public boolean connect() {
        Connection con = null;
      
        try {
          
            Class.forName("com.mysql.jdbc.Driver");
            String url ="jdbc:mysql://localhost:3306/yourdatabasename";
            con = DriverManager.getConnection(url, "newuser", "newuser");
            System.out.println("connection Established");
            return true;
          
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //close the connection in a finally block 
            //so we don't kill the database with open connections
            close(con);
        }
      

    }

    private void close(Connection con){
        try{

            if (con != null) con.close();
        }catch(Exception e){
            //do nothing ...failed to close connection but whatever just testing
        }
    }
}

Create a unit test to test the connection. Yes, I know unit tests typically don't have database connections and should use mock objects, but this was a handy, simple way to run my code and make sure I can connect to the database.

To get the JUnit jar added to your project the easiest way to create the test is to right click on the class above and select  "New" and then "JUnit Test Case". Click the "Next" button, then check the "connect" method in the list. Then click "Finish".

Now alter the class just created to look like this:

import static org.junit.Assert.*;

import org.junit.Test;

public class ConnectToMySqlTest {

    @Test
    public void testConnect() {
       
        boolean connected = false;
       
        ConnectToMySql c = new ConnectToMySql();
        connected = c.connect();
       
        assertTrue(connected);
    }

}

Right click on ConnectToMySqlTest and choose "Run As" and "JUnitTest".

If all is well then you should have a green bar that tells you the test ran successfully. If not look at the error message in the output window. If you can't connect make sure you have tested connecting with the user name and password you are trying to test with outside of the Java project to make sure there's not a general problem with the user name, password, firewall, database connection itself.

SQL Server to MySQL Syntax Differences

I recently converted a large set of procedures from SQL Server 2008 Syntax to MySQL.

For basic conversion of SQL Server stored procedures to MySQL see this post:

http://websitenotebook.blogspot.com/2013/11/converting-sql-server-strored.html

Here are some of the other syntax changes I needed to make to get  my SQL Server stored procedures working on MySQL:

• For getDate() use CurDate() or Now()
• Use Length() instead of len()
• If statements - replace first BEGIN with THEN and END with END IF; use ELSE and ELSEIF in between
• use ifnull instead of isnull; isnull returns take one parameter and returns 1 or 0 to indicate if value is null;
• All the columns in your HAVING clause must be in the SELECT column list
• Instead of TOP N use LIMIT N at the end of your select statement
• SQL Server money data type needs to be changed to decimal with appropriate precision (say decimal(19, 2) where 2 is 2 decimal places) 
• Can't cast to varchar. Use char: CAST(id as CHAR(10))
• MySQL wants your DECLARE statements always at the top of the stored procedure.  
• If trying to get record count use row_count() function: set RecordCount = row_count();
• A procedure with multiple select statements will return the first recordset first when calling a stored procedure through MySQL JDBC driver so make sure the first select statement is the recordset you want the returned or handle multiple recordsets. Use SET instead of SELECT when setting values.
• To exec SQL inside a stored procedure:
SET @s = 'INSERT into mytable select x from other table'
PREPARE stmt1 FROM @s; 
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;  
• Use CONCAT instead of + to concatenate values - and note that if you put a space between CONCAT and the parenthesis won't work:

  CONCAT('%', @email, '%')  

• CASE statement has CASE WHEN ... THEN ... WHEN...THEN...ELSE... END (don't forget THEN and doesn't end with END CASE even though IF end with END IF). Also if you have ELSE CASE WHEN in your code replace with just WHEN
• Instead of UPDATE x=y FROM (join tables) put the join after the update and before the set statement: UPDATE table1 t1 JOIN table2 t2 on t1.pk = t2.fk SET t1.t1_field = t2.t2_field WHERE t1.some_field=some_value
• If using two dashes for comment must have space or tab after the dashes
• Can't do a FULL OUTER JOIN in MySQL but can emulate with a UNION of two left join select statements 
• Temporary tables hang around for the life of a client connection. They aren't automatically dropped at the end of a stored procedure run so you'll have to manually delete them if you want to use the same table again for a particular connected session.
• Can't alter procedure SQL so have to drop and recreate which creates issues with high-availability systems as noted in this blog post (though there may be alternate solutions and in some cases can cause problems in your app anyway if changing signatures - so you'd have to find some alternate to this to coordinate with your app code): https://www.codeofhonor.com/blog/a-better-way-to-update-sql-stored-procedures
• SQL Server lets you get away with having all your joins before all your ON statements and figures out which JOIN goes with which ON. In MySQL you need to put the ON statement right after the JOIN statement to which it refers. 
• Use AUTOINCREMENT instead of IDENTITY creating tables: NewColumn INT IDENTITY(1,1) NOT NULL becomes NewColumn INT NOT NULL AUTO_INCREMENT
• Instead of select returnId = @@identity use SELECT (@@Identity AS id) or SELECT  id = (@@Identity);
• Stored procedures with no parameters need () after procedure name
• You may need to use varbinary(1000) or appropriately length instead of binary data type in stored procedure parameter list if you get truncation error
• Instead of PATINDEX use POSITION or LOCATE
• Can't use keywords as variable names. For example can't use: Declare sql varchar(1000) - you'll have to pick another variable name
• Temporary tables do not have # in front of the name; instead use syntax: create temporary table or drop temporary table;
• Derived tables do not have # in front of name; make sure to use AS when defining the alias: i.e. instead of select... #t select.... as t
• Execute instead of exec; If you are calling one stored procedure inside another use CALL instead of EXEC
• If you see 'expecting end$' in error message when creating a stored procedure make sure you have a begin and end around the lines of SQL within the body of the procedure. 
• Unexpected IDENT_QUOTED: Declare the delimiter for the procedure at the top of the procedure to be something other than semicolon (;). Set the delimiter back to semicolon at the end of the procedure. See DELIMITER in sample code above.
• Use backticks (`) instead of square brackets [] for db objects with spaces in 
  names

Distributed Databases - Seattle Scalability Meetup Notes

At Seattle Scalability Meetup: distributed database design

separate query from distributed computation

Build for high concurrency

Network io most precious resource

Prefer predictable degradation to complex recovery scenarios

Build every component to be distributed, do not reuse old RDBMS architecture

Netty - java network library

ProtoBuff, Thrift faster than RPC in Hbase

Shard - have to ask every server. Every query hits every machine.

Global indexes faster for distributed systems. Tells you which server has data you need based on primary index telling ranges on each server.