Finance App Tables
lazarus / 16-Databases

----------------------------------------------------------------------
pat03uk · 2024-11-09 11:05

I am new to sqlite3 and have a problem with a small finance app which
I'm hoping I can get some help with.

I have three tables (simplified):
   payee (id, name).      eg amazon, bmw etc
   category(id, name)    eg clothing, medicine etc
   transaction(id, payee_id, category_id, name, amount)

If a new transaction is entered (by entering text values for payee and
category) I need to:
   a) check if payee (name) is present, if not enter it into the payee
table,  then get back the payee id and enter that into the transaction
record.
   b) do the same same for the category

Is this a sensible approach?. It seems i would need to do an insert or
ignore query to enter name into the payee table, followed by a select
query to get back the id to enter into the transaction record - or is
there a one step way or simpler way.

----------------------------------------------------------------------
Sieben · 2024-11-09 11:39

The usual way is rather to have the user enter a new payee or category
first before it can be used with the main table (preferably with a
lookup combo). That way you can also provide better checks for
duplicates, otherwise you might end up with eg a bunch of different
Amazon 'brands' like Amazon Co, Amazon Limited or even simple typos
entering the payee table. BTW this has nothing to do with the brand of
database you're using.

----------------------------------------------------------------------
pat03uk · 2024-11-09 11:47

The problem is that input can come from a gif file (a text file of
accounts transactions etc) exported from another finance app as well
as user input.

----------------------------------------------------------------------
pat03uk · 2024-11-09 11:54

I have tried (where p is the payee name)

Insert or Ignore into payee(name) values('+quotedStr(p)+')
which won't return a value, followed by
Select id From payee where name = ' + quotedStr(p)
which will return a value for id

this seems long winded

I have also tried:
Insert or Ignore into payee(name) values('+quotedStr(p)+') RETURNING
id as payeeID
and even:
UPDATE payee set name = '+quotedStr(p)+' WHERE name = '+quotedStr(p)+'
RETURNING id as payeeID
both of which always return the value 1.

----------------------------------------------------------------------
silvercoder70 · 2024-11-09 12:03

I would suggest the following ...

Code: Text  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i for each
transaction do  get payee id given some criteria  if not found then
insert payee    do the above for the category   insert row into the
transaction table

----------------------------------------------------------------------
cdbc · 2024-11-09 12:14

Hi
How about this:
1) when the app starts up, you 'select id, name from payee;' into a
TPayeeList.
2) after that, you 'select id, name from category;' into a
TCategoryList.
3) with a transaction you check the 'Lookup'-lists to find the names
and id's
   you need, if they're not found, you offer the user the ability to
add them to
   the sqlite3 database and when saved - add them to your lookup-
lists...

just my 2 cents worth
Regards Benny

----------------------------------------------------------------------
five · 2024-11-09 12:15

Simply...

Assuming that we have a unique index on the [Name] field

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  INSERT
INTO PAYEE(Name) SELECT  'Payee name' WHERE NOT EXISTS(SELECT 1 FROM
PAYEE WHERE Name = 'Payee name'); INSERT INTO CATEGORY(Name) SELECT
 'Category name' WHERE NOT EXISTS(SELECT 1 FROM CATEGORYWHERE Name =
'Category name'); -- Assuming that the Transaction [ID] Field is
autoincrementINSERT INTO TRANSACTION ( Payee_id, category_id, Name,
Amount )SELECT     (SELECT ID FROM PAYEE WHERE Name = 'Payee name'), 
  (SELECT ID FROM CATEGORY WHERE Name =  'Category name'),     'The
transaction description',     1234.67;

----------------------------------------------------------------------
pat03uk · 2024-11-09 14:46

Thanks everyone for the help - I appreciate it.

silvercoder70

I think this is more or less what i've tried, a problem is that insert
payee doesn't return a value(id), so i then have to follow with a
select payee which does return a value(id)

cdbc

When I started I thought that payees and category could be simple
lists. But then transactions could also be a list, which begs the
question why bother with a database? needs a bit of thought!

five
I tried this but have yet to get it working, for simplicity I tried:
Insert into transaction(payeeID, date) Select ((Select id From payee
Where name = '+quotedStr(payeename)+'),'+quotedStr(date)+')'

