B4J Question [ABMaterial] MySQL DB location questions

[Cableguy]

Hi guys and gals,

While witing for AB to release his tuto on how to do a self-contained/self deployable RPI ABM hardware/software solution, I want to start creating my ABM based app...

To do so I plan to use a kind of "Portable" B4J version, dedicated exclusively to this project, in a 1TB HDD encosure. This should be more than enough for development and testing purposes.

My question is, Where should I put/create my DB file and how would the path to it look like, since it would not be a fixed letter drive.

My goal is to be able to do all development in this drive and then just ZIP from there and UNZIP to the definitive deployement machine (typically a RPI 3B booting from HDD)

Sorry if this seems a stupid question, and I must confess that I havent even started the seting up, but wanted to start on a solid base idea.

Thanks

 

[Harris]

DB file

Does this imply Data Base file?

 

[Cableguy]

Does this imply Data Base file?

Yeap, MySQL Database file

 

[Harris]

Ok, I see what you are trying to accomplish - which would make server update easier...

When I do it, Mysql is installed locally with default dir options - don't even recall a directory being an option...
When deployed to server, I use phpmyadim to "export data" locally and "import" on server to update the DB structure.

Wait for the DB guru's to wake up in their respective time zones... There may (hopefully) be a way.

 

[keirS]

Based on what you want to do I would say MySQL could well be the wrong solution for you. For a completely self contained deployment I would use the H2 RDBMS. This is a full DB server written in Java. To integrate the server in B4J is incredibly simple and it takes 5 lines of code.

 #AdditionalJar: h2-1.4.196

Include the H2 DB in your project.

Sub Process_Globals
    Public Const APP_NAME As String = "MYABAPP"
    Dim H2  As SQL

H2DBFILE= "jdbc:h2:file:" &  File.DirData(APP_NAME) & "/MYABDB;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=9090
H2.Initialize("org.h2.Driver",H2DBFILE)

You now have a fully running H2 DB server.

Because H2 is written in Java you can include all your SQL scripts to create the database and tables using the files manager and run them from within the JAR.

H2.ExecNonQuery("RUNSCRIPT FROM 'classpath:/Files/H2.sql'")

 

[Harris]

H2 RDBMS - works with jRDC2 as well? (I suspect YES).

 

[keirS]

H2 RDBMS - works with jRDC2 as well? (I suspect YES).

I have never tried it but it should work.

 

[Harris]

Also, (I forgot to ask) - is it MySQL (sql) syntax compatible (since my app uses this currently)?
Next Reports requires to specify the JDBC driver, wondering if works as well...
I guess a simple test would prove this (by me when time permits).

Thanks

 

[OliverA]

H2 RDBMS - works with jRDC2 as well? (I suspect YES).

Yes. It also has its own pooling. There is also hsqldb. I have a very modified jrdc2 that tests these db’s and the various pooling options out there. From my own testing, SQLite is actually a very viable option, even without pooling.

 

[Cableguy]

Guys, I apreciate the interest this thread has gotten from you, but I really would like just a clear answer to MY question, not that you send me on a different route…

It's like I am asking how to get to Paris and you telling me I should go to London instead!

All I need to know is posted very clearly (I think) in my first post!

 

[OliverA]

You need an embedded database if you want to zip something up. Your original premise needs a portable database. SQLite, h2, or hsqldb are some of the options you have for that. H2 and HSQLDB Support pooling, SQLite does not. In my testing, SQLite achieves impressive results, even without pooling.

 

[Cableguy]

I Don't think I will be needing pooling since the app will have a very limited number of users…

Until now I have only used MySQL… no experience whatsoever with other DB products, so …

 

[Harris]

but I really would like just a clear answer to MY question

Sorry dude, my bad....

You need an embedded database if you want to zip something up

Seems this is your answer.... Perhaps not what you wanted to hear - but neither is "I'm pregnant" said the 16 year old to her 17 year old boy friend...

 

[alwaysbusy]

While witing for AB to release his tuto on how to do a self-contained/self deployable RPI ABM hardware/software solution

I'm not sure what you do expect. There is not much of a tutorial to write. You just run the jar on your raspberry pi with java on it. As for using a MySQL database, that is probably another beast. I did setup a portable version of MySQL once but on Windows. Maybe a similar thing can be done on a pi? I just installed a normal MySQL server first, and then adjusted the paths in my.ini to relative ones so I could run it from an USB stick. Looked something like this:

Folders:

/ABMRun/2600 ' here is where the ABM app sits
/ABMRun/jre-9.0.4  ' a java runtime
/ABMRun/mysql-5.7.23-winx64  ' a normal mysql setup, with a changed my.ini (see further)

my.ini (make sure all the folders like data, logs and tmp do exist):

[mysqld]
basedir = "/ABMRun/mysql-5.7.23-winx64"
datadir = "/ABMRun/mysql-5.7.23-winx64/data"
tmpdir = "/ABMRun/mysql-5.7.23-winx64/tmp"
plugin_dir = "/ABMRun/mysql-5.7.23-winx64/lib/plugin"

port = 3306
server_id = 1
max_connections = 30
sql_mode = ''

explicit_defaults_for_timestamp = 1

innodb_status_output = 1
innodb_status_output_locks = 1

character_set_server = utf8
collation_server = utf8_general_ci

general_log = 1
general_log_file = "/ABMRun/mysql-5.7.23-winx64/logs/general_query_all.log"

log_error = "/ABMRun/mysql-5.7.23-winx64/logs/mysqld_error.log"
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_syslog = 0
log_timestamps = SYSTEM
long_query_time = 3
slow_query_log = 1
slow_query_log_file = "/ABMRun/mysql-5.7.23-winx64/logs/slow_query.log"

