Good news. I'm still fooling with the backup object a little, but probably won't change anything at this point. I'm working on the doc for the ooSQLiteBackup object and the functional equivalents.
My plan is to put up a new package this weekend that will have the online backup complete, along with doc finished for that portion. I have also updated my source with SQLite 3.7.13, I just haven't committed it yet. So, the next package will be based on 3.7.13. When the ooSQLite package reaches the release stage, my plan is update the package with the latest SQLite version when they do a release. Then, I will place the prebuilt binaries on SourceForge. This will allow users that are not comfortable with compiling and building to always have access to the latest SQLite version. At most, ooSQLite will lag by one day a new SQLite version. I'm trying to keep a running list of changes between package versions put on SourceForge, as I make a change, in the ReleaseNotes file. You may want to watch that file to be alerted to changes in the source that may effect you. -- Mark Miesfeld On Thu, Jun 14, 2012 at 12:22 PM, hakan <[email protected]> wrote: > As I saw that ooSQLite was updated in incubator, regarding the SQLite backup > API,I downloaded the source and compiled it under windows 7 64bit. From the > sample /testing/loadDatabase.rex, I made my own database loading rex. > The program creates a sqlite database in memory, with 6 columns, read and > parse a 181Mb file and creates SQL insert statements, then inserts the > resulting records (101657 records) into the memory database and last step > save the in memory database to disc. > This programs elapsed time was around 61-65 seconds( including copy to disc) > , compared to the previous ~17 MINUTES when using a disc based database only, > with same data. > Great improvment. > By the way I also downloaded the latest SQLite source 3.7.13 and used that in > the compile above. > All works very well. > /hex > > ------------------------- > Ursprungligt Meddelande: > Från: hakan <[email protected]> > Till: Mark Miesfeld <[email protected]> > Kopia: > Datum: fredag, 08 juni 2012 22:20 > Ämne: Re: [Oorexx-devel] ooSQLite usage > Well, tried with BEGIN TRANSACTION..... COMMIT and cut the time by around 5 > minutes, so when initial loading is done, it seems that inmemory database is > much quickier, because of hardisc speed, if a possibility to save the > inmemory database to harddisc is available. > Looking forward to your implementation of the .backup (.dot ) commands in > ooSQLite. > Keep up your good work (as a side note I am very satisfied with ooDialog 4.2 > excellent work, I love it, maybe as I now understand how it works (at least > to some point) and how to use it together with ooRexx, OO is hard for an old > mainframer :-) ) > /hex > > > > ------------------------- > Ursprungligt Meddelande: > Från: Mark Miesfeld <[email protected]> > Till: [email protected], Open Object Rexx Developer Mailing List > <[email protected]> > Kopia: > Datum: fredag, 08 juni 2012 21:13 > Ämne: Re: [Oorexx-devel] ooSQLite usage > Very cool. > > On Fri, Jun 8, 2012 at 11:07 AM, hakan <[email protected]> wrote: >> After trying the ooSQLite package, I must say I am very impressed (well I am >> not a DB person, so the learning curve is step) but I find it useful for >> some of my ooREXX programs. >> The samples have been very helpful, I am now able to create, load and query >> a database/tables in ooREXX. >> One thing is performance, when creating a new database, I have read/parsed a >> file and then loaded it (92 000 rows) to a database/table , took around 17 >> minutes on a i3 2.13Ghz ! > > From my reading (but I'm also not a DB person) this is a problem with > INSERTS with not using an explicit BEGIN TRANSACTION COMMIT. > > As I understand it, for every individual SQL statement executed, > SQLite will implicitly add the BEGIN TRANSACTION / COMMIT if it is not > already within a transaction. The SQLite engine only does about 4 or > 5 inserts a second under this condition. > > If you add the explicit BEGIN TRANSACTION before the first insert and > then end with COMMIT the speed should be more normal. Something like > this: > > BEGIN TRANSACTION; > INSERT INTO "episodes" VALUES(0, NULL, 'Good News Bad News'); > INSERT INTO "episodes" VALUES(1, 1, 'Male Unbonding'); > INSERT INTO "episodes" VALUES(2, 1, 'The Stake Out'); > INSERT INTO "episodes" VALUES(3, 1, 'The Robbery'); > ... > INSERT INTO "food_types" VALUES(13, 'Seafood'); > INSERT INTO "food_types" VALUES(14, 'Soup'); > INSERT INTO "food_types" VALUES(15, 'Vegetables'); > COMMIT; > > I guess it is COMMIT not END TRANSACTION. I first thought it was END > TRANSACTION. > > The above is the raw SQL, I'm assuming you are wrapping the raw SQL in > a prepared statement. > > >> I then tried to to create a ooSQLite database in memory instead of a file >> (ooSQLite supports this, I used :memory: instead of a filename) then loading >> the same amount of data took around 15-20 seconds, >> but there is one problem I can't save the in memory database to a file with >> ooSQLite, as I understand from SQLite website there exist an backup API that >> let you save an in memory database to disc. >> I have tried it with the ooSQLITE3.exe (cli interface) doing the same as the >> ooRexx program( except backup) above does, by opening a :memory: database >> then create a table, then use .read file with SQL inserts and last use the >> .backup file CLI command. The result is that with the same amont of rows >> (inserts) it took around 30 seconds to have the database copied to disc >> including the SQl inserts. > >> So, Question is is it possible to implement the backup api (to be able to >> copy a in memory database to disc) in ooSQLite, maybe also to make inserts >> into a database from a file (like the .read file CLi command). ? >> I may have missed something regarding this! > > Yes, both of those things are next on my list. Or at least very near > the top. I intend to support the backup API as the next coding I do. > > In addition, I've planned from the start to support the "dot" commands > present in the command line shell in ooSQLite classes. The most > important ones like .dump, .read, .schema, etc., first. Maybe not all > of them, I'm not sure they all make sense. But .dump and .read are > very useful. > > Right now, my plan is to add them to the .ooSQLite class. So, something like: > > .ooSQLite~read(dbObj, fileName) would read the SQL statements in > fileName and execute them on the database connection dbObj. > > .ooSQLite~dump(dbObj, fileName) would dump the data base to fileName. > > Although both of those commands could be added to .ooSQLiteConnection > instead, makeing it: > > dbConn~dump(fileName) > dbConn~read(fileName) > > I haven't though out the details completely yet, I just intend to add > them as soon as possible. > > For the backup API, the plan is to have a ooSQLiteBackup class.: > > obj = .ooSQLiteBackup(dbConnObj) > > then methods on the class to do the backup. > > I haven't tried the BEGIN TRANSACTION / COMMIT yet. But I think > everything is in place to do it. Just like using :memory for the file > name. I hadn't tried it yet, but I knew it should work. > > Let me know if you run into any bugs. You can open them in Tracker, > probably best, so I don't forget them. > > For other people reading this, and general FYI, because I haven't > mentioned it. > > In the bin directory is also an executable, ooSQLite3.exe on Windows, > oosqlite3 on Linux. > > This executable is a command line shell used to work with any SQLite database. > > C:\work.ooRexx\wc\incubator\ooSQLite\testing>ooSQLite3 ooFoods.rdbx > SQLite version 3.7.12.1 2012-05-22 02:45:53 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .schema > CREATE TABLE episodes ( > id integer primary key, > season int, > name text ); > CREATE TABLE food_types( > id integer primary key, > name text ); > CREATE TABLE foods( > id integer primary key, > type_id integer, > name text ); > CREATE TABLE foods_episodes( > food_id integer, > episode_id integer ); > sqlite> > > Type the ".help" to see commands. This is the exact command line > shell provided by SQLite, just compiled during the ooSQLite build, > named to ooSQLite.exe, and added to the package. Currently there is > no change to the source code so it should work exactly like the SQLite > command line shell. > > The reason I hadn't mentioned it yet is that the name change is not > designed to conceal the source of the executable. It is included for > convenience to ooRexx programmers and to 'fit' with the naming > conventions of the package. > > The doc will clearly state that, and I wasn't going to mention the > executable until I had the statement in the doc. I don't want any one > thinking I wrote ooSQLite.exe myself. > > > -- > Mark Miesfeld > > ------------------------------------------------------------------------------ > Live Security Virtual Conference > Exclusive live event will cover all the ways today's security and > threat landscape has changed and how IT managers can respond. Discussions > will include endpoint security, mobile security and the latest in malware > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ > _______________________________________________ > Oorexx-devel mailing list > [email protected] > https://lists.sourceforge.net/lists/listinfo/oorexx-devel ------------------------------------------------------------------------------ Live Security Virtual Conference Exclusive live event will cover all the ways today's security and threat landscape has changed and how IT managers can respond. Discussions will include endpoint security, mobile security and the latest in malware threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/ _______________________________________________ Oorexx-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/oorexx-devel
