r3wp [groups: 83 posts: 189283]
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search
 

World: r3wp

[SQLite] C library embeddable DB .

Pekr
22-Dec-2006
[560]
I read "manifest typing" and it is strange - even if you set your 
column as an integer, it allows you to enter non-integer data ...
Robert
22-Dec-2006
[561x2]
That's cool!! One of SQLite big advantages. Why should I be forced 
to tell the type upfront?
There is just no need for this. The DB should store each cell most 
efficient.
Pekr
22-Dec-2006
[563]
what do you use for typical rebol strings, or even dates? text? varchar?
Robert
22-Dec-2006
[564]
I never specify anything. Only for INTEGER
Pekr
22-Dec-2006
[565]
that has a bit of a bad effect for sqladmin grid ... if you don't 
specify field type or length, it lists one collumn wide thru all 
the screen ...
Robert
8-Jan-2007
[566]
Question: I have something like a bill-of-material. And I would like 
to get such a structure back as graph. I'm just thinking of this 
isn't a generic function suitable to be coupled with a database. 
What do you think?
Pekr
8-Jan-2007
[567x2]
Robert - what do you mean as a graph? A hieararchical structure?
I have good link, although it relates to mySQL - it shows how to 
query hierarchical data - http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
Robert
8-Jan-2007
[569x3]
yes, most likely but with the possibility to have cycles etc.
Thx for the link. As always, good references Petr. You should make 
your money with answers.google.com
and of course having more than one parent.
Pekr
8-Jan-2007
[572x2]
heh, never heard of answers.google.com ... well, maybe that is why 
I would like to work as a consultant, hopefully with IBM ... the 
bad thing is, that I might miss programming a bit :-)
as for real graphs with relational model storage - that is beyond 
my understanding .... hopefull article might give you some pointers 
...
Sunanda
8-Jan-2007
[574]
It's worth tracking down some of Joe Celko's articles on the issue 
too.
http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html
Robert
17-Feb-2007
[575x2]
I'm thinking about updating to the newest SQLite version. And I just 
read there is a new API which should be used.


The sqlite3_prepare_v2() and sqlite3_prepare16_v2() interfaces are 
recommended for all new programs. The two older interfaces are retained 
for backwards compatibility, but their use is discouraged. In the 
"v2" interfaces, the prepared statement that is returned (the sqlite3_stmt 
object) contains a copy of the original SQL. This causes the sqlite3_step() 
interface to behave a differently in two ways:


If the database schema changes, instead of returning SQLITE_SCHEMA 
as it always used to do, sqlite3_step() will automatically recompile 
the SQL statement and try to run it again. If the schema has changed 
in a way that makes the statement no longer valid, sqlite3_step() 
will still return SQLITE_SCHEMA. But unlike the legacy behavior, 
SQLITE_SCHEMA is now a fatal error. Calling sqlite3_prepare_v2() 
again will not make the error go away. Note: use sqlite3_errmsg() 
to find the text of the parsing error that results in an SQLITE_SCHEMA 
return. 


When an error occurs, sqlite3_step() will return one of the detailed 
result-codes like SQLITE_IOERR or SQLITE_FULL or SQLITE_SCHEMA directly. 
The legacy behavior was that sqlite3_step() would only return a generic 
SQLITE_ERROR code and you would have to make a second call to sqlite3_reset() 
in order to find the underlying cause of the problem. With the "v2" 
prepare interfaces, the underlying reason for the error is returned 
directly.
Ashley, are you working on an update to SQLite.r at the moment?
Pekr
17-Feb-2007
[577]
How long will the old API exist?
Robert
17-Feb-2007
[578]
I don't know.
Pekr
17-Feb-2007
[579]
btw - do you use collation for the german language?
Robert
17-Feb-2007
[580]
No, just the plain version.
Ashley
17-Feb-2007
[581]
are you working on an update to SQLite.r at the moment?

 I didn't even know they had changed the API! If it's an easy change 
 I'll do it sooner than later.
Will
17-Feb-2007
[582]
one more link related to trees in mysql: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
Robert
18-Feb-2007
[583x2]
Will, thx for the link.
Ashley, it looks mostly compatible to me, just the returned errors 
are a bit different.
Ashley
20-Feb-2007
[585]
1.0.3 available at: http://www.dobeash.com/download.html

