Search This Blog

Friday, January 31, 2014

The Black Toolkit 1.2 is released

The Black Toolkit 1.2 is released is released containing new improvements and bugfixes, specially on the new SQL Client.
  • Fixed some bugs.
  • Supported new HTML5 events!
  • SQL Client has new improvements.

Friday, January 24, 2014

Making Heavy Metal SQL Client recognize your DB through ODBC.

The Black Toolkit is made to be extensible, that means you can install XML files to add more functionality to the software.

To make SQL Client to work with another DB you need to make an XML for it, and place it in the sql\ installation dir of The Black Toolkit. There´s one file for DB:


<ondetect>Heavy Metal will detect the XML file to use if the DB it recognizes the query in ondetect tag. Must be a query just this DB recognizes</ondetect>

<ongettables>An SQL Query statement for getting the tables of the DB. You can return more than one column, such as database,schema,table_name (PostgreSQL) or database,table_name (MySQL) </ongettables>

<maptypes>
   Types for SQL-to-java and java-to-SQL mapping made for the tool. Examples:

    <map type="TEXT" javatype="String"/> 
    <map types="LONG"  javatype="long" />

</maptypes>

<ongetfields>An SQL Query for getting the fields for a table. Heavy Metal will expand %1% and %2%... by the db/schema/table names returned by ongettables. The query must return the column name and type as in maptypes.</ongetfields>

<ongetpkfields>An SQL Query for getting the Primary key field names for a table. Heavy Metal will expand %1% and %2% ... by the schema/table names returned by ongettables.</ongetpkfields>

<ongetfkfields>An SQL Query for getting the imported fields for a column: Heavy metal will expand %1% and %2%... by the schema/table names returned by ongettables, and the %3% (or %4%) by the column name.
</ongetfkfields>

<sequencecmd>An SQL Query for access the sequence or generator of a table. This is for the SQL importer.</sequencecmd>

<onselect>An SQL Query for making a SELECT statement of a table</onselect>

<object name="ObjName" global="true" query="..." >
   This will declare an database object. You can declare object as much as you like.
    name=Name of the object (Like Indexes, Views, Functions...).
    global="false" if it depends of a table (like an Index), "true" if does not (like a Function).
    query=SQL for query these objects. Must return a full list of details of the objects in the database.
 

    <action name="Action1" helpurl="...">
     SQL for the user can make actions for the object. Like CREATE, ALTER, etc. You can declare action    as much you like. Heavy Metal will expand %1% by the db.table name and %3%, %4%,... for the query return (for non global objects). And %1%,... by the query return (for global objects).
     </action>
</object>



Monday, January 13, 2014

Importing data with foreign data wrappers on PostgreSQL

Foreign data wrappers is a new feature of PostgreSQL 9.1. It serves to read data from external data sources. The data sources can be...

Oh! http://wiki.postgresql.org/wiki/Foreign_data_wrappers

The list is extense! but will need to compile the extension from source, in a Linux server is easy. In Windows you ´ll need MinGW.

There´s 2 FDW which comes with PostgreSQL 9.3 for Windows: the file_fdw and postgres_fdw.



create extension file_fdw;
create server files foreign data wrapper file_fdw;

create foreign table foo_csv (
field1 varchar(13),
field2 varchar(50),
...

) server files options(format 'csv', encoding 'windows-1252', header 'true', delimiter ';', quote '"');



For Postgres_fdw here a example:

create extension postgres_fdw;

create server pgserver foreign data wrapper postgres_fdw options( host 'myhost', port '5432', dbname 'mydb');

create user mapping for myuser server pgserver options(user 'postgres', password '##@%¨$&¨'¨);

create foreign table my_foreign_table (
  id int,
  datet timestamp,
  ...
) server pgserver;

 

For copying data to a real table you can give a simple command:
create table foo as (select * from foo_csv where field2='E467');