not sure if this can or should work, its complicated sqlite! (also
tried replacing the first select with values.

----------------------------------------------------------------------
cdbc · 2024-11-09 15:03

Hi
Don't forget this:Code: Pascal  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i TSQLite3Connection = class(TSQLConnection)   ...  public   
constructor Create(AOwner : TComponent); override;    procedure
GetFieldNames(const TableName : string; List :  TStrings); override; 
 function GetConnectionInfo(InfoType:TConnInfoType): string;
override;    procedure CreateDB; override;    procedure DropDB;
override;    function GetInsertID: int64; // <-- THIS ONE !!! \o/   
// See http://www.sqlite.org/c3ref/create_collation.html for detailed
information    // If eTextRep=0 a default UTF-8 compare function is
used (UTF8CompareCallback)    // Warning: UTF8CompareCallback needs a
wide string manager on Linux such as cwstring    // Warning:
CollationName has to be a UTF-8 string    procedure
CreateCollation(const CollationName: string; eTextRep: integer; Arg:
Pointer=nil; Compare: xCompare=nil);    procedure LoadExtension(const
LibraryFile: string);  Published Easy access to the *Latest inserted
ID*
Regards Benny

----------------------------------------------------------------------
five · 2024-11-09 16:22

Quote from: pat03uk on November 09, 2024, 03:46:07 pm
five
I tried this but have yet to get it working, for simplicity I tried:
Insert into transaction(payeeID, date) Select ((Select id From payee
Where name = '+quotedStr(payeename)+'),'+quotedStr(date)+')'

not sure if this can or should work, its complicated sqlite! (also
tried replacing the first select with values.

That should work for you...
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
Project1; {$mode objfpc}{$H+} uses  {$IFDEF UNIX}  cthreads, 
{$ENDIF}  Classes ,sysutils , strings, Dialogs  { you can add units
after this }; var sSQLCommand : AnsiString;    P1 : string;    P2 :
String; begin   // Set Parameter  and raw sql command  P1 :=
quotedstr('MyPayee');  P2 := quotedstr('2024-11-09');  sSQLCommand :=
'INSERT INTO Transaction( payeeID, date) SELECT (SELECT id FROM payee
WHERE name = @payeename ), @date ; ' ;  // Raw SQL 
writeln(sSQLCommand);  writeln(P1);  writeln(P2);  // Parse parameter 
sSQLCommand := StringReplace( sSQLCommand , '@payeename',
P1,[rfReplaceAll]);  sSQLCommand := StringReplace( sSQLCommand ,
'@date', P2,[rfReplaceAll]);  // Final SQL  writeln(sSQLCommand);   //
Use the final SQL Command in your query object ....   readln;end.

----------------------------------------------------------------------
pat03uk · 2024-11-09 16:59

five

Thanks for that. I am having success with this. The first problem was
using transaction which must be a reserved word. Now i have it working
fine along the lines you suggest with payeeID and accountID but not
categoryID. This must be a simple problem for me to solve.

Insert into transactions(payeeID, accountID) Select(Select id From
payees Where name = "patrick"),(Select id From accounts Where
name="Ford Money") works fine

Pat

----------------------------------------------------------------------
CharlyTango · 2024-11-09 18:11

Before we dig deeper in table design or application design, here's a
tip.

Do not call the table "transaction" because this will lead to
misunderstandings in the future. A "Transaction" in the context of SQL
databases means a set of SQL Commands which are to be done or not.

Do not call the Primary keys "ID" only, use more distinctive
expressions instead. This way some can understand the table design
much more easier and some design software is able to recognize the
connections.

example:

   payee (payee_id, payeename).      eg amazon, bmw etc
   category(category_id, categoryname)    eg clothing, medicine etc
   booking(booking_id, payee_id, category_id, bookingtext, amount)

Whenever possible, do not use self combined Statements, use parameters
instead:

QuoteSQLQuery1.SQL.Text:= 'SELECT booking_id, payee_id from booking
WHERE booking_id = :booking_id';
SQLQuery1.ParamByName('booking_id').AsInteger := StrToInt(Edit1.Text);
or

QuoteSQLQuery1.SQL.Text:= 'INSERT INTO booking( payee_id, category_id,
bookingtext, amount)
VALUES ( :payee_id, :category_id, :bookingtext, :amount)';
SQLQuery1.ParamByName('booking_id').AsInteger := StrToInt(Edit1.Text);
SQLQuery1.ParamByName('category_id').AsInteger :=
StrToInt(Edit2.Text);

SQLQuery1.ParamByName('bookingtext').AsString :=
Editbookingtext.Text);

SQLQuery1.ParamByName('category_id').AsFloat:= etc etc;
SQLQuery1.ExecSQL;

----------------------------------------------------------------------
five · 2024-11-09 21:53

Quote from: pat03uk on November 09, 2024, 05:59:27 pmfive

Thanks for that. I am having success with this. The first problem was
using transaction which must be a reserved word. Now i have it working
fine along the lines you suggest with payeeID and accountID but not
categoryID. This must be a simple problem for me to solve.

Insert into transactions(payeeID, accountID) Select(Select id From
payees Where name = "patrick"),(Select id From accounts Where
name="Ford Money") works fine

Pat

I don't know with SQLITE, but the standard in SQLSERVER use of backet
can overcome your problem.

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  INSERT
INTO [TRANSACTION] ([payeeID], [accountID])  SELECT (SELECT id FROM
[payees] WHERE [name] = "patrick") , (SELECT id FROM [accounts] WHERE
[name]="Ford Money")

----------------------------------------------------------------------
Zvoni · 2024-11-11 07:19

You are aware that above approaches only work, if the column "name" in
payee as well as in category is UNIQUE?? (HINT HINT HINT!!!)
and i'm going to leave out case-sensivity here....

----------------------------------------------------------------------
five · 2024-11-11 12:37

Quote from: Zvoni on November 11, 2024, 08:19:42 amYou are aware that
above approaches only work, if the column "name" in payee as well as
in category is UNIQUE?? (HINT HINT HINT!!!)
and i'm going to leave out case-sensivity here....

Yes for sure, it was mantionned earlier in that thread. Good to recall
it.