Tabel Editing Data base Ver 2.0

[microdevweb]

Editable table linked to a database

sample code for the above table

Code: Select all

UseSQLiteDatabase()
XIncludeFile "DataTable.pbi"
Global DataFile$=GetCurrentDirectory()+"DB\Teste.sqlite"
Global Event
; 
Procedure OpenMainWindows()
      Enumeration TbCivilite
            #Id
            #Pays
            #Localite
            #CodePostal
      EndEnumeration
      OpenWindow(0,0,0,800,600,"Teste",#PB_Window_SystemMenu)
      
      Table::CreateTable(0,50,50,450,450,"Teste",DataFile$,"TbLocalite","","")
      Table::AddColumn(0,#Id,"ID","id",150,#True)
      Table::AddColumn(0,#Pays,"Pays","idPays",150)
      Table::SetEditableColumn(0,#Pays,#True,0,0)
      Table::AddColumn(0,#Localite,"Localite","localite",150)
      Table::SetEditableColumn(0,#Localite,#True,Table::#TypeString,0)
      Table::SetRequired(0,#Localite,#True,#True)
      Table::AddColumn(0,#CodePostal,"Code postal","code_postal",150)
      Table::SetEditableColumn(0,#CodePostal,#True,Table::#TypeString,0)
      Table::AddLinkColumn(0,#Pays,"TbPays","id","pays")
      Table::SetFlagColumn(0,#Localite,#True,#True,#True)
      Table::SetFlagColumn(0,#CodePostal,#True,#True,#True)
;       Table::SetLineHeight(0,80)
      Table::SetOrderOnColumn(0,#Localite)
      Table::DrawTable(0)
EndProcedure

OpenMainWindows()
;TesteLoc()
Repeat
      Event=WaitWindowEvent()
      Table::EventTable(0,Event)
      Select Event
            Case #PB_Event_Gadget
                  Select EventGadget()
                        Case 1
                              Debug Table::GetValue(0,#Localite)
                  EndSelect
      EndSelect
Until Event=#PB_Event_CloseWindow

Ver 2.0
https://www.mediafire.com/?1oudyomu2igyy71
The column are editable directly with a right click of the mouse.
Data related tables are directly displayed in a combobox.
zip content:
DataTable.pbi
IMG (the picture for icone)
DB (the testing database)
Main.pb (for testing)
operation
To create the table

Code: Select all

CreateTable(idTable,X,Y,widht,height,title$,dataName$,dataTable$,user$,paseword$)

For the addition of column
WARNING: The first column must include the unique key of the table (hide with the flag)

Code: Select all

AddColumn(idTable,idColumn,title$,dbColumn$,width,hide.b=#False)

A place in the event loop (Event=WaitwindowEvent ou WindowEvent)

Code: Select all

EventTable(idTable,Event)

For a link with another table
idColumn=The column to link
dbTableLink$=Secondary table
dbColumnLink$=Secondary column
dbReturnLink$=Secondary column displayed

Code: Select all

AddLinkColumn(idTable,idColumn,dbTableLink$,dbColumnLink$,dbReturnLink$)

Customizing the table
Added ability to sort, search ( type LIKE), resizing column

Code: Select all

SetFlagColumn(idTable,idColumn,orderOn.b,chercheOn.b,redimOn.b)

Choice of column used to sort departure

Code: Select all

SetOrderOnColumn(idTable,idColumn,descOn.b=#False)

Optional graphical configuration

Code: Select all

SetLineHeight(idTable,height)

Code: Select all

SetLineColorPairImpair(idTable,colorPair,colorImpair)

Code: Select all

SetLineTileColor(idTable,colorBack,colorFont)

Code: Select all

SetLineTileColumnColor(idTable,colorBack,colorFont)

Code: Select all

SetRibonColor(idTable,colorBack,colorFont)

Code: Select all

SetTitleHeight(idTable,height)

Code: Select all

SetTitleColumnHeight(idTable,height)

Code: Select all

SetTextColor(idTable,color)

First of use LoadFont()

Code: Select all

SetFontTitle(idTable,font)

Code: Select all

SetFontTitleColumn(idTable,font)

Code: Select all

SetFontText(idTable,font)

News vers 2.0

to make the column editable (with direct update of database)
TypeEdit= The flag of stringGadget (number, upercase, lowercase, ect)
LenEdit =maximum len of the string (no inlemented now)

Code: Select all

SetEditableColumn(idTable,idColumn,EditableOn.b,TypeEdit,LenEdit)

to make the column requiered and/of single

Code: Select all

SetRequired(idTable,idColumn,RequiredOn,UniqueRecordOn)

Wath is expeted after....
Adding a new line to add a record in the database

Excuse me, my English is not very good

[idle]

looks good, thanks for sharing.

[AAT]

Hi, microdevweb.

Something wrong in your example:



Good luck!

[microdevweb]

Hi AAT,
I did not implement the database, you must do

Code: Select all

Procedure pCreateTable(DataBase)
      Protected query$,Result
      ;Table des civilités
      query$="CREATE TABLE IF NOT EXISTS TbCivilite ("+
             "id INTEGER PRIMARY KEY AUTOINCREMENT,"+
             "civilite TEXT)"
      Result=DatabaseUpdate(DataBase,query$)
      If Result=0
            MessageRequester("Erreur",DatabaseError())
            ProcedureReturn #False
      EndIf
      ;Table des Pays
      query$="CREATE TABLE IF NOT EXISTS TbPays ("+
             "id INTEGER PRIMARY KEY AUTOINCREMENT,"+
             "pays TEXT)"
      Result=DatabaseUpdate(DataBase,query$)
      If Result=0
            MessageRequester("Erreur",DatabaseError())
            ProcedureReturn #False
      EndIf
      ;Table des Pays
      query$="CREATE TABLE IF NOT EXISTS TbLocalite ("+
             "id INTEGER PRIMARY KEY AUTOINCREMENT,"+
             "idPays INT,"+
             "localite TEXT,"+
             "code_postal TEXT"+  
      ")"
      Result=DatabaseUpdate(DataBase,query$)
      If Result=0
            MessageRequester("Erreur",DatabaseError())
            ProcedureReturn #False
      EndIf
EndProcedure
;Création du fichier DataBase
Procedure pCreateDataBase()
      CreateDirectory(GetCurrentDirectory()+"\DB")
      DataBaseFile$=GetCurrentDirectory()+"\DB"+"\Animalia.sqlite"
      If FileSize(DataBaseFile$)=-1
            If CreateFile(0,DataBaseFile$)=0
                  MessageRequester("Erreur","La Data base n'a pas été créée...",#PB_MessageRequester_Ok)
                  ProcedureReturn #False  
            Else
                  CloseFile(0) 
            EndIf
      EndIf
      If OpenDatabase(0,DataBaseFile$,"","")=0
            MessageRequester("Erreur","Impossible d'ouvrir la base de données...",#PB_MessageRequester_Ok)
            ProcedureReturn #False
      EndIf
      pCreateTable(0)
      ProcedureReturn #True
EndProcedure

[AAT]

Hi, microdevweb

I did not implement the database, you must do...

I understood you, thanks!
One more remark:
GetCurrentDirectory() - Returns the full path of the current directory. It will end with a directory separator ('\' on Windows and '/' otherwise). ((c) Help to PureBasic)
so "backslash" in
GetCurrentDirectory()+"\
is excess:

Code: Select all

debug GetCurrentDirectory()+"\DB" 
debug GetCurrentDirectory()+"\DB"+"\Animalia.sqlite"

Good luck!

[microdevweb]

thank you AAT
your comment is true, accustomed with other language

[microdevweb]

The litel up for the new version, now the column are directly editable

[electrochrisso]

Good stuff AAT, thanks for sharing.

[infratec]

Hi,

it works also with PostgreSQL if you modify your code a bit:

replace

Code: Select all

query$+" LIMIT "+Str(myTable()\firtLine-1)+","+myTable()\nbLineScreen

with

Code: Select all

query$ + " LIMIT " + myTable()\nbLineScreen + " OFFSET " + Str(myTable()\firtLine-1)

Than you can use:

Code: Select all

Global DataBase$="host=localhost dbname=test"

instead of

Code: Select all

Global DataBase$=GetCurrentDirectory()+"DB\Teste.sqlite"

Oh, I replaced DataFile$ wih DataBase$

It's not the fastest solution and for large databases it's not really usable, but it works

Bernd

[microdevweb]

Thank you infratec for the info,

I was thinking of doing a version for protgres sql in the near future

I tested on the basis of records 100000. It had air fluid

[infratec]

Hi,

one thing I just noticed:

If you are nearly at the bottom of a table which is sorted after field1
and you enter a search text which should shows you entries from the mid of the table,
than you see an empty gadget after pressing return.
Very late I noticed that I have to scroll up by hand to see the entries.

The offset should be set to 0 if a search starts.

Bernd

[AAT]

Good stuff AAT, thanks for sharing.

Hi, electrochrisso,
The autor is microdevweb, not me!
All thanks should be addressed to him.