Physical Database Documentation
 
Library Physical Database

Commonly Used Tables

Products Salelines Sales User Data Accounts Contactlog Eftposreceipts Eftposreceipts2 Locations Payments Pricemaps Reference Data Statetable System Data R_paymenttypes Salelog Usagetoken Analytics Data Ds_customer_a Ds_customer_b Ds_product_a Ds_sales_a Other Tables Accountadjustments Accountallocations Accountchangeslog Accountshumanrequired Accountsupport Activitylog Advisor_actions Advisor_tests Allocatedrights Apikeys Apikeys_access Apikeys_lock Apikeys_resource_locks Apitxnlog Apitxnlogdata Asndetail Asnheader Asnheader2 Asnitem Attribute Attributedefinitions Authcodelog Autobalancerecon Autobalancereconlog Backgroundlog Barcode_patterns Barcodefailurelog Barcodeinvalid Barcodeoneoff Barcodeoneofflog Barcodepatterns Barcodes Barcodes_ean99 Barcodes_trade Barcodeusedlog Bisho_status Bistx Bookingres Bookingsales Bookingscans Bookingsessions Bulkloadlog Cabinets Callerid Cameradef Carriers Cashdraweractionlog Cashdrawerlog Cautionlist Ceres_productlist Chatheader Chatmessage Companies Configui Contact Contactdetails Contactdetailsmap Costchangesseen Counters Counters_log Createapps Customercardgroups Customercards Customermessages Customers Customersdcid Customersdcidrpt Customersdcids Customersdcidslinks Customerstate Customertracks Customertrackspending Decisiontreeheader Decisiontreelines Demographic Discountfilter Discounts Discountsmanual Docuheader Doculine Documentformats Documentparsematch Documentprocessing Documulti Doesnotexist Drivercashlog Ediauditlog Employees Eventaudit Expectedsales Expensetypes Faults_detail Fdlonline_enablematrix Feectl Fees Floatlog Flow_anchor_raw_string Fmea Fpuicontrol Fpuicontrol2 Franchise_barcodes Franchise_departments Franchise_products Franchise_products_original Futuredatachanges Futuretxns Gateways Geoareas Globalvalues Goodsinheader Goodsinhistory Goodszeropoint Headerreceive Headerreceive_head Heartbeat Hiregroup Httpurllog Idset2 Idsetctl Idsetdata Idsetdata2 Inventorylevels Ldflog Linkedserver Livepayments Livesalelines Livesales Localsaleflags Location_area Location_cab Location_tradingdays Longdatavalues Machinesuser Macro Maintlist Marketingprogram Measurements Media Mediacontrol Mediafiles Mediaresource Mediastorage Membership Membershipdata_all Messageformat Messageq Messageuser Messagingt Modifiergroups Modifierheaders Modifieritems Modifierpriceheader Modifierpriceline Modifierproductgroups Modifieruplifts Modififergroups Multichain_sourcemap Multilane Multilink_all Multiplu Networklane Networklane_environment Networklane_status Networklanecode Networklaneconfig Ordercategories Ordersreceived Ordersreceivedlines Orderstxns Overflow_gnap Overflow_tmpr Perfcounters Pidcoststatus Plicence Poheader Poheader_head Poheaderreceive Poheaderreceive_head Poline Poline_head Poms Posaudit Posendtypes Poshipping Poslat Posrelationships Prefsaleitems Pricebandmap Pricebands Pricebookcurrent Pricebooksource Pricebookspecialsheader Pricebookspecialsline Pricebufferdist Pricechangepending Pricegroups Pricehistory Pricemapsexceptions Priceoptions Pricerevision Priceused Printformats Printing_pending Printingformats Printingrules Procuserlogicline Product_cab_active Product_dispatch Product_dispatch_error Product_packsize Productattribute Productconnections Productconnectionstype Productcontrollog Productkitdef Productkits Productkitsext Productlocspacemap Productlocspacesets Products1 Products2 Products3 Products5 Products_category0 Products_category1 Products_costhistory Products_inventory Products_inventory_changes Products_inventory_point Products_manufacturer Products_overlay Products_properties Productsbom Productsbomheader Productsinst Productslocation Productslocationbis Productsordering Productspacesets Productssale Productsupplier Productsupplierdist Productvariant Profanity Purchasecontrols Purchaseorderprocessing Qoh_pub Qtydiscounts Quickcodeaudit R_colors R_departments R_departments2 R_departments3 R_departments4 R_departments5 R_departments6 R_departments7 R_departments8 R_fashiongroups R_pgm_periods R_returnreasons R_sizes R_styles R_writeoffs Rcvstock Rcvstockline Receiptextn Remotecommands Remotecommandslog Rewardmovements Rewardprogramoptions Rewardprogrampids Rewardprograms Rewardprogramspids Rewardtxns Rewardtxnslog Sale_delivery Salealloc Salechangesaftercomplete Salechar Salecomments2 Saleinfo Salelines_cost Salelines_deleted Saleloadtrace Saleloadtraceline Sales_errors Sales_picking Sales_recall Sales_standingorders Salesdcid Saleservercommands Salesprofilesingle Salestaff Salestatistics Salesunpaid Salevar Salevar2 Samplelist Scriptmap Scripts Seritem Seritemlog Servercommands Settings Settingsactive Settingsdist Settingskv Settingskvdist Settingsmembership Settingspolicy Settingspolicymap Settlecashdetail Settlepaydetail Shelfvisit Shifttrack Ss_pricebuffer Staff Staff_eauth Staff_magid Staff_ownership Staff_rights Staff_stats Stafftablecontrol Staffused Staffused_log Statementhistory Stickies Stock_fifo Stock_lifo Stockcount Stockholdrequests Stockmovements Stockpoints Stockreceipts Stocktakedetail Stocktakedetail2 Stocktakemaster Stocktakesnapshot Stocktakesnapshot_tracked Stocktxnhdr Stocktxnitem Subscriptions Supplier_inventory Suppliermessages Supplierpricebook Supplierpricebookline Suppliers Suppliers2 Suppliersautoorder Svcaccount Svcards Svctxnhistory2 System_status Tabledisthead Tablemgmt Taxrates Telephone_number_plans_override Ticketpricerequired Ticketpriceshowing Timeclock Timeclocklog Timeschedule Tmpr_log Tnotes Topology Tracelogic Trackeditem Trackeditemobservation Traffic_counts Transit_inventory Transit_inventory_lines Transit_request Transit_request_lines Triggers Tushlog Typesuser U_cw_cartridges U_cw_printers Upstream Usagerecord Userlogicvariable Valuedomainrule Vehicle_makes Vehicle_models Versioncontrol Verticalsecurity Voucher Voucherprogram Webgroup Webhooks Webpages Webpagesrules Worklist Workqueue Workqueuetx Internal Technical Tables B2backup Customers2 Customers3 Dbcomm Emailinrules Firewallrules Keylease Keyleasefreelist Localdb_datpq Localdb_datpq_inbound Localdb_lanetx_queue Localdb_membership_replicate Localdb_pinb_cache Localdb_receivequeue Localdb_replicationstatus Localdb_salestechnicalaudit Localdb_sendqueue Localdb_sendqueuedata Localdb_settings Localdb_values_write Localdb_version Loginkeys Products4 Replicationcontrol Salememory Sales2 Serverprocessing Serverprocessingdates Tubtlocal Wipheader Wipline Deprecated Tables Auditsystem Pictures Retired Tables Biscontrol Cjl Eftpos_txns Linkproductspictures Pomprodlink Experimental Tables Keytranslate Posscriptpage Posscripttrigger

