Без рубрики – Page 3

Creating an email subscription web service (Part 2)

In the previous post of this series I decided what tables I’m going to have in my database. I created those tables (people_table, lists_table, subscriptions_table) using Workbench. Then I had to spend one evening trying to fix an issue with Russian language in MySQL. I just couldn’t save entries to the database, if those entries had Russian symbols in them. I was trying to find a solution that would fix this issue once and for all, like maybe changing some setting in MySQL or in Workbench. But I couldn’t.

So, for now the only way for me to use Russian symbols in my database is to set the collation of each table at the time of its creation to utf8-default. If the table already exists and it has wrong collation, I don’t know how to change it. If you know how to set text encoding globally in MySQL, please, tell me in the comments section below.

I have read something about JavaMail and about setting up a mailing server in general. It all seemed complicated, so I’m not sure if I’ll ever complete this project. I will definitely read some more on this topic, though. For now, I decided to do without the mailing part of an app. What I want to do now, is create a web form with two fields – name and email – and a server side which will get the information from a web form and save it to the database.

So, I created the form. This is what it looks like.

webform

And here is its source code.

<!DOCTYPE html>
<html>
<head>

<title>Enter your email</title>

</head>

<body>

<form action="http://localhost:8080/EmailSubscriptionService/AddEmailToList" method="post">
	<input name="name" type="text" value="Name"><br>
	<input name="email" value="Email"><br>
	<input type="hidden" name="list_id" value="1">
	<button>Send</button>
</form>



</body>

</html>

<!DOCTYPE html>

<html>

<head>

<title>Enter your email</title>

</head>

<body>

</form>

</body>

</html>

Notice the third input – list_id. It has type “hidden” so it isn’t visible on the page, but the value of the list_id parameter will be sent to the server. This is how the server will know which list the user is subscribing to. The value of this parameter is set manually for now.

I also created a Web Project in Eclipse called EmailSubscriptionService. To use JDBC in it I downloaded Connector/J from https://dev.mysql.com/downloads/connector/j/

I needed to put the mysql-connector-java-5.1.39-bin.jar file from the archive somewhere into my project. I didn’t know what the right place for it was, so I just put it into WebContent/WEB-INF/lib folder. I’m almost sure (after googling a little) that it is not the best place for this file. But it works, and that’s what is most important for me right now.

I created two files: AddEmailToList.java and MySQLConnection.java. Then I spent two evenings coding. Finally, I have all the main functionality. I can add people to the list from the web form.

This is my AddEmailToList.java file:

package emailSubscriptionPackage;


import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class AddEmailToList
 */
@WebServlet("/AddEmailToList")
public class AddEmailToList extends HttpServlet {
	private static final long serialVersionUID = 1L;

    /**
     * Default constructor. 
     */
    public AddEmailToList() {
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub


	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		System.out.println("inside doPost method");
		String name = request.getParameter("name"); 
		String email = request.getParameter("email");
		int list_id = Integer.parseInt(request.getParameter("list_id"));
		
		System.out.printf("trying to add person with name: %s email: %s to list with list_id: %s\n",name,email,list_id);
		
		
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		if(name.length() > 0 && email.length() > 0){
			
			MySQLConnection.connect();
		
			savePerson(name,email,list_id,out);
			
			MySQLConnection.closeConnection();
			

			
		}else{
			
			out.println("Name and/or email missing.");
		}
		
		
	}
	
	
	public static void savePerson(String name,String email,int list_id, PrintWriter out){
		
		if(!MySQLConnection.listExistsWithId(list_id)){
			out.println("The list you are trying to subscribe to doesn't exist");
			return;
		}
		
		int person_id = MySQLConnection.findPersonWithEmail(email);
		
		if(person_id > 0){
			//person exists in table
			
			System.out.println("person exists");
			
			addExistingPersonToList(person_id,list_id,out);

			
		}else{
			
			int new_person_id = MySQLConnection.insertPesronIntoPeopleTable(name, email);
			
			if(new_person_id > 0){
				addExistingPersonToList(new_person_id,list_id,out);
			}else{
				out.println("Something went wrong");
			}
			
		}
		
		
	}
	
	
	
