Models and Connections

How to make models, connections and migrations.


First we need to define database connection;

def db = (database("dbtype","dbstring"))

Database type and Database strings are connection types. For example you need sqlite3 connection;

def db = (database("sqlite3","sqlite_database_example.db"))

Now ready to connect models and databases;

def modeldef = (model(db, "modelname", {....table definitions}))

Db is your database connection. Model name and table definitions make table structure. Modeldef is simple model definition.

For example we need to users and payments tables;

def user = (model(db, "users", {
    "id": "int"
    "name": "text",
    "surname": "text",
    "username": "text",
    "email": "text",
    "password": "text",

def payment = (model(db, "payments", {
    "user_id": "int",
    "money": "int",
    "currency": "text",
    "datetime": "DATETIME",
    "duedate": "DATETIME"

All models ready for use. If you need migration with database;

drop(modeldef) #=> if your table exist in database use drop function first before migration.

Modeldef is your definition. We need two models for migrate;



How to create or update data from database.

Now everything was ready. We go on create and update statements.

create(modeldef, {values and keys... for create})
update(modeldef, {values and keys...}, {values and keys...})

Create function needs two things;

Modeldef is your model definition and objects are your objects.

Keys and values (Hash) for what do you want to create.

For example;

create(user, {
    "id": 1,
    "name": "Oytun",
    "username": "oytunistrator",
    #.....other information
create(payment, {
    "user_id": 1,
    "money": 100,
    "currency": "$"
    #....other information

We created one user and created payment for example. Now we need to change payment values;

update(payment, {"user_id":1}, {"money": 200})

Payment changed 100 -> 200 for first user.


How to fetch data from database.

Now we look changes and etc. Use simple ussage;

def resultdef = (fetch(modeldef, {...keys and values}))

Resultdef is result definition and model definition with keys or empty for all data.

For example we need to view payment changes;

def payment_list = (fetch(payment, {"id": 1})

We fetch first user from payments and defined to payment_list definition. Inspect command looks to some definition debugging.


How to make database search.

Search all in database with single and multiple queries. Results always array.

def resultdef = (search(modeldef, [.....strings] or string))

Resultdef is your result definition and modeldef is your model definition. Query side is too simple. Use one string to search or use array for multiple search in single result. Thats magic.

This too easy for query management.

For example you make indexing pages but you cannot find best pages with query;

def indexdb = (database("internal","./indexdb"))
def page = (model(indexdb, "pages", {}))
def i = 0;
  create(page, {"page_"+i:"Example Page ("+i+")"})
  def i = (i+1);
def results = (search(page, ["page_8","page_5", "Example Page 4"]))
...page_8, page_5 or "Example Page 4" search results

Automaticaly search in key,value based storage and gives results for use.

Sub Note:

Some database types can give you results based on variables you give. You can create your own functions in unsupported types.


How to make query in database.

If you need more customized sql queries. Query function is too simple;

def resultdef = (query(modeldef, "YOUR QUERY....", params))

Resultdef is your result definition and modeldef is your model definition. Queries and params prepare for query. It needs more customization for your needs.

For example we get users query with limited data;

def limited_users = (query(user, "SELECT * FROM users LIMIT ?", 100))

We defined limited_users and run parameterized query. We limited 100 users to get. All done.


We need to delete something from model. Use simple case;

def resultdef = (delete(modeldef, {....keys and values}))

Resultdef for result (maybe true, false or error). Modeldef is your model definition. Keys and values (aka hash) for what do you need to delete.

For example delete maked payment;

def deleted = (delete(payment, {"id": 1}));
if (deleted){
    show("Payment 1 deleted.")

Deleted definition is simple. We are deleted first payment.