AltME groups: search

Yes, it would really help to more easily backup at the file level 
- not just one monstrous file each time, but certain tables ...

With the SQLite 4 storage engines you don't necessarily even need 
tables at all - it's a key-value store. Even SQLite recreates the 
table metaphor on top.

I use computed indexes for computed lookup lists, such as for precomputed 
intermediate results of computations, translation tables, etc. If 
the computation uses signed numbers, you have to do an offset base 
position to get the results from the positions less than 1. Having 
a hole slows down the computation because it has to be handled in 
mezzanine code. PICKZ/POKEZ would actually be better for most of 
these situations because the computations work better with 0-based 
numbers (modulus, for instance). It's pretty common in code that 
actually *needs* to use PICK/POKE on series.

(For comparison again, sorry) In R3, objects are in many ways like 
the tables in Lua, used for data purposes as well as for contexts, 
underlying several other datatypes or operations as well. Most contexts 
are declared using these other datatypes or functions that wrap objects; 
raw objects are more often used as data structures than as contexts. 
It might make sense to support case-sensitive objects as data structures. 
Nonetheless, I wasn't the one making the suggestion, and I'd have 
to do a bit of research to dig up who was requesting this.

I didn't need native/action tables until now, the interpreter made 
me add them sooner than I planned. Extending Red with new natives 
is already possible using simply routines. For new dynamically added 
datatypes , it will be soon possible too at Red/System level and 
with an appropriate interface, from Red directly (with routines when 
needed).

No, it works because it is interpreted, which is an execution model 
very different from static compilation. In the interpreter, there 
is no need to make internal tables for functions or variables, they 
are simply resolved at runtime.

Since Red needed jump tables, you can also go the other way around, 
and cast an integer/pointer to a function, then call it

Actually, the SELECT expression where the compilation error occured 
was involving a word lookup in an object context, so those lookups 
are done, as far as we know, using internal hash tables, I wonder 
if the issue is not related to hash tables implementation bugs in 
R2. If I tried to put a PROBE between SELECT and the word lookup, 
the error was disappearing.

ChristianE: nice work! 


How do you cope with multiple columns having the same name returned 
by INSERT? This can happen when you select from several tables, and 
may lead to nasty errors.

I was parsing your mdp docs to convert to epub and found some have 
authors or not, dates or not, tables were done differently etc

Today I tried combining some tables in Excel, but without (frustrating!) 
no success. So tomorrow I will try and build a quicky REBOL script 
to put the data in one Rebdb databasetable and then do a dump of 
that and import that again in Excel.

So I combine data NAME PROP1 with NAME PROP2 giving a table NAME 
PROP1 PROP2
Any tips suggestions for lookalike scripts? Tia!

i am comparing values of two tables

Not sure what to do about the lack of metadata support ...which is 
only really used when updating tables

so they think its better to keep it. 

so if you use MSSQL use sp_databases, or sp_tables; for mysql use 
SHOW TABLES etc..

or select * from INFORMATION_SCHEMA.tables for many of them (works 
on MSSQL 2005+, mysql, postgre, not sure about sqlite)

If you want to get the column metadata about a table, use the 'columns 
command:
- For R2: http://www.rebol.com/docs/database.html#section-27

- For R3: http://www.diefettenjahresindvorbei.de/odbc/odbc-docs.html#section-3.8
 though there's an error in the docs, so use 'columns instead of 
'tables

I used 'columns and 'tables with R2 on SQL Server, it does work well.

Back in 2001 I created an intranet site for a local celebrity by 
making the database tables, then having R2 use the 'columns command 
to generate column lists that were used to autogenerate the server-side 
pages for the intranet. All it took was one function call per table 
to generate the whole site. Restyling the site just meant editing 
the HTML template in the function (this was before CSS was practical).

Got an answer from the provider. connection not from the website 
are blocked for safetyreasons. Only reachable by scripts on the website. 
The "admin" user for adjusting tables etc is not the admin user for 
granting access etc to users.

Or simply I have to use more powerfull SQL solution, but heck, I 
don't like all the heave SQL servers for some simple few tables solution 
....