Reserved Fields

Some fields in Fieldpine have common purpose over all tables where they are present. In general these fields are used for internal purposes and are do not contain business level information. If working directly at the database level you should ignore and never change these fields.

DeleteDtu

Datatype: Date/Time. Date the record was deleted for replication purposes in UTC.

This field is used in some tables to assist with record deletion and replicated systems. Applications will not see this field unless they specifically request it.

If this field contains a NULL, the record is valid. If this field contains a date/time UTC, then this record was or will be deleted after this date time. If you are reading the table directly you should include the predicate "where deletedtu is NULL" to exclude deleted records.

When a row is deleted in a distributed environment, the software may chose to insert the current date/time into this field rather than executing a true "delete" at this time. Client databases that are replicating from this database can then receive these deletes as edits using the normal "changed since" logic. They can elect to immediately delete the record, or wait a period of time if they are also replication sources.

  • Request to delete a record made
  • Delete changed to edit, setting RVE to now, and deletedtu to now
  • Normal use of this table includes the predicate "where deletedtu is null" and no longer sees the record
  • Distribution aware applications, see this as a record edit and either delete the row or apply the edit, depending on their needs
  • Periodically a clean up task is run to truely delete the rows where deletedtu is a date/time old enough that all continuous replication will have completed.
  • If a client periodically does a full table alignment, then it can skip the clean up task if it wishes and let the alignment process delete the rows
  • Note, if the deleted rows primary key is re-used, then the deletedtu field is reset to null, meaning the new record is visible again