[mysql]
default-character-set = utf8
show-warnings

prompt = "\u@\h [\d] > "

Then I have 2 bat files: one to start the mysql server and one to start the ABM app. Both are in the root of the /ABMRun folder:

startMySQL.bat:

cd mysql-5.7.23-winx64
bin\mysqld --defaults-file=my.ini --console

startApp.bat

cd 2600
..\jre-9.0.4\bin\java -jar 2600.jar

As specific for Pi (without mySQL that is), we just setup one Pi (maybe in your case with a MySQL?). Copy the java runtime and the whole ABM app folder to it. Test if everything works and then we clone the SD card to make setups for other clients.

 

[Cableguy]

Thanks @alwaysbusy, You have actually answered my question…

I'm not sure what you do expect. There is not much of a tutorial to write. You just run the jar on your raspberry pi with java on it. As for using a MySQL database, that is probably another beast. I did setup a portable version of MySQL once but on Windows. Maybe a similar thing can be done on a pi? I just installed a normal MySQL server first, and then adjusted the paths in my.ini to relative ones so I could run it from an USB stick. Looked something like this:

Folders:

/ABMRun/2600 ' here is where the ABM app sits
/ABMRun/jre-9.0.4  ' a java runtime
/ABMRun/mysql-5.7.23-winx64  ' a normal mysql setup, with a changed my.ini (see further)

my.ini (make sure all the folders like data, logs and tmp do exist):

[mysqld]
basedir = "/ABMRun/mysql-5.7.23-winx64"
datadir = "/ABMRun/mysql-5.7.23-winx64/data"
tmpdir = "/ABMRun/mysql-5.7.23-winx64/tmp"
plugin_dir = "/ABMRun/mysql-5.7.23-winx64/lib/plugin"

port = 3306
server_id = 1
max_connections = 30
sql_mode = ''

explicit_defaults_for_timestamp = 1

innodb_status_output = 1
innodb_status_output_locks = 1

character_set_server = utf8
collation_server = utf8_general_ci

general_log = 1
general_log_file = "/ABMRun/mysql-5.7.23-winx64/logs/general_query_all.log"

log_error = "/ABMRun/mysql-5.7.23-winx64/logs/mysqld_error.log"
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_syslog = 0
log_timestamps = SYSTEM
long_query_time = 3
slow_query_log = 1
slow_query_log_file = "/ABMRun/mysql-5.7.23-winx64/logs/slow_query.log"

[mysql]
default-character-set = utf8
show-warnings

prompt = "\u@\h [\d] > "

Then I have 2 bat files: one to start the mysql server and one to start the ABM app. Both are in the root of the /ABMRun folder:

startMySQL.bat:

cd mysql-5.7.23-winx64
bin\mysqld --defaults-file=my.ini --console

startApp.bat

cd 2600
..\jre-9.0.4\bin\java -jar 2600.jar

As specific for Pi (without mySQL that is), we just setup one Pi (maybe in your case with a MySQL?). Copy the java runtime and the whole ABM app folder to it. Test if everything works and then we clone the SD card to make setups for other clients.

now I can raise other questions…

First, as it seems I cannot Simply copy my MySQL DB File from the computer to the Pi, I now understand the point made by
@keirS and @OliverA …
So I may be turning myself to SQL-Lite...

 

[DonManfred]

I cannot Simply copy my MySQL DB File from the computer to the Pi

But you can easily backup your Mysql-Database to a SQL File (mysqldump) and import this in the rPI-MySQL-DB then (mysqlimport or mysql directly).

 

[alwaysbusy]

as it seems I cannot Simply copy my MySQL DB File from the computer to the Pi

But you could use an export and import no? Most exports can make .sql files which contain all the tables, indexes, views, etc queries + the data.

 

[Cableguy]

But you can easily backup your Mysql-Database to a SQL File (mysqldump) and import this in the rPI-MySQL-DB then (mysqlimport or mysql directly).

But you could use an export and import no? Most exports can make .sql files which contain all the tables, indexes, views, etc queries + the data.

Hummmm…. Interesting Idea….

Will play with it...

 

[Harris]

When deployed to server, I use phpmyadim to "export data" locally and "import" on server to update the DB structure.

@Cableguy wanted to "zip" the local DB and unzip it to deploy on Pi... Others provided potentiality satisfactory solutions based on minimal explanation of scope of project (number of web users / demand, size of DB, # of tables and relations, etc).

I consider SQLite to be a mobile (phones and tablets) DB system. Works great - and is part of the OS on devices (iOS and Android). From all the posts I have read, using SQLite for your DB on moderate server deployments (VPS or your Pi) is asking for trouble (debug and maintenance).

But you could use an export and import no?

I agree, Using MySQL - seems these are the rules... An extra step - yes, arduous - not really...

 

[OliverA]

From all the posts I have read, using SQLite for your DB on moderate server deployments (VPS or your Pi) is asking for trouble (debug and maintenance)

Where? SQLite (or another embedded DB) would pretty much give him a "portable" solution, where he can easily move the database to a new location (it's just 3 files in WAL mode for SQLite) and may only need to change a line of code (unless the DB is always located at the same relative location to the app or uses a configuration file that contains the pathname, then no code change is needed). Plus, with the meager resource of a Pi3B, you would not have to install another service (the DB server). Now, if you are going to serve a ton of users and run on a huge machine, MySQL may be the way to go (if you need sharding, user accounts, master/slave relationships, etc). Please note my signature message though.