AltME groups: search

About 50k of REBOL/Core source. Build as a relative level and an 
index level on top of that - top-most a full relational DB. I've 
also started a SQL interface, but I'm not sure, if I wanna finish 
that part. :-)

if anyone uses, mysql, they can rewrite the sql statements.

When I can get that simple part working then I want to add Make-Doc, 
my-sql-protocol and Andrew's ML dialect as services so I can get 
some leverage from these.

  MySQL 5.0 Adds Features for Enterprise Developers and DBAs
by Ken 
  North

Baseball legend Satchel Paige is famous for having said 

Don't look back, something might be gaining on you." Companies selling 
a commercial SQL database management system (DBMS) know its MySQL 
that's gaining on them. With an already large installed base, MySQL 
is set to attract new users because of the feature set of version 
5.0. It includes capabilities for which developers have often turned 
to commercial SQL products.


The purposes for which we use personal, mobile, workgroup, departmental, 
enterprise and web databases are diverse. Application requirements 
are a primary determinant of the capacity and features we need from 
an SQL DBMS. For example, a high-volume transaction processing web 
site places greater demands on a database than a contact list manager 
for laptops and small business servers.


A Web Techniques magazine article, "Web Databases: Fun with Guests 
or Risky Business?" discussed features that characterize an industrial-grade 
SQL DBMS. It explained SQL security and mission-critical databases, 
defined as


    "A database is mission critical if its lack of data integrity has 
    serious consequences, such as causing the loss of customers or even 
    lives."


Maintaining data integrity is implicit -- that's a prime directive 
for a DBMS. The article explained other features that enterprise 
developers look for in an SQL platform:


    ... mission-critical applications require features such as intrinsic 
    security, transaction journaling, concurrency controls and the ability 
    to enforce data integrity constraints. Without those features, you 
    do not have secure, robust databases. Connecting a database to a 
    Web server adds other requirements, such as a multithreaded architecture 
    and the ability to do database backups without taking the server 
    down.


    Freeware and PC DBMSs are suitable for certain classes of applications, 
    but not for high-volume Web sites and mission-critical databases. 
    In any case, don't bet your business, or lives, on such software 
    unless you have the source code and the expertise to understand and 
    repair it.


Since that article appeared in print, improvements to MySQL have 
removed the "not ready for prime time" label. Features described 
in that article are now available to MySQL users:

    * transactions
    * concurrency control, locking, SQL standard isolation levels
    * intrinsic security
    * integrity constraints
    * thread-based memory allocation.

  TII Computer Deals at Dell Home Systems 180x150
	


MySQL uses separate threads to handle TCP/IP and named pipes connections, 
authentication, signaling, alarms and replication. The combination 
of threaded architecture and MySQL clustering provides powerful parallel 
processing capabilities. MySQL can process transactions in parallel 
with separate connections on separate processors using separate threads.
MySQL Milestones


A decade of development has moved MySQL out of the bare-bones DBMS 
category, enlarged its user base, and turned MySQL AB into a profitable 
company. One of the important milestones was integration of the InnoDB 
engine with MySQL 4.0. That upgrade gave MySQL multiple tablespaces, 
tables greater than 4GB and support for transaction processing. Other 
enhancements included OpenGIS spatial data types and hot backups. 
The latter enables a DBA to perform a backup without taking the DBMS 
offline. Hot backup software is available as a commercial add-on 
for databases using the InnoDB storage engine. 


MySQL 5.0, the newest version, is a major milestone. There have been 
enhancements to the tool sets, storage engines, types and metadata. 
 MySQL 5.0 includes features enterprise developers have come to expect 
from commercial SQL products. 

    * capacity for very large databases
    * stored procedures
    * triggers
    * named-updateable views
    * server-side cursors
    * type enhancements
    * standards-compliant metadata (INFORMATION_SCHEMA)
    * XA-style distributed transactions
    * hot backups.


MySQL has a demonstrated capacity for managing very large databases. 
Mytrix, Inc. maintains an extensive collection of Internet statistics 
in a one terabyte (1 TB) data warehouse that contains 20 billion 
rows of data. Sabre Holdings runs the oldest and largest online travel 
reservation system. It replicates 10-60 gigabytes per day from its 
master database to a MySQL server farm. The MySQL databases are used 
to support a shopping application that can accommodate a million 
fare changes per day."

After Ruby on rails, Sql on rails. How to make killer web apps: http://sqlonrails.org/screencast

With abstractions, unit-testing, versioning, environment scoping 
(development, testing, production), autodomentation, portability, 
compatibility (any SQL db here), etc. Rails rocks.

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.

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.