Thought I'd share this in case anyone found it useful (or had any 
suggestions/criticisms).  First part is a description in REBOL of 
a database schema (sample here: http://reb4.me/r/schema-sample), 
second part is a function that generates the MySQL code to create 
the database/tables. I'd like to add functions to analyse database 
schemas against my REBOL schema and create 'ALTER' statements as 
necessary.  All in good time...

hey guys... do you know how  we can check values of two tables in 
mysql?????!!!!

so you have put the users answer also in a mySQL table?

read both tablerows from the tables into different variables and 
compare the subfields of the returned rows result1/fielda result2/fielda

could you tell me how can i compare values of two tables in database?

@TomBon: my query for joining two tables is :insert db["select * 
from data LEFT JOIN data1 ON data.oneone=data1.oneone"]     and output 
is :[

    ["c" "a" "t" "a" "e" "r" "o" "a" none none none none none none none 
    none]

]    plz tell me what should i write in query that i get values instead 
of none in output ?

i compare each field of tables with each other like :insert db["select 
data.oneone,data1.oneone from data LEFT JOIN data1 ON data.oneone=data1.oneone"]
results: copy db 
probe results

insert db["select data.onetwo,data1.onetwo from data LEFT JOIN data1 
ON data.onetwo=data1.onetwo"]
results: copy db 
probe results

insert db["select data.onethree,data1.onethree from data LEFT JOIN 
data1 ON data.onethree=data1.onethree"]
results: copy db 
probe results .....

guys ! could you plz tell me after comparing values of two tables 
how can we show the output on web page?

divs really cause sooooo much browser display issues.... tables usually 
are easier to manage.

looking forward to proper tables support... which seems not to work 
yet in make-doc-pro

So not using the hierarchical model, we can end bold in the middle 
of doing italic text. That is confusing, because then we should be 
able to do the same with tables.

A way to go may be to make a deep analyse of, what a document is 
- what it consist of. There are basic elements like letters, dividers 
(<br/>), ... Then there are bigger elements (containers) like notes, 
tables, ... And we can talk about change of state like bold-on, bold-off, 
italic-on, italic-off, font change, etc. The containers should be 
strictly hierarchical. The basic elements and the containers will 
be represented in a sequence. The inside of a container is maybe 
also a sequence. Decisions should be made, if change of state can 
happen anywhere, or if going to one state and back is a container 
too. (I'm thinking loud here, you may notice.)

tables: What's not working at the moment?

and about tables in MDP, are they completed? I can't get them working

try this from your rebol console.. 

do load http://powerofq.com/start
°load° "°mysqlprot°"


a: read join mysql://yourName:[yourPassword-:-localhost]/yourDatabaseName 
"show tables"

Here are the results: >> do load http://powerofq.com/start
connecting to: powerofq.com
>> load "mysqlprot"
== mysqlprot
a: read join mysql://tim:[password-:-localhost]/hs4u "show tables"
>> a: read join mysql://tim:[password-:-localhost]/hs4u "show tables"

** Access Error: Invalid port spec: mysql://tim:[password-:-localhost]/hs4ushow 
tables

** Near: a: read join mysql://tim:[password-:-localhost]/hs4u "show tables"

I googled it and added the user as per the OLD-Password syntax. Now 
I tried a: read join mysql://tim:[password-:-localhost]/hs4u "show tables"
and got the same error message

yes gabriele, it works fine for this ->  char *mysql_info(MYSQL *mysql) 
 points to a char, but this -> MYSQL_RES *mysql_list_tables(MYSQL 
*mysql, const char *wild) delivers the integer pointer again as string. 
I know I have to provid a struct! which will be filled by passing 
the pointer to the routine but can I pass just a empty block instead 
a struct! ?, or better pass the recieved integer pointer later to 
a block ?

Is there any conclusion about DocKimbel's mySQL driver and 4.11 server 
release? Does it work or no? In terms of one project, where we exchange 
data, client agrees to our proposed way, only if we import data to 
their tables on their server. It is Debian linux, mySQL 4.11 or so, 
so before I produce some script and give it to them, I would like 
to ask if the problem of connection/authentication is resolved? Thanks 
a lot ....

have you any short script which will try to set-up new db, tables, 
insert records etc.?

nope, no script. It seems that I made some troubles in my install 
by copying my olds testing tables in the new server.

With v3.x.y servers, there's still sometimes connections errors 1045 
that shouldn't happen. I guess that's related to some encryption 
implementation difference between my client and v3 servers. Workaround 
for this, is catching error! values when connecting and retrying 
the connection if the user/pass is fixed and should be able to connect 
to the server (proper right set in 'mysql' tables).

I mean running a v5 server with v3.x tables.

I've just run a big stress test with 40'000+ requests generating 
around 2Gb of traffic without any trouble ! No bad handshakes anymore 
if you're using v5+ tables and new passwords.

1) Load the REBOL mysql console : do %mysql.r
2) Connect to the server
3) Command: show tables
4) result ok
5) Stop/Start the server
6) Command: show tables
7) result ok (the driver has reconnected transparently)

