SQLApp
SQL application example
SQLApp.h
#ifndef _SQLApp_SQLApp_h
#define _SQLApp_SQLApp_h
#include <SqlCtrl/SqlCtrl.h>
#include <plugin/sqlite3/Sqlite3.h>
using namespace Upp;
#define LAYOUTFILE <SQLApp/SQLApp.lay>
#include <CtrlCore/lay.h>
#define SCHEMADIALECT <plugin/sqlite3/Sqlite3Schema.h>
#define MODEL <SQLApp/SQLApp.sch>
#include "Sql/sch_header.h"
Convert& DateIntConvert();
struct SQLApp : public WithSQLAppLayout<TopWindow> {
WithQueryLayout<TopWindow> query;
void Serialize(Stream& s);
void Query();
void Query(int q);
void SetupQuery();
void ClearQuery();
void NewBook();
void EditBook();
void DeleteBook();
void BookMenu(Bar& bar);
void NewBorrow();
void EditBorrow();
void DeleteBorrow();
void ReturnedToday();
void BorrowMenu(Bar& bar);
void SetupMenu(Bar& bar);
void MainMenu(Bar& bar);
typedef SQLApp CLASSNAME;
SQLApp();
};
#endif
main.cpp
#include "SQLApp.h"
#define SCHEMADIALECT <plugin/sqlite3/Sqlite3Schema.h>
#include "Sql/sch_source.h"
void SQLApp::MainMenu(Bar& bar)
{
bar.Add("Book", THISBACK(BookMenu));
bar.Add("Borrow record", THISBACK(BorrowMenu));
#ifdef _DEBUG
bar.Add("SQL console", callback(SQLCommander));
#endif
}
SQLApp::SQLApp()
{
CtrlLayout(*this, "Window title");
menu.Set(THISBACK(MainMenu));
book.SetTable(BOOK);
book.AddKey(ID);
book.AddColumn(AUTHOR, "Author");
book.AddColumn(TITLE, "Title");
book.SetOrderBy(TITLE, AUTHOR);
book.WhenBar = THISBACK(BookMenu);
book.WhenLeftDouble = THISBACK(EditBook);
borrowed.SetTable(BORROW_RECORD);
borrowed.AddKey(ID);
borrowed.Join(BOOK_ID, book);
borrowed.AddColumn(BORROWED, "Borrowed").SetConvert(DateIntConvert());
borrowed.AddColumn(RETURNED, "Returned").SetConvert(DateIntConvert());
borrowed.AddColumn(PERSON, "Who borrowed");
borrowed.ColumnWidths("75 75 372");
borrowed.SetOrderBy(BORROWED);
borrowed.WhenBar = THISBACK(BorrowMenu);
borrowed.WhenLeftDouble = THISBACK(EditBorrow);
borrowed.GoEndPostQuery();
CtrlLayoutOKCancel(query, "Select");
query.status = 0;
query.borrowed_from.SetConvert(DateIntConvert());
query.borrowed_to.SetConvert(DateIntConvert());
query.clear <<= THISBACK(ClearQuery);
}
#ifdef _DEBUG
#define SCHEMADIALECT <plugin/sqlite3/Sqlite3Schema.h>
#include "Sql/sch_schema.h"
#endif
GUI_APP_MAIN
{
SQL;
Sqlite3Session sqlite3;
if(!sqlite3.Open(ConfigFile("simple.db"))) {
Exclamation("Can't create or open database file\n");
return;
}
SQL = sqlite3;
#ifdef _DEBUG
SqlSchema sch(SQLITE3);
sqlite3.SetTrace();
All_Tables(sch);
if(sch.ScriptChanged(SqlSchema::UPGRADE))
Sqlite3PerformScript(sch.Upgrade());
if(sch.ScriptChanged(SqlSchema::ATTRIBUTES))
Sqlite3PerformScript(sch.Attributes());
if(sch.ScriptChanged(SqlSchema::CONFIG)) {
Sqlite3PerformScript(sch.ConfigDrop());
Sqlite3PerformScript(sch.Config());
}
sch.SaveNormal();
sqlite3.SetTrace();
#endif
SQLApp app;
LoadFromFile(app);
app.Query();
app.Run();
StoreToFile(app);
}
borrow.cpp
#include "SQLApp.h"
struct DateIntConvertCls : Convert {
virtual Value Format(const Value& q) const;
virtual Value Scan(const Value& text) const;
virtual int Filter(int chr) const;
};
Value DateIntConvertCls::Format(const Value& q) const
{
return IsNull(q) ? String() : ::Format(Date(1970, 1, 1) + (int)q);
}
Value DateIntConvertCls::Scan(const Value& text) const
{
String txt = text;
if(IsNull(txt))
return Null;
Date d;
if(StrToDate(d, txt))
return d - Date(1970, 1, 1);
return ErrorValue("Invalid date!");
}
int DateIntConvertCls::Filter(int chr) const
{
return CharFilterDate(chr);
}
Convert& DateIntConvert()
{
return Single<DateIntConvertCls>();
}
class BorrowDlg : public WithBorrowLayout<TopWindow> {
public:
SqlCtrls ctrls;
typedef BorrowDlg CLASSNAME;
BorrowDlg();
};
BorrowDlg::BorrowDlg()
{
CtrlLayoutOKCancel(*this, "Book borrow record");
ctrls
(BORROWED, borrowed)
(RETURNED, returned)
(PERSON, person)
;
borrowed.SetConvert(DateIntConvert());
borrowed <<= GetSysDate() - Date(1970, 1, 1);
returned.SetConvert(DateIntConvert());
}
void SQLApp::NewBorrow()
{
if(!book.IsCursor())
return;
SQL * Select(ID).From(BORROW_RECORD)
.Where(BOOK_ID == book.GetKey() && IsNull(RETURNED));
if(SQL.Fetch() && !PromptYesNo("The book is still borrowed!&Continue?")) {
borrowed.FindSetCursor(SQL[0]);
return;
}
BorrowDlg dlg;
if(dlg.Run() != IDOK)
return;
SQL * dlg.ctrls.Insert(BORROW_RECORD)(BOOK_ID, book.GetKey());
borrowed.Query();
borrowed.FindSetCursor(SQL.GetInsertedId());
}
void SQLApp::EditBorrow()
{
if(!book.IsCursor())
return;
int q = borrowed.GetKey();
BorrowDlg dlg;
SQL * Select(dlg.ctrls).From(BORROW_RECORD).Where(ID == q);
if(!dlg.ctrls.Fetch(SQL))
return;
if(dlg.Run() != IDOK)
return;
SQL * dlg.ctrls.Update(BORROW_RECORD).Where(ID == q);
borrowed.Query();
borrowed.FindSetCursor(q);
}
void SQLApp::DeleteBorrow()
{
if(book.IsCursor() && PromptYesNo("Delete the borrow record?")) {
SQL * Delete(BORROW_RECORD).Where(ID == book.GetKey());
book.Query();
}
}
void SQLApp::ReturnedToday()
{
if(borrowed.IsCursor() && IsNull(borrowed.Get(RETURNED)) && PromptYesNo("Mark as returned today?")) {
int q = borrowed.GetKey();
SQL * SqlUpdate(BORROW_RECORD)
(RETURNED, GetSysDate() - Date(1970, 1, 1))
.Where(ID == q && IsNull(RETURNED));
borrowed.Query();
borrowed.FindSetCursor(q);
}
}
void SQLApp::BorrowMenu(Bar& bar)
{
bar.Add(book.IsCursor(), "New borrow record..", THISBACK(NewBorrow))
.Key(K_ENTER);
bar.Add(borrowed.IsCursor(), "Edit borrow record..", THISBACK(EditBorrow));
bar.Add(borrowed.IsCursor(), "Delete borrow record", THISBACK(DeleteBorrow))
.Key(K_DELETE);
bar.Add(borrowed.IsCursor() && IsNull(borrowed.Get(RETURNED)), "Returned today",
THISBACK(ReturnedToday))
.Key(K_SPACE);
}
book.cpp
#include "SQLApp.h"
struct BookDlg : public WithBookLayout<TopWindow> {
SqlCtrls ctrls;
BookDlg() {
CtrlLayoutOKCancel(*this, "Book");
ctrls
(AUTHOR, author)
(TITLE, title)
;
}
};
void SQLApp::NewBook()
{
BookDlg dlg;
if(dlg.Run() != IDOK)
return;
SQL * dlg.ctrls.Insert(BOOK);
Query(SQL.GetInsertedId());
}
void SQLApp::EditBook()
{
if(!book.IsCursor())
return;
BookDlg dlg;
int q = book.GetKey();
SQL * Select(dlg.ctrls).From(BOOK).Where(ID == q);
if(!dlg.ctrls.Fetch(SQL))
return;
if(dlg.Run() != IDOK)
return;
SQL * dlg.ctrls.Update(BOOK).Where(ID == q);
Query(q);
}
void SQLApp::DeleteBook()
{
if(book.IsCursor() && PromptYesNo("Delete the book?")) {
SQL * Delete(BORROW_RECORD).Where(BOOK_ID == book.GetKey());
SQL * Delete(BOOK).Where(ID == book.GetKey());
Query();
}
}
void SQLApp::BookMenu(Bar& bar)
{
bar.Add("New book..", THISBACK(NewBook));
bar.Add(book.IsCursor(), "Edit book..", THISBACK(EditBook));
bar.Add(book.IsCursor(), "Delete book..", THISBACK(DeleteBook));
bar.Separator();
bar.Add("Query..", THISBACK(SetupQuery))
.Key(K_CTRL_F);
}
query.cpp
#include "SQLApp.h"
void SQLApp::Query()
{
SqlBool where;
SqlSet borrowed = Select(BOOK_ID).From(BORROW_RECORD).Where(IsNull(RETURNED));
if(query.status == 1)
where = ID != borrowed;
if(query.status == 2)
where = ID == borrowed;
SqlBool bdate;
if(!IsNull(query.borrowed_from))
bdate = BORROWED >= ~query.borrowed_from;
if(!IsNull(query.borrowed_to))
bdate = bdate && BORROWED <= ~query.borrowed_to;
if(!bdate.IsEmpty())
where = where && ID == Select(BOOK_ID).From(BORROW_RECORD).Where(bdate);
book.Query(where);
}
void SQLApp::ClearQuery()
{
query.status = 0;
query.borrowed_from <<= query.borrowed_to <<= Null;
}
void SQLApp::SetupQuery()
{
if(query.Execute() == IDOK)
Query();
}
void SQLApp::Query(int q)
{
book.Query();
if(book.FindSetCursor(q))
return;
ClearQuery();
book.Query();
book.FindSetCursor(q);
}
void SQLApp::Serialize(Stream& s)
{
int version = 0;
s / version;
s % query.status
% query.borrowed_from
% query.borrowed_to;
}
SQLApp.lay
LAYOUT(SQLAppLayout, 548, 440)
ITEM(SqlArray, book, LeftPosZ(4, 536).TopPosZ(28, 200))
ITEM(SqlArray, borrowed, LeftPosZ(4, 536).TopPosZ(232, 204))
ITEM(MenuBar, menu, LeftPosZ(4, 304).TopPosZ(4, 20))
END_LAYOUT
LAYOUT(BookLayout, 340, 88)
ITEM(Label, dv___0, SetLabel(t_("Author")).LeftPosZ(4, 40).TopPosZ(4, 19))
ITEM(EditString, author, LeftPosZ(48, 284).TopPosZ(4, 19))
ITEM(Label, dv___2, SetLabel(t_("Title")).LeftPosZ(4, 40).TopPosZ(28, 19))
ITEM(EditString, title, LeftPosZ(48, 284).TopPosZ(28, 19))
ITEM(Button, ok, SetLabel(t_("OK")).LeftPosZ(200, 64).TopPosZ(56, 24))
ITEM(Button, cancel, SetLabel(t_("Cancel")).LeftPosZ(268, 64).TopPosZ(56, 24))
END_LAYOUT
LAYOUT(BorrowLayout, 280, 88)
ITEM(Label, dv___0, SetLabel(t_("Borrowed")).LeftPosZ(4, 56).TopPosZ(4, 19))
ITEM(EditField, borrowed, LeftPosZ(60, 72).TopPosZ(4, 19))
ITEM(Label, dv___2, SetLabel(t_("Returned")).LeftPosZ(140, 56).TopPosZ(4, 19))
ITEM(EditField, returned, LeftPosZ(196, 76).TopPosZ(4, 19))
ITEM(Label, dv___4, SetLabel(t_("Person")).LeftPosZ(4, 56).TopPosZ(28, 19))
ITEM(EditString, person, LeftPosZ(60, 212).TopPosZ(28, 19))
ITEM(Button, ok, SetLabel(t_("OK")).LeftPosZ(140, 64).TopPosZ(56, 24))
ITEM(Button, cancel, SetLabel(t_("Cancel")).LeftPosZ(208, 64).TopPosZ(56, 24))
END_LAYOUT
LAYOUT(QueryLayout, 292, 112)
ITEM(LabelBox, dv___0, SetLabel(t_("Status")).LeftPosZ(8, 276).TopPosZ(8, 36))
ITEM(Switch, status, SetLabel(t_("Does not matter\nIn library\nBorrowed")).LeftPosZ(16, 256).TopPosZ(24, 16))
ITEM(Label, dv___2, SetLabel(t_("Borrow date between")).LeftPosZ(8, 108).TopPosZ(52, 19))
ITEM(EditDate, borrowed_from, LeftPosZ(116, 72).TopPosZ(52, 19))
ITEM(Label, dv___4, SetLabel(t_("and")).LeftPosZ(192, 20).TopPosZ(52, 19))
ITEM(EditDate, borrowed_to, LeftPosZ(212, 72).TopPosZ(52, 19))
ITEM(Button, clear, SetLabel(t_("Clear")).LeftPosZ(8, 64).TopPosZ(80, 24))
ITEM(Button, ok, SetLabel(t_("OK")).LeftPosZ(152, 64).TopPosZ(80, 24))
ITEM(Button, cancel, SetLabel(t_("Cancel")).LeftPosZ(220, 64).TopPosZ(80, 24))
END_LAYOUT
SQLApp.sch
TABLE_(BOOK)
INT_ (ID) PRIMARY_KEY AUTO_INCREMENT
STRING_ (AUTHOR, 2000)
STRING_ (TITLE, 2000)
END_TABLE
TABLE_(BORROW_RECORD)
INT (ID) PRIMARY_KEY AUTO_INCREMENT
INT_ (BOOK_ID) REFERENCES(BOOK)
STRING_ (PERSON, 2000) INDEX
INT_ (BORROWED) INDEX
INT_ (RETURNED) INDEX
END_TABLE
|
