I will be giving a general overview of database management in accessory_java (main repository). The code of another Java library based on similar ideas, ib (main repository), is also used as a supporting resource.
I started developing accessory_java while working on a personal project. It was meant to be a container of generic resources which I could eventually use in other applications. Basically, a library including not just useful methods and variables, but also a solid starting point to face virtually any project in Java. Or, in other words, a way to adapt Java to my programming approach, to help me easily overcome the language peculiarities. That initial code has kept growing and evolving until reaching the current stage, which I consider mature, comprehensive and reliable enough.
Main features of accessory_java:
- Built from scratch, by relying on stable functionalities and with the minimum amount of external dependencies possible. At the moment, there is only one external dependency: the MySQL connector. And it can even be ignored in case of not using the database (DB) resources.
- Including virtually no comments or documentation, but with an overall clear structure and quite rigid rules and conventions which are applied in a very systematic and consistent way. Actually, this article and potential future ones might be gradually compensating the aforementioned lacks.
- Main priorities (ordered): friendliness, safety (e.g., default validity checks everywhere or no uncaught exceptions), efficiency/speed, scalability/adaptability.
- Theoretically technology agnostic, although currently mostly focused on MySQL and Linux.
Before using any of the resources of this library, one of the start overloads in the accessory._ini class has to be called, as shown in the code below.
String app_name = samples.APP;
boolean includes_legacy = false;
String db_name = samples.PERFECT_NAME;
String username = samples.PERFECT_USERNAME;
String password = samples.PERFECT_PASSWORD;
boolean encrypt = true;
String user = samples.USER;
boolean delay_encryption = true;
HashMap<String, Object> dbs_setup = null;
if (samples.USE_DB)
{
String setup = null;
String host = null;
if (encrypt && !delay_encryption)
{
dbs_setup = parent_ini_db.get_setup_vals(db_name, setup, user, host, encrypt);
}
else dbs_setup = parent_ini_db.get_setup_vals(db_name, setup, username, password, host);
}
_ini.start(app_name, includes_legacy, dbs_setup);
if (encrypt && delay_encryption)
{
paths.update_dir(paths.DIR_CRYPTO, samples.PERFECT_LOCATION);
paths.update_dir(paths.DIR_CREDENTIALS, samples.PERFECT_LOCATION);
db.update_credentials(db_crypto.SOURCE, user, username, password);
}public abstract class samples
{
public static final String APP = "whatevs";
public static final String ID = "whichevs";
public static final String USER = "whoevs";
public static final String PLACEHOLDER = "wherevs";
public static final boolean USE_DB = false;
public static final String PERFECT_NAME = PLACEHOLDER;
public static final String PERFECT_LOCATION = PLACEHOLDER;
public static final String PERFECT_USERNAME = PLACEHOLDER;
public static final String PERFECT_PASSWORD = PLACEHOLDER;
public static void print_error(String sample_id_) { print_message(sample_id_, null, false); }
public static void print_message(String sample_id_, String message_, boolean is_ok_)
{
String message = sample_id_ + misc.SEPARATOR_CONTENT;
if (is_ok_) message += message_;
else message += "ERROR";
generic.to_screen(message);
}
public static void print_messages(String sample_id_, String[] messages_)
{
String message = sample_id_;
for (String message2: messages_) { message += misc.SEPARATOR_CONTENT + message2; }
generic.to_screen(message);
}
public static void print_end() { generic.to_screen("sample code completed successfully"); }
}Previous articles about accessory_java:
- Encryption -- accessory_java.
- Context, conventions, structure, evolution -- accessory_java.
- Friendliness, safety, efficiency and adaptability -- accessory_java.
- Types and classes -- accessory_java.
- Arrays -- accessory_java.
A Java library easing the automated communication with Interactive Brokers (IB). It relies on and emulates the defining ideas of accessory_java. An analysis of this library is beyond the scope of the present article and its code will be merely used to support the explanations about accessory_java.
DB management is, by far, the most complex and most important part of these libraries. That's why this discussion is spread across two articles. The present one gives a general overview without entering much into the specifics. The next article will be focusing on the details of the most relevant approaches and implementations.
As explained in previous works, all the references to "types" are made in the accessory_java sense, understood as categorised constants managed by the _types classes. Here, I am also talking about "DB types", which refer to both the corresponding accessory_java types (i.e., all the accessory._types with the root accessory._types.CONFIG_DB_SETUP_TYPE, a condition only met by accessory._types.CONFIG_DB_SETUP_TYPE_MYSQL for the time being) and the underlying reality (e.g., MySQL as one of the existing DB alternatives). See the previous article to know more about my ideas regarding ambiguity, clarity and understanding skills. Those still having doubts should consider contacting my biographer. In case of doing so, remember to say "how can a clam cram in a clean cream can?" to prove that you have read and understood these instructions. You can recite it backwards if that makes you feel special.
public static String check_type(String type_, String[] types_)
{
return check_type(type_, types_, null, null);
}
public static String check_type
(
String type_, String[] types_, String action_add_remove_, String type_add_remove_
)
{
String output = strings.DEFAULT;
String type2 = strings.normalise(type_);
if (!strings.is_ok(type2)) return output;
String type_add_remove = strings.normalise(type_add_remove_);
String action = strings.normalise(action_add_remove_);
for (String type: get_subtypes(strings.DEFAULT, types_))
{
if
(
strings.are_equal(type2, type) || (strings.is_ok(type_add_remove) &&
strings.are_equal(add_type(type2, type_add_remove), type))
)
{
output =
(
strings.are_equal(action, ACTION_REMOVE) ?
remove_type(type, type_add_remove) : type
);
break;
}
}
return output;
}
public static String remove_type(String subtype_, String type_)
{
if (!strings.is_ok(subtype_) || !strings.is_ok(type_)) return subtype_;
String type = type_;
if (!strings.contains_start(type, subtype_, false)) return subtype_;
type += SEPARATOR;
return strings.get_end(subtype_, type.length());
}
public static String add_type(String subtype_, String type_)
{
if
(
!strings.is_ok(subtype_) || !strings.is_ok(type_) ||
strings.contains_start(type_, subtype_, false)
)
{ return subtype_; }
return (type_ + SEPARATOR + subtype_);
}All the DB-related code is stored in the DB classes, which are the ones whose names start with "db_" (e.g., accessory.db_static). This substring can also be added to the name of the given package, as what happens in ib with db_ib.basic, for instance. The _ini_db classes are formally startup classes, although they do have an important contribution to DBs and will be analysed too.
@SuppressWarnings("unchecked")
protected boolean populate_all_dbs(HashMap<String, Object> dbs_setup_)
{
HashMap<String, Object> setup_vals = (HashMap<String, Object>)arrays.get_new(dbs_setup_);
String db = common.DEFAULT_DB;
String name = (String)arrays.get_value(setup_vals, accessory._types.CONFIG_DB_NAME);
if (!strings.is_ok(name)) name = common.DEFAULT_DB_NAME;
HashMap<String, Object[]> sources = new HashMap<String, Object[]>();
sources = add_source_market(db, sources);
sources = add_source_execs(db, sources);
sources = add_source_basic(db, sources);
sources = add_source_remote(db, sources);
sources = add_source_orders(db, sources);
sources = add_source_trades(db, sources);
sources = add_source_watchlist(db, sources);
sources = add_source_apps(db, sources);
boolean is_ok = populate_db(db, name, sources, setup_vals);
return is_ok;
}Despite the overall structure indeed being flexible and adaptable enough to potentially support any alternative, the present focus on MySQL can be noted everywhere, even beyond purely functional aspects; and not just MySQL, but also SQL, relational DBs as a whole. Most of the names and approaches are inspired by and, in some cases, fully based on relational concepts. Although this fact should be seen as a minor side outcome, not as a real constraint for supporting other formats like NoSQL. In fact, my original intention was to focus on data sources in general, with DBs merely being an important subtype. And the current implementation isn't really too far from that original intention, despite its major focus on DBs. Thanks to the overall types/keys reliance, it wouldn't be too difficult to generalise the structure of sources/tables and fields/columns to support virtually any other data input like XML, JSON or even HTML.
public static boolean params_are_ok
(
String source_, String what_, String[] cols_,
HashMap<String, String> vals_, String where_, int max_rows_,
String order_, HashMap<String, db_field> cols_info_, boolean is_static_
)
{
String table = db.get_table(source_);
if
(
!(
!strings.is_ok(table) || (what_.equals(db.QUERY_DELETE) && !strings.is_ok(where_)) ||
(
(what_.equals(db.QUERY_INSERT) ||
what_.equals(db.QUERY_UPDATE)) &&
!arrays.is_ok(vals_)
)
|| (what_.equals(db.QUERY_TABLE_CREATE) && !arrays.is_ok(cols_info_))
)
)
{ return true; }
HashMap<String, String> items = new HashMap<String, String>();
items.put("table", table);
if (what_.equals(db.QUERY_SELECT))
{
items.put("cols", strings.to_string(cols_));
items.put("max_rows", strings.to_string(max_rows_));
items.put("order", strings.to_string(order_));
}
if
(
what_.equals(db.QUERY_DELETE) ||
what_.equals(db.QUERY_UPDATE) ||
what_.equals(db.QUERY_SELECT)
)
{ items.put("where", strings.to_string(where_)); }
if
(
what_.equals(db.QUERY_INSERT) || what_.equals(db.QUERY_UPDATE)
)
{ items.put("values", strings.to_string(vals_)); }
if (what_.equals(db.QUERY_TABLE_CREATE)) items.put(_keys.INFO, strings.to_string(cols_info_));
String message =
(
"Wrong " + _types.remove_type
(
what_, _types.DB_QUERY
)
.toUpperCase() + " query" + misc.SEPARATOR_CONTENT
);
String temp = strings.to_string(items);
if (strings.is_ok(temp)) message += temp;
db.manage_error(source_, db.ERROR_QUERY, null, null, message, is_static_);
return false;
}accessory._types.CONFIG is the common root for all the DB types and, as such, they are stored and managed via the corresponding _ini_config/config classes. This decision was made at the very beginning, when it wasn't still clear what the types or the DB code were supposed to become. I would do it differently now, however the current setup works pretty well and relevant modifications aren't expected. Actually, it can't even be considered theoretically inconsistent: the basic DB information does represent an essential part which is likely to remain more or less unaltered, precisely the typical config scenario.
public static final String CONFIG_DB_IB_WATCHLIST = "config_db_ib_watchlist";
public static final String CONFIG_DB_IB_WATCHLIST_SOURCE = "config_db_ib_watchlist_source";
public static final String CONFIG_DB_IB_APPS = "config_db_ib_apps";
public static final String CONFIG_DB_IB_APPS_SOURCE = "config_db_ib_apps_source";
public static final String CONFIG_DB_IB_APPS_OLD = "config_db_ib_apps_old";
public static final String CONFIG_DB_IB_APPS_OLD_SOURCE = "config_db_ib_apps_old_source";
public static final String CONFIG_DB_IB_FIELD = "config_db_ib_field";
public static final String CONFIG_DB_IB_FIELD_USER = "config_db_ib_field_user";
public static final String CONFIG_DB_IB_FIELD_SIZE = "config_db_ib_field_size";
public static final String CONFIG_DB_IB_FIELD_TIME = "config_db_ib_field_time";
public static final String CONFIG_DB_IB_FIELD_SYMBOL = "config_db_ib_field_symbol";
public static final String CONFIG_DB_IB_FIELD_PRICE = "config_db_ib_field_price";
public static final String CONFIG_DB_IB_FIELD_OPEN = "config_db_ib_field_open";Additionally to the types associated with each DB, there are common DB types which define, for instance, another major concept here: setups. They are meant to store all the basic, operative information for the given DB; all that is required to make a connection, for example. As what happens in various other parts, mainly in the ones which I developed at the very beginning, this implementation is assumed to be comprehensive and adaptable, although I haven't actually used it much because of systematically relying on the default configuration, which is good enough for my use case.
public static final String CONFIG_DB = "config_db";
public static final String CONFIG_DB_NAME = "config_db_name";
public static final String CONFIG_DB_SETUP = "config_db_setup";
public static final String CONFIG_DB_SETUP_MAX_POOL = "config_db_setup_max_pool";
public static final String CONFIG_DB_SETUP_HOST = "config_db_setup_host";
public static final String CONFIG_DB_SETUP_TYPE = "config_db_setup_type";
public static final String CONFIG_DB_SETUP_TYPE_MYSQL = "config_db_setup_type_mysql";
public static final String CONFIG_DB_SETUP_CREDENTIALS = "config_db_setup_credentials"; The type/key duality present everywhere throughout these libraries has a particularly important impact here via source/table and field/column (or "col" as referred in the code). Dealing with the field/column distinction is so important that it explains most of the numerous modifications which I have performed during its quite intense evolution. In fact, this is the main reason for the appearance of the modes being discussed in another section.
The justification for that dual setup is evident: unalterable keys (the types) to be unconstraintly used at runtime without having to worry about external modifications, the sources and fields; versus the modifiable values (anecdotally being called keys within this context) connected to the actual DB, the corresponding tables/columns. This idea is strictly true just theoretically, but not so much in the current implementation. Note that these variables aren't being synchronised with the corresponding values in the real DB. Let's assume, for instance, that source1, associated with "table1", contains field1 ("col1") and field2 ("col2"). source1/field1 will never change, but "table1" and "col1" could become "table11" and "col11", respectively, via accessory.config.update or the more descriptive accessory.db methods change_table_name_queries/change_col_name_queries. Such a modification would not affect the actual DB configuration, where "table1"/"col1" may still exist, although all the queries from that moment on will be including the new values. Thus, a better version for the statement above would be: the types/keys are here modelling the unalterable references which I use when writing code vs. the associated changeable values which are applied to all the DB interactions and which are assumed to reflect the actual ones in the DB.
public static boolean change_db_name_queries
(
String source_, String name_
)
{ return update_db(get_db(source_), name_); }
public static boolean change_table_name_queries(String source_, String name_)
{
String source = check_source(source_);
return
(
(!strings.is_ok(source) || !strings.is_ok(name_)) ? false :
config.update(get_db(source), source, name_)
);
}
public static boolean change_col_name_queries(String source_, String field_, String name_)
{
String source = check_source(source_);
String field = check_field(source, field_);
return
(
(!strings.is_ok(source) || !strings.is_ok(field) || !strings.is_ok(name_)) ?
false : config.update(get_db(source), field, name_)
);
}There is no specific designation for the key associated with the type which intuitively seems more important: the one referring to the DB as a whole. The reason for this apparent oddity is that it isn't really too relevant, not in comparison with sources, the real big deal. The systematic reliance on these types, even when dealing with aspects beyond their own scope (e.g., issues related to the DB configuration like modifying its setup), is what explains the aforementioned situation. This source-intensive support has become so important that it represents by itself a good justification for the discussed DB-related type/key duality, up to the point of compensating for all the drawbacks provoked by such implementation, as will be discussed in the next article.
public static String get_db(String source_)
{
return (String)get_setup_common(get_valid_source(source_), _types.CONFIG_DB);
}
public static String get_current_setup() { return get_setup(get_current_source()); }
public static String get_valid_setup(String source_)
{
String output = get_setup(source_);
return (strings.is_ok(output) ? output : DEFAULT_SETUP);
}
public static String get_setup(String source_)
{
return (String)get_setup_common(get_valid_source(source_), _types.CONFIG_DB_SETUP);
}
public static String get_valid_type(String source_)
{
String output = get_type(get_valid_source(source_));
if (!strings.is_ok(output)) output = _cur_type;
String type = (strings.is_ok(output) ? output : DEFAULT_TYPE);
if (strings.is_ok(type) && !type.equals(_cur_type)) _cur_type = type;
return type;
}accessory._ini_db/accessory_ib._ini_db are startup classes, not DB classes, but they perform an important task for DB management in these libraries: populating all the corresponding global arrays, adding the values and interrelationships to the keys defined by the types in the previous section. That is, the sources/fields are just abstract references which are empty and isolated before a _ini_db.populate_all_dbs method is called.
@SuppressWarnings("unchecked")
protected boolean populate_all_dbs(HashMap<String, Object> dbs_setup_)
{
HashMap<String, Object> setup_vals = (HashMap<String, Object>)arrays.get_new(dbs_setup_);
String db = (String)arrays.get_value(setup_vals, _types.CONFIG_DB);
if (!strings.is_ok(db)) db = accessory.db.DEFAULT_DB;
String name = (String)arrays.get_value(setup_vals, _types.CONFIG_DB_NAME);
if (!strings.is_ok(name)) name = accessory.db.DEFAULT_DB_NAME;
HashMap<String, Object[]> sources = new HashMap<String, Object[]>();
sources = add_source_tests(db, sources);
sources = add_source_credentials(db, sources);
sources = add_source_info(db, sources);
sources = add_source_crypto(db, sources);
boolean is_ok = populate_db(db, name, sources, setup_vals);
return is_ok;
}The types are stored in accessory.config like any other CONFIG type, although the main global arrays populated in the _ini_db classes are located in accessory.db. db_common and db_quick also contain some arrays for more specific purposes (e.g., preloading cols to ensure the fastest access when using quick querying methods).
static String[] SETUP_IDS = new String[0];
static String[] SOURCES = new String[0];
static String[] FIELDS = new String[0];
static String INSTANCE = null;
static String _cur_source = strings.DEFAULT;
static String _cur_type = DEFAULT_TYPE;
static String _last_query = null;
private static int[] SOURCES0 = new int[0];
private static db_field[] FIELDS0 = new db_field[0];
private static Object[][] SETUPS = new Object[0][0];
private static boolean[] DEFAULTS = new boolean[0];private void populate_all_internal
(
HashMap<String, Object> dbs_setup_, String[] sources_to_ignore_
)
{
if (sources_to_ignore_ != null) SOURCES_TO_IGNORE = arrays_quick.get_new(sources_to_ignore_);
perform_first_actions();
String error = strings.DEFAULT;
if (populate_all_dbs(dbs_setup_)) perform_actions_after_population();
else error = parent_ini.ERROR_DBS;
SOURCES_TO_IGNORE = null;
_populated = true;
if (error.equals(strings.DEFAULT)) return;
_ini.manage_error(error);
}
private void perform_first_actions()
{
db.INSTANCE = _keys.get_key(_types.WHAT_INSTANCE);
db.SETUP_IDS = new String[]
{
_types.CONFIG_DB, _types.CONFIG_DB_SETUP, _types.CONFIG_DB_SETUP_TYPE, db.INSTANCE
};
}
private void perform_actions_after_population()
{
db_common.populate_is_quick_ini();
db_quick.populate_cols_ini();
db_quick.populate_quicker_ini();
}This whole part has a relevant impact on memory usage, speed and even startup times, aspects which will be analysed in detail in the second article about DB management. The main idea to know now is that these global arrays store essential information and, consequently, they can't be removed. This is why I have been working on their optimisation for some weeks already and will continue to do so. Low-memory consumption and quick access to these collections are vital to guarantee the best performance for these libraries, in general and in their important DB-related actions.
private static int get_field_i(String source_, String field_, boolean is_ini_)
{
int output = arrays.WRONG_I;
if (!strings.is_ok(field_)) return output;
int[] min_max = get_source_min_max(source_, is_ini_);
if (min_max == null) return output;
for (int i2 = min_max[0]; i2 <= min_max[1]; i2++)
{
if (field_.equals(FIELDS[i2])) return i2;
}
return output;
}
private static int[] get_source_min_max(String source_, boolean is_ini_)
{
int source_i = get_source_i(source_, is_ini_);
return
(
source_i > arrays.WRONG_I ? new int[]
{
SOURCES0[source_i],
(
(source_i < db.SOURCES0.length - 1 ?
SOURCES0[source_i + 1] : FIELDS_TOT) - 1
)
}
: null
);
}
private static int get_source_i(String source_, boolean is_ini_)
{
String source = (is_ini_ ? source_ : check_source(source_));
return (strings.is_ok(source) ? arrays_quick.get_i(SOURCES, source) : arrays.WRONG_I);
}As already explained, MySQL (and/or accessory.db.CONFIG_DB_SETUP_TYPE_MYSQL) is the only DB type currently supported. The whole DB development has been fully focused on MySQL until now and I have no plans to support additional types. However, most of the code is technology-agnostic, built such that potential extensions could be added as easily as possible. All the specific code (e.g., the one dealing with MySQL or SQL functionalities) is stored in their own classes, what is expected to also happen with any future technology. There are some caveats, but the DB modes need to be understood first.
private static HashMap<String, Object> get_data_type_static(String data_type_)
{
HashMap<String, Object> output = new HashMap<String, Object>();
String data_type = data.check_type(data_type_);
if (!strings.is_ok(data_type)) return output;
String type = null;
if (data.is_boolean(data_type)) type = TINYINT;
else if (data_type.equals(data.STRING)) type = VARCHAR;
else if (data_type.equals(data.STRING_BIG)) type = TEXT;
else if (data_type.equals(data.TIMESTAMP)) type = TIMESTAMP;
else if (data_type.equals(data.TINYINT)) type = TINYINT;
else if (data_type.equals(data.INT)) type = INT;
else if (data_type.equals(data.LONG)) type = BIGINT;
else if (data_type.equals(data.DECIMAL)) type = DECIMAL;
else return output;
output.put(_keys.TYPE, type);
output.put(_keys.MAX, get_max_size_static(data_type_));
return output;
}
private static long get_max_size_static(String data_type_)
{
long max = 0;
String data_type = data.check_type(data_type_);
if (!strings.is_ok(data_type)) return max;
if (data.is_boolean(data_type)) max = 1;
else if (data_type.equals(data.TIMESTAMP)) max = DEFAULT_SIZE_TIMESTAMP;
else if (data_type.equals(data.DECIMAL)) max = 64;
else if (data_type.equals(data.TINYINT)) max = 3;
else if (data_type.equals(data.INT)) max = numbers.MAX_DIGITS_INT;
else if (data_type.equals(data.LONG)) max = numbers.MAX_DIGITS_LONG;
else if (data_type.equals(data.STRING)) max = (long)get_max_value_static(data_type);
else if (data_type.equals(data.STRING_BIG)) max = (long)get_max_value_static(data_type);
return max;
}
private static double get_max_value_static(String data_type_)
{
double max = 0;
String data_type = data.check_type(data_type_);
if (!strings.is_ok(data_type)) return max;
if (data.is_boolean(data_type)) max = 1;
else if (data_type.equals(data.TIMESTAMP)) max = DEFAULT_SIZE_TIMESTAMP;
else if (data_type.equals(data.DECIMAL)) max = Math.pow(10, 64);
else if (data_type.equals(data.TINYINT)) max = 127;
else if (data_type.equals(data.INT)) max = numbers.MAX_INT;
else if (data_type.equals(data.LONG)) max = numbers.MAX_LONG;
else if (data_type.equals(data.STRING)) max = 255;
else if (data_type.equals(data.STRING_BIG)) max = 65535;
return max;
}static parent_db get_valid_instance(String source_)
{
String source = check_source(source_);
parent_db instance = get_instance((strings.is_ok(source) ? source : get_current_source()));
return (instance != null ? instance : get_instance_ini(get_valid_type(source)));
}
static String get_select_count_col(String source_)
{
return get_valid_instance(source_).get_select_count_col();
}I use the expression "DB modes" to describe an unclearly-delimited, unplanned reality, which just happened during this development, perhaps the most noticeable outcome from the evolution of my main intentions: firstly, excessively focused on code friendliness and, lately, relying on more efficient and practical approaches. Due to its complexity and importance, this variation has especially impacted the DB-related code, by ultimately provoking the appearance of the various modes.
public static String get_variable(String input_)
{
return get_variable(get_current_source(), input_);
}
public static String get_variable(String source_, String input_)
{
return get_valid_instance(source_).get_variable(input_);
}
public static String get_value(String input_)
{
return get_value(get_current_source(), input_);
}
public static String get_value(String source_, String input_)
{
return get_valid_instance(source_).get_value(input_);
}
public static String get_quote_variable()
{
return get_quote_variable(get_current_source());
}
public static String get_quote_variable(String source_)
{
return get_valid_instance(source_).get_quote_variable();
}
public static String get_quote_value()
{
return get_quote_value(get_current_source());
}
public static String get_quote_value(String source_)
{
return get_valid_instance(source_).get_quote_value();
}
public static String get_keyword_where()
{
return get_keyword_where(get_current_source());
}
public static String get_keyword_where(String source_)
{
return get_valid_instance(source_).get_keyword_where();
}
public static String get_keyword_order()
{
return get_keyword_order(get_current_source());
}
public static String get_keyword_order(String source_)
{
return get_valid_instance(source_).get_keyword_order();
}
public static String get_keyword_max_rows()
{
return get_keyword_max_rows(get_current_source());
}
public static String get_keyword_max_rows(String source_)
{
return get_valid_instance(source_).get_keyword_max_rows();
}accessory.db is the main DB class and also an important part of accessory_java since the very beginning. This is where the first, default mode is stored. Meaning all the original non-static code and, specifically, the querying methods where multiple conversions (e.g., from/to fields/cols) and checks (e.g., validity of source or fields) are performed. The other modes also rely on some of these non-static functionalities, despite their slow and safety-prone nature, although only when dealing with aspects where performance isn't a concern.
public static ArrayList<HashMap<String, String>> select
(
String source_, String[] fields_, String where_cols_, int max_rows_, String order_cols_
)
{ return db_queries.select(source_, fields_, where_cols_, max_rows_, order_cols_); }
//Use this method carefully! No data checks or field/col conversions are performed.
public static ArrayList<HashMap<String, String>> select_quick
(
String source_, String[] cols_, String where_cols_, int max_rows_, String order_cols_
)
{ return db_queries.select_quick(source_, cols_, where_cols_, max_rows_, order_cols_); }
public static int select_count(String source_) { return select_count(source_, DEFAULT_WHERE); }
public static int select_count(String source_, String where_cols_)
{
return db_queries.select_count(source_, where_cols_);
}
public static <x> void insert_update
(
String source_, HashMap<String, x> vals_raw_, db_where[] where_
)
{ insert_update(source_, vals_raw_, db_where.to_string(where_)); }When the DB code back then was proven to be too slow for my expectations, I created the second mode by adding the is_quick_ parameter to the original querying methods. The improvement consisted in taking in/returning cols rather than fields by implicitly avoiding all the required conversions. By bringing these ideas a bit further, I came up with the quicker mode: a fully static implementation skipping most of the checks and requiring a specific class for each supported DB type (just accessory.db_quicker_mysql for the time being). Since finishing the first versions, I have been performing many changes to facilitate the management of the new three-part reality, like creating accessory.db_quick. The quick/is_quick label has gradually become descriptive of these two last modes, by considering the quicker alternative a more evolved version, the one to use if supported (what happens every time as far as there is a working quicker implementation for MySQL, the only available DB type). For months already, I have been using the quicker mode almost exclusively.
public static ArrayList<HashMap<String, String>> select
(
String source_, String[] cols_, String where_cols_,
int max_rows_, String order_cols_
)
{ return db_quicker.select(TYPE, source_, cols_, where_cols_, max_rows_, order_cols_); }
public static int select_count(String source_, String where_cols_)
{
return db_quicker.select_count(TYPE, source_, where_cols_);
}
public static void insert_update
(
String source_, String any_col_,
HashMap<String, String> vals_, String where_cols_
)
{ db_quicker.insert_update(TYPE, source_, any_col_, vals_, where_cols_); }public static String get_string
(
String source_, String field_col_, String where_,
String wrong_, boolean is_field_, boolean is_quick_
)
{
String output =
(
is_quick_ ? db_quick.select_one_string
(
source_, get_field_quick_col
(
source_, field_col_, is_field_, is_quick_
),
where_, db.DEFAULT_ORDER
)
: db.select_one_string(source_, field_col_, where_, db.DEFAULT_ORDER));
return (!db.WRONG_STRING.equals(output) ? output : wrong_);
}
public static int get_int
(
String source_, String field_col_, String where_, int wrong_
)
{
return get_int
(
source_, field_col_, where_, wrong_,
DEFAULT_IS_FIELD, is_quick(source_)
);
}
public static int get_int
(
String source_, String field_col_, String where_,
int wrong_, boolean is_field_, boolean is_quick_
)
{
int output =
(
is_quick_ ? db_quick.select_one_int
(
source_, get_field_quick_col
(
source_, field_col_, is_field_, true
),
where_, db.DEFAULT_ORDER
)
: db.select_one_int(source_, field_col_, where_, db.DEFAULT_ORDER)
);
return (output != db.WRONG_INT ? output : wrong_);
}I am working now on a new implementation which is likely to become very important for these libraries, similarly to what happened with the quicker mode. It is currently stored in the new accessory.db_cache class and could easily become a further DB mode. Either way, all the DB-related code will probably continue being improved during the upcoming weeks or months, to make sure that this curious-but-worthy reality will be as friendly and efficient as possible.
public static String get_placeholder(int id_)
{
return (START + PLACEHOLDER_KEY + Integer.toString(id_) + END);
}
public static int add
(
String source_, String query_, String db_type_,
boolean return_data_, boolean is_quick_
)
{
int output = WRONG_ID;
if (!strings.are_ok(new String[] { source_, query_, db_type_ })) return output;
String query = get_query_add(source_, query_);
if (!strings.is_ok(query)) return output;
_queries = arrays_quick.add(_queries, query);
_sources = arrays_quick.add(_sources, source_);
_types = arrays_quick.add(_types, db_type_);
_returns = arrays_quick.add(_returns, return_data_);
_are_quick = arrays_quick.add(_are_quick, is_quick_);
return _queries.length - 1;
}
public static ArrayList<HashMap<String, String>> execute
(
int id_, String[] cols_, HashMap<String, HashMap<Integer, String>> changing_vals_
)
{
ArrayList<HashMap<String, String>> output = null;
if (!id_is_ok(id_)) return output;
String source = _sources[id_];
String query = get_query_execute(source, _queries[id_], changing_vals_);
String type = _types[id_];
if (!strings.are_ok(new String[] { query, source, type })) return output;
boolean return_data = _returns[id_];
boolean is_quick = _are_quick[id_];
return
(
is_quick ? db_quick.execute_query
(
type, source, query, return_data, cols_
)
: db.execute_query(type, source, query, return_data, cols_)
);
}The main DB class overall, accessory.db, is also the main class for the original non-static mode and, as such, includes multiple non-static references. Actually, most of its public methods are relying on non-static resources at some point. accessory.db_queries is a non-public, instrumental class whose resources are used almost exclusively from accessory.db. It was precisely built for that purpose, developed as part of the original DB implementation, in parallel to accessory.db. Another static, internal class is accessory.db_sql, which is meant to improve the internal structure of the non-static classes associated with the corresponding DB types, a code which will be deeply analysed in the next article.
public static void __create_table_like
(
String table_name_, String source_like_, boolean drop_it_
)
{
__lock();
String source = db.check_source_error(source_like_);
if (!strings.is_ok(source))
{
__unlock();
return;
}
if (!strings.is_ok(table_name_))
{
db.manage_error(source, "Wrong table name");
__unlock();
return;
}
String cur_source = db._cur_source;
db._cur_source = source;
parent_db instance = db.get_valid_instance(source);
create_table_like_internal(table_name_, source, instance, drop_it_);
db._cur_source = cur_source;
__unlock();
}
public static void __backup_table(String source_, String backup_name_)
{
__lock();
String source = db.check_source_error(source_);
if (!strings.is_ok(source))
{
__unlock();
return;
}
String table_source = db.get_table(source);
String table_backup =
(
(strings.is_ok(backup_name_) ?
backup_name_ : (table_source + "_backup"))
);
String cur_source = db._cur_source;
db._cur_source = source;
parent_db instance = db.get_valid_instance(source);
create_table_like_internal(table_backup, source, instance, true);
instance.backup_table(table_source, table_backup);
db._cur_source = cur_source;
__unlock();
}The second, quick mode was initially included in accessory.db/accessory.db_queries. After the development of the quicker mode and the multiple subsequent changes to ease their usage and coordination, I created accessory.db_quick as the main place for the new common quick mode, understood as quicker when possible (i.e., always). By definition, the third quicker mode requires a specific implementation for each DB type and, consequently, accessory.db_quicker_mysql was a relevant part of this subdevelopment since the start. accessory.db_quicker and accessory.db_static appeared later, as internal classes helping build a more coherent and adaptable structure.
public static boolean change_db_name_queries(String source_, String name_)
{
boolean output = db.change_db_name_queries(source_, name_);
if (output) update_conn_info(source_);
return output;
}
public static boolean change_table_name_queries(String source_, String name_)
{
boolean output = db.change_table_name_queries(source_, name_);
if (output) update_conn_info(source_);
return output;
}
public static boolean change_col_name_queries(String source_, String field_, String name_)
{
boolean output = db.change_col_name_queries(source_, field_, name_);
if (output) update_conn_info(source_);
return output;
}
public static String get_col(String source_, String field_)
{
int i = db.get_field_i(source_, field_);
return (i > arrays.WRONG_I ? _cols[i] : db.get_col(source_, field_));
}By following a more or less informal convention in these libraries, a new class is created for each relevant source/table (e.g., accessory.db_credentials or db_ib.market). All of them share the same basic structure like global constants for source and fields, which are the main references used when dealing with them. That is, db_ib.orders.SOURCE is used everywhere instead of accessory_ib._types.CONFIG_DB_IB_ORDERS_SOURCE. For evident reasons, most of my work with these specific classes has been happening in ib, specifically in the db_ib package. This is where the need for db_ib.common was firstly perceived and its implementation peculiarities grew, a code which was later generalised and added to accessory.db_common. All these are to-the-point resources, a more pragmatic alternative to the abstract and comprehensive code in the main DB classes, the methods which are actively used in ib, for instance.
public static String get_string
(
String source_, String field_col_, String where_, String wrong_
)
{
return get_string
(
source_, field_col_, where_, wrong_, DEFAULT_IS_FIELD, is_quick(source_)
);
}
public static String get_string
(
String source_, String field_col_, String where_,
String wrong_, boolean is_field_, boolean is_quick_
)
{
String output =
(
is_quick_ ? db_quick.select_one_string
(
source_, get_field_quick_col
(
source_, field_col_, is_field_, is_quick_
),
where_, db.DEFAULT_ORDER
)
: db.select_one_string(source_, field_col_, where_, db.DEFAULT_ORDER)
);
return (!db.WRONG_STRING.equals(output) ? output : wrong_);
}
public static int get_int
(
String source_, String field_col_, String where_, int wrong_
)
{
return get_int
(
source_, field_col_, where_, wrong_, DEFAULT_IS_FIELD, is_quick(source_)
);
}
public static int get_int
(
String source_, String field_col_, String where_,
int wrong_, boolean is_field_, boolean is_quick_
)
{
int output =
(
is_quick_ ? db_quick.select_one_int
(
source_, get_field_quick_col
(
source_, field_col_, is_field_, true
),
where_, db.DEFAULT_ORDER
)
: db.select_one_int(source_, field_col_, where_, db.DEFAULT_ORDER)
);
return (output != db.WRONG_INT ? output : wrong_);
}public static String get_status(int request_)
{
return get_status_type(common.get_string(SOURCE, STATUS, get_where_request(request_)));
}
public static String get_status2(int request_)
{
return get_status2_type(common.get_string(SOURCE, STATUS2, get_where_request(request_)));
}
public static String get_type_order(int request_)
{
return get_order_type(common.get_string(SOURCE, TYPE_ORDER, get_where_request(request_)));
}
public static String get_error(int request_)
{
return common.get_string(SOURCE, ERROR, get_where_request(request_));
}
public static int get_order_id(int request_)
{
return get_order_id(request_, true);
}
public static int get_order_id(int request_, boolean is_main_)
{
return
(
db_common.get_int(SOURCE, (is_main_ ? ORDER_ID_MAIN : ORDER_ID_SEC),
get_where_request(request_), ib.common.WRONG_ORDER_ID
);
}
public static int get_request(String symbol_)
{
String where = db_common.join_wheres
(
common.get_where_symbol(SOURCE, symbol_), get_where_active()
);
return
(
db_common.get_count(SOURCE, where) == 1 ? db_common.get_int
(
SOURCE, REQUEST, where, ib.common.WRONG_REQUEST
)
: ib.common.WRONG_REQUEST
);
}
public static int get_request(int order_id_main_)
{
return db_common.get_int
(
SOURCE, REQUEST, get_where_order_id(order_id_main_), ib.common.WRONG_REQUEST
);
}
public static String get_symbol(int request_)
{
return common.get_string(SOURCE, SYMBOL, get_where_request(request_));
}
public static String get_symbol_order_id(int order_id_main_)
{
return common.get_string(SOURCE, SYMBOL, get_where_order_id(order_id_main_));
}One of the defining characteristics of the original mode is precisely its non-static essence and the corresponding resources are hence some of the first samples of these classes. Namely, the accessory.parent_db and all the classes inheriting from it, a condition which only accessory.db_mysql meets at the moment. All this will be discussed in more detail in the upcoming article with the second part.
public abstract class parent_db
{
public abstract ArrayList<HashMap<String, String>> execute_query
(
String source_, String query_
);
public abstract String sanitise_string(String input_);
public abstract ArrayList<HashMap<String, String>> execute
(
String source_, String type_, String[] cols_, HashMap<String, String> vals_,
String where_, int max_rows_, String order_, HashMap<String, db_field> cols_info_
);
public abstract HashMap<String, Object> get_data_type(String data_type_);
public abstract long get_default_size(String data_type_);
public abstract long get_max_size(String data_type_);
public abstract double get_max_value(String data_type_);
public abstract String get_value(String input_);
public abstract String get_variable(String input_);
public abstract String get_quote_value();
public abstract String get_quote_variable();
public abstract String get_keyword_where();
public abstract String get_keyword_order();
public abstract String get_keyword_max_rows();
public abstract String get_select_count_col();
public abstract boolean table_exists(String table_name_);
public abstract void drop_table(String table_name_);
public abstract void create_table(String table_name_, HashMap<String, db_field> cols_);
public abstract void create_table_like(String table_name_, String table_like_name_);
public abstract void backup_table(String table_source_, String table_backup_);
public abstract void backup_db_to_file(String any_source_);
public abstract void restore_db_from_file(String any_source_);
public abstract String get_db_backup_path(String any_source_);
public abstract String get_db_restore_path(String any_source_);
protected abstract Connection connect_internal(String source_, Properties properties);
private boolean _is_ok = false;
private String _query_type = strings.DEFAULT;
public boolean is_ok() { return _is_ok; }
public void is_ok(boolean is_ok_) { _is_ok = is_ok_; }
public String get_query_type() { return _query_type; }
public void update_query_type(String query_type_) { _query_type = query_type_; }
public Connection connect(String source_, Properties properties_)
{
_is_ok = false;
Connection output = connect_internal(source_, properties_);
if (output != null) _is_ok = true;
return output;
}
}In a first moment of the development and with my main focus on non-static classes, accessory.parent was expected to become much more important than what it finally did. Something similar can be said about accessory.data, a class originally assumed to be a cornerstone of accessory_java, finally relegated to its current insignificant position, exclusively used for very specific DB-related purposes. However and equivalently to what happens with other outcomes on these lines provoked by the evolution of these libraries, I am quite happy with the final result and, although it would most likely not be repeated if everything were being redone again, important modifications in these parts aren't likely to occur either.
public static <x> boolean complies(x val_, data data_)
{
boolean is_ok = false;
Class<?> type = generic.get_class(val_);
if (type == null || !is_ok(data_)) return is_ok;
Class<?> type2 = data_.get_class();
if (!generic.is_string(type) && !type_complies(type, type2)) return is_ok;
if (generic.are_equal(type2, Boolean.class))
{
if (generic.is_boolean(type)) is_ok = true;
else if (generic.is_number(type))
{
double val = numbers.to_number(val_);
is_ok = (val == 0.0 || val == 1.0);
}
else if (generic.is_string(type))
{
String val2 = (String)val_;
if (strings.is_boolean(val2)) is_ok = true;
else if (strings.is_number(val2))
{
double val3 = strings.to_number_decimal(val2);
is_ok = (val3 == 0.0 || val3 == 1.0);
}
}
}
else
{
double size = 0;
if (generic.is_string(type2)) size = (double)((String)val_).length();
else if (generic.is_number(type2)) size = numbers.to_number(val_);
else return false;
size size2 = data_.get_size();
is_ok = numbers.is_ok(size, size2.get_min(), size2.get_max());
}
return is_ok;
}Usually, when talking about DB non-static classes, I refer to accessory.db_field, accessory.db_where and accessory.db_order. All of them created at the very beginning of the development and inheriting from accessory.parent. accessory.db_field is the most important one and, unlike the other two, its current version seems good enough even with my present expectations, pretty different to the ones back when it was firstly created. This class represents a comprehensive reality where the overrided toString method isn't too important, not when outputing a simplistic string, as it does right now. accessory.db_field is relevant within the global DB-information storage and especially impactful on the memory usage front.
public static long check_size(String source_, String type_, long size_)
{
long size = size_;
HashMap<String, Object> info = db.get_data_type(type_);
if (!arrays.is_ok(info)) return 0;
long max = (long)info.get(_keys.MAX);
if (size <= 0 || size > max)
{
size =
(
strings.is_ok(source_) ?
db.get_default_size(source_, type_) :
db.get_default_size(type_)
);
}
return size;
}
public static boolean default_is_ok(String source_, String type_, long size_, Object default_)
{
if (default_ == null) return true;
long size = check_size(source_, type_, size_);
return (size > 0 ? data.complies(default_, to_data(type_, size, DEFAULT_DECIMALS)) : false);
}
public static db_field adapt(String source_, db_field input_)
{
db_field output = new db_field(input_);
if (!output.is_ok()) return output;
output._size = check_size(source_, input_._type, input_._size);
output._decimals = adapt_decimals(input_._decimals);
return output;
}The other remaining classes are mostly direct outcomes from my attitude at the beginning of the development: too concerned about friendliness, understood in its most safety-prone sense, and too focused on non-static classes. Both of them are kind of useful and unlikely to be removed, but they wouldn't probably be here if I were creating them today. db_where.toString() and db_order.toString() are indeed essential as far as these two classes aren't more than friendly interfaces mostly meant to generate the corresponding specific strings (i.e., where and order statements).
public String toString()
{
String output = strings.DEFAULT;
if (!is_ok(_source, _field_col, _operand, _value, _link, _is_quick)) return output;
String value =
(
_is_quick ? db.adapt_input(_value) : db.adapt_input
(
_temp_source, _temp_field_col, _value
)
);
if (value == null) return output;
String col = db.get_variable
(
_temp_source,
(
_is_quick ? _temp_field_col :
db.get_col(_temp_source, _temp_field_col)
)
);
String operand = null;
boolean is_like = operand_like_is_ok(_temp_operand);
boolean is_not_like = (!is_like && operand_not_like_is_ok(_temp_operand));
if (is_like || is_not_like)
{
operand =
(
" " + (is_like ? operand_like_to_string(_temp_operand) :
operand_not_like_to_string(_temp_operand)) + " "
);
if
(
strings.matches_any
(
_temp_operand, new String[]
{
OPERAND_LIKE_START, OPERAND_LIKE_BOTH,
OPERAND_NOT_LIKE_START, OPERAND_NOT_LIKE_BOTH
},
false
)
)
{ value = "%" + value; }
if
(
strings.matches_any
(
_temp_operand, new String[]
{
OPERAND_LIKE_END, OPERAND_LIKE_BOTH,
OPERAND_NOT_LIKE_END, OPERAND_NOT_LIKE_BOTH
},
false
)
)
{ value += "%"; }
}
else operand = operand_to_string(_temp_operand);
value = (_is_literal ? db.get_value(_temp_source, value) : value);
output = (col + operand + value);
return output;
}public String toString()
{
if
(
!is_ok
(
_source, _field_col_else, _order,
_is_field_col, _is_quick
)
)
{ return strings.DEFAULT; }
String field_col_else = strings.DEFAULT;
if (_is_field_col)
{
field_col_else = db.get_variable
(
_temp_source,
(
_is_quick ? _temp_field_col_else :
db.get_col(_temp_source, _temp_field_col_else)
)
);
}
else field_col_else = _temp_field_col_else;
String output = field_col_else + " " + order_to_string(_order);
return output;
}