User Profile and User Properties DB?

[BarrySumpter]

Hi all,
Just about to start developing a user profile with user properties DB.

Which db would be easier and faster to develoep?

SQLite or .txt?
There were .ini and .cfg type file in win that were super fast. simple and easy to work with.
Does MAC have those native type DB files?

[FourthWorld]

Why not use custom properties in a stack file?

[townsend]

If you store User Properties in a text file, you'll probably have to parse the file.
1- read the file
2- find the property you want
3- update it
4- then save the whole file again.

There are many User Properties examples out there. Here's one I wrote for myself.
It's a single function you can just drop into your app.
It's called dbData, and has 2 parameters: iHandle & iEntry.
iHandle contains your made up internal reference name, of a Property.
iEntry contains the value of the Property you want to save and retrieve.

Here's how it works:

If 'iHandle' already exists 'iEntry' is Updated instead of Inserted.
Null in 'iHandle' closes connection.
Null in 'iEntry' returns retrieved 'iEntry'.
Successful Inserts & Updates return 'ok'.
Any errors, return details, prefixed with the word, 'ERROR'.

Code: Select all

function dbData iHandle, iEntry
     global conID
     -- Step 1: Check for Close Connection Request
     if iHandle is empty then
          revCloseDatabase conID
          put the result into temp
          if the result is empty then
               return "ok"
          else
               else return the result
          end if
          put empty into conID
     end if
     -- Step 2: Build Full Path and SQLite file name
     set itemDel to slash  -- start to build current path for SQlite DB
     put the effective filename of this stack into realpath  --gets current path
     delete last item of realpath --removes .livecode file name on this stack
     put  realpath & "/System.data" into pathNdb -- adds SQLite file name to path
     -- Step 3: Open Connection -- close if all values are empty
     put revopendatabases("sqlite",,,,) into dbIDs --gets a list of open databases
     if not (conID is among the items of dbIDs) then  -- needs new connection id
          put revOpenDatabase("sqlite", pathNdb,,,,,,) into conID
          put the result into temp
          if the result is not a number then
               answer "ERROR=" & conID
          end if
     end if
     -- Step 4: Retrieve Data if Entry is empty
     put "'" & iHandle & "'" into iHandle -- add single quotes
     put "'" & iEntry & "'" into iEntry  -- required for SQL data
     if iEntry ="''" then  -- two single quotes is a request to retrieve data
          put "SELECT Entry FROM control WHERE Handle="  & iHandle into tSQL
          --revExecuteSQL conID, tSQL
          put revdb_querylist(,,conID,tSQL) into ptext
          if "revdberr" is in ptext then
               return "ERROR=" & ptext
          end if
          return ptext  -- valid a valid entry
     end if
     -- Step 5: Create Table
     put  "CREATE TABLE control (Handle text primary key, Entry text);" into tsql
     revExecuteSQL conID, tSQL
     put the result into view -- for debuging
     if "already exists" is in the result then
          -- good
     end if
     -- Step 6: Try to Insert New Row with SQL Insert statement
     put "INSERT INTO control (Handle, Entry)"  into tSQL
     put "VALUES (" & iHandle & ", " & iEntry & ");" after tSQL
     revExecuteSQL conID, tSQL
     put the result into view -- for debuging
     if "is not unique" is in the result then -- row already exits
          -- then go on to update row
     else if the result is 1 then
          return "ok"
     else
          return "ERROR=" & the result  --- exit with error
     end if         
     -- Step 7: Try to Update the Row Instead
     put "UPDATE control SET Entry=" & iEntry &  " WHERE Handle="  & iHandle into tSQL
     revExecuteSQL conID, tSQL
     put the result into view -- for debuging
     if the result is not 1 then
          return "ERROR=" & the result  --- exit with error
     end if         
     return "ok"
end dbData

The only thing missing is a way to delete the Property entirely.
I was thinking maybe "delete" in the 'iEntry" would be a good way go.

