| # | User | Message | Date |
| 189 | Ashley | Kai: use "reduce ['next ...] Claude: I will, but only when R3 goes Beta. | 29-Mar-09 8:29 |
| 188 | Claude | hi ashley, i would like to know if you would port rebdb to R3 ? | 18-Feb-09 13:57 |
| 187 | Kai | Ashley - how do I overcome this problem: I need to reduce the record block prior to db-inserting it because it contains sub-blocks. I would like to use 'next inside for autoincs, however..... | 18-Jan-09 7:24 |
| 186 | ManuM | . . | 23-Dec-08 8:13 |
| 185 | BrianH | The REBOL driver is a little smaller than RebDB, but there is a dll that is about 200k. The databases are smaller, I think. Look in the SQLite group here for details. | 15-Oct-08 1:19 |
| 184 | amacleod | 225K..small enough | 15-Oct-08 1:19 |
| 183 | amacleod | How much is involced in using sqLite? And how large is it? | 15-Oct-08 1:16 |
| 182 | BrianH | Entire DB is loaded in memory. If that is a problem, use SQLite. | 15-Oct-08 1:14 |
| 181 | amacleod | Is RebDB disk based or is the enitre DB loaded into memory? | 15-Oct-08 1:13 |
| 180 | Ashley | Fixed. | 8-Oct-08 22:26 |
| 179 | Ashley | Ah, I just noticed all the "(br)" entries ... that'll teach me for changing make-doc versions! ;) | 8-Oct-08 21:55 |
| 178 | Claude | thank you very much BrianH | 8-Oct-08 18:44 |
| 177 | BrianH | Don't expect it to be fast though. | 7-Oct-08 20:57 |
| 176 | BrianH | I think the find clause here might work for you: http://www.dobeash.com/RebDB/sql-guide.html#section-4 | 7-Oct-08 20:55 |
| 175 | Claude | hi, how a can do a select with "like " => select * from table where name like 'RA%' | 7-Oct-08 19:49 |
| 174 | btiffin | Whoa. 2.0.3; that was a pleasant surprise. Thanks Ashley. | 8-Sep-08 13:05 |
| 173 | Louis | Ok, thanks. That make the choice easier. | 8-Sep-08 8:20 |
| 172 | Ashley | Depends on how much data and how much complexity you need. RebDB is good for under a million rows where the column data is short (i.e. not storing 1MB strings). SQLite is a better choice for lots/big rows or where you need non-trivial joins/views. I'd use RebDB for simple apps like a "Contacts" DB, and SQLite for anything requiring half a dozen or more tables. | 7-Sep-08 23:01 |
| 171 | Louis | Ashley, which is easiest to use with RebGUI? RebDB or your sqlite.r? Also, how do you decide which one you will use for a particular project? | 7-Sep-08 16:03 |
| 170 | Ashley | 2.0.3 released. Release notes can be found here: http://www.dobeash.com/RebDB/release-notes.html | 7-Sep-08 13:18 |
| 169 | PeterWood | Thanks no rush on my part. | 6-Sep-08 5:42 |
| 168 | Ashley | Same here ... and yes that behaviour is intentional. 2.0.3 is stable, it just requires some doc updates. I'll see about releasing it in a day or two. | 6-Sep-08 5:27 |
| 167 | PeterWood | Sorry about the formatting..as you might guess I'm running AltME on a Mac. | 6-Sep-08 4:57 |
| 166 | PeterWood | I find that the db/base-dir is getting set to the directory from which the db.r is loaded. Is this behaviour intentional? >> what-dir == %/Users/peter/Desktop/RebDBTest / >> do %~/Code/Library/Rebol/RebDB-202/db.r Script: "RebDB server" (14-Jan-2006 ) == none >> what-di r == %/Users/peter/Desktop/RebDBTest / >> db/base-di r == %/Users/peter/Code/Library/Rebol/RebDB-202/ | 6-Sep-08 4:55 |
| 165 | PeterWood | Ashley are you planning to release 2.0.3 as a production version? | 6-Sep-08 3:38 |
| 164 | Graham | This ftp based storage is kind of neat. I have to update my users' tables, and need to provide a script that updates their firebird tables. I can embed the data in the update script, but this sounds a much better idea. | 21-Mar-08 9:01 |
| 163 | JohanAR | That will be good enough for me, since I just want to synchronize each row in a table every now and then. | 18-Mar-08 5:46 |
| 162 | Ashley | Table location is a global setting, so no. You can run two copies of %db.r (with different base-dir settings), but they won't share the same memory space. | 17-Mar-08 21:17 |
| 161 | JohanAR | Is it possible to have some tables locally and some on the ftp? Maybe I can make a copy of "db" and use it in parallell? | 17-Mar-08 16:25 |
| 160 | Ashley | Correct, db/base-dir: ftp://user:pwd@ftp.site.com/public_html/ db/lines?: false The second line will help minimize the amount of traffic by turning line breaks off. | 17-Mar-08 6:38 |
| 159 | btiffin | RebDB does have a built in client / server mode if that will help. db-client.r can talk with SQL.r from just about anywhere. Or try set path with an ftp url, it may just work, never tried but looking at the code and the use of base-dir it seems like a reasonable chance of success. | 17-Mar-08 6:07 |
| 158 | JohanAR | Works great.. Thanks! You know, a really cool feature for RebDB would be to use tables stored on an ftp instead of locally. I think it would be possible since it's RAM based and you control your commits, but I could be wrong :) Now I'll have to sync my database manually every time I change computer | 17-Mar-08 5:48 |
| 157 | Ashley | You are correct, with a standard SQL DB a delete or update statement without a where clause will affect every row in the table. With RebDB you can achieve the same thing by providing a where clause as follows: where [rowid > 0] and for delete operations don't overlook the truncate (or drop) functions. | 16-Mar-08 20:51 |
| 156 | JohanAR | I've only used MySQL a little, and quite a while ago, but I _think_ update (without where) affected the entire table there. Could be wrong though :) Anyhow, I really love RebDB (And RebGUI also). It's really easy to use, and my program would probably be alot messier if I wrote my own data management functions :P | 16-Mar-08 9:15 |
| 155 | Ashley | The "logic" is that db-update (and db-delete) are logical companions to both db-lookup and db-select ... so they have to operate comfortably in either mode (key or predicate). It is quite possible to have a db that *only* operates in "key" mode and *never* uses predicates! ;) | 16-Mar-08 9:10 |
| 154 | JohanAR | thanks alot mate! | 16-Mar-08 7:51 |
| 153 | btiffin | That one got me too. You don't use /where if you are using a key value db-update database field "Value" 3 will hit the first record with key 3. "key "in this sense means first field in the order of the column names. A block without /where would be implied [value-to-compare-to-first-field value-to-compare-to-second-field-value...] It can be a little weird when there are duplicates and you don't use secondary keys to filter. There is more to it or course. But I always use /where syntax. :) | 15-Mar-08 22:52 |
| 152 | JohanAR | ie anyone wanna explain? :) | 15-Mar-08 19:48 |
| 151 | JohanAR | I don't think I understand the purpose of "predicate" in db-update, where you don't wan't to use /where.. | 15-Mar-08 19:48 |
| 150 | JohanAR | no problems.. didn't know about extract so now I'm using that in conjunction with random/only :) | 15-Mar-08 19:47 |
| 149 | Ashley | If RebDB supported this then it would be no more efficient than doing it in your own code (i.e. RebDB would have to retrieve the column data required to sort on and then go through another process to discard it from the result set). Doing this in your own code is usually rather simple, as in: foreach [col-i-want col-to-discard] RebDB-query [ do something with col-i-want ] or just using 'extract in simpler cases. | 14-Mar-08 22:11 |
| 148 | JohanAR | I think it's a little inconvenient that I can't order by a column that I'm not selecting. For example "db-select/order [name] mydb lasttime" fails :( Is it possible to work around this, so I don't have to select both name and lasttime, because it clutters my code? | 14-Mar-08 13:04 |
| 147 | Brock | Thought it was about time I started trying some of the great work done by the community and stop relying on the slightly less than friendly flat-file data storage I've been using. RebDB is very interesting so far. | 23-Jan-08 3:10 |
| 146 | Brock | Gotcha, thanks. That's actually where I go the link, just didn't read far enough to see that it was pre-release. | 23-Jan-08 3:06 |
| 145 | Ashley | Check my message above from 13-Apr-2007 ;) | 23-Jan-08 2:33 |
| 144 | Brock | Ashley, just noticed the RebDB on your Dobeash website is not the latest version. | 23-Jan-08 1:51 |
| 143 | Michael | Of course. I need to drink more coffee. Thanks, Ashley. | 20-Jan-08 22:44 |
| 142 | Ashley | One or more of your values is a string. Check the contents of my-table.dat | 20-Jan-08 22:34 |
| 141 | Michael | Ashley, what I am doing wrong here? (I'm trying to sum the column "distance" below): SQL> select distance my-table distance -------- 1 3 7 SQL> select count distance my-table Count ----- 3 SQL> select sum distance my-table script cannot-use | 20-Jan-08 19:03 |
| 140 | GiuseppeC | Ok, I suppose I will have to learn both. RebDB for very simple projects and SQL for large ones. After Christmas I will buy Rebol/Pro and when my project will be ready the whole suite. | 13-Dec-07 22:39 |
| 139 | Ashley | SQLite supports joins, RebDB doesn't.
SQLite driver requires /PRO, RebDB doesn't.
SQLite is disk-based, RebDB is RAM-based. I use RebDB for small, simple, single-user apps; and SQLite for larger multi-user apps. | 13-Dec-07 22:20 |
| 138 | GiuseppeC | Hello, I have a question: which are the advantages of SQLLite over RebDB ? I have to chose something to lear for my projects. | 13-Dec-07 19:50 |
| 137 | btiffin | Thank you sir. That's a way round. :) | 3-Jul-07 6:03 |
| 136 | Ashley | db/base-dir: %data/ | 3-Jul-07 5:53 |
| 135 | btiffin | Ashley; Can we get rid of the %rebgui.log and RebDB %.log database replay conflict? I've taken to just deleting the %rebgui.log on app startup, which is probably not a good habit. Preferred would be a rename of the database replay logs to something less 'conflicting', say .ldb or some such. .LOG is too easy to trip over during app (especially widget) development. But I'll accept a nice short No, too. :) | 3-Jul-07 1:10 |
| 134 | Ammon | Thanks Ashley! I kind of got distracted from what I was doing, but I'm working on it some more and your fixes work. | 28-Apr-07 21:58 |
| 133 | Anton | "Prevayler" | 16-Apr-07 1:40 |
| 132 | Pekr | Doc, yes, it was Prevailer ... | 15-Apr-07 16:11 |
| 131 | Dockimbel | Pekr, you're thinking about "Prevalence" ? http://www.advogato.org/article/398.html | 13-Apr-07 21:43 |
| 130 | Robert | Looks interesting. I will see how this works with SQLite & RebService... | 13-Apr-07 11:18 |
| 129 | Pekr | Maybe an interesting project? http://sqlrelay.sourceforge.net/ | 13-Apr-07 10:42 |
| 128 | Pekr | there is the concept called, uh, I forgot, java persistent database, few hundred of lines of code .... | 13-Apr-07 10:37 |
| 127 | Pekr | locking? why not? You can distribute ticket :-) | 13-Apr-07 10:35 |
| 126 | Henrik | what about locking? | 13-Apr-07 10:32 |
| 125 | Pekr | the question is, if RIF comes or not. | 13-Apr-07 8:14 |
| 124 | Pekr | I can imagine solving even sharing problem - simply to queue requests .... | 13-Apr-07 8:14 |
| 123 | Pekr | ah, high hopes :-) | 13-Apr-07 8:14 |
| 122 | Ashley | I'm hoping R3 will make it and the SQLite driver redundant ;) | 13-Apr-07 8:03 |
| 121 | Pekr | interesting to know, someone still uses RebDB ... although in-memory and not shareable, it is cool Rebol design! | 13-Apr-07 8:00 |
| 120 | Ashley | Think I've nailed the 3 spots /only is required: http://www.dobeash.com/RebDB/RebDB-203.zip Note that this pre-release includes a few other untested changes; including /joins and /replaces refinements to db-select (refer discussion from 12-Feb-2006 onwards for explanation). | 13-Apr-07 7:59 |
| 119 | Ammon | That's what I thought so I started poking around but I didn't find the place to add the /only to... | 13-Apr-07 4:20 |
| 118 | Ashley | Sounds like a missing /only refinement on an insert. I'll look at this later tonight ... odd how no-one else has hit this in over 2 years ... maybe not many folks store blocks! ;) | 13-Apr-07 3:59 |
| 117 | Ammon | Ashley, I noticed some strange behavior with db-select. If you do NOT supply the /where argument then blocks are inserted into the result correctly but if do they are inserted inline. Example: >> db-describe tags == [id integer! tag string! files block!] >> db-select [id files] tags == [1 [3]] >> db-select/where [id files] tags [tag = "t"] == [1 3] I've spent about an hour trying to find where to fix this in db.r but I'm not seeing it. Any ideas? | 13-Apr-07 3:30 |
| 116 | xavier | it works ashley , thanks for everything , u found the solution. thanks to that i normally could be able to access to rebdb from my encaped program. I ll do trial to see if i can access to the db.r script from the network but normally this could be THE solution to my problem. Thanks for your very good job and for the information :)). | 15-Nov-06 23:39 |
| 115 | Ashley | RebDB uses features introduced in core 2.6 (case, unless, etc) so the solution to your problem is to use an SDK that supports Core 2.6 and later. Calling %db.r from your encapped script will use the SDK version of REBOL, whilst running it from file explorer will use whatever version of REBOL is associated with .r files. Latest SDK builds are located here: http://www.rebol.net/builds/sdk/ You should then be able to include %db.r directly in your encapped script.r. | 14-Nov-06 22:38 |
| 114 | Anton | A guess is that the path %test/param.par is not being resolved correctly when encapped. Try adding. probe what-dir and see if the results are different encapped and normally. | 14-Nov-06 15:12 |
| 113 | xavier | hello, i d like to know if its possible to call db.r script from an encaped program. It says that core 2.6 is needed and if i try to remove the security it fails. what i try to do is the following :
rebol [] #include %/C/Documents%20and%20Settings/xavier/rebApps/spikes/mezz.r; #include %/C/Documents%20and%20Settings/xavier/rebApps/spikes/prot.r #include %/C/Documents%20and%20Settings/xavier/rebApps/spikes/view.r test: request-file write %test/param.par test do to-rebol-file read %test/param.par probe db-select * patients ask "" in this example the file u are supposed to point is db.r. When it s not encaped it work, not when it is. if someone has an idea ... | 14-Nov-06 15:01 |
| 112 | Normand | Thanks for those suggestions. If I finish it, I'll feedback with it. | 23-Jun-06 16:26 |
| 111 | Ashley | Another option is to select the rowid(s) you are after: id: db-select/where rowid table [...] then perform the operations you want: db-update/where ... compose [ ... (id) ...] BTW, thanks for the feedback on both RebDB/RebGUI ... always good to know where folks are having both success and problems. More demos/tutorials are on the ever growing ToDo list. ;) | 23-Jun-06 3:41 |
| 110 | BrianH | If records aren't inserted often you can use a date/time for the field - otherwise use a number that you would increment. Keep in mind that if you do a lot of insertions and deletions, a number field could end up overflowing unless you occasionally compact the numbers. | 23-Jun-06 3:37 |
| 109 | BrianH | It sounds like you should add your own field for insert order. I don't think that RebDB keeps the records in the order it inserts them. Even its web site says that it sorts the records when selecting - this isn't Access you know. If you want to keep some information in the database, put it there. | 23-Jun-06 3:33 |
| 108 | Normand | Well. Is there any way to obtain the rowid (physical row order number) of a selected entry, or deleted or updated entry? I am trying to devise a way to navigate into the database, from the initial point of a query or of an update. But with delete operations, the first column, id, is not allways in sequence. >> db-select id bottinDB == [1 2 3 4 7 8 9 10] >> db-select rowid bottinDB == [1 2 3 4 5 6 7 8]. And, as much as I can, I want to keep the historical order of entry, as it gives me information. It may be usefull to know that I did read a book or meet a person before another. The rowid gives a hint to that. All and all, parametrizing the update and obtaining the rowid are stoping me to complete my small everyday application. By the way, I do not see any example of 'demo' applications with RebDB and RebGui. A little help with mine could give birth to one. I should say that, design wise, RebGui is a great decision, to favor simplicity. You adress there some needs never covered by other systems: the myriads of simple applets everybody needs but were unable to ask. After a couple of weekends, I have got 400 lines of codes and am almost done. And I am not a programmer. So I figure out how efficient it is for you guys. Thanks. | 23-Jun-06 3:09 |
| 107 | Ashley | db-update returns an integer! indicating how many rows where updated. Rowid is the physical row sequence computed dynamically at query execution. You can update multiple columns at once by providing a block of column names followed by a block of values (but number of values must match number of columns). If you are updating a large number of column values it is probably more efficient (and easier to code) to delete then re-insert the row. | 23-Jun-06 2:44 |
| 106 | Normand | Thanks for the 'find' explanation. As for the update, the formula 'db-update/where names name "Jane" [rowid = 1]' is a rather simple case. I am trying to update a 39 columds database splitted into 3 tab-panels. So I figure that there is a way to parametrize the update and to obtain back, in return, the rowid where RebDB placed it, as the rowid is not allways the same as the saved id, as shown in >> db-select id bottinDB == [1 2 3 4 7 8 9 10] >> db-select rowid bottinDB == [1 2 3 4 5 6 7 8]. | 23-Jun-06 2:31 |
| 105 | Ashley | Following works fine: db-update/where names name "Jane" [rowid = 1] and reversing the find is saying: find "e" name -> where name appears in "e" ... so [] is corect find "Foe" name -> where name appears in "Foe" ... so ["Foe" ...] is correct "is this behaviour a specification of RebDB" ... this is how the REBOL find word works. Type "help find" at the REBOL console for more information. | 23-Jun-06 2:19 |
| 104 | Normand | And about find: db-select/where * exDB [find name "e"] Expectedly, any letter of the string searched retrieves it: >> db-select/where * exDB [find name "e"] == ["Foe" "Joe" "1991-01-01" "514-480-8080"] >> db-select/where * exDB [find name "oe"] == ["Foe" "Joe" "1991-01-01" "514-480-8080"] >> db-select/where * exDB [find name "Foe"] == ["Foe" "Joe" "1991-01-01" "514-480-8080"] But, reverting column and search-string, is this behaviour a specification of RebDB, or is it simply luck? >> db-select/where * exDB [find "e" name] == [] >> db-select/where * exDB [find "Foe" name] == ["Foe" "Joe" "1991-01-01" "514-480-8080"] | 22-Jun-06 19:48 |
| 103 | Normand | I also tried: >> db-update/where bottinDB (get fieldsnames) (get fvalues) compose ['rowid = (to-id)] ** Script Error: db-update expected columns argument of type: word block >> do [ db-update/where bottinDB (get fieldsnames) (get fvalues) compose ['rowid = (to-id)] ] ** Script Error: db-update expected columns argument of type: word block >> reduce [ db-update/where exDB ( get exColumns) (get exValues) compose [rowid = to-id] ] ** Script Error: db-update expected columns argument of type: word block I am getting short of inspiration. | 22-Jun-06 19:37 |
| 102 | Normand | I am trying to update my RebDB database with a level of indirection, as I am using a block of 39 colunms names and the corresponding 39 entry-fields from ReBGui. Shortening those blocks for the example's sake, I do try to update the DB with the following command: Let's say that exColumns: [name surname dob phone] and exValues: ["Doe" "Jane" "1990-01-01" "418-840-4040"]. Then >> db-update/where exDB (exColumns) (exValues) compose ['rowid = (to-id)] do not work, nor variants I know. Proof: >> db-create exDB exColumns == true >> db-insert exDB ["Foe" "Joe" "1991-01-01" "514-480-8080"] == true >> db-commit exDB == true Then what is a working update, as those do not work with indirection in column & values spec and rowid? to-id: 1 Variant1 >> db-update/where exDB exColumns exValues compose [rowid = to-id] == ** User Error: Invalid or duplicate column Variant2 >> db-update/where exDB (exColumns) (exValues) compose [rowid = to-id] == ** Script Error: db-update expected columns argument of type: word block Variant3 >> do [ db-update/where exDB exColumns exValues compose [rowid = to-id] ] == ** User Error: Invalid or duplicate column Variant4 >> reduce [ db-update/where exDB exColumns exValues compose [rowid = to-id] ] == ** User Error: Invalid or duplicate column Variant5 >> reduce [ db-update/where exDB (exColumns) (exValues) compose [rowid = to-id] ] == ** Script Error: db-update expected columns argument of type: word block The more I go, the more I am frustrated into the word/value distinction. What is the resiliently simple answer to my show stopper? | 22-Jun-06 19:09 |
| 101 | Ashley | Almost there, needs to be in the form: db-select/where * my-table [find string-column "string"] | 19-Jun-06 2:12 |
| 100 | Normand | Another newbee one: db-select can retreive the rows on multiples values like the query db-select/where * noms [any [nom = 'leclerc prenom = 'marion]] but can I query a column's values to find all rows where a column contains a substring, like the query db-select/where * noms [find ["lec"] nomprenom] ? This last one is not the answer. What would be the good query for such a purpose? | 18-Jun-06 2:29 |
| 99 | Maxim | but you do need to do two queries beforehand... which is not exactly a join.. but anyhow you do end up with a block containing interleaved data from two tables ... and its pretty quick. | 7-Apr-06 15:49 |
| 98 | Maxim | which can do some of the joining. | 7-Apr-06 15:47 |
| 97 | Maxim | I have a merge function. :-) | 7-Apr-06 15:46 |
| 96 | Pekr | other than that - I like RebDB very much and I regard it being one of the best rebol tools so far .... | 7-Apr-06 15:46 |
| 95 | Maxim | the only drawback is that sql use needs ticks for table names. acter using it for a while I actually like this better, cause it clearly identifies what is a reserved word and was is an argument, within the statements. | 7-Apr-06 15:46 |
| 94 | Pekr | that is not the problem of RebDB imo ... e.g. me and my friend stopped using it because of missing 'join and because Ashley stated, that instead of reinventing the wheel (RIF based on-disk version), he suggests SQLite .... | 7-Apr-06 15:46 |
| 93 | Maxim | it allows us to use rebdb by supplying table names within values, instead of having to supply them directly within code. | 7-Apr-06 15:44 |
| 92 | Maxim | is anyone here interested in a contextualised version of rebdb ? I mean one with everything hidden within an object, and most functions fixed to support it? | 7-Apr-06 15:43 |
| 91 | Ashley | help db-update is a good start. ;) For a full list of db functions just enter "help db-" at the console. | 5-Apr-06 7:37 |
| 90 | Normand | update tbtst set [id name surname] to [2 "Leclerc2" "Normand2"] where [rowid = 2] | 4-Apr-06 10:46 |
| 89 | Normand | Thanks that works. Can someone provide the syntax in db.r of the following command in sql.r. I am looking for the function to update all or some 20 fields, loading only db.r in my interface. The manual is sparse, so I tried it in sql.r but it does not give the equivalent function. Is there an echo to toggle to have such translation? It would be helpfull to later to discover db.r. The function I tried is : | 4-Apr-06 10:46 |
| 88 | Ashley | >> db-select/where * prospectDB compose [rowid = (pickno)] | 2-Apr-06 21:48 |
| 87 | Normand | >> db-select/where * prospectDB [rowid = (pickno)] ** Script Error: Cannot use subtract on paren! value | 2-Apr-06 20:21 |
| 86 | Normand | The Parenthesis does not work too: | 2-Apr-06 20:21 |
| 85 | Normand | Also should I select a row before to delete it or update it? Reading the Quick start guide, il may be implicit from the examples. | 2-Apr-06 19:51 |
| 84 | Normand | >> pickno: 7 == 7 >> db-select/where * prospectDB [rowid = :pickno] ** Script Error: Cannot use subtract on get-word! value ** Where: switch ** Near: insert tail buffer copy/part skip >> :pickno == 7 >> db-select/where * prospectDB [rowid = 7] == [ 7 "7-02-2006" "Nom-7" "Prénom-7" "Québec" "Cie-7" "Référence: US" "Conjoint-7" "Enfant-7" "Bur: 418-845-7" "Rés: 418-845-7... >> | 2-Apr-06 19:49 |
| 83 | Normand | Is it possible to parametrize the rowid? If I read the error message, it seems not : | 2-Apr-06 19:48 |
| 82 | Thør | . | 2-Apr-06 8:55 |
| 81 | Maxim | and btw... good job on rebdb... for what it does, I like it a lot. | 10-Mar-06 3:11 |
| 80 | Maxim | I understand... which is why I noted, it could be an option... cause although substitution variables are closer to SQL, expression embedding within parenthesis is quite familiar in rebol ... but in any case, We can all just wrap the rebdb calls within our own I guess ;-) | 10-Mar-06 3:09 |
| 79 | Ashley | See my post from 12-Feb. The answer to "noise" is substitution variables (as it's a form of expression more familiar to long time SQLers). Also, I've never liked the idea of performing an operation (be it 'compose, 'reduce, 'copy, etc) that takes away that choice from the coder ... someone may use statements that *never* need to be composed and they shouldn't have to carry that cost just because some else needs it. | 9-Mar-06 23:02 |
| 78 | Maxim | wrt simplyfing the use of "noise" ... why not just call compose/deep by default within the 'execute call of the client on ALL sql calls? it could be a global option and IMHO the use of parens within the code is quite obvious and is used in many dialects. and its less cumbersome than to add the reduce word in your code, a string and then variables out of context of the sql statement. | 9-Mar-06 22:42 |
| 77 | Maxim | ashley, I'm starting to use rebdb this week for a very cool segmented code versioning project. | 9-Mar-06 22:32 |
| 76 | Coccinelle | sql-protocol don't need these heavy use of 'compose, 'join, 'reduce if you use the dialect. Something like this work :
var1: "Marco"
insert my-db [select * from my-table where name = var1]
my-result: copy my-db Another example to ilustrate this : insert my-db [select * from my-table where name like rejoin ["*" var1 "*"]] This is only if you use the dialect to query the database. If you use the standard SQL string, you need to compose the query. | 13-Feb-06 16:39 |
| 75 | Pekr | Ashley, I have the question from Bobik - he asks, if proposed changes will be implemented anytime soon? | 13-Feb-06 14:21 |
| 74 | Ashley | Not quite ... see SQLite group ;) | 13-Feb-06 11:03 |
| 73 | Pekr | well, as RIF is not coming, we are still far away from non-memory -only version, right? | 13-Feb-06 10:59 |
| 72 | Pekr | ok, thanks .... | 13-Feb-06 10:58 |
| 71 | Ashley | Quick hack to get this one common case working [well]. Proper solution (full JOIN support) is a major redesign. | 13-Feb-06 10:58 |
| 70 | Pekr | Ashley - is that a techical limitation of RebDB design, or more a parser problem, that aggregates (master-detail) are supported only to second level? | 13-Feb-06 10:34 |
| 69 | Pekr | I vote for 'appends keyword ... | 13-Feb-06 9:18 |
| 68 | Ashley | Perhaps another keyword, 'appends, that inserts the matching label after the column that would otherwise have been replaced. Or, allow column names to be specified multiple times in the select clause with replacements occurring from the tail, so: select [id id date] from orders replaces id with name might return: 1 "Bob" 3-Jan-2006 2 "Fred" 4-Jan-2006 etc | 12-Feb-06 22:30 |
| 67 | Pekr | Ashley - why 'replace? Do you want to really replace id with looked-up value? I can imagine having some usage for 'id, especially in the case where 'id is of some meaning. I know it should not be, but e.g. some ppl might use, for companies db, companie's registration number, which is not anonymous id, and could be further used in the resultset ... | 12-Feb-06 14:37 |
| 66 | Pekr | here's some interesting reading on mySQL optimisations - http://dev.mysql.com/doc/refman/5.0/en/optimization.html | 12-Feb-06 13:57 |
| 65 | Sunanda | Traditional with embedded SQL, the technique is to use "host variables" which start with a colon:
sql reduce “select * from table where [all [col1 = :var1 col2 = :var2 ]]”
And you'd magically replace :var1 with the value of var1. Which is almost exactly the behaviour you'd expect from :var1 in REBOL too. If you insist that that host variables always have a space before and after, that makes the whole substitution process a fairly simple parse operation. | 12-Feb-06 1:15 |
| 64 | Ashley | Thanks guys, I've had a good look at both implementations and I've got ideas from both for a future full JOIN implementation; but at the moment my master/detail code has come along nicely. I've now enhanced the db-select function to accept statements in these additional forms: select * from master joins [select * from details where &id] on id select * from master joins [select * from details where [all [master-id = &id master-date = &date]] on [id date] which works exactly like a normal join with the following differences: a) It can only join one table to another b) Detail columns are always joined to the right of master columns c) Table.column prefixes are not supported so all columns in the join must be uniquely named Apart from that you get all the benefits of db-select (can replace * with specific column combinations, order and group by on the final result set, etc) *and* it's significantly faster than even the raw REBOL code example I gave before (as the SQL is parsed once within db-select and all loop sub-selects are done in-line). I've also implemented “lookups” with the following form: select * from table replaces id with name select * from table replaces [id-1 id-2] with [table-1 table-2] which performs a highly optimized db-lookup for each replaced value, but has the following restrictions: a) The lookup expects lookup tables in the form [id label other-column(s)] b) Only single-key lookups are supported c) A lookup that fails will replace the column value with none! I'm now in the process of benchmarking these changes against sqlite to see where the bottlenecks (if any) are. Feedback on the design decisions is welcome. While I was doing this, I was once again reminded how cumbersome it is to construct SQL statements (not just for RebDB, same goes for the other SQL protocols), as the heavy use of 'compose, 'rejoin, etc adds noise that reduces legibility. The design goal is to provide alternatives to: sql compose/deep [select * from table where [all [col1 = (val1) col2 = (val2)]]] so for a start the 'sql function should probably accept a string, to allow: sql join “select * from “ table type constructs; but this doesn't make the first example easier. So how about the 'sql function accept a block containing a string statement followed by a number of substitution variables, as in: sql reduce [“select * from table where [all [col1 = &1 col2 = &2]]” val1 val2] which makes things a bit more readable (and shortens the expression if longer word names are used multiple times). So the two questions here are: a) Is this a good idea? b) If so, what substitution character (& % $ @ other) will cause the least conflict with REBOL and/or SQL? | 12-Feb-06 1:02 |
| 63 | Gabriele | ashley: in your example you are doing a query for each record returned in the first query, depending on the overhead of doing a query this might be a lot slower than a support for ioins in the dbms itself, even without any optimization (my code does not do any optimization, the programmer has to do optimization manually) | 9-Feb-06 12:02 |
| 62 | Gabriele | ashley: sent latest dbms3.r via email. | 9-Feb-06 11:56 |
| 61 | Coccinelle | Ashley, sql-protocol generates the code used to extract and join the data is grouped in 2 functions :
- make-do-select
- make-do-loop
They receive the column list, the table list (a block of table/alias pair), the where code to apply and the database (the port). If you provide these parameters and change the code to invoke RebDB fuction to get the data, you will have a basic join implementation for RebDB. You can use it and extend it for RebDB, if you want, I will be happy if you do so. | 9-Feb-06 11:31 |
| 60 | Pekr | Graham - I noticed you used Firebird for your medical system - is there kind of embedded version as with sqlite? | 9-Feb-06 9:27 |
| 59 | Pekr | anyway .... maybe a good question for RT, when finally RIF arrives, because if I understand you correctly, till then, RebDB will be memory only database (although open/seek is working already) | 9-Feb-06 9:26 |
| 58 | Pekr | IIRC MySQL docs join section describes very nicely the technique of optimisation. And after reading it some few monts ago I agree, that 'join is not trivial task at all, basically due to get it optimised. But you are right that most of the time those two mentioned cases are needed. The thing which complicates the optimisation part is - is your column a key? primary key? is it at least indexed? etc. | 9-Feb-06 8:17 |
| 57 | Pekr | yes, basically it is typical two cases - master-detail, and lookup!!! | 9-Feb-06 8:15 |
| 56 | Pekr | not sure your sql query rewrite was much clearer, but - your last post makes so much sense! | 9-Feb-06 8:15 |
| 55 | Ashley | The final [untested] solution is about as efficient as you can get. Adding JOIN support to RebDB so it can break the query down into similar steps is not a simple task. In all but the most trivial of cases you'd be better off coding it yourself. What might be a good idea is to add something that lets you more easily specify the most common JOIN operation - master/detail with optional LOV (List Of Values) lookup(s). Your query is a classic example of this construct and it accounts for a surprisingly large number of queries. The function would accept two queries, a master query (the orders table in your case) and a details query (the order-items table) and an optional block of column/LOV-table pairs (that perform substitutions such as company-name). The skeleton would look like: sql-join [master-query [block!] detail-query [block!] /order /lov [block!] ] [ buffer: copy [] ... buffer ] and would be used as such: sql-join/order/lov [ select [company-id order-id date amount total] from orders where [amount > 2000] ] [ select * from order-items where %ID% ] [8 1 5] [company-id companies] Would this make things a tad easier? | 9-Feb-06 6:42 |
| 54 | Ashley | Then break it down into discrete queries and wrap it in some loops: blk: copy [] foreach [company-id order-id date amount total] sql [ select [company-id order-id date amount total] from orders where [amount > 2000] ][ company-name: second sql compose [lookup companies (company-id)] foreach [item-id item-price goods-name] sql compose [select * from order-items where (order-id)] [ insert tail blk reduce [order-id date amount total item-id item-price goods-name company-name] ] ] sort/skip/compare blk 8 [8 1 5] | 9-Feb-06 6:13 |
| 53 | Graham | not here either http://www.colellachiara.com/soft/ | 9-Feb-06 6:10 |
| 52 | Ashley | Back to Pekr's JOIN problem. First, let's reformat the SQL into something more readable: select o.order-id, o.date, o.amount, o.total, oi.item-id, io.item-price, oi.goods-name, c.company-name from orders o , order-items oi , companies c where o.order.id = oi.order-id and o.company-id = c.company-id and o.amount > 2000 order by c.company-name, o.order-id, o-item-id | 9-Feb-06 6:10 |
| 51 | Graham | oohh.. looks like a Cobalt Raq. | 9-Feb-06 6:08 |
| 50 | Graham | rebol.it is a good bet. | 9-Feb-06 6:07 |
| 49 | Ashley | dbms3.r isn't in the library, where can I grab a copy from? | 9-Feb-06 5:53 |
| 48 | Ashley | Thanks, I'll take a look at it. (Marco's sql-protocol.r is also a good read). | 8-Feb-06 23:20 |
| 47 | Gabriele | ashley, my old dbms3.r supports joins, though probably far from doing it efficiently. in case you need the code feel free to use it. | 8-Feb-06 18:02 |
| 46 | Pekr | we would need some join/key ... to define key on which to join two blocks to create third one ... or maybe union/key ... | 8-Feb-06 14:18 |
| 45 | Pekr | do not understand the syntax of select from a,b ... actually - never understood it :-) our db allows select, the rest is crosslinked via joins :-) | 8-Feb-06 14:11 |
| 44 | Ashley | UNION cannot substitute for JOIN. UNION aggregates the rows returned by multiple statements while JOIN [can] aggregate the columns returned by referring to multiple tables. A subtle but important distinction. For example: Table-A Col ==== 1 2 Table-B Col ==== A B select a.col, b.col from a, b 1 A 1 B 2 A 2 B select * from a union select * from b 1 2 A B | 8-Feb-06 14:05 |
| 43 | Pekr | that join simply creates one big table for you, which is nice, as you simply can traverse in one loop .... | 8-Feb-06 13:52 |
| 42 | Pekr | maybe it is a pity rebol can't do union and join on more per record base, but per item base ... | 8-Feb-06 13:51 |
| 41 | Sunanda | I learned before JOIN existed.....Means you are more skilled than me :-) | 8-Feb-06 13:51 |
| 40 | Pekr | Yes, that might be possible ... I am not that skilled in SQL yet, so maybe it could be done other way .... | 8-Feb-06 13:50 |
| 39 | Pekr | outer join just selects company name ... | 8-Feb-06 13:49 |
| 38 | Sunanda | Not sure if I'm off topic here.....But you can do pretty much everything in SQL without using JOIN. Though you may need UNION, subselects and WHERE. Which makes for more portable SQL as JOIN syntax often used vendor-specific extensions to the SQL-92 standard. | 8-Feb-06 13:49 |
| 37 | Pekr | inner join simply "adds rows" of order-items per order | 8-Feb-06 13:49 |
| 36 | Pekr | so basically select row defines you final format of result block ... | 8-Feb-06 13:48 |
| 35 | Pekr | not sure I got it syntactically 100% correct, but you got the idea :-) | 8-Feb-06 13:47 |
| 34 | Pekr | SELECT o.order-id, o.date, o.amount, o.total, oi.item-id, io.item-price, oi.goods-name, c.company-name FROM orders o INNER JOIN order-items oi ON oi.order-id = o.order.id LEFT OUTER JOIN companies c ON c.company-id = o.company-id WHERE o.amount > 2000 ORDER BY c.company-name, o.order-id, o-item-id | 8-Feb-06 13:46 |
| 33 | Ashley | Stick to basic JOIN syntax in the form: select b.name, b.address from a, b where a.id = b.id and ... those INNER JOIN and LEFT OUTER JOIN statements are unfamiliar to me and I get a headache just looking at them. ;) | 8-Feb-06 13:45 |
| 32 | Pekr | that was just an example simple schema, but well, I may try to write short SQL query from that ;-) | 8-Feb-06 13:39 |
| 31 | Ashley | An example based on what you are trying to do with Orders, Items and Companies might be better to start with. | 8-Feb-06 13:38 |
| 30 | Pekr | but that is maybe unnecessarily complicated example, we were migrating data to SAP R3 from our old systems ..... | 8-Feb-06 13:34 |
| 29 | Pekr | while inner join seems to be kind of aggregate ... | 8-Feb-06 13:33 |
| 28 | Pekr | left outer join seems like kind of subselect) | 8-Feb-06 13:33 |
| 27 | Pekr | so let's forget those aliases, which are nice things to have too :-) | 8-Feb-06 13:33 |
| 26 | Pekr | ok, here's my rebol odbc code for ADS (Advantage Database Server): insert db-port trim/lines { SELECT DISTINCT ko.kodfyzak, fk.nazev, fk.ulice, fk.mesto, fk.psc, fk.kodzeme, zk.land1, upper(fk.kodmeny), upper(e.ico), ko.kodfypri1, fp.nazev, fp.ulice, fp.mesto, fp.psc, fp.kodzeme, zp.land1, upper(fp.kodmeny) FROM zakp_exp e INNER JOIN Kontjkv5 k5 ON k5.zakazka = concat(e.miv, left(e.czak, 6)) LEFT OUTER JOIN kontrakt ko ON ko.ciskontr = k5.ciskontr LEFT OUTER JOIN firma fk ON fk.kodfirmy = ko.kodfyzak AND fk.aktualni = 'A' LEFT OUTER JOIN firma fp ON fp.kodfirmy = ko.kodfypri1 AND fp.aktualni = 'A' LEFT OUTER JOIN zem zk ON fk.kodzeme = zk.kodzeme LEFT OUTER JOIN zem zp ON fp.kodzeme = zp.kodzeme LEFT OUTER JOIN delka_psc psc ON zp.land1 = psc.land1 ORDER BY fk.nazev, fp.nazev } | 8-Feb-06 13:32 |
| 25 | Ashley | Just write the SQL statement as you would in MySQL, etc ... then we can see where the issues are. | 8-Feb-06 13:29 |
| 24 | Pekr | so the inner join? (agregate) | 8-Feb-06 13:24 |
| 23 | Pekr | inner join orders, order items, left outer join companies .... | 8-Feb-06 13:23 |
| 22 | Ashley | Data structure makes sense, what's the problematic SQL statement? (in its simplest form) | 8-Feb-06 13:22 |
| 21 | Pekr | would you store company-name in orders directly? Surely not (although we are doing it too for some purposes, as getting grid drawn fast) | 8-Feb-06 13:16 |
| 20 | Pekr | yes ... | 8-Feb-06 13:16 |
| 19 | Pekr | that is imo correct design to simply avoid data inconsistency. Imagine if you would put 'company-name into 'oders, and later on company changes it's name a bit - you simply want that info to have stored only once ... | 8-Feb-06 13:15 |
| 18 | Anton | Orders Order-Items Companies company-id, company-name, company-address ? | 8-Feb-06 13:15 |
| 17 | Pekr | And now you have typical problem - you surely don't want your grid to show 'company-id, but 'company-name or other related data - but those are not present in 'orders database ... | 8-Feb-06 13:14 |
| 16 | Pekr | then, when you enter new order into 'orders, you simply assign it to company, by using and storing foreign key, so 'company-id ... | 8-Feb-06 13:14 |
| 15 | Pekr | in 'companies, you have 'company-id, 'company-name, 'company-address .... | 8-Feb-06 13:13 |
| 14 | Pekr | ok, here it is: you have tables called 'orders, 'order-items, 'companies ..... | 8-Feb-06 13:12 |
| 13 | Anton | So, Petr, you solved the original problem which occurred for you ? | 8-Feb-06 13:09 |
| 12 | Ashley | It's called a "Pseudo-Relational Database" for a good reason. ;) But in answer to Pekr's previous point; *you* can work out *how* to do that aggregation more efficiently than the limited AI of most RDBMS systems. I've worked at DB2 and Oracle shops where it was mandated that all JOINs be performed inline! SQL optimizers are not trivial to write, and the meta-data overhead (indexes, statistics, hints, etc) required to get them to make the "right" choices are often a false economy. | 8-Feb-06 13:08 |
| 11 | Pekr | no, Anton, everything is ok, no problems with columns, or switching .... I will describe you one example .... | 8-Feb-06 13:06 |
| 10 | Anton | But, Petr, perhaps it would be good if you could show the operation and tables that you are having trouble with, and Ashley can help you find the best way. Maybe the performance will be more than you need, and the expression simpler in rebol. | 8-Feb-06 13:04 |
| 9 | Anton | So it looks like the problem is Pekr's expectation that the database have certain common functions, often implemented in other databases. Switching from one database to another is hard because they don't all support the same functions. I think RebDB, as a youthful, new, growing database, should not be expected to have everything yet. | 8-Feb-06 13:01 |
| 8 | Pekr | yes, but what you describe is more than day-by-day example of proper db usage. Even with small projects, when using 3NF notation, you simply store only foreign keys in tables, so those "aggregate" functions are needed too often ... | 8-Feb-06 12:50 |
| 7 | Ashley | JOIN differs from SUB-SELECT where you want to aggregate the columns of more than one table, so: select a.col, b.col from a, b cannot be refactored as a sub-select. There are two reasons why I have not implemented JOINs in RebDB [yet]: 1) Dramatic increase in code complexity 2) You can almost always do it more efficiently in REBOL as you *know* the data structures and desired result set *in advance*. About the only time this does not work well [in RebDB] is where you have to pull the contents of more than one table across a network to derive a small subset as the result set. So while this SQL would not suffer: select a.col, b.col from a, b this might: select a.key, b.val from a, b where a.key = b.key depending on the size of b. | 8-Feb-06 12:48 |
| 6 | Pekr | hmm, actually join has to do the same, so :-) | 8-Feb-06 11:14 |
| 5 | Pekr | Ashley - subselect is not 'join, is it? In y our above second example for e.g., it will mean that for each-record in table 'a, there will be one subselect ... that is going to be slow, no? | 8-Feb-06 11:02 |
| 4 | Ashley | Also note that many join operations can be rewritten as sub-selects, as in: sql compose/deep [ select * from a where [ col < ( sql [select max [col] from b] ) ] ] or: sql compose/deep/only [ select * from a where [ find (sql [select [col] from b]) col ] ] | 7-Feb-06 23:30 |
| 3 | Ashley | <Jaime>
Pekr. Join is relative easy to implement. Just do a search on each table, and then create a new block by using the key that joins them. foreach key table1 [ values-table1: select key table1 values-table2: select key table2 append join-table reduce [values-table1 values-table2] ] That is more or less the pseudo algorithm </Jaime> | 7-Feb-06 23:09 |
| 2 | Ashley | <Pekr>
Ashley, just wanted to ask and can't find rebdb group here ... Isn't it possible to implement 'join? You once said that you will wait once RT adds RIF, but that will probably come who knows when - it is year and half late already. Do you think it would not be possible to proceed without RIF and switching to on-disk storage? 'join is really badly missing with rebdb and I am thinking switching to sqlite only because of that one feature. Once you have your data spread across many tables, it is difficult to work without it. Or how you do it? </Pekr> | 7-Feb-06 23:09 |
| 1 | Ashley | From !RebGUI group. | 7-Feb-06 23:09 |