I further investigated all this. Yes, the tables are used by the 
kernel (if told to do so but what I see it's mostly so by default) 
and can be changed dynamically.

yes, of course, no problems. I really liked RebDB - imo really cool 
piece of code ... but unless you need join multiple tables and have 
some more relations ....

The ISP owns the mysql db .. and opens up some tables for you to 
use.  So, no, it's not possible unless you run your own Vm like as 
on Amazon or linode or slicehost or whatever.

Petr -- there is no design in non-CSS HTML that you can't achieve 
with CSS -- even if you need to use barebones tables occasionally 
to do column things.  The statement "why do all .css based pages 
look the same?" might as well say "why do all web pages look the 
same?".

If I understood them correctly, Photoshop helps them to cut image 
into html design .... I wonder how they decide what will be covered 
by typical html aproach (e.g. tables etc.) or using some div + css 
aproach, as that requires rather distinctive aproach?

I did not say I don't like how css look. It looks maybe even better, 
kind of a book design -more typographically correct, more blending 
of images and text, which was not easily possible with tables etc., 
simply different ...

re workflow - interesting - I will order Zeldman's book too .... 
Alistapart seems to be a good resource too. I never build large site, 
just few webpages, generated by script or using DreamWeaver 4 long 
time ago. My mine problem right now is - how to start to think of 
a design. From typographics area I know I should divide page into 
some sections. I did so using tables extensively, now I wonder, if 
I should use css columns, simply using "div" tags ...

Divs are ultimately more flexible than tables.

Tables have their place -- for the display of tabular data as they 
were intended.
For page layout and markup, DIVs are ultimately less boxy.

They also, usually, produce data flow that is more friendly than 
tables for people using accessibility aids.

So for typical boxy design, as e.g. webshop product listing, you 
would use tables or not?

know what next task is a true challenge making tables in the style 
of GUI4CLI ... I'm affraid to turn mad ;)

this code sample show how multi column tables are treated using Gui4Cli 
;)

But as you say it normand it's not an easy topic so being able to 
solve it will be in my opinion a community effort because we need 
a little more than TDM  (to render tables for example... )

just installed postgesql 8 on windows 2003, but can't seem to create 
tables.  Get syntax errors reported.

for me to be honnest the advantages of  using SVG instead of images 
 are a lot this is a quick list of my thoughts on what can be done 
with  SVG format.
 - easier embeding of graphical content
- resizing auto ith not grphics quality loss
- cheap but sharp skining 

 - graphical content of an  User  Interface  very low because rebol 
 is pretty able to  work with compression of text content ( compress/decompress)

A little sample to enlight this  compressed SVG file with blender 
logo ->  less than 500 octets (any size can be applyed with no graphic 
loss (aliasing, color loose, blur, pixelisation etc..) )  a fixed 
jped for same content will use  around  50ko. This means less datas 
in memory to as graphic pixel tables are very heavy in VM memory