[BarrySumpter]

Townsend Rocks!

Freakin' aye dude!

Now thats what these forums are all 'bout!

I'm going to have to start contributing more.

Many thanks!

[BarrySumpter]

Why not use custom properties in a stack file?

Completely missed this post.

Not sure how that works but will have a quick research to see what trouble I can find.

Trying to tie it all together:

maybe:
http://revdeveloper.com/forums/viewtopi ... 3&start=15

and:
http://forums.runrev.com/phpBB2/viewtop ... for+iPhone

Would there be a better step-by-step tutorial on this.
I do recall trying to sort out and use custome properties but I think it was to advanced for me at the time.

----
update: Wasted a lot time and energy chasing this stackData consept down.
There should have been a step-by-step lesson.

[BarrySumpter]

updated slightly for easier reading (for me)
wasn't there a way to add colors to forum posts to match the LC Scipt Editor Colors?

so much easier to follow that the StackData.

Code: Select all

function dbData iHandle, iEntry
   
   -- if 'iHandle' already exists 'iEntry' is Updated instead of Inserted.
   -- Null in 'iHandle' closes connection.
   -- Null in 'iEntry' returns retrieved 'iEntry'.
   -- Successful Inserts & Updates return 'ok'.
   -- Any errors, return details, prefixed with the word, 'ERROR'.
   
   
   global conID
   
   
   --  Step 1: Check for Close Connection Request 
   
   if iHandle is empty then
      
      revCloseDatabase conID
      
      put the result into temp
      
      if the result is empty then
         return "ok"
      else
         
         return the result
      end if
      
      put empty into conID
      
   end if
   
   
   -- Step 2: Build Full Path and SQLite file name
   
   set itemDel to slash  -- start to build current path for SQlite DB
   
   put the effective filename of this stack into realpath  --gets current path
   
   delete last item of realpath --removes .livecode file name on this stack
   
   put  realpath & "/System.data" into pathNdb -- adds SQLite file name to path
   
   
   -- Step 3: Open Connection -- close if all values are empty
   
   put revopendatabases("sqlite",,,,) into dbIDs --gets a list of open databases
   
   if not (conID is among the items of dbIDs) then  -- needs new connection id
      put revOpenDatabase("sqlite", pathNdb,,,,,,) into conID
      put the result into temp
      if the result is not a number then
         answer "ERROR=" & conID
      end if
   end if
   
   
   -- Step 4: Retrieve Data if Entry is empty
   
   put "'" & iHandle & "'" into iHandle -- add single quotes
   put "'" & iEntry & "'" into iEntry  -- required for SQL data
   
   if iEntry ="''" then  -- two single quotes is a request to retrieve data
      
      put "SELECT Entry FROM control WHERE Handle="  & iHandle into tSQL
      
      -- revExecuteSQL conID, tSQL
      
      put revdb_querylist(,,conID,tSQL) into ptext
      
      if "revdberr" is in ptext then
         return "ERROR=" & ptext
      end if
      
      return ptext  -- valid a valid entry
   end if
   
   
   -- Step 5: Create Table
   
   put  "CREATE TABLE control (Handle text primary key, Entry text);" into tsql
   
   revExecuteSQL conID, tSQL
   
   put the result into view -- for debuging
   
   if "already exists" is in the result then
      -- good
   end if
   
   
   -- Step 6: Try to Insert New Row with SQL Insert statement
   
   put "INSERT INTO control (Handle, Entry)"  into tSQL
   
   put "VALUES (" & iHandle & ", " & iEntry & ");" after tSQL
   
   revExecuteSQL conID, tSQL
   
   put the result into view -- for debuging
   
   if "is not unique" is in the result then -- row already exits
      -- then go on to update row
   else if the result is 1 then
      return "ok"
   else
      return "ERROR=" & the result  --- exit with error
   end if         
   
   
   -- Step 7: Try to Update the Row Instead
   
   put "UPDATE control SET Entry=" & iEntry &  " WHERE Handle="  & iHandle into tSQL
   
   revExecuteSQL conID, tSQL
   
   put the result into view -- for debuging
   
   if the result is not 1 then
      return "ERROR=" & the result  --- exit with error
   end if         
   
   return "ok"
   
