|
Post by kokenge on Feb 19, 2008 17:15:25 GMT -5
A very simple Family Tree System is available. Written in Run Basic, and uses SQLite to maintain the data It maintains information about People and their relationships. Events can be maintained for; Birth Death Marriage Divorce Comments The only limit on the database size it your disk. It has been tested with 27,000 names and 18,000 events. You can produce; 1. Ancestor trees 2. Defendant trees 3. Surname list and stats 4. Interesting stats and graphs There are several ways to sort the file. You can search records using wild cards. This is new and just out of the box. So I'm sure there are bugs. If you like you can send me the bugs, or fix them and let me know the fix. Information and download is here: RunBasic.AndBest.Info/
|
|
|
Post by carlgundel on Feb 20, 2008 0:14:28 GMT -5
Wow, you've been busy!
Thanks!
-Carl
|
|
|
Post by kokenge on Feb 20, 2008 5:15:35 GMT -5
One thing that would really make coding simple is dynamic variables similar to PHP. Something like field$$. That way when you get the list of CSV field names from the DB, all you have to do to fill the variables is a loop through the list. if field$ = "customerNum", then the variable customerNum$ is field$$.
So far looking good.
Thanks....
|
|
|
Post by carlgundel on Feb 20, 2008 10:47:38 GMT -5
One thing that would really make coding simple is dynamic variables similar to PHP. Something like field$$. That way when you get the list of CSV field names from the DB, all you have to do to fill the variables is a loop through the list. if field$ = "customerNum", then the variable customerNum$ is field$$. So far looking good. Thanks...I. Could you make a suggestion about how you imagine such code would look in Run BASIC? -Carl
|
|
|
Post by turbov21 on Feb 20, 2008 10:57:52 GMT -5
One thing that would really make coding simple is dynamic variables similar to PHP. Something like field$$. That way when you get the list of CSV field names from the DB, all you have to do to fill the variables is a loop through the list. if field$ = "customerNum", then the variable customerNum$ is field$$. Are you talking about... <?php
foreach ($results as $key => $val) { $field[$key] = $val; }
?> ...or do you mean... <?php
$start = "andy,male,stargirl";
list($nam, $sex, $fav) = split(",", $start);
echo($fav);
?> ...?
|
|
|
Post by kokenge on Feb 20, 2008 12:47:33 GMT -5
Many places to use it. For example when getting a CSV file from excel and the first entry is the column names. Here is a good example. Instead of naming all the fields in a SQL QUERY, I can just ask for wild card stuff. I don't care how it comes, I only know I want certain things form it. For example
sqliteconnect #mySQLdb, "album.db3" ' Connect to the DB sql$ = "SELECT count(*) as count,* FROM album LEFT JOIN admin ON admin.adminNum = album.adminNum ORDER by album.albName" #mySQLdb execute(sql$) fields$ = #mySQLdb columnnames$() WHILE #mySQLdb hasanswer() result$ = #mySQLdb nextrow$(" |") i = 0 while word$(fields$,i,",") <> "" word$(fields$,i,",")$$ = val(word$(result$,i,"|")) WEND do stuff ....................... WEND
Here I ask for the count(*) as count and all the rest of the data *. When I read the columnnames that went with the query, I can use them to create real variables in the program. I know the first columnname will be "count" Since I ask for word$(fields,i,",")$$, knowing the first field name is 'count' and using the double $$ I get a real variable called 'count$'. Plus all the others. Makes it really tooooo simple.
HTH.. Thanks ...
|
|
|
Post by kokenge on Feb 20, 2008 13:57:23 GMT -5
I noticed my last reply may be a little confusing.
The following code:
field$ = "custNum"
' and the sataement field$$ = "123"
' would be the same as custNum$ = "123"
HTH Sorry about the confusion Have a great day...
|
|
|
Post by carlgundel on Feb 20, 2008 15:53:29 GMT -5
I noticed my last reply may be a little confusing. The following code: field$ = "custNum" ' and the sataement field$$ = "123" ' would be the same as custNum$ = "123" HTH Sorry about the confusion Have a great day... Thanks for that, but it still isn't quite clear to me. How would that database example be coded in RB as it exists now? -Carl
|
|
|
Post by kokenge on Feb 20, 2008 18:16:45 GMT -5
Ok! Here is a real concrete example of code from the family tree system. Here is some code I use now. sql$ = "SELECT person.personNum, person.fatherNum, person.motherNum, person.firstName, person.middleName, person.lastName, person.lnPrefix, person.lastName1, person.ln1Prefix, person.prefix, person.suffix, person.gender, person.age, person.race, person.language, person.notes, event.personNum,event.type, event.eventDate,event.spouseNum, event.qual, event.loc, event.descr, event.city, event.county, event.state, event.zip, event.cntId, event.notes, event.rowId, julianday('now') - event.eventDate as eventDays FROM person LEFT JOIN event ON person.personNum = event.personNum OR person.personNum = event.spouseNum" #mySQLdb execute(sql$) WHILE #mySQLdb hasanswer() result$ = #mySQLdb nextrow$(" |") PpersonNum = val(word$(result$,1,"|")) PfatherNum = val(word$(result$,2,"|")) PmotherNum = val(word$(result$,3,"|")) PfirstName$ = trim$(word$(result$,4,"|")) PmiddleName$ = trim$(word$(result$,5,"|")) PlastName$ = trim$(word$(result$,6,"|")) PlnPrefix$ = trim$(word$(result$,7,"|")) PlastName1$ = trim$(word$(result$,8,"|")) Pln1Prefix$ = trim$(word$(result$,9,"|")) Pprefix$ = trim$(word$(result$,10,"|")) Psuffix$ = trim$(word$(result$,11,"|")) Pgender$ = trim$(word$(result$,12,"|")) Page = val(word$(result$,13,"|")) Prace$ = trim$(word$(result$,14,"|")) Planguage$ = trim$(word$(result$,15,"|")) Pnotes$ = trim$(word$(result$,16,"|")) EpersonNum = val(word$(result$,17,"|")) Etype$ = trim$(word$(result$,18,"|")) EeventDate$ = trim$(word$(result$,19,"|")) EspouseNum = val(word$(result$,20,"|")) Equal$ = trim$(word$(result$,21,"|")) Eloc$ = trim$(word$(result$,22,"|")) Edescr$ = trim$(word$(result$,23,"|")) Ecity$ = trim$(word$(result$,24,"|")) Ecounty$ = trim$(word$(result$,25,"|")) Estate$ = trim$(word$(result$,26,"|")) Ezip$ = trim$(word$(result$,27,"|")) EcntId$ = trim$(word$(result$,28,"|")) Enotes$ = trim$(word$(result$,29,"|")) ErowId = val(word$(result$,30,"|")) Edays = val(word$(result$,31,"|")) do stuff........ WEND
Here is what it would look like with dynamic variables sql$ = " SELECT *, julianday('now') - event.eventDate as eventDays FROM person LEFT JOIN event ON person.personNum = event.personNum OR person.personNum = event.spouseNum" #mySQLdb execute(sql$) fields$ = #mySQLdb columnnames$() WHILE #mySQLdb hasanswer() result$ = #mySQLdb nextrow$(" |") i = 0 WHILE word$(fields$,i,",") <> "" word$(fields$,i,",")$$ = word$(result$,i,"|") i = i + 1 WEND do stuff ..... WEND
If I ever change the sql$ in xample 1, I have to supply the variable names in the sql$, then count where they are in word$(result$,?,"|") to match. Trying to use the wild card (*) is not realistic and is a crap shoot at best. In the second example, I can change it all I want and the wild card (*) works regardless. I don't care where the variables are located. The columnnames$() will tell me the names and match the locations, and make and fill the variables using $$. All the time spent on the first sql$, writing down names, and matching them goes away. Thanks..
|
|
|
Post by turbov21 on Feb 20, 2008 18:22:16 GMT -5
Thanks for that, but it still isn't quite clear to me. How would that database example be coded in RB as it exists now? -Carl I had to look this up, so I may be doing it wrong... nam$ = "Andy" nam$$ = "'s favorite icecream is vanilla."
' Gives you "Andy" print nam$
' Gives you "'s favorite icecream is vanilla." print nam$$
' Gives you "'s favorite icecream is vanilla." print Andy$
' Gives you "Andy's favorite icecream is vanilla." print nam$ + Andy$ The idea is that the value, I think, would also become a variable.
|
|
|
Post by turbov21 on Feb 20, 2008 18:53:13 GMT -5
What about something like this... ' TODO global DataBaseName$, DataBasePath$, Dlimit$ DataBaseName$ = "todo2.db" Dlimit$ = ", "
sqliteconnect #td, DataBaseName$ sqle$ = " select * from posts where userid = 'andy' and done = 0 order by pid desc"
#td execute(sqle$)
while #td hasanswer() row$ = #td nextrow$(Dlimit$) print ColVal$("post", row$, #td COLUMNNAMES$()) wend
#td disconnect()
function ColVal$(f$, r$, c$) cnt = 1 while word$(c$, cnt, Dlimit$) <> "" if f$ = word$(c$, cnt, Dlimit$) then exit while end if cnt = cnt + 1 wend
ColVal$ = word$(r$, cnt, Dlimit$) end function You could put ColVal$ in a library and use... run "ColVal", #cv ...instead of loading it into every program. EDIT: No, that doesn't solve your problem all the way. But if you maybe combined that and this: runbasic.wikispaces.com/rbHash
|
|
|
Post by carlgundel on Feb 20, 2008 21:35:16 GMT -5
Ok! Here is a real concrete example of code from the family tree system. Here is some code I use now. Okay, I get it. Let me think about this. I am not keen on adding syntax from other languages unless I really have to. I've already polluted BASIC with the #handle message(a, b, c) syntax. The less syntax the better. I'm more likely to add some sort of utility method to the database accessor to do what you need. -Carl
|
|
|
Post by kokenge on Feb 21, 2008 4:27:07 GMT -5
Ya! I agree with you. However working with something simple like the Family Tree is not a big problem. It's when you work with larger systems that it gets really messy. For example working with systems with 3000+ separate DB tables, and doing joins with 5 or 10 of those tables gets time consuming trying to get everything to match.
There are other problems besides databases. I just loaded a flat CSV file with over 50 fields. The first line of the file contained the field names. I wrote a program just to generate the code to use in the conversion. It also happens in XML.
So somehow it would be nice if it worked in other places besides DB's.
Thanks .... Have a great day..
|
|
|
Post by kokenge on Feb 21, 2008 7:22:12 GMT -5
Well! Maybe you don't have to do anything.. I just wrote this and it works fine. Uses in memory SQLite to x-ref the field names and positions. One time you need to get the columnames$(). For each row passed you [getData] into memory. Then pull it out using dbData$("name") with the name you want. No more changes to get data every time you change your SELECT statement
Have a great day...
[pre] goto [main]
' -------------------------------------- ' create in memory of db names and pos ' --------------------------------------
[fillNames] firstTime$ = "Y" sqliteconnect #MEMdb, ":memory:" sql$ = "CREATE TABLE dbinfo ( fieldName text, pos int, data text)" #MEMdb execute(sql$) sql$ = "CREATE UNIQUE INDEX fn ON dbinfo(fieldName); CREATE UNIQUE INDEX pos ON dbinfo(pos)" #MEMdb execute(sql$) fields$ = #mySQLdb columnnames$() i = 1 WHILE word$(fields$,i,",") <> "" a$ = trim$(word$(fields$,i,",")) sql$ = "INSERT INTO dbinfo VALUES ('";a$;"',";i;",'')" #MEMdb execute(sql$) i = i + 1 WEND RETURN
[getData] result$ = #mySQLdb nextrow$(" |") i = 1 WHILE word$(result$,i,"|") <> "" a$ = trim$(word$(result$,i,"|")) sql$ = "UPDATE dbinfo set data = '";a$;"' WHERE pos = ";i #MEMdb execute(sql$) i = i + 1 WEND RETURN
FUNCTION dbData$(name$) global #MEMdb sql$ = "SELECT data FROM dbinfo WHERE fieldName = '";name$;"' LIMIT 1" #MEMdb execute(sql$) rows = #MEMdb ROWCOUNT() 'Get the number of rows if rows = 0 then dbData$ = "?" else dta$ = #MEMdb nextrow$("|") dbData$ = trim$(word$(dta$,1,"|"))
end if END FUNCTION ' ============================= ' main code ' ============================= [main] sqliteconnect #mySQLdb, "ftree.sqlite3" ' Connect to the DB
sql$ = " SELECT * FROM event LIMIT 10" #mySQLdb execute(sql$) if firstTime$ <> "Y" then gosub [fillNames] WHILE #mySQLdb hasanswer() gosub [getData]
type$ = dbData$("type") personNum = val(dbData$("personNum")) eventDate$ = dbData$("eventDate")
print personNum;" ";type$;" ";eventDate$ WEND WAIT END [/pre]
|
|
|
Post by carlgundel on Feb 21, 2008 10:48:02 GMT -5
There are other problems besides databases. I just loaded a flat CSV file with over 50 fields. The first line of the file contained the field names. I wrote a program just to generate the code to use in the conversion. It also happens in XML. So somehow it would be nice if it worked in other places besides DB's. Yeah but that kind of indirection can make your head spin. BASIC isn't supposed to do that to your poor head. -Carl
|
|