- Dynamic graphical content. some thing like text scrolling or efffect 
like Flash ones but with  a  reduction of  1000  of the data and 
memory use SVG use a labeling system for  each graphical content 
that compose the image we can provided a tiny system to handle easy 
animation and show/hide of graphical content using raw rebol commands 
-> this will make pretty easier the realisation of dynamic animations 
in rebol ViD content and  as any REBOL/View face is DRaw/AGG capable 
the effects that we can create isn the futur with this idea will 
have no limits  -> this means too to have a pretty fast and ell designed 
SVG engine (that's my actual path on research)

I have rebol scripts for converting chars to unicode, the problem 
is, that Rebol cannot print unicode strings and the socond thing 
is, that you need conversion tables. There must be some way how to 
distribute these tables from network, because I'm sure you don't 
want to make Rebol about 2MB bigger than now you to support some 
charsets.

tutorial: using word tables: http://www.geocities.com/benjaminmaggi/doc/word_tables.htm

also im thinking about XPCOM i found it to be a more open technology 
approved by many companies and products, adding support for this 
technlogy would be great and the sources are out there for free.

  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."

<Pekr>
Ashley, just wanted to ask and can't find rebdb group here ...


Isn't it possible to implement 'join? You once said that you will 
wait once RT adds RIF, but that will probably come who knows when 
- it is year and half late already. Do you think it would not be 
possible to proceed without RIF and switching to on-disk storage?


'join is really badly missing with rebdb and I am thinking switching 
to sqlite only because of that one feature. Once you have your data 
spread across many tables, it is difficult to work without it. Or 
how you do it?
</Pekr>

yes, but what you describe is more than day-by-day example of proper 
db usage. Even with small projects, when using 3NF notation, you 
simply store only foreign keys in tables, so those "aggregate" functions 
are needed too often ...

But, Petr, perhaps it would be good if you could show the operation 
and tables that you are having trouble with, and Ashley can help 
you find the best way. Maybe the performance will be more than you 
need, and the expression simpler in rebol.

ok, here it is: you have tables called 'orders, 'order-items, 'companies 
.....

UNION cannot substitute for JOIN. UNION aggregates the rows returned 
by multiple statements while JOIN [can] aggregate the columns returned 
by referring to multiple tables. A subtle but important distinction. 
For example:

Table-A
	Col
	====
	1
	2

Table-B
	Col
	====
	A
	B

	select a.col, b.col from a, b

	1 A
	1 B
	2 A
	2 B

	select * from a union select * from b

	1
	2
	A
	B

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?

but you do need to do two queries beforehand... which is not exactly 
a join.. but anyhow you do end up with a block containing interleaved 
data from two tables ... and its pretty quick.

Works great.. Thanks!


You know, a really cool feature for RebDB would be to use tables 
stored on an ftp instead of locally. I think it would be possible 
since it's RAM based and you control your commits, but I could be 
wrong :) Now I'll have to sync my database manually every time I 
change computer

Is it possible to have some tables locally and some on the ftp? Maybe 
I can make a copy of "db" and use it in parallell?

This ftp based storage is kind of neat.  I have to update my users' 
tables, and need to provide a script that updates their firebird 
tables.  I can embed the data in the update script, but this sounds 
a much better idea.

Depends on how much data and how much complexity you need. RebDB 
is good for under a million rows where the column data is short (i.e. 
not storing 1MB strings). SQLite is a better choice for lots/big 
rows or where you need non-trivial joins/views. I'd use RebDB for 
simple apps like a "Contacts" DB, and SQLite for anything requiring 
half a dozen or more tables.

Anyone had any experience with SQLite (http://www.sqlite.org/), especially 
in conjunction with REBOL? What are the limitations of sqlite3-protocol.r 
(http://www.rebol.org/cgi-bin/cgiwrap/rebol/view-script.r?script=sqlite3-protocol.r), 
if any?

I'm interested to see how folks have dealt with issues such as:


 1) Support for datatypes other than TEXT, INTEGER, NUMBER and BLOB?

 2) All tables in one DB, or one table per DB to improve concurrency?

 3) Multi-user access across a network, given this statement from 
 the SQLite website:


SQLite uses reader/writer locks to control access to the database. 
(Under Win95/98/ME which lacks support for reader/writer locks, a 
probabilistic simulation is used instead.) But use caution: this 
locking mechanism might not work correctly if the database file is 
kept on an NFS filesystem. This is because fcntl() file locking is 
broken on many NFS implementations. You should avoid putting SQLite 
database files on NFS if multiple processes might try to access the 
file at the same time. On Windows, Microsoft's documentation says 
that locking may not work under FAT filesystems if you are not running 
the Share.exe daemon. People who have a lot of experience with Windows 
tell me that file locking of network files is very buggy and is not 
dependable. If what they say is true, sharing an SQLite database 
between two or more Windows machines might cause unexpected problems.

I am a bit worried about that - all tables in one file, because of 
no readability (not plain text, but a binary) and possible file corruption, 
but it seems to me, that it works, or sqlite would not be so highly 
praised ...

I also somehow don't like too much having everything in one file 
:-) (because I expect simple backup of some tables could be done 
simply by copying files ... I wonder, if I would use separate file 
for some tables, if it would be able to join them etc.?

1) how to influence where it stores/creates database?


Don't know, I've only been looking at all this for a day and havn't 
worked that out yet either.


2) if I would use separate file for some tables, if it would be able 
to join them