Data structure makes sense, what's the problematic SQL statement? 
(in its simplest form)

Just write the SQL statement as you would in MySQL, etc ... then 
we can see where the issues are.

that was just an example simple schema, but well, I may try to write 
short SQL query from that ;-)

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.

Yes, that might be possible ... I am not that skilled in SQL yet, 
so maybe it could be done other way ....

Thanks, I'll take a look at it. (Marco's sql-protocol.r is also a 
good read).

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

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]

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?

not sure your sql query rewrite was much clearer, but - your last 
post makes so much sense!

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.

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?

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.

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.

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.

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 ;-)

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.

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.

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

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.

I think the find clause here might work for you: http://www.dobeash.com/RebDB/sql-guide.html#section-4

sqlite3-protocol.r has a minor bug whereby locals/cols are not cleared. 
Fix is to add a "clear cols" at the beginning of the ' sqlite-exec 
func. Two other changes I made to this function were:


1) Changing “SQLITE_TEXT [any [attempt [load val: sqlite3/column_text 
stmt j] val]]” so as REBOL values are returned, and

2) Removing the /only clause from "system/words/insert/only tail 
result col" for those that prefer flat data structures (i.e. non-blocked 
records)

Finally, a simple wrapper makes the whole thing more usable:

context [

	db: none

	set 'open-db func [name [file!]] [
		db: open join sqlite://localhost/ name
	]

	set 'close-db does [
		close db
	]

	set 'describe func ['table [word!]] [

  insert db rejoin ["select type, name, sql from sqlite_master where 
  upper(tbl_name) = '" uppercase form table "' order by rootpage"]
		db/locals/sqlresult
	]

	set 'sql function [arg [string! block!]] [statement] [
		case [
			string? arg [insert db arg]
			string? first arg [
				statement: copy first arg
				repeat i -1 + length? arg [
					replace/all statement join ":" i pick arg i + 1
				]
				insert db statement
			]
		]
		db/locals/sqlresult
	]
]

which lets you do stuff like:

>> open-db %test.db
>> sql "create table t1 (col1 INTEGER, col2 TEXT)"
== []
>> describe t1
== [table t1 "CREATE TABLE t1 (col1 INTEGER, col2 TEXT)"]
>> sql reduce ["insert into t1 values (1,':1')" now/date]
== []
>> sql "select * from t1"
== [1 13-Feb-2006]
>> close-db

I will once they've settled down ... the SQLITE_TEXT change mentioned 
previously should read:

	SQLITE_TEXT [
		val: sqlite3/column_text stmt j
		any [
			all [
				attempt [tmp: load val]
				not block? tmp
				not word? tmp
				tmp
			]
			val
		]
	]


which should correctly handle TEXT with embedded white-space or illegal 
load chars. The context of funcs can also have the following useful 
func added:

	set 'sql-columns does [
		copy db/locals/cols
	]


and should probably 'copy return db/locals/sqlresult in both the 
'sql and 'describe functions.

What I don'T like about sqlite is that 1) it uses binary storage 
and that 2) it uses all-in-one-file aproach. I know it is low level, 
but I prefer directory/one-file-per-db aproach of RebDB kind of cool 
... I can watch/backup small dbs on per file base, not on going into 
sql base, to find out what internally changed ...

I am also used to rebol blocks. Somehow using string mode for constructing 
sql query is one level off for me, but otoh maybe better, because 
with block mode you sometimes don't cover all syntax options or you 
simply has to learn how to use it, whereas with string you simply 
compose default sql query and you can cut and paste examples or queries 
from docs, visual tools, etc.

I've given up on the one table per database idea as the default attachment 
limit is 10 files. On the positive side, the ATTACH command seems 
to work properly under 3.0+ and table names unique to an attached 
database do not have to be prefixed in queries. ;) My 'connect function:

>> help connect
USAGE:
    CONNECT database /create /attach databases /blocked /direct

DESCRIPTION:
     Open a SQLite database.
     CONNECT is a function value.

ARGUMENTS:
     database -- (Type: file url)

REFINEMENTS:
     /create -- Create database if non-existent

     /attach -- Attach up to 10 databases (of type file!) to this connection
         databases -- Database files (Type: block)
     /blocked -- Return each row as a block
     /direct -- Do not mold/load REBOL values

lets you do this:


 connect/attach %/c/app/data/system.db [%/c/app/data/users.db %/c/app/data/reports.db]
	sql "select * from a-system-table"
	sql "select * from a-user-table"
	sql "select * from a-report-table"


which could be quite useful in many situations. The default, however, 
is now one database.


BTW, my rewrite (even after adding a lot more functionality) is about 
twice as fast as the original protocol version.

