| # | User | Message | Date |
| 1063 | Janko | youre welcome | 26-May 16:42 |
| 1062 | amacleod | I changed all the paths to absolute paths...it seems to have done the trick. Thanks for the hint, Janko! | 26-May 16:18 |
| 1061 | Janko | I got similar error but I don't know if it was for the relative path or not the right version of dll / so | 26-May 16:08 |
| 1060 | amacleod | It works fine until I try to call another app. Paths do not change but I'm not using absolute paths. I'll need to test that... | 26-May 16:05 |
| 1059 | Janko | Check if you have the right path to file.. and make it an absolute path.. and if you have the right version .. sqlite != sqlite3 | 26-May 16:02 |
| 1058 | amacleod | I seem to get an error with sqlite after using "call" to start an external program. ** User Error: SQLite out of memory ** Near: make error! reform ["SQLite" error] Anyone experience this? | 26-May 15:56 |
| 1057 | RobertS | . | 22-May 16:00 |
| 1056 | Gregg | It shouldn't be hard to devise a small benchmark though. | 21-May 14:49 |
| 1055 | Gregg | The OS caches things too, don't forget. | 21-May 14:48 |
| 1054 | Janko | If you had persistent connection and if sqlite does some caching it could be faster, but if you need to open a connection on each request I think it would be much slower because that is more expensive (I assume).. it probably also matters how many scripts do you need to run per request | 21-May 5:12 |
| 1053 | Chris | Perhaps particularly a CGI environment where each instance is a separate process? | 21-May 4:59 |
| 1052 | Chris | Say, where there are a moderate number of moderate sized scripts - could it be quicker just to fire up an SQLite connection and access the scripts/modules from records as opposed to flat file? | 21-May 4:58 |
| 1051 | Chris | Has anyone done any benchmarks for running scripts from a DB instead of from the filesystem? | 21-May 4:55 |
| 1050 | Janko | found solution. I need to join with subqueries: {SELECT * FROM } table { as i LEFT OUTER JOIN ( SELECT id_} table { as ibs_paren, SUM(price * qty) as amount FROM } table {_b ib GROUP BY id_} table { ) ibs ON i.id = ibs_paren LEFT OUTER JOIN ( SELECT id_} table { as ips_paren, SUM(ip.amount) as payed_amount FROM } table {_p ip GROUP BY ip.id_} table { ) ips ON i.id = ips_paren order by title; | 9-May 10:24 |
| 1049 | Janko | This guy has totally the same problem but no answer http://forums.devshed.com/db2-development-114/left-outer-join-3-tables-sum-field-of-2nd-3rdt-588801.html | 9-May 9:32 |
| 1048 | Janko | ah, my method with count won't work because count also multiplies if I have more than 1 columnt in second subtable | 9-May 9:15 |
| 1047 | Janko | hm.. I have a question for SQL / sqlite experts :
I have a query with two JOINS . There is parent table which has 2 subtables ... in each subtable I need to aggregate (SUM) some value ... select i.*, SUM(ib.price * ib.qty) as amount, SUM(ip.amount) as payed_amount from invoice_sent as i left outer join invoice_sent_b as ib on i.id = ib.id_invoice_sent left outer join invoice_sent_p as ip on i.id = ip.id_invoice_sent group by i.id order by i.title; The problem is tha because of join , the amount is correct , is the sum of values in invoice_sent_b , but payed_amount is multiplied by the number of rows invoice_sent_b has . I understand why this happens, but I don't know how to prevent it, if I want to get all data in one query. ( I know how to solve the prolem , but it's outside the DB so it's more of a hack -- I also ger COUNT of _b table rows and divide second SUM by it on client ) | 9-May 9:00 |
| 1046 | Janko | yes | 30-Apr 15:51 |
| 1045 | Janko | you can put cheyenne there or any other custom server you want | 30-Apr 15:50 |
| 1044 | Pekr | ah, virtual machine, ok ... | 30-Apr 15:50 |
| 1043 | Janko | it's like you have your own computer that you can reinstall stuff or OS .. separated from others but it's running on virtualisation software so there are many such separate computers per one real computer , so it's *cheaper* than paying for having a full server | 30-Apr 15:49 |
| 1042 | Janko | virtual private server ... like you can buy here .. www.linode.com or sliceshare.com | 30-Apr 15:48 |
| 1041 | Pekr | btw - what is VPS? | 30-Apr 15:47 |
| 1040 | Janko | I will try this on my local computers and then on that VPS.. and report you back :) | 30-Apr 15:47 |
| 1039 | Janko | hm... mucho interesante :) | 30-Apr 15:46 |
| 1038 | Pekr | Janko - I did small test for you. With no indices the speed was: 0:00:00.516 and I used LIKE expressions, which need to do searches in terms of field values .... REBOL [] print "Starting test for Janko ..." do %sqlite.r attempt [delete %janko.db] connect/direct/create %janko.db sql { CREATE TABLE [domains] ( [id] INTEGER NOT NULL PRIMARY KEY, [domain] VARCHAR NOT NULL, [user] VARCHAR NOT NULL, [processed] DATETIME NULL, [ok_count] INT NOT NULL DEFAULT 0, [fail_count] INT NOT NULL DEFAULT 0, [error] TEXT NULL ) } sql "BEGIN" for i 1 1000 1 [ sql reduce ["insert into domains values(?,?,?,?,?,?,?)" i i i i i i i] ] sql "COMMIT" start: now/time/precise sql {update domains set user = 'u3' where domain like '%1%' and user like '%1%'} print now/time/precise - start disconnect %janko.db halt | 30-Apr 15:42 |
| 1037 | Janko | any query I try :) .. I talked about this to you already :) .. look a little up at 14-Apr | 30-Apr 15:12 |
| 1036 | Janko | I talked about this in detail a while ago.. now I optimised the problem so it's not a biggie any more but I am still mad that all rebol files can do changes in moments and when I need to update the sqlite page halts for noricable time | 30-Apr 15:11 |
| 1035 | Pekr | show me the table structure, show me the query :-) | 30-Apr 15:10 |
| 1034 | Janko | yes, and nothing big.. 5 simple rows | 30-Apr 15:09 |
| 1033 | Pekr | ah, single query? | 30-Apr 15:09 |
| 1032 | Janko | yes, I know for that .. that would come into play if I would be doing 10 inserts for example ,.. with begin commit it would take just 1/10 of time as without , but I am doing a simple sql scentence here. and I tried begin/commit also with this. | 30-Apr 15:08 |
| 1031 | Pekr | Have you tried put BEGIN transaction and END transaction outer commands? Because if not, then it commits each time. The difference is going to be ... well, drastic ... | 30-Apr 15:00 |
| 1030 | Janko | and this is no problem with rebol binding as the delays are the same if I go to sqlite3 shell | 30-Apr 14:46 |
| 1029 | Janko | (I tried rebDB on same VPS - localy where comp. is fast I notice no delay at sqlite either) | 30-Apr 14:43 |
| 1028 | Janko | I use it for very simple task just so it takes case for locking of messages to bots that can come from multiple processes.. but at my small/cheap VPS that I use for bots update/insert/delete takes multiple seconds which is very bad.. all rebol writes/deletes which it does with normal files return imediately on same VPS and yesterday I tried rebDB and it was also much much faster for insert/update/delete (almost no noticable delay) for the same amount of data (300 lines) as sqlite.. funny thing is that sqlite delays the same at these operations if there is 300 or 5000 rows in table | 30-Apr 14:42 |
| 1027 | Pekr | SQLIte is fast for simple to middle local stuff. I have few obstacles with it 1) it stores everything in one file. You can't use simplicity of file-system for simple back-up purposes. Attaching DBs (max 10 precompiled value) is not an option, as then transactions are not atomic 2) it is not secure - can't be secured easily, because encryption is not part of the package 3) serverless (both advantage = no install, but also disadvantage). It provides locking. They claim multiple instances of app can access one file, but I did not find more info on that. Dunno how granular locking you can do. You have to create server front-end yourself ... | 30-Apr 14:27 |
| 1026 | Janko | only that fast (at sqlite) is still a little problematic to me | 30-Apr 14:24 |
| 1025 | Pekr | rebol.com motto - Web 3.0 starts here. Smarter, faster, better. | 30-Apr 14:23 |
| 1024 | Maxim | I love the sqlite motto :-) "Small. Fast. Reliable. Choose any three." | 30-Apr 14:13 |
| 1023 | Pekr | Reading thru SQLite changelog found this - http://sqlite.org/rtree.html ... not sure of its practical usability, but some of you might find it interesting :-) | 30-Apr 14:09 |
| 1022 | sqlab | normally there is, but not always. Most socket connections are static, they stay connected for hours or longer. | 21-Apr 13:52 |
| 1021 | Robert | Hm... normaly there should be event/data | 21-Apr 11:27 |
| 1020 | sqlab | yes | 21-Apr 11:26 |
| 1019 | Robert | Event = TCP/IP event? | 21-Apr 11:10 |
| 1018 | sqlab | As I know, that there should be data available, I read again after timeout | 21-Apr 10:50 |
| 1017 | sqlab | If I have some processes serving incoming events and sending their data to a central storage process, the central process sometimes seems to get an event, but not the data with that event. When the data really arrives, I do not get the event. Maybe he got the first event meant for an other connection | 21-Apr 10:45 |
| 1016 | Robert | Don't understand the problem. Is it realted to my suggestion or your current try? | 21-Apr 10:40 |
| 1015 | sqlab | Robert, that could be a solution. Unfortunately I observed a crosstalk of events with many ip connections, means one process is awakened without data available, if I send data additionaly on the same server via ip | 21-Apr 10:38 |
| 1014 | Robert | I'm pretty sure a proxy process can handle 200req/s at the frontend and backend. So if 200req/s is the maximum you can get from one process, adding more processes just devides this down. But it won't scale up. | 21-Apr 10:36 |
| 1013 | sqlab | I get up to 200 inserts with just one process, with two concurrent process this slows down to 30 to 60 per second | 21-Apr 10:32 |
| 1012 | sqlab | Maybe my test settings are different from your requirements. During testing I let two concurrent processes insert a record with three fields, one ip address, a timestamp and one text field with variable length from 1k to 8k., and this i do with full speed | 21-Apr 10:29 |
| 1011 | Robert | You can add things like priority, read-only support etc. | 21-Apr 10:29 |
| 1010 | Robert | IMO you are much flexibler anyway. | 21-Apr 10:28 |
| 1009 | Robert | sqlab, use a proxy process that does the priorization. Send requests from client to proxy-process and this one handles 1 connection to the database. | 21-Apr 10:28 |
| 1008 | Janko | how are you testing it.. how many writes/sec .. processes? | 21-Apr 10:28 |
| 1007 | Janko | hm.. too bad | 21-Apr 10:24 |
| 1006 | sqlab | no difference, sooner than later I get a lock, even with 100 retries after less than 1000 statements | 21-Apr 10:24 |
| 1005 | sqlab | my mistake, I forgot to set the number of retries during connect. I will test again | 21-Apr 10:16 |
| 1004 | Janko | He tried only 100 times (by looking at the example he gave) .. and I don't know what he means by 500 >>> in line with mysql
and others- at 500+ simultaneous users you start to get about a 10% drop
because of lock overhead. <<< Most probably not 500 processes writing all the time ... without delays | 21-Apr 10:10 |
| 1003 | sqlab | yes, it reduces, but they happen still | 21-Apr 10:05 |
| 1002 | sqlab | do-step seems to retry until not busy | 21-Apr 10:04 |
| 1001 | Janko | aha .. does it at least signidicantly reduce the occurence of the lock or it's the same as no retry? | 21-Apr 10:04 |
| 1000 | sqlab | sorry, that was the number of statements until a lockk occurs | 21-Apr 10:02 |
| 999 | Janko | what disconnection and connecting? :) | 21-Apr 10:01 |
| 998 | sqlab | a few 10k times | 21-Apr 10:01 |
| 997 | Janko | how many times do you retry? | 21-Apr 10:01 |
| 996 | Janko | aha ... hm.. I havent tried it myself yet | 21-Apr 10:00 |
| 995 | sqlab | trying again is already implemented in sqlite.r in the function do-step. I expanded the wait time with a random duration up to one second. still not good enough And now additionaly I connected and disconnected according the link . Again I encountered locks | 21-Apr 9:57 |
| 994 | Janko | I understood the text in link that if you get a lock, you wait for a while and try again.. and that by this method even it scales to quite big number of concurr processes compared to mysql for example | 21-Apr 9:40 |
| 993 | sqlab | Janko: I just tested again two concurrent processes writing into one table. This time I used the approach connecting and closing for each statement, as the link you gave suggested.. I still got locking. | 21-Apr 9:17 |
| 992 | amacleod | works...thanks a lot. I had this fear of having to change large parts of my code if I added or removed columns...This solves that problem. | 17-Apr 1:53 |
| 991 | sqlab | should be like insert into table (col3, col2, col1) values (?, ?, ?) | 16-Apr 20:42 |
| 990 | amacleod | For example: SQL reduce [{UPDATE books SET bk=?, chap=? WHERE ref_number=?} blk/2 blk/3 blk/1] | 16-Apr 19:40 |
| 989 | amacleod | Can you do the same for inserting values? I know I can do it for updating row data but I can not figure out the syntax for inserting.. | 16-Apr 19:39 |
| 988 | amacleod | That sounds helpful..thanks sqlab. | 16-Apr 18:13 |
| 987 | sqlab | In sql there is no need for reordering the column order, as you can get any order you desire by using the column names in your select statement. | 16-Apr 17:48 |
| 986 | Janko | alter table supports just renaming table and adding rows, for all else you need to create new table -> insert .. select ... ; data and drop old and rename new | 16-Apr 15:57 |
| 985 | amacleod | Is there a way to reorder columns in SQLITE? | 16-Apr 15:55 |
| 984 | Pekr | you can use varchar even for indexes, it should speed up things significantly ... | 14-Apr 19:28 |
| 983 | Janko | but the changes you proposed will help me get it faster anyway and I will try them | 14-Apr 19:13 |
| 982 | Oldes | Yes.. it's possible, that the VPS does many other disk IO oparations so you have to wait. | 14-Apr 19:09 |
| 981 | Janko | I used sqlite here and there for more real DB work and I never seen any critical slownes (extept if you do a typical like inserting 100 rows each in it's own transaction (without begin commit), in fact it seemed always very fast to me ... thats why I suspect all this wouldn't show up if I had some better VPS. Also because if fluctuates so much I suspect disk on computer vps is on is maybe busy doing other stuff so at one moment it is idle and it works faster at another it waits for >3 seconds | 14-Apr 19:07 |
| 980 | Janko | aha, I will try that .. and I can use Integer for user ... because now each user get's folder like u<Int> ... and I could maybe put all 3 mailboxes into the same table so it would be only 1 insert / update instead of 3 on changes ... I didn't think performance will be the problem here (it still seems a little funny) , because it's just a simple table without any relations or anything and no big amunt of data | 14-Apr 19:03 |
| 979 | Oldes | also... if you want to index domain and user, you should not use varchar without length. | 14-Apr 18:59 |
| 978 | Janko | aha, thanks for idea.. I will use explain .. although it's so simple query that it shouldn't do any strange things I think | 14-Apr 18:55 |
| 977 | Janko | yes, I think so too.. it is interesting that the time looks the same on 4000 rows ... maybe reason for this is also because I am on some very cheap VPS (12EUR, 180MB ram, ? CPU) ... | 14-Apr 18:54 |
| 976 | Oldes | I'm not sqlite user but I would probably tried to use EXPLAIN to see, what's going on when you do the query - http://www.sqlite.org/lang_explain.html | 14-Apr 18:52 |
| 975 | Oldes | 1s to 5s on 180 rows is bad. whatever you do. | 14-Apr 18:51 |
| 974 | Janko | I had it without indexes at first , and later added indexes while I was trying various things, at 180 records there wasn't any noticable change. Well the result doesn't seem so bad to me right now.. if it has the same delay with 4000 records it's okey-ish. On my local computer which is much better than some small VPS I noticed no delays. I just realized that the delay at web-app was 3x bigger than this because I have 3 bots and each has it's own "mailbox" ... The solution for this situation will be affloading the inserts from the request process, for the future when things will need to scale up I will try doing this different anyway, that's why I was playing with actor like systems anyway | 14-Apr 17:42 |
| 973 | sqlab | If you update an index field, the index too has to be updated. Do you open and close your db, as it is recommended in the link you posted? Then you have to add the time for that too. | 14-Apr 15:56 |
| 972 | Janko | have to go..storm | 14-Apr 15:26 |
| 971 | Janko | hm.. very interesting results... and positive basically :) with 4000 records insert time, and update time for the same query is just the same as with 183 , and select is fast as before... then it's survivable .. I was afraid that if with so few rows it's so bad with couple more it will be unworkable | 14-Apr 15:25 |
| 970 | Janko | (maybe I just need a better VPS) | 14-Apr 15:08 |
| 969 | Janko | but I am sure even if I write that data inf ile as rebol blocks and load and foreach them to find the one it would take far less than 1s , it's just 180 rows!!!! :) | 14-Apr 15:07 |
| 968 | Pekr | ... well, I am not good at sql internals, so .... try various combinations, and you'll see .. | 14-Apr 15:06 |
| 967 | Pekr | simply put - how db chooses, which index to use? You have them separate as well as mixed. I would use mixed index (domain,user) only if those two fields would be defined as a primary key together ... | 14-Apr 15:05 |
| 966 | Pekr | I would left out first index and add index for your primary key .... (not sure though :-) | 14-Apr 15:04 |
| 965 | Janko | I did before when trying stuff index|dom_user|domains|11|CREATE INDEX dom_user on domains ( domain, user ) index|dom1|domains|16|CREATE INDEX dom1 on domains ( domain ) index|user1|domains|21|CREATE INDEX user1 on domains ( user ) | 14-Apr 15:03 |
| 964 | Pekr | OK, index you ID field, then also user and domain field | 14-Apr 15:01 |
| 963 | Janko | I will insert random rows so I will have couple of 1000 , and then I will see what I get | 14-Apr 15:01 |
| 962 | Janko | I added the indexes to both, and each separate .. but it's roghly the same ... before indexes select returned imediatelly on the same where | 14-Apr 15:00 |
| 961 | Janko | "u3" is just here for testing purposes, so I can repeat the query :) | 14-Apr 14:59 |
| 960 | Janko | Because times vary from 1s to 5s I suspect taht vps's disk or something might be a little owerburdened, but as I said 300kb rebol (this is 10kb) worked im ms range according to cheyenne | 14-Apr 14:58 |
| 959 | Pekr | btw - why do you set user="u3" for records, where user is already of "u3" value? | 14-Apr 14:58 |
| 958 | Pekr | try to index domain, and maybe even user fields, or it will go sequentially thru all of record lines ... | 14-Apr 14:57 |
| 957 | Janko | I tried now with transaction .. it has no point here becauase it's just one query at the time ... but results were as expected.. update did finish imediatelly , but commit took the 1-3s time also | 14-Apr 14:56 |
| 956 | Janko | CREATE TABLE [domains] (
[id] INTEGER NOT NULL PRIMARY KEY,
[domain] VARCHAR NOT NULL,
[user] VARCHAR NOT NULL,
[processed] DATETIME NULL,
[ok_count] INT NOT NULL DEFAULT 0,
[fail_count] INT NOT NULL DEFAULT 0,
[error] TEXT NULL
) test query is as straighforward as it can be: update domains set user = "u3" where domain = "www.todelete.com" and user = "u3"; | 14-Apr 14:54 |
| 955 | Janko | This is a very small VPS, but I have 300kb raw rebol data structures in ordinary files, and I edit and seek them without any indexes and it works immediatelly.. I only moved this part of data to sqlite because it handeles the file locking ( these are sort of mailboxes so that the app and bots can communicate over them ) | 14-Apr 14:51 |
| 954 | Janko | Pekr.. I will | 14-Apr 14:48 |
| 953 | Janko | I also added the indexes now and it is maybe a little faster but on single where but on both that I need it looks more or less the same (select by same condition is imeddiate) | 14-Apr 14:47 |
| 952 | Pekr | can you post whole query? | 14-Apr 14:44 |
| 951 | Janko | I don't get this ... I started getting very long loading times with my webapp when I changed or inserted the and it was very fast before ... now I saw that it's the sqlite making these delays.. this is not the problem of sqlite.r but the sqlite itself because I get the same behaviour with sqlite3 shell. But I can't believe this , I am certain I am doing something wrong.. I remember sqlite can handle GB of data and is very fast, but in my case... I have 183 rows in a simple 5 column table (db file is 10kb) .. if I do single update table X set y = ".." where Z = ".."; it takes like 3 seconds. This updates just 1 row out of 183. Does anyone have any idea? I tried to do the "Vacuum" command but it's the same after it. | 14-Apr 14:39 |
| 950 | Janko | . | 14-Apr 14:29 |
| 949 | Janko | this might be usefull to users of sqlite.. I yesterday encountered the "db is locked" error and it got me a little worried, but with this (simple) aproach it seems to solve that http://itsystementwicklung.de/pipermail/list-pysqlite/2009-April/000380.html | 11-Apr 15:32 |
| 948 | amacleod | I did not realize sqlite.r was set up to use mysql3.so (linux libs) Got my app running on linux witout a hitch... Auto detects OS...great! | 1-Apr 4:10 |
| 947 | Janko | thanks Doc! | 17-Mar 20:10 |
| 946 | Dockimbel | You don't need to use apt-get for sqlite, just download the latest library and put it in your app folder near sqlite.r : http://www.sqlite.org/sqlite-3.6.11.so.gz (I guess you'll need to rename it to libsqlite3.so). | 17-Mar 19:39 |
| 945 | Janko | aha.. stupid me.. apt-get usually doesn't provide latest versions but a more "stable" ones ... yes it's 3.3.8 | 17-Mar 18:35 |
| 944 | Dockimbel | Have your application execute the following SQL statement and then look at the result : select sqlite_version(); AFAICT, sqlite3_prepare_v2 is available starting from SQLite 3.5.2. | 17-Mar 18:33 |
| 943 | Janko | I hope this doesn't mean I something else won't work as it should | 17-Mar 18:29 |
| 942 | Janko | ha.. I changed sqlite_prepare_v2 to sqlite_prepare in sqlite.r and now rebpro and cheyenne work!! | 17-Mar 18:24 |
| 941 | Janko | ah... I looked at history of this chat and saw that there is debian package libsqlite3-dev ... I installed this and now I have libsqlite3.so .. it seems rebpro finds it without copying it to local directory now but it still gibes the same " Cannot open sqlite3_prepare_v2" error | 17-Mar 18:06 |
| 940 | Janko | when I installed sqlite with apt-get install sqlite3 I got 2 files into /usr/lib/ directory ./usr/lib/libsqlite3.so.0.8.6 and ./usr/lib/libsqlite3.so.0 ... if I run rebol it doesn't find sqlite so I copied one of them (I tried with both, they are of same size btw) to directory where rebol / rebpro and sqlite.r is and renamed it to libsqlite3.so and then it seems to find it ... now this is just common sense as I am not that experienced with this on linux | 17-Mar 17:58 |
| 939 | Janko | -s probably means without asking for permissions... I tried now rebpro -s -q but I got the same .so error .. maybe I didn't prepare the .so files well ..I am just looking into docs >> do %sqlite.r ** Access Error: Cannot open sqlite3_prepare_v2 ** Near: *prepare: make routine! [db [integer!] dbq [string!] len [integer!] stmt [struct! [[integer!]]] dummy [struct! [[integer... | 17-Mar 17:51 |
| 938 | Oldes | http://www.rebol.com/docs/words/wusage.html | 17-Mar 17:49 |
| 937 | Oldes | you have to start it with the -s switch and good is to use the -q switch as well. | 17-Mar 17:48 |
| 936 | Janko | aha.. I am getting somewhere .. it alows it now but I get some error with .so .. maybe this is the reason cheyenne can't open it either >> do %sqlite.r Script: "SQLite driver" (26-Nov-2008) REBOL - Security Check: Script requests permission to open a port for read/write on: libsqlite3.so Yes, allow all, no, or quit? (Y/A/N/Q) Y ** Access Error: Cannot open sqlite3_prepare_v2 ** Near: *prepare: make routine! [db [integer!] dbq [string!] len [integer!] stmt [struct! [[integer!]]] dummy [struct! [[integer.. | 17-Mar 17:41 |
| 935 | Janko | wow.. first rebol download > 1MB :) | 17-Mar 17:31 |
| 934 | Janko | hu, I never saw that download page :) | 17-Mar 17:22 |
| 933 | Oldes | At least what I was trying, there was the license message on boot, but I could use the /library | 17-Mar 17:19 |
| 932 | Oldes | http://www.rebol.net/builds/#section-1 | 17-Mar 17:18 |
| 931 | Oldes | just download it and try it | 17-Mar 17:18 |
| 930 | Oldes | I think you don't need the license | 17-Mar 17:17 |
| 929 | Janko | I am an new/old user so I am still on free versions of rebol.. I gues now is time to buy the SDK | 17-Mar 17:13 |
| 928 | Janko | Oldes: yes, it looks that way by the error given | 17-Mar 17:10 |
| 927 | Oldes | Or you can use Rebpro from Rebol's SDK | 17-Mar 17:01 |
| 926 | Oldes | I think the library calls are not enabled in linux version of Core.. ask Carl. | 17-Mar 16:58 |
| 925 | Janko | Did anyone try using sqlite.r from Dobeash in free rebol code 2.7.6 on linux. (debian). I get "feature not available in this REBOL" but it works on windows. | 17-Mar 16:08 |
| 924 | Janko | . | 17-Mar 15:59 |
| 923 | Janko | a view into db file as hex quickly resolved which version is which, it is written at the start, and so I saw what is going on, thanks Petr | 11-Mar 8:12 |
| 922 | Janko | It worked for me but now I started to get "Not a database or encripted" error and I couldn't figure out why.. I discovered that the sqliteadmin app was making v2 files now and that was the problem. | 11-Mar 8:05 |
| 921 | Pekr | IIRC it supports version 3 of the driver. Dunno the name of .dll. But - just rename sqlite3.dll to sqlite.dll and run few tests, no? :-) | 11-Mar 7:59 |
| 920 | Janko | I can't seem to find info if dobedash's sqlite.r should work with sqlite3.dll or sqlite.dll, does anyone have any idea? | 11-Mar 7:52 |
| 919 | BrianH | I agree. None are planned at the moment, but it is likely that there will be more in the future. | 1-Mar 15:01 |
| 918 | Graham | I think it will be a long time before R3 is usable in the real world ... so my understanding is that there will still be 2.7 releases. | 1-Mar 6:37 |
| 917 | BrianH | There may be some changes to behavior that was so broken that it couldn't possibly have been relied on by existing code, like the change in return value of ALTER in 2.7.6. I would be surprised if the INSERT/dup/part bug Carl found this week isn't fixed in the next R2 release, for instance, whenever that release happens. | 1-Mar 5:03 |
| 916 | BrianH | We find new bugs in R2 natives all of the time - Carl just found one in the last week. There will likely be bugfix releases, but no incompatible changes will be made to R2 anymore, afaict. All of the new functions in R3 and incompatible improvements to existing functions will likely be made in a separate script that you can load or encap. That script will be maintained outside of the R2 update cycle. I think that will be the plan, at least. | 1-Mar 5:00 |
| 915 | ? | like = think | 1-Mar 2:38 |
| 914 | ? | I like 2.7.6 is the last of the 2.x series. Brian may release a library containing updated mezzanines and patches. | 1-Mar 2:38 |
| 913 | BrianH | Right now 2.7.7 is not being worked on at all. That sonds like a good thing to fix when we do start working on 2.7.7. | 1-Mar 1:26 |
| 912 | Graham | Is this GC problem being worked on for 2.7.7 ?? | 1-Mar 1:15 |
| 911 | amacleod | Robert, 'recycle' worked like a charm! Thanks a lot. I had the same problem when reading from the DB so I used 'recycle again in hte query loop.... This had the added benifit of of sealing amemory leak with the images too. Memory would increase evrytime a new image was displayed but now it tops out at an acceptable level.. | 1-Mar 0:40 |
| 910 | Robert | Overall I don't care to much, which way we go. It must be simple and straight forward to use. And we must have a way on the Rebol side to privode the C side of life. Like pointers and structs etc. without tricks. | 27-Feb 17:04 |
| 909 | Robert | I don't know anything about the plug-in approach at the moment, so I can't say. | 27-Feb 17:04 |
| 908 | Pekr | Robert - what is your preferable method for "DLL interface"? Is it a mixture of rebol dialect (as in R2) and plug-in aproach? | 27-Feb 16:09 |
| 907 | amacleod | Thanks Robert. | 27-Feb 14:30 |
| 906 | Robert | That's one cause why I want to get my hands on the external library interface in R3 ASAP. | 27-Feb 14:30 |
| 905 | Robert | You have to try. The SQLite driver uses a heuristic every 100 rounds or so. Just play around to see if it makes a difference. | 27-Feb 14:30 |
| 904 | amacleod | So at what point would I use recycle? After each insert? in my foreach loop? | 27-Feb 14:24 |
| 903 | Robert | I think it's on the way from Rebol to DLL. | 27-Feb 14:23 |
| 902 | amacleod | I'm able to get a large set of results from mysql and use it (View the images in a layout) but when I try to insert this data into sqlite it seems to get currupted... It sounds like a sqlite problem... | 27-Feb 14:04 |
| 901 | amacleod | no, How would that work when working with large database results? | 27-Feb 13:54 |
| 900 | Robert | IIRC there is a bug in Rebol's GC that can show up when using big datasets and corrupts data. Have you tried to play around with RECYCLE | 27-Feb 8:13 |
| 899 | amacleod | Actually size of the image does not seem to be the prob as this works: SQL reduce [{insert into images values (?,?,?,?,?,?,?,?)} "img/1" "img/2" "img/3" "img/4" "img/5" pic "img/7" "img/8"] where pic is a large 4000x3000 full color photo. I get no error. But if I loop 50 and insert the above data 50 times I get an error??? | 27-Feb 6:14 |
| 898 | amacleod | I see what might be the prob...
Some images are large...Too large? And may be getting truncated. I was using binary..should I use blob? | 27-Feb 5:51 |
| 897 | amacleod | I'm getting errors when I try to insert a binary file into sqlite I have no problem when I read/binary an image from disk and insert it but when I download it from a mysql db its saving as those crazy text characters. I'm converting it back to binary with "to-binary" and when I probe it it looks right but it keeps converting back to the original mysql output... Any ideas what might be going on? I can view the outputed image from mysql when I use to-binary so I know that its not currupted. | 27-Feb 5:32 |
| 896 | Pekr | Oldes - thanks - that is an interim solution for R2 :-) | 21-Jan 13:56 |
| 895 | Oldes | this is not sqlite retated, but: do http://box.lebeda.ws/~hmm/rebol/projects/ucs2/latest/make-ducet.r | 21-Jan 11:32 |
| 894 | Robert | Ok, now I got it. ;-) | 21-Jan 10:13 |
| 893 | Pekr | I want to sort in SQLite. But then you receive your recordset to REBOL, you put it into grid for e.g., which has facility for sorting columns. Then you press particular column, and grid sorts your result recordset using rebol's built in 'sort function ... and the result is wrong ... (well, but this is minor issue, the importance is to get correct resultset from the query. I just tried to say, that R3 has to address some localisation principles itself too ....) | 20-Jan 21:51 |
| 892 | Robert | But you can sort today in R2. Why do you need SQLite collations if you don't want to sort in SQLite? | 20-Jan 14:26 |
| 891 | Pekr | ... one more point for future. Imagine obtaining correct order from SQL, then using REBOL level grid, and column sort facility. I think that we also will need to get such things adressed in R3 directly, or it will distort sort order ... | 20-Jan 14:01 |
| 890 | Pekr | REBOL SQLite DLL? I don't want other DLL ... We need better interfacing to do it in REBOL as a binary, with back-pointer from C level :-) | 20-Jan 13:54 |
| 889 | Robert | And than have those collations hard coded to a Rebol SQLite DLL. | 20-Jan 13:47 |
| 888 | Robert | SET-SORTING-MODE "CZ" | 20-Jan 13:47 |
| 887 | Pekr | There is some collation function which we need to wrap. I posted it here some time ago, but we were not succesfull in wrapping and utilising it. I also tried to look into IIRC Python sources, and it was not clear to me, how to specify it in REBOL level. IIRC it is callback type function ... | 20-Jan 13:00 |
| 886 | Robert | Do you have any references to an country specific sorting implementation? Than I can take a look how to add it. | 20-Jan 12:33 |
| 885 | Robert | Putting this into SQLite would require, that we add those country specific sorting rules at the C level and provide a Rebol call, so select the correct sorting. | 20-Jan 12:33 |
| 884 | Robert | Ah, ok. How about sorting in Rebol? Does this help? | 20-Jan 12:32 |
| 883 | Pekr | use case? proper national sorting? IF you do some SELECT on field like last name, and you want some in-between results, e.g. A - D, then Czech alphabet has C with a hook upon it, and it is supposed to sort right after C, but without collation support it will sort after Z .... | 20-Jan 10:48 |
| 882 | Robert | Do you have a use-case for this? Never had this requirement. | 20-Jan 9:07 |
| 881 | Robert | Is this used to define user defined comparing functions? | 20-Jan 9:07 |
| 880 | Pekr | It would be nice to support collations too, but I was not successfull in wrapping that functionality .... | 20-Jan 8:58 |
| 879 | Robert | The newest version of SQLite adds support for nested transactions. I'm going to look at the SQLite driver to see how we can support this. | 20-Jan 7:53 |
| 878 | Robert | Ok, I thought there was a different trick. Well, I'm not using JOINS nor VIEWS a lot in my app. I preferr to get back Rebol blocks and traverse these and collect what I need. Much simpler than hacking long SQL statements. | 4-Jan 20:06 |
| 877 | sqlab | The new field OldID holds the former primary key. You have to join via the oldID instead of the primary key. If you can not alter your select statement, maybe you can generate an adequate view. | 4-Jan 17:54 |
| 876 | Robert | sqlab, don't understand what you mean. How will an oldID help me to get all records back together? This would require that the application knows about oldID. | 4-Jan 13:36 |
| 875 | sqlab | I just tried using an extra object with only the reference pointers. But I never checked, if it is working with more than one opened database because of the limitations regarding simulataneous access by more than one process. | 4-Jan 13:27 |
| 874 | sqlab | Why not transfer the old ID to a new indexed field oldID? | 4-Jan 13:24 |
| 873 | Pekr | of course you are right. You just have to be carefull or you could end-up with some "dead" child records. | 4-Jan 12:19 |
| 872 | Robert | How else will you do it if you transfer one set of related records from database A to a database B? | 4-Jan 11:05 |
| 871 | Pekr | rewriting IDs? A risky business :-) I have never done anything like that. | 4-Jan 11:01 |
| 870 | Robert | A bit OT: Has anybody an idea how a "schema driven" database export does/could work? I have an applicaiton that uses some tables, and records are linked by primary index IDs. Now I want to export a record and all its dependend records either into a new database or over the network to some other process. Because ID ranges are different in the export target database or on the remote server, I need to rewrite the old IDs with the new ones. At the moment I have a hand written, very app specific (and error prone) function for this. But I would like to do this in a much more generic fashion. Maybe just specifcing the relationship with some simple dialect and than have a generic function collecting everything. | 4-Jan 9:13 |
| 869 | Robert | FYI: I'm currently adding some stuff to Ashley's SQLite driver to: 1st: Handle in memory databases (":memory:") 2nd: To handle connection to more than one database file at once. So, if someone did this already pleasae let me know :-) | 4-Jan 9:10 |
| 868 | ManuM | . . | 23-Dec 15:16 |
| 867 | BrianH | I am not as familiar with the bugs in your SQLite access infrastructure as I am with SQLite itself, so I helped where I could :( | 22-Dec 20:38 |
| 866 | amacleod | Thanks for all the help. | 22-Dec 20:37 |
| 865 | BrianH | That is a good approach anyways, as it will help prevent SQL injection attacks. | 22-Dec 20:36 |
| 864 | amacleod | BrianH, Its working...I just do not know how to make it dynamic. | 22-Dec 20:36 |
| 863 | amacleod | to make it more sophisticated I can parse the search input for "aluminum ladders" and seperate words as having "and" between each...like most search engines. THan build it and insert it. | 22-Dec 20:35 |
| 862 | BrianH | If yo can be certain that any ' in your strings is being escaped properly, that may be the way to go. | 22-Dec 20:34 |
| 861 | amacleod | I gues I can build the {select * from fdbooks where ftext like '%ladder%'} string dynamically with rejoin and insert it: srch: {select * from fdbooks where ftext like '%ladder%'} reslts: sql reduce [srch] == [[2 "FFP-LADDERS" "1-PORTABLE LADDERS" "2." " SIZES AND TYPES OF PORTABLE LADDERS IN USE^/" "" 4-Dec-2008/15:29:1 9] [4 "FFP-LADD... | 22-Dec 20:32 |
| 860 | BrianH | Have you considered whether it is a casing issue? | 22-Dec 20:31 |
| 859 | BrianH | It sounds like you are on the right track, but running into bugs in the REBOL SQLite access infrastrcture. | 22-Dec 20:31 |
| 858 | amacleod | >> srch: "aluminum"
== "aluminum"
>> reslts: sql reduce [{select * from fdbooks where ftext like '%'||?||'%'} srch]
== [] >> srch: 'aluminum == aluminum >> reslts: sql reduce [{select * from fdbooks where ftext like '%'||?||'%'} srch] == [[9 "FFP-LADDERS" "1-PORTABLE LADDERS" "3." " CONSTRUCTION OF PORTABLE ALUMINUM LADDERS^/" "" 4-Dec-2008/15:29:19 ] [10 "FFP-LADD... | 22-Dec 20:28 |
| 857 | amacleod | insert and update use "?" so you can use variables. reslts: sql reduce [{select * from fdbooks where ftext like '%'||?||'%'} srch] == [] works when srch is a word! but not when it contains a string! | 22-Dec 20:25 |
| 856 | amacleod | Thanks BrianH, But how do I use it dynamically? | 22-Dec 20:23 |
| 855 | BrianH | If you want to search for a multiword string, use LIKE '%aluminum ladder%', then use OR to add other clauses. The choices above, with examples: 1: like '%aluminum%ladder%' 2: like '%aluminum ladder%' 3: like '%aluminum%' and like '%ladder%' 4: like '%aluminum%' or like '%ladder%' The || operator means string concatenation, not or. | 22-Dec 20:22 |
| 854 | amacleod | 2 and 3 I suppose | 22-Dec 20:16 |
| 853 | amacleod | I need to search for multi-word string "aluminum ladder" (in that order... but also would like to beable to search for the occurnace of multiple words in any order: "aluminum" and "Ladder" and "water" etc | 22-Dec 20:14 |
| 852 | BrianH | Pick one of the 4. | 22-Dec 20:13 |
| 851 | BrianH | Answer my question first then I can answer yours :) | 22-Dec 20:13 |
| 850 | amacleod | It gives different results... >> reslts: sql [{select * from fdbooks where ftext like '%aluminum%' || '%ladder%'}] == [[9 "FFP-LADDERS" "1-PORTABLE LADDERS" "3." " CONSTRUCTION OF PORTABLE ALUMINUM LADDERS^/" "" 4-Dec-2008/15:29:19 ] [10 "FFP-LADD... >> reslts: sql [{select * from fdbooks where ftext like '%ladder%aluminum%' }] == [[11 "FFP-LADDERS" "1-PORTABLE LADDERS" "3.1.1" { Solid Beam Aluminum Construction- This type of ladder has a solid side rail co... | 22-Dec 20:12 |
| 849 | BrianH | Are you checking for whether - Both of the words are in the string in that order - Both of the words are in the string directly adjacent to each other in that order - Both of the words are in the string in any order - Either of the words are in the string in any order ? | 22-Dec 20:11 |
| 848 | amacleod | How do I do that with variables? | 22-Dec 20:09 |
| 847 | BrianH | Equivalent to the above: {select * from fdbooks where ftext like '%aluminum%ladder%'} | 22-Dec 20:08 |
| 846 | amacleod | sqlab, >> reslts: sql reduce [{select * from fdbooks where ftext like '%aluminum%' || '%ladder%'}] == [[9 "FFP-LADDERS" "1-PORTABLE LADDERS" "3." " CONSTRUCTION OF PORTABLE ALUMINUM LADDERS^/" "" 4-Dec-2008/15:29:19 ] [10 "FFP-LADD... works. Thanks again...but | 22-Dec 20:06 |
| 845 | sqlab | Dont't use the reduce | 22-Dec 20:06 |
| 844 | sqlab | a || b is the sqlite syntax for concatenation of two strings. http://www.sqlite.org/lang_expr.html | 22-Dec 20:00 |
| 843 | Graham | oh ... sqlite ? ... ooops | 22-Dec 20:00 |
| 842 | BrianH | SQLite has a CONTAINS extension? | 22-Dec 19:59 |
| 841 | Graham | Instead of LIKE, you can also use CONTAINS | 22-Dec 19:59 |
| 840 | Graham | And once you learn sql, then you can look at the dialect .... which is a simple substitution thing. | 22-Dec 19:57 |
| 839 | Graham | Anyway, you don't need to use the sql dialect ... just create your sql statement and then execute it. | 22-Dec 19:55 |
| 838 | Graham | I've never tested whether the sql dialect can replace ? inside single quotes. | 22-Dec 19:54 |
| 837 | BrianH | Or REJOIN. | 22-Dec 19:54 |
| 836 | BrianH | Those || are string concatenation, afaict. Use JOIN in REBOL for that. | 22-Dec 19:53 |
| 835 | Graham | I don't know sqllite's syntax .. but this looks wrong to me reslts: sql reduce [{select * from fdbooks where ftext like '%aluminum ladders%'}] it should simply be reslts: sql {select * from fdbooks where ftext like '%aluminum ladders%'} | 22-Dec 19:53 |
| 834 | Graham | the sql statements are standard sql. You don't need to use the ? stuff .... ie. you can compose your statement before hand. | 22-Dec 19:52 |
| 833 | Graham | Then you substitute your variables ... that's it. | 22-Dec 19:51 |
| 832 | Graham | You construct your sql statement and test it. | 22-Dec 19:51 |
| 831 | amacleod | I do not see anything like that in any docs I can find. | 22-Dec 18:41 |
| 830 | amacleod | I can find the sql commands but I do not know how to rebolize them. Where can I find that I need to do this: '%'||?||'%' ? | 22-Dec 18:41 |
| 829 | Graham | You need a primer in sql ... | 22-Dec 18:22 |
| 828 | amacleod | And how could I figure this out myself. Is this syntax a part of the sqlite.r interface or sqlite itself? | 22-Dec 16:13 |
| 827 | amacleod | and while we are here... what about multiple words in string... "ladders" and "aluminum" as apposed to "aluminum ladders" | 22-Dec 16:12 |
| 826 | amacleod | What if I want multi-word string? as in; reslts: sql reduce [{select * from fdbooks where ftext like '%aluminum ladders%'}] == [[9 "FFP-LADDERS" "1-PORTABLE LADDERS" "3." " CONSTRUCTION OF PORTABLE ALUMINUM LADDERS^/" "" 4-Dec-2008/15:29:19 ] [10 "FFP-LADD... | 22-Dec 16:10 |
| 825 | amacleod | Not string? | 22-Dec 16:07 |
| 824 | amacleod | as word? | 22-Dec 16:06 |
| 823 | amacleod | I got it! it works when srch: 'ladders | 22-Dec 16:06 |
| 822 | amacleod | sqlab, reslts: sql reduce [{select * from fdbooks where ftext like '%'||?||'%'} srch] == [] where srch: "ladders" no error but not finding what should be there.: reslts: sql reduce [{select * from fdbooks where ftext like '%ladders%'}] == [[2 "FFP-LADDERS" "1-PORTABLE LADDERS" "2." " SIZES AND TYPES OF PORTABLE LADDERS IN USE^/" "" 4-Dec-2008/15:29:1 9] [4 "FFP-LADD... | 22-Dec 16:06 |
| 821 | PeterWood | I got this unexpected behaviour: >> do %~/Code/Library/Rebol/sqlite.r Script: "SQLite driver" (26-Nov-2008) >> connect %~/Code/SQLite3/mydata.db ** User Error: SQLite out of memory ** Near: make error! reform ["SQLite" error] >> change-dir %~/Code/SQLite3 == %/Users/peter/Code/SQLite3 / >> connect %mydata.db == 3400768 | 22-Dec 14:59 |
| 820 | sqlab | A concatenation like like '%' || ? || '%' shoud work | 22-Dec 6:13 |
| 819 | Graham | you have to create your dialected string first | 22-Dec 5:50 |
| 818 | amacleod | Ignore that last message...Sorry | 22-Dec 1:23 |
| 817 | amacleod | SQL reduce [{INSERT INTO table VALUES (?, ?, ?)} fname lname phone] | 22-Dec 1:22 |
| 816 | amacleod | This works: rslts: sql reduce [{SELECT * FROM books WHERE ftext LIKE '%string%' } ] | 22-Dec 0:59 |
| 815 | amacleod | I got the 'like' command working with a text string but I can not fiqure out how to use variables... something like: rslts: sql reduce [{SELECT * FROM books WHERE ftext LIKE '%?%' } var] | 22-Dec 0:38 |
| 814 | GiuseppeC | This also brings me a question: what about SQLite and UNICODE ? | 19-Dec 19:02 |
| 813 | GiuseppeC | Amacleod: if you search for ANSI 92 you should be able to find for the complete specification. | 19-Dec 19:02 |
| 812 | amacleod | Thanks, I try it out in the morning. I googled but could not find it. I've had trouble finding a good source of docs for sqlite before. w3schools looks good. Thanks again all! | 19-Dec 4:24 |
| 811 | ? | Amacleod - http://www.w3schools.com/sql/sql_like.asp | 18-Dec 14:57 |
| 810 | sqlab | select * from table where f_text like '%table%' but the wildcards may vary according the database | 18-Dec 14:54 |
| 809 | Chris | Begins with is LIKE '...%' Anywhere is LIKE '%...%' End is LIKE '%...' Rebol filenames: LIKE '%.r' | 18-Dec 12:24 |
| 808 | Robert | See SQLite homepage. It's something like this: LIKE '%...%' | 18-Dec 12:20 |
| 807 | amacleod | syntax? | 18-Dec 12:01 |
| 806 | sqlab | "like" is your friend | 18-Dec 7:06 |
| 805 | amacleod | Let me first make sure i need fts... I want to find a sub-string within a larger string: for example: find the the rows that contain word "table" in a column called f_text one row of which might contain "When inserting into an fts table, if no docid is provided," This would be a hit since table is contained in the string. | 18-Dec 1:39 |
| 804 | amacleod | does sqlite.r support these fts tables? | 18-Dec 1:33 |
| 803 | amacleod | how do you search for a string within a column. Is that what they refer to as full text search? (fts) | 18-Dec 1:24 |
| 802 | Ashley | Yep, by default the driver molds and loads REBOL values. Use /direct if you don't want this behaviour. | 14-Dec 21:48 |
| 801 | GiuseppeC | It seems to work even with block of lines | 14-Dec 15:15 |
| 800 | GiuseppeC | Forget it, just found the solution. My fear was all about string handling but everything seems going well. | 14-Dec 15:11 |
| 799 | GiuseppeC | Hello, I am experimenting with SQLite and I have a question: How do I store a web page retrieved with READ onto a SQLite field ? | 14-Dec 15:03 |
| 798 | amacleod | That's what happens when you work in hte middle of the night. | 4-Dec 16:57 |
| 797 | amacleod | Thanks Pekr. | 4-Dec 16:55 |
| 796 | amacleod | Found it!
I had my 'begin' of my transaction inside the loop. dope! | 4-Dec 16:55 |
| 795 | Pekr | which version of sqlite dll are you using? there was one bug with update, which I reported one year ago, which even corrupted data. They fixed it thought in next version. So hopefully this is not your case ... | 4-Dec 16:54 |
| 794 | amacleod | I found part of the problem... If I'm updating multiple records it fails to update but if update one record it updates. I'm using a transaction here. do I need to place each var in ()? | 4-Dec 16:46 |
| 793 | amacleod | Sorry I mean: nor this: SQL reduce [{UPDATE fdbooks SET bk=?, chap=?, sec_num=?, ftext=?, key_words=?, up_date=? WHERE ref_number=?} blk/2 blk/3 blk/4 blk/5 blk/6 (to-date blk/7) blk/1] | 4-Dec 16:29 |
| 792 | amacleod | Banging my head against a wall! This works: SQL reduce [{UPDATE notes SET note=?, up_date=? WHERE book=? AND chapter=? AND section=?} note_text now bk_tit/1 bk_tit/2 bk_tit/3] But this does not: SQL reduce ["Update fdbooks SET up_date=? WHERE ref_number=?" now ref] nor this: SQL reduce [{UPDATE notes SET note=?, up_date=? WHERE book=? AND chapter=? AND section=?} note_text now bk_tit/1 bk_tit/2 bk_tit/3] Any ideas? | 4-Dec 16:26 |
| 791 | Robert | I see two options:
1. We enhance the driver to be able to handle more database handles at the same time. This needs a way to select a database handle as the current one. 2. We make the driver as a prototype object which carries everything for one database handle. Than we need a way how to state which instance to use. | 4-Dec 12:59 |
| 790 | Robert | And than the returned DB handle has to be used for all actions against this database file. It's much like a file handler. | 4-Dec 11:25 |
| 789 | Robert | I see one problem, if the to-be-opened database doesn't exists yet it needs to be created. This can only be done by a call to sqlite3_open and not via the ATTACH sql command. | 4-Dec 11:24 |
| 788 | Ashley | Robert, I was thinking we can depreciate the /create refinement by making that implict as well ... and the change required to support additional CONNECTs after the first should be as simple as changing the line that reads: all [dbid sql-error "Already connected"] to something like: if all [dbid file? database] [ unless find file %/ [insert file what-dir] sql rejoin ["attach '" ... return ] which then raises the interesting question as to whether we should force database to be file! (so you'd have to attach multiple databases by issuing multiple CONNECTs ... it would certainly simply the CONNECT logic! ;) | 4-Dec 11:14 |
| 787 | Pekr | uff, so much? I expected something sub 500 ;-) | 4-Dec 11:00 |
| 786 | Robert | IIRC encryption costs $2000 | 4-Dec 10:40 |
| 785 | Robert | Look on the SQLite homepage under professional support. | 4-Dec 10:40 |
| 784 | Pekr | I am not hesitating to invest some money into it ... | 4-Dec 9:58 |
| 783 | Pekr | well, but at some point, you open-up that partition in order to be able to access it. The security is not there anymore. What I would like to have is direct SQLite low-level encryption, so that file might be visible to FS, but still encrypted. And your app provides password or something like that ... IIRC BrianH is using some such solution, I just don't remember its name. | 4-Dec 9:58 |
| 782 | Sunanda | Could you use (say) Truecrypt to host the data files on a encrypted partition? | 4-Dec 9:54 |
| 781 | Pekr | Any link? | 4-Dec 9:14 |
| 780 | Robert | I did buy the extension. Works very well. | 4-Dec 9:10 |
| 779 | Robert | Yes. | 4-Dec 9:09 |
| 778 | Pekr | Gyus, how to encrypt data in SQLite? I can't do it at app level (field storage level), as then SELECT would not work. Is the only solution to buy some SQLite variant, which encrypts at low level? | 4-Dec 9:01 |
| 777 | Robert | Ashley, ok. Let's do it together, I can spend some time to discuss things and code some variants. | 4-Dec 8:57 |
| 776 | amacleod | If I'm updating multiple fields is this the syntax?
SQL reduce ["update books SET bk=?, chap=?, section=?, up_date=? WHERE id=?" book chapter section update id] I do not get an error but it does not seem to be updating all the fields. | 4-Dec 4:08 |
| 775 | Ashley | I'll look into it ... I like the idea of making this implicit. | 3-Dec 21:59 |
| 774 | Robert | Maybe something like a current database. | 3-Dec 17:53 |
| 773 | Robert | Any ideas for this? | 3-Dec 17:53 |
| 772 | Robert | Ok, some more findings. I think the best way is to make a copy of the SQLite object for each database file. Than things are independent. The only thing to solve is to find an elegant way to select which SQLite object/connection to use without having to pre-fix all calls. | 3-Dec 17:51 |
| 771 | Robert | I now just commented the line that checks if a database is already connected. At least it now works but I'm not sure if this has some undesireable side-effects. | 3-Dec 15:11 |
| 770 | Robert | And those files exist. | 3-Dec 14:54 |
| 769 | Robert | There is already a handler for this case but only if all databases are given upfront in a block. | 3-Dec 14:39 |
| 768 | Robert | Forget the last one. Doesn't work. | 3-Dec 14:31 |
| 767 | Robert | BTW: I have this problem on Linux at the moment. The code runs with several calls on Windows. | 3-Dec 14:30 |
| 766 | Robert | I think makeing CONNECT handling this case implicit would make a lot of sense to make it simpler for users. So the programmer know, it's possible to alway call CONNECT/CREATE. What do you think? | 3-Dec 14:29 |
| 765 | Robert | Ashley, I just remembered that you can't call CONNECT/CREATE several times in one application. It gives the error "Database already connected" even if you use different file names. To open more than one database file you have to use the sql ATTACH command starting from the 2nd database file. | 3-Dec 14:28 |
| 764 | Ashley | 1.0.6 available at: http://www.dobeash.com/download.html Fixes finalize error as documented at: http://www.mtcnet.net/~henryvm/sqlite/ | 26-Nov 12:12 |