Yes. The ATTACH command lets you "hook up" to multiple databases, 
and you can prefix references with database name.

3) noticed there is brand new version of techfell protocol


Of the *four* sqlite scripts on REBOL.org %sqlite3-protocol.r is 
the one to use if you have a Pro licence.

4) those guys are really screwing with GPL license ...

Who? SQLite is PD as is %sqlite3-protocol.r

Re: RebDB. I'm coming to the conclusion that SQLite is a superset 
of all I wanted from RIF; and it's:

	1) Here today
	2)Lean & mean
	3)One small (256KB) drop in DLL
	4)Works well with REBOL
	5)Public Domain


And, like RebDB, it is pretty flexible about column values (it uses 
column affinity to suggest how values may be coerced – so an INTEGER 
column could contain [1 999 “three” “four”])  which is more REBOLish 
than most other RDBMS's; and all values (TEXT, INTEGER, REAL and 
BLOB) are stored as variable byte length.


My benchmarking of RebDB vs SQLite with three tables; Customers (100), 
Orders (10 per customer) and Items (10 per order) gave the following 
results (measured in Transactions Per Second, with the first for 
RebDB and the second for SQLite):

	select * from Customers 148		120
	select * from Orders	141		11
	select * from Items	 76		 .73
	selective join on Order-Items for one order	8.93	437.23
	join on all Order-Items			.08	4.93
	size of all dat file(s)			410KB		625KB


What you have to remember is that RebDB is 100% memory-based using 
tight loops and REBOL natives where possible, versus SQLite which 
is disk-based and being called via routines.

Alek - as for backups, one file per table is imo better, because 
imagine your all-tables-in-one-file containing blog, the db may easily 
exceed large size ....

As I mentioned near the beginning of this thread, SQLite supports 
multiple database files each containing one or more tables - in fact 
they go so far as recommending that you separate multiple high-access 
tables out into different databases for concurrency reasons. In this 
sense, SQLite "databases" act more like traditional "tablespaces". 
So, if we wanted we could write our REBOL front-end so that it created/accessed 
each table in a database of the same name thus ensuring a one-to-one 
mapping between table names and database names. The advantages of 
this approach are:

	backups (only those tables that change need be backed up)

 external table administration (you can drop a table by deleting its 
 database file)

 concurrency (you spread your file locking across a greater number 
 of physical files)

Disadvantages:


 Administering your database is more cumbersome (you can't use the 
 sqlite3 admin tool to administer all tables in one session)

 Value of sqlite_master is diminished (you can't "select * from sqlite_master" 
 to report on all your tables in one query)

 Query references need to add a database prefix when referring to 
 a table not in their own database

 Name conflicts (all tables in one file means multiple databases can 
 use the same table names - the solution with multiple files would 
 be to segregate at the directory level)

 Multiple database files means you need to zip them prior to some 
 operations such as email attachment, etc


On balance, I actually prefer the one file / one database approach.