Ashley - maybe I vote for functions having sql- or sqlite- prefix 
.... sql then could become sql-query .... because ... standalone 
"disconnect" in rebol script is a bit strangely looking .... in rebol 
you can disconnect from many things ... it does not look obvisous 
at first sight .... what do others think?

I would prefer setting /blocked or not, /direct or not, as a result 
fo query, so maybe those refinements should be available with /sql 
function instead?

Bumped the version to 0.1.2 with three enhancements:

1) Automatic NULL -> none! conversion (and vice versa)

2) 'sql now handles a SQLITE_BUSY return code by retrying the failing 
sqlite3_step 5 times at one second intervals

3) About value (number of retries) can be set with a new connect/timeout 
refinement

It seems you want cursors.

Then you can separate preparing of an sql statement and fetching 
from the cursor.

This would allow to reset an already compiled statement and to redo 
 do it without a new compilation.
Or even to cache compiled statements as Caché does it.

1) why did you name somehow not traditionally functions as connect 
and disconnect?


open and close are already taken and I wanted to avoid pathing (sqlite/open) 
or prefixes (sqlite-open) as the number of words added to the global 
context is relatively few and I wanted them to be meaningful words 
in their own right (same reason why VID doesn't have vid-layout, 
vid-center-face, etc). Connect and disconnect are the commands used 
to login/logout of another user in many databases; so while the context 
is not strictly correct, they are recognisable DB words.

2) why can't we support multiple name-spaces?


You can via the connect/attach refinement (and this lets you transparently 
reference tables across multiple "databases"). Why would you want 
to concurrently access multiple databases that are *not* related 
to each other in any way?

3) can we have opened only one db at a time?


Yes, bar /attach as above. The benefit of this approach is that you 
minimise the number of file locks a single process obtains, and you 
don't have to track state information (i.e. you don't need to tell 
'sql which DB you are referring to).


4) Would it also be possible to extend e.g. 'headings to return non-string 
 values?

No, as it may contain strings like "count(*)"

5) don't agree with exposing functions as you did


I tend to write CONNECT, SQL, etc in upper-case so db references 
embedded in my code stand out. Come up with a better naming scheme 
that everyone likes though and I'll adopt it. ;)

you used db-open even for RebDB

 ... that's because it needed to define about 20 low-level SQL statement 
 functions like 'select, 'delete, ''show, 'close, 'insert, etc that 
 conflicted with existing REBOL words and could not be easily renamed 
 (folks expect a SELECT statement, an UPDATE statement, etc or something 
 that resembles it). With the SQLite driver, all these statements 
 are handled by the SQLite library itself so we only need to provide 
 a few high-level accessor functions like SQL and DESCRIBE; these 
 types of words don't conflict with REBOL's lower-level atomic action 
 type words so there isn't the same need to group and isolate them 
 by prefix and/or context.


kind of disadvantage here to not refer by pointer. We are used to 
it, no?

 But is it *needed*? If you *must* work with multiple databases concurrently 
 and *cannot* have them attached, then SQLite is probably not the 
 right solution for you anyway ... it's an embedded C library with 
 limited concurrency and no user rights management after all. ;)

And another question ... I have a table which conains string data 
...

sql {select * from person where firstname = "Ingo"}


does not find the row (tried with singel quotes, too). What am I 
doing wrong? It goes without saying, that the data is actually there, 
and I can find it using the following SQL

sql {select * from person where firstname like "%ingo%"}

REBOL supports a maximum of 16 callbacks; so to avoid this error 
don't do %sqlite.r more than once within a script (and there is no 
sensible reason to do so anyway).


As for strings, remember that the driver mold's them; so they are 
actually stored as "string" (inclusive of quotes). You can reference 
them in one of two ways:

	sql {select * from table where col = '"string"'}

or

	sql ["select * from table where col = ?" "string"]


The second form is preferred as the binding is handled for you by 
the driver. I'm in the process of writing a SQLite Driver Guide (that 
covers this and other tricks) but it's a week or two away at the 
moment.

Thanks Ashley,

I found out the first point by myself, and just added a check whether 
'sql has already been set before doing sqlite.r.

But so far I had no idea about the molding of strings, I could have 
tried for months, I guess ;-)

Hi Ashlsy, I found a bug in sqlite.r. 'sql reuses its own return 
value on subsequent calls. so ...

>> all: sql "select * from persons"
== [["Ivo" "Hohmann" ...] ....]
>> me: sql {select * from persons where firstname = '"ingo"'}
== [["Ingo" "Hohmann" ...] ...]
>> all =? me
== true

Deliberate design that. The last line of 'sql is simply:

	buffer

