Überblick
Beispiele
Schnappschüsse
Vergleiche
Anwendungen
Herunterladen
Documentation
Basar
Status & Fahrplan
Häufig gestellte Fragen
Autoren & Lizenz
Forum
Ultimate++ finanziell unterstützen
Diese Seite durchsuchen
Sprache
Deutsch













SourceForge.net Logo



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

 

 

 

 

Diese Seite gibt es auch in english, català, čeština, español, euskara, français, română, русский, 中文(简体) und 中文(繁體). Willst du mitmachen?