almost working with spring-jdbc

1 files changed, 99 insertions, 28 deletions

diff --git a/src/test/java/com/juick/tests/ApiTests.java b/src/test/java/com/juick/tests/ApiTests.java
index f7d6d6ac..83afb62e 100644
--- a/src/test/java/com/juick/tests/ApiTests.java
+++ b/src/test/java/com/juick/tests/ApiTests.java
@@ -1,18 +1,21 @@
 package com.juick.tests;
 
 import com.juick.Message;
+import com.juick.Tag;
 import com.juick.User;
 import com.juick.server.MessagesQueries;
+import com.juick.server.TagQueries;
 import com.juick.server.UserQueries;
 import junit.framework.Assert;
+import org.junit.After;
 import org.junit.Before;
 import org.junit.Test;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.jdbc.datasource.DriverManagerDataSource;
 
-import java.io.FileInputStream;
-import java.io.IOException;
-import java.util.Properties;
+import java.util.ArrayList;
+import java.util.Calendar;
+import java.util.List;
 
 /**
  * Created by vt on 14.01.2016.
@@ -21,35 +24,103 @@ public class ApiTests {
     JdbcTemplate jdbc;
     @Before
     public void setupConnection() {
-        Properties conf = new Properties();
-        try {
-            conf.load(new FileInputStream("/etc/juick/api.conf"));
-            DriverManagerDataSource dataSource = new DriverManagerDataSource();
-            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
-            dataSource.setUrl("jdbc:mysql://localhost/juick?autoReconnect=true&user="
-                    + conf.getProperty("mysql_username", "")
-                    + "&password=" + conf.getProperty("mysql_password", ""));
-            jdbc = new JdbcTemplate(dataSource);
-        } catch (IOException e) {
-            e.printStackTrace();
-        }
+        DriverManagerDataSource dataSource = new DriverManagerDataSource();
+        dataSource.setDriverClassName("org.h2.Driver");
+        dataSource.setUrl("jdbc:h2:~/test");
+        jdbc = new JdbcTemplate(dataSource);
+        jdbc.execute("CREATE TABLE bl_users (user_id int(10) unsigned NOT NULL, " +
+                "bl_user_id int(10) unsigned NOT NULL, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP )");
+        jdbc.execute("CREATE TABLE messages (" +
+                "message_id int(10) unsigned NOT NULL AUTO_INCREMENT," +
+                "user_id int(10) unsigned NOT NULL," +
+                "place_id int(10) unsigned DEFAULT NULL," +
+                "lat decimal(10,7) DEFAULT NULL," +
+                "lon decimal(10,7) DEFAULT NULL," +
+                "ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," +
+                "replies smallint(5) unsigned NOT NULL DEFAULT 0," +
+                "maxreplyid smallint(5) unsigned NOT NULL DEFAULT 0," +
+                "privacy tinyint(4) NOT NULL DEFAULT '1'," +
+                "attach nchar(3) check (attach in ('jpg', 'mp4', 'png'))," +
+                "readonly tinyint(1) NOT NULL DEFAULT 0," +
+                "likes smallint(6) NOT NULL DEFAULT 0," +
+                "hidden tinyint(3) unsigned NOT NULL DEFAULT 0" +
+                ")");
+        jdbc.execute("CREATE TABLE messages_tags (" +
+                "message_id int(10) unsigned NOT NULL," +
+                "tag_id int(10) unsigned NOT NULL" +
+                ")");
+        jdbc.execute("CREATE TABLE users (" +
+                "id int(10) unsigned NOT NULL AUTO_INCREMENT," +
+                "nick char(64) NOT NULL," +
+                "passw char(32) NOT NULL," +
+                "banned tinyint(3) unsigned NOT NULL DEFAULT 0)");
+        jdbc.execute("CREATE TABLE useroptions (" +
+                "user_id int(10) unsigned NOT NULL," +
+                "jnotify tinyint(1) NOT NULL DEFAULT 1," +
+                "subscr_active tinyint(1) NOT NULL DEFAULT 1)");
+        jdbc.execute("CREATE TABLE subscr_users (" +
+                "user_id int(10) unsigned NOT NULL," +
+                "suser_id int(10) unsigned NOT NULL," +
+                "jid char(64) DEFAULT NULL," +
+                "active tinyint(1) NOT NULL DEFAULT 1," +
+                "ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)");
+        jdbc.execute("CREATE TABLE messages_txt (" +
+                "message_id int(10) unsigned NOT NULL," +
+                "tags varchar(255)," +
+                "repliesby varchar(96)," +
+                "txt TEXT)");
+        jdbc.execute("CREATE TABLE tags(" +
+                "tag_id int(10) unsigned NOT NULL AUTO_INCREMENT," +
+                "synonym_id int(10) unsigned DEFAULT NULL," +
+                "name char(48) NOT NULL," +
+                "top tinyint(1) unsigned NOT NULL DEFAULT 0," +
+                "stat_messages int(10) unsigned NOT NULL DEFAULT 0," +
+                "stat_users  smallint(5) unsigned NOT NULL DEFAULT 0)");
+        jdbc.execute("CREATE TABLE replies (" +
+                "message_id int(10) unsigned NOT NULL," +
+                "reply_id smallint(5) unsigned NOT NULL," +
+                "user_id int(10) unsigned NOT NULL," +
+                "replyto smallint(5) unsigned NOT NULL DEFAULT 0," +
+                "ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP," +
+                "attach nchar(3) check (attach in ('jpg', 'mp4', 'png'))," +
+                "txt text)");
     }
-
-    @Test
-    public void userTests() {
-        User user = UserQueries.getUserByUID(jdbc, 3694);
-        Assert.assertEquals("it should be me", "vt", user.getUName());
+    @After
+    public void tearDown() {
+        jdbc.execute("DROP ALL OBJECTS DELETE FILES");
     }
+
     
     @Test 
     public void messageTests() {
-        Message msg = MessagesQueries.getMessage(jdbc, 2817722);
-        User ugnich = msg.getUser();
-        Assert.assertEquals("ugnich", ugnich.getUName());
-        org.junit.Assert.assertEquals("ugnich", MessagesQueries.getMessageAuthor(jdbc, 2817722).getUName());
-        Assert.assertEquals("Microsoft", msg.Tags.get(0));
-        Message reply = MessagesQueries.getReply(jdbc, 2817722, 8);
-        User linda = reply.getUser();
-        Assert.assertEquals("Linda-chan", linda.getUName());
+        int user_id = UserQueries.createUser(jdbc, "me", "secret");
+        User user = UserQueries.getUserByUID(jdbc, user_id);
+        Assert.assertEquals("it should be me", "me", user.getUName());
+        int mid = MessagesQueries.createMessage(jdbc, user_id, "yo", null, new ArrayList<>());
+        Message msg = MessagesQueries.getMessage(jdbc, mid);
+        org.junit.Assert.assertEquals("yo", msg.getText());
+        Calendar calendar = Calendar.getInstance();
+        calendar.setTime(msg.getDate());
+        org.junit.Assert.assertEquals(2016, calendar.get(Calendar.YEAR));
+        User me = msg.getUser();
+        Assert.assertEquals("me", me.getUName());
+        org.junit.Assert.assertEquals("me", MessagesQueries.getMessageAuthor(jdbc, mid).getUName());
+        int tagID = TagQueries.createTag(jdbc, "weather");
+        Tag tag = TagQueries.getTag(jdbc, tagID);
+        List<Tag> tagList = new ArrayList<>();
+        tagList.add(tag);
+        int mid2 = MessagesQueries.createMessage(jdbc, user_id, "yo2", null, tagList);
+        Message msg2 = MessagesQueries.getMessage(jdbc, mid2);
+        org.junit.Assert.assertEquals(1, msg2.Tags.size());
+        int ugnich_id = UserQueries.createUser(jdbc, "ugnich", "x");
+        User ugnich = UserQueries.getUserByUID(jdbc, ugnich_id);
+        int rid = MessagesQueries.createReply(jdbc, msg2.getMID(), 0, ugnich.getUID(), "bla-bla", null);
+        org.junit.Assert.assertEquals(1, rid);
+        Message msg3 = MessagesQueries.getMessage(jdbc, mid2);
+        org.junit.Assert.assertEquals(1, msg3.Replies);
+        org.junit.Assert.assertEquals("weather", msg3.Tags.get(0));
+        org.junit.Assert.assertEquals(ugnich.getUID(), UserQueries.checkPassword(jdbc, ugnich.getUName(), "x"));
+        org.junit.Assert.assertEquals(-1, UserQueries.checkPassword(jdbc, ugnich.getUName(), "xy"));
     }
+
 }