not:

	copy/deep buffer


This is important when dealing with a large number of values as you 
want to pass a reference not double the amount of memory used with 
a redundant copy/deep! I'll add this "gotcha" to the documentation 
I'm writing.

clear buffer

 is also an optimization as it defaults to 32K values (make block! 
 1032 * 32) and I don't won't to reallocate it each SQL call. The 
 following benchmarks (Transactions Per Second) give some backround 
 to the design decisions I made:

	buffer				1744718
	copy buffer			282
	copy/deep buffer		76

	clear buffer			1144733
	buffer: copy []			824352
	buffer: make block! 32768	387


So the approach I took optimizes for large result sets by allocating 
a large buffer once up-front and then just referencing it thereafter.

ah, I am dumb ... that is distribution of funcionality between sql 
and connect which confused me ...

imo that should be somehow consolidated - why connect uses /flat 
to not blockify, and /direct to not mold/all, but sql function uses 
/raw for both of functionalities?

I like those features being separated as in connect, du not understand 
why 'sql has /direct for not molding, and /raw combining /direct 
and /flat ... but it does lack /flat itself :-)

/no-copy is a reasonable compromise, and fits with the /flat refinement 
in that the driver defaults to the expected "safe" behaviour. I'll 
add that to the next build.


XML output: I'm adding HTML output to the next build; is XML needed 
in addition to that? And if so, what does a "standard" table definition 
look like under XML (please provide a short example).


SQL refinements: the idea is that in most cases you specify all the 
refinements you need with CONNECT and just use SQL without any; except 
in the following two special cases:


1. SQL/direct: where you used CONNECT (without the /direct refinement), 
but need to access a *specific* table that does not need its TEXT 
column values MOLDed (e.g. sqlite_master)


2. SQL/raw: as above but flattens data. Used primary to access system 
tables / data, this ensures that values are always returned in a 
consistent format; lets you write code like:

	to file! third DATABASE


without having to worry about the format of the returned block(s).


Grammatical question. Do folks prefer I use the word INDEXES or INDICES 
to refer to more than one INDEX?

SQL/raw: For the sake of clarity, I've renamed this /flat and changed 
all "SQL/raw" calls to "SQL/flat/direct".

0.1.6 available at: http://www.dobeash.com/SQLite/sqlite.r


Now that the driver functionality has stabilized, this build focuses 
on delivering optimal performance. Changes of note include:

	- no-copy directive added

 - Added a new /info refinement to control when column names and widths 
 are obtained
	- main SQL loop rewritten to be approx twice as fast as 0.1.5
	- format / pad optimised (also about twice as fast as well)

 - IMPORT removed (better to use sqlite admin tool for large externel 
 loads / unloads)
	- Example code removed to http://www.dobeash.com/SQLite/demo.r

 - Code reordered and tidied up (directives appear at beggining of 
 context and routines defined on a single line each)

Enjoy!

btw, do you know if it's possible to separate sqlite's engine from 
the rest of the code? (sql, etc) we'd need only the low level stuff, 
file format, indexind etc.

If someone has an SDK licence could they confirm this strange behaviour 
by replacing the last line of %sqlite.r with:

attempt [delete %test.db]
connect/create %test.db
sql "create table t (c)"
sql "insert into t values (1)"
print mold SQL ["select * from t where c = ?" 1]
wait 2


and encapping it with either enpro or enface. Run both the script 
and the encapped version and compare the output ... I get an empty 
block returned by any statement that makes use of bind variables 
and is encapped. DOing %sqlite.r from an encapped script works fine, 
as does something like:

	do uncompress #{789C...

so my only guess is it's a binding issue of some sort.

One strange bug remains. Occasionally %demo.r will fail (typically 
selecting rows from Items) due to garbage characters that somehow 
get inserted. I have reduced the problem down to this script:

REBOL []

unless value? 'SQLite [do %sqlite.r]

