I became J-illiterate when reading your real-file code. ;-) Nevertheless tara support inverted table and jdb is inverted table. and there can have no reason for inefficiency. (untested)
On Tue, 01 Dec 2009, Alex Rufon wrote: > Hi, > > During my free time (we just finished a long weekend here) I worked on an > additional utility for JDB. My plan was to add a backup and restore > functionality by exporting a database to excel through Tara. The task is > essentially done for the export function (just need to work on the import) > when I encountered a curious thing. For some reason, boxing a list of numbers > tends to bring my laptop to its knees as compared to converting the numbers > to string first before boxing them. > > I would appreciate if anybody can try this and give me feedback or opinion on > it. > > NB. ========================================================= > NB. Start of a new J session. Lets initialize a JDB northwind database > load 'data/jdb' > load 'data/jdb/northwind' > db_demo '' > NB. You'll see some text then close your J session > 2!:55 '' > > NB. ========================================================= > NB. At this point, you should have opened a new J session > NB. and connect to the northwind database > load 'data/jdb' > [server=: Open_jdb_ jpath '~temp' > +-+ > |1| > +-+ > [db=: Open__server 'northwind' > +-+ > |2| > +-+ > NB. ========================================================= > NB. At this point, you should have loaded the script at the > NB. end of this email > load 'c:\documents and > settings\arufon\j602-user\projects\piscatory\dbmaintenance.ijs' > (jpath '~temp/northwind.xls') backupJDB2XLS db > > NB. ========================================================= > NB. After this, you should see a bunch of debugging text > NB. displayed which can be easily turned off by commenting > NB. out 'smoutput' commands in the script. > > So whats the problem? Well, the problem is with this code: > NB. --------------------------------------------------------- > NB. NONCE! > NB. if you uncomment the following line and replace the > NB. other with this code, this code will run out of memory > NB. temp=. box every each mask from data > temp=. cutopen each format each mask from data > > If you use my original code which is: > NB. temp=. box every each mask from data > > The operation will grind to a halt when processing the Orders table and will > actually abend with an out of memory. I actually looked into it and J memory > usage peaked at 800MB with a VM of 1.4GB for the process. :) > > Any comments are appreciated. Thanks. > > Oh as soon as I'm done with the restore code ... I'll add this to the wiki. :) > > r/Alex > > NB. ========================================================= > NB. START OF CODE: Beware of linewraps!!! > > NB. ========================================================= > NB. Backup and Restore using Tara/Excel > NB. [email protected] 2009 12 1 18 40 59.014000000000003 > NB. > NB. This script uses Tara/Excel to allow exporting and importing > NB. of JDB database and will be using the following concept > NB. 1. Each spreadsheet is one JDB table > NB. 2. The first row is always the field name > NB. 3. The second row is the field type > NB. 4. All rows after that are data > NB. ========================================================= > require 'primitives' > require 'data/jdb' > require 'tables/tara' > > NB. ========================================================= > NB.*backupJDB2XLS (d) backup's a JDB to an MS-Excel file > NB. > NB. x is: Excel file name > NB. y is: <db locale>[;table names] > NB. > NB. Note that the user must pass a valid excel file name as a > NB. left argument and at the minimum the database local. An > NB. optional parameter of a boxed list of tablename to export can > NB. be passed. The function will use the sequence specified > NB. in exporting. > backupJDB2XLS=: dyad define > NB. Get the passed parameters > outputfile=. x > 'database tablelist'=. 2 take y > database=. box database > NB. --------------------------------------------------------- > NB. First thing we do is check if we have a list of tables > if. 0 equal tally tablelist do. > NB. Since the user did not pass a list of tables, lets use the one > NB. defined in the database > tablelist=. Tables__database > end. > > NB. --------------------------------------------------------- > NB. Lets process each of the tables > NB. I'm using an explicit loop here so that I won't have to > NB. muck up the workspace with helper verbs > exportdata=. 0 2 shape ace > for_table. tablelist do. > table=. open table > smoutput 'Processing ', table, '...' > NB. --------------------------------------------------------- > NB. We work on the header information first by building the > NB. datatype information. Please note the format of the > NB. result of ReadCols > NB. ReadCols__Database 'Worlds' > NB. > +---------+---------------------------------------------------------------+ > NB. |table |+------+------+------+------+------+------+ > | > NB. | ||Worlds|Worlds|Worlds|Worlds|Worlds|Worlds| > | > NB. | |+------+------+------+------+------+------+ > | > NB. > +---------+---------------------------------------------------------------+ > NB. |column > |+-------+-----------+---------+----------+---------+----------+| > NB. | > ||WorldID|Description|CreatedBy|CreateDate|UpdatedBy|UpdateDate|| > NB. | > |+-------+-----------+---------+----------+---------+----------+| > NB. > +---------+---------------------------------------------------------------+ > NB. |type |+-------+-------+-------+-----+-------+-----+ > | > NB. | ||varchar|varchar|varchar|float|varchar|float| > | > NB. | |+-------+-------+-------+-----+-------+-----+ > | > NB. > +---------+---------------------------------------------------------------+ > NB. |unique |1 0 0 0 0 0 > | > NB. > +---------+---------------------------------------------------------------+ > NB. |parent |+++++++ > | > NB. | |||||||| > | > NB. | |+++++++ > | > NB. > +---------+---------------------------------------------------------------+ > NB. |parentkey|+++++++ > | > NB. | |||||||| > | > NB. | |+++++++ > | > NB. > +---------+---------------------------------------------------------------+ > tempSchema=. ReadCols__database table > > NB. --------------------------------------------------------- > NB. We parse the datatype first > tempType=. (box 2,1) pick tempSchema > NB. We have to replace the datatype with the parent name for non empty items > tempParent=. (box 4,1) pick tempSchema > mask=. indices tally every tempParent > tempParent=. mask from tempParent > tempType=. (tempParent) mask amend tempType > NB. We apply the unique marker to each appropriate fields > mask=. indices (box 3,1) pick tempSchema > tempUnique=. mask from (box ';') append passive each tempType > tempType=. (tempUnique) mask amend tempType > > NB. --------------------------------------------------------- > NB. We now setup the header information which is the field > NB. names atop the field types > tempHeader=. ((box 1,1) pick tempSchema) laminate tempType > > NB. --------------------------------------------------------- > NB. Get all data for current table data removing the column names > data=. ravel drop rank 1 Read__db 'from ', table > NB. We find out which columns are not boxed and box them > mask=. indices not (box 'boxed') equal datatype each data > NB. --------------------------------------------------------- > NB. NONCE! > NB. if you uncomment the following line and replace the > NB. succeeding one with this code, this script will run out > NB. of memory > NB. temp=. box every each mask from data > temp=. cutopen each format each mask from data > data=. transpose open temp mask amend data > NB. This is the part where we put in the header info > data=. tempHeader append data > data=. table link box data > smoutput data > NB. --------------------------------------------------------- > NB. Save the data > exportdata=. exportdata append data > end. > > NB. --------------------------------------------------------- > NB. Now we save the data to an excel file > smoutput 'Saving to ', jpath outputfile > exportdata writexlsheets jpath outputfile > ) > > NB. END OF CODE > NB. ========================================================= -- regards, ==================================================== GPG key 1024D/4434BAB3 2008-08-24 gpg --keyserver subkeys.pgp.net --recv-keys 4434BAB3 ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