RVE

Datatype: Double. Contains a last approved edit date/time for replication purposes in UTC.

Values in this fields consist of a UTC timestamp in the format YYYYMMDD.HHmmSSCCC This value can exceed the precision of floating point numbers in some instances so typical use should remove the CCC milliseconds portion. To cater for clock differences the value can be clamped and adjusted by higher ranking servers. Leap seconds will not appear in generated RVE values, if an RVE is generated during a leap second the generator pauses until the leap second is over.

The RVE field can be used to detect recent changes in the database. Whenever the record is changed Fieldpine updates the RVE field. So a select of

select * from table where RVE > 20200823.1340
will return any rows updated on or after 23-aug-2020 13:40 UTC.

RVEL

Datatype: Double. Contains edit date/time within the current database for replication purposes in UTC.

Values in this fields consist of a UTC timestamp in the format YYYYMMDD.HHmmSSCCC This value can exceed the precision of floating point numbers in some instances so typical use should remove the CCC milliseconds portion. See details of the RVE field for more information.

RVV

Datatype: String. A list of values indicating the lineage of the record. This value can be used to assist distributed database update conflicts.

The exact contents and handling of this field is not described and has no business level value. Never change or update these field values.

RVS

Datatype: Integer. A bitfield indicating which servers have acknowledged this record. This is a simple method to ensure records are transmitted to all hosts. It is mostly used on log style tables. As each host confirms receipt of this row, the source system sets the bit to say acknowledged.

If a lane generates a log record (LR) it transmits this to each server it uses. It sends with the RVS field of null or zero.

"data": {
  "key": 12345,
  "name": "Joan the Great",
  "rvs": 0
}
As it receives acknowledgement from a server, it updates the row and sets the bit for that server. This does not cause an edit transmit.

A server receiving a record, sets it own bit and stores the record. It then transmits to other servers.

"data": {
  "key": 12345,
  "name": "Joan the Great",
  "rvs": 0
}
Server 1 receives packet, sets RVS bit 1, and stores to disk
"data": {
  "key": 12345,
  "name": "Joan the Great",
  "rvs": 1
}
Generating lane sends the same record to all servers Server 2 receives packet, sets RVS bit 2, and stores to disk
"data": {
  "key": 12345,
  "name": "Joan the Great",
  "rvs": 2
}
Sending lane updates it's RVS to indicate accepted in both servers ( RVS 3 = 1 | 2 )
"data": {
  "key": 12345,
  "name": "Joan the Great",
  "rvs": 3
}
Server 1 transmits to Server 2, including the current RVS
"data": {
  "key": 12345,
  "name": "Joan the Great",
  "rvs": 1
}
Server 2 receives packet, and combines the RVS values, resulting in "3" as value stored
"data": {
  "key": 12345,
  "name": "Joan the Great",
  "rvs": 3
}