Some of you might have seem my topic in the Coding Questions forum about PB ORM/CRUD.
Since there where no answers I decided to try something on my own.
The result is posted here: A CRUD generator.
What is CRUD?
CRUD is a acronym for Create, Read, Update and Delete and is normally used to describe the 4 base operations against databases that most DB-software uses.
Why a generator?
Inside programs it's often convenient to use user defined structures to work with structured data.
In many DB-programs programmers end up with having the database with tables on one hand and an almost identical set of structures on the other (where the structures map records in the DB)
The generator simply automates the (often tedious) task to create structures and access-procedures for all database tables in the database that the program is made to access.
So your generator made all the CRUD for me, what now?
Example database (1 table) for demo:
Code: Select all
CREATE TABLE user (
Id INTEGER UNSIGNED NOT NULL,
Name VARCHAR(45) NOT NULL,
PRIMARY KEY (Id)
)
Additionally it creates an "include all file" called CRUD.pb (inside the CRUD directory)
All you have to do to attach all the CRUD code to your own application is to:
XIncludeFile "CRUD\CRUD.pb"
Now that you have the crud accessible you can create a user in the DB like this:
demo_Create.pb
Code: Select all
XIncludeFile "CRUD\CRUD.pb"
UseODBCDatabase()
If Not OpenDatabase(0, "demo", "", "", #PB_Database_ODBC)
MessageRequester("Error", "Unable to open database")
End
EndIf
Global user.TUser
user\Id = 1
user\Name = "MaHan"
user_Create(@user)
CloseDatabase(0)
demo_Read.pb
Code: Select all
XIncludeFile "CRUD\CRUD.pb"
UseODBCDatabase()
If Not OpenDatabase(0, "demo", "", "", #PB_Database_ODBC)
MessageRequester("Error", "Unable to open database")
End
EndIf
Global user.TUser
user_Read(@user, "Id = 1")
Debug "user\Id: " + Str(user\Id)
Debug "user\Name: " + user\Name
CloseDatabase(0)
Procedure.l user_Create(*user.Tuser)
Procedure.l user_Read(*user.Tuser, filter.s)
Procedure.l user_Update(*user.Tuser, filter.s)
Procedure.l user_Delete(filter.s)
I'll leave it to the readers imagination and experimentation to find out how <table>_Update and <table>_Delete procedures work
Okay, now that I described a bit what this is all about here is the code:
GenerateCRUD.pb:
Code: Select all
;
; PureBasic ODBC Crud file generator v0.01.
; Written 2009 by MaHan (Mattias Hansson)
;
; This software is released as public domain.
;
; By the use of this software all users acknowledge that the original author has no
; responsibility: direct, indirect or otherwise for any consequences of its function
; or malfunction.
;
; If you use this software mentions are welcome but not required.
;
; Note: All Auto generated code (the output of this program) is the users own property.
XIncludeFile "enumtables.pb"
UseODBCDatabase()
If Not ExamineDatabaseDrivers()
MessageRequester("ODBC Error", "Can't find any ODBC DSN:s")
End
EndIf
NextDatabaseDriver()
DSNName.s = DatabaseDriverName() ;Just takes the first DSN - adjust for your own needs.
ODBCListTables("DSN="+DSNName, #False)
If Not OpenDatabase(0, DSNName, "", "", #PB_Database_ODBC)
MessageRequester("Error", "Unable to open database")
End
EndIf
If FileSize("CRUD") = -1
CreateDirectory("CRUD")
EndIf
ForEach TableNames()
filename.s = "CRUD\" + TableNames()+".pb"
q.s=Chr(34)
If Not (FileSize(filename) = -1)
Debug "Not generating " + filename + ". File exists. (if you wanna regenerate: backup and remove old CRUD-files first)"
End
EndIf
If DatabaseQuery(0, "select * from " + TableNames())
If CreateFile(0, filename)
;Write file header
WriteStringN(0, ";Autogenerated CRUD file. GenerateCRUD.pb by MaHan in 2009.")
WriteStringN(0, "")
WriteStringN(0, "EnableExplicit")
WriteStringN(0, "")
WriteStringN(0, "#" + TableNames() + "_TableName = " + q + TableNames() + q)
;Write structure definition
WriteStringN(0, "Structure T" + TableNames())
numCol = DatabaseColumns(0)
;Write structure fields
For i = 0 To numCol-1
Select DatabaseColumnType(0, i)
Case #PB_Database_Long
WriteStringN(0, " " + DatabaseColumnName(0, i) + ".l")
Case #PB_Database_String
WriteStringN(0, " " + DatabaseColumnName(0, i) + ".s")
Case #PB_Database_Float
WriteStringN(0, " " + DatabaseColumnName(0, i) + ".f")
Case #PB_Database_Double
WriteStringN(0, " " + DatabaseColumnName(0, i) + ".d")
Case #PB_Database_Quad
WriteStringN(0, " " + DatabaseColumnName(0, i) + ".q")
EndSelect
Next
;Finish structure
WriteStringN(0, "EndStructure")
WriteStringN(0, "")
;Write Create() procedure
WriteStringN(0, "Procedure.l " + TableNames() + "_Create(*"+TableNames() + ".T" + TableNames() + ")")
WriteStringN(0, " Protected q.s=chr(34)")
WriteStringN(0, " Protected sql.s = " + q + "insert into " + TableNames() + " " + q)
WriteStringN(0, " With *"+TableNames())
fieldNames.s = ""
values.s = ""
comma.s = ""
For i = 0 To numCol-1
If i = 0
comma = ""
Else
comma = ", "
EndIf
fieldNames + comma + DatabaseColumnName(0, i)
Select DatabaseColumnType(0, i)
Case #PB_Database_Long
values + comma + q + " + " + "Str(\" + DatabaseColumnName(0, i) + ")" + " + " + q
Case #PB_Database_String
values + comma + q + " + q + \" + DatabaseColumnName(0, i) + " + q + " + q
Case #PB_Database_Float
values + comma + q + " + " + "Str(\" + DatabaseColumnName(0, i) + ")" + " + " + q
Case #PB_Database_Double
values + comma + q + " + " + "Str(\" + DatabaseColumnName(0, i) + ")" + " + " + q
Case #PB_Database_Quad
values + comma + q + " + " + "Str(\" + DatabaseColumnName(0, i) + ")" + " + " + q
EndSelect
Next
WriteStringN(0, " sql + " + q + "(" + fieldNames + ")" + q)
WriteStringN(0, " sql + " + q + " values (" + values + ")" + q)
WriteStringN(0, " EndWith")
WriteStringN(0, "")
WriteStringN(0, " If Not DatabaseUpdate(0, sql)")
WriteStringN(0, " Debug "+ q + "CRUD Layer Error:" + q + " + DatabaseError()")
WriteStringN(0, " ProcedureReturn #False")
WriteStringN(0, " EndIf")
WriteStringN(0, " ProcedureReturn #True")
WriteStringN(0, "EndProcedure")
WriteStringN(0, "")
;Write Read procedure
WriteStringN(0, "Procedure.l " + TableNames() + "_Read(*"+TableNames() + ".T" + TableNames() + ", filter.s)")
;WriteStringN(0, " Protected q.s=chr(34)")
WriteStringN(0, " Protected sql.s = " + q + "select " + q)
fieldNames.s = ""
comma.s = ""
For i = 0 To numCol-1
If i = 0
comma = ""
Else
comma = ", "
EndIf
fieldNames + comma + DatabaseColumnName(0, i)
Next
WriteStringN(0, " sql + " + q + fieldNames + " from " + TableNames() + " where " + q + " + filter")
WriteStringN(0, "")
WriteStringN(0, " If Not DatabaseQuery(0, sql)")
WriteStringN(0, " Debug "+ q + "CRUD Layer Error:" + q + " + DatabaseError()")
WriteStringN(0, " ProcedureReturn #False")
WriteStringN(0, " EndIf")
WriteStringN(0, "")
WriteStringN(0, " If Not NextDatabaseRow(0)")
WriteStringN(0, " ProcedureReturn #False")
WriteStringN(0, " EndIf")
WriteStringN(0, "")
WriteStringN(0, " With *"+TableNames())
For i = 0 To numCol-1
Select DatabaseColumnType(0, i)
Case #PB_Database_Long
WriteStringN(0, " \" + DatabaseColumnName(0, i) + " = Val(GetDatabaseString(0, "+Str(i)+"))")
Case #PB_Database_String
WriteStringN(0, " \" + DatabaseColumnName(0, i) + " = GetDatabaseString(0, "+Str(i)+")")
Case #PB_Database_Float
WriteStringN(0, " \" + DatabaseColumnName(0, i) + " = ValF(GetDatabaseString(0, "+Str(i)+"))")
Case #PB_Database_Double
WriteStringN(0, " \" + DatabaseColumnName(0, i) + " = ValD(GetDatabaseString(0, "+Str(i)+"))")
Case #PB_Database_Quad
WriteStringN(0, " \" + DatabaseColumnName(0, i) + " = Val(GetDatabaseString(0, "+Str(i)+"))")
EndSelect
Next
WriteStringN(0, " EndWith")
WriteStringN(0, "")
WriteStringN(0, " FinishDatabaseQuery(0)")
WriteStringN(0, " ProcedureReturn #True")
WriteStringN(0, "EndProcedure")
WriteStringN(0, "")
;Write update procedure
WriteStringN(0, "Procedure.l " + TableNames() + "_Update(*"+TableNames() + ".T" + TableNames() + ", filter.s)")
WriteStringN(0, " Protected q.s=chr(34)")
WriteStringN(0, " Protected sql.s = " + q + "update " + TableNames() + " set " + q)
WriteStringN(0, " With *"+TableNames())
comma.s = ""
For i = 0 To numCol-1
If i = 0
comma = ""
Else
comma = ", "
EndIf
Select DatabaseColumnType(0, i)
Case #PB_Database_Long
WriteStringN(0, " sql + " + q + comma + DatabaseColumnName(0, i) + " = " + q + " + Str(\" + DatabaseColumnName(0, i) + ")")
Case #PB_Database_String
WriteStringN(0, " sql + " + q + comma + DatabaseColumnName(0, i) + " = " + q + " + q + \" + DatabaseColumnName(0, i) + " + q")
Case #PB_Database_Float
WriteStringN(0, " sql + " + q + comma + DatabaseColumnName(0, i) + " = " + q + " + Str(\" + DatabaseColumnName(0, i) + ")")
Case #PB_Database_Double
WriteStringN(0, " sql + " + q + comma + DatabaseColumnName(0, i) + " = " + q + " + Str(\" + DatabaseColumnName(0, i) + ")")
Case #PB_Database_Quad
WriteStringN(0, " sql + " + q + comma + DatabaseColumnName(0, i) + " = " + q + " + Str(\" + DatabaseColumnName(0, i) + ")")
EndSelect
Next
WriteStringN(0, " EndWith")
WriteStringN(0, "")
WriteStringN(0, " sql + " + q + " where " + q + " + filter")
WriteStringN(0, "")
WriteStringN(0, " If Not DatabaseUpdate(0, sql)")
WriteStringN(0, " Debug "+ q + "CRUD Layer Error:" + q + " + DatabaseError()")
WriteStringN(0, " ProcedureReturn #False")
WriteStringN(0, " EndIf")
WriteStringN(0, "")
WriteStringN(0, " ProcedureReturn #True")
WriteStringN(0, "EndProcedure")
WriteStringN(0, "")
;Write delete procedure
WriteStringN(0, "Procedure.l " + TableNames() + "_Delete(filter.s)")
;WriteStringN(0, " Protected q.s=chr(34)")
WriteStringN(0, " Protected sql.s = " + q + "delete from " + TableNames() + " " + q)
WriteStringN(0, " sql + " + q + " where " + q + " + filter")
WriteStringN(0, "")
WriteStringN(0, " If Not DatabaseUpdate(0, sql)")
WriteStringN(0, " Debug "+ q + "CRUD Layer Error:" + q + " + DatabaseError()")
WriteStringN(0, " ProcedureReturn #False")
WriteStringN(0, " EndIf")
WriteStringN(0, "")
WriteStringN(0, " ProcedureReturn #True")
WriteStringN(0, "EndProcedure")
WriteStringN(0, "")
CloseFile(0)
EndIf
FinishDatabaseQuery(0)
Else
Debug DatabaseError()
EndIf
Next
CloseDatabase(0)
;Write include file that includes all Crud-includes generated (so you can have them in you project
;with a single include.
If CreateFile(0, "CRUD\CRUD.pb")
WriteStringN(0, ";Autogenerated CRUD file. GenerateCRUD.pb by MaHan in 2009.")
WriteStringN(0, "")
ForEach TableNames()
filename.s = "CRUD\" + TableNames()+".pb"
WriteStringN(0, "XIncludeFile " + q + filename + q)
Next
WriteStringN(0, "")
CloseFile(0)
EndIf
(original post by Stephen here)
enumtables.pb:
Code: Select all
;/////////////////////////////////////////////////////////////////////////////////
;A small utility for listing the tables in an ODBC database.
;It does not use SQL (for which different drivers require different SQL statements in
;order to list the tables etc.)
;It also does not use internal Purebasic structures.
;Developed with Purebasic 4.2 beta 4.
;Windows only.
;Unicode : Yes.
;
;By Stephen Rodriguez.
;April 2008.
;/////////////////////////////////////////////////////////////////////////////////
#SQL_SUCCESS = 0
#SQL_SUCCESS_WITH_INFO = 1
#SQL_NO_DATA = 100
#SQL_NULL_HANDLE = 0
#SQL_HANDLE_ENV = 1
#SQL_HANDLE_DBC = 2
#SQL_HANDLE_STMT = 3
#SQL_ATTR_ODBC_VERSION = 200
#SQL_OV_ODBC3 = 3
#SQL_NTS = -3
#SQL_DRIVER_COMPLETE = 1
#SQL_C_CHAR = 1
#SQL_C_WCHAR = -8
;The following compiler directive accounts for Ansi and Unicode modes.
CompilerIf #PB_Compiler_Unicode
#SQL_CHAR = #SQL_C_WCHAR
CompilerElse
#SQL_CHAR = #SQL_C_CHAR
CompilerEndIf
Global NewList TableNames.s()
;Set the 'blnListAllObjects' parameter to #True if you wish to list all objects.
;You have to do this in the case of the MS Excel ODBC driver for example.
;Returns zero if no error else returns an SQL error code.
Procedure.l ODBCListTables(dsn$, blnListAllObjects = #False)
Protected result.w, env, dbc, stmt, table, len
Protected tableName$ = Space(512)
ClearList(TableNames())
;Create an environment handle and set the environment attribute to use ODBC 3.
SQLAllocHandle_(#SQL_HANDLE_ENV, #SQL_NULL_HANDLE, @env)
SQLSetEnvAttr_(env, #SQL_ATTR_ODBC_VERSION, #SQL_OV_ODBC3, 0)
;Allocate a connection handle.
SQLAllocHandle_(#SQL_HANDLE_DBC, env, @dbc);
;Connect to the database.
result = SQLDriverConnect_(dbc, #Null, dsn$, #SQL_NTS, #Null, 0, #Null, #SQL_DRIVER_COMPLETE)&$ffff
If result = #SQL_SUCCESS Or result = #SQL_SUCCESS_WITH_INFO
;Allocate a statement handle.
SQLAllocHandle_(#SQL_HANDLE_STMT, dbc, @stmt)
;Retrieve a list of tables.
If blnListAllObjects
SQLTables_(stmt, #Null, 0, #Null, 0, #Null, 0, 0, 0)
Else
SQLTables_(stmt, #Null, 0, #Null, 0, #Null, 0, @"TABLE", #SQL_NTS)
EndIf
;Loop through the tables.
SQLBindCol_(stmt,3,#SQL_CHAR,@tableName$, 512, @len)
result = SQLFetch_(stmt)&$ffff
While result = #SQL_SUCCESS
table+1
;Debug "Table " + Str(table) + " is named : " + tableName$
AddElement(TableNames())
TableNames() = tableName$
result = SQLFetch_(stmt)&$ffff
Wend
If result = #SQL_NO_DATA
result = #SQL_SUCCESS
EndIf
SQLFreeHandle_(#SQL_HANDLE_STMT, stmt);
EndIf
;Free the handles.
SQLFreeHandle_(#SQL_HANDLE_DBC, dbc);
SQLFreeHandle_(#SQL_HANDLE_ENV, env)
ProcedureReturn result
EndProcedure
;TEST.
;In my case I set up a DSN connection to a MS Access database named customers.
;You will need to create your own DSN connection before running this program.
; ODBCListTables("DSN=customers;")
;/////////////////////////////////////////////////////////////////////////////////
;A small utility for listing the tables in an SQLite database.
;Developed with Purebasic 4.2 beta 4.
;Requires PB 4.2 to run.
;Platforms : All.
;Unicode : Yes.
;
;By Stephen Rodriguez.
;April 2008.
;/////////////////////////////////////////////////////////////////////////////////
;UseSQLiteDatabase()
;Set the 'blnListAllObjects' parameter to #True if you wish to list all objects.
;You have to do this in the case of the MS Excel ODBC driver for example.
;Returns zero if an error.
Procedure.l SQLiteListTables(database$, blnListAllObjects = #False)
Protected result, db, sql$, table
db = OpenDatabase(#PB_Any, database$, "", "", #PB_Database_SQLite)
If db
If blnListAllObjects
sql$ = "select * from sqlite_master"
Else
sql$ = "select * from sqlite_master WHERE type='table'"
EndIf
If DatabaseQuery(db, sql$)
While NextDatabaseRow(db)
table+1
Debug "Table " + Str(table) + " is named : " + GetDatabaseString(db, 2)
Wend
result = 1
EndIf
CloseDatabase(db)
EndIf
ProcedureReturn result
EndProcedure
;TEST.
; SQLiteListTables("MyDatabase.db")
Please post updates and/or bugfixes here if you want.
best regards
/Mattias Hansson (aka MaHan)
edit: Just added mail notification on reply in case someone asks questions later on.