end dbData

[FourthWorld]

Why not use custom properties in a stack file?

...
I do recall trying to sort out and use custome properties but I think it was to advanced for me at the time.

Custom props are a snap to work with:

To create a data store:

Code: Select all

create stack "MyData"
set the filename of stack "MyData" to (specialFolderPath("preferences")&"/MyPrefsData.dat")
save stack "MyData"

To put data into the store:

Code: Select all

on SetPref pLabel, pValue
   put specialFolderPath("preferences")&"/MyPrefsData.dat" into tStack
   set the uPrefs[pLabel] of stack tStack to pValue
   save stack tStack
end SetPref

To get data from the store:

Code: Select all

on GetPref pLabel
   put specialFolderPath("preferences")&"/MyPrefsData.dat" into tStack
   return  the uPrefs[pLabel] of stack tStack
end GetPref

Those examples assume the data is for preferences; of course you could set the filename to any valid path, and rename the handlers to something more generic if desired.

Also, it my be useful to note that the file name extension for the stack file doesn't have to be ".rev", it can be anything you like.

[BarrySumpter]

UPDATE:
The code posted immediately above is just psudo code.
That code does not compile and needs a lot of work.
There is a lot of reseach and effort to get that code to work properly.
On reflection I should have ran with the script posted by townsend.
----

[BarrySumpter]

removed by poster as not to distract from the topic.

[BarrySumpter]

This was me being tired n silly asking basic LC scripting questions coming from another large project not in LC. And thinking the psudo code should compile.

[bn]

Hi Barry,

one thing I noticed is that your syntax

Code: Select all

put GetPref ("UserName") into lUserName

is for a function but

Code: Select all

on GetPref pLabel

is a command.

try to change it to

Code: Select all

function GetPref pLabel

and see if it works.

Another thing

Code: Select all

put lUserNameinto text of field txtUserName

is not correct.

it is either

Code: Select all

put lUserName into field txtUserName

or

Code: Select all

set the text of field txtUserName to lUserName

careful with commenting

Code: Select all

'=====  This routine works

this will not work, prepend it with hashes or minus signs

Kind regards

Bernd

[BarrySumpter]

Many thank!

Nope, just me bing tired with VisualBasic code fresh in me head n being silly.
LOL

Humbling to write the least.

[BarrySumpter]

Nope. I have no clue whatsoever:

errors

Code: Select all

SetPref ("UserName", lUserName)

works:

Code: Select all

put SetPref ("UserName", lUserName)  into lstr

works:

Code: Select all

put SetPref ("UserName", lUserName)

[bn]

Hi Barry,

please have a look at:

http://www.runrev.com/newsletter/januar ... p?a=NWS124

SetPref is a command the way you declared it.

Then:
If I want to change a custom property that is an array I always take the custom property into a local variable, then change the value of the array and set the custom property to the local variable.

instead of:

Code: Select all

on SetPref pLabel, pValue
   put gPrefsDat into tStack
   set the uPrefs[pLabel] of stack tStack to pValue
   save stack tStack
end SetPref

try

Code: Select all

on SetPref pLabel, pValue
   put gPrefsDat into tStack
   put the uPrefs of this stack into tArray -- local variable to hold and change array
   put pValue into tArray[pLabel]
   set the uPrefs of this stack to tArray -- update the custom property with the new values
   save stack tStack
end SetPref

if you want to call this command you would code:

Code: Select all

setPref "userName", lUserName

Kind regards

Bernd

[BarrySumpter]