repeat cnt 10 [
	prin ["^/Run" cnt "..."]
	;	Clean up from previous runs
	error? try [delete %test.db]
	error? try [delete %test.db-journal]
	;	Create Items (1000 rows) records
	prin " create ..."
	CONNECT/flat/create %test.db
	SQL "create table t (c1,c2,c3,c4,c5)"
	prin " insert ..."
	SQL "begin"
;	loop 1000 [
	repeat z 10000 [

;		SQL reduce ["insert into t values (?,?,?,?,?)" 1 "A 1" $1 1 $1 
* 1]

  SQL reduce ["insert into t values (?,?,?,?,?)" 1 reform ["A" 1] $1 
  1 $1 * 1]
	]
	SQL "commit"
	prin " select ..."
	SQL "select * from t"
	DISCONNECT
]

quit


Running the script should cause a failure like the following within 
the first couple of runs:


Run 1 ... create ... insert ... select ...** Syntax Error: Invalid 
tag -- <C
** Near: (line 1) À<C"
>> sqlite/direct?: true
== true
>> sql "select * from t where c2 like '%<%'"
== [1 {À<C^B"} "$1.00" 1 "$1.00"]


Changing the repeat to a loop seems to shift the error, often (but 
not always) making it take more runs to materialize. Replacing the 
reform with a string will often (but not always) allow all runs to 
complete successfully. Changing the number or order of INSERTed values 
also seems to shift the error. I'm not sure whether this is a REBOL 
or SQLite library error, but any help in tracking it down would be 
greatly appreciated.

The high memory consumption and crashing of an process or should 
I say silently vanishing, that I observed with this routine after 
two consecutive runnings, I saw already  sporadic with the former 
releases after some sql errors and trying to resolve a busy lock 
on the db with some tricks, for example repeating a step.

connect/create %test.db
sql "create table test (id, name, last_name)"
sql {insert into test values (1, "Petr", "Krenzelok")}
sql {select * from test}

; returns [[1 Petr Krenzelok]]   ... sqlite3 does not do that according 
to Bobik's info ... I am not sure I like it - it convert apparent 
string to word, I don't want that and I am not sure I want to use 
/direct for that, that seem like a reverse logic to me ...

ah, now I see ... and I have questions .... Ashley, when the sql 
query is not block, but a string - you send the query as-is? Why 
the difference? with ' it works, with " it does not ... can the functionality 
be made just the same? I think most ppl are used to "quote" string, 
not to 'quote it like that' .... I would prefer driver to return 
an error for quotes, if they can't be used, instead of returning 
string as a word :-)

it is inconsistent with:


sql ["insert into test values (1, ?, ?)" "Petr" "Krenzelok"] ; in 
this case it returns strings, not words ... imo string and block 
sql queries should be compatible

I'm not following this , but in sql ' is used as the quote character 
for literal strings, not "

Hmm, adding /direct to the example posted previously and changing 
the last part of the INSERT to "... form $1 1 form $1 * 1" seems 
to work properly (100 error-free runs so far). The *only* difference 
then is in this line of the value binding logic:

	unless direct [val: mold/all val]

which if you change it to something like:

	unless direct [p: mold/all val]
	*bind-text sid i p length? p 0


seems to handle more runs before a failure. Thinking that mold/all 
might be the problem I then reran my /direct test with the following 
SQL statement:


 SQL reduce ["insert into t values (?,?,?,?,?)" 1 mold/all reform 
 ["A" 1] mold/all $1 1 mold/all $1 * 1]


which is functionally equivalent to the failing statement ... but 
no failures (after 100 runs). So, the conditions needed to reproduce 
this error [so far] are:

	SQLite library
	INSERT statement using a particular sequence of bind variables
	MOLD/ALL coded / used in a particular manner
	High volume of INSERTs

Now is that an obscure error or what? ;)

But they are not the same way ...

	SQL "insert into t values ('text')
	SQL {insert into t values ('"text"')}

map to:


 SQL ["insert into t values (?)" "text"]	; with /direct refinement

 SQL ["insert into t values (?)" "text"]	; without /direct refinement


The first approach in each case is saying, "I want this value to 
be stored as a SQLite TEXT value which will not be LOADed upon retrieval"; 
while the second is saying, "I want this value to be stored as a 
MOLDed SQLite TEXT value which will be LOADed upon retrieval back 
into a REBOL string value (as opposed to the word 'text)".


A string! statement is strictly literal, it is passed onto SQLite 
with no parsing or conversion. If you want to bind values, use the 
block form ... that's what it's there for!

Does sqlite support timestamp/date fields ?  Is there a way to set 
a default value for a field ( so that on a sql insert, you don't 
have to explicitly mention that column  ) ?

Graham, yes to all the above except timestamp/date fields. SQLite 
only supports 5 datatypes: Integer, Decimal, Binary, Null and Text. 
The driver (unless using the /direct refinement to connect) MOLDs 
and LOADs other REBOL types such as date!, pair!, etc into SQLite 
TEXT fields so date is certainly supported at the REBOL level (although 
an "order by date" clause will not give the expected results ... 
I tend to use 'sort/skip SQL "select id,date from t" 2' type constructs 
to achieve the desired result).


Given how common this later operation is (order by date) I'm looking 
at changing the way date is bound. Instead of just MOLDing it, if 
it is transformed to YYYY-MM-DD format then not only can LOAD recognize 
it but it can be sorted (as TEXT) directly by SQLite.

Agreed. It's just that almost every datatype apart from date (pair! 
is also problematic) happens to sort correctly under SQLite as is; 
and changing date's TEXT representation so that it sorts correctly 
within SQLite is fairly easy. I'd rather write:

	result: SQL "select id,date from t order by date"


than:

	result: sort/skip SQL "select id,date from t"  2

as it's both easier to maintain and more efficient.

Replace the column-text block in the SQL function with:

	[(
		either direct [
			[*column-text (sid) idx]
		][
			[
				s: v: *column-text (sid) idx
				while [s: find s {""}] [change/part s "" 2]
				load v
			]
		]
	)]

I've added this to the next build.

0.1.9 posted with improved concatenation handling logic. In addition 
to handling strings you can now also do:

	SQL "select name||100 from customers"

and

	SQL {select id||'"-'"||name from customers}


Most REBOL types are supported (in concatenation type operations) 
although blocks may cause problems.

Value Binding: Is something like this possible as well?

	SQL ["select * from ? where col_1 = ?" mytable 1]

IMO it would be very handy, because those statements can be prepared 
and assigned to rebol words, for simple access in an application. 
It the SQL statement changes, I only have to do it at one place.

I got an error in the 'sql func ...


** Script Error: length? expected series argument of type: series 
port tuple bitset struct
** Where: switch
** Near: *bind-text sid i val length?

the database is opened with /direct refinement.

The call is:
	sql ["select * from person where guid = ?" guid1]


Where I know, that the dataset with this guid exists, because I have 
just got it from another selsct.
The dataset contains only strings, some of them empty.


Well, this is it: ["h-o-h.org_20060326_182311691_1224" "Urte" "Hermann" 
"Urmeli" "" "" "" "" "" "" "" "" "" "" "Opera ID: 359" "" "" ""]

And I am using the right guid.

Any ideas?

So, the error in one small message: 

>> sql ["select * from person where guid = ?" #"a"]

** Script Error: length? expected series argument of type: series 
port tuple bitset struct
** Where: switch
** Near: *bind-text sid i val length?

You could always encrypt selective columns client-side (e.g. sql 
reduce ["insert into t values (?,?)" key encrypt string])

anybody tested to open sqlite.r generated databases with a database 
manger?
 Not sure what you mean here. The following works fine for me:

	c:\> sqlite3.exe my-db.db

docs state that I get back the number of columns and rows
 ... no longer the case (docs need to be updated)


Most efficient way to get number of rows is sql "select count(*) 
from my-table"


Number of columns can be found by: (length? describe "my-table") 
/ 6

I can't get back the columns name

 ... use the DESCRIBE command, or CONNECT/info (which then populates 
 sqlite/columns and sqlite/widths for every query; or alternatly, 
 set sqlite/col-info? true / false for a particular query).

A simple rows function:

	set 'rows make function! [
		"Return row count."
		table [string!]
	][
		first sql/flat/direct reform ["select count(*) from" table]
	]

just add it to the body of the sqlite context.

No, not C pragma, a set of PRAGMA statements that SQLite extensions 
to SQL, for settings and diagnostics.

 wtcSQLite allows you to add/edit/delete indexes, fields, tables, 
 triggers, views, data, and manage multiple database aliases without 
 knowing any SQL at all. 
http://www.zend.com/php5/contest/contest.php?id=74&single=1

(it's phpmyadmin for sqlite)

Ashley - Bobik is getting following error (not so with original sqlite3 
protocol):

>> sql "select * from kategorie"

== [[1 30 34 M30] [2 35 39 M35] [3 40 44 M40] [4 45 49 M45] [5 50 
54 M50] [6 55 59 M55] [7 60
 64 M60] [8 65 69 M65] [9 70 74 M70] [...
>> sql "select * from kartoteka"
** Script Error: Out of range or past end
** Where: sql
** Near: either all [block? v #"[" <> first s] [rejoin v] [v]
>>

Does anyone understand this error?

>> sql "select * from comm"
** Script Error: Out of range or past end
** Where: sql
** Near: either all [block? v #"[" <> first s] [rejoin v] [v]
>>
>> sql "select guid from comm"

== [[h-o-h.org_20060326_182311681_3176] [h-o-h.org_20060326_182311681_7315] 
[h-o-h.org_20060326_182311701_2470] [h-o-h.org_2006032
6...
>> length? sql "select guid from comm"
== 541
>> probe tables

["comm" {CREATE TABLE comm ( guid, type, value, note, flags, keywords, 
reference, created, updated, deleted )} ;...

Furthermore, I sometimes have the problem, that errors in sql statements 
are not caught by try, if I call it from a view gui. 
Is this a known problem?

Hi Ashley, while trying to find a minimal code example ... I found 
the error ... ;-) 

That's the error message ...
** User Error: SQLite SQL logic error or missing database
** Near: make error! reform ["SQLite" error]

And it was caused by:

      if string? face/user-data [
         if error? set/any 'err try [

            set pAddress-disp first rule compose [pAddress get guid = (face/user-data) 
            *]
	; rule creates an sql string and starts calls 'sql with it
	; yadda yadda yadda ...
         ]
      ][probe disarm err]

Do you find the error??? 

Somehow the [probe disarm err] block moved to the wrong if ... 


I don't know how this could trigger _this_ error, but after I moved 
the block the error has not occurred again.

OK, maybe it is just me, but I can't get enough permission for my 
CGI, even if run in -cs mode, for sqlite.log to be written to (the 
script works in console though, but I am root there, so ...). Could 
the driver be updated to work with log?: false variable? I can see 
logging on four places, but only on one place (sql function) it ever 
checks the variable ....

From the User Guide: "Every connect, disconnect, error and statement 
retry is logged to %sqlite.log. This refinement adds SQL statements 
as well. While this can be useful to monitor what SQL statements 
are being issued and what the volume and distribution is; be sure 
to monitor the size of this file in high transaction environments."


If you really don't want any log output then just direct it to /dev/null

ah, so this variable applies only to disable/enable logging of sql 
statements, right?

'time is set in 'sql and used by 'format

I know that 'time is set in SQL, but it isn't used by FORMAT AFAICT

From his blogs it appears that Carl is just extracting SQLite's btree 
and indexing engine, but leaving out the SQL stuff that duplicates 
functionality already in REBOL (think blocks and series functions). 
You may be able to access the data (a little unlikely), but it won't 
be SQLite support.

Depends on the number and size of records you wish to handle, whether 
ACID is important to you, and whether you need SQL access or not.

A good entry point if you just want to grab the SQL syntax is: http://www.sqlite.org/lang.html

What am I doing wrong here:

rebol []
do %sqlite.r
do %rebgui.r

if not exists? %id.txt [write %id.txt 1]

db: %indodex.db
either not exists? db [
    CONNECT/create/flat/direct/format db

    SQL "create table base (id, Nama, Alamat, Telefon, Handfon, Fax, 
    Email, Tgl_Nikah, Nota)"

    SQL "create table birthdays (id, Nama, Jenis, Hubungan, Tgl_Lahir, 
    Agama, Nota)"
][
    CONNECT/flat/direct/format db
]
unless value? 'ctx-rebgui [
	either exists? %rebgui-ctx.r [do %rebgui-ctx.r] [do %rebgui.r]
]
set-colors
tab-size: 120x55
fonts: reduce [font-sans-serif font-fixed font-serif "verdana"]
do show-cc: make function! [] [
display "IndoDex Ver. 1.0.1" [
    tab-panel #HW data [
        "Add" [

            label "Title:" priority: drop-list 30 #W "Pak" data ["Pak" "Ibu" 
            "Sdr." "Sdri." "Drs." "Dr." "Tuan" "Nyonya"] 20x5
            return
            label "Nama:" nama: field 
            return
            label "Alamat:" alamat: area 50x30
            return
            label "Telefon" telefon: field
            return
            label "Handfon" handfon: field
            return
            label "Fax:" fax: field
            return
            label "E-Mail:" email: field
            return
            label "Nota:" nota: area 50x30
            return
            button "Save" [
                (id: to-integer read %id.txt)

                SQL/flat/direct {insert into base values (id, Nama, Alamat, Telefon, 
                Handfon, Fax, Email, Tgl_Nikah, Nota)}
                (write %id.txt id)
                show-text ex-status "Saved"]
        ]
        "Edit" [
            
	]
	"Search" [
	]
    ]
]
]
do-events

This doesn't work either:


 SQL ["insert into base values (?, ?, ?, ?, ?, ?, ?, ?, ?)" ID, Nama, 
 Alamat, Telefon, Handfon, Fax, Email, Tgl_Nikah, Nota]

Try reducing the block, as in:


 SQL reduce ["insert into base values (?, ?, ?, ?, ?, ?, ?, ?, ?)" 
 ID, Nama, Alamat, Telefon, Handfon, Fax, Email, Tgl_Nikah, Nota]

Here's the latest version:

rebol []
do %sqlite.r
do %rebgui.r
unless value? 'ctx-rebgui [
	either exists? %rebgui-ctx.r [do %rebgui-ctx.r] [do %rebgui.r]
]
set-colors
tab-size: 120x55
fonts: reduce [font-sans-serif font-fixed font-serif "verdana"]

if not exists? %id.txt [write %id.txt 1]
i: to-integer read %id.txt
i: i - 1
either not exists? %indodex.db [
    CONNECT/create %indodex.db

    SQL "create table base (ID, Title, Nama, Alamat, Telefon, Handfon, 
    Fax, Email, Tgl_Nikah, Nota)"

    SQL "create table birthdays (ID, Nama, Jenis, Hubungan, Tgl_Lahir, 
    Agama, Nota)"
][
    CONNECT %indodex.db
]
do show-cc: make function! [] [
set-colors
display "IndoDex Ver. 1.0.1" [
            label 16 "ID:" id: text (to-string i)
            return

            label 16 "Title:" title: drop-list 30 #W "Pak" data ["Pak" "Ibu" 
            "Sdr." "Sdri." "Drs." "Dr." "Tuan" "Nyonya"] 20x5
            return
            label 16 "Nama:" nama: field 
            return
            label 16 "Alamat:" alamat: area 50x30
            return
            label 16 "Telefon:" telefon: field
            return
            label 16 "Handfon:" handfon: field
            return
            label 16 "Fax:" fax: fax: field
            return
            label 16 "E-Mail:" email: field
            return
            label 16 "Nota:" nota: area 50x30
            return
            button "Save" [

                ;UNCOMMENT THE FOLLOWING LINES AND YOU WILL SEE THEY DON'T WORK. 
                ANYBODY KNOW WHAT IS WRONG?

                ;SQL reduce ["insert into base values (?, ?, ?, ?, ?, ?, ?, ?, ?, 
                ?)" id/text, title/text, nama/text, alamat/text, telefon/text, handfon/text, 
                fax/text, email/text, tgl_nikah/text, nota/text]

                ;SQL {insert into base values ('Pak' 'Ibu' 'Sdr.' 'Sdri.' 'Drs.' 
                'Dr.' 'Tuan' 'Nyonya' 'Jonew')}
                (i: to-integer i)
                (i: i + 1)
                (write %id.txt i)
                (i: to-string i)
            ]
            button "GUI Info [

                print [id/text " " title/text " " nama/text " " alamat/text newline]
            ]
            button "DB Info" [
                print TABLES
                SQLite/col-info?: true
                print SQL "select * from base"
                print SQLite/columns
            ]
            button "Halt" [
                halt
            ]
            button "Quit" [
                quit
            ]
]
]
do-events