Supports latest SQLite v2 API.

*** WARNING ***
Is not compatible with older versions of the SQLite DLL.
Robert
22-Feb-2007
[586]
Great! I'll give it a try with a new DLL.
Ashley
21-Jun-2007
[587]
http://www.sqlite.org/

2007-Jun-18 - Version 3.4.0


  This release fixes two separate bugs either of which can lead to 
  database corruption. Upgrading is strongly recommended.
Pekr
2-Jul-2007
[588]
is there any solution, how to infuence shell window appearance, if 
using sqlite3.exe? There seems to be no silent mode ... you can start 
sqlite3.exe and type .help to see more comands, or look here:

http://www.die.net/doc/linux/man/man1/sqlite3.1.html
Ashley
20-Jul-2007
[589]
1.0.4 available at: http://www.dobeash.com/download.html


Mac OS X version 10.4 support added as per http://developer.apple.com/documentation/MacOSX/Conceptual/OSX_Technology_Overview/AppTechnology/chapter_5_section_20.html
Pekr
22-Nov-2007
[590]
Our Sqlite docs say: "2.3.3 Fkeys - This refinement returns information 
about the foreign keys (if any) that reference a table.", but sqlite 
does not support foreign-keys, only via triggers, no?
Pekr
23-Nov-2007
[591x2]
hmm, sqlite does not provide insert or update facility, that is weak 
:-(
it has insert or replace = replace, but that deletes original record 
and replaces it with new, probably changing row id, so no facility 
like insert .... on duplicate key update .....
BrianH
23-Nov-2007
[593x3]
What are you talking about?
All updates work like that, in every database that I am familiar 
with. It's a transaction isolation thing.
I don't think it changes the row id though, and definitely doesn't 
change the primary key.
Pekr
23-Nov-2007
[596x3]
simply put, I don't want to issue a reqeust first, then decide if 
received recordset is zero lenght to insert, and if ther is some 
record, to update ....
so you trying to say, that update, if record does not exist, will 
add new one?
it does not sound appropriate, or why guys were asking mysql to add 
insert .... on duplicate key update facility, if update would be 
able to do it itself?
BrianH
23-Nov-2007
[599x2]
No, I'm saying that update deletes the original record and inserts 
a new one, on every relational database I know. No update-in-place.
It just doesn't check for cascading actions or trigger events until 
it is done.
Pekr
23-Nov-2007
[601x4]
imo our driver does not work correctly. I am totally confused by 
modes. Neither /direct nor normal mode does what I expect. I want 
rebol string to be stored as string in sqlite.
it might work with rebol, but when you go to consolo and issue select 
* from test where id='5', it is not found. Because rebol stores it 
as "5", so I have to issue '"5"'
hmm, but issuing single '5', it is returned to rebol as integer, 
even if column type is text, that sucks :-)
now I have to choose, if I work with rebol only, or console only.
BrianH
23-Nov-2007
[605]
SQLite used to only have text values - numbers were translated at 
runtime. Perhaps the REBOL driver hasn't caught up.
Pekr
23-Nov-2007
[606x2]
I don't understand what is our /direct mode good for, it does things 
as {"1234"} .... without it, rebol stores "1234" .... now if you 
want use tools like sqliteadmin, you would be mad using /direct mode 
stored values ...
I simply want rebol string "1234" to be stored as '1234' and when 
returned from query to be it once again "1234" not integer if I use 
'1234' (when using compose and sending string query)
BrianH
23-Nov-2007
[608x2]
MySQL has a non-standard command for inserting if not there for update: 
REPLACE. SQLite has more detailed conflict resolution, but includes 
REPLACE as a MySQL-compatible shortcut for INSERT OR REPLACE (the 
OR REPLACE is itself a shortcut for ON CONFLICT REPLACE). I agree 
that UPDATE should have the option of acting like INSERT OR REPLACE.
As for the datatype mismatch, I would have to look at the code of 
sqlite.r to see if it is aware that SQLite data is no longer typeless.