[Maria-discuss] new table format and CONNECT engine
![](https://secure.gravatar.com/avatar/33fb5c08359d9d82c9358b7775aab4b9.jpg?s=120&d=mm&r=g)
Hi guys, I have a file format that is fixed size, and i want to include at CONNECT engine, anyone could help me? it's a file with 6 columns each one fixed size (1xstring, 1x time64_t, 1x ulong (64bits), 3x double(64bits), each row have 110bytes starting at a fixed position, the file have a header with last update time (time64_t) and a row count (32bits ulong) the file struct is easy but i don't know how to include it with connect engine i just want read access no write thanks :) -- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
![](https://secure.gravatar.com/avatar/06fb7e7f8217ef0e572a8334567b7559.jpg?s=120&d=mm&r=g)
Hi
TABLE_TYPE = BIN or FIX.
READONLY=1
I think that you can skip the header with HEADER=1. But I'm not sure that this will work with these particular table types.
--------------------------------------------
Sab 13/6/15, Roberto Spadim
![](https://secure.gravatar.com/avatar/33fb5c08359d9d82c9358b7775aab4b9.jpg?s=120&d=mm&r=g)
yeap that's the problem, particular data type :/
2015-06-13 18:54 GMT-03:00 Federico Razzoli
Hi
TABLE_TYPE = BIN or FIX. READONLY=1 I think that you can skip the header with HEADER=1. But I'm not sure that this will work with these particular table types.
-------------------------------------------- Sab 13/6/15, Roberto Spadim
ha scritto: Oggetto: [Maria-discuss] new table format and CONNECT engine A: "Maria Discuss"
Data: Sabato 13 giugno 2015, 22:58 Hi guys, I have a file format that is fixed size, and i want to include at CONNECT engine, anyone could help me?
it's a file with 6 columns each one fixed size (1xstring, 1x time64_t, 1x ulong (64bits), 3x double(64bits), each row have 110bytes starting at a fixed position, the file have a header with last update time (time64_t) and a row count (32bits ulong)
the file struct is easy but i don't know how to include it with connect engine
i just want read access no write
thanks :)
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
![](https://secure.gravatar.com/avatar/33fb5c08359d9d82c9358b7775aab4b9.jpg?s=120&d=mm&r=g)
I was trying the HEADER=1 but don`t work, check what i`m trying to do: create table teste( ticker char(68) not null, ts timestamp not null field_format='II', (this should be a time64_t value) bid double not null, ask double not null, last double not null, volume bigint not null, unknown char(2) not null ) engine=CONNECT readonly=1 header=1 table_type=bin offset=432 (this don`t exists) option_list='eof=1' lrecl =110 (record size) FILE_NAME='/home/mysql/data/ticks.dat'
![](https://secure.gravatar.com/avatar/392856b43200ef35060b655747853c7c.jpg?s=120&d=mm&r=g)
Hi,
If it it read only, why don't you just import it with load data infile
periodically, such as every 30 seconds? Load into new table then swap,
just like you would a materialized view. LOAD DATA INFILE handles fixed
width files if you do:
LOAD DATA INFILE ... FIELDS DELIMITED BY '';
If the fixed with LOAD DATA INFILE won't work, then just load the file with
a delimiter that does not exist in the file, so that all lines are treated
as a single value. Use the LOAD DATA ... SET syntax combined with SUBSTR()
to extract the columns. It isn't too difficult to make it work, but it
might require a little trial and error.
Here is a very old blog post with a trick to get "top" like output in the
database without having to run the "top" tool. It uses the LOAD DATA ...
SET technique, so it will give you something to go on:
http://swanhart.livejournal.com/131541.html and
http://swanhart.livejournal.com/131788.html
Regards,
--Justin
On Sat, Jun 13, 2015 at 4:05 PM, Roberto Spadim
I was trying the HEADER=1 but don`t work, check what i`m trying to do:
create table teste( ticker char(68) not null, ts timestamp not null field_format='II', (this should be a time64_t value) bid double not null, ask double not null, last double not null, volume bigint not null, unknown char(2) not null ) engine=CONNECT readonly=1 header=1 table_type=bin offset=432 (this don`t exists) option_list='eof=1' lrecl =110 (record size) FILE_NAME='/home/mysql/data/ticks.dat'
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
![](https://secure.gravatar.com/avatar/392856b43200ef35060b655747853c7c.jpg?s=120&d=mm&r=g)
Hi,
What would really be nice is a CONNECT storage type that could execute a
program and read the output. For security, it would only run executables
owned by the user the database is running as, setuid the user the database
is running as, or executable files could be restricted to those in the data
directory, but it could never follow symlinks. You could have something
like:
create table test (
...
)
engine=CONNECT
readonly=1 -- must be true
header=1
table_type=exec_program
option_list='output=csv'
FILE_NAME="process_flatfile.sh /path/to/flatfile.txt"
This would execute the shell script, which would format the output, and
return the data as CSV, as specified in option_list.
Regards,
On Sat, Jun 13, 2015 at 4:20 PM, Justin Swanhart
Hi,
If it it read only, why don't you just import it with load data infile periodically, such as every 30 seconds? Load into new table then swap, just like you would a materialized view. LOAD DATA INFILE handles fixed width files if you do: LOAD DATA INFILE ... FIELDS DELIMITED BY '';
If the fixed with LOAD DATA INFILE won't work, then just load the file with a delimiter that does not exist in the file, so that all lines are treated as a single value. Use the LOAD DATA ... SET syntax combined with SUBSTR() to extract the columns. It isn't too difficult to make it work, but it might require a little trial and error.
Here is a very old blog post with a trick to get "top" like output in the database without having to run the "top" tool. It uses the LOAD DATA ... SET technique, so it will give you something to go on: http://swanhart.livejournal.com/131541.html and http://swanhart.livejournal.com/131788.html
Regards,
--Justin
On Sat, Jun 13, 2015 at 4:05 PM, Roberto Spadim
wrote: I was trying the HEADER=1 but don`t work, check what i`m trying to do:
create table teste( ticker char(68) not null, ts timestamp not null field_format='II', (this should be a time64_t value) bid double not null, ask double not null, last double not null, volume bigint not null, unknown char(2) not null ) engine=CONNECT readonly=1 header=1 table_type=bin offset=432 (this don`t exists) option_list='eof=1' lrecl =110 (record size) FILE_NAME='/home/mysql/data/ticks.dat'
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
![](https://secure.gravatar.com/avatar/33fb5c08359d9d82c9358b7775aab4b9.jpg?s=120&d=mm&r=g)
the data file
Http://spad.im/mt5/ticks.dat
Em sábado, 13 de junho de 2015, Justin Swanhart
Hi,
What would really be nice is a CONNECT storage type that could execute a program and read the output. For security, it would only run executables owned by the user the database is running as, setuid the user the database is running as, or executable files could be restricted to those in the data directory, but it could never follow symlinks. You could have something like: create table test ( ... ) engine=CONNECT readonly=1 -- must be true header=1 table_type=exec_program option_list='output=csv' FILE_NAME="process_flatfile.sh /path/to/flatfile.txt"
This would execute the shell script, which would format the output, and return the data as CSV, as specified in option_list.
Regards,
On Sat, Jun 13, 2015 at 4:20 PM, Justin Swanhart
javascript:_e(%7B%7D,'cvml','greenlion@gmail.com');> wrote: Hi,
If it it read only, why don't you just import it with load data infile periodically, such as every 30 seconds? Load into new table then swap, just like you would a materialized view. LOAD DATA INFILE handles fixed width files if you do: LOAD DATA INFILE ... FIELDS DELIMITED BY '';
If the fixed with LOAD DATA INFILE won't work, then just load the file with a delimiter that does not exist in the file, so that all lines are treated as a single value. Use the LOAD DATA ... SET syntax combined with SUBSTR() to extract the columns. It isn't too difficult to make it work, but it might require a little trial and error.
Here is a very old blog post with a trick to get "top" like output in the database without having to run the "top" tool. It uses the LOAD DATA ... SET technique, so it will give you something to go on: http://swanhart.livejournal.com/131541.html and http://swanhart.livejournal.com/131788.html
Regards,
--Justin
On Sat, Jun 13, 2015 at 4:05 PM, Roberto Spadim
javascript:_e(%7B%7D,'cvml','roberto@spadim.com.br');> wrote: I was trying the HEADER=1 but don`t work, check what i`m trying to do:
create table teste( ticker char(68) not null, ts timestamp not null field_format='II', (this should be a time64_t value) bid double not null, ask double not null, last double not null, volume bigint not null, unknown char(2) not null ) engine=CONNECT readonly=1 header=1 table_type=bin offset=432 (this don`t exists) option_list='eof=1' lrecl =110 (record size) FILE_NAME='/home/mysql/data/ticks.dat'
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net javascript:_e(%7B%7D,'cvml','maria-discuss@lists.launchpad.net'); Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
![](https://secure.gravatar.com/avatar/392856b43200ef35060b655747853c7c.jpg?s=120&d=mm&r=g)
HI,
Btw, you can do the above scenario with the external script, by making a
shell script that writes to a named pipe. It will block until something
reads from the pipe, which would be the CONNECT engine. Once the pipe
closes (because CONNECT has finished) just loop and write to the pipe again.
--Justin
On Sat, Jun 13, 2015 at 5:03 PM, Justin Swanhart
Hi,
What would really be nice is a CONNECT storage type that could execute a program and read the output. For security, it would only run executables owned by the user the database is running as, setuid the user the database is running as, or executable files could be restricted to those in the data directory, but it could never follow symlinks. You could have something like: create table test ( ... ) engine=CONNECT readonly=1 -- must be true header=1 table_type=exec_program option_list='output=csv' FILE_NAME="process_flatfile.sh /path/to/flatfile.txt"
This would execute the shell script, which would format the output, and return the data as CSV, as specified in option_list.
Regards,
On Sat, Jun 13, 2015 at 4:20 PM, Justin Swanhart
wrote: Hi,
If it it read only, why don't you just import it with load data infile periodically, such as every 30 seconds? Load into new table then swap, just like you would a materialized view. LOAD DATA INFILE handles fixed width files if you do: LOAD DATA INFILE ... FIELDS DELIMITED BY '';
If the fixed with LOAD DATA INFILE won't work, then just load the file with a delimiter that does not exist in the file, so that all lines are treated as a single value. Use the LOAD DATA ... SET syntax combined with SUBSTR() to extract the columns. It isn't too difficult to make it work, but it might require a little trial and error.
Here is a very old blog post with a trick to get "top" like output in the database without having to run the "top" tool. It uses the LOAD DATA ... SET technique, so it will give you something to go on: http://swanhart.livejournal.com/131541.html and http://swanhart.livejournal.com/131788.html
Regards,
--Justin
On Sat, Jun 13, 2015 at 4:05 PM, Roberto Spadim
wrote: I was trying the HEADER=1 but don`t work, check what i`m trying to do:
create table teste( ticker char(68) not null, ts timestamp not null field_format='II', (this should be a time64_t value) bid double not null, ask double not null, last double not null, volume bigint not null, unknown char(2) not null ) engine=CONNECT readonly=1 header=1 table_type=bin offset=432 (this don`t exists) option_list='eof=1' lrecl =110 (record size) FILE_NAME='/home/mysql/data/ticks.dat'
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
![](https://secure.gravatar.com/avatar/33fb5c08359d9d82c9358b7775aab4b9.jpg?s=120&d=mm&r=g)
well the data is binary encoded, i removed the header, only got the timestamp ok, but not double values and 64bits integer :/ files attached [image: Imagem inline 1] create table teste2( ticker BINARY(68) not null, (i think it's a unicode string) ts timestamp not null field_format='II', (should be time64_t) bid double not null field_format='D', (should be ieee 64bits double) ask double not null field_format='D', last double not null field_format='D', volume bigint UNSIGNED not null field_format='II', (should be ulong 64bits) unknown char(2) not null (not important value) ) engine=CONNECT readonly=1 table_type=bin option_list='eof=0' lrecl =110 FILE_NAME='/home/mysql/data/ticks2.dat'
participants (3)
-
Federico Razzoli
-
Justin Swanhart
-
Roberto Spadim