You have a few errors in there.


 - your values are comma seperated (e.g. use SQL reduce ["..." val1 
 val2 ...] instead

 - tgl_nikah/text appears in your INSERT but isn't present in your 
 display
	- your "DB Info" button can use ... print ROWS "base"

Ashley, I notice that sqlite.r value binding is missing some records 
when using LIKE.


>> sql ["select * from base where alamat like ?" "%Grand%"]   <<=====<<< 
This fails.
== "No rows selected."

>> sql ["select * from base where alamat like '%Grand%'"]      <<====<<< 
This finds a record.

For an SQL expert, it shouldn't take more than one hour, I guess. 
Maybe even less, I didn't counted how much MySQL specific syntax 
or functions are used.

Paul : if TRETBASE can support the same DB structure, support SQL 
JOINs (or similar feature), user and security management, not being 
more verbose than SQL, and being as fast as MySQL without using more 
memory, I'll drop MySQL at once and use TRETBASE as a backend in 
all my projects.

I guess that it would also needs a few changes in the SQL requests 
also, MySQL specific features like LAST_INSERT_ID( ) need to be ported. 
Anyway, it shouldn't take more than a couple of hour to make it work 
with SQLite (once you have the right driver).

Also I'm missing basic instalation notes...
1. to add into httpd.conf:
	webapp [
		virtual-root "/curecode"
		root-dir %www/curecode-r091/ ;or different dir
	]
2. To get a known admin password, change in private/build-db.sql:
EE10C315EBA2C75B403EA99136F5B48D 
to

21232F297A57A5A743894A0E4A801FC3  ;(enbase/base checksum/method "admin" 
'md5 16) = so the known password will be 'admin' for admin account

3. to run private/install.r script

There's a bug in %index.rsp from a change I did last week in the 
statistics SQL code. Fixing...

I could refresh the list below when you jump in Detail mode (and 
clear it when jumping in Search mode), but that means doing a quite 
big SQL query on all the tickets each time you switch from one mode 
to another...I tried to avoid that in exchange to a little less intuivite 
UI. Maybe there's some better solution.