Please disregard this post until I get some rest.
Been looking at it too long.
Might have actually worked but got befuddled yet again with debugging stops.

Nevermind my last post.
Too easy to test the simple script.

The answer is NO.
I don't have th check if the db is there first.
Very cool.

Was I wrong here?
As long as the ide is open my StackData is constant.
But as soon as I close the ide.
And reload the app I loose some data.

Do I need to do something on close.
Maybe save StackData or close StackData]
something about persistance?

Code: Select all

global gPrefsDat

function SetPref pLabel, pValue
   put gPrefsDat into tStack
   set the uPrefs[pLabel] of stack tStack to pValue
   save stack tStack
end SetPref

function  GetPref pLabel
   put gPrefsDat into tStack
   return  the uPrefs[pLabel] of stack tStack
end GetPref

on openStack
   
   initPrefDB
   
   put GetPref ("BarrysItemsOnly") into valBarrysItemsOnly
   
   if valBarrysItemsOnly contains true or false then
      set the hilite of button chkBarrysItemsOnly to valBarrysItemsOnly
   else
      set the hilite of button chkBarrysItemsOnly to false
      put SetPref ("BarrysItemsOnly", the hilite of button chkBarrysItemsOnly)
   end if
   
   put GetPref ("BarrysFolderLocation") into fldrBarrys
   
   if fldrBarrys is empty then
      FindBarrysFolder
   end if
   
   if there is a folder fldrBarrys then   
      set the text of field txtBarrysFolder to fldrBarrys 
   else
      FindBarrysFolder
   end if
   
end openStack

on initPrefDB
      
   create stack "MyData"
   
   -- set the filename of stack "MyData" to (specialFolderPath("preferences")&"/MyPrefsData.dat")
   
   set itemDel to slash  -- start to build current path for SQlite DB
   
   put the effective filename of this stack into realpath  --gets current path
      
   delete last item of realpath --removes .livecode file name on this stack
   
   put  realpath & "/Prefs.dat" into gPrefsDat -- adds SQLite file name to path
   
   set the filename of stack "MyData" to gPrefsDat
   
   -- save stack "MyData"

end initPrefDB

on FindBarrysFolder
   
   if the platform = "MacOS" then
      put specialFolderPath ("cusr") into fldrHome
      set the text of field field0 to "MAC Home Folder: " & fldrHome
   end if
   
   if the platform = "win32" then
      put specialFolderPath ("Documents") into fldrHome
      set the text of field field0 to "Widows Documents folder: " & fldrHome
   end if
   
   put fldrHome into fldrBarrys
   
   put "/Barrys" after fldrBarrys
   
   if the platform = "MacOS" then
      -- put "/HD" before fldrBarrys
      -- put "/" after fldrBarrys
   end if
   if there is a folder fldrBarrys then
      put return after field field0
      put "Barrys Folder found: " & fldrBarrys after field field0
      set the text of field txtBarrysFolder to fldrBarrys
      
            put SetPref ("BarrysFolderLocation", fldrBarrys) -- into fldrBarrysxxxxx
       
   else
      put return after field field0
      put "Barrys Folder NOT found: " & fldrBarrys after field field0
      
      put return after field field0 
      put "Requesting Barrys Folder Location" after field field0 
      
      local tFolder
      
      Repeat until toupper(tFolder) contains toupper("/Barrys")
         answer folder "Please select the Barrys folder you want to work with:"
         if it is empty then exit repeat
         
         put it into tFolder
          
         if there is a folder tFolder then
            if toUpper(tFolder) contains toUpper("/Barrys") then
               set the  text of field txtBarrysFolder to tFolder
               put SetPref ("BarrysFolderLocation", the text of field txtBarrysFolder)
            else
               Answer "the Barrys folder must contain a '/Barrys' folder in it's path" titled "Must have /Barrys in folder path"
               --goto AnswerFolder
            end if
            
         end if
         
      end repeat
      
   end if
   
end FindBarrysFolder