	public static void addExistingPersonToList(int person_id,int list_id, PrintWriter out){
		
		boolean personIsAlreadySubscribedToList = MySQLConnection.personIsSubscribedToList(person_id, list_id);
		
		if(personIsAlreadySubscribedToList){
			out.println("You are already subscribed to this list");
		
		}else{
			
			if(MySQLConnection.subscribePersonToList(person_id, list_id)){
				out.println("You have successfully subscribed to this list");
			}else{
				out.println("An error occured while subscribing to the list");
			}
			
			
		}
	}

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

package emailSubscriptionPackage;

import java.io.IOException;

import java.io.PrintWriter;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

/**

* Servlet implementation class AddEmailToList

@WebServlet("/AddEmailToList")

public class AddEmailToList extends HttpServlet {

private static final long serialVersionUID = 1L;

/**

* Default constructor.

public AddEmailToList() {

// TODO Auto-generated constructor stub

}

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

// TODO Auto-generated method stub

}

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

// TODO Auto-generated method stub

System.out.println("inside doPost method");

String name = request.getParameter("name");

String email = request.getParameter("email");

int list_id = Integer.parseInt(request.getParameter("list_id"));

System.out.printf("trying to add person with name: %s email: %s to list with list_id: %s\n",name,email,list_id);

response.setContentType("text/html");

PrintWriter out = response.getWriter();

if(name.length() > 0 && email.length() > 0){

MySQLConnection.connect();

savePerson(name,email,list_id,out);

MySQLConnection.closeConnection();

}else{

out.println("Name and/or email missing.");

}

public static void savePerson(String name,String email,int list_id, PrintWriter out){

if(!MySQLConnection.listExistsWithId(list_id)){

out.println("The list you are trying to subscribe to doesn't exist");

return;

}

int person_id = MySQLConnection.findPersonWithEmail(email);

if(person_id > 0){

//person exists in table

System.out.println("person exists");

addExistingPersonToList(person_id,list_id,out);

}else{

int new_person_id = MySQLConnection.insertPesronIntoPeopleTable(name, email);

if(new_person_id > 0){

addExistingPersonToList(new_person_id,list_id,out);

}else{

out.println("Something went wrong");

}

public static void addExistingPersonToList(int person_id,int list_id, PrintWriter out){

boolean personIsAlreadySubscribedToList = MySQLConnection.personIsSubscribedToList(person_id, list_id);

if(personIsAlreadySubscribedToList){

out.println("You are already subscribed to this list");

}else{

if(MySQLConnection.subscribePersonToList(person_id, list_id)){

out.println("You have successfully subscribed to this list");

}else{

out.println("An error occured while subscribing to the list");

}

And this is myMySQLConnection.java file:

package emailSubscriptionPackage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.*;

import java.sql.*;

public class MySQLConnection {

	public static Connection con;
	
	public static void connect(){
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Driver loading success!");
			
			String url = "jdbc:mysql://localhost/email_subscription_bd?autoReconnect=false&useSSL=false";
			String name = "root";
            String password = "123";
            
            try{
            	con = DriverManager.getConnection(url,name,password);
            	System.out.println("Connected!");
            	
            }catch(SQLException e){
            	e.printStackTrace();
            	 
            }
            
            
            
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}
		
		
		
		
	}
	
	
	public static void closeConnection(){
		
		try{
			con.close();
			System.out.println("Disconnected");
		}catch(SQLException e){
			e.printStackTrace();
		}
		
		
	}
	
	
	public static boolean listExistsWithId(int list_id){
		
		try{
			Statement st = con.createStatement();
			String sqlCommand = String.format("select list_id from lists_table where list_id=%s",list_id);
			ResultSet rs = st.executeQuery(sqlCommand);
		
			if(rs.next()){
				
				return true;
		
			}
	
		}catch(SQLException e){
			e.printStackTrace();
		}
		return false;
	}
	
	

	public static int findPersonWithEmail(String email){
		System.out.println("finding person with email");
	try{
		Statement st = con.createStatement();
		String sqlCommand = String.format("select person_id from people_table where person_email='%s';", email);
		ResultSet rs = st.executeQuery(sqlCommand);
	
		
		if(rs.next()){
			int id = rs.getInt(1);
			return id;
		}else{
			return 0;
		}
		
	
	}catch(SQLException e){
		e.printStackTrace();
		return 0;
	}
	
	
}

	public static boolean personIsSubscribedToList(int person_id,int list_id){
		System.out.println("inside personIsSubscribedToList method");
		try{
			Statement st = con.createStatement();
			String sqlCommand = String.format("select person_id,list_id from subscriptions_table where person_id='%s' and list_id=%s;", person_id,list_id);
			ResultSet rs = st.executeQuery(sqlCommand);
			if(rs.next()){
				System.out.println("person is subscribed to list");
				return true;
			}
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		
		
		return false;
	}


	public static boolean subscribePersonToList(int person_id,int list_id){
		System.out.println("inside subscribePersonToList method");
		try{
			Statement st = con.createStatement();
			String sqlCommand = String.format("INSERT INTO subscriptions_table (person_id, list_id) VALUES ('%s', '%s');", person_id,list_id);
			st.execute(sqlCommand);
			
		
			return true;
			
			
		}catch(SQLException e){
			e.printStackTrace();
		}
		
		
		return false;
	}
		
		
	
	
	

	
	
	public static int insertPesronIntoPeopleTable(String name, String email){
		System.out.printf("saving person: %s with email: %s\n",name,email);
		int person_id = 0;
		try{
			Statement savePersonStatement = con.createStatement();
			String sqlCommand = String.format("INSERT INTO people_table (person_email, person_name) VALUES ('%s', '%s');", email,name);
			savePersonStatement.executeUpdate(sqlCommand,Statement.RETURN_GENERATED_KEYS);
			System.out.println("Person was added");
			
			ResultSet rs = savePersonStatement.getGeneratedKeys();
	        if (rs.next()){
	            person_id = rs.getInt(1);
	        }
			
			return person_id;
		}catch(SQLException e){
			e.printStackTrace();
			System.err.println("SQLState: " +
                     ((SQLException)e).getSQLState());

                 System.err.println("Error Code: " +
                     ((SQLException)e).getErrorCode());

                 System.err.println("Message: " + e.getMessage());
		}
		
		return person_id;
	}


	
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

package emailSubscriptionPackage;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.ResultSet;

import java.util.*;

import java.sql.*;

public class MySQLConnection {

public static Connection con;

public static void connect(){

try{

Class.forName("com.mysql.jdbc.Driver");

System.out.println("Driver loading success!");

String url = "jdbc:mysql://localhost/email_subscription_bd?autoReconnect=false&useSSL=false";

String name = "root";

String password = "123";

try{

con = DriverManager.getConnection(url,name,password);

System.out.println("Connected!");

}catch(SQLException e){

e.printStackTrace();

}

}catch(ClassNotFoundException e){

e.printStackTrace();

}

public static void closeConnection(){

try{

con.close();

System.out.println("Disconnected");

}catch(SQLException e){

e.printStackTrace();

}

public static boolean listExistsWithId(int list_id){

try{

Statement st = con.createStatement();

String sqlCommand = String.format("select list_id from lists_table where list_id=%s",list_id);

ResultSet rs = st.executeQuery(sqlCommand);

if(rs.next()){

return true;

}

}catch(SQLException e){

e.printStackTrace();

}

return false;

}

public static int findPersonWithEmail(String email){

System.out.println("finding person with email");

try{

Statement st = con.createStatement();

String sqlCommand = String.format("select person_id from people_table where person_email='%s';", email);

ResultSet rs = st.executeQuery(sqlCommand);

if(rs.next()){

int id = rs.getInt(1);

return id;

}else{

return 0;

}

}catch(SQLException e){

e.printStackTrace();

return 0;

}

public static boolean personIsSubscribedToList(int person_id,int list_id){

System.out.println("inside personIsSubscribedToList method");

try{

Statement st = con.createStatement();

String sqlCommand = String.format("select person_id,list_id from subscriptions_table where person_id='%s' and list_id=%s;", person_id,list_id);

ResultSet rs = st.executeQuery(sqlCommand);

if(rs.next()){

System.out.println("person is subscribed to list");

return true;

}

}catch(SQLException e){

e.printStackTrace();

}

return false;

}

public static boolean subscribePersonToList(int person_id,int list_id){

System.out.println("inside subscribePersonToList method");

try{

Statement st = con.createStatement();

String sqlCommand = String.format("INSERT INTO subscriptions_table (person_id, list_id) VALUES ('%s', '%s');", person_id,list_id);

st.execute(sqlCommand);

return true;

}catch(SQLException e){

e.printStackTrace();

}

return false;

}

public static int insertPesronIntoPeopleTable(String name, String email){

System.out.printf("saving person: %s with email: %s\n",name,email);

int person_id = 0;

try{

Statement savePersonStatement = con.createStatement();

String sqlCommand = String.format("INSERT INTO people_table (person_email, person_name) VALUES ('%s', '%s');", email,name);

savePersonStatement.executeUpdate(sqlCommand,Statement.RETURN_GENERATED_KEYS);

System.out.println("Person was added");

ResultSet rs = savePersonStatement.getGeneratedKeys();

if (rs.next()){

person_id = rs.getInt(1);

}

return person_id;

}catch(SQLException e){

e.printStackTrace();

System.err.println("SQLState: " +

((SQLException)e).getSQLState());

System.err.println("Error Code: " +

((SQLException)e).getErrorCode());

System.err.println("Message: " + e.getMessage());

}

return person_id;

}

This is my lists_table with just one list in it:

lists_table

After I get the request from the web form I first check if the list with a specified id exists. Just in case.

Then I add a person to the people_table, if that person is not already in the table, which can be checked by the email: each email is unique in the people_table.

people_table

After that I get the person_id from the table and add it to the subscriptions_table along with the list_id, which is 1 all the time (it was hardcoded into the web form, as I showed above). This is my subscriptions_table:

subscriptions_table

What’s next

Since I am more interested in learning databases (and a little bit of web development) than in learning the mailing stuff, I will postpone the learning of JavaMail until better times. Instead, I will create an admin panel for my app. It will allow me to see my lists of subscribers from the browser.

To be continued.

Adding servlet-api.jar to Eclipse project

As you may know, if you have servlets in you Java project you need to add servlet-api.jar to you Web App Libraries. Since I don’t work in Eclipse very often, I always keep forgetting how to do it. So, here is a short instruction with pictures.

Go to Web App Libraries/Build Path/Configure Build Path…

screen-shot-2016-09-11-at-21-31-16

Click Add External JARs…

screen-shot-2016-09-11-at-21-30-40

Find and choose the servlet-api.jar file. It should be located in the lib folder of your Tomcat. In my case the path to the file is:

/usr/local/apache-tomcat-8.0.33/lib/servlet-api.jar

Developer’s guide to learning design (and Photoshop)

Here are my thoughts on how one can start learning UI design.

For a very long time I thought that I don’t need to learn design. Or, more precisely, I thought that I must not learn design, because I’m a specialist, and specialists don’t waste their time on things that are outside of their domain of expertise.

But the thing is, I’m interested in design. As far as I can remember I always loved to draw, and I believe I do have at least some artistic abilities. Also, it seems so cool to be able to design and then develop you own apps from start to finish. Nothing hinders your creativity. You don’t just implement other people’s ideas, you come up with your own ideas, and everything you create is truly yours.

So, I decided to at least try to learn something about design. Maybe I’ll get serious about this design thing one day. And maybe it will not be that bad. Yes, if I do it for real, I might be spreading myself to thinly. But on the other hand, what if I become one of those mythical creatures called unicorn developers? Wouldn’t that be cool?

Learning design is a lot like learning programming

Anyway, for now I’m just learning the basics. More specifically, I decided to learn Photoshop. I have already spent several evenings learning Photoshop, and here is what I have found out.

Learning design is a lot like learning programming. At first I didn’t know where to start, and I thought that maybe I needed to buy a course on Photoshop. But then I decided to take the approach I used for learning programming. And it worked.

First of all you need to learn how to use the tools. To do it you just draw something. And when you try to draw something in Photoshop, you are going to have lots of questions. When you have questions, just Google them. That’s how you learn design. And that’s how I learned programming. There is no need to buy any courses.

What to draw? Don’t try to create at first. Just copy. I believe it is very important, and here is why. If you try to create something as a beginner, you creativity will be hindered by you lack of ability. So you will end up producing something too simple and too ugly. You might have a great vision inside your head, but then you will have to simplify it and distort it to match your limited abilities.

On the other hand, if you just forget about creativity for a while, and concentrate on copying stuff, that will enable you to develop your abilities. This way you know exactly what you are trying to achieve, and you just do whatever it takes to recreate someone else’s design. Your newly developed skills will let you be creative later.

The best way to start with this approach is to go through some tutorials. Here are the tutorials I used.

Tutorial 1. Tutorial 2. Tutorial 3.

And this is just one example of what I ended up with:

web-ui-kit

Tutorials are good, because they tell you in great detail, what you should do. I think it is the best way to learn different tools in Photoshop.

I plan on going through a couple more tutorials. Then, the next step is to find a design that I like and try to copy it. This time without a tutorial. Then I will do it for many times for different designs.

Notice, how it is exactly the same as learning programming. In programming you too go through a bunch of tutorials at first. Then you create a bunch of your own apps. Only after that you read books and go through courses.

Other things I discovered

When it comes to mechanical skills of a designer (like ability to use Photoshop) I believe they can be all divided into two big areas.

One is all about rectangles and texts. Seriously, while going through the tutorials, that was all I was doing: drawing rectangles and texts. Now, when we talk about text, there is this thing called typography which is an entire field of knowledge in its own right, and there is a lot I can learn there. But for now I like to think about texts and rectangles as being two parts of one skill set which is all about placing stuff on the screen.

Note, that here I’m only talking about the mechanics of design process. There is a difference between knowing how to place stuff on the screen and knowing where to place stuff on the screen. I’m not even starting to think about learning the latter part just yet.

The second area (or a skill set) is all about illustrations. This one I haven’t yet started to learn. But I think it can be learned the same way, through tutorials. In this case the tutorials don’t need to be necessarily focused on UI design. They can be more general and focused on little things, like, for instance, how to draw a semicircle, or a sector. Stuff like that. Also I know that designers like to use other people’s artwork when it comes to icons, so maybe learning how to do illustrations is not that crucial for a UI designer.

There is also a funny little thing I have learned. Usually, when designers give me their designs so that I can start developing an app, I often notice that they misspell words. Like all the time. And over time that lead me to believe that maybe designers as a species are sort of not very bright people, to put it mildly.

Now, when I started learning Photoshop I discovered the real reason for designers’ ‘illiteracy’. It turned out that Photoshop doesn’t have a spell checking capability (or at least I haven’t found one). BTW, can you find a typo in the picture in this post? I didn’t make it on purpose.

On writing short posts from now on

Today I came up with an idea of how I should run my blog from now on.

Here is the thing. I learn something about programming every day. But what I learn during one day is usually not enough to make for a good, average sized article on this blog. Of course, I can just research a specific topic for several days and when I have enough to say about the topic write a blog post. That’s the way I was trying to run this blog up until now.

But the problem is that I am not very consistent with my topics. I switch them very often. Some time ago I decided to create a web app with JavaMail and MySQL server (I wrote a post about it). The next day I thought it would be cool if I was able to do some UI/UX design on top of programming. So I started learning photoshop. Couple of days ago I had to use some Python on my job (I learned a little bit of Python a long time ago, but I have never used it at my job before). As a result I got interested in Python, so now I want to learn more about it. And so on.

What I like about my blog posts is that I can always come back to them and be reminded of the stuff that I have once learned. I like to use this blog as my personal learning diary. That way I don’t have to Google the same stuff over and over again. But again, the problem is that, because I am not consistent with my topics, I don’t write a lot of articles on this blog.

And here is my idea. What if instead of trying to write normal sized articles I would just write every day about the stuff that I learned that day. Sure, these short articles will be of lesser value for the readers. But at least I will be able to write on a regular basis. And I will have my learning diary, which is super cool.

That’s it. I don’t want to commit myself to daily blogging just yet, but I suspect that I will in the near future.

For now I just allow myself to write short posts.

My new project – creating an email subscription web service (Part 1)

This post starts a series of posts on me creating a certain web app. I will create this app for educational purposed as I am currently learning web development. As you may know, my specialty is iOS development, but I feel like I need to get at least some experience in server side development.

The best way to learn how to program is to build something for real, so I have chosen to create an email subscription web service. You know, the kind of service for entrepreneurs that allows them to build lists of their potential customers with emails. The one example of such an app that I know of is MailChimp. So, I’m going to do something similar.

There will be a difference though, that will make things much easier for me. The difference is that my app will be entirely mine, meaning that I will be its only user. This app will help me collect the emails of other people. Who are those other people? Maybe the future readers of my blog. Maybe future customers of my future businesses. Who knows? The thing is that creating such an app is a challenging task and it requires learning a lot of useful stuff, like programming a web app and designing a database.

I will be using Java server with JavaMail framework and MySQL database. Mind you I don’t know much about any of those things. I will be learning them along the way as I will be developing my app.

The first step: database

First of all I guess I’m going to design a database. I have a MySQL server installed on my computer. I have already created a database called email_subscription_db in it. Before creating tables and populating them with data I decided that I should learn how to use some visual tool for dealing with databases. Designing a database through Terminal window requires too much typing. So I downloaded MySQL Workbench. I looked at it and it seems easy to use. I haven’t done much with it yet, though.

Before creating tables I need to figure out exactly which tables I will need. And still before that I need to decide what things my app should be able to do, because that will determine what tables I’ll be using.

Web app features

First of all there should be multiple lists of people. And I should be able to send different emails to different lists of people. Also it should be possible to send series of emails that were prepared in advance. I should be able to see the history of my communication with my subscribers as well as stats. It already sounds too complicated for me. Let’s simplify things a little bit. I don’t need to create the whole app in one iteration. For now I can create just a tiny part of the app with the most necessary functionality.

Simplification

So, what are the things that I need right now? I’ll keep multiple lists, because they are not so hard to create. But I will forget about preprogrammed series of emails for now. And no stats. Basically what I need to be able to do is choose one list of subscribers and send them one letter.

Now what tables do I need to accomplish just that? Here is a list of tables that I think I will need.

people table which will contain peoples emails, names and other information
lists table which will contain basically the names of the lists of subscribers.
Lists and people have a many-to-many relationship, meaning that one person can subscribe to multiple lists, and vice versa: one list can have multiple subscribers. To connect the two tables together I need an intermediate table, which I will call Subscriptions. Each subscription will contain an id of a person and an id of a list to which that person is subscribed.
Letters table. This is a table that will contain the letters that I’ll be sending to my subscribers. Each letter will belong to exactly one list. For now this table won’t contain any information on the time when the letter were sent. Or, you know what? Maybe I don’t even need this last table right now. In the first release of my app there will be no way to save letters, so there really is no need for this table.

So, that’s it. I only need three tables: people, lists and subscriptions.

Now I’m going to start playing around with MySQL Workbench.

Part 2

How I became a professional iOS developer in 12 months

In this post I’m going to share my story with you. If you plan to change careers and go into IT, it might serve you as an example. I’m sure there are many ways to go into IT, and the way I did it might not be appropriate for you, but anyway…

My previous profession

So, let’s start from the beginning. After I graduated from university in my home city of Stavropol (Russia) I went to work as a civil engineer at a company located in the same city. The work was all about oil field development. My job was to design all sorts of structures that are needed on oil fields like foundations for different modular buildings, structures that support oil pipes and many others. It seemed like a cool job at first, but I soon realised that there was no place for creativity in that job. After you learn the basics you just repeat the same process over and over again, copying the same stuff (with little modifications) from one AutoCad drawing to another. I worked there for 2 years. All this time I lived in my parents’ house.

Then I decided to move to Moscow. How I did it with just 50000 rubles in my pocket and what misadventures I had to go through is a whole separate story. Let me just tell you it wasn’t as easy as I thought it would be. I couldn’t find a job for 4 months.

Finally I was able to get a job. Now my job was to design so called structural health monitoring systems. It was more interesting than my previous job, and I liked it at first. But after some time it became pretty much repetitive. It also didn’t pay well.

By the way, what I hated most about my two engineering jobs is that they were not intellectually demanding. I always felt that I wasn’t realising my potential. I did the work that can be done by people who are not so bright as I was. And what’s irritating, those people got promoted all the time, not me. Because I don’t have people skills, I’m not talkative and so on. Basically, I felt that I was in the wrong place.

How I decided to go into IT

I have a friend in Moscow who works in IT. He’s a product manager. And he’s always been telling me that I could become a good programmer. He advised me to take courses on programming, which I did. The idea was simple: I would learn programming, find a job as a programmer and eventually start to make 2 or 3 times more money than I was making at that time, plus programming is more intellectually demanding activity than filling spreadsheets and editing Word files, so hopefully I wouldn’t be bored all the time.

So, for almost one year in 2011 – 2012 I attended a university on weekends. I was studying Java development. Actually the courses were more general than that. I would learn a little bit about everything in the IT field. As for the actual Java development, there wasn’t a lot of practical stuff in those courses.

Towards the end of that studying I caught a entrepreneurial disease. I watched some YouTube videos, read some books and was convinced that I needed to become a businessman. Immediately. I quit my courses even though I had only one month left to finish them properly and get my degree. I became what is called a wantrepreneur. At first I tried to do MLM. After four months I decided that was not a good idea. Then I tried to do infobusiness for several months. It went a little bit better, but still I couldn’t make money: I was paying more money for the ads than I was getting money from my customers. So eventually I quit that too. I did all this stuff as I was working at my day job.

Finally, after all my attempts to start a business failed, I found myself in a pretty nasty situation. At that point I’d been working at my job for 4 years. I had no new business ideas left. The initial plan to enter the IT was long forgotten. I remember that all I could think of at that time was how much I hated my job.

Luckily, at the very same time I had saved a large amount of money. Since I hated my job so much, for years I’ve been getting myself ready to quit it by saving money. Finally, I quit my job when I had enough money to live in Moscow unemployed for 2 years. I mean no buying stuff, no entertainment, just paying rent and buying food.

You might ask why save so much money in order to quit the job. The reason was that I didn’t just want to quit my job to immediately find another one. I needed to do something different. I didn’t know what, exactly. I needed time to think.

After one month of thinking (while living in Moscow, which is costly for an unemployed person) I still didn’t know what to do, so I started looking for a job. I really didn’t want to get another job as a civil engineer so I was kind of depressed at that time. Actually I didn’t really look for a job, I was just reading through job postings on the internet thinking to myself how sad it was that I’d learned to earn my living by doing things that I hate.

Just at that period of time I met with my friend I told you about a while ago. The IT guy. And while we were discussing my situation he reminded me of my intention to enter the IT. I’d totally forgotten about it, by the way. For some reason I didn’t remember about the IT thing during the whole month I was unemployed in Moscow. But as soon as my friend reminded me that I still had that option, I knew that moment that was exactly what I’d do.

It was the best thing I could do in that situation. I couldn’t stand a thought of just getting another job at that time. And I had plenty of money. So I could spent some time just learning to code. I decided to learn iOS development so I bought myself a MacBook Air. And I already had an iPad at that time. So that’s how I started. There was no reason to stay in Moscow while I was learning, so I moved back to Stavropol to live with my parents. There I only spent money on my food. I didn’t go out much. So I calculated that with that kind of living and with the amount of money that I had I could afford to be unemployed for about 4 years if I wanted to. But I figured that one year would be more than enough to learn programming.

How I learned the actual stuff

I started with the Stanford SC193P lectures on iOS development. I watched all those lectures in the first month of my studying while I was still in Moscow. Well, let me give you some timing.

I quit my job in the beginning of August 2014.

I decided to study iOS development in the beginning of September 2014.

I moved to Stavropol in the middle of October 2014.

I moved back to Moscow and got my first job as a programmer in the middle of August 2015.

So, for the first month I just watched lectures and did my homework. Then I did some tutorials. I released my first app to the App Store in November 2014, I believe. Then I spent several months till March 2015 making and releasing my other apps. Here are my apps, by the way, if you want to check them out:

https://itunes.apple.com/ru/developer/karen-grigoryan/id948320401

At the beginning I planned to become a freelancer. I didn’t like the idea of working in the office. And I wanted to travel. I even seriously considered the option of moving to Thailand instead of my home city, to learn programming. Good thing I didn’t do that.

At the beginning of 2015 I registered at oDesk (now UpWork) to become a freelancer. I even got a Payoneer card to collect my money that I would make there. But I soon realised that I didn’t have enough experience to get any serious offers. And the easy tasks were hard to get, because too many people could do them. So, I decided that after my study is over I should return to the corporate world for at least several years before thinking about freelancing again.

After March 2015 I started looking at the job postings. I made a list of common requirements for the iOS development jobs. And I started to learn the required technologies one by one.

At about the same time my IT friend decided to start a business. He created a hotel booking site, took a leave of absence from his job and started to work on his business. He told me that I could create an app for his business if I wanted. That way I could practise on a real world business app, and later I’d be able to show this app off on a job interview. So, I spent about one month working on the hotel booking app for my friend’s business. I never finished it, though. And my friend’s business never took off. But I still used that unfinished app during my job interviews when I was looking for my first IT job. And I learned a lot while making this app, so working on this app was not a complete waste of time.

How I finally got my first IT job

Couple of months later, in the beginning of August 2015, I decided to look for a job for real. The first hurdle that came my way was that there were almost no job postings, appropriate for me. All the jobs had very high requirements. At that point I didn’t understand one simple thing. If your skills don’t match the requirements listed in a job posting, that doesn’t mean you can’t apply for that job. You always have a chance. But I didn’t know about it. So, looking through job postings I felt kind of depressed. I even started to doubt if I could ever enter the IT. What if my entire plan was a big mistake?

Then I tried another approach. I found a list of top 100 Russian mobile development companies. About 20 of them were located in Moscow. About 10 were in Saint-Petersburg. Others were in a bunch of other major Russian cities. My plan was simple. I would send my resume to all the Moscow companies first (at that point I was still in Stavropol, the South of Russia). Then when I get job interviews I’d go to Moscow and live in a hostel there temporarily. If I wasn’t lucky and nobody wanted to hire me, I would send my resume to the Saint-Petersburg companies, then move there and repeat the process. Then, if nobody wanted me there either, I’d do the same approach with other cities. I would visit those cities one by one until I finally find a job somewhere.

It turned out everything was much easier than I expected. I got a job on my very first interview. I had a couple more interviews after the first one, but I went there only out of curiosity, because I already knew that I had a job at that point.

And that’s how I became a professional iOS developer in 12 months. Maybe later I will add some concluding thoughts here.

What kind of apps to create to make money

If you look at my apps in the App Store, you’ll find out that they (with the exception of my silly game Ugly Plants) are all related to productivity in some way or another. And they are all pretty basic. They are simple. They don’t have server side. They are journals and to-do lists. There is a reason for why it is that way.

Create only useful apps

My first app was Simple Q&A Diary. I had the idea for this app long before I started learning iOS development. I didn’t think it was going to be a mobile app at that time, though. I thought maybe someday I’d create a web app. But I wasn’t sure how I would do it as I lacked experience in coding. Also I’d have to work on registration and sign in features, which was too complicated for someone who wasn’t even a newbie in programming. At that time I only dreamed that someday I’d learn programming. So it wasn’t a real plan.

Then, when I decided to learn iOS development and I needed to create my first app, I had the idea ready. And what was good about realising my idea in the form of a mobile app was that in a mobile app there was no need to register and log in the users. And the mobile device didn’t need to connect to the internet in order for the app to work. So I created this app. It was a huge success compared to my other apps. It makes me more then $100 a year.

Then I created a game called Ugly Plants. It is a total failure. Nobody likes it. And nobody can find it on the App Store, since nobody is looking for ugly plants. I don’t know what keywords to use to make the game more searchable (or should I say findable?). Anyway, I decided to never make games again, and instead only create useful apps.

Learn from your successes

Ok, let’s not go into the history of all my apps. Let me just tell you what I do to make money from my apps. Here are the guiding principles that I use. First of all, I try to learn from my successes. I know there is a ton of advice on how to promote your app. I don’t use most of it. I don’t have time for PR and I found out that social networks are mostly useless. I guess social networks might be useful if you take them seriously enough and dedicate a lot of time to them. But I don’t have time. I have a full time job. And I don’t take my apps that seriously. The real reason for creating apps is not money but honing my developer skills. And I create apps that I use myself, so even if nobody likes them I haven’t wasted my time creating them. More on that later.

Success for me is having an app that generates $100 a year. Then creating another app that generates $100 a year. Then improve one of my apps so that it starts generating $150 a year. And so on.

How to get an idea for an app? Create apps for yourself

I don’t try to figure out what other people might need. You can do a market research. There is nothing wrong about it. But if you go down that road it is so easy to get mistaken. I personally don’t do it. What I do instead is I create apps for myself. The way I look at it is this. I’m not a unique snowflake. If I need something there must be thousands of people who need the same thing. So, if I create an app for myself, somebody is going to buy it. And that’s what’s happening to my apps. I regularly use all of my apps except that silly game – Ugly Plants. And I see that people are using all of my apps except that silly game that I myself don’t play.

How I price my apps

When I first uploaded my very first app Simple Q&A Diary I made it free. The reason was that I didn’t have my banking account ready for receiving money from the App Store. So the app was free for several months. Then when my banking account was ready I priced the app at $1. It was at that price for quite some time. It was making about $5 a month. Then as an experiment I priced it at $2 for one month. The amount of downloads that month was the same. So by changing the price I doubled my income from this app. I thought maybe it was not the end, so I priced it at $3 for the next month. And the income was much smaller. So I returned it back to $2. That’s how I picked the right price for my first app.

I used the same approach to picking the price for my other apps ever since. I always start small and then increase the price by $1 every month. You need to wait for at least a month to gather enough statistics before changing the price.

One more thing. When I release a paid app I always release it as free first. I keep it free for several days and only after that I put a price on it. The reason for this is that I think there is a high probability that large amount of downloads at the beginning will improve the App Store ratings for the app later on. I always notice that right after release or an update of an app it is actively downloaded for several days and then the number of downloads decreases abruptly and stays low after that. I think that at the beginning the App Store shows your app to some large number of people and rates your app based on their behaviour. The more people from that first sample audience downloaded your app the higher the rating it is going to get. It is just a theory but it looks logical and it explains what I see in the stats of my apps.

Have free apps

I have a free app called Stream Journal. It is downloaded about 5 times a day. I put a button ‘Info’ into it that opens my site in Safari. According to the analytics of my site about 100% of people who downloaded my app visit my site. There they can see my other apps. I don’t know how many of them buy my paid apps. But I think it’s nice to have an audience of hundreds and thousands of people. Later on I’ll be able to promote my paid apps inside my free apps. I’ll be able to use Push Notifications to talk to my audience which is about 2000 people at the time of this writing.

Have a good design

A good design is important. So it is worth it to at least try to make your app look nice. Learning a little bit of Photoshop won’t hurt you, even if you don’t plan to become a designer. But sometimes you don’t even need Photoshop to make your app look a little better. Basically you just need to care about the looks of you app and make at least some effort. When I created my first couple of apps I didn’t think about good design at all. Then when I finally decided to do something about the ugliness of my apps my sales doubled. Take a look at this picture:

comparison of designs

And I didn’t even use Photoshop a lot. Most of the work was done in the Xcode’s Storyboard. The result doesn’t look great, but it looks much better than what was before. That’s the point. If you want your apps to sell, make sure that they at least don’t look like shit.

So, that’s about it. Now you know how I make money on App Store.

My adventures with MySQL and trying to connect it to Java project

First of all, why do I learn MySQL in the first place. As you may know, I’m a professional iOS developer. I’ve been developing apps for more than 1,5 years already. For now, I can only do the client side of an app. Which is ok. It allows me to have a job and make some money. But I always wanted to be able to create a client-server app all by myself. I always knew that eventually I’m going to learn a little bit of server side programming, but up until recently I’ve been postponing the learning.

Some time ago I started learning Java. I played around with servlet programming and created a couple of hello-world type of programmes. Then I decided to learn about JDBC and connect a simple MySQL database to my hello-world servlet. I had some theoretical knowledge about MySQL an it’s query syntax, so I thought I’d just study how to connect MySQL to Tomcat. After spending some time reading I realised that it was not that easy because I lacked hands-on experience with MySQL.

So I decided to set aside servlet programming for a while and learn some basics of MySQL. Now I’m going to tell you what I’ve found out. If you are just starting out with MySQL, this post may be helpful to you. I must warn you that I’m not an expert on this subject. I’ve only been learning this stuff for a couple of days.

The big picture

Just a couple of days ago I wasn’t sure what MySQL really was. Was it a library that one should include into the Java project? Was it a server that I needed to connect to Eclipse? You know, you can tell Eclipse about your Tomcat server, so that Eclipse can use it. Maybe I should have done something similar with MySQL server? I spent some time figuring this stuff out.

I found out that MySQL is a server. And a server is a program that runs on a computer sort of in the background. It’s job is to wait for clients’ requests and to send responses to those requests. What you need to do is download a program called MySQL server and install it on your computer. Then you should start the program. After the MySQL server has started you can talk to it from command line. You can create your database and populate it with initial data using SQL commands.

How do you use MySQL server from your Java program? You need a connector which will connect your Java program to MySQL server. The connector is called JDBC (Java DataBase Connector) and it is a class or a bunch of classes (I’m not sure now what exactly it is). This connector can work with multiple types of database servers, not only with MySQL. To enable it to work in conjunction with MySQL server you will need to download a driver and attach it to you project. But we won’t discuss it in this post.

Downloading MySQL server

To download MySQL server go to this page: http://dev.mysql.com/downloads/mysql/

I downloaded this one: “Mac OS X 10.11 (x86, 64-bit), DMG Archive”. Installation was pretty straightforward.

Starting the MySQL server

Before you can do anything with the MySQL server you need to start it. To start, stop or restart the server at first I used these commands in the Terminal:

sudo /usr/local/mysql/support-files/mysql.server start

sudo /usr/local/mysql/support-files/mysql.server stop

sudo /usr/local/mysql/support-files/mysql.server restart

Later I found out that there was a more convenient way to start and stop the server. If after installing MySQL server you go to System preferences you will see a MySQL icon. Click it. Now you have a button with which you can start and stop the server.

To connect to the server from Terminal go to this folder /usr/local/mysql/bin

cd /usr/local/mysql/bin

and execute this command:

./mysql -u root -h localhost -p

(here -u means user, -h means host and -p means password).

Now type in your temporary password which you got right after you installed MySQL on your computer.

Now, if you try to execute some command, mysql will tell you that you need to set a new password instead of your temporary password. I used this command to do it:

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;

MyNewPass is your new password, obviously. I used the password 123. So my command looked like this:

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘123’;

Annoying issue

After playing around a little with MySQL something went wrong. I wasn’t able to stop or start the server. There was some error like “The server quit without updating PID file”. There were other errors related to this “PID file”. I Googled and Googled. Different people on the internet suggested different things. Somebody said you should edit PID file (I couldn’t even locate this file, by the way). Another person said you should delete it. Yet another one said you should create an empty PID file. Nothing helped. Then somebody said you should just kill the mysql process. Like this:

sudo kill -9 PID

PID is a number of the process. You get it from a list of processes which you can see if you type

ps aux | grep mysql

into the command line. I killed this process but it kept resurrecting. Even restarting my computer didn’t help. At this point I was really annoyed. All I wanted to do was to play around with MySQL server and practise some commands. And instead I spent several hours not even knowing what I was doing.

Finally I decided to just remove the MySQL server from my computer and install it one more time. I found the instruction on how to do it and just typed blindly these commands into the Terminal one by one:

sudo rm /usr/local/mysql

sudo rm -rf /usr/local/mysql*

sudo rm -rf /Library/StartupItems/MySQLCOM

sudo rm -rf /Library/PreferencePanes/My*

edit /etc/hostconfig and remove the line MYSQLCOM=-YES- (I didn’t do this one because there wasn’t such a file on my computer)

rm -rf ~/Library/PreferencePanes/My*

sudo rm -rf /Library/Receipts/mysql*

sudo rm -rf /Library/Receipts/MySQL*

sudo rm -rf /private/var/db/receipts/*mysql*

After I deleted the server the mysql process was still running, so I had to restart my computer one more time to get rid of it. Then I just reinstalled MySQL server and after that it worked just fine.

Learning MySQL commands

I used the “MySQL 5.6 Reference Manual”, which you can find here: https://dev.mysql.com/doc/refman/5.6/en/tutorial.html, and went through it. More precisely, I went through sections 3.1 to 3.5 of chapter 3 called “Tutorial”. I didn’t just read it, but practised commands in the Terminal. It took me about 1,5 hours. I could have gone further and read section 3.6 “Examples of Common Queries”, but I decided I don’t need it for now. The idea was to learn just enough to be able to create a simple database and connect it to Java project.

What’s next

My next step will be to practise servlet programming some more and finally learn how to use MySQL server in my Java project. I will write another post about it.