List

Try: /ViewFile.php?path=ViewFile.php -- Online

package ca.tecreations.db.mysql.runtime;

import ca.tecreations.*;
import ca.tecreations.db.DB;
import ca.tecreations.db.mysql.MySQL;
import ca.tecreations.misc.TString;
import ca.tecreations.net.Client;
import ca.tecreations.net.NoTLSConnectionException;

import tim.Tim;
// MySQL Shortcut for user creation.
// CREATE USER 'test_user'@'%' IDENTIFIED BY 'pass';
// GRANT ALL PRIVILEGES ON test_db.* to 'test_user'@'%' IDENTIFIED BY 'pass';
// FLUSH PRIVILEGES;
// CREATE DATABASE test_db;

/**
 *
 * @author tim
 */
public class Topics {
    DB db = null;
    MySQL mysql = null;
    Client client = null;

    public Topics() {
        String host = "sumscool.ca";
        int port = 3306;
        String db = "sumscool_ca";
        String user = "tim";
        String pass = "redacted";
        connect(host,port,db,user,pass);   

        try {
            client = new Client(new Properties(ProjectPath.getNetConfigPath() + "Client_sumscool.ca.properties"),true,null);
        } catch (NoTLSConnectionException ntlsce) {
            System.out.println("No connection to client. Can't do .init");
            System.exit(0);
        }
    }
    
    public void connect(String host, int port, String db, String user, String pass) {
        mysql = new MySQL(host,port,db,user,pass.toCharArray());
        this.db = mysql;
    }
    
    public TString getFirstMediaDir() {
        return new TString(TString.MYSQL_TABLES,"a");
        
    }
    
    public String get(String key) {
        return mysql.getValue("SELECT v FROM data WHERE k='" + key + "'");
    }
    
    public void set(String key, String value) {
        String last = mysql.getValue("SELECT v FROM data WHERE k='" + key + "'");
        if (last == null) {
            mysql.issue("INSERT INTO data (k,v) VALUES('" + key + "','" + value + "')");
        } else {
            mysql.issue("UPDATE data SET v='" + value + "' WHERE k='" + key + "'");
        }
    }

    public void createTopicsDatabases() {
        mysql.issue("DROP DATABASE IF EXISTS topics");
        mysql.issue("CREATE DATABASE IF NOT EXISTS topics");
        mysql.issue("USE topics");
        mysql.issue("DROP DATABASE IF EXISTS topics_tables");
        mysql.issue("CREATE DATABASE IF NOT EXISTS topics_tables");
        mysql.issue("USE topics");
    }
    
    public void createTopicsTables() {
        String sql;

        mysql.issue("DROP TABLE IF EXISTS data");
        sql = "CREATE TABLE IF NOT EXISTS data (k VARCHAR(255), v VARCHAR(255))";
        mysql.issue(sql);
        
        mysql.issue("DROP TABLE IF EXISTS topics");
        sql =  "CREATE TABLE IF NOT EXISTS topics(";
        sql += "    topic VARCHAR(8192),";
        sql += "    tableName VARCHAR(64),";
        sql += "    created VARCHAR(" + Tim.TIMS_TIME_WIDTH + ")";
        sql += ")";
        mysql.issue(sql);
    }
    
    public String getLastTopicDirName() {
        return get("last.topic.dir.name");
    }
    
    public void setLastTopicDirName(String name) {
        set("last.topic.dir.name",name);
    }
    
    public String getNextTopicTableName() {
        String lastTopicTable = getLastTopicDirName();
        if (lastTopicTable == null || lastTopicTable.equals("")) {
            lastTopicTable = new TString(TString.MYSQL_TABLES).get();
        } else {
            lastTopicTable = new TString(TString.MYSQL_TABLES,lastTopicTable).increment();
        }
        setLastTopicDirName(lastTopicTable);
        return lastTopicTable;
    }
    
    public String createTopic(String topic) {
        String tableName = mysql.getValue("SELECT tableName FROM topics WHERE topic='" + topic + "'");
        if (tableName == null) {
            String nextTable = getNextTopicTableName();
            //store("topic_last.media.dir","_");
            String sql = "INSERT INTO topics (topic,tableName,created) VALUES ('" + topic + "','" + nextTable + "','" +
                         Tim.now() + "')";
            mysql.issue(sql);
        
            sql =  "CREATE TABLE IF NOT EXISTS topics_tables." + nextTable + " (";
            sql += "    id INT AUTO_INCREMENT PRIMARY KEY,";
            sql += "    post_title VARCHAR(1024) NOT NULL,";
            sql += "    post_text VARCHAR(8192) NOT NULL DEFAULT \"\",";
            sql += "    post_media_dir_name VARCHAR (255) NOT NULL DEFAULT \"\",";
            sql += "    created VARCHAR (" + Tim.TIMS_TIME_WIDTH + ") NOT NULL";
            sql += ")";
            mysql.issue(sql);
            
            String path = "/var/www/sumscool.ca/html/topics/" + nextTable + "/";
            client.mkdirs(path);
            client.setPOSIXUser(path,"www-data");
            client.setPOSIXGroup(path,"www-data");
            client.setPOSIXFilePermissions(path,775);
            path += "List.php";
            client.copyFile(null,"/var/www/sumscool.ca/html/List.php",path);
            client.setPOSIXUser(path,"www-data");
            client.setPOSIXGroup(path,"www-data");
            client.setPOSIXFilePermissions(path,775);
            return nextTable;
        } 
        return tableName;
        
    }
    
    public MySQL getMySQL() {
        return mysql;
    }
    
    public String getNext(String s) {
        TString tString = new TString(TString.BIG_ALPHA_NUM,s);
        tString.increment();
        return tString.toString();
    }

    public void init() {
        client.empty("/var/www/sumsool.ca/html/topics/");
    }
    
    public static void main(String[] args) {
        Topics storehouse = new Topics();
        MySQL mysql = storehouse.getMySQL();

        System.out.println("Connected: " + mysql.isConnected());
        storehouse.createTopicsDatabases();
        storehouse.createTopicsTables();
         
        String tecLibTable = storehouse.createTopic("tecreations library");
        System.out.println("TecLibTable: " + tecLibTable);
        
        String sumscoolTable = storehouse.createTopic("sumscool");
        System.out.println("sumscoolTable: " + sumscoolTable);
        
        System.exit(0);
    }
}

// DO ALL DELETIONS IN mysql.db AND mysql.user

//flush privileges;
//CREATE USER 'sitesec_user'@'%' IDENTIFIED WITH mysql_native_password BY '';
//GRANT ALL ON sitesec_dev.* TO 'sitesec_user'@'%';
//FLUSH privileges;