How to create MySQL MSSQL and PostgreSQL database

[mrcoollion]

I want to create a database and it's tables&fields automatically from livecode script.

As basis information I have the SQLite (development version) database available that shows the information below in SQLiteExpert in the DDL tab http://www.sqlite.org/.
Theoretical this information should be sufficient to enable me to generate a database including its tables and fields.

On the MySQL site (see links below) it states that I am able to create a database with 'CREATE DATABASE Databasename;'
And I also should be able to do the same with 'CREATE TABLE tablename;' and it's fields.

My question is: How can I do this from within livecode script for non SQLite databases and specifically MySQL MSSQL and PostgreSQL databases?

--- Information from SQLiteExpert in the DDL tab. ------------------------
CREATE TABLE [Table_1](
[Field_A] TEXT(6) NOT NULL ON CONFLICT FAIL,
[Field_B] TEXT NOT NULL ON CONFLICT FAIL,
[Field_C] TEXT NOT NULL ON CONFLICT FAIL,
[Field_D] TEXT NOT NULL ON CONFLICT FAIL,
[Field_E] TEXT,
[Field_F] TEXT NOT NULL ON CONFLICT FAIL,
UNIQUE([Field_A] ASC, [Field_B] ASC, [Field_C] ASC) ON CONFLICT FAIL);

CREATE TABLE [Table_2](
[Field_A] TEXT NOT NULL ON CONFLICT FAIL UNIQUE ON CONFLICT FAIL,
[Field_B] TEXT,
[Field_C] TEXT,
[Field_D] TEXT,
[Field_E] TEXT);
---------------------------

Links:
https://dev.mysql.com/doc/refman/5.7/en ... abase.html
https://dev.mysql.com/doc/refman/5.5/en ... table.html

Kind regards,

Paul

[AxWald]

Hi,

you may have a look at HeidiSQL - a database manager that works with the 3 database engines you mentioned.
Besides doing a good & reliable job (I use it as my main db manager, daily & heavily) it shows you the CREATE code for any table, like this:

Code: Select all

CREATE TABLE `PL_Upload` (
	`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`Created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
	`JobID` INT(10) UNSIGNED NOT NULL,
	`JobText` VARCHAR(100) NOT NULL,
	`ArtNum` VARCHAR(10) NOT NULL,
	`ArtNam` VARCHAR(50) NOT NULL,
	`ArtVK` DECIMAL(10,2) UNSIGNED NOT NULL,
	`IsDone` INT(1) UNSIGNED NOT NULL DEFAULT '0',
	`ChangeStamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`ID`),
	UNIQUE INDEX `ArtNum` (`ArtNum`)
)
COMMENT='Temp Table'
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2
;

So you can make a demo table with HeidiSQL, and get a great template for your further work ;-)

Creating the database itself may not be harder at least in MySQL; at a quick glance: though I have the option, I fail with authorization issues. Never built the db itself via code - but it should be possible.

Have fun!

[mrcoollion]

Thx AxWald,

With some experimenting I am now able to generate the tables in a MySQL database with Livecode and am in the phase of building routine for this.

Your suggestion for the HeidiSQL tool i can certainly use to get information on the different syntax of the Databases so thank alot.

When I get it working I will share the basics .....