DataBase question ...

[marc_256]

Hi,

For my CAD/CNC GUI,
I like to build a DataBase for the (see image below)

- Materials
- Tools
- ...


But what is the easy-est way to build a DataBase ?
So, the user can ADD, INSERT, DELETE, STORE this datas.

For now (test mode), I used the DataSection, and read the data.



Thanks,
Marc

[miso]

Others may disagree, but I always use sqlite for local database. I'm pretty satisfied. (I built and use one with around 500.000 record, and still fine)

[skywalk]

No disagreement from me. SQLite is the defacto standard for local database use.

[SMaag]

First you have to think about what you need!

If you need real databasefunctions: Multiple tables and a cross connection of the tables! Then a Database!
Start with SQlite. This is a passive Database without a server. That means 1 single user at same time.
SQlite can be updated later very easy to a MySQL Server Database.
But for a Programm like your NC, SQLite would be the Database to use.
For creating a database you can use PB's Database function and CreateDatabase command to create the empty Database and
additional CreateTable commands to create your tables in the Database. Or you use a Database creating tool
like HeidiSQL.

If you do not need real databasefunctions: That means, if you can do all your things with a single table like a single Exel Sheet, you do not
need a Database. It's possible to use .csv like list or a Parameterlist (internal PB Listfunction) what you laod and save.

I made some Tools to simplify creating PB Database Code

1. CodeCreation Modul
https://github.com/Maagic7/PureBasicFra ... reation.pb

2. DbCodeCreation Modul
https://github.com/Maagic7/PureBasicFra ... reation.pb

[infratec]

As you can read: SQLite

But since then you have to create the file and then the tables,
you can also include a default database in the datasection an write this to a file.
So you can also have default entries in your tables.

Simple check at program start if the file is availble, if not, write the default database from your DataSection.

As 'Tool' I prefer:
https://sqlitebrowser.org/

It is compete free and open source.

[idle]

I'd ask how are you representing the data in memory?
I'm currently writing a db engine for PB that's binary compatible with users structures, if you can use allocate structure and copy structure in your program then you can use it as a storage engine, you only have to declare JsonRuntimeStructures which can be nested and can include pb arrays list and maps you just cant cant use pointers fields in structures.

something like this would work

Code: Select all

Structure GeneralData 
  number.i 
  name.s
  quantity.i 
EndStructure 

Structure vec3 
  x.d
  y.d
  z.d
  w.d
EndStructure   

Structure Material 
  number.i
  material.s
  type.s 
EndStructure   

Structure item 
  mat.material 
  List positions.vec3() 
EndStructure  
  
Structure Project 
  Gen.GeneralData 
  pos.vec3 
  List model.item() 
EndStructure 

IMPBDB_AllocateStructure(*project,project)  
IMPBDB_AllocateStructure(*material,Material)

Procedure AddMaterial(Material$,type$,number.i) 
  
  Protected *material.Material = AllocateStructure(Material) 
  *material\material = Material$ 
  *material\type = type$ 
  *material\number = number 
  
   ProcedureReturn db\set(0,"materials:" + Material$,*material,"material") 
     
 EndProcedure    
  
Global *db.IIMPBDB = IMPBDB_OpenDB("projects",filepath$,0) 

*mat.material = AddMaterial("Aluminuim","AL6063",123)

*proj.project = AllocateStructure(project) 
*proj\Gen\name = "widget"
*proj\Gen\number = 200 
*proj\pos\x = 20.500 
*proj\pos\y = 100.00 
*proj\pos\z = 123.40 

AddElement(*proj\model()) 
*proj\model()\mat\material = *mat\material 
*proj\model()\mat\type = *mat\type 
*proj\model()\mat\number = *mat\number 
AddElement(*proj\model()\positions()) 
*proj\model()\positions()\x = 22.00 
*proj\model()\positions()\y = 102.00 
*proj\model()\positions()\z = 123.20  

db\Set(0,"projects:" + *proj\Gen\name ,*proj, "project") 

*ls.IMPBDB_List = db\Enum(0,"materials:") 
ForEach *ls\results()                         
   *mat.Materials = *ls\results()\value
    Debug *mat\material
Next    

the whole project would be saved to disk and restored to a pointer.
you can call Set multiple times and it will only update the disk if the data has changed it's a just a write ahead log so it also stores all the history and can be used to redo undo ....

you can see it here
https://www.purebasic.fr/english/viewtopic.php?t=86090
which is an example of doing wild card indexing and full text indexing.

[marc_256]

Hi,


Thanks for the help and tips.
I never created a DataBase before, so it will be hard (again).

Is it possible to create a empty SQLite DataBase, so the user can enter his data ?
If I understand, I can create columns/titles and sub data as .s/.l/.w/.f/ ...

As I'm still in development mode, I will start with expand my local DataSection,
So I can later convert it to a SQLite/DataBase data.

PS: as there are a lot of machines with XP, Win7, Win8.1
Can I use SQLite in this OS ?


