Conversion of scriptcase 9 from using sqlite to mariadb

[rr1]

If you are with multiple users on your scriptcase dev environment then you know that sqlite can cause delays and even locking. And it is slow in comparison to mariadb.
So here is a way to no longer work with sqlite but instead use mariadb…

Requirements: a CLEAN NEW installation of the 64 bit scriptcase 9
Tools: SQLIteStudio from here: https://sqlitestudio.pl/index.rvt?act=download
Mariadb from here (64 bit): https://downloads.mariadb.org/mariad…_group=windows

Take the one that looks like this:
mariadb-10.2.11-winx64.msi MSI Package Windows x86_64 54.7 MB Checksum
Instructions

I assume your 64 bit scriptcase is already installed (need not be registered yet).
To make things easy get a mysql client from here: https://mariadb.com/kb/en/library/clients-utilities/
or grab heidisql from here: https://mariadb.com/kb/en/library/heidisql/

Stop your ApacheScriptcase9 service in your windows services screen.
Install mariadb.
When using mariadb choose to use InnoDB !! This is for multiuser and thus very handy and fast in this case.

Stop your scriptcase service.

Backup your scriptcase datase from c:\program files\Netmake\v9\wwwroot\scriptcase\devel\conf\scr iptcase
m_scriptcase.db
This database is not empty so it will need to be converted to mysql.
start sqlite studio and open your nm_scriptcase.db with it.

[ATTACH=CONFIG]n81856[/ATTACH]

Now you can export your database to an sql file.

It will look like this:

– File generated with SQLiteStudio v3.1.1 on do jan 11 10:41:06 2018

– Text encoding used: System

PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

– Table: sc_tbapl
CREATE TABLE sc_tbapl (
Cod_Prj VARCHAR (120) NOT NULL,
Versao INT (11) NOT NULL,
Cod_Apl VARCHAR (120) NOT NULL,
friendly_name VARCHAR (255),
Login VARCHAR (32) NOT NULL,
…

You will now need to convert this script, change all column names TEXT to either MEDIUMTEXT of LONGTEXT
In sqlite there is a statement at the beginning
PRAGMA foreign_keys = off;
Change this to:
SET FOREIGN_KEY_CHECKS=0;

Also at the end there is:
PRAGMA foreign_keys = on;
Change it to:
SET FOREIGN_KEY_CHECKS=1;

[rr1]

Step 2: Install mariadb (64 bit preferred of course). Choose your own root user/password.

Start heidisql (or use mysql with he commandline) and connect with your mariadb/mysql and do the commands:
create database scriptcase;
use scriptcase;
Then import your script from above (or copy/paste it in heidisql) and simply do the commands.
That will import your sqlite tables and fields into your mariadb.
Do check that SET FOREIGN_KEY_CHECKS=1 is actually being done.
You should get NO errors but since the process is not that easy I suggest doing it in smaller steps.

[rr1]

step 3: create a user scriptcase in mariadb
In mysql commandline (or use heidisql to do it by gui) do
create user ‘scriptcase’@127.0.0.1 identified by ‘MYPASSWORD’
grant all privileges on scriptcase.* to ‘scriptcase’@‘127.0.0.1’;
flush privileges;

Doing a select host,user,password from user should display your users then.

Step 4:
The data is now imported so now setup your mysql/mariadb use in scriptcase.
Go to c:\Program Files\NetMake\v9\wwwroot\scriptcase and change config_.php in config.php
Then start your scriptcase service again wait a few seconds and go to the config.php in your browser. You should see this:
[ATTACH=CONFIG]n81861[/ATTACH]
Choose MySQL instead of MySQL PDO and enter your scriptcase user and password (you should have made those).

Rename config.php back to config_.php (dont be afraid you can not run config_.php it only works if it is named config.php)

Restart the service again…

Basically your scriptcase now runs on mariadb and should perform better using multiple users.

[aducom]

Of course this works for mysql too. I changed all text fields to longtext fields. Don’t use a replace all statement, there’s a field text_xml that will be changed to longtext_xml causing obvious issues.