Re: [Oorexx-devel] ooSQLite usage

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Found one strange thing
The program creates an array of SQL insert statements, after parse of the source file. 
This array is then used to do the actuall insert in the memory database (the array is also saved to a file)

memDB = .ooSQLiteConnection~new(":memory:")
....
discDB = .ooSQLiteBackup~new(memDB, fileDB)
......
do sql over mycmds
   stmt = .ooSQLiteStmt~new(memDB, sql)
   stmt~step
   stmt~finalize 
end
and then
/* here we do the backup to disc */
  ret = discdb~step(-1)
  if ret == discDB~DONE then do
    say 'Back up finished with no error:'  --discDB~lastErrCode discDB~lastErrMsg
  end
  memDB~close  

What's happening is that one empty record is inserted in the database !

creating an in memory database with ooSQlLite3 ( ooSQLite :memory:)
and create table and then loading the table with .read mycmds.txt will not create an empy row in memory database

the last 2 record of input file (array) is
INSERT INTO LIDS (lid, uid, empid, Name, privileges) VALUES('xxxxx', yyyyyyyyyyy       ', '  zzzzz', 'name', privileges');             -- can't show real values   
--empty line -- (just empty x'0D0A' )

file is created with
f =.stream~new(mycmds.txt)        
do line over mycmds                    /* Write the lines */
 f~lineout(line)
end
f~close
-------------------------
Ursprungligt Meddelande:
Från: hakan <he...@us...>
Till: oor...@li...
Kopia: 
Datum: torsdag, 14 juni 2012 21:22
Ämne: Re: [Oorexx-devel] ooSQLite usage
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 <he...@us...>
Till: Mark Miesfeld <mie...@gm...>
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 <mie...@gm...>
Till: he...@us..., Open Object Rexx Developer Mailing List <oor...@li...>
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 <he...@us...> 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
Oor...@li...
https://lists.sourceforge.net/lists/listinfo/oorexx-devel

Re: [Oorexx-devel] ooSQLite usage

REXX interpreter

Re: [Oorexx-devel] ooSQLite usage