Pekr's other comments in relation to schema implementation also have 
merit (I've agreed with Pekr twice today - a new record!); I see 
the value of an ftp schema, an http schema, etc; but what value in 
a sqlite schema? Given that the entire schema can be written in a 
much more concise fashion as an anonymous context that exports a 
couple of key access functions to the global context; I can't see 
what the functional differences between the two implementations would 
be?


So, bar any good reasons to the contrary, these are the features 
of the implementation I am currently working on (a rough design spec 
if you like):

	Implemented as an anonymous context

 "Database" is a directory (which is specified when a database is 
 opened with 'open-db)

 Each table resides in a "tablespace" (aka SQLite database file) of 
 the same name
	File is automatically opened on first reference

 The /blocked refinement of 'db-open specifies that rows will be returned 
 in their own block (default is a single block of values)

 Non-numeric values (which SQLite stores natively as INTEGER and REAL) 
 will be subject to 'mold/all on insert and 'load on retrieval

 The /native refinement of 'open-db will turn this behaviour off (see 
 comments below)

 SQLite binding will be supported allowing statements such as ["insert 
 into table values (?,?,?)" 1 [bob-:-mail-:-com] "Some text"] and ["select 
 * from table where email = ?" [bob-:-mail-:-com]]


Whether to store values (including string!) as molded values in SQLite 
is an interesting question; on the one hand it gives you transparent 
storage and access to REBOL values – but at the performance cost 
of having to mold and load every TEXT value returned; and the storage 
cost of the overhead of a molded representation. On the other hand, 
if I only want to store numbers and strings anyway then I don't want 
this overhead. I think the only practical solution is a /native type 
option as detailed above.

<<SQLite "databases" act more like traditional "tablespaces">>
That's a nice flexible approach.

It may add an apparent unnecessary level of complexity for small 
databases, but the tablespace approach is intended to scale almost 
unlimitedly.

Think to when REBOL has taken over the world, and we have tables 
that exceed a single disk drive. Tablespaces exist (in part) to handle 
that sort of issue.

It's a data space that contains tables.

A tablespace can be split across disk volumes (or servers or machines)
And a table is defined as residing in one or more tablespaces.


The unit of back up is a tablespace -- or a database (which consists 
of 1 or more tablespaces)

It adds a lot of flexibility for large systems. But can be overkill 
for smaller ones....Where you probably just have one tablespace that 
lives in one folder.

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

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

Changes of note include:


 - New /format refinement of CONNECT that formats output like MySQL 
 (can be turned on/off via SQLIte/format?: true|false)
	- Widths block (SQLite/widths) added to supporrt above

 - DESCRIBE, TABLES and INDEXES functions rewritten (and simplified)
	- Added an EXPLAIN function

 - CONNECT rewritten (note that the attach refinement has been replaced 
 by the function accepting a block! of file names instead)
	- DATABASE function added

 - Experimental IMPORT function added (but not exported to global 
 context - see example for how it is used)

 - Error trapping / reporting more informative (especially if a library 
 call error occurs)
	- Example block updated (do example)

Documentation will be updated as time permits.

/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?

Yes, just use CONNECT/flat and the driver will return all values 
in a single block which RebGUI can use directly in lists and tables 
(no conversion required).

Did anybody tested to open sqlite.r generated databases with a database 
manger? I can't. In one tool I can see the database but not tables, 
even I have created one.

And if I use the TABLES command I get:
>> print tables

material CREATE TABLE material (_id INTEGER PRIMARY KEY AUTOINCREMENT,_version,_benchmark,_prev,_next,_product_id,name,price,pcdl,pudl,netto,nudl,scrap,loss,process_loss,scrap_re

cycle_rate,scrap_recycle,srcdl,srudl,process_loss_recycle_rate,process_loss_recycle,plrcdl,plurdl) 
sqlite_sequence CREATE TABLE sqlite_sequence(name,seq)

IIRC you can even access those internal tables of SQLite and query 
them.

Well, for the most part this database metadata isn't really stored 
in tables in SQLite. Instead queries about the metadata are generated 
from internal structures when needed, by pragma statements. Still, 
the effect is the same.

 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)

If you have a complex app, it's impossible to get the tables right 
the first step. What I have done so far is to only put data into 
columns that is required for SELECT statements. All other data is 
stored as a rebol block in one generic _DATA column.

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

Sorry I mean TABLES information.

there is one thing I really don't like about sqlite - it stores everything 
into one file. I want one file for table, one file for index, as 
with mysql, because for me it means simplicity - I can just look 
into file system and see how big some table is, or selectively backup 
some tables .... mySQL works that way IIRC

Carl mentions paging. Does that not mean that you can have really 
big tables that are swapped to disk?

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

Click on the <DB Info> button, and you will see that the tables have 
been created. But I haven't been able to insert any data.


Another problem is that the id field doesn't advance to the next 
number.

ok, so how can I explain to  mysel data corruption? It is reproducable. 
should I set type of fields when creating tables? Or should I create 
tables in external tool?

I need to output tables and text at specific locations as well as 
bar codes

Qtask (for exmaple) supports ALL browser, since www.iQtask.com is 
simplly HTML. 

Qtask did some tricky things with JavaScript to control tables, which 
is why Safari and Chrome fail.  We should have this fixed by the 
end of this month.  It is a small set of fixes.
So we then will support all browsers.


But still I woudl build a custom version of Chrome to do some "extra" 
tricks.  We also plan to support Flash plug ins, for audio support, 
etc.  These are all extras.

I love your "killing REBOL schtick" .


REBOL is a language, the x-internet concept is one of the many cool 
things about it.  BUT, for now, I'm going to keep my marraige in 
place between REBOL + HTML + JAVASCRIPT.

But Draw dialect is really too level for a daily use. A higher level 
dialect with relative positionning and higher level constructs (e.g. 
tables support), like VID or HTML is needed.