This program uploads files into a Content Management System running on a relational database. This can be Generic or a Proprietary like Tera, Prestige, DewarView or Sypress system running on MySql, Oracle, Sybase, SqlServer, Mimer or ODBC-accessed database. On startup, it gets its default parameter file : FORMAT or the parameter file stated in the DF header field. The latter is usually WIRE for wire traffic, VWIRE, ZWIRE, BOX for mailbox, FORM for data formats traffic etc.. The parameter files are in tables/db for generic and Tera and tables/ses for the 3 Atex systems This Program then scans its input directory and mangles the files. Header fields are stuffed into the database. The actual data contents are left in a Queue prescribed by the contents of the EQ header field. The EQ field is normally added to the file at the ipwheel-tables/sys/USERS stage. Sometimes funny chrs such as Space or Slash are required which will of course give weirdo results to other programs downstream from ipwheel and upstream from this one. So a dot, '.', is used as an escape chr. Dot is followed by an UPPERCASE chr or a '@' then octal 040 is subtracted from it. Hence : .@ = SPC .N or .. = . .O = / .J = " etc If the queue name is NOT in the EQ field, it can be replaced with any other using the 'outque' parameter. eg: outque:TOP/\SU/nextLevel/\EQ For a file with an SU of 'AP' and an EQ of 'BIZ' will be put in the queue : /fip/spool/top/ap/nextlevel/biz or with a -o /wires input switch (see below) /wires/top/ap/nextlevel/biz Note that the whole queuename is forced lowercase. As the text has already been cleaned up by IPXCHG, this program can add Fixed and header information at the top and bottom of the file using the 'before:' and 'after' parameters. Incoming files are checked for FIP header fields : DF: Format table file default: FORMAT DM: send a unix mail msg to these Unix logons default: none Files of the same name in a different queue are normally NOT overwritten - the filename is altered depending on the 'dup:' keyword. If 'dup:inc' then the filename is incremented : if the name is 8 chrs long, the last chr is incremented; if less than 8 chrs, an 'a' is suffixed to the name. This is the default. If 'dup:$' then the filename is changed to a CCM-like extention of $01 etc. It uses environment variables : FIP_SES_defEQ default queue default: junk FIP_SES_defSN default filename default: new.000 FIP_SES_LINE default line length for \$L def: 64 The Translation parameter file has the syntax : note for most parameters - passwords, table and column names - case is sometimes important so keep uppercase upper dbhost:(name of server) or dbname:(name of server) dbname:MimDewar dbdatabase:(name of database) dbdatabase:GNW Use this ONLY for Sybase or MsSQLSVR or MySql where we need to chg database after logging on dbuser: (User logon) dbuser:DEWAR dbpwd: (user password) dbpwd:dribbleOG dbchrset: (Chrset name - mysql only) dbchrset:latin1 dbtbl: (table name) dbtbl:EDMASTER Normally only one table can be inserted from the contents of one file. However for multiple inserts in Generic, each table can be specified (up to dbtbl99; dbtbl is the same as dbtbl1) : dbtbl:HEADER dbtbl2:HEADER dbtbl3:SUBCATEGORY dbtbl4:TEXT Either allow-update-fiphdr: (\FipHdr) Test this fipseq for 'update' or 'delete' and update or delete the entry eg allow-update-fiphdr:\JU This turns 'insert' lines (see below) into updates (or delete) There MUST be at least one column set to 'key:' and the data MUST contain a non-blank value or you will get an error! Or update: (full SQL string in FipSeq) (eoln) eg. update:UPDATE EDMASTER set UNO = '\NU' WHERE AUTHOR = '\NA' and FILENAME = '\NX' Note that AUTHOR and FILENAME are normally the key Only one 'update' line per parameter file is allowed. Any 'insert' lines are ignored. Or a number of 'insert' lines - each to fill a single column of the table : insert: (DataBase column/fieldname plus attributes to fill in ) (eoln) Syntax : insert:(name) type:(char|int|long|date|date+NUMBER|text) size:(length to pad out to - for char only) maxsize:(max length - for char only) printable: default:(defaultstring) nulldefault: data:(FipSeq string) fiphdr:yes/no case:(upper|lower) split:(max length of field) key: cmd: multiple-no: id:feed or id:cat (Tera only) update: (yes/no) - Use double quotes to embed spaces and tabs if necessary - There are several fields which MUST be filled in - those for the primary key for example. The 'insert' subkeywords in more detail : -- TYPE This can be CHAR, INT, FILENAME, TEXT, DATE or DATE+(number) Normally these will all be CHAR except the dates and a single FILENAME FILENAME is used to describe the filename to be left on disk. DATE+(number) can be used for the PURGEDATE : insert:PURGEDATE type:date+10 will create a Date that is 10 days in front of the current System Date TEXT is used only for generic traffic and is the data of the file and is only available for those databases which allow BLOB or TEXT data. -- SIZE Fixed Length of the field - if longer, trim; if shorter, pad with spaces. -- MAXSIZE Maximum length of the field - if longer, trim; if shorter, leave as is. -- PRINTABLE Chrs must only be normal printable ascii : space, 0-9, A-Z, a-z and normal punctuation -- DEFAULT If there is no data for this field, use this default strng (in FipSeq) -- NULLDEFAULT not used in this version -- DATA FipSeq string to insert (restricted to 99 chrs long) -- CASE Force the contents upper or lower case -- SPLIT If a field has a size of over 254 - LEAD is a good example - it is normally split across LEAD, LEAD1, LEAD2 etc. Use 'split:254' to specify the size of each (sub)record. -- KEY One field should be flagged with an extra field 'key:'. When a record is to be inserted but it has a duplicate key, this field is incremented. This field is normally also flagged type:FILENAME. ** NOTE that for Duplicate Keys to be correctly handled, the (one) field you want to be modified MUST be labelled 'key:' -- ID For Tera only. id:cat or id:feed are valid -- UPDATE (yes/no) Allow updates to this field ? This is ONLY used if 'allow-updates-fiphdr' is non blank. It defaults to YES -- FIPHDR (yes/no) For TEXT fields, add the fiphdr or not - default:no -- CMD The string in the 'data:' subkeyword is actual an SQL command NOT pure data. eg : insert:EDNO cmd: data:"(Select MAX (EDNO) + 1 from STORIES) or for Oracle dates using 'to_date' insert:DATETIME cmd: data:"to_date (\047\HD\HM\HY\HH\HN\HB\047, \047DDMMYYYYHH24MISS\047)" -- MULTIPLE-NO: Where you want to insert multiple rows from a single file, with different data for each row. you can This is ONLY available for type Generic. eg: ; these have no 'multiple-no' so are present for all inserts insert:XTYPE type:char data:wire.\SU insert:XNAME type:char data:\SN insert:XDATE type:char data:"\HD-\HM-\HY \HH:\HN:\HB" ; first record is Priority - only output row is XP has data test-for-multiple:1 \XP insert:SUBTYPE type:char data:Priority multiple-no:1 insert:XVALUE type:char data:\XP multiple-no:1 ; second record is Headline/Keyword test-for-multiple:2 \XK insert:SUBTYPE type:char data:Headline multiple-no:2 insert:XVALUE type:char data:\XK multiple-no:2 ; data is signified as type:multidata ; third record is Headline/Keyword ; text type is the actual data insert:SUBTYPE type:char data:Text multiple-no:3 insert:XVALUE type:text multiple-no:3 Other keywords are : ; comment name: (fipseq for filename) (end of line) format: (pc, mac, unix, raw) (eoln) ext: (extention to the filename) (eoln) number: (dec, oct or hex) (eoln) before: (fipseq) (eoln) after: (fipseq) (eoln) filebefore: (filename) (eoln) fileafter: (filename) (eoln) dup: ($ or inc) (eoln) supercede: (eoln) takes: (Field Name:NewName) (eoln) size: (size of filename) (eoln) forcesize: (force the size of filename) (eoln) abstract: (size of abstract) (eoln) outque: (Fip Sequence to replace EQ) (eoln) newEN: (New Hdr field to replace EN) (eoln) newEP: (New Hdr field to replace EP) (eoln) newES: For Tera id:Feed, the ES field if NOT FipHdr ES newEC: For Tera id:Cat, the EC field if NOT FipHdr EC newEI: (New Hdr field to replace EI) (eoln) newEH: (New Hdr field to replace EH - Prestige only - subfolder) (eoln) newEF: (New Hdr field to replace EF) (eoln) newER: (New Hdr field to replace ER - pkg Raw filename) (eoln) replace-hash: Chr to replace the hash in the ER field default is octal 201 owner: (new owner/logon for these files) (eoln) script: (name of script plus any optional fipseq) (eoln) respath:(path for resource fork) (eoln) restype:(type of resource fork) (eoln) resorig:(original template res fork) (eoln) resfile: (type of file) (eoln) archive: (name of archive in log/data) (eoln) forcename: (upper/lower/nochg) (eoln) forceque: (upper/lower/nochg) (eoln) log: (log message) (eoln) hdrchgchr: Exchange this chr from all Fip Hdr fields BEFORE including the header field in Data to be inserted into Database fields. Syntax: hdrchgchr: (original chr) (replaced chr) Note there are no spaces/tabs between the two chrs. If no replacement is specified, a Space is used. hdrchgchr:\140\047 hdrchgchr:Aa All 256 chrs can be stripped. hdrzap: zap all chrs between FROM to TO in the Data to be inserted into Database fields hdrzap:<:> Delete all <P>, <\HTML> etc Only one dirzap may be specified. zap-fiphdr-field: (one or more comma-separated FipHdr fields) Zap these FipHdr fields before processing zap-fiphdr-field:JI,JW Use this where you may be using FipHdr fields in Options etc and want to make doubly sure there are no existing fields. chkexists: (Full Path/file or queuename) (eoln) chktimeout: (timeout in seconds) (eoln) uno-select: sypress-select: SQL string for the Sypress Sequence Number change uno-update: sypress-update: SQL string for the Sypress Sequence Number change uno-ignore: sypress-ignore: number which is used as a return code from uno-update which means no further action is required - default is -999 prestige-default-queue: Default queue for Prestige - normally 'spike' prestige-default-group: Default group for Prestige - normally 'wire' prestige-space: A single non-space character to replace the '^' to signify a space in a Prestige queue or group name. test-for-multiple: Test a FipHdr field and only insert this row if data exists in the FipHdr field multiple-get-key: SQL to generate a unique key for multiple inserts. multiple-get-key:select NEWS_SEQ.NEWSVAL from DUAL; multiple-stop-on-error: Stop and an move the input file to 'woops' on error multiple-ignore-duplicates: Completely ignore duplicate entries default is error multiple-ignore-errors: Completely ignore errors for multiples greater than this number. multiple-id-name name of the field in the Sybase/Sql Svr which is used as the unique Id (default is 'Id'); multiple-file-name name of file which will hold the text if it is required on disk. eg multiple-file-name: /remote-system/testfiles/\SU/\$y\$m\$d/\EI multiple-sp: (stored procedure name) Run a stored procedure instead of an insert. dbtbl:NEWS_HEADER dbtbl2:NEWS_DETAILS dbtbl19:NEWS_COMPANY dbtbl20:NEWS_DETAILS dbtbl21:NEWS_BYLINES dbtbl22:NEWS_COPYRIGHTS dbtbl50:NEWS_EXTRAS dbtbl66:NEWS_TEXT ; Tests ; no test for first - always want ;;test-for-multiple:1 \QS test-for-multiple:2 \B1 test-for-multiple:19 \B2 test-for-multiple:20 \B3 test-for-multiple:21 \B4 test-for-multiple:22 \B5 test-for-multiple:50 \B5 ; any text in $T - if so add to NEWS_TEXT test-for-multiple:66 \$T stored-procedure: (stored procedure name) Run a stored procedure instead of an insert for NON-multiples pkg-no: 1=gatrixx pkg-data1: to pkg-data10: sql-after: Some Sql in FipSeq to process AFTER all the items have been done Normally any entry with a Text/Blob line is done last. This allows triggers, etc to started once everything else has finished. default:none eg sql-after:insert into NEWS_ID (ID) values (\JI) eg sql-after:exec trigger_world \EN log-errors: If any error is flagged by the database, the SQL and result is placed in a file /fip/log/2db_error_(param name) trace-file: (TraceFile in FipSeq) default: none Stuff all Sql used and the results into this Trace file. The folder MUST exist beforehand. eg trace-file:/fip/log/sql/NEWS_UPLOAD.\$d keep-spaces: Normally runs of spaces inside a field are stripped to a single one. Use this to keep all the spaces. default: no escape-backslash: Double any BackSlashes in FipHdr fields. default: no You will nearly always need this for MySql. escape-backslash-data: Double any BackSlashes in data. default: no You will need this for MySql IF the data part is text .. .. but not blobs/binary. word-length: (number) default: 8 Average number of chrs in a word - used for \$w This overrides env FIP_DB_WORD line-length: (number) default: 64 Average number of chrs in a line - used for \$l This overrides env FIP_DB_LINE locale: set the locale to this default is the same as the environment of the shell which started the program eg locale:fr_FR The parameter MUST be a valid locale on that system. max-timeout: (number of seconds elasped time between accesses) default 3600 for one hour After this time, ip2db will disconnect and re-connect to the database. Where fipseq is a sequence of fixed text, unix escape chrs, FIP header fields and Octal chrs). PCHR is normally a '{' and is used for {M0 for roman etc. FORMAT is the that of the filename pc chr set (no funnies especially backslash) unix chr set (no * ! $ spc ) mac chr set (no : etc ) raw - let anything through The default is 'format:pc' so that metacharacters, spaces etc are stripped. NUMBER defaults to octal and is used for the \000 strings. BEFORE and AFTER are strings to add before and after the text (maximum size is around 8k). They are in FipSeq so FipHdr fields etc may be specified too. FILEBEFORE and FILEAFTER are files containing (any amount of data) to add before and after the text. The data is in FipSeq so FipHdr fields etc may be specified too. OWNER allows you to change the owner of output files to this logon. This MUST be in /etc/passwd and you must have the correct authority for this. default: he who started this program. SCRIPT is the name of an optional user script that is started AFTER the output file has been finished. The script must be in the normal path unless you add the pathname of the script. The actual output path and name of the new file are under Fip Hdr field names EP and EN. The line is parsed for each new file. eg: script:wakeup \SU \SN \EP/\EN for a file called f8899 from UPI that was put in queue 'eve/biz' with name upi8899.10 would attempt to start : wakeup UPI f8899 eve/biz/upi8899.10 If you have embedded spaces or other funnies (for unix) remember to quote the line. SIZE and FORCESIZE makes sure the filename is EQUAL TO or LESS than the number of chrs specified. This does NOT include the extention, so if you have an extention of '.t' for a PC filename, make the 'size:8' ie 8 chr filename plus the '.t'. In the case of FORCESIZE, the filename part is ALWAYS the SIZE and is padded with the string in the FORCESIZE parameter: size:24 forcesize:XXXXXXXXXXZZZZZZZZXXX ABSTRACT is the no of characters to save as first line of text - the FipSeq \$T.Normally this is 80 characters but it can be any number up to 2048; TAKES looks at the field name and looks for 'spc', 'Xtakes', '2ndadd' etc. It puts into a new header field, a sort key which is : spc : 0 Xtakes : X where X is the number of takes XYYadd : addX where X is the take number For Mac resource forks, 4 parameters specify : - where a template is to use as the basis - what software is being used - nfshare, ethershare, ushare - the file type - the path for the res fork from the data file eg L respath: ../.rsrc restype:ethershare resfile:TEXT resorig:rsrc_template Note that, except for restype, none of these parameters are converted so case is important ARCHIVE will archive a copy in the log marked with this name. This is the normal fip-style archive where files are reviewable/resendable from the Resend function in 'IP' the user interface. FORCENAME : upper will force the file name Uppercase, lower to lowercase nochange to leave 'as is' The default is lowercase. FORCEQUE : upper will force the queue name Uppercase, lower to lowercase nochange to leave 'as is' The default is lowercase. Only the queuename in the EQ field or the 'outque' part is forced upper/lower; not the main path as defined by the '-o' input switch. newEN/newEP/etc : These header fields are added internally for the new name and path of the output file. However you may already have an EN and/or EP and wish to use them. Use newEN/newEP to specify another 2 letter header code which you are NOT using. LOG: Is a FipSeq string for stuffing a message in the item log, replacing the 'Sending ..' text as in : Tue Jan 24 17:25:03 ip2ses !o : Sending apu f008624.t The first part of the message - up to the ':' before Sending - is fixed. Remember that EP and EN are still valid (see script: above) along with all FipSeq FipHdr fields you may have created with repeat, combie, style, newdate etc. There is also an extra internal FipHdr field - ZN - which contains the number of duplicates found for this filename. CHKEXISTS is a complete pathname of either a standing file or a queue. This is used to check that NFS drives are actually mounted BEFORE writing new files. The program checks for (default) 10 secs before aborting that file and continuing on with the next. Change the timeout period with 'chktimeout:' NOTE THAT PROCESSING BLOCKS FOR THIS FILE AND WILL BE RETRIED AT LEAST ONCE A MINUTE. eg : chkexists:/data1/nfs/helios/.Desktop CHKTIMEOUT is the timeout for chkexists. Default is 10 seconds. This can be '0' for never timeout or any number. TEST-FOR-MULTIPLE (Generic, multiple inserts only) Test a FipHdr field and only insert the row if there is data MULTIPLE-GET-KEY: (generic, multiples only) For Oracle - SQL for getting a key which will be saved in FipHdr EI (unless switched by 'newEI' to another field). Oracle eg : multiple-get-key:select NEWS_SEQ.NEXTVAL from DUAL; For Sybase and SqlServer - use 'uno-select' and 'uno-update' Sybase eg: multiple-get-key:yespls (there must be any old data here) uno-update: update NEWS_ID where ID = ID + 1 uno-select:select ID from NEWS_ID MULTIPLE-STOP-ON-ERROR - Normally multiple inserts continue even if one row cannot be inserted. Use this to Stop on every error. In either case the file is sent to either 'spool/woops' or the 'error-queue' MULTIPLE-SP: (index) (stored procedure name) see STORED-PROCEDURE below - used only for the index multiple. STORED-PROCEDURE: (stored procedure name) This kwd is used to run a stored procedure rather than a straight insert in the SQL. In this case the values are still added after the sp call. eg: stored-procedure:fip_update_or_delete insert:id type:int data:\EI insert:time1st type:int data:\$u insert:action type:char data:\RT would run : fip_update_or_delete 3255, 1044533232, "delete" Where sections of FipHdr fields are required or changes to the output style, use keywords : fixed, partial, combie, optional, repeat, newdate and/or style. (see The SysAdmin manual for more information). They are normally specified : fixed:QZ 1234543 partial:QT ST,3,2,U,<,> combie:QY ep|na,(0000000)a option:QE ep,11,7,s repeat:QK XK,-,3 or repeat:QP PK,,4,#X style:QS XN,%.03d newdate:QT hours+3 "\ZD-\ZM" Note that SES allows several more FipSeqs : \$c is the number of bytes or characters in the file \$l is the number of lines (no of chrs divided by Line length which can be altered by the env variable FIP_SES_LINE) \$t is the first 80 chrs of text - number changedable with 'abstract' keyword \$1 is the first line of text - maximum is 2000 chrs \$2 is the second line of text .. \$9 is the ninth line of text If using the Sypress flag '-Z', note that : - FipHdr field EI holds the EDNO to use for EDNO, EDSTORYID, EDPATH - FipHdr field ES hold the Subque to use for EDPATH (0-9) - extra keywords for the parameter file for SQL to get and update the EDNO 'sypress-select:' and 'sypress-update:' If using the Prestige flag '-P', note that : - No dirstamp and queue - Default data queue is /prestige/share/story on the boot drive - extra keywords for the parameter file for SQL to get and update the 'story-id' 'uno-select:' and 'uno-update:' - FipHdr fields generated internally are : EI - holds the unique 'story_id' to use for each story. EN - Filename to use for the data part - which is based on EI ES - The que-group code made from Hdr fields PQ and PG (and defaults to spike-wire) - Default input spooled queue is spool/2prestige - Default parameter file is tables/ses/PRESTIGE If using the Tera version - incoming FipHdr field EC needs to hold the WireCategory field. - incoming FipHdr field ES needs to hold the WireAgency field. - EN will be the same as EN, do not use EP. - on the insert line use 'id:feed' or id:cat' for the correct lookup - Default input spooled queue is spool/2db - Default parameter file is tables/ses/TERA Input switches (all optional) : -c : Set the DBANSItoOEM and DBOEMtoANSI flags for MSQLsvr -C : Clear the DBANSItoOEM and DBOEMtoANSI flags for MSQLsvr -d : done folder for raw incoming data. def: file deleted after upload. -D : Time between reconnects when Database is down. def: 10 secs. -E : pause after connecting in seconds. default: no wait at all -o : Output path name default: /fip/spool/ses ie the path preceding the EQ field -J : Drive for output path (NT only) default: C This is a single letter -w : Dir Stamp path name default: dirstamp if the parameter does NOT start with a '/' it is assumed under the Output Path name (-o). To stop the DirStamp : -w"" -q : queue to scan default: 2ses -Q : keep quiet if the queue for the output file does not exist or there are two many duplicates. default:shout -H : add a HawkEye copy of each file default: no queue spool/2hawkeye must exist. -l : log all files default: do NOT log -m : file mask - input to umask for file creation. Pls remember this is input as a DECIMAL number while access is normally an octal ie -m 420 = 0644. default: 0 for rw-rw-rw- access -s : logon and off for each file. default: logon on the first file until logged off by the server For MS-Sqlsvr sites, check you are using a version of SQL svr which does not eat all your memory when using this option (SQL 7.0 in particular loses 4k per connection) -S : display the SQL strings and results. default: do NOT log -t : sleep time betwix scans default: 1 sec -u : default owner for ALL files. default: that of ip2ses This may be overridden by the 'owner' parameter. -X : ignore (delete) files in error default: send to woops queue -x : send files that error and DO NOT default: send to woops queue get into the database to this queue -y : use sffsleep to pause (fudges Oracle ignoring signals) default: no -Y : default chrset for mysql default: database default -U : (Generic/UNIX only) add the Uid to the filename when moving to the done folder default: no -z : default format file default: tables/ses/FORMAT -P : This is a Prestige system default:DewarView -Z : This is a Sypress EditIX system default:DewarView -G : This is a Generic system default:DewarView -v : print version number and exit Version Control ;012q19 24dec04 parse name, database, server and password ;a-c 27dec04 added zap-fiphdr-field ;d-h 07mar05 added chrset for mysql and added real_connect for mysql ;i-o 14aug06 added odbc and timing stats and -T for param path and locale ;p-q17 1dec06 added wantfiphdr and multiple-ignore-duplicates ;q1-5 allow-update-fiphdr ;9 uno-ignore added ;10 26apr08 allow up 200 rows in total ;15 added -E ;16-17 bugette in -s ;18 9oct09 used MicroSoft IDENT_CURRENT instead of select @@identity ;011z 29jun00 added oracle packages ;a 01aug00 added sql-after ;b 12sep00 added 201 for HASH in filenames for package ;c 23oct00 cleanup for pkgs ;d 01nov00 added trace. (CHECK write_tracefile where bess/mary) !! ;e 06nov00 added keep-multiple-spaces: ;f 11dec00 no servername is no longer an error in oracle ;g/h 26feb01 PAR reports zombies - none found but WAIT added in case also filebfore was zapping any data! ;j 24may01 started BUT not activated //hostname/ for -o for WINNT ;k/l 04dec01 added EH for Prestige subfolder ;m 01aug02 add -y to use sffsleep for Oracle bug ;n/o 30aug02 bugette for Prestige queues ;p 10feb03 added exec-sp: line ;q 03apr03 added escape-backslash ;r 15apr03 added log-errors ;s 13jun03 added escape-backslash-data ;t 30jul03 env varies FIP_DB_WORD/word-length added ;u 04sep03 added -U add Uid to doneque file (Not WINNT) ;v-w 26nov03 EI - internal UI is now long; long added; comments are now ONLY at start of line ;x 01oct04 speedy ;y-z 10dec04 TDS ;010f 01mar00 added MYSQL bits ;a 04apr00 allow up to 99 tables ;b 14apr00 added multiple-get-key ;c 20apr00 added prestige-space ;d 30apr00 added Sybase Text for Generic (both file and TEXT) ;e/f 02may00 added mysql getkey (copyright) 2024 and previous years FingerPost Ltd.