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. ========================================================= ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