Marc,

[infratec]

When your PB program works on these OSs, then SQLite too. It is 'inbuild'

You can create an empty database, but ...
then it is empty and no one can do anything with it.

There have to be at least one table inside.
Then, depending on the table fields, you can insert, update, delete and query records.

Maybe a material table, a tool table, a project table ...

[infratec]

A simple Demo:

Code: Select all

EnableExplicit


Define.i File, DB
Define DBFilename$, SQL$

UseSQLiteDatabase()

DBFilename$ = GetPathPart(ProgramFilename()) + "Test.sq3"

If FileSize(DBFilename$) < 0
  File = CreateFile(#PB_Any, DBFilename$)
  If File
    CloseFile(File)
  EndIf
EndIf

DB = OpenDatabase(#PB_Any, DBFilename$, "", "")
If DB
  SQL$ = "CREATE TABLE IF NOT EXISTS material (number TEXT, material TEXT, sort_type TEXT)"
  If DatabaseUpdate(DB, SQL$) = 0
    Debug DatabaseError()
  Else
    
    SQL$ = "INSERT INTO material (number, material, sort_type) VALUES ('Mat0001', 'Aluminium', 'AL6063')"
    If DatabaseUpdate(DB, SQL$) = 0
      Debug DatabaseError()
    Else
      
      SQL$ = "SELECT number, material, sort_type FROM material WHERE 1"
      If DatabaseQuery(DB, SQL$)
        While NextDatabaseRow(DB)
          Debug GetDatabaseString(DB, 0) + " " + GetDatabaseString(DB, 1) + " " + GetDatabaseString(DB, 2)
        Wend
        FinishDatabaseQuery(DB)
      EndIf
      
      SQL$ = "UPDATE material SET sort_type = 'AL6064' WHERE number = 'Mat0001'"
      If DatabaseUpdate(DB, SQL$) = 0
        Debug DatabaseError()
      Else
        
        SQL$ = "SELECT number, material, sort_type FROM material WHERE 1"
        If DatabaseQuery(DB, SQL$)
          While NextDatabaseRow(DB)
            Debug GetDatabaseString(DB, 0) + " " + GetDatabaseString(DB, 1) + " " + GetDatabaseString(DB, 2)
          Wend
          FinishDatabaseQuery(DB)
        EndIf
        
      EndIf
      
      SQL$ = "DELETE FROM material WHERE sort_type = 'AL6064'"
      If DatabaseUpdate(DB, SQL$) = 0
        Debug DatabaseError()
      EndIf
      
    EndIf
    
  EndIf
  CloseDatabase(DB)
EndIf

[miso]

Hello Marc!

PS: as there are a lot of machines with XP, Win7, Win8.1
Can I use SQLite in this OS ?

Only WinXP is interesting, You have to make code that compiles with PB 6.04, the last version that supports Windows XP. The latter are fine with the newest.

I'm aware, that you are a hardboiled veteran coder, so I won't tire you with the basics. Instead, I will write about my experiences when I was in your shoes, when I found that I have some data I would like to organize and make it easily editable/searchable.

Just like you I choosed databases (and was a good choice). I was aware of the existence of databases, was able to write querys, but I was not really used to them. I learned the hard way, so even possible that I might have picked up some bad habits I'm not aware.

So my consequencies using SQLITE:

-The most important part is to correctly set up the records of a table

-Table name choice was not important, theres really no wrong choices

-consider an extra column for your records as a UNIQUE INDEX for the row, maybe with autoincrement. Might be handy later on in a way you would not expect.

-material id in your example can be an integer, instead of strings

-aluminium also can be an integer instead of string

-material code must be a string. I use fixed length strings like varchar(20) or varchar(50)
That way the database saves that long of an empty space in the database file, making it easy to change without major shennanigans, but you have to carefully evaluate the maximum length of the possible entries. I assume the code is unique, so you can make it unique. This ensures, that you wont have
a duplicate entry with that material code.

-PB refers to columns as a number, database refers to them as a name. My records have a list of integers, so I named columns with a choosen delimeter and a number like "DB_"+str(#DB_MATERIALID). That way, I was easily able to convert an integer columnid to a database column name vica versa. Was very good when I created buttons to modify them. (I don't use enumeration but directly sets the values to those constants, ensuring not to accidentally mismatch with the database rows)

My post went long and messy. I hope theres at least one thing that might help you in your considerations.

[marc_256]

Hello everyone,

First at all, thanks for your posts and inputs.

I need to learn to use databases, it will be important in the future for my program.
So, I gone take some time this WE to learn some stuff about PB database use.

I think, I need to put my data on paper, what I want to store and what not ...
also, the DB files will be small ones, some IDnumbers, IDnames, and some data.
For example, I have for now
- 20 materials
- 100 tools (lathe, mill)

Marc

[infratec]

A quick intro:

https://www.purebasic.fr/english/viewtopic.php?t=74764