FARMERS WIFE SQL COMMUNICATION
Index:
Rates / money, and booking start and stop time
must be minimum of :
0 > 1 = id
2 > 5 = year
6 > 7 = month
8 > 9 = day of month
10 > 11 = hour
12 > 13 = min
14 > 15 = sec
and then you canhave to put 10 extra numbers there, like a cpu click or whatever
to make sure its really unique.
these should always be 26 characters long
example :
id200212241200001234567890
id-2002-12-24-12-00-00-1234567890
id-yyyy-mm-dd-hh-mm-ss-?cpuclick
time of day in bookings etc..
stored as a float
eg. 6.5 == 06:30
eg. 26.5 == 02:30
eg. 19.75 == 19:45
remember that a one day booking time goes from
06:00 > 06:00 eg. 6.0 > 30.0
and that minutes can be
:00, :15, :30, :45
(.0)(.25)(.50)(.75)
VARCHAR, from 1 > 255
FLOAT
INTEGER
BIT
DATETIME, must be in eg. "2002-07-26 11:52:20" format
if your dbms handles these types and the statements below , wife will work, but "rates / money" have to be verified (see "rates / money" below)
DELETE FROM table_name
CREATE TABLE table_name (some_types)
CREATE UNIQUE INDEX tablename_Ix ON tablename (mycolumn)
mike, do i really need to make these???
INSERT INTO table_name mycolumn VALUES (myvalue)
SELECT * FROM table_name WHERE mycolumn = '0'
Rates / money, and booking start and stop time
in the attempt to support as many dbms's in the future all rates are stored
as floats.
again, i learned that inserting rate into an sql server table goes '12345.67',
but then the same in
access would accept it, but in the value add 1234567.0, thanks!!
access wants a "," instead of a ".", this could be a locale
thing.
wife knows this when populating rates to sql, but you can get this problem too.
also, when retriving rates like (added 24.24) will return 24.239999998, wife
uses round on the decs. so
the correct precission is kept.
fyi: wife keeps precission of 24.42, internaly stored as an integer "2442" as god wanted us to do!
if you do not set a boolean yourself upon adding new entries, wife will get
different values
depending on what dbms you use.
ms sql server 7 is what i would recommend, even that access will work to.
empty bool in sql server 7: "<NULL>"
empty bool in ms access "0"
so wife thinks in an access enviroment that you have added this "0"
and that will override any wife default booleans that are "1"
this is how you get wife to update your stuff done on the sql side
id: VARCHAR(32)
put a unique id here, can be a simple incrementer, it does not really matter,
just make it unique.
status: VARCHAR(3) (should have been integer, but MSACCESS sucks!)
always put "0" here when adding to this table, wife will mod it to
either 1=DONE or -1=ERROR see below error_text.
cmd: VARCHAR(64)
the wife command to execute, see below "sql com table commands"
arg1: VARCHAR(255)
arg2: VARCHAR(255)
arg3: VARCHAR(255)
arg4: VARCHAR(255)
arg5: VARCHAR(255)
these depends on what command is used, see below "sql com table commands"
error_text: VARCHAR(255)
id "status" == -1 then the error message will be shown here
Column value description
1) Projects
com: update tells wife to update from sql
arg1: projects name of table to upgrade from
arg2: id (project id) id = of the project to update/create/delete
arg3: user_id id = of the user performing the update/create/delete, USER MUST
EXIST
example 1: make a new project
sqldb "INSERT INTO projects (id,name) VALUES ('id2002122421000012345',
'My Sql Created Project',)"
sqldb "INSERT INTO wife_com (id,status,cmd,arg1,arg2,arg3) VALUES (1,0,update,projects,id2002122421000012345,id2001080821274915480099)"
as you see you dont need to populate all the columns, like "the_number"
wife will make a auto number for you
if you have not populated the field.
wife will always make sure that all fields are legal and populated as they should.
views will be created with defaults if wife does not find any in the projects_views
when wife gets the update it will look in all sub project tables and find whatever
you have added,
this means that everytime this update is performed wife checks it all, you dont
have to give wife a
update foreach of the entries you add or remove, it will do it for you.
so if you dont add custom fields, it will add the default for you!
tips and tricks:
if you also define "client_contact_id" and leaves all client_* columns
empty wife will auto fill in these for you, including all discount columns
2) Containers
com: update tells wife to update from sql
arg1: containers name of table to upgrade from
arg2: id (container id) id = of the container to update/create/delete
arg3: user_id id = of the user performing the update/create/delete, USER MUST
EXIST
3) Bookings
com: update tells wife to update from sql
arg1: bookings name of table to upgrade from
arg2: id (booking id) id = of the booking to update/create/delete
arg3: user_id id = of the user performing the update/create/delete, USER MUST
EXIST
4) Bookings Links
com: update tells wife to update from sql
arg1: bookings name of table to upgrade from
arg2: id (link id) id = of the link list to update/create/delete
5) Contacts
com: update tells wife to update from sql
arg1: contacts name of table to upgrade from
arg2: id (contact id) id = of the contact to update/create/delete
arg3: user_id id = of the user performing the update/create/delete, USER MUST
EXIST
6) Mediaorders
com: update tells wife to update from sql
arg1: mediaorders name of table to upgrade from
arg2: id (contact id) id = of the contact to update/create/delete
arg3: user_id id = of the user performing the update/create/delete, USER MUST
EXIST
7) Todos
com: update tells wife to update from sql
arg1: objects_users_todo name of table to upgrade from
arg2: id (todo id) id = of the todo to update/create/delete
arg3: user_id id = of the user performing the update/create/delete, USER MUST
EXIST
8) Tapes
com: update tells wife to update from sql
arg1: tapes name of table to upgrade from
arg2: lib_number from library number 0 to 11
arg3: id (tape id) id = of the tape to update/create/delete
arg4: user_id id = of the user performing the update/create/delete, USER MUST
EXIST
9) Invoices
com: update tells wife to update from sql
arg1: invoices name of table to upgrade from
arg2: id (invoice id) id = of the tape to update/create/delete
arg3: user_id id = of the user performing the update/create/delete, USER MUST
EXIST
example of adding a small invoice with 2 objects
sqldb "INSERT INTO invoices (id,name,due) VALUES ('id2003030221000014432','My
Invoice','30')"
sqldb "INSERT INTO invoices_entries (invoice_id,name,ref,quant,sell,add_vat,disc)
VALUES ('id2003030221000014432','My Object','myref','25.32','320.00','1','15.7')"
sqldb "INSERT INTO invoices_entries (invoice_id,name,ref,quant,sell,add_vat,disc)
VALUES ('id2003030221000014432','My Object 2','myref2','10.05','30.42','1','5')"
sqldb "INSERT INTO wife_com (id,status,cmd,arg1,arg2,arg3) VALUES ('1','0','update','invoices','id2003030221000014432','id2001080821274915480099')"
Create column data table
from project
com: command tells wife that it is an build in command to be fired up
arg1: create_column_data_table_from_project the command to execute
arg2: id (project id) id of the project to export
arg3: view filter the view filter to use (Final / Invoice)
arg4: table_name